note 文档

# 数据操作

# SELECT 查询

#完整的查询语句
SELECT select_expr[,select_expr...]
	[
		FROM table_references
		[WHERE条件]
		[GROUP BY {col_name | position} [ASC |DESC]，...分组]
		[HAVING 条件 对分组结果进行二次筛选]
		[ORDER BY {col_name | position} [ASC |DESC]，...排序]
		[LIMIT 限制显示条数]
	]
1
2
3
4
5
6
7
8
9
10
  • SELECT
select * from tb_name;
select [字段],[字段] from tb_name; #查询指定字段
select [* | 字段] from database.tb_name; #查询不同数据库下的表
select 字段名称 as 编号 from tb_name #别名

distinct 用于剔除重复的记录
1
2
3
4
5
6
ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值
curdate() 获取当前日期 年月日
curtime() 获取当前时间 时分秒
sysdate() 获取当前日期+时间 年月日 时分秒
1
2
3
4
  • where子句查询

对表中的所有记录进行筛选、过滤使用where子句

下面的运算符可以在 WHERE 子句中使用：

img

比较：=、<、<=、>、>=、!=、<>、!>、!<、<=>
指定范围：between and、not between and
指定集合：in、not in
匹配字符：like、not like
是否为控制：is null、is not null
多个查询条件：and、or
like进行模糊查询，"%" 表示通配，表示0或多个字符。"_"表示一个任意的字符
1
2
3
4
5
6
7
  • GROUP BY查询结果分组
配合GROUP_CONCAT()得到分组详情
配合聚合函数
    MAX()
	MIN()
    count(column)统计某列的行数
    sum(column)对某列的值求和
    avg(column)对某列的值求平均值
配合WITH ROLLUP记录上面所有记录的总和
1
2
3
4
5
6
7
8
  • HAVING语句
select id,sex,group_concat(username),count(*) from user group by sex having count(*)<5; #对分组结果进行二次筛选
1
  • ORDER BY排序
select * from user order by desc; 
# order by 排序的列 asc 升序(从低到高)
# order by 排序的列 desc 降序(从高到低)
1
2
3
  • LIMIT限制查询结果显示条数
select * from user limit n；#只有一个参数，显示（n）条记录
select * from user limit i,n；#两个参数，显示从i+1开始，总共n条记录
1
2
  • 笛卡尔积查询
笛卡尔积查询：如果同时查询两张表，左边表有m条数据，右边表有n条数据，那么笛卡尔积查询是结果就是 m*n 条记录。这就是笛卡尔积查询。例如： 
	select * from dept,emp;
	上面的查询中包含大量错误的数据, 一般不使用这种查询。
	
	如果只想保留正确的记录，可以通过where条件进行筛选，将符合条件的保留下来，不符合条件的自然就会被剔除，例如：
	select * from dept,emp
	where dept.id=emp.dept_id;

cross join # cross join将A表的所有行分别与B表的所有行进行连接，返回的记录数为两个表的记录数乘积。cross join 的语法不加 on 关键字

# 内连接查询
join | inner join # inner join组合两个表中的记录，只有公共字段之中有相符的值才进行连接。通过ON连接条件
# 左外连接查询
LEFT [outer] JOIN #是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。
# 右外连接查询
RIGHT [outer] JOIN # 是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。

# 联合查询
UNION
UNION ALL
UNION和UNION ALL区别是UNION去掉相同记录，UNION ALL是简单的合并到一起
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
补充3、where和having都用于筛选过滤，但是： 
	(1) where用于在分组之前进行筛选, having用于在分组之后进行筛选
	(2) 并且where中不能使用列别名, having中可以使用别名
	(3) where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!!
	
补充4、SQL语句的书写顺序和执行顺序:
  SQL语句的书写顺序：
	select...
	from...
	where...
	group by...
	having
	order by...
	limit
	...
  SQL语句的执行顺序：
	from... -- 确定要查询的是哪张表 (定义表别名)
	where... -- 从整张表的数据中进行筛选过滤
	select... -- 确定要显示哪些列 (定义列别名)
	group by... -- 根据指定的列进行分组 
	having
	order by... -- 根据指定的列进行排序
	limit
	...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# INSERT 插入

INSERT tb_name () value|values(),(null,null,'',...); #不指定具体字段名
INSERT tb_name (字段名称...) value|values (null,null,'',...); #列出指定字段
INSERT tb_name (字段名称...) value|values(),(null,null,'',...); #同时插入多条记录
INSERT [into] tb_name () set 字段名=值，...; #set形式插入记录
insert [into] tb_name [(字段名称，...)]select 字段名称 from tb2_name [where条件] # 将查询结果插入到表中

-- 插入记录：insert into 表名(列1，列2，列3...) values(值1，值2，值3...);
insert into stu(id,name,gender,birthday,score)values(1,'王海涛','男','1999-2-2',70);
1
2
3
4
5
6
7
8

# DELETE 删除

delete from tb_name [where条件][order by 字段名称][limit限制条数] #删除数据
truncate [table] tb_name # 彻底清空数据
1
2

# UPDATE 修改

update tb_name set 字段名称=值，... [where条件][order by 字段名称][limit限制条数]
1

# 表操作

# 数据类型

# 数值类型
tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long

float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

# 字符串类型
char(n) 定长字符串，最长255个字符。n表示字符数，
varchar(n) 变长字符串，最长不超过 65535个字节，n表示字符数，一般超过255个字节，会使用text类型
所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）
text 大文本（长文本）类型
最长65535个字节，一般超过255个字符列的会使用text。
另，text也分多种，其中bigtext存储数据的长度约为4GB。

# 日期类型
1、date：年月日
2、time：时分秒
3、datetime：年月日 时分秒
4、timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。
timestamp最大表示2038年，而datetime范围是1000~9999
timestamp在插入数、修改数据时，可以自动更新成系统当前时间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 字段约束

完整性约束条件

 primary key
 auto_increment
 foreign key
 not null
 unique key
 default
1
2
3
4
5
6
ALTER TABLE bookinfo MODIFY book_name VARCHAR(20) NOT NULL;
删除
ALTER TABLE bookinfo MODIFY book_name VARCHAR(20);
1
2
3

主键 primary key

主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号,

单字段主键and多字段主键
1.在定义列的同时定义主键
id int primary key auto_increment
列名 数据类型 PRIMARY KEY
2.在定义列的后边定义主键
[CONSTRAINT<约束名>] primary(<列名>)
constraint pk_id primary key(book_id);
多字段主键：
PRIMARY KEY(book_id,card_id);

例如:
*创建user表,指定id为主键, 作为用户的唯一标识
create table stu(
	id int **primary key**,
);
主键的特点： 唯一且不能为空！！！
当主键为数值时，为了方便维护，可以设置主键为自增（auto_increment）
**示例：创建user表,指定id为主键, 并指定主键自增**
create table user(
	id int primary key auto_increament,
);
自增的作用是, 后期往表中插入记录时, 自增的列可以不用插入值(插入null即可), 数据库会自动插入值(1,2,3…), 例如：
insert into stu values(null, '王海涛','男', '1993-1-10');
insert into stu values(null, '刘沛霞','男', '1995-6-15');
提示：如果建表时没有指定主键自增, 也可以后期修改id为主键自增。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 添加主键
alter table bookinfo add [constraint [PK_ID]] PRIMARY KEY(字段名称，...);
删除主键
alter table bookinfo DROP PRIMARY KEY;
修改表为列添加主键
alter table bookinfo MODIFY book_id int primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint PK_ID PRIMARY KEY(book_id);

1.主键自增
如果只添加主键约束, 不设置自增
alter table stu modify id int primary key;
如果已经添加主键约束, 仅仅设置自增，但需注意:
(1)如果没有设置主键, 不可添加自增
(2)只有当主键是数值时, 才可以添加自增
alter table stu modify id int auto_increment;
主键自增值设置
alter table tb_name auto_increment=值

删除主键自增
-- 删除主键自增时, 要先删除自增
alter table stu modify id int;
-- 再删除主键约束
alter table stu drop primary key;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

唯一 unique

保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复

1.在定义列的同时定义
列名 数据类型 UNIQUE
username varchar(50) unique,
2.在定义列的后边定义
[CONSTRAINT<约束名>] UNIQUE(<列名>)
constraint uk_bname UNIQUE(book_name);

3种方法更改唯一约束，同主键约束
唯一约束特点是不能重复！！(允许为空)
1
2
3
4
5
6
7
8
9
10
11
12
删除
alter table book_info drop index uk_bname;
alter table book_info drop key uk_bname;
1
2
3

默认 DEFAULT

列名 数据类型 DEFAULT 默认值
press varchr(20) DEFAULT'机械工业出版社'

修改表
alter table bookinfo MODIFY press varchr(20) DEFAULT'机械工业出版社'
alter table bookinfo alter COLUME press varchr(20) DEFAULT'机械工业出版社'
删除约束
alter table bookinfo MODIFY press varchr(20)
alter table bookinfo alter COLUME press drop DEFAULT
1
2
3
4
5
6
7
8
9

非空 not null

保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。

​ password varchar(50) not null,

非空约束的特点是： 不能为空值， 即插入数据时该列必须得有值！！

外键 foreign key

外键是用于表和表之间关系的列，

查看当前FOREIGN_KEY_CHECKS的值可用如下命令：

SELECT @@FOREIGN_KEY_CHECKS;
禁用外键约束：

SET FOREIGN_KEY_CHECKS=0;
启用外键约束：

SET FOREIGN_KEY_CHECKS=1;
1
2
3
4
5
6
7
8
9
[CONSTRAINT<外键约束名>] FOREIGN KEY(<列名>) references<主表名>(主键)

创建时添加
CONSTRAINT fk_cid FOREIGN KEY(book_category_id) references bookcategory(category_id)

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
CONSTRAINT fk_cid FOREIGN KEY(book_category_id) references bookcategory(category_id) ON DELETE CASCADE

修改外键
alter table bookinfo add foreign key(book_category_id) references bookcategory(category_id);
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);
-- 其中 add constraint fk_dept_id 表示新增列，列名为fk_dept_id (名字由自己定义)

删除外键
alter table bookinfo drop foreign key fk_cid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 查看数据表

查询指定表的结构
1.desc tb_name
2.describe tb_name
3.show columns from tb_name
1
2
3
4

# 创建表

create table [if not exists] tb_name(
字段名称 字段类型[完整性约束条件]
...
)engine=引擎名称 charset='编码方式';
1
2
3
4
create table emp(
	id int primary key auto_increment,	-- 员工编号
	name varchar(20),					-- 员工姓名
	dept_id int,						-- 部门编号
	foregin key (dept_id) references dept(id)
);
1
2
3
4
5
6

# 删除表

drop table [if exists] 表1，表2，...;
drop table if exists stu;
1
2

# 修改表

alter table tb_name rename [to|as] new_name # 修改表名
1

# 新增列

alter table <表名> add <新列名><数据类型>[约束条件] [first | after 已存在列名] #添加列
ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype...];

1、往stu表中添加score列，double类型
alter table stu add score double;
1
2
3
4
5

# 删除列

alter table <表名>drop <列名> #删除列
ALTER TABLE tabname DROP [COLUMN] col_name;

1、删除stu表中的score列
alter table stu drop score;
1
2
3
4
5

# 修改列

alter table <表名> change <旧列名><新列名><新数据类型> #修改列名
alter table <表名> modify <列名><数据类型> #修改列数据类型
alter table <表名> modify <列1><数据类型>first | after <列2> #修改列的排列位置
ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]...);

修改id列，将id设置为主键
alter table stu modify id int primary key;

2、修改id列，将id主键设置为自动增长
alter table stu modify id int auto_increment;
1
2
3
4
5
6
7
8
9
10

# 表分区

查看是否支持表分区 show plugins;

一般两种：水平分区和垂直分区

partition by range(book_id)(

partition p1 values less than (20109999),

partition p2 values less than (20159999),

partition p3 values less than (MAXVALUE)

)

SELECT * FROM bookinfo partition(p1);

# 数据库操作

mysqladmin -u用户名 -p旧密码 password 新密码 #修改密码

控制台登录时设置编码
 mysql --default-character-set=gbk -uroot -proot
1
2
3
4
5

# 创建数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
1
2
3
create database if not exists mydb1 charset utf8mb4 COLLATE utf8mb4_general_ci;
提示：创建库时，要记得指定编码

[ ]中的内容是可选的。语法说明如下：
<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。
1
2
3
4
5
6
7
8

# 删除数据库

-- 04.删除mydb1库
drop database mydb1;
drop database if exists mydb1;
1
2
3

# 修改数据库

alter database db_name [default] character set [=] charset_name
1

# 查看数据库

show databases; #显示当前数据库
use mydb1; #进入数据库
SELECT database();# 查看当前进入的库

show create database mydb1;-- 查看建库时的语句（并验证数据库库使用的编码）

查看当前库中的所有表
show tables [from book];
show tables; 

SELECT VERSION();显示当前版本
select now();显示当前日期和时间
select user();显示当前用户
show variables like 'char%';  查看MySQL数据库使用的编码
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 备份&恢复

# 备份

打开CMD窗口(不要登录)，通过命令备份mydb1数据库 备份命令: mysqldump -u用户名 -p 数据库名字 > 数据文件的位置

mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1，参数2.... > 备份文件.sql	例如: mysqldump -uroot -p mydb1 > d:/mydb1.sql

数据库地址：127.0.0.1
数据库用户名：root
数据库密码：1234
数据库名称：test1
数据库名称：test2
数据库名称：test3
mysqldump目录：/usr/bin/mysqldump
备份目录：/root/db_back/

# 备份数据库内容
mysqldump -uroot -p1234  database1  > /root/db_back/database1.sql
# 备份表中内容
mysqldump -uroot -p1234  database1 table1  > /root/db_back/table1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
输入密码, 如果没有提示错误, 即备份成功, 查询d盘的mydb1.sql文件
提示: (1)备份数据库的命令后面不要添加分号(;)
(2)备份数据库只是备份数据库中的表, 不会备份数据库本身

# 恢复

由于备份时, 没有备份数据库本身, 所以在恢复库中的数据前, 需要先创建好要恢复的库 -- 创建mydb1数据库 create database mydb1 charset utf8mb4;

在CMD窗口中(不要登录)，通过命令恢复mydb1数据库 -- 在CMD窗口中(不要登录) 恢复命令: mysql -u用户名 -p 数据库名字 < 数据文件的位置 例如: mysql -uroot -p mydb1 < d:/mydb1.sql

输入密码, 如果没有提示错误, 即恢复成功, 下面进行验证
-- 在登录状态下, 选择mydb1库, 查询其中的表是否恢复了回来
use mydb1;
show tables;

# SQL优化

# 索引

hash索引：不利于条件查找
B+树

存储引擎：
MyISAM 索引、结构、数据分开3个文件存储

InnoDB一个文件存结构、一根文件索引和数据一起存
一个数据块最大16KB 索引8B指针6B 最终的索引加data算1KB
高度为3的B+树 总共可以存储1170*1170*16条数据
聚合索引：InnoDB，索引和数据一起存放
1
2
3
4
5
6
7
8
9
10

字段代替*

不使用in not in

不使用or 用union

不用模糊中的%col%

不用null

where左端不运算

避免用 where 1=1

# 索引失效

or

2 多列索引

like %col

字符没加''

在索引列上操作

复合索引不能用!=

← 事务 →