第一部分：MySQL超强实用语句123条（纯代码版）
一：查看数据库和表
二：限定查询
三：排序
四：多表查询
五：内连接和外连接
六：聚合函数和分组统计
七：子查询
八：分页查询
九：常用函数
十：更新操作
十一：表和库的管理
十二：约束
十三：用户和权限管理
十四：事务管理
一：查看数据库和表
1.登录MySQL
mysql -u 用户名 -p 密码 -h 数据库服务器地址
2.查看当前所有数据库
show databases;
3.使用指定数据库
use 数据库名;
4.查看当前数据库中所有数据表
show tables;
5.显示当前操作数据库
select database();
6.显示当前登陆用户
select user();
7.导入数据库脚本文件
source D:\init.sql;
8.查看表结构
desc 表名;
9.查看表中所有数据
select * from 表名;
10.查询列加别名
select ename 姓名,job 职位,hiredate 入职时间 from emp;
11.查询列加别名，别名有空格用双引号
select empno,ename,sal "your salary" from emp;
12.为查询结果添加字符串连接：concat
select concat('编号为',empno,'的雇员,姓名为',ename,',职位为',job) from emp;
13.查询雇员姓名和年薪：使用四则运算
select ename 雇员姓名,(sal+ifnull(comm,0))*12 年薪 from emp;
14.查询所有职位：distinct去除重复列
select distinct job from emp;
二：限定查询
15.查询工资大于1500的雇员信息
select * from emp
where sal>1500;
16.查询雇员编号不是7369的雇员信息
select * from emp
where empno!=7369;
17.查询姓名是smith的雇员编号、姓名、工资、入职时间
select empno,ename,sal,hiredate
from emp
where ename='smith';
18.查询没有奖金的雇员信息
select * from emp
where comm is null;
19.查询基本工资大于1000，并且可以获取奖金的雇员姓名、工资、奖金
select ename,sal,comm from emp
where sal>1000 and comm is not null;
20.查询从事销售工作，或工资大于等于2000的雇员信息
select * from emp
where job='saleman' or sal>=2000;
21.查询从事非销售工作，并且工资不小于1500的雇员的编号、姓名、职位、工资、入职时间
select empno,ename,job,sal,hiredate
from emp
where job!='saleman' and sal>=1500;
select empno,ename,job,sal,hiredate
from emp
where (not job='saleman') and (not sal<1500);
22.查询基本工资大于1500，但小于3000的雇员信息
select * from emp
where sal>1500 and sal<3000;
select * from emp
where sal between 1500 and 3000;
23.查询1981年入职的雇员编号、姓名、入职时间、所在部门编号
select empno,ename,hiredate,deptno
from emp
where hiredate between '1981-1-1' and '1981-12-31';
24.查询编号为7369、7499、7788的雇员信息
select * from emp
where empno=7369 or empno=7499 or empno=7788;
select * from emp
where empno in (7369,7499,7788);
25.查询姓名为smith、allen、king的雇员编号、姓名、入职时间
select empno,ename,hiredate
from emp
where ename in ('smith','allen','king');
26.查询雇员姓名以S开头的雇员信息
select * from emp
where ename like 'S%';
27.查询雇员姓名中包含M的雇员信息
select * from emp
where ename like '%M%';
28.查询从事销售工作，并且姓名长度为4个字符的雇员信息
select * from emp
where job='saleman' and ename like '____';
29.查询1981年入职的雇员编号、姓名、入职时间、所在部门编号
select empno,ename,hiredate,deptno
from emp
where hiredate like '1981%';
三：排序
30.查询所有雇员信息，按工资由低到高排序
select * from emp
order by sal;
31.查询部门10的雇员信息，按工资由高到低排序，如果工资相同，则按入职时间由早到晚排序
select * from emp
where deptno=10
order by sal desc,hiredate;
32.查询姓名不包含M，且工资大于1500或者年薪不低于30000的雇员编号、姓名、年薪，按雇员姓名降序排列
select empno,ename,sal,(sal+ifnull(comm,0))*12 income
from emp
where ename not like '%M%'
and (sal>1500 or (sal+ifnull(comm,0))*12>=30000)
order by ename desc;
四：多表查询
33.查询雇员编号、雇员姓名、工资、所在部门名称及位置
select empno,ename,sal,dname,loc
from emp e,dept d
where e.deptno=d.deptno;
34.查询雇员姓名、工资、入职时间、所在部门编号、部门名称
select e.ename,e.sal,e.hiredate,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
35.查询雇员姓名、雇员工资、领导姓名、领导工资
select e.ename 雇员姓名,e.sal 雇员工资,m.ename 领导姓名,m.sal 领导工资
from emp e,emp m
where e.mgr=m.empno;
36.查询雇员姓名、雇员工资、部门名称、领导姓名、领导工资
select e.ename 雇员姓名,e.sal 雇员工资,d.dname 部门名称,m.ename 领导姓名,m.sal 领导工资
from emp e,dept d,emp m
where e.deptno=d.deptno and e.mgr=m.empno;
37.查询雇员姓名、雇员工资、部门名称、工资所在等级
select e.ename 雇员姓名,e.sal 雇员工资,d.dname 部门名称,s.grade 工资等级
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
38.查询雇员姓名、雇员工资、部门名称、雇员工资等级、领导姓名、领导工资、领导工资等级
select e.ename 雇员姓名,e.sal 雇员工资,d.dname 部门名称,s.grade 雇员工资等级,m.ename 领导姓名,m.sal 领导工资,sm.grade 领导工资等级
from emp e,dept d,salgrade s,salgrade sm
where e.deptno=d.deptno
and e.sal between s.losal and s.hisal
and e.mgr=m.empno
and m.sal between sm.losal and sm.hisal;
五：内连接和外连接
39.查询工资大于1500的雇员姓名、工资、部门名称、领导姓名
select e.ename,e.sal,d.dname,m.ename
from emp e
inner join dept d on e.deptno=d.deptno
inner join emp m on e.mgr=m.empno
where e.sal>1500
select e.ename,e.sal,d.dname,m.ename
from emp e,dept d,emp m
where e.deptno=d.deptno
and e.mgr=m.empno
and e.sal>1500
40.查询雇员姓名、工资、领导姓名、领导工资
select e.ename,e.sal,m.name,m.sal
from emp e
left join emp m on e.mgr=m.empno;
select e.ename,e.sal,m.ename,m.sal
from emp m
right join emp e on e.mgr=m.empno;
41.查询部门编号、部门名称、部门位置、部门中雇员姓名、工资
select d.deptno,d.dname,d.loc,e.ename,e.sal
from dept d
left join emp e on d.deptno=e.deptno
order by d.deptno;
六：聚合函数和分组统计
42.查询部门30的总人数
select count(empno) 总人数
from emp
where deptno=30;
43.查询部门30的最高工资、最低工资、平均工资
select max(sal),min(sal),round(avg(sal),2)
from emp
where deptno=30;
44.查询每个部门的平均工资
select deptno 部门编号,avg(sal) 平均工资
from emp
group by deptno;
select d.dname 部门名称，avg(sal) 平均工资
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
45.查询部门的名称及每个部门的员工数量
select d.dname 部门名称,count(e.empno) 员工数量
from dept d
left join emp e on d.deptno=e.deptno
group by d.dname;
46.查询平均工资大于2000的部门的编号和平均工资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
47.查询出非销售人员的职位名称，以及从事同一工作的雇员的月工资总和，并且要满足工资总和大于5000，查询的结果按月工资总和的升序排列
select job,sum(sal) sum
from emp
where job!='saleman'
group by job
having sum(sal)>5000
order by sum;
七：子查询
48.查询工资比7566高雇员信息
select *
from emp
where sal>(select sal from emp where empno=7566);
49.查询雇员的编号、姓名、部门名称
select empno,ename,(select dname from dept where deptno=e.deptno)
from emp e;
50.查询工资比7654高，同时又与7900从事相同工作的雇员信息
select *
from emp
where sal>(
select sal from emp where empno=7654
) and job=(
select job from emp where empno=7900
);
51.查询工资最低的雇员的姓名、工作、工资
select ename,job,sal
from emp
where sal=(select min(sal) from emp);
52.查询工资高于公司平均工资的雇员信息
select *
from emp
where sal>(select avg(sal) from emp);
53.查询每个部门的编号和最低工资，要求最低工资大于等于部门30的最低工资
select deptno,min(sal)
from emp
group by deptno
having min(sal)>=(
select min(sal) from emp where deptno=30
);
54.查询部门的名称、部门的员工数、部门的平均工资、部门的最低收入雇员的姓名
select
(select dname from dept where deptno=e.deptno) dname,
count(empno),
avg(sal),
(select ename from emp where sal=min(e.sal)) ename
from emp e
group by deptno;
55.查询平均工资最低的工作及平均工资
select job,avg(sal)
from emp
group by job
having avg(sal)=(
select min(t.avg) from (select avg(sal) avg from emp group by job) t
);
56.查询所在部门编号大于等于20的雇员信息
select * from emp
where deptno>=20;
select * from emp
where deptno in (
select deptno from dept where deptno>=20
);
57.查询工资与部门20中的任意员工相同的雇员信息
select * from emp
where sal in (
select sal from emp where deptno=20
);
58.在所在从事销售工作的雇员中找出工资大于1500的员工
select *
from (select * from emp where job='saleman') t
where t.sal>1500;
八：分页查询
59.查询工资的前3名
select * from emp
order by sal desc
limit 0,3;
select * from emp
order by sal desc
limit 3;
60.查询工资大于1000的第4到8个用户
select * from emp where sal>1000 limit 3,5;
61.查询工资最低的用户
select * from emp order by sal limit 1;
62.每页显示4条(pageSize每页大小),显示第3页的内容(pageIndex页码)
select * from emp
limit (pageIndex-1)*pageSize,pageSize;
九：常用函数
一：字符串函数
63.拼接字符串
select concat('编号为',empno,'的员工，姓名为',ename) from emp;
64.将字符串变为小写
select lower('Hello') from dual
65.将字符串变为大写
select upper('Hello') from dual;
66.获取字符串长度
select length('hello') from dual;
67.将字符串反转
select reverse('hello') from dual;
68.去除字符串两边的空格
select trim(' hello ') from dual;
69.将字符串s中的s1替换为s2
select replace('hello world','o','xx') from dual
70.将字符串s重复n次后返回
select repeat('hello',3) from dual
71.在字符串s的左边使用s1进行填充，直至长度为len
select lpad('hello',8,'x') from dual;
72.字符串s的右边使用s1进行填充，直至长度为len
select rpad('hello',8,'x') from dual;
73.从第i个位置开始对字符串s进行截取，截取len个
select substr('hello',2,3) from dual;
二：数值函数
74.返回大于n的最小整数
select ceil(10.1) from dual;
75.返回小于n的最大整数
select floor(10.1) from dual;
76.将n保留y位小数，四舍五入
select round(3.1415.3) from dual;
77.将n保留y位小数，不四舍五入
select truncate(3.1415,3) from dual;
78.返回0到1的随机数
select rand() from dual;
三：日期和时间函数
79.返回当前日期时间
select now() from dual;
80.返回当前日期
select curdate() from dual;
81.返回当前时间
select curtime from dual;
82.返回日期中的年
select year('2018-2-14') from dual;
83.返回日期中的月
select month('2018-2-14') from dual;
84.返回日期中的日
select day('2018-2-14') from dual;
85.返回两个日期时间之间相隔的整数
select timestampdiff(day,'1993-9-23','2018-11-22') from dual;
86.格式化日期
select date_format(now(),'%Y年%m月%d日 %H:%i:%s') from dual;
四：流程控制函数
87.if(f,v1,v2)如果f为真，则返回v1，否则返回v2
select if(5>2,'yes','no') from dual;
88.ifnull(v1,v2)如果v2不为null，则返回v1，否则返回v2
select ifnull(null,'0') from dual
89.case when f1 then v1 when f2 then v2…else v end 如果f1为真，则返回v1；如果f2为真，则返回v2…否则返回v
select case when 5<2 then 'one' when 6>4 then 'two'
else 'three' end
from dual;
五：系统信息函数
90.返回当前操作的数据库
select database();
91.返回当前登陆的用户
select user();
92.返回MySQL服务器的版本
select version();
十：增删改操作
93.insert
insert into emp(empno,ename,job,hiredate,sal,deptno)
values(9527,'唐伯虎','画家',now(),6666,50);
94.delete
删除市场部所有工资高于5000的员工
delete from emp
where deptno=(select deptno from dept where dname='市场部')
and sal>5000;
95.update
将smith的job设置为manager，sal设置为8888，comm设置为666
update emp
set
job='manager',sal=8888,comm=666
where ename='smith';
十一：表和库的管理
96.创建数据表
create table t_student
(
id int primary key auto_increment,
name varchar(10) not null,
age int,
sex varchar(8) not null default '女',
address varchar(100),
height double,
birthday date
)charset=utf8;
97.添加数据
insert into t_student (name,age,sex,birthday,height)
values ('范婷婷',18,'女','1998-12-4',170.6);
insert into t_student(name,age,sex)
values(null,10,'男');
insert into t_student
values(null,'程瑞',19,'男','南京',176.6,now())
三.修改数据表
98.添加列
alter table t_student add weight double;
99.修改列类型
alter table t_student modify name varchar(250);
100.修改列名
alter table t_student change sex gender varchar(8);
101.修改表名
alter table t_student rename student;
rename table student to t_student;
102.删除表
drop table if exists user;
103.截断表
truncate table emp;
104.创建库
create database if not exists shop charset utf8;
105.删除库
drop database if exists shop;
十二：约束
106.在创建表时添加约束
create table student
(
id int primary key,
name varchar(10) not null,
age int check(age between 1 and 120),
sex varchar(8) not null check(sex in ('male','female')),
IDCard varchar(18) unique,
class_id int,
foreign key (class_id) references class (c_id)
)charset=utf8;
create table class
(
c_id int primary key,
c_name varchar(20) not null,
c_info varchar(200)
)charset=utf8;
107.查看表所有信息包括约束
show create table t_student;
108.为约束指定名称
create table student
(
id int,
name varchar(10) not null,
age int,
sex varchar(8),
IDCard varchar(18),
class_id int,
constraint pk_id primary key (id),
constraint ck_age check(age between 1 and 120),
constraint ck_sex check(sex in ('male','female')),
constraint uq_IDCard unique (IDCard),
constraint fk_class_id foreighn key (class_id) references class(c_id)
)charset=utf8;
109.在创建表后再添加约束
create table student
(
id int,
name varchar() not null,
age int,
sex varchar(8),
IDCard varchar(18),
class_id int
);
alter table student add constraint pk_id primary key (id);
alter table student add constraint ck_age check(age between 1 and 120);
alter table student add constraint ck_sex check(sex in ('male','female'));
alter table student add constraint uq_IDCard unique(IDCard);
alter table student add constraint fk_class_id foreign key (class_id)
references class (c_id);
二：删除约束
110.删除主键约束
alter table 表名 drop primary key
111.删除外键约束
alter table 表名 drop foreign key 约束名称
112.删除唯一约束
alter table 表名 drop index 约束名称
113.删除非空约束
alter table 表名 modify 列名 数据类型 null
114：常见五种约束：主键，非空，检查，唯一，外键
create table student
(
id int primary key,
name varchar(10) not null,
age int check(age between 1 and 120),
sex varchar(8) not null check(sex in ('male','female')),
IDCard varchar(18) unique,
class_id int,
foreign key (class_id) references class (c_id) on delete cascade
)charset=utf8;
十三：用户和权限管理
115：创建用户并授予权限
grant select,update
on test.emp
to tom@localhost identified
by '123';
二：查看权限
116.查看自己的权限
show grants;
117.查看其他人的权限
show grants for 用户名@来源地址;
三：撤销权限
118.撤销权限
revoke delete
on shop.user
from mike@'%';
119：删除用户
use mysql;
delete from user where user='用户名';
flush privileges;
十四：事务管理
120.查看autocommint模式:
show variables like 'autocommit'
121.关闭自动提交:
set autocommit=off 或 set autocommit=0
122.手动提交事务:
commit
123.手动回滚事务:
rollback
