mysql大全

mysql普通事项

• 登录账户密码 mysql -uroot -p333

• DB：DateBase 数据库

• DBMS:数据库管理系统

• SQL：结构化查询语言

• 使用desc + 表名是查看整个表的结构，

  或者使用 show columns from 表名;

• 结束一条语句 \c

• 数据库的导入：source sql文件路径

create database bjpowernode;
use database bjpowernode;
source D:\code\mysql\bjpowernode.sql;

• 数据库的导出：退出数据库登录执行以下代码 mysqldump 数据库名字 存放地址 用户名 密码

mysqldump bjpowernode>D:\code\mysql\beifen.sql -uroot -p333

DQL 数据查询语言

select

select EMPNO,ENAME from emp;
select ENAME,SAL*12 as yearsal from EMP;
select ENAME,SAL*12 as '年薪' from EMP;

条件查询

select ENAME,SAL from EMP where sal = 5000;
select EMPNO,ENAME,SAL from emp where ENAME = 'SMITH';
select ENAME,SAL from emp where SAL >= 1100 and SAL <= 3000;
select ENAME,SAL from emp where SAL between 1100 and 3000;
select ENAME,SAL,COMM from emp where comm is not null;
select ENAME,JOB from emp where sal in (800,5000);
select ENAME,JOB from emp where sal not in (800,5000);

• null 不是一个值，不能使用=，智能使用 is null/ is not null
• and 和 or 同时使用，and 优先级更高，建议使用小括号

模糊查询

• 在模糊查询中，必须掌握两个特殊的符号，一个是%，一个是_
• %代表任意多个字符，_代表任意一个字符

//找出名字中有O的
select ename from emp where ename like '%o%';
//找出名字中第二个是O的
select ename from emp where ename like '_a%';
//找出名字中有下划线的
select ename from emp where ename like '%\_%';
//找出名字中最后一个字母是T的
select ename from emp where ename like '%T';

查询并排序

• 默认升序或者自己加asc
• 降序desc
• 多字段排序加逗号,

select ENAME,SAL from emp order by sal desc;
select ENAME,SAL from emp order by sal desc,ENAME asc;
select ENAME,SAL from emp where job = 'SALESMAN' order by sal desc;

NULL

• 有null参加的数学运算结果为NULL，分组函数自动忽略NULL。

select ENAME,(SAL+COMM)*12 as yearsal from emp;
select ENAME,(SAL+ifnull(comm,0))*12 as yearsal from emp;
select count(comm) from emp;
select ifnull(comm,0) from emp;

select ENAME,ifnull(COMM,0) as COMM from emp;

分组函数

• 所有分组函数都是对某一组数据进行操作的。
• 多行处理函数，输入多行输出结果只有一条。
• 单行处理函数，输入几行输出就有几行。
• count()计数，count(*)是表的总行数

select count(sal) from emp;

• sum ()求和

select sum(sal) from emp;

• avg() 平均值

select avg(sal) from emp;

• max() 最大值

select ENAME,max(SAL) from emp;

• min() 最小值

select ENAME,min(SAL) from emp;

• 分组函数不可直接使用在where子句当中

select ename,sal from emp where sal>(select avg(sal) from emp);

• group by 按照某个字段或者某些字段进行分组

​ 1、分组函数一般都会和group by 联合使用，这也是为什么被称为分组函数的原因，并且任何一个分组函数都是在group by语句执行结束后才会执行，当一条sql语句没有group by 时，整张表自成一组。

​ 2、group by 会在 where 执行之后才会执行。

​ 3、当使用group by 时，只能出现两个字段：

​ —参加分组的字段

​ —分组函数的字段

​ 4、多个字段联合分组时，用逗号隔开

select max(sal),job from emp group by job;
select job,avg(sal) from emp group by job;
select max(sal),job,deptno from emp group by deptno,job;

• having 是对分组之后的数据进行再次过滤

select max(sal),job from emp group by job having max(sal)>2000;//效率低下
select max(sal),job from emp where sal>2000 group by job ;
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

去重

• distinct 只能出现在所有字段的最前方，表示后面所有字段联合去重。

select distinct job from emp;
select distinct deptno,job from emp;

连接查询

• 在实际开发中大部分情况下都不是从单张表中查询数据，一般都是多张表联合查询取出最终结果。
• 建议给表起别名 emp a,dept b

内连接

— 两张表是平等的，没有主副之分

• 等值连接

select 
e.ename,d.dname
from 
emp e
join
dept d
on
e.deptno = d.deptno;

• 非等值连接

select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;

• 自连接

​ 一张表当作两张表，自己连接自己

select
e.ename,b.ename as manger
from
emp e
join
emp b
on
e.mgr = b.empno;

外连接

— 主要查询的是主表，当副表中数据没有和主表中的相匹配，以null代替

• 左外连接（左连接），左边是主表（left join）

select
e.ename,b.ename as manger
from
emp e
left join
emp b
on
e.mgr = b.empno;

• 右外连接（右连接），右边是主表（right join）

三张表以上连接

select e.ename as '员工',d.dname,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

子查询

• 可以先生成子表，并重新命名，然后在子表中查询

select t.avgsal,s.grade
from 
(select avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on 
t.avgsal between s.losal and s.hisal;

union

• 拼接起来

select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman'

limit 取结果中的部分数据

• limit的语法 limit startIndex,length

select ename,sal from emp order by sal desc limit 0,5;

DQL语句顺序

select
from
where
group by
having
order by
limit

例子

1. 取得每个部门最高薪水的人员名称

select
     e.ename,t.*
from
     emp e
join 
    (select deptno,max(sal) as sal from emp group by deptno) t
on
     e.deptno = t.deptno and e.sal = t.sal;

2. 哪些人的薪水在部门的平均薪水之上

select e.ename,e.sal,t.*
from emp e 
join
(select deptno,avg(sal)as avgsal from emp group by deptno) t
on
e.deptno = t.deptno and e.sal>t.avgsal;

3. 取得部门中（所有人的）平均的薪水等级

select avg(t.grade)as avggrade ,t.deptno
from 
(select
     e.ename,e.deptno,s.grade
from
     emp e
join 
     salgrade s
on
    e.sal between s.losal and s.hisal) t
group by 
t.deptno;

​ 取得部门中所有人平均薪水的等级

select 
t.avgsal,s.grade,t.deptno
from
(select avg(sal)as avgsal,deptno from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;

4. 不准用分组函数max()取得最高薪水给出两种解决方案

• 排序limit : order by sal desc limit 0,1

select ename,sal from emp order by sal desc limit 0,1;

• 表的自连接

select ename,sal from emp where sal not in(
    select 
         e.sal 
    from
         emp e
    join
         emp a
    on
        e.sal < a.sal
);

5. 给出平均薪水最高的部门的部门编号

select max(t.avgsal),t.deptno
from
(select avg(sal) as avgsal,deptno from emp group by deptno) t;

6. 取得平均薪水最高的部门名称

select 
    d.dname
from
    (select max(t.avgsal),t.deptno
    from
    (select avg(sal) as avgsal,deptno from emp group by       deptno) t) p
join 
    dept d
on
    p.deptno = d.deptno;  

7. 求平均薪水的等级最低的部门名称

select d.dname,min(q.grade) from
(select
      t.deptno,s.grade
from
      (select avg(sal)as avgsal,deptno from emp group by deptno) t
join
       salgrade s
on
     t.avgsal between s.losal and s.hisal
) q
join
     dept d
on
    q.deptno = d.deptno;

8. 取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名

select ename from emp where sal>
(select max(sal)from emp where empno not in 
    (select distinct mgr from emp where mgr is not null)
);

9. 取得薪水最高的前五名

select ename,sal from emp order by sal desc limit 0,5;

10. 取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limit 5,5;

11. 取得最后入职的5名员工

select ename,hiredate from emp order by hiredate desc limit 0,5;

12. 取得每个薪水等级有多少员工

select 
t.grade,count(ename)
from
    (select 
        e.ename,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal) t
group by t.grade;

13. 有3个表 s(学生表)，c(课程表)，sc(学生选课表)

​ s(sno,sname) 代表（学号，姓名）

​ c(cno,cname,cteacher) 代表（课号，课名，教师）

​ sc(sno,cno,scgrade) 代表（学号，课号，成绩）

​ 问题：

​ （1）、找出没选过"黎明"老师的所有学生姓名

select distinct sname 
from
	s a
join
    (select 
        b.sno
    from
        sc b
    where
        b.cno != (select cno from c where cteacher = '黎明')) d
on 
	d.sno = a.sno;

​ （2）、列出2门以上（含2门）不及格学生姓名及平均成绩

select
	a.name,d.unpass_num
from
    (select 
        t.sno,count(t.sno)as unpass_num 
    from
        (select sno from sc where scgrade < 60) t
    group by 
    t.sno) d
join
	s a
on
	d.sno = a.sno and d.unpass_num > 2;

​ （3）、即学过1号课又学过2号课所有学生的姓名

select 
	a.sname 
from
	(select sno from sc where cno = 1 and cno = 2) d
join
    s a
on
	a.sno = d.sno;

14. 列出所有员工及领导的名字

select
	a.ename as '员工',ifnull(b.ename,'无') as '领导'
from 
	emp a
left join
	emp b
on
	a.mgr = b.empno;

15. 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

select 
	a.empno,a.ename,d.dname
from
    emp a
join
    emp b
on
    a.mgr = b.empno 
join
	dept d
on
	a.deptno = d.deptno
where a.hiredate < b.hiredate;

16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select 
	e.*,d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno;

17. 列出至少有5个员工的所有部门

select 
	t.deptno,d.dname,t.sum
from
	(select count(ename)as sum,deptno from emp group by deptno) t
join
	dept d
on
	t.deptno = d.deptno
where
	t.sum >= 5
;

18. 列出薪资比“SMITH”多的所有员工信息

select * from emp where sal >(select sal from emp where ename = 'SMITH');

19. 列出所有“CLERk”(办事员)的姓名机器部门名称,部门的人数

select 
	e.ename,d.dname,t.num
from
	emp e
join
	dept d
on
	e.deptno = d.deptno and e.job = 'CLERK'
join 
    (select 
        deptno,count(ename) as num
    from 
        emp group by deptno) t
on
	t.deptno = e.deptno	
;

20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select count(ename)as num,job from emp group by job
having min(sal) > 1500;

21. 列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp where deptno = (select deptno from dept where dname = 'sales');

22. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级。

select 
	e.ename as'员工',d.dname,c.ename as '领导',s.grade
from
	emp e
left join
    emp c
on
	e.mgr = c.empno 
join
	dept d
on
	d.deptno = e.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp)
;

23. 列出与“scott”从事相同工作的所有员工及部门名称

select 
	e.ename,e.deptno,d.dname
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
where 
	e.job = (select job from emp where ename = 'scott')
	and e.ename != 'SCOTT';

24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

select 
e.ename,e.sal,e.deptno
from 
emp e
join
(select distinct sal from emp where deptno = 30) t
on
e.sal = t.sal and e.deptno != 30
;

25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

select 
	e.ename,e.sal,d.dname
from 
	emp e
join
	dept d
on
	d.deptno = e.deptno
where 
	sal > (select max(sal) from emp group by deptno having deptno = 30) ;

26. 列出在每个部门工作的员工数量，平均工资和平均服务期限

select 	deptno,count(ename),avg(sal),avg(timestampdiff(year,hiredate,now()))as avg_service_time 
from 
	emp 
group by 
    deptno;

27. 列出所有员工的姓名，部门名称和工资

select 
	e.ename,d.dname,e.sal
from
	emp e
join
	dept d
on
	d.deptno = e.deptno;

28. 列出所有部门的详细信息和人数

select 
	d.deptno,d.dname,d.loc,count(e.ename)
from
	emp e
right join
	dept d
on
	d.deptno = e.deptno
group by 
	e.deptno,d.dname,d.loc;

29. 列出各种工作的最低工资及从事工作的雇主

select 
	min(e.sal),e.job,a.ename
from 
	emp e 
left join
	emp a
on
	e.mgr = a.empno
group by 
	e.job;

30. 列出各个部门的manager 的最低薪资

select
	e.deptno,min(e.sal)
from
	emp e
where
	job = 'manager'
group by
	e.deptno;

31. 列出所有员工的年工资，按年薪从低到高排序

select ename,sal*12 as yearsal from emp order by yearsal asc;

32. 求出员工领导的薪水超过3000的员工名称与领导名称

select
	e.ename,m.ename,m.sal
from
	emp e
join
	emp m
on
	e.mgr = m.empno
where 
	m.sal > 3000;

33. 求出部门名称中，带’s’字符的部门员工的工资合计、部门人数

select
	d.dname,ifnull(sum(e.sal),0),count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno 
where
	d.dname like '%s%'
group by
	e.deptno;

34. 给任职日期超过30年的员工加薪10%

update emp2 set
	sal = sal*1.1
where
	 timestampdiff(year,hiredate,now()) > 30;

DML 数据操作语言

insert

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;

• 注意：也可以不写要插入的列名字,但是要插入的数量类型要和列名定义格式相互对应。

insert into  dept values(10,'accounting','new york');

• 可以将查询出来的结果创建成为一张新表

create table emp2 as select empno,ename from emp;

delete

• 语法格式：delete from 表名 where 条件；
• 删除所有记录: delete from 表名；
• 删除表,保留定义格式，保留表名字，不可恢复: truncate table 表名；
• 彻底删除整个表，连表名都消失不见：drop table 表名；

update

• 修改格式 update 表名 set 字段1 = 值1，字段2 = 值2…where 条件

update emp2 set emptno = 11,ename = 'zhangsan' where ename = 'SMITCH';

DDL 数据定义语言

drop

• 彻底删除整个表，连表名都消失不见：drop table 表名；

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

create

数据类型

• int 整数型
• bigint 长整型
• float 浮点型
• char() 定长字符串括号里面加长度（性别）
• varchar 可变长字符串（姓名、简介）
• data 日期类型
• BLOB 二进制大对象（图片、视频、媒体）
• CLOB 字符大对象（较大文本）

约束

• default 默认值
• not null 非空约束
• unique 唯一约束, 当为空时可以重复
• primary key 主键约束，不能为NULL,也不能重复,表的三范式中第一要就时要求要有主键约束，充当这行记录在这张表中的唯一标识。一张表的主键约束只能有一个。mysql 提供主键值自增 auto_increment,此时插入时需要指定列。

create table test(
id int primary key auto_increment,
    username varchar(255)
);

• foreign key 外键约束，用于两张表相互关联时，字段内的内容必须来自于另外一个表的某个字段。创建表的时候先创建父表，删除时先删子表。

create table t_class(
    cno int,
    cname varchar(255),
    primary key(cno)
);
create table t_student(
    sno int primary key,
    sname varchar(255),
    classno int,
    foreign key(classno) references t_class(cno)
);

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );

alter

• 用于修改表名或者修改表的字段
• 删除一个字段

alter table t_class drop cname;

• 添加一个字段

alter table t_class add cname int;

• 在指定位置添加字段可使用 FIRST AFTER

alter table t_class add snum int AFTER cname;
alter table t_class add smaster varchar(255) not null default 'ZHANGSAN' FIRST;

• 如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句

alter table t_class modify snum bigint;
ALTER TABLE t_class change snum Stu_Number int;

• 修改字段默认值

alter table t_class alter smaster set default 'LISI';
alter table t_class alter smaster drop default;

• 修改表名

ALTER TABLE t_class rename to t_classes;

• 删除主/外键约束,先查看外键别名,drop froeign key 别名

alter table t_student drop primary key;
show create table t_student;
alter table t_student drop foreign key t_student_ibfk_1;

• 增加主建：alter table 表名 add primary key (字段);
• 增加外键:Alter table 表名 add [constraint 外键名字] foreign key [外键字段] references 父表(主键字段);

alter table t_student add foreign key (classno) references t_calsses(cno);

存储引擎

• 常见的存储引擎

1.MyISAM存储引擎

​ —使用三个文件表示每个表：

​ 格式文件 — 存储表结构的定义（xxx.frm）

​ 数据文件 — 存储表内的数据(xxx.MYD)

​ 索引文件 — 存储表上的索引(xxx.MYI)

​ 优点：可被压缩节省存储空间，并且可被转换成只读表，提高检索效率。

​ 缺点：不支持事务

2. 默认引擎 InnoDB存储引擎

​ 优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

​ — 提供一组用来记录事务性活动的日志文件。

​ — 用commit(提交)、SAVEPOINT 及 ROLLBACK（回滚）支持事务处理

​ — 提供全ACID 兼容

​ — 在MySQL服务器崩溃后提供自动恢复

​ — 支持外键及引用的完整性，包括级联删除和更新

3. memory存储引擎

​ 查询快、但存储在内存中，易丢失。

• 修改存储引擎

alter table t_student engine=myisam;

TCL 事务控制语言

• 一个事务是一个完整的业务逻辑单元不可再分

​ — 比如：银行账户转账,act-001向act-002转账10000,需要两个事务同时成功才可以。

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

• 和事务有关的之后DML语句（insert delete update）

• 原子性：事务是最小的工作单元，不可再分

• 一致性：事务必须保证多条DML语句同时成功后者同时失败

• 持久性：最终数据必须持久化到文件中事务才算成功的结束。

• Commit Transaction 提交事务

• Rollback Transaction 回滚事务

• mysql事务默认情况下是自动提交的，执行一次DML语句提交一次， 关闭自动提交：start transaction

start transaction;
insert into t_classes(smaster,cno,cname,Stu_Number)values('zhansgan',1,'class1',22);
insert into t_classes(smaster,cno,cname,Stu_Number)values('zhansga',2,'class2',33);
commit;
insert into t_classes(smaster,cno,cname,Stu_Number)values('zhansg',5,'class3',44);
insert into t_classes(smaster,cno,cname,Stu_Number)values('zhans',6,'class4',55);
rollback;

隔离级别

• 隔离性：事务A与事务B之间具有隔离

​ 第一级别：读未提交，读到了脏的数据，可以读取到对方未提交的数据

​ 第二级别：读已提交，解决脏读现象，不可重复读，对方提交之后的事务我可以读到

​ 第三级别：可重复读，读到的是幻象

​ 第四级别：序列化读/串行化读，解决了所有问题，需要事务排队

​ oracle数据库默认的隔离级别是：读已提交

​ mysql数据库默认的隔离级别是：可重复读

• 查看事务的隔离级别

select @@global.tx_isolation;

• 设置隔离级别

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

索引

• 索引相当于目录，可以快速的找到对应的资源，使用的是B树。
• 数据量庞大，或者给特定字段添加索引，或者经常出现。
• 主键和具有unique约束的字段会自动添加索引
• 创建索引 create index 索引名 on 表名（字段）

​ 删除索引 drop index 索引名 on 表名

create index emp_sal_index on emp(sql);
drop index emp_sal_index on emp;

• 模糊查询时第一个字符是百分号时，使用不了索引

视图

• 什么时视图？站在不同的角度去看数据。

• 视图作用：保护乙方隐私

• 创建视图/删除视图

create view myview as select empno,ename from emp; 
drop view myview;
update myview set ename = 'hehe',sal = 1 where empno = 7369;
delete from myview where empno = 7369;

• 对视图增删改查会影响原表数据（通过视图影响原表，不是直接操作原表）

数据库三范式

1. 任何一张表都应该有主键，并且每一个字段原子性不可再分。

2. 建立在第一范式之上，所有非主键字段完全依赖主键，不能产生部分依赖。

3. 建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

• 实际开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
• 一对一：两张表主键共享\外键接表1主键并且唯一

​ t_user_login

​ id(pk) username password

---

​ 1 zs 123

---

​ 2 ls 456

​ t_user_detail

​ id(pk+fk) realname phone

---

​ 1 zhangsan 1231247

---

​ 2 lisi 2135656

• 一对多，两张表，多的表加外键。

​ t_class

​ cno(pk) cname cmaster

---

​ 1 class1 zs

---

​ 2 class2 ls

​ t_student

​ id(pk) name class(fk)

---

​ 1 ads 1

---

​ 2 asd 2

---

​ 3 ggg 1

• 多对多，三张表，两个对象表加一张关系表（两个外键）。

​ t_student

​ id(pk) name class(fk)

---

​ 1 ads 1

---

​ 2 asd 2

---

​ 3 ggg 1

​ t_subject

​ subno(pk) sname s_teacher

---

​ 1 English joker

---

​ 2 Chinese luxun

---

​ 3 Math lisi

​ t_relation

​ id(pk) sno(fk) tno(fk)

---

​ 1 1 2

---

​ 2 2 3

---

​ 3 3 1

​

DCL 数据控制语言

• grant 授权
• revoke撤销权限

MySQL优化

1. from 优先级最高，先生成一个临时表
2. where 循环遍历当前临时表中的每一个数据行
3. group by 首先对临时表排序处理，然后吧相同特征的数据行保存到一i个临时表中
4. having 将group by 生成的临时表过滤处理
5. select 遍历指定内容，生成临时表
6. order by 将排序后的内容组成一个全新的临时表
7. limit 截取

• 执行比较慢 的命令

1. group by
2. order by
3. where
4. limit