MySQL

 MySQL常用知识点总结，忘了可以进行查阅

数据库（创建+查看、删除数据库+备份恢复数据库）

一、创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name

[create_specification[,create_specification]...]

create_specification:

[DEFAULT]CHARACTER SET charset_name

[DEFAULT]COLLATE collation_name

注释：

1.CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8

2.COLLATE：指定数据库字符集的校对（排序）规则，默认是utf8_general_ci（不区分大小写），常见的是utf8_bin（区分大小写）

3.在创建数据库、表的时候，为了规避关键字，可以使用反引号解决``（键盘左上角ESC下面那个就是，不摁shift）（删除同样加上反引号）

eg：CREATE DATABASE xty_db01 CHARACTER SET utf8 COLLATE utf8_bin；

二、查看、删除数据库

1.显示数据库语句：SHOW DATABASES

2.显示数据库创建语句：SHOW CREATE DATABASE db_name

3.数据库删除语句（慎用）：DROP DATABASE [IF EXISTS] db_name

三、备份恢复数据库（备份：把数据库备份到文件中，恢复：把文件重新恢复到数据库中）

1.备份数据库（需要quit退出MySQL，在DOS执行）：

mysqldump -uroot -proot -B 数据库1 数据库2 ...数据库n >文件名.sql

eg：mysqldump -uroot -p[root,dos建议这里不写密码，但其实无所谓] -B xty_db01 > d:\\ bak.sql

2.恢复数据库（需要进去MySQL再执行）：

source 文件名.sql

eg； source d:\\bak,sql

3.备份库的表（需要quit退出MySQL，在DOS执行）：

mysqldump -uroot -p 数据库 表1 表2 ... 表n > 文件名.sql

4.恢复库的表（需要进去MySQL、在该表所属库下执行）：

source 文件名.sql

表（创建+修改+删除）

一、创建表

1.基本语句：

CREATE TABLE table_name

(

列名1 列的类型，

列名2 列的类型；

列名3 列的类型；

)character set 字符集 collate 校对规则 engine 引擎

注释：

（1）character set：如不指定则为所在数据库字符集

（2）collate：如不指定则为所在数据库校对规则

（3）engine：引擎（myisam、innob等）

（4）创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型

2.MySQL常用数据类型（列类型）

四大类型：数值类型、文本（字符串）类型、二进制类型（MySQL的字段可以直接存二进制进去）、时间日期类型（date：'年-月-日'，time：'时-分-秒'，datetime：'年-月-日-时-分-秒'，timestamp：时间戳，配置后可以自动更新或在修改列时自动更新，year：存放年）

 

数值型（整数）的基本使用：

（1）在能够满足需求的情况下，尽量选择占用空间小的类型

TINYINT 1字节 范围：有符号 -2^7~2^7-1（-128~127） 无符号 0~2^8-1 (0~255)

SMALLINT 2字节 范围：有符号 -2^15~2^15-1 无符号 0~2^16-1

MEDIUMINT 3字节 范围：有符号 -2^23~2^23-1 无符号 0~2^24-1

INT 4字节 范围：有符号 -2^31~2^31-1 无符号 0~2^32-1

BIGINT 8字节 范围：有符号 -2^63~2^63-1 无符号 0~2^64-1

默认有符号，无符号要在create时在列类型后+unsigned

BIT(M) M∈[1，64] 添加数据范围是按照给定的位数确定的，比如M=8，表示一个字节 0~255；显示按照位。（即：添加十进制、查询显示时二进制）

数值型（小数）的基本使用：

1.FLOAT/DOUBLE [UNSIGNED]

Float 单精度 Double 双精度

2.DECIMAL[M,D] [UNSIGNEDE]

（1）M是小数位数的总和，D是小数点后面的位数

（2）如果D是0，表示没有小数位

（3）M的最大值是65 D最大值是30 如果M被省略，默认值是10，如果D被省略，默认值是0。

（4）DECIMAL 可以支持更加精确的小数位

文本（字符串）型的基本使用：

1.CHAR(size) 固定长度字符串 最大255字符

2.VARCHAR(szie) 可变长度字符串 最大65535字节 （虽然VARCHAR 有65535个字节，但是有三个要用来记录字段大小[65535-3=65532]，如果是utf8编码，一个字符对应三个字节，需除以3，才是最多能存的字符 ）

3.细节：（1）char(4) 和varvhar(4)里的4表示的是字符，而不是字节，不区分字符是汉字还是字母，但是不能超出字符范围 varchar里的字符占用字节数，要看具体的编码方式

（2）char(4) 是定长，无论插入几个字符，占用的空间都是4个字符 varchar(4)是变长，按照实际占用空间分配（varchar本身还要占用1~3个字节来记录存放空间长度）

（3）char的查询速度更快，如果数据是定长使用char；如果数据是不确定的，使用varchar（比如：留言，文章）

（4）在存放文本，如果varchar不够用，可以使用text（0~2^16）、mediumtext（0~2^24）或者longtext（0~2^32）

日期类型的基本使用

1.date 年月日：'2022-4-30'

2.time 时分秒：'12：01：00'

3.datetime 年月日时分秒：'2020-4-30 10：08：00'

4.timestamp 时间戳，在insert和update时，自动更新（不需赋值） 但是要配置，配置即在类型后面加上NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

二、修改表 （使用ALTER TABLE 语句添加、修改或删除列的语法）

1.添加列 ALTER TABLE tablename add（列名 列类型 [DEFAULT expr]） [DEFAULT exper]：有默认值，为exper

2.修改列 ALTER TABLE tablename modify （列名 列类型 [DEFAULT expr]）

3.删除列 ALTER TABLE tablename drop （列名）

3.查看表的结构 desc 表名

4.修改表名 rename table 表名 to 新表名

5.修改表字符集 alter table 表名 character set 字符集

6.not null：不允许有空值 not null default xx：不允许为空值，且默认值为xx（xx：数字/' '）插入时要求在某个列后面，用after 该列名

三、删除表

语句：DROP TABLE table_name

数据库的增删改查 CRUD （对记录而言）

一、Insert语句（添加数据）

1.基本语法： INSERT INTO table_name [（列名[，列名...]）] VALUES （数据[，数据...]）

2.细节：

（1）插入的数据应与字段的数据类型相同

（2）数据长度应在列的规定范围内

（3）在values中列出数据位置必须与被加入的列的排列位置相对应

（4）字符和时期类型数据应包含在单引号中（' '）

（5）列可以插入空值[前提是该字段允许为空]，可以在一次形成多个记录，只要在values后加上若干个（）就行

（6）如果是给所有的字段添加数据，可以不写前面的字段名称；部分字段添加要在前面写上列名

二、Update语句（更新数据）

1.基本语法：UPDATE table_name SET 列名=expr1（或列名+=expr1）[，列名2=expr2 ...] [WHERE where_definition] 如果没有where条件，会修改所有的记录，因此要小心

2.细节：

（1）SET子句指示要修改哪些列和要给予哪些值

（2）WHERE子句指示要更新哪些行

（3）如果要修改多个字段，可以通过 set字段1=值1，字段2=值2... 不要忘记中间的逗号

三、Delete语句（删除数据）

1.基本语法： DELETE FROM table_name [WHERE where_definition]

2.细节：

（1）WHERE子句指示要删除哪些行，如果不使用where子句，将删除表中所有数据

（2）FROM子句指示是哪张表

（2）Delete语句只能删除某一行的值，不能删除某一列的值（可使用update设为null或者' '）

（3）使用delete语句仅删除记录，不删除表本身

四、Select语句（查找数据）

1.基本语句： SELECT [DISTINCT] *|{列名1，列名2 ...} FROM table_name

注释：（1）Select指定查询哪些列的数据（2）*号代表查询所有列 （3）FROM 指定查询哪张表 （4）DISTINCT可选 ，指显示结果时，是否去掉重复数据，去重只有在查出来的结果完全相同才可以，某一列相同不去重

2.使用表达式对查询的列进行运算

SELECT *|{列名1|表达式，列名2|表达式，... } FROM tablename

3.在select语句中可使用as语句，as在列名和别名之间可有可无，别名如果是两个以上的单词需要用” “括起来。

SELECT 列名 as 别名 from 表名

2+3实例：统计每个学生的总分并使用别名表示分数—>select stuname，(math+english+chinese) as sum （或者是中文的总分也行）from student

4.在where子句中经常使用的运算符（where起到过滤作用）

（1）比较运算符

[1]> < <= >= = <> !=

[2]BETWEEN ...AND... 显示在某一闭区间的值（english between 80 and 90，英语成绩在80-90之间)

[3]IN(set) 显示在in列表中的值 （math in（89，90，91），数学成绩为89，90，91）

[4]LIKE '徐%'（%代表任意多个字符，表示以徐开头的字符串都行）LIKE _（下划线代表任意单个字符） NOT LIKE '' 模糊查询

如果要查询的字符串本身就含有%、_ ，这时就要使用ESCAPE '<换码字符>'短语对通配符进行转义了

eg：LIKE 'DB_Design' ESCAPE '\' ESCAPE '\'表示”\“为换码字符，这样匹配串中紧跟在”\“后面的字符”_“转换为普通字符了

[5]IS NULL 判断是否为空

（2）逻辑运算符

[1] and多个条件同时成立

[2] or多个条件任一成立

[3] not不成立 eg：where not（sal>90）

5.使用order by 子句排序查询结果

(1)语句SELECT 列名1，列名2，列名3 FROM table order by 列名 asc|desc，...

(2)order by 指定排序的列，排序的列既可以是表中的列名，也可以是select 语句后指定的列名

(3)asc 升序（默认）、desc（降序）

(4)order by 子句应位于select语句的结尾

6.统计/合计函数

（1）count （返回行的总数）

[1]select count（*）|count（列名）from table_name [where where_definition]

[2]count (*) 返回满足条件的记录的行数 VS count（列名） 统计满足条件的该列有多少个，但是会排除null

（2）sum（返回满足where条件的行的和，主要用在数值列）

[1]select sum（列名）from table_name [where where_definition]

（3）avg（返回满足where条件的行的平均值）

[1]select avg（列名）from table_name [where where_definition]

（4）max/min（返回满足where条件的行中的最大/最小值）

[1]select max（列名） from table_name [where where_definition]

[2]selec min（列名） from table_name [where where_definition]

7.使用group by子句对列进行分组

（1）select 列名1，列名2，列名3 ... from table_name group by 列名 [，列名2 ...]

（2）group by 用于对查询的结果分组统计 eg：每个（各个）部门的平均工资

（3）使用having子句对分组后的结果进行过滤 select 列名1，列名2，列名3 ... from table_name group by 列名 having ...

（4）having用于限制分组显示结果，后不仅可以加原来的列名，还可以用别名

8.分页查询（处理海量数据）

（1）select ... limit start，rows

（2）表示从start+1行开始去，取出rows行，start从0开始计算

（3）公式：limit 每页显示记录数*（第几页-1），每页显示记录数

多子句查询顺序：先group by分好组，再having筛选，然后order by排序，最后limit 分页查询

多表查询

1.多表查询是指基于两个和两个以上的表查询。在实际应用中，查询单个表可能不满足我们的需求。

select * from 表名1，表名2；

在默认情况下，当两个表查询时，规则如下：

（1）从第一张表中，取出一行和第二张表的每一行进行组合，返回的表中含有两张表的所有列。

（2）一共返回的记录数 第一张表行数*第二张表行数

（3）我们将多表查询默认处理返回的结果成为笛卡儿积。解决多表查询的关键是要写出正确的过滤条件where

（4）多表查询的条件不能少于表的个数-1，否则会出现笛卡尔积。

2.自连接

（1）自连接是指在同一张表的连接查询[将同一张表看作两张表]

（2）自连接的特点：[1]把同一张表当作两张表用 [2]需要给表取别名-> 表名 表别名 [3]列名不明确时，可以指定列的别名-> 列名 as 列的别名

3.子查询

（1）子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询

（2）单行子查询是指只返回一行数据的子查询语句 ag：select * from 表名 where 列名=（select 列名 from 表名 where where_destination）

（3）多行子查询是指返回多行数据的子查询 使用关键字 in ag：select * from 表名 where 列名 in（select 列名 from 表名 where where_destination）

（4）子查询当作临时表使用（放在from 后面 取别名方便where子句的书写）

把子查询当作一张临时表可以解决很多很多复杂的查询，还可以优化。

（5）在多行子查询中使用all操作符 eg：all（select 列名 from 表名 where where_destination）表示子查询查到的所有数据 where 列名`>all（select 列名 from 表名 where where_destination） 表示取比所有数据都要大的列

（6）在多行子查询中使用any操作符 eg：any（select 列名 from 表名 where where_destination）表示子查询查到的任一数据 where 列名`>all（select 列名 from 表名 where where_destination） 表示取大于查到的任一数据的列

（7）多列子查询 是指查询返回多个列数据的子查询语句（前六个子查询都是单列的 单列单行 单列多行）

select 列名1，列名2，... from 表名 where where_destination 为多列子查询

select * from 表名 where （列名1，列名2，...）=（select 列名1，列名2，... from 表名 where where_destination）其中外层列名顺序要与内层列名顺序一致

（8）表名.* 表示将该表所有都显示出来，适用于多表查询；不同表中有重名的列，可以使用表名.列名来区分

4.表的复制 自我复制数据（蠕虫复制）

（1）自我复制 insert into 表名 select * from 表名 2->4->8->16...增长很快

（2）整个表复制到另一个表中 insert into 表名1 （列名序列）select （列名序列）from 表名

（3）复制表结构 （在创建表的时候） create table 表名1 like 表名2 表1就会有和表2一样的结构了

5.表的去重（去掉表中重复的数据）

步骤：（1）先创建一张临时表my_tmp，该表的结构和欲处理表一样[like]

（2）把欲处理表的记录通过 distinct关键字处理后把记录复制到临时表

（3）清除掉欲处理表的记录[delete from]

（4）把临时表的记录复制到欲处理表

（5）drop掉临时表

6.集合查询（对多个select语句的结果进行操作）

包括并操作union、交操作intersect和差操作except

（1）[1]union all 将两个查询结果合并，并不去重

select ... union all select ...

[2]union 将两个查询结果合并，去重

select ... union select ...

（2）intersect 将两个查询结果取交集

（3）except 将两个查询结果取差集

7.外连接

外连接主要分为左外连接（左侧的表完全显示，即使没有匹配）、右外连接（右侧的表完全显示，即使没有匹配）

外连接出现背景：前面的连接是通过where对多个表形成的笛卡儿积选择，导致得到的结果都是匹配上的，某些原来匹配不到的就被丢掉了，有时候我们仍然需要它们，这就需要用到外连接了。

（1）语法：[1]左外连接 select * from 表1 left join 表2 on 条件

[2]右外连接 select * from 表1 right join 表2 on 条件

表1，表2根据相对位置定位 左表，右表

MySQL约束

约束用于确保数据库的数据满足特定的商业规则，在MySQL中，约束包括：not null（非空）、unique（唯一）、primary key（主键）、foreign key（外键）和check（检查）五种

一、primary key（主键）

1.基本使用： 字段名 字段类型 primary key

2.作用：用于唯一标示表行的数据，当定义主键约束后，该列不能重复

3.细节：

（1）primary key 默认包含非空约束 （不重复且不唯一）

（2)一张表最多只有一个主键，但可以是复合主键（两个列加起来） eg：primary key（列名1，列名2）

（3）主键可以直接在字段后指定 字段名 字段类型 primary key 也可以在最后写primary key（列名）

（4）使用desc表名，可以看到primary key的情况

（5）实际开发中，每个表往往都会设计一个主键

二、not null（非空）

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

1.字段名 字段类型 not null

三、unique（唯一）

当定义了unique，那么该列值是不能重复的

1.字段名 字段类型 unique

2.细节：

（1）如果没有指定not null，则unique字段可以有多个null

（2）如果一个字段 是unique+not null 使用效果类似primary key

（3）一张表中可以有多个unique字段

四、foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null 外键指向主键

1.foreign key（本表字段名）references 主表名（主键名或unique字段名） 定义在从表上，先创建主表。

2.细节：

[1]外键指向的字段，要求是primary key或者是unique

[2]表的引擎类型是innodb才支持外键

[3]外键字段的类型要和主键字段类型一致（长度可以不一样）

[4]外键字段的值必须要在主键中出现过，或者为null（前提是外键字段允许为null）

[5]一旦建立主外键关系，主键数据不能随意删除了（有外键指向它）只有当没有外键指向它或者是删掉了指向它的外键才可以删除

[6]主外键用来约束多表之间的数据，防止数据过于混乱

五、check（检查）

orcale和sql sever均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

1.列名 列的类型 check（check 条件）

2.用于强制行数据必须满足的条件

索引

索引有主键索引、唯一索引（unique）、普通索引（index）和全文索引。

索引可以提高查询速度，提高数据库性能

一、索引机制

建议看韩顺平MySQL P63索引机制

二、创建索引

1.索引的类型：

[1]主键索引：主键本身就是一个索引，为主索引

[2]唯一索引：字段是唯一的，同时也是索引，为unique索引

[3]普通索引：

[4]全文索引:[适用于MySAM]建立在文章上。开发中通常不使用MySQL自带的全文索引而是使用Solr和ElasticSearch

2.查询表是否有索引 show index from 表名

3.添加索引

（1）添加唯一索引

create unique index 索引名 on 表名（列名）

（2）添加普通索引

[1]create index 索引名 on 表名（列名）

[2]alter table 表名 add index 索引名 （列名）

如何选择：如果某列的值不会重复的，优先考虑使用unique索引，否则使用普通索引

（3）添加主键索引

[1]建表的时候直接在列名 列类型后加primary key

[2]建完表后，alter table 表名 add primary key（列名）

4.删除索引

（1）删除索引 drop index 索引名 on 表名

（2）删除主键索引 alter table 表名 drop primary key

5.修改索引

删除+添加新的（朴实无华）

6.查询索引

（1）show index from 表名

（2）show indexes from 表名

（3）show keys from 表名

（4）desc 表名（较为粗狂，没有前三个细）

7.创建索引的规则（哪些列上适合使用索引）

（1）较频繁的作为查询条件字段应该创建索引

（2）唯一性太差的字段不适合2单独创立索引（如性别）

（3）更新频繁的字段不适合建索引（如登录次数）

（4）不会出现在where子句中（即不会作为查询条件）字段不该创建索引

视图

视图需求：表的列信息很多，有些信息是个人重要信息，我们希望某个用户只能查询该表的其他信息而看不到这些重要信息，可以使用视图。

一、基本原理

1.视图是一个虚拟的表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表（基表）

2.对视图的总结：

[1]视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表

[2]视图也有列，数据来自基表

[3]通过视图可以修改基表的数据

[4]基表的改变，也会影响到视图的数据

二、视图的基本使用

1.创建视图： create view 视图名 as select 语句

2.修改视图： alter view 视图名 as select 语句

3.显示怎样创建视图： show create view 视图名

4.删除视图： drop view 视图名1[，视图名2...]

5.查看视图结构： desc 视图名

三、视图细节

1.创建视图后，到数据库去看，对应视图只有一个视图结构文件（视图名.frm）

2.视图的数据变化会影响到基表，基表的数据变化也会影响到视图

因为视图与基表数据是共享的（其实都是基表的数据），任何一方修改都会产生共同的影响。

3.视图中可以再使用视图，数据也是来源基表

create view 视图名2 as select 列名序列 from 视图名1

四、视图最佳实践

1.安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。

2.性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。（多表查询 ...as select （列名序列） from 表1，表2 where 条件）

3.灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

MySQL管理

一、MySQL用户管理

当我们做项目开发时，可以根据不同的开发人员，赋给他们相应的MySQL操作权限，所以MySQL数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用。

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）不相同

1.mysql中的用户，都存储在数据库mysql中user表

2.其中user表的重要字段有：

（1）host：允许登录的”位置“，local host表示该用户只允许本机登录，也可以指定IP地址，比如：192.168.1.100 将来这个用户只能在这个IP登录

（2）user：用户名

（3）authentication_string：密码，是通过mysql的password（）函数加密之后的密码，并不是直接存放各个用户的密码

3.创建用户： create user '用户名' @ ‘允许登录位置’ identified by '密码'

创建用户，同时指定密码

4.删除用户： drop user '用户名' @'允许登录位置'

我们认为 '用户名' @'允许登录位置' 是一个完整的用户信息

5.用户修改密码

alter user '用户名'@'允许登陆位置' identified by '密码'

二、MySQL权限管理

权限是针对某个用户，在某个库里的某个数据对象

1.给用户授权（root用户）

（1）基本语法：grant 权限列表 on 库.对象名 to '用户名'@'允许登陆位置' [identified by '密码']

（2）说明：

[1]权限列表，多个权限用逗号分开

grant select on ....

grant select，delete，create on ...

grant all [privileges] on .... //表示授予该用户在该对象上的所有权限

[2]特别说明

*. * ：代表系统中的所有数据库的所有对象（表，视图，存储过程）

库.*：代表某个数据库中的所有数据对象（表，视图，存储过程）

[3] identified by '密码' 可以省略，也可以写出

如果用户存在，就是在赋权限的同时修改密码；如果用户不存在，就是赋权限的同时创建用户

2.回收用户权限

基本语法：revoke 权限列表 on 库.对象名 from '用户名'@'允许登录位置'；

3.权限生效指令

如果权限没有生效，可以执行下面命令来刷新（仅针对版本较低的MySQL，5.7以上不需要了）

flush privileges

4.细节：

（1）grant to、revoke from

（2）在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有连接权限

（3）也可以这样指定，create user '用户名'@'192.168.1.%' 表示用户在192.168.1.*的IP可以登录mysql

（4）在删除用户的时候，如果host不是%，需要明确指定，'用户'@'允许登录位置' 即：drop user 用户名，默认就是drop user '用户名'@'%'