MySql

1.终端登录数据库

mysql -uroot -proot

2.查询所有的数据服务器

show databases;  //一定要加分号

3.选择一个数据库进行操作

use 数据库

4.退出数据服务器

exit;

5.创建数据库

create database 数据库名;

6.查看某个数据库中的所有数据表

use 数据库;
show tables;  #查看当前库的所有表

或者
show tables from 数据库名; #在自己的库查看其他库的所有表

7.查看自己所在的数据库

select database();

8.创建表

create table 表名(
(列名) (列类型),
id int,
name varchar(20),
pwd varchar(20));  #最后一个不加分号

9.查看表结构

use 数据库名;
desc 表名;

10.查看表里的数据

select * from 表名;

11.向表中插入数据

insert into 表名 (字段名,字段名) values (1,'哈哈');

12.更改表中的数据

update 表名 set name(字段名)='嘻嘻' where id=1;

13.删除表中的数据

delete from 表名 where id=1

14.基础查询

#语法
# select 查询列表 from 表名;
# 查询列表:表中的字段、常量值、表达式、函数

1.查询表中的单个字段

select 字段名 from 数据库名;
seletct name from user;

2.查询表中的多个字段

select name,pwd,sex from user;

3.查询表中的所有字段

select * from 数据库名;

15.as起别名

使用as起别名

select 100-98 as 结果;

select name as 姓名,sex as 性别 from user;

使用空格起别名

select name  姓名,sex  性别 from user;

别名中有关键字,使用引号引起来

select money as "out put" from salary;

16.distinct去重

select distinct dep_ip from emp;

17.concat拼接字段

select concat('a','b','c') as 结果;

18.ifnull判断是否为空

select ifnull(money,0) as 金额 money from 表名;
#如果money为null则显示为0

19.条件查询

#语法:
#select 查询列表 from 表名 where 筛选条件;

# 筛选条件:
# 1.按条件表达式筛选
# 	条件运算符:> < = !=(<>) >= <=

# 2.按逻辑表达式查询:
#   作用:用于连接条件表达式
#		逻辑运算符:
#		&& || !
#		and or not
#   &&和and: 两个为true才为true,否则是false
# 	||和or: 一个为ture则为ture,两个为false才是false
#   !和not : 取反,true为false,false为true

# 3.模糊查询:
# 	like 配合通配符%(%任意多个字符)使用,通配符_ (任意单个字符)
#		between and
#		in
#	 	is null

1.按条件表达式筛选

#查询工资>10000的员工信息
select *  from emp where salary>10000;
#查询员工编号不等于90号的员工名和部门编号
select name,dep_id from emp where dep_id!=90;

2.按逻辑运算符筛选

#查询工资在1000-2000之间的员工名、工资

select name,salary from emp where salary>=1000 and salary<=2000;
#查询部门编号不是在90-110之间,或者工资高于1000的员工信息
select * from emp where dep_id<90 or dep_id>110 or salary>1000;

3.like模糊查询

#查询员工名中包含字符a的员工信息
select * from emp where name like '%a%';
#查询员工名中第三个字符为e,第五个字符为a的员工名和工资
select name,salary from emp where name like '__e_a%';
#查询员工名中第二个字符为_的员工名
# 使用\进行转义
select name from emp where name like '_\_%';

#自定义转义字符
select name from emp where name like '_#_%' escape '#';

4.between and

#查询员工编号在100-120之间的员工信息

# 使用and
select * from emp where emp_id>=100 and emp_id<=120;

#使用between and
select * from emp where emp_id between 100 and 120;

5.in

#查询员工的工种编号是A、B、C中的一个的员工名和工种编号

#使用or
select name,job_id from emp where job_id='A' or job_id='B' or job_id='C';

#使用in 
select name,job_id from emp where job_id in('A','B','C');

6.is null和is not null

# 查询没有奖金的员工名和奖金率
select name,com_pct from emp where com_pct is null;
# 查询有奖金的员工名和奖金率
select name,com_pct from emp where com_pct is not null;

7.安全等于<==>

# 查询没有奖金的员工名和奖金率
select name,com_pct from emp where com_pct <==> null;
#查询工资为1000的员工信息
select name,salary from emp where  salary <==> 1000;

20.排序查询

#语法:
# select 查询列表 from 表名 【where筛选条件】 order by 需要排序的字段 【asc|desc】
# desc降序,asc升序
#查询员工信息,要求从高到低排序
select * from emp order by salary desc;
#查询部门编号>=90的员工信息,按入职时间的先后顺序进行排序
select * from emp where dep_id>=90 order by hizedate asc;
#按年薪的高低显示员工的信息和年薪
select *,salary*12 from emp order by salary*12 desc;
#按姓名的长度显示员工的姓名和工资
select length(name),name,salary from order by length(name) desc;
#查询员工信息,先按工资升序排序,再按员工编号降序排序
select * from emp order by salary asc,emp_id desc;

21.常见函数

#调用语法
# select 函数名(参数) 【from表】;

1.length获取参数的字节个数

select length('hello');

2.concat拼接字符串

select concat(last_name,'_',first_name)  from emp;

3.upper(转大写)、lower(转小写)

#将姓转成大写,名转换为小写,然后拼接
select concat(upper(last_name),lower(first_name)) from emp;

4.substr、substring截取字符串

#截取索引字符
select substr('一二三四五六七八九十',7);#sql索引从1开始,七八九十

#截取指定位置的字符
select substr('一二三四五六七八九十',1,3); #一二三
#姓名中首字符大写,其他字符小写然后用'_'拼接
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) from emp;

5.instr计算字符的第一次出现的起始索引

select instr('一二三四五六七八九','四五六'); #4

select instr('一二四五六三四五六四五六七八九','四五六');#3

6.trim去除前后指定字符

select trim('      天天      '); #默认去除前后的空格

select trim('a' from 'aaaaaaa天aaaaa下aaaaaa');#天aaaaa下

7.lpad用指定的字符左填充指定的长度

select lpad('刘德华',10,'*');# *******刘德华

8.rpad用指定的字符右填充指定的长度

select lpad('刘德华',10,'*');# 刘德华*******

9.replace替换

select replace('鸣人和小樱','小樱','雏田');#鸣人和雏田

22.数学函数

1.round四舍五入

select round(1.45);#1
select round(1.65);#2

select round(1.567,2);#小数点后保留两位1.57

2.ceil向上取整

select ceil(1.002); #2

3.floor向下取整

select floor(9.99);#9

4.truncate 截断

select truncate(1.699999,1); #截断,只保留一位小数

5.mod取余

select mod(10,3);#1
#等同于
select 10%3;

23.日期函数

1.now返回当前系统日期+时间

select now();

2.curdate 返回当前系统日期,不包含时间

select curdate();

3.curtime 返回当前时间,不包含日期

select curtime();

4.获取指定部分,年、月、日、时、分、秒

#获取年year
select year(now()); #2020
select year('1998-1-1'); #1998
select year(hiredate) from emp;


#获取月份month
select month(now()); #10月

#获取月份的英文monthname
select monthname(now()); #july

#获取日day
select day(now());  

#获取时
select hour(now());

#获取分
select minute(now());

#获取秒
select second(now());

#获取毫秒
select microsecond(now());

5.str_to_date 将字符通过指定的格式转换为日期

select str_to_date('1999-3-2','%Y-%c-%d');#1999-03-02

#查询入职日期为1992-4-3的员工信息
select * from emp where hiredate = '1992-4-3';

select * from emp where hiredate = str_to_date('4-3 1993','%c-%d %Y');

6.date_format将日期转换成字符

select date_format(now(),'%y年%m月%d日');


# 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
select name,date_format(hiredate,'%m月/%d日 %y年') from emp where com_pct is not null;

24.其他函数

select version(); #查看版本
select database();#查看当前所在的库
select user(); #查看当前用户

25.流程控制函数

1.if函数

select if(10>5,'大','小');

select name,com_pct,if(com_pct is null,'没奖金哈哈哈','有奖金嘻嘻') from emp;

2.case函数

#和switch语句差不多
#查询员工的工资,要求
#部门编号=30,显示的工资为1.1倍
#部门编号=40,显示的工资为1.2倍
#部门编号=50,显示的工资为1.3倍

select salary,dep_id, 
case dep_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end 
from emp;
#查询员工的工资情况
#如果工资>2000,显示A级别
#如果工资>1500,显示B级别
#如果工资>1000,显示C级别
#否则显示D级别

#类似多重if
select salary, 
case
when salary>2000 then 'A'
when salary>1500 then 'B'
when salary>1000 then 'C'
else 'D'
end
from emp;

26.分组函数

# sum求和,avg平均值,max最大值,min最小值,count计算个数
#查询工资的和
select sum(salary) from emp;

#查询工资的平均值
select avg(salary) from emp;

#查询最低工资
select min(salary) from emp;

#查询最高工资
select max(salary) from emp;

#查询工资的总个数
select count(salary) from emp;

27.分组查询

1.分组前的筛选

#语法
select 分组函数,列(要求出现在group by的后面)
from 表名
【where 筛选条件】
group by 分组的列表
【order by 子句】
#查询每个工种的最高工资
select max(salary),job_id
from emp
group by job_id;
#查询每个位置上的部门个数
select count(*),location_id
from dep
group by location_id;
#查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary),dep_id
from emp
where email like '%a%'
group by dep_id;
#查询有奖金的每个领导下员工的最高工资
select max(salary),manager_id
from emp
where com_pct is not null
group by manager_id;

2.分组后的筛选

#查询那个部门的员工个数>2
select count(*),dep_id
from emp
group by dep_id
having count(*)>2;
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select max(salary),job_id
from emp
where com_pct is not null
group by jpb_id
having max(salary)>12000;
#查询领导编号>102的每个领导手下的最低工资>5000的领导编号是那个,以及其最低工资

select min(salary),manager_id
from emp
where manager_id>102
group by manager_id
having min(salary)>5000;
#按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些

select count(*),length(last_name)
from emp
group by length(last_name)
having count(*)>5;
#查询每个部门每个工种的员工的平均工资
select avg(salary),dep_id,job_id
from emp
group by dep_id,job_id;

3.分组中添加排序

#查询每个部门每个工种的员工的平均工资
select avg(salary),dep_id,job_id
from emp
group by dep_id,job_id
order by avg(salary) desc;
#查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
select avg(salary),dep_id,job_id
from emp
where dep_id is not null
group by dep_id,job_id
order by avg(salary) desc;

28.连接查询(多表查询)

1.等值连接查询

#查询女生所对应的男生
select name,boyname
from boys,beauty
where beauty.boyfriend_id=boys.id;
#查询员工名和对应的部门名
select last_name,dep_name
from emp,dep
where emp.dep_id=dep.dep_id;
#查询有奖金的员工名和部门名
select last_name,dep_name,com_pct
from emp,dep
where emp.dep_id=dep.dep_id
and emp.com_pct is not null;
#查询城市名中第二个字符为o的部门和城市名
select dep_name,city
from dep,locations
where dep.location_id = locations.location_id
and city like '_o%';
#查询每个城市的部门个数
select count(*),city
from dep,locations
where dep.location_id=locations_id
group by city;
#查询有奖金的每个部门的部门名称和部门的领导编号和该部门的最低工资
select dep_name,dep.manager_id,min(salary)
from dep,emp
where dep.dep_id = emp.dep_id
and com_pct is not null
group by dep_name,dep.manager_id;
#查询每个工种的工种名和员工的个数,并且按员工个数排序
select job_title,count(*)
from emp,jobs
where emp.job_id=jobs.job_id
group by job_title
order by count(*) desc;
#查询员工名、部门名和所在的城市
select name,dep_name,city
from emp,dep.locations
where emp.dep_id=dep.dep_id
and dep.location_id=locations.location_id

2.非等值连接查询

#查询员工的工资和工资级别
select salary,grade_level
from emp e,job_gra g
where salary between g.low_sal and g.hig_sal;

3.自连接

#一张表拆分成两张
#查询员工名和上级的名称
select e.emp_id,e.name,m.emp_id,m.name 
from emp e,emp m
where e.manger_id = m.emp_id

29.sql99语法

select 查询列表
#之前使用逗号进行连接表1,表2
from 表1 【连接类型】 join 表2 
on 连接条件 #on就是之前使用的where
【where 筛选条件】 #之前使用and

连接类型:
内连接:inner
外连接:
	左外:left [outer]
	右外:right [outer]
	全外:full [outer]
交叉连接:cross
A表          
 
  id   name  
 
  1  小王
 
  2  小李
 
  3  小刘
 
  B表
 
  id  A_id  job
 
  1  2    老师
 
  2  4    程序员

1.左连接:(左边的表不加限制)

//left join (左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。


select a.name,b.job from A a  left join B b on a.id=b.A_id
 
  三条记录
 
  小王  null
 
  小李  老师
 
  小刘  null

2.右连接:(右边的表不加限制)

//right join (右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。

select a.name,b.job from A a  right join B b on a.id=b.A_id
 
  两条记录
 
  小李  老师
 
  null  程序员

3.内连接(等值连接):(只有2张表匹配的行才能显示)

//inner join (等值连接或者叫内连接):只返回两个表中连接字段相等的行。

select a.name,b.job from A a  inner join B b on a.id=b.A_id
 
  只能得到一条记录
 
  小李  老师

4.全外连接: (左右2张表都不加限制)

select a.name,b.job from A a  full join B b on a.id=b.A_id
 
  四条数据
 
  小王  null
 
  小李  老师
 
  小刘  null
 
  null  程序员

30.子查询

1.单行单列子查询

#查询谁的工资比Abel高
select * from emp
where salary>(
		select salary 
  	from emp
  	where name = "Abel"
)

2.多行子查询

#返回location_id是1400或1700的部门中的所有员工姓名

#1.查询location_id是1400或1700的部门编号
select distinct dep_id
from dep
where location_id in(1400,1700);

#2.查询员工姓名,要求部门号是1列表中的某一个
select name from emp
where dep_id in(
	select distinct dep_id
	from dep
	where location_id in(1400,1700) 
);

3.select子查询

#查询每个部门的员工个数
select d.*,(
	select count(*)
  from emp
  where emp.dep_id=dep.dep_id
) 个数
from dep;

31.分页查询

#语法
select 查询列表 from 表1,表2
where 条件 group by 分组字段
order by 排序
limit 起始索引,要显示的条数;
#查询前五条员工的信息
select * from emp limit 0,5;

#查询第11条到第25条
select * from emp limit 10,15;


#查询有奖金的员工信息,并且工资较高的前10名显示出来
select * from emp where com_pct is not null
order by salary desc
limit 0,10;
#公式 page:要显示的页数  size:每页的条目数
select 查询列表 from 表
limit (page-1)*size,size

32.Union联合查询

语法:
查询语句1
union
查询语句2
union
...

注意,查询多条查询语句的时候,语句列数和顺序需要一致
union默认去重,union all 不去重
#查询部门编号>90或者邮箱包含a的员工信息
select * from emp where email like '%a%' or dep_id>90;

#使用union
select * from emp  where email like '%a%'
union
select * from emp where dep_id>90;
#查询中国用户中男性的信息以及外国用户中男性的用户信息
selcect id,cname,csex from t_ca where csex='男'
union
select t_id,tName,tGender from t_ua where tGender='male';

33.插入语句

#语法
insert into 表名或者列名 values (值1,值2....);
#向beauty插入
insert into beauty (id,name) values (1,"名字");
#语法
insert into 表名 set 列名=值,列名=值,...
insert into beauty
set id=1,name='名字';

34.更新(修改)语句

#语法
update 表名 set 列=新值,列=新值
where 筛选条件
#修改beauty表中姓唐的用户的电话为138

update beauty set phone = '138'
where name like '%唐%';
#修改boys表中的id为2的名称为张飞,魅力值为10

update boys set boyname='张飞',usercp=10
where id=2;
#修改多表
#sql92语法
update 表1,表2
set 列=新值,....
where 连接条件
and 筛选条件;

#sql99语法
update 表1 
inner|left|right join 表2
on 连接条件
set 列=新值,....
where 筛选条件;
#修改小王的女朋友的手机号为114

update boys,beauty
set beauty.phone='114'
where  boys.id=beauty.id
and boys.boyName='小王';
#修改没有男朋友的女生的男朋友编号都为2

update boys,beauty
set beauty.boyfriend_id=2
where  boys.id=beauty.id
and  boys.id is null;

35.删除语句

#语法
#单表删除
delete from 表名 where 筛选条件

#删除整个表
truncate table 表名;
#删除手机号以9结尾的女生信息

delete from beauty where phone like '%9';
#多表删除
#sql92语法
delete 表1,表2 from 表1,表2
where 连接条件
and 筛选条件; 


#sql99语法
delete 表1,表2
from 表1
inner|left|right join 表2 on 连接条件
where 筛选条件;
#删除小王的女朋友信息
delete b 
from beauty b,boys bo
where b.boyfriend_id=bo.id
and bo.boyName='小王';
#删除小李的信息以及他女朋友的信息

delete b,bo
from beauty b,boys bo
where b.boyfriend_id=bo.id
and bo.boyName='小李';

36.创建数据库

#语法
create database 库名;
#创建books库
create dabase books;

37.修改库的字符集

alter database books character set gbk;

38.删除库

drop databse books;

39.表的创建

create table 表名(
	列名 列的类型【(长度) 约束】,
  列名 列的类型【(长度) 约束】,
  列名 列的类型【(长度) 约束】,
  列名 列的类型【(长度) 约束】
);

40.表的修改

#修改列名
alter table book change column 旧列名 新列名 数据类型;
alter table book change column id uid int;

#修改列的类型或者约束
alter table book modify column 列名 数据类型;
alter table book modify column cid int;

#添加新列
alter table 表名 add column 新列名 数据类型;
alter table user add column cid int;

#删除列 
alter table 表名 drop column 列名;
alter table user drop column cid;

#修改表名
alter table 旧表名 rename to 新表名;
alter table user rename to cuser;

41.表的删除

#删除表
drop table 表名;
drop table user;

42.表的复制

#仅复制表的结构,不复制数据
create table 自定义的表名 like 要复制的表名
create table copy like user


#复制表的结构+数据
create table 自定义的表名
select * from 要复制的表名;

create table copy
select * from user;


#只复制部分数据
create table 表名
select 列名,列名...
from 要复制的表
where 列名='值';


create table copy
select id,name
from user
where nation='中国';


#仅复制某些字段
create table 表名
select 列名,列名...
from 要复制的表
where 列名='值';

create table copy
select id,name
from user
where 1=2; //创造不可能的条件,实现

43.常见约束

1.非空约束

#保证该字段不能为空
not null

2.默认约束

#保证该字段有默认值
default

3.主键约束

#保证该字段的值具有唯一性,并且是非空的
primary key

4.唯一约束

#保证该字段的值具有唯一性,可以为空
unique

5.检查约束

#检查约束,mysql不支持
check

6.外键约束

#用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
foreign key

7.约束的分类

#列级约束,表级约束

create table 表名(
	字段名 字段类型 列级约束,
  字段名 字段类型,
  表级约束
)


#列级约束:六大约束都支持,但外键约束没效果

#表级约束:除了非空、默认其他都支持

44.创建表时添加约束

#添加列级约束
create table stu(
		id int primary key,#主键约束
  	stuName varchar(20) not null,#非空约束
  	phone  int unique, #唯一约束
  	age int default 18,#默认约束
  
  
  	#majorid int foreign key references major(id) #外键约束
  	#majorid int references major(id) #外键约束
  	
);


create table major(
	id int primary key,
  majorName varchar(20)
);

45.创建表时添加表级约束

#语法
【constraint 约束名】 约束类型(字段名)
#添加表级约束

create table stu(
	id int,
  stuname varchar(20),
  phone int,
  age int,
  majorid int,
  
  pirmary key(id),#主键
  unique (phone,其他字段),#唯一键
  foreign key(majorid) references major(id) #外键
  
);


create table major(
	id int primary key,
  majorName varchar(20)
);

46.创建约束通用写法

create table stu(
		id int primary key,#主键约束
  	stuName varchar(20) not null,#非空约束
  	phone  int unique, #唯一约束
  	age int default 18,#默认约束
  	majorid int,
		foreign key(majorid) references major(id) #外键
  
);
  
  create table major(
	id int primary key,
  majorName varchar(20)
);

47.修改表时添加约束

#语法
#添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

#添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

create table stu(
	id int,
  stuname varchar(20),
  phone int,
  age int,
  majorid int
  );
  
    create table major(
	id int primary key,
  majorName varchar(20)
);
  
  #添加非空约束
  alter table stu modify column stuname varchar(20) not null;
  
  #添加默认约束
  alter table stu modify column age  int default 18;
  
  #添加主键约束
  #列级约束
  alter table stu mofify column id int primary key;
  #表级约束
  alter table add primary key(id);
  
  #添加唯一键
  #列级约束
  alter table stu mofify column phone int unique;
  #表级约束
  alter table add primary unique(phone);
  
  #添加外键
  alter table stu add foreign key(majorid) references major(id);

48.修改表时删除约束

create table stu(
	id int,
  stuname varchar(20),
  phone int,
  age int,
  majorid int
  );
  
    create table major(
	id int primary key,
  majorName varchar(20)
);


#删除非空约束
alter table stu modify column stuname varchar(20) null;

#删除默认约束
alter table stu modify column age int;

#删除主键
alter table stu modify column id int;
alter table stu drop primary key;

#删除唯一键
alter table stu drop index 约束名或者默认字段名;

#删除外键
alter table stu drop foreign key 外键名或者默认字段名;

49.标识列(自增长列)

#创建表时设置标识列

create table stu(
	id int primary key auto_increment,#auto_increment 自动增长
  name varchar(20)
);
#修改表时设置标识列
alter table	 stu mofify column id int primary key auto_increment;

#修改表时删除标识列
alter table stu modify column id int;

50.事务

1.事务的基本使用

#关闭自动提交事务
set autocomit=0;

#编写事务
update account set balance = 500 where username='小王';
update account set balance = 1500 where username='小李';

#提交事务
commit;
#回滚事务
rollback;

51.隔离级别

#设置隔离级别
select @@tx_isolation;

#设置当前mysql连接的隔离级别
set	 transaction isolation level read committed;

#设置数据库系统的全局的隔离级别
set session|global transaction isolation level read committed;
#事务的隔离级别
read uncommitted:可以出现脏读,幻读,不可重复读
read committed:避免脏读,可以出现幻读和不可重复读
repetable read:避免脏读,幻读,可以出现幻读
serializable: 避免脏读、幻读、幻读

mysql默认repetable read级别

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!