MySQL学习笔记

SQL 分类

DDL操作

1、查询

show databases；

select database（）

2、创建

create database 【if not exists】 数据库名 default charset utf8mb4(四个字节 ，utf8不是少)

3、删除

Drop database 【if exits】 数据库名

4、使用

use 数据库名

查询所有表：

• show tables

查询表结构：

• desc 表明

创建

create table tb_user(
	id int comment '编号'，
	name varchar(50) '年龄'，
	age int comment '年龄'，
	gender varchar(1) comment '性别'
);
show create table tb_user;

字符分类

char 性能高，varchar性能低，varchar随着输入的内容自动变长。

gender char（1）男女

案例：

create table emp(
	id int,
	workno varchar(10),
	name varchar(10) ,
	gender char(1) ,
	age tinyint unsigned ,
	idcard char(18) ,
	entrydate date 
)comment '员工表';

修改

alter table 表明 add 字段名 类型

alter table emp change nickname username varchar(30)
alter table emp drop username

修改表明：

alter table 表明 rename to 新

删除表

drop table 【if exists】 表明
truncate  table :删除后在重新创建，就是格式化

总结

DML:增删改

insert、update、delete

添加数据

insert into emp(id,workid,name,gender) valuse (1,12,'nifnd','male')

修改

update 表明 set 字段名=值， 字段名=值 where 【条件】

删除

delete from 表明 【where 条件】

delete from emp where gender='female'
删除所有数据
delete from emp 

总结

DQL 查询

查询多个字段

1、查询自定字段
select name,workno,age from emp;
2、查询所有字段
select * from emp;
3、查询所有员工的工作地址
select workaddress as '工作地址' from emp;
4、查询员工的上班地址（去重复）
select distinct workaddresss '工作地址' from emp;

1 查询年龄大于88
select * from emp where age=88
2 小于20的员工
select * from emp where age<=20
3 查询没有身份证号的员工

select * from emp where idcard is null
4 查询身份证号的员工

select * from emp where idcard is not null
5 不能88的员工

select * from emp where age！=88

select * from emp where age<>88

6 在15 到20之间的员工

select * from emp where between 15 and 20

select * from emp where age=> 15 and age=< 20
7 等于18 20 22 的员工

select * from emp where age=18 or age=20 or age=12

select * from emp where age in(18,20,22)
8 员工两个字的   - 一个字段  % 任意字段

select * from emp where name like '_ _'
9 身份证最后一位是x的员工信息

select * from emp where idcard like '%X'

聚合函数

统计总数量   null 不算计数
select count(*) from emp
平均年龄

select avg(age) from emp
最大年龄

select max(age) from emp
西安地区的年龄之和

select sum(age) from emp where area='西安'

分组查询

• having 对已经查询的数据再进行过滤

根据性别分组，统计男、nv的数量
select gender,count(*) from emp group bu gender
根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) from emp group by gender
查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于3的工作地址

select count(),address from emp where age<45 group by area having count( >=3)

排序

根据年龄升序
select * from emp order by age asc
根据入职时间降序

select * from emp order by date desc
根据年龄对公的员工升序，年龄相同，再按入职降序

select * from emp order by age asc,date desc
升序asc  可以省略

分页查询

分页 10条
select * from emp limit 0,10

select * from emp limit 10
第二页10条

select * from emp limit 10,10

select * from emp where gender =female and age in(20,21,22,230)
select * from emp where gender=male and age >=20 and age =<40 and like name='---'
select count(*),gender from emp where age<=60 group by gender
select name,gender from emp where age<=35 order by age,date desc
select * from emp where gender='male' and ge >=20 and age =<40 order by age,date limit 5

DQL执行顺序

总结

DCL 管理数据权限

函数：

select if (true ,'ok','error')  //ok
select ifnull ( 'ok','error')  //ok
case when then else end

select 
id,
name,
( case when math >=85 then 'good' when math>=60 then 'min' else '不及格' end)  '数学',
english,
chinese 
from

emp

约束

create table user(
	id int promary key auto_increment,
	name varchar(10) not null unique,
	age int check(age >0 and age <=120)
	status char(1) default '1',
	gender char(1) 
)
insert into user(name ,age,status,gender)

values( 'tom',19,'1','male')

外键：

外键约束：

多表查询：

多表关系：

多表查询：

select * from emp,stu  # 笛卡尔积
select * from emp,stu where emp.dep_id=dep_id

多表查询的分类

内连接：

都是求交集相等
select emp.name,dept.name from emp,dept where emp.dep_id=dept.id
select * from emp,dept from emp,dept inner join dept on emp.dep_id=dept.

左外连接：

select * from emp left join dept on emp.dep_id =dept.id
select * from emp right join dept on emp.dep_id =dept.id

自连接：

select * from emp a1 , emp a2 where a1.mangerid=a2.id
select a1.name,a2.name from emp a1 left join emp a2 on a1.mangerid=a2.id

联合查询：

select * from emp where salary<=5000
union all  [all]去重 
select * from emp where age<=20

子查询：

标量子查询：

列子查询：

练习：

select * from emp e,dept d where e.dept_id=d.id
select * from emp e inner join dept d on e.dept_id=d.id where e.age<30
select distinst * from emp e,dept d where e.dept_id=d.id
select * from emp e left join dept d on e.dept_id=d.id where e.age>40
select * from emp e,salrty s where e.salary between s.low and s.high

总结：

事务

事务四大特性：

并发事务：

事务的隔离级别：

总结：

MYSQL高级部分

存储引擎

索引

索引分类：

show index from tb_user
create index idx_user_name on tb_user(name)
create unique index idx_user_phone on tb_user(phone)
create index idx_user_por_age_sta on tb_user(pro,age,sta)
drop index idx_user_phone on tb_user(phone)

SQL性能优化

• 模糊查询

当使用like 模糊查询时 ， %在左是索引失效，%在右索引可用

索引设计原则：

总结：

SQL优化

插入数据

主键优化

Order By优化

group by优化

limit 优化

count 优化

update优化

更新只用主键的时候是行锁，如果使用的条件不是主键，就是表锁。

总结：

视图、存储过程、触发器

视图

create or replace view stu as select id,name from student where id<10
查询视图

show create view stu

select * from str where id<3
修改视图

create or replace view stu as select id,name,no from student where id<3
alter view stu as select id,name,no from student where id<3
drop view of exists stu

with check option 会级连，递归检查所有视图条件

with local check option 不会递归 ，只检查自己视图的

create view tb_user_view as select id,name ,pro,age,gender  from tb_user_view
select * from tb_user_view

存储过程

create procedure p1()
begin
	select count(1) from student
end
call p1();
select * from informatuin_schema.ROUTIMES where ROUTIME_SCHEMA=''
show create procedure p1
drop procedure if exists p1

show session variables
show [globall] session variables like 'auto%'
设置系统变量

set session autocommit =0

暂时跳过了

触发器

暂时跳过

总结

锁

全局锁

表级锁

读锁都可以读，写锁只有这个客户端能读能写（共享锁、排它锁）

元数据锁

意向锁

行级锁

　

 

总结

innoDb引擎

逻辑存储结构

 

架构

 

事务原理

MVCC

总结

数据库管理工具