重学Mysql

Mysql函数(5.7)查询手册
Mysql函数(8.0)查询手册
Mysql基础教程

连接数据库

## 连接本地数据库
mysql -u用户名 -p密码
mysql -uroot -proot 

## 连接远程数据库
mysql -u用户名 -p密码 -h远程数据库IP地址 -P端口 -D数据库名
mysql -uroot -proot -h192.168.1.88 -p3306 -Dtest

sql基本语句

## 查看数据库版本
select version();
## 查看数据库当前时间
select now();
## 查看用户
select user();

## 创建库（注意：库名多是大写字母）
create database 库名;
## 查看有哪些库
show databases;
## 删除某个库
drop database 库名;
## 创建库时制定编码格式
create database 库名 character set utf8;
## 进入某一个库下
use 库名;

## 创建一个数据库表
create table person{
	name varchar(10),
	sex varchar(2),
	age int
};
## 查看该仓库下的所有表
show tables;
## 查看某个表的所有列及列的数据类型
desc 表名;
## 查看创建表的详细语句，数据库引擎及字符编码
show create table 
## 表重命名
alter table 旧名 rename 新名
## 删除表
drop table 表名;

增删改查

增

## 插入所有列
insert into person values("zhangsan","男",18);
## 插入部分列
insert into person(name,age) values("zhangsan",18);
## 插入查询出来的数据
insert into user(username) select name from account;

查

## 查看所有数据
select * from person;
## 查看符合条件的数据
select * from person where age = 18;
## 查看通过符合多个条件的数据
select * from person where age = 18 and sex = '男';
## 查看部分列数据
select name,sex from person where age = 18;
## 别名的使用，注：as可以省略
select name as "姓名" ，sex as "性别" from person where age = 18;

改

## 更新所有数据
update person set name = 'lisi';
## 更新符合条件的数据
update person set name = 'lisi' where age = 18;
## 修改字段数据类型
alter table 表名 modify 字段名 字段类型
## 修改字段名
alter table 表名 change 旧字段名 新字段名 新字段类型
## 添加字段
alter table 表名 add 字段名 字段类型
## 删除字段
alter table 表名 drop 字段名 字段类型
## 删除表的外键约束
alter table 表名 drop foreign key 外键约束名
## 删除没有关联的表
drop table if exists 表名 (多个逗号隔开)

删

## 删除表中的所有数据（如果不加where表示删除所有数据）
delete from person where .....
## 删除表中所有数据
truncate table 表名;

1. delete是逐行一条一条删除记录的，truncate则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比delete快
2. delete删除后，配合事件回滚可以找回数据；truncate不支持事务的回滚，数据删除后无法找回
3. delete删除数据后，系统不会重新设计自增字段的计数器；truncate晴空表记录后，系统会重新设置自增字段的计算器
4. delete的使用范围更广，因为它可以通过where子句指定条件来删除部分数据；而truncate不支持where子句，只能删除整体。
5. delete回返回删除数据的行数，而truncate只会返回0，没有任何意义

约束

主键约束及自增长

## 一个表中只能设置一个主键
create table person(
	id int(11) primary key auto_increment,
	name varchar(10),
	sex varchar(2),
	age int
);

字段唯一

create table user(
	ID int primary key auto_increment,
	PHONE_NUM varchar(11) unique
);

字段不能为空

create table user(
	ID int primary key auto_increment,
	USERNAME varchar(100) not null unique,
	GENDER char(1) not null,
);

字段默认值

create table person(
	id int(11) primary key auto_increment,
	name varchar(10) default '',
	sex varchar(2) default '男',
	age int default 0
);

表达式

like

select * from person where name like '张三%'; 
select * from person where name like '%张三';
select * from person where name like '%张三%';
select * from person where name like '%张%三'%;
select * from person where name like '张三_';

排序

## 默认为升序（asc），降序使用desc
select * from person order by age (asc | desc)
## 排序可以同时指定两个关键词
select * from person order by age desc,name asc

分页

## 1的意思是从1开始，但是不包括1，2的意思是取2条
select * from person limit 1,2
## 分页与排序同时使用时，必须先排序，后分页
select * from person order by age limit 1,2

聚合函数

## 统计数量是最好用count(1)
select sum(age) from person;
select sum(age) from person where id > 2;
select count(*) from person;
select count(1) from person;
select count('a') from person;
select count(age) from person;
select count(*) from person where id > 2;

select max(age) from person;
select min(age) from person;
select avg(age) from person;

分组

select age from person group by age;
select name,age from person group by name,age;
select age,count(1) from person group by age;

分组统计

select count(1) from (select age from person group by age) temp

分组获取最大值

## 统计各年龄的最大id
select max(id) ,age from person group by age;

having的使用

## where不能和group by一起使用，所以此时出现了having
select age from person group by age having age > 2;
select count(1),age from person group by age having count(1) > 1;
select age from person group by age having count(1) > 1;

having后面并不是所有字段都可以作为判断条件，必须是可以放在select 和 from之间的字段才能作为判断条件。

多表查询

## student表
create table student(
	id int(11) primary key auto_increment;
	name varchar(100) default '',
	age int(11) default 0
);

## score表
create table score(
	id int(11) primary key auto_increment,
	student_id int(11),
	type varchar(100) default '',
	score int(11) default 0
);

交叉查询

## 笛卡尔积运算
select * from student cross join score
select * from student st cross join score sc where st.id = sc.student_id;
## 开发中最常用的语句
select st.name st,age,sc.type,sc.score from student st,score sc where st.id = sc.student_id;

内连接查询

## 这种方式和交叉连接得到的结果相同，所以这种方式很少使用
select * from student st inner join score sc on st.id = sc.student_id;

外连接查询

## 左外连接(会获取到左边的所有数据)
select * from student st left outer join score sc on st.id = sc.student_id;
##  右外连接
select * from student st right outer join score sc on st.id = sc.student_id;

嵌套查询

select * from score where student_id in (select id from student where naem = 'zhangsan');

组合查询

## union中，所有查询的列数和列顺序必须相同
select cust_name, cust_contact, cust_email
from customers
where cust_state in ('a,'b','c')
union
select cust_name, cust_contact, cust_email
from customers
where cust_name = 'test';

索引操作

## 创建索引
create index user_index on user(id);
## 创建唯一索引
create unique index user_index on user(id);
## 删除索引
alter table user drop index user_index;

创建索引能提高查找效率，但是索引也有弊端。创建索引系统会自动维护一个索引表，每当数据增加，更新，删除时都需要更改索引表，所以创建索引会降低增加，更新，删除的效率。

创建索引的建议：

1. 定义主键的数据列一定要建立索引
2. 定义有外键的数据列一定要建立索引
3. 对于经常查询的数据列最好建立索引
4. 对于需要在指定范围内的快速或频繁查询的数据列最好创建索引
5. 经常用在where句中的数据列最好创建索引
6. 经常出现在关键字order by，group by，distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

不建议创建索引：

1. 对于那些查询中很少设计的列，重复值比较多的列不要建立索引
2. 对于定义为text，image和bit的数据类型的列不要建立索引
3. 对于经常存取的列避免建立索引
4. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个，。索引虽说提高了访问速度，但是太多索引会比较影响数据的更新操作
5. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字母排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被受用，因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

sql数据类型

详细数据类型

数据类型	占用字节数	对比
整数
tinyint	1个字节(-128-127)
smallint	2个字节(-2的16次方～2的16次方-1)
mediumint	3个字节(-2的24次方～2的24次方-1)
int	4个字节(-2的32次方～2的32次方-1)	与java的int取值范围相同
bigint	8个字节(-2的64次方～2的64次方-1)	与java的long取值范围相同
小数
float(n,d)(n表示总位数，d表示小数位数)	单精度浮点型 4个字节	对应java的float
double(n,d)(n表示总位数，d表示小数位数)	双精度浮点型 8个字节	对应java的double
字符串
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符
时间
date	日期，只包含年月日
time	时间，只包含时分秒
datetime	日期和时间都包含
timestamp	时间戳
enum	todo 待完善
set	todo 待完善

• int(3) 3只用于显示宽度，并不能限制取值范围和字节占用空间，文本的则限制字符串的长度
• 常用的是datetime和timestamp，datetime存入的是时间，没有时区的概念。timestamp是时间戳，同时间戳在不同时区对应不同时间。
• datetime是任意时间范围，timestamp是1970年到2038年
• 一般在操作当前时间时使用timestamp，操作历史或者很远的未来时间时使用datetime
• Char的存储速度比Varchar快，但是浪费存储空间

数据库关系设计

关系类型

• 一对一关系
• 一对多关系
• 多对多关系

主键和外键

• 一对一关系中，通常在主要表中设置主键，然后在次要表中设置外键
• 一对多关系中，通常在“一”表中设置主键，在“多”表中设置外键
• 多对多关系中，通常需要引用第三张表，称为关系表。在两个实体表中都设置主键，然后在关系表中设置外键。

---

• 一个表中，要么有主键盘，要么有外键，否则这个表就是一个孤立的表
• 在设有外键的表中一般也设置自己的主键，作用是便于扩展

三大范式

• 第一范式，又称为原子性，即表中的所有字段都不能再拆分
• 第二范式，满足第一范式，且不存在部分依赖，及非主属性必须完全依赖主属性。（主属性及主键，完全依赖是针对联合主键的情况，非主键列不能只依赖于主键的一部分）
• 第三范式，满足第二范式，且不存在传递依赖，及非主属性不能于非主属性之间有依赖关系，非主属性必须直接以来主属性，不能间接依赖主属性。（A->B,B->C A->C）

事务处理

## 开始事务
start transaction;
## 插入操作 A
insert into 'user' values(xxx,xxx);
## 创建保留点
savepoint updateA;
## 插入操作 B
insert into 'user' values(yyy,yyy);
## 回滚到保留点updateA
rollback to updateA;
## 提交事务，只有操作 A 生效
commit;

权限控制

• GRANT 和 REVOKE 可在几个层次上控制访问权限:
  • 整个服务器，使用GRANT ALL 和 REVOKE ALL
  • 整个数据库，使用 ON database.*;
  • 特定的表，使用ON database.table;
  • 特定的列
  • 特定的存储过程
• 新创建的账户没有任何权限

## 创建账户
create user myuser identified by 'mypassword';
## 修改账户
update user set user = 'newuser' where user='myuser';
flush privileges;
## 删除账户
drop user myuser;
## 查看权限
show grants for myuser;
## 授予权限
grant select,insert on *.* to myuser;
## 删除权限
revoke select ,insert on *.* from myuser;
## 更改密码
set passowrd for myuser = 'mypass';

存储过程（todo 待完善）

触发器（todo 待完善）

SQL优化

• 创建索引

• 执行计划分析

  查看某个sql语句的执行计划只需要在sql语句前面添加EXPLAN

  EXPLAIN select * from person where age = 16 and id > 0;

  1. type：表示执行sql使用了哪种类型。类型从好到差依次是system，const, eq_reg, ref, range, index, ALL
  2. rows：数据遍历的行数，数值越小越好
  3. key_len：使用索引的长度，该值越小越好
  4. key：实际用到的索引，null就是没有
  5. id：相同，执行顺序由上而下；不相同，子查询id序号递增，id值越大优先级越高，越先被执行
  6. select_type：
  7. Ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。

• sql优化具体分析

  1. 尽量给字段设置默认值，避免字段值为null（如果字段是int类型的age，首先给age创建索引时null不会参与创建索引。where age=0 比where age is null效率高）
  2. 在where语句中少使用or，使用or将不使用索引
     如select name from person where age = 16 or age = 20;
     可以写成：
     select name from person where age = 16 union all select name from person where age = 20;
  3. 在where语句中避免使用in

sql工具

python相关

sqlalchemy-engine

sqlalchemy-mysql

网上的sql资源

图解SQL
138张图带你MySQL入门

互联网公司常用分库分表方案汇总

数据库设计技巧

SQL试题

MySQL

SQL基础知识汇总

常用sql

复制表

create table tbA as select * from tbB;

删除表中重复数据

delete from vod where id not in (select count(t.id) from (select min(id) as id from vod group by vod_play_url) t)

查询是否有重复数据

select vod_play_url from vod group by vod_play_url having count(vod_play_url) > 1;