Gepostet vor Aktualisiert vor Mysql26 minutes lesen (Über 3843 Wörter)

重学Mysql

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

连接数据库

1
2
3
4
5
6
7
## 连接本地数据库
mysql -u用户名 -p密码
mysql -uroot -proot 

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

sql基本语句

1
2
3
4
5
6
## 查看数据库版本
select version();
## 查看数据库当前时间
select now();
## 查看用户
select user();
1
2
3
4
5
6
7
8
9
10
## 创建库（注意：库名多是大写字母）
create database 库名;
## 查看有哪些库
show databases;
## 删除某个库
drop database 库名;
## 创建库时制定编码格式
create database 库名 character set utf8;
## 进入某一个库下
use 库名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## 创建一个数据库表
create table person{
	name varchar(10),
	sex varchar(2),
	age int
};
## 查看该仓库下的所有表
show tables;
## 查看某个表的所有列及列的数据类型
desc 表名;
## 查看创建表的详细语句，数据库引擎及字符编码
show create table 
## 表重命名
alter table 旧名 rename 新名
## 删除表
drop table 表名;

增删改查

增

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

查

1
2
3
4
5
6
7
8
9
10
## 查看所有数据
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;

改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
## 更新所有数据
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 表名 (多个逗号隔开)

删

1
2
3
4
## 删除表中的所有数据（如果不加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，没有任何意义

约束

主键约束及自增长

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

字段唯一

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

字段不能为空

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

字段默认值

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

表达式

like

1
2
3
4
5
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 '张三_';

排序

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

分页

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

聚合函数

1
2
3
4
5
6
7
8
## 统计数量是最好用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;
1
2
3
select max(age) from person;
select min(age) from person;
select avg(age) from person;

分组

1
2
3
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;

分组统计

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

分组获取最大值

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

having的使用

1
2
3
4
## 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之间的字段才能作为判断条件。

多表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 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
);

交叉查询

1
2
3
4
5
## 笛卡尔积运算
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;

内连接查询

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

外连接查询

1
2
3
4
## 左外连接(会获取到左边的所有数据)
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;

嵌套查询

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

组合查询

1
2
3
4
5
6
7
8
## 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';

索引操作

1
2
3
4
5
6
## 创建索引
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）

事务处理

1
2
3
4
5
6
7
8
9
10
11
12
## 开始事务
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;
    • 特定的列
    • 特定的存储过程
  • 新创建的账户没有任何权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 创建账户
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

    1
    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

复制表

1
create table tbA as select * from tbB;

删除表中重复数据

1
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)

查询是否有重复数据

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

Links

Kategorien

Letzte Einträge

Archive

Tags

ADB1
AIDL1
Bundle1
Docker1
FFMpeg3
IPC1
ImageView1
LiveData1
Mac1
Messenger1
Mysql1
Nginx1
RustDesk1
RxJava1
Socket1
Socks51
Ubuntu1
ViewModel1
adb1
bash1
centOS1
code2
curl1
git2
gradle1
handler1
homebrew1
java1
jdk1
mysql2
nginx1
redis1
ssh1
ui适配1
xshell1
代理1
内存泄漏1
原理1
基础1
学会拒绝1
工具1
性能1
成员激励1
方法论1
源码1
爬虫1
资源搭建1
进度转换1
×