MySQL之DDL常用命令
create database study1;
create database study2 character set gbk;
show databases;
show create database study2;
alter database study2 character set utf8;
show create database study2;
drop database study2;
use study1;
select database();
create table employee(
id int,
name varchar(20),
sex char(1),
birthday date,
job varchar(50),
salary double
);
show tables;
desc employee;
alter table employee add address varchar(100);
alter table employee modify job varchar(30);
alter table employee drop address;
rename table employee to user;
show create table user;
alter table user character set gbk;
alter table user change name username varchar(20);
drop table user;
MySQL之DML常用命令
delete只是删除表中的数据，不删除表，删除后的数据可以恢复。 truncate删除是把表直接drop掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比delete快。
use study1;
create table emp(
id int,
name varchar(100),
sex char(6),
birthday date,
salary float(10,2)
);
insert into emp values (1,'andy','male','1995-05-15',10000);
insert into emp (id,name,sex,birthday,salary) values (2,'paul','male','1996-06-15',15000);
insert into emp (id,name,sex,birthday,salary) values (3,'lucy','female','1998-07-11',8000);
insert into emp (id,name,sex,birthday,salary) values (4,'james','male','1985-08-10',50000),
(5,'marry','female','1987-06-15',30000),
(6,'carter','male','1995-05-15',10000);
select * from emp;
update emp set salary=80000 where name='james';
delete from emp where name='lucy';
delete from emp;
truncate table emp;
MySQL之DQL常用命令
create database monkey1024;
use monkey1024;
create table dept(
deptno int(2),
dname varchar(14),
loc varchar(13)
);
delete from dept;
insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');
select * from dept;
drop table dept;
create table emp(
empno int(4),
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal double(7,2),
comm double(7,2),
deptno int(2)
);
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
select * from emp;
create table salgrade(
grade int(11),
hisal int(11),
losal int(11)
);
insert into salgrade values(1,1200,700);
insert into salgrade values(2,1400,1201);
insert into salgrade values(3,2000,1401);
insert into salgrade values(4,3000,2001);
insert into salgrade values(5,9999,3001);
select ename from emp;
select empno,ename from emp;
select * from emp;
select empno,ename,sal*12 from emp;
select empno,ename,sal*12 as yearsal from emp;
select empno,ename,sal*12 yearsal from emp;
select empno,ename,sal*12 as '年薪' from emp;
select empno,ename,sal from emp where sal=5000;
select empno,ename,job from emp where job='manager';
select empno,ename,sal from emp where sal<> 5000;
select empno,ename,sal from emp where sal>=1600 and sal<=3000;
select empno,ename,sal from emp where sal between 1600 and 3000;
select empno,ename,comm from emp where comm is null;
select empno,ename,comm from emp where comm is not null;
select empno,ename,job,sal from emp where job='manager' and sal>2500;
select ename,job from emp where job='salesman' or job='manager';
select * from emp where sal>1800 and (deptno=20 or deptno=30);
select ename,job from emp where job in('salesman','manager');
select ename,sal from emp where sal not in (1600,3000);
select ename from emp where ename like 'm%';
select ename from emp where ename like '%n';
select ename from emp where ename like '%o%';
select ename from emp where ename like '_a%';
MySQL排序和处理函数
select ename,sal from emp order by sal;
select ename,job,sal from emp where job='manager' order by sal;
select ename,sal from emp order by sal asc;
select ename,sal from emp order by sal desc;
select ename,job,sal from emp order by job desc,sal desc;
select * from emp order by 6;
select lower(ename) as ename from emp;
select upper(ename) as ename from emp;
select substr(ename,2,1) from emp;
select ename from emp where substr(ename,2,1)='a';
select ename from emp where ename like '_A%';
select ename,length(ename) as nameLength from emp;
select ename,ifnull(comm,0) from emp;
select sal+ifnull(comm,0) from emp;
select ename,(sal+ifnull(comm,100))*12 yearsal from emp;
select ename,sal,job,
(case job
when 'manager' then sal*1.1
when 'salesman' then sal*1.5
else sal
end) as newsal
from emp;
select * from emp where job=trim(' manager ');
select round(sal,1) from emp;
select rand(),sal from emp;
select round(rand()*100),sal from emp;
select ename,hiredate from emp where hiredate='1981-12-03';
select ename,hiredate from emp where hiredate = str_to_date('02-20-1981','%m-%d-%Y');
select ename,date_format(hiredate,'%m-%d-%Y') hiredate from emp;
MySQL分组和聚合函数
select sum(sal) from emp;
select sum(sal+ifnull(comm,0)) from emp;
select avg(sal) as avgsal from emp;
select max(sal) as maxsal from emp;
select max(hiredate) as lastemp from emp;
select min(sal) as minsal from emp;
select count(*) from emp;
select count(comm) from emp; /*count函数不会统计数据为null的记录*/
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
/*distinct去除重复记录*/
select distinct job from emp;
select count(distinct job) from emp;
select distinct job,deptno from emp;
select sal from emp;
select max(sal) as maxsal from emp;
select max(sal) as maxsal from emp group by job; /*按职位分组，找出最高薪水*/
select job,max(sal) as maxsal from emp group by job;
/*在有group by的DQL询问中，select语句后面只能跟聚合函数+参与分组的字段*/
select ename,job,max(sal) as maxsal from emp group by job;
select job,max(sal) as maxsal from emp group by job order by maxsal;
select deptno,avg(sal) as avgsal from emp group by deptno;
/*group by后面可以写多个字段，数据库会分别对这些字段进行分组*/
select deptno,job,max(sal) as maxsal from emp group by deptno,job;
select job,max(sal) as maxsal from emp where job <> 'manager' group by job;
/*下面写法不正确，where后面不能直接使用聚合函数*/
select job,avg(sal) as avgsal from emp where avg(sal)>2000 group by job;
/*where和having的区别
where和having都是为了完成数据的过滤，它们后面都是添加条件；
where是在group by之前完成过滤；
having是在group by之后完成过滤*/
select job,avg(sal) from emp group by job having avg(sal)>2000;
select xx from xx where xx group by xx having xx order by xx;
/*from:将硬盘上的表文件加载到内存
where:将符合条件的数据筛选出来。生成一张新的临时表
group by:根据列中的数据种类，将当前临时表划分成若干个新的临时表
having:可以过滤掉group by生成的不符合条件的临时表
select:对当前临时表进行整列读取
order by：对select生成的临时表，进行重新排序，生成新的临时表
limit:对最终生成的临时表的数据行，进行截取*/
MySQL之连接查询
use monkey1024;
# emp和dept两张表中数量的乘积
select e.ename,d.dname from emp e,dept d;
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
select d.dname,e.ename from emp e join dept d on e.deptno=d.deptno;
# 内连接查询出的数据是两张表的交集，即上图中C所表示的部分
select d.dname,e.ename from emp inner join dept d on e.deptno=d.deptno;
# inner可以省略
select d.dname,e.ename from emp e join dept d on e.deptno = d.deptno;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
select e.name,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
select a.ename empname,b.ename leadername from emp a join emp b on a.mgr=b.empno; # 自连接
/*左外连接包含左边表的全部行（不管右边的表是否存在与它匹配的行），和
右边表中全部匹配的行*/
select e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno;
# outer可以省略
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno;
/*右外连接包含右边表的全部行（不管左边的表中是否存在与它匹配的行），和
左边表中全部匹配的行*/
select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;
#outer可以省略
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
# 右外连接都可以改写成左外连接，左外连接都可改写成右外连接
select a.ename empname,b.ename leadername from emp a left join emp b on a.mgr=b.empno;
select e.ename,d.dname,e.sal,s.grade
from emp e join dept d on e.deptno=d.deptno
join salgrade s on e.sal between s.losal and s.hisal;
MySQL查询其他内容
# select语句嵌套select语句被称为子查询
select ename,sal from emp where sal>(select avg(sal) from emp);
select t.deptno,t.avgsal,s.grade from
salgrade s join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.avgsal
between s.losal and s.hisal order by t.deptno;
# 使用union合并结果,合并结果集时查询字段的个数必须一致
select empno,ename,job from emp where job='manager' union
select empno,ename,job from emp where job='salesman';
/*limit
limit起始下标m，长度n，
m:记录开始的index，默认从0开始，表示第一条记录；
n:指从第m+1条开始，取n条；*/
select * from emp limit 0,5;
select * from emp limit 5;
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit 2,7;
MySQL存储引擎简介
什么是存储引擎？
存储引擎是通过采用不同的技术将数据存储在文件或内存中，不同的技术有不同的存储机制，其功能和能力也不同，通过选择不同的技术，可以获得特殊的速度或功能，从而提高应用的性能，存储引擎是MySQL特有的
常用的存储引擎
MyIsam
节省数据库空间，当数据读远大于修改时，可以使用该存储引擎
InnoDB
支持事务，如果数据修改较多时，可以使用该存储引擎
Memory
存储在内存中，速度快，如果存储非永久性数据时，可以使用该存储引擎
show engines;
create table table_name(
no int)engine=myisam;
alter table table_name engine=innodb;
show create table emp;
事务transaction
什么是事务
通常一个事务对应一个完整的业务，例如银行转账，事务可以保证多个操作原子性，要么全部成功要么全部失败
1001的账户向1002的账户进行转账500元的操作，此时将会执行两条SQL语句
update t_bank set money=500 where account=1001;
update t_bank set money=2500 where account=1002;
如果上面的sql语句第一条执行成功了，但是由于某些原因第二条sql语句执行失败了，此时账户1001上的金额变成了500，而账户1002上的金额是2000，这样就不正确了。 为了能够正常的执行业务，上面两条sql语句要么全部执行成功，要么全部执行失败，因为他们是最小的业务单元，不能再进行拆分了。
当一条sql语句执行结束后，并不会立即修改数据库表中的数据，而是在内存中记录一下，第二条sql语句执行成功后，才会修改数据库表中的数据，如果第二条sql语句执行失败了，则将清空内存中的记录，此时并不会修改数据库表中的数据，要实现这样的功能，就必须使用事务来完成。
事务具有四个特征
原子性：
事务是最小单元，不可再分；
一致性：
事务要求所有的DML语句操作的时候，必须保证同时成功或同时失败；
隔离性：
一个事务不会影响其他事务的执行
持久性：
在事务完成之后，该事务对数据库所作的更改将持久地保存在数据库中，并不会被回滚；
MySQL事务的提交和回滚
MySQL中默认情况下，事务是自动提交的，当执行一条DML语句时，就开启并且自动提交了事务。可以通过下面的语句查看事务是否是自动提交的：
show variables like '%commit%';
如果想要关闭MySQL事务的自动提交，可以使用下面语句手动开启事务：
create table t_bank(
account int(10) primary key,
money int(15)
);
insert into t_bank values (1001,1000),(1002,2000);
select * from t_bank;
start transaction;
update t_bank set money=500 where account=1001;
update t_bank set money=2500 where account=1002;
# 此时事务将会回滚，清空内存中的记录，不会修改数据库中的字段值
rollback;
# 提交事务
commit;
事务的隔离级别
read uncommitted读未提交
事务A和事务B，事务A未提交的数据，事务B可以读取，这里读取到的数据叫做“脏数据”，该级别最低，一般只是理论上存在，数据库的默认隔离级别都高于该级别。
read committed读已提交
事务A和事务B,事务A提交的数据，事务B才可以读取到，换句话说：对方事务提交之后的数据，当前事务才可读取到，可以避免读取脏数据，但是改级别会有不可重复读的问题，事务B读取一条数据，当事务A修改这条数据并提交后，事务B再读取这条数据时，数据发生了变化，即事务B每次读取的数据有可能不一致，这种情况叫做“不可重复读".
repeteable read重复读
MySQL默认的隔离级别是重复读，该级别可以达到”重复读“的效果，但是会有”幻读“的问题，即事务A 读取数据，此时事务B修改了这条数据，但是事务A读取的还是之前的旧数据，这样就出现了幻读。
serializable 串行化
事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待，这样保证了同一个时间点上只有一个事务操作数据库，该级别可以解决“幻读”的问题。但是这种级别一般很少使用，因为吞吐量太低，用户体验不好。
MySQL索引和视图
索引的作用
索引相当于一本字典目录，能够提高数据库的查询效率，表中每一个字段都可添加索引。主键会自动添加索引，在查询时，如果能通过主键查询的尽量使用主键查询，效率高。 MySQL数据库表中的检索方式有两种：
全表扫描（效率低） 例如：select * from emp where ename = ‘KING';如果ename字段没有添加索引的话，就会发生全表扫描。通过索引检索
什么情况下适合添加索引
该字段数据量庞大；该字段很少的DML操作（由于索引也需要维护，DML操作多的话，也影响检索效率）；该字段经常出现在where条件中；
注意：实际开发中会根据项目需求等综合因素来做调整，添加索引并不能保证一定能够提升检索效率，索引添加不当也有可能会导致效率降低。
create index 索引名 on 表名 (列名);
create index dept_dname_index on dept(dname);
# 查看索引
show index from 表名;
show index from dept;
# 删除索引
drop index 索引名 on 表名;
drop index dept_dname_index on dept;
/*视图
视图其实就是一个查询结果，视图的作用可以隐藏表的实现细节*/
create view 视图名称 as 查询语句；
create view e_info as select empno,ename,sal from emp;
# 使用视图
select * from e_info;
# 修改视图
alter view 视图名称 as 查询语句
alter view e_info as select ename,job from emp;
# 删除视图
drop view if exists 视图名称;
drop view if exists e_info;
练习
use monkey1024;
select deptno,max(sal) as maxsal from emp group by deptno;
select e.ename, t.deptno,t.maxsal from emp e join
(select deptno,max(sal) as maxsal from emp group by deptno) t on e.deptno=t.deptno
where t.maxsal = e.sal;
select deptno,avg(sal) as avgSal from emp group by deptno;
select e.ename,t.deptno,e.sal from emp e join
(select deptno,avg(sal) as avgSal from emp group by deptno) t on e.deptno=t.deptno
where e.sal>t.avgSal;
select deptno,avg(sal) as avgSal from emp group by deptno;
select t.deptno,t.avgSal,s.grade from salgrade s join (select deptno,avg(sal) as avgSal from emp group by deptno) t
on t.avgSal between s.losal and s.hisal;
select ename,sal from emp order by sal desc limit 0,1;
select deptno,avg(sal) from emp group by deptno;
select max(t.avgSal) as maxSal from (select deptno,avg(sal) as avgSal from emp group by deptno) t;
select deptno,avg(sal) as avgSal from emp group by deptno
having avgSal = (select max(t.avgSal) as maxSal from (select deptno,avg(sal) as avgSal from emp group by deptno ) t);
select deptno,avg(sal) as avgSal from emp group by deptno order by avgSal desc limit 0,1;
select d.dname, t.deptno,t.avgSal from dept d join
(select deptno,avg(sal) as avgSal from emp group by deptno order by avgSal desc limit 0,1) t on d.deptno=t.deptno;
select deptno,avg(sal) as avgSal from emp group by deptno;
select distinct mgr from emp where mgr is not null;
select max(sal) as maxSal from emp where empno not in(select distinct mgr from emp where mgr is not null);
select e.ename from emp e join (select distinct (mgr) as mgr from emp where mgr is not null) t on e.empno =t.mgr where
e.sal>(select max(sal) as maxSal from emp where empno not in (select distinct (mgr) from emp where mgr is not null));
select e.empno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
select t.grade,count(t.empno) from (select e.empno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.grade;
select a.empno,a.ename,a.hiredate from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate;
select count(e.empno),e.deptno from emp e group by e.deptno having count(e.empno)>=5;
select d.dname from dept d join (select count(e.empno),e.deptno from emp e group by e.deptno having count(e.empno)>=5) t on d.deptno=t.deptno;
select job,min(sal) as minSal from emp group by job having min(sal)>1500;
select job,min(sal) as minSal,count(*) as total from emp group by job having min (sal)>1500;
select deptno from dept where dname='salse';
select e.ename from emp e where e.deptno=(select deptno from dept where dname='sales');
select d.dname,count(e.ename) as total,sum(e.sal) as totalSal from emp e right join dept d on e.deptno=d.deptno group by d.dname;
select d.dname,count(e.ename) as total,sum(e.sal) as totalSal from emp e right join dept d on e.deptno=d.deptno
where d.dname like '%s%' group by d.dname;
面试题
有3个表S(学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩） 问题：
找出没选过“黎明”老师的所有学生姓名。列出2门以上（含2门）不及格学生姓名及平均成绩。即学过1号课程又学过2号课所有学生的姓名。
初始化数据：
drop table if exists sc;
create table sc(
sno varchar(200),
cno varchar(200),
scgrade varchar(200)) default charset=utf8;
drop table if exists c;
create table c(
cno varchar(200),
cname varchar(200),
cteacher varchar(200)) default charset=utf8;
drop table if exists s;
create table s(
sno varchar(200),
sname varchar(200)) default charset=utf8;
desc sc;
insert into c (cno,cname,cteacher) values ('1','语文','张');
insert into c (cno,cname,cteacher) values ('2','政治','王');
insert into c (cno,cname,cteacher) values ('3','英语','李');
insert into c (cno,cname,cteacher) values ('4','数学','赵');
insert into c (cno,cname,cteacher) values ('5','物理','黎明');
insert into s (sno,sname) values ('1','学生1');
insert into s (sno,sname) values ('2','学生2');
insert into s (sno,sname) values ('3','学生3');
insert into s (sno,sname) values ('4','学生4');
insert into sc (sno,cno,scgrade) values ('1','1','40');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '1', '2', '30');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '1', '3', '20');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '1', '4', '80');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '1', '5', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '2', '1', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '2', '2', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '2', '3', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '2', '4', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '2', '5', '40');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '3', '1', '60');
INSERT INTO sc (sno,cno,scgrade) VALUES ( '3', '3', '80');
select * from s;
select cno from c where cteacher='黎明';
select sno from sc where CNO='5';
select sno,sname from s where sno not in (select
sno from sc where cno=(select cno from c where cteacher='黎明'));
select sno from sc where scgrade<60 group by sno having count(*)>=2;
select t2.sn,avg(t1.scgrade) as avgScgrade from
sc t1 join (select sno from sc where scgrade<60 group by sno having count(*)>=2) t2 on t1.sno=t2.sno group by t2.sno;
select s.sname from s join (select t1.sno from (select sno from sc where cno=1) t1 join (select sno from sc where cno=2) t2
on t1.sno=t2.sno) t3 on s.sno=t3.sno;
