一篇文章搞懂Mysql(干货!)

一篇文章搞懂Mysql(干货!)

 

MySQL命令：

（1）查看当前服务器中的数据库

show databases;

大小写不区分，分号";"表示结束

（2）查看数据库中包含的表

use 数据库名;

show tables;

（3）查看表的结构（字段）

use 数据库名;

describe [数据库名.]表名;

可缩写成：desc 表名;

4. 创建及删除数据库和表

（1）创建新的数据库

create database 数据库名;

（2）创建新的表

create table 表名 (字段1 数据类型,字段2 数据类型[,...][,parmary key (主键名)]);

主键一般选择能代表唯一性的字段，不允许取空值（NULL），一个表只能有一个主键。

create database test;

use test;

CREATE TABLE test1 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));

desc test1;

（3）删除指定的数据表

drop table [数据库名.]表名;

如不用use进入库中，则需加上数据库名

（4）删除指定的数据库

drop database 数据库名;

5. 管理表中的数据记录

（1）向数据表中插入新的数据记录

方法1：

insert into 表名(字段1，字段2[,...]) values(1,'zhangsan',70.5,PASSWORD('123456'));

PASSWORD('123456')：查询数据记录时，密码字串以加密形式显示，若不使用PASSWORD()，查询时以明文显示

insert into test1(id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456')));

select * from test1;

方法2：

insert into 表名 values(2,'lisi',90,654321);

insert into test1 values(2,'lisi',90,654321);

select * from test1;

（2）查询数据记录

select 字段名1,字段名2[,...] from 表名 [where 条件表达式];

例如：

select * from test1;

查看完整表

select id,name,score from test1 where id=2;

select id,score,name from test1 where id=2;

按照指定顺序显示id等于2的行

select name from test1\G

以列表方式纵向显示

select * from test1 limit 2;

只显示前2行

select * from test1 limit 2,3;

显示第2行后的前3行

（3）修改、更新数据表中的数据记录

update 表名 set 字段1=字段值1[,字段2=字段值2,...] [where 条件表达式];

例如：

update test1 set passwd=PASSWORD('') where name='zhangsan';

 

select * from test1;

update test1 set name='wangwu',passwd='' where id=2;

select * from test1;

（4）在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式];

例如：

delete from test1 where id=2;

select * from test1;

6. 修改表名和表结构

（1）修改表名

alter table 旧表名 rename 新表名;

例如：

alter table test1 rename test_table1;

show tables;

（2）扩展表结构（增加字段）

alter table 表名 ADD address varchar(50) default '地址不详';

deafult '地址不详'：表示此字段设置默认值"地址不详"，可与NOT NULL配合使用

例如：

alter table test_table1 ADD address varchar(50) default '地址不详';

select * from test_table1;

（3）修改字段（列）名，添加唯一键

alter table 表名 change 旧列名 新列名 数据类型 [unique key];

change可修改字段名、数据类型、约束等所有项

例如：

alter table test_table1 change name user_name varchar(10) unique key;

desc test_table1;

 

（4）删除字段

alter table 表名 drop 字段名;

例如：

alter table test_table1 drop passwd;

desc test_table1;

（5）案例扩展

use test;

create table if not exists info (

id int(4) zerofill primary key auto_increment,

name varchar(10) not null,

cardid int(18) not null unique key,

hobby varchar(50));

 

desc info;

if on exists：表示检测要创建的表是否已存在，如果不存在就继续创建

int(4) zerofill：表示若数制不满4位数，则前面用"0"填充，例如0001

auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复;自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

unique key：表示此字段唯一键约束，此字段数据不可以重复，一张表中只能有一个主键，但是可以多多个唯一键

not null：表示此字段不允许为null

 

一、数据表高级操作

1. 克隆表，将数据表的数据记录生成到新的表中

方法一：

create table test1 like test_table1;

通过like方法，复制test_table1表结构生成test1表

insert into test1 select * from test_table1;

将test_table1表中的数据复制到test1表中

show tables;

+----------------+

| Tables_in_test |

+----------------+

| info |

| test_table1 |

+----------------+

2 rows in set (0.00 sec)

create table test1 like test_table1;

desc test1;

+-----------+--------------+------+-----+--------------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+--------------+------+-----+--------------+-------+

| id | int(11) | NO | PRI | NULL | |

| user_name | varchar(10) | YES | UNI | NULL | |

| score | decimal(5,2) | YES | | NULL | |

| address | varchar(50) | YES | | 地址不详 | |

+-----------+--------------+------+-----+--------------+-------+

4 rows in set (0.00 sec)

 

insert into test1 select * from test_table1;

select * from test1;

+----+-----------+-------+--------------+

| id | user_name | score | address |

+----+-----------+-------+--------------+

| 1 | zhangsan | 70.50 | 地址不详 |

+----+-----------+-------+--------------+

1 row in set (0.00 sec)

 

方法二：

create table test2 (select * from test1);

show create table test2\G

获取数据表的表结构、索引等信息

select * from test2;

create table test2 (select * from test1);

show create table test2\G

select * from test2;

+----+-----------+-------+--------------+

| id | user_name | score | address |

+----+-----------+-------+--------------+

| 1 | zhangsan | 70.50 | 地址不详 |

+----+-----------+-------+--------------+

1 row in set (0.00 sec)

2. 清空表，删除表内的所有数据

方法一：

delete from test_table1;

delete清空表后，返回的结果内有删除的记录条目，delete工作时是一行一行的删除记录数据的，如果表中有自增长字段，使用delete from删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录

delete from test_table1;

Query OK, 1 row affected (0.00 sec)

select * from test_table1;

Empty set (0.00 sec)

方法二：

truncate table test2;

truncate清空表后，没有返回被删除的条目，truncate工作时是将表结构按原样重新建立，因此在速度上truncate会比delete清空表块，使用truncate table清空表内数据后，ID会从1开始重新记录

truncate table test2;

Query OK, 0 rows affected (0.01 sec)

select * from test2;

Empty set (0.00 sec)

3. 创建临时表

create temporary table 表名 (字段1 数据类型，字段2 数据类型[,...] [,primary key (主键名)]);

临时表创建成功之后，使用show tables命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以执行增删改查等操作，比如使用drop table语句手动直接删除临时表。

create temporary table test3 (

id int(4) zerofill primary key auto_increment,

name varchar(10) not null,

cardid bigint(18) not null unique key,

hobby varchar(50));

 

insert into test3 values(1,'zhangsan',112233445566778899,'running');

 

select * from test3;

+------+----------+------------------------+---------+

| id | name | cardid | hobby |

+------+----------+------------------------+---------+

| 0001 | zhangsan | 112233445566778899 | running |

+------+----------+------------------------+---------+

1 row in set (0.00 sec)

show tables;

+----------------+

| Tables_in_test |

+----------------+

| info |

| test1 |

| test2 |

| test_table1 |

+----------------+

4 rows in set (0.00 sec)

quit

Bye

[root@localhost ~]# mysql -u root -p abc123

Enter password:

mysql> select * from test.test3;

ERROR 1146 (42S02): Table 'test.test3' doesn't exist

4. 创建外键约束，保证数据的完整性和一致性

外键的定义：如果同一个属性字段x在表中是主键，而在表二中不是主键，则字段x称为表二的外键。

主键表和外键表的理解：

（1）以公共关键字做关键的表为主键表（父表、主表）

（2）以公共关键字做外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键，要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

 

创建主表

create table 主表名 (主键字段 数据类型,字段2 数据类型);

create table profession (pid int(4),proname varchar(50));

desc profession;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| pid | int(4) | YES | | NULL | |

| proname | varchar(50) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

 

创建从表

create table 从表名 (字段1 数据类型,字段2 数据类型,字段3 数据类型,外键字段 数据类型);

create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));

desc student;

 

为主表添加一个主键约束，主键名建议以"PK_"开头

alter table 主键表名 add [constraint PK_主键名] primary key (主键字段);

alter table profession add constraint PK_pid primary key (pid);

 

desc profession;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| pid | int(4) | NO | PRI | NULL | |

| proname | varchar(50) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

 

为从表添加外键，并将从表的外键字段和主表的主键字段建立外键关联。外键名建议以"FK_"开头

alter table 外键表名 add [constraint FK_主键名] foreign key (外键字段) references 主键表名 （主键字段）;

alter table student add constraint FK_pro foreign key (proid) references profession (pid);

desc student;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| name | varchar(10) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| proid | int(4) | YES | MUL | NULL | |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

插入新的数据记录时，要先主表再从表

主表赋值

insert into profession values(1,'数学');

insert into profession values(2,'英语');

select * from profession;

+-----+---------+

| pid | proname |

+-----+---------+

| 1 | 数学 |

| 2 | 英语 |

+-----+---------+

2 rows in set (0.00 sec)

从表赋值

 

insert into student values(1,'zhangsan',18,1);

insert into student values(2,'lisi',19,1);

insert into student values(3,'wangwu',20,2);

insert into student values(4,'zhaoliu',20,4);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test"."student", CONSTRAINT "FK_pro" FOREIGN KEY ("proid") REFERENCES "profession" ("pid"))

select * from student；

+----+----------+------+-------+

| id | name | age | proid |

+----+----------+------+-------+

| 1 | zhangsan | 18 | 1 |

| 2 | lisi | 19 | 1 |

| 3 | wangwu | 20 | 2 |

+----+----------+------+-------+

3 rows in set (0.00 sec)

删除数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。

mysql> delete from student where proid=1;

Query OK, 2 rows affected (0.01 sec)

 

mysql> delete from profession where pid=1;

Query OK, 1 row affected (0.00 sec)

 

mysql> delete from profession where pid=2;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ("test"."student", CONSTRAINT "FK_pro" FOREIGN KEY ("proid") REFERENCES "profession" ("pid"))

 

mysql> select * from student;

+----+--------+------+-------+

| id | name | age | proid |

+----+--------+------+-------+

| 3 | wangwu | 20 | 2 |

+----+--------+------+-------+

1 row in set (0.00 sec)

 

mysql> select * from profession;

查看和删除外键约束

show create table student;

desc student;

alter table student drop foreign key FK_pro;

alter table student drop key FK_pro;

desc student;

 

5. MySQL中6种常见的约束

常见约束    说明

主键约束（primary key）    用于保证该字段的值具有唯一性并且非空

外键约束（foreign key）    用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值

非空约束（not null）    用于保证该字段的值不能为空

唯一性约束（unique [key    index]）

默认值约束（default）    用于保证该字段有默认值

自增约束（auto_increment）    用于保证该字段的值是上一条的+1值，也可以自定义值，下一条将在该值基础上+1。可以约束任何一个字段，该字段不一定是主键字段，但主键字段一定是自增字段，即primary_key要与auto_increment一起作用于同一个字段

二、 数据库用户管理

1. 新建用户

create user '用户名'@'来源地址' [identified by [password] '密码'];

'用户名'：指定将创建的用户名

'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%

'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由MySQL自动加密；若使用加密密码，需要先使用select password('密码')获取该密码的密文，再在语句中添加password '密文'；若省略'identified by'部分，则用户的密码将为空（不建议使用）

mysql> create user 'user1'@'localhost' identified by '123456';

mysql> select password('123456');

+-------------------------------------------+

| password('123456') |

+-------------------------------------------+

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-------------------------------------------+

1 row in set, 1 warning (0.00 sec)

 

mysql> create user 'user2'@'%' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

 

2. 查看用户信息

创建后的用户保存在mysql数据库的user表里

use mysql;

select user,authentication_string,host from user;

 

3. 重命名用户

rename user '旧用户名'@'旧来源地址' to '新用户名'@'新来源地址';

注：'新用户名'@'新来源地址'不可与已有账户冲突

rename user 'user1'@'localhost' to 'zhangsan'@'%';

select user,authentication_string,host from user;

rename user 'zhangsan'@'%' to 'user2'@'%';

 

4. 删除用户

drop user '用户名'@'来源地址';

drop user 'zhangsan'@'%';

select user,authentication_string,host from user;

 

5. 修改密码

（1）修改其他用户密码

set password for '用户名'@'来源地址' = password('新密码');

set password for 'user2'@'%' = password('654321');

select user,authentication_string,host from user;

（2）修改当前用户密码

set password = '新密码'

set password = '';

select user,authentication_string,host from user;

 

6. 忘记密码登录改密

vim /etc/my.cnf

#在mysqld参数下插入以下内容,表示登录时跳过授权表。当忘记账号密码时可以使用该参数修改密码，但是要随用随关，重启mysql，不然服务器上会有很大的风险。

skip-grant-tables

systemctl restart mysqld

mysql

use mysql;

update user set authentication_string=password('123456') where user='root';

flush privileges;

select user,authentication_string,host from user;

一、数据库用户授权

1. 授予权限

grant语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，grant语句将会创建新的用户；当指定的用户名存在时，grant语句用于修改用户信息。

grant 权限列表 on 数据库名.数据表名 to '用户名'@'来源地址' [identified by '密码'];

权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如"select,insert,update"。使用"all"表示所有权限，可授权执行任何操作。

数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用"*.*"b表示授权操作的对象为所有数据库中的所有表。

'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里链接。来源地址可以是域名、IP地址，还可以使用"%"通配符，表示某个区域或网段内的所有地址，如"%.test.com"、"192.168.122.%"等。

identified by：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略"identified by"部分，则用户的密码将为空。

grant select on test.* to 'zhangsan'@'localhost' identified by '123456';

#允许用户zhangsan在本地查询test数据库中所有表的数据记录，但禁止查询数据库中的表的记录。

grant all on *.* to 'lisi'@'%' identified by '123456';

#允许用户lisi在所有终端远程连接mysql，并拥有所有权限。

flush privileges;

#刷新权限

2. 查看权限

方法一：

show grants for '用户名'@'来源地址';

show grants for zhangsan@localhost;

show grants for lisi@'%';

方法二：

select * from mysql.user where user='用户名' and host='来源地址'/G;

select * from mysql.user where user='zhangsan' and host='localhost'\G;

select * from mysql.user where user='lisi' and host='%'\G;

3. 删除权限

revoke 权限 on 数据库名.数据表名 from '用户名'@'来源地址';

revoke all on test.* from zhangsan@localhost;

show grants for zhangsan@localhost;

+----------------------------------------------+

| Grants for zhangsan@localhost |

+----------------------------------------------+

| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |

+----------------------------------------------+

1 row in set (0.00 sec)

#权限删除后，仍会有允许用户登录的权限存在

 

revoke update on *.* from lisi@'%';

show grants for lisi@'%';

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for lisi@% |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'lisi'@'%' |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

4. 全部权限（all privileges）

all privileges所含权限    功能

select    查询数据

insert    插入数据

update    更新数据

delete    删除数据

create    创建库/表

drop    删除库/表

reload    重载，可使用flush语句进行刷新操作

shutdown    关闭MySQL服务

process    显示或杀死属于其他用户的服务线程

file    在MySQL服务器上读写文件

references    建立外键约束

index    建立索引

alter    更改表属性

show databases    查看全局数据库

super    允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS

create temporary tables    创建临时表

lock tables    锁表

execute    执行存在的函数和程序

replication slave    查看从服务器，从主服务器读取二进制日志

replication client    查询主服务器、从服务器状态

create view    创建视图

show view    显示视图

create routine    创建存储过程

create user    创建用户

event    时间

trigger    创建触发器

create tablespace    创建表空间

注：    

mysql --version

mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper

不同版本的权限列表不同，以上仅以5.7.20为例。

 

二、MySQL索引

1. 索引的概念

● 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）

● 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度

● 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容

● 索引是表中一列或者若干列值排序的方法

● 建立索引的目的是加快对表中记录的查找或排序

 

2. 索引的作用

● 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因

● 当表很大或查询设计到多个表时，使用索引可以成千上万倍地提高查询速度

● 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本

● 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性

● 可以加快表与表之间的连接

● 在使用分组和排序时，可大大减少分组和排序的时间

● 建立索引在抖索和恢复数据库中的数据时能显著提高性能

 

3. 索引的副作用

● 索引需要占用额外的磁盘空间

对于MyISAM引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址

而InnoDB引擎的表数据文件本身就是索引文件

● 在插入和修改数据时要花费更多的额时间，因为索引也要随之变动

 

4. 创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

● 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位

● 记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能

● 经常与其他表进行连接的表，在连接字段上应该建立索引

● 唯一性太差的字段不适合建立索引

● 更新太频繁的字段不适合创建索引

● 经常出现在where字句中的字段，特别是大表的字段，应该建立索引

● 在经常进行group by、order by的字段上建立索引

● 索引应该建在选择性高的字段上

● 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

 

5. 索引的分类和创建

新建实验表

use test;

desc member;

 

（1）普通索引

普通索引：最基本的所有类型，没有唯一性之类的限制

①直接创建索引

create index 索引名 on 表名 (列名[(length)]);

● (列名[(length)])：length是可选项，下同。如果省略length的值，则使用整个列的值作为索引。如果指定，使用列的前length个字符来创建索引，这样有利于减小索引文件的大小。在不损失精确性的情况下，长度越短越好。

● 索引名建议以"_index"结尾。

create index name_index on member (name);

desc member;

 

②修改表方式创建

alter table 表名 add index 索引名 (列名);

alter table member add index cardid_index (cardid);

desc member;

 

③创建表的时候指定索引

create table 表名 (字段1 数据类型,字段2 数据类型[,...]，index (列名));

create table test (id int,name varchar(10),index name_index (name));

desc test;

（2）唯一索引

唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

①直接创建唯一索引

create unique index 索引名 on 表名(列名);

create unique index phone_index on member(phone);

desc member;

 

②修改表方式创建

alter table 表名 add unique 索引名 (列名);

alter table member add unique add_index (address);

desc member;

③创建表的时候指定

create table 表名 (字段1 数据类型,字段2 数据类型[,...],unique 索引名 (列名));

create table test (id int,name varchar(10),cardid bigint(18),unique cardid_index (cardid));

desc test;

（3）主键索引

主键索引：是一种特殊的唯一索引，必须指定为"primary key"。一个表只能有一个主键索引，不允许有空值。添加主键将自动创建主键索引。

①创建表的时候指定

create table 表名 ([...],primary key (列名));

create table test (id int,name varchar(10),primary key(id));

desc test;

或

create table 表名 ([...],主键字段 数据类型 primary key[;...]);

create table test (id int primary key,name varchar(10));

desc test;

②修改表方式创建

alter table 表名 add primary key (列名);

alter table member add primary key (id);

desc member;

（4）组合索引

组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的所有。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select语句查询时where条件使用的字段顺序必须和组合索引中的排序一直，否则索引将不会生效。

①创建：

create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,index 索引名 (列名1,列名2,列名3));

create table menu (id int,foodname varchar(20),price int,index foodprice_index (id,foodname,price));

desc menu;

show create table menu;

②查询：

select * from 表名 where 列名1='...' and 列名2='...' and 列名3='...';

insert into menu values(1,'鱼香肉丝',28);

insert into menu values(2,'麻婆豆腐',18);

insert into menu values(3,'水煮肉片',38);

insert into menu values(4,'辣子鸡',38);

select * from menu;

select * from menu where price=38 and foodname='辣子鸡';

select * from menu where price=38 and id=3;

（5）全文索引

全文索引（fulltext）：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在MySQL5.6版本以前，fulltext索引仅可用于MyISAM引擎，在5.6版本之后innodb引擎也支持fulltext索引。全文索引可以在char、varchar或者text类型的列上创建。每个表只允许有一个全文索引。

①直接创建索引

create fulltext index 索引名 on 表名 (列名);

create fulltext index remark_index on member (remark);

desc member;

show create table member;

②修改表方式创建

alter table 表名 add fulltext 索引名 (列名);

drop index remark_index on member;

 

desc member;

alter table member add fulltext remark_index (remark);

desc member;

③创建表的时候指定索引

create table 表名 (字段1 数据类型[,...],fulltext 索引名 (列名));

数据类型只可为char、varchar、text。

create table staff_info (id int(4),name char(10),cardid bigint(18),age int(3),phone bigint(11),remark text, fulltext remark_index (remark));

desc staff_info;

show create table staff_info;

④使用全文索引查询

select * from 表名 where match(列名) against('查询内容');

insert into staff_info values (1,'zhangsan',112233445566778899,23,13111111111,'this is chairman');

insert into staff_info values (2,'lisi',212233445566778899,33,13222222222,'this is ceo');

insert into staff_info values (3,'wangwu',312233445566778899,43,13333333333,'this is cfo');

insert into staff_info values (4,'zhaoliu',412233445566778899,44,13444444444,'this is hr');

 

select * from staff_info;

select * from staff_info whereremark) against('ceo');

6. 查看索引

show index from 表名;

show keys from 表名;

 

show index from member;

 

show keys from member;

一般建议使用\G纵向查看

show index from 表名\G;

show keys from 表名\G;

show keys from member\G;

 

7. 删除索引

（1）直接删除索引

drop index 索引名 on 表名;

drop index name_index on member;

desc member;

 

（2）修改表方式删除索引

alter table 表名 drop index 索引名;

alter table member drop index cardid_index;

desc member;

 

（3）删除主键索引

alter table 表名 drop primary key;

alter table member drop primary key;

desc member;

8. 案例

比如为某商场做一个会员卡系统。这个系统有一个会员表，有下列字段：

● 会员编号 int(10)

作为主键，使用primary key

● 会员姓名 varchar(10)

建立普通索引

● 会员身份证号码 varchar(18)

建立唯一索引

● 会员电话 bigint(11)

● 会员住址 varchar(50)

● 会员备注信息 text

建立fulltext，全文索引。不过fulltext用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的index也可以

create table vip (id int(10),name varchar(10),cardid varchar(18),phone bigint(11),address varchar(50),remark text);

alter table vip add primary key(id);

create index name_index on vip (name);

create unique index cardid_index on vip(cardid);

alter table vip add fulltext remark_index (remark);

desc vip;

show index from vip\G;

一、事务

1. 事务的概念

● 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
● 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
● 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
● 事务通过事务的整体性以保证数据的一致性。
● 事务能够提高在向表中更新和插入信息期间的可靠性。
总结来说，事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

2. 事务的ACID特点

ACID是指在可靠数据库管理系统（DBMS）中，事务（transaction）应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这是可靠数据库所应具备的几个特性。
在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果、

（1）原子性

原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
事务是一个完整的操作，事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

案例：
A给B转账100元钱的时候，只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行。

（2）一致性

一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
当事务完成时，数据必须处于一致状态。
当事务开始前，数据库中存储的数据处于一致状态。
当正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到已知的一致状态。

案例：
对银行转账事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

（3）隔离性

隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

①事务之间的相互影响

事务之间的相互影响分为以下几种

1.脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。

2.不可重复读：一个事务内两个相同的查询却返回了不同数据，这是由于查询时系统中其他事务修改的提交而引起的。

3.幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好像发生了幻觉一样。

4.丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

②MySQL事务支持的四种隔离

1. 未提交读（Read Uncommitted）
   允许脏读，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据。
2. 提交读（Read Committed）
   只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别（不重复读）。
3. 可重复读（Repeated Read）
   可重复度。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响。MySQL默认使用该隔离级别。
4. 串行读（Serializable）
   完全串行化的读，每次读都需要获得表级共享锁，读写相互都会堵塞，相当于锁表。

③主流数据库的默认隔离级别

主流数据库	默认隔离级别
MySQL	repeatable read可重复读
Oracle	read committed提交读
SQL Server	read committed提交读

④查询全局事务隔离级别

show global variables like '%isolation%';
select @@global.tx_isolaion;

⑤查询会话事务隔离级别

show session variables like '%isolation%';
select @@session,tx_isolation;
select @@tx_isolation;

⑥设置全局事务隔离级别

set global transaction isolation level read committed;

⑦设置会话事务隔离级别

set session transaction isolation level read committed;

（4）持久性

持久性：在事务完成以后，该事务对数据库所做的更改便持久的保存在数据库之中，并不会被回滚。
不管系统是否发生故障，事务处理的结果都是永久的。
一旦事务被提交，事务的效果会被永久的保留在数据库中。

3. 事务控制语句

begin 或 start transaction：显式的开启一个事务
commit 或 commit work：提交事务，并使已对数据库进行的所有修改变为永久性。
rollback 或 rollback work：回滚会结束用户的事务，并撤销正在进行的所有未提交的数据。
savepoint S1：使用savepoint允许在事务中创建一个回滚点，一个事务中可以有多个savepoint，"S1"代表回滚点名称。savepoint的作用类似于游戏中的存档。
rollback to [savepoint] S1：把事务回滚到标记点。类似于游戏中的读取存档。

（1）案例

create table account (

id int(10) primary key not null,

name varchar(40),

money double

);

desc account;

insert into account values(1,'A',1000);

insert into account values(2,'B',1000);

select * from account;

（2）测试提交事务

①不提交测试

begin;

update account set money=money-100 where name='A';

select * from account;

quit

mysql

use test;

select * from account;

②提交测试

begin;

update account set money=money-100 where name='A';

commit;

quit;

mysql

select * from test.account;

（3）测试回滚事务

begin;

update test.account set money=money+100 where name='A';

select * from test.account;

rollback;

select * from test.account;

（4）测试多点回滚

begin;

select * from test.account;

update test.account set money=money+100 where name='A';

savepoint s1;

select * from test.account;

update test.account set money=money+100 where name='B';

savepoint s2;

select * from test.account;

insert into test.account values(3,'C',1000);

select * from test.account;

rollback to s1;

select * from test.account;

mysql

mysql> select * from test.account;

4. 使用set设置控制事务

set autocommit=0; #禁止自动提交
set autocommit=1; #开启自动提交，MySQL默认为1
show variables like 'autocommit'; #查看MySQL中的autocommit值

如果没有开启自动提交，当前会话连接的MySQL的所有操作都会当成一个事务直到你输入rollback或commit，当前事务才算结束。当前书屋结束前新的MySQL连接时，无法读取到任何当前会话的操作结果。
如果开启了自动提交，MySQL会把每个sql语句当成一个事务，然后自动的commit。
当然，无论开启与否，begin;commit|rollback;都是独立的事务。

（1）autocommit=0

select * from test.account;

set autocommit=0;

update test.account set money=money+100 where name='B';

select * from test.account;

quit;

mysql

select * from test.account;

（2）autocommit=1

select * from test.account;

set autocommit=1;

update test.account set money=money+100 where name='B';

select * from test.account;

quit;

mysql

select * from test.account;

（3）查看autocommit

show variables like 'autocommit';

二、MySQL存储引擎

1. 存储引擎的概率

● MySQL中的数据用各种不同的技术存储在文件中，每一种技术都是用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。
● 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。
● MySQL常用的存储引擎为：MyISAM/InnoDB。
● MySQL数据库中的组件，负责执行实际的数据I/O操作。
● MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

2. MyISAM

（1）MyISAM的特点

● MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。
● 访问速度快，对事务完整性没有要求
● MyISAM适合查询、插入为主的应用
● MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
.frm文件存储表结构定义
.MYD(MYData)为数据文件的扩展名
.MYI(MYIndex)为索引文件的扩展名
● 表级锁定形式，数据在更新时锁定整个表
● 数据库在读写过程中相互阻塞
会在数据写入的过程中阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
● 数据单独写入或读取，速度过程较快且占用资源相对少
● MyISAM支持的存储格式
静态表
动态表
压缩表

（2）MyISAM支持的存储格式

①静态（固定长度）表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。

②动态表

动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行optimize table语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

③压缩表

压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

（3）MyISAM适用的生产场景分析

● 公司业务不需要事务的支持
● 单方面读取或写入数据比较多的业务
● MyISAM存储引擎数据读写都比较频繁的场景不适合
● 适用读写并发访问相对较低的业务
● 数据修改相对较少的业务
● 对数据业务一致性要求不是非常高的业务
● 服务器硬件资源相对比较差

3. InnoDB

（1）InnoDB特点介绍

● 支持事务，支持4个事务隔离级别
● MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
● 读写阻塞与事务隔离级别相关
● 能非常高效的缓存索引和数据
● 表与主键以簇的方式存储
● 支持分区、表空间，类似于Oracle数据库
● 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
● 对硬件资源要求还是比较高的场合
● 行级锁定，但是全表扫描仍然会是表级锁定，如update table set a=1 where user like '%zhang%';
● InnoDB中不保存表额行数，如select count(*) from table;时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。需要注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表
● 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引
● 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

（2）InnoDB适用生产场景分析

● 业务需要食物的支持
● 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
● 业务数据更新较为频繁的场景，如论坛、微博等
● 业务数据一致性要求较高，如银行业务
● 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力

4. MyISAM与InnoDB的区别

对比项	MyISAM	InnoDB
事务和外键的支持	不支持事务和外键	支持事务和外键
锁表状态	表级锁定	行级锁定（用like模糊匹配全表扫描时是表级锁定）
并发读写能力	不支持MVCC（多版本并发控制，实现了读-写、写-读的并发执行）	支持MVCC
全文索引的支持	支持全文索引	5.5版本之前不支持全文索引，5.5之后支持
硬件要求	对硬件要求相对较低，资源消耗较低	对硬件要求较高，特别是内存，能提高缓存能力
存储格式	1. 表名.frm（文件存储表结构） 2. 表名.MYD（MYData数据文件） 3. 表名.MYI（MYIndex索引文件）	1. db.opt（表属性文件） 2. 表名.frm（表结构文件） 3. 表名.ibd（表数据元数据）
适用场景	MyISAM适用于如资料档案室、商品仓库等数据修改较少，以读为主，单独读取和插入并且不支持事务的场景	InnoDB适用于如微博、论坛等读写较多，一致性要求高并且支持事务的场景
读写侧重	MyISAM更注重于读	InnoDB更注重于写
搜查响应速度	MyISAM搜索、访问速度快	InnoDB稍慢
数据完整性	MyISAM对完整性无要求（不支持事务，ACID特点）	InnoDB具有很好的完整性（支持事务，ACID特点）

5. 企业选择存储引擎的依据

需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
● 支持的字段和数据类型
所有引擎都支持通用的数据类型
但不是所有的引擎都支持其他的字段类型，如二进制对象
● 锁定类型：不同存储引擎支持不同级别的锁定
表锁定：MyISAM支持
行锁定：InnoDB支持
● 索引的支持
建立索引在搜索和恢复数据库中的数据时能显著提高性能
不同的存储引擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
● 事务处理的支持
提高在向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储引擎

6. 对于引擎的操作

（1）查看系统支持的存储引擎

show engines;

（2）查看表使用的存储引擎

方法一：
show table status from 库名 where name='表名'\G

show table status from test where name='account'\G

方法二：

use 库名;

show create table 表名;

use test;

show create table account;

（3）修改存储引擎

①通过alter table修改

use 库名;
alter table 表名 engine=指定引擎;

use test;

alter table account engine=MyISAM;

show table status from test where name='account'\G

②通过修改/etc/my.cnf配置文件，指定默认存储引擎并重启服务

vim /etc/my.cnf

 

......

[mysqld]

......

default-storage-engine=INNODB

 

systemctl restart mysqld.service

mysql

use test;

create table engine_test(id int,engine_name varchar(30));

 

show create table engine_test;

注：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

③通过create table创建表时指定存储引擎

use 库名;

create table 表名(字段1 数据类型[,...]) engine=MyISAM;

create table engine_test(id int,engine_name varchar(30)) engine=MyISAM;

一、MySQL日志管理

1. MySQL日志路径

MySQL的日志默认保存位置为/usr/local/mysql/data

2. 设置、修改日志路径

MySQL日志路径可在MySQL配置文件（/etc/my.cnf）中进行设置、修改。

vim /etc/my.cnf

 

[mysqld]

##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

log-error=/usr/local/mysql/data/mysql_error.log    #指定日志的保存位置和文件名

 

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

 

##二进制日志（binlog），用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

log-bin=mysql-bin                                #也可以log_bin=mysql-bin

##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5                                #设置超过5秒执行的语句被记录，缺省时为10秒

systemctl restart mysqld

3. 查询日志功能是否开启

mysql

show variables like 'general%';

##查看通用查询日志是否开启

show variables like 'log_bin%';

##查看二进制日志是否开启

show variables like '%slow%';

##查看慢查询日志功能是否开启

show variables like 'long_query_time';

##查看慢查询时间设置

set global slow_query_log=ON;

##在数据库中设置开启慢查询的方法

二、MySQL备份与恢复

1. 数据备份的重要性

备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因：

● 程序错误

● 人为操作错误

● 运算错误

● 磁盘故障

● 灾难（如火灾、地震）和盗窃

2. 数据库备份的分类

（1）备份对象角度

从物理与逻辑的角度，备份可分为物理备份和逻辑备份

 

①物理备份

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份的方法：

● 冷备份（脱机备份）：是在关闭数据库的时候进行的

● 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

● 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

 

②逻辑备份

对数据库逻辑组件（如表等数据库对象）的备份

 

（2）备份策略角度

从数据库的备份策略角度，备份可分为完全备份、差异备份以及增量备份

 

①完全备份

每次对数据库进行完整的备份。

会导致备份文件占用空间巨大，并且有大量的重复数据。

恢复时，直接使用完全备份的文件即可。

 

②差异备份

备份自从上次完全备份之后被修改过的文件。

每次差异备份，都会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用额外的磁盘空间。

恢复时，先恢复完全备份的数据，再恢复差异备份的数据。

 

③增量备份

只有在上次完全备份或者增量备份后被修改的文件才会被备份。

每次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间。

恢复数据，需要按照次序恢复完全备份和增量备份的数据。

 

3. 常见的备份方法

（1）物理备份

备份时数据库处于关闭状态，直接打包数据库文件。

备份速度快，恢复时也是最简单的。

 

（2）专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

 

（3）启用二进制日志进行增量备份

进行增量备份，需要刷新二进制文件

 

（4）第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

 

4. MySQL完全备份

（1）完全备份的特点

是对整个数据库、数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

InnnoDB存储引擎在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm（表结构文件）、表名.ibd（表数据文件）

 

（2）完全备份的优缺点

优点：

备份与恢复操作简单方便

缺点：

数据存在大量的重复

占用大量的备份空间

备份与恢复时间长

 

（3）完全备份的分类

①物理冷备份与恢复

关闭MySQL数据库

使用tar命令直接打包数据库文件夹

直接替换现有MySQL目录即可

 

②mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份

可以将指定的库、表导出为SQL脚本

使用命令mysql导入备份的数据

 

（4）物理冷备份与恢复

①建表

use test;

create table if not exists test (

id int(4) not null auto_increment,

name varchar(10) not null,

sex char(10) not null,

hobby varchar(50),

primary key (id));

desc test;

insert into test values(1,'zhangsan','male','running');

insert into test values(2,'lisi','female','singing');

select * from test;

②物理冷备份

systemctl stop mysqld

yum install -y xz

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

#压缩备份

③物理冷恢复

删除数据表

systemctl start mysqld

mysql

use test;

show tables;

drop table test;

Query OK, 0 rows affected (0.00 sec)

show tables;

Empty set (0.00 sec)

quit

Bye

systemctl stop mysqld

恢复数据库文件

tar Jxvf /opt/mysql_all_2021-08-31.tar.xz -C /

systemctl start mysqld

mysql

show databases;

use test;

show tables;

select * from test;

（5）mysqldump备份与恢复

①完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -u 用户名 -p[密码] --databases 库名1 [库名2]... > /备份路径/备份文件名.sql

导出的就是数据库脚本文件

例如：

mysqldump -u root -p --databases test > /opt/test.sql

备份test库

cd /opt

ls

mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh

mysql_all_2021-08-31.tar.xz test.sql

mysqldump -u root -p --databases mysql test > /opt/mysql_test.sql

备份mysql库和test库

ls

mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh

mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql

②完全备份MySQL服务器中所有的库

mysqldump -u 用户名 -p[密码] --all-databases > /备份路径/备份文件名.sql

例如：

mysqldump -u root -p --all-databases > /opt/all.sql

备份全部库

ls

all.sql mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql

mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh

③完全备份指定库中的部分表

mysqldump -u 用户名 -p[密码] [-d] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

使用"-d"选项，说明只保存数据库的表结构

不使用"-d"选项，说明表数据也进行备份

例如：

mysqldump -u root -p test test > /opt/test_test.sql

备份test库中的test表

ls

all.sql mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql

mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh test_test.sql

④查看备份文件

grep -v "^--" /opt/test_test.sql | grep -v "^/" | grep -v "^$"

⑤恢复数据库

删除数据库

mysql -u root -p -e 'drop database test;'

#"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出

mysql -u -root -p -e 'show databases;'

恢复数据库

mysql -u root -p < /opt/test.sql

mysql -u -root -p -e 'show databases;'

⑥恢复数据表

删除数据表

mysql -u root -p -e 'drop table test.test;'

mysql -u root -p -e 'show tables from test;'

恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在

mysql -u root -p test < /opt/test_test.sql

mysql -u root -p -e 'show tables from test;'

5. MySQL增量备份与恢复

（1）Mysql增量备份

使用mysqldump进行完全备份存在的问题

● 备份数据中有重复数据

● 备份时间与恢复时间过长

是自上一次备份后增加/变化的文件或者内容

MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

（2）增加备份的特点

没有重复数据，备份量不大，时间段

恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

（3）MySQL二进制日志对备份的意义

● 二进制日志保存了所有更新或者可能更新数据库的操作

● 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

● 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

（4）增量备份

①开启二进制日志功能

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format = MIXED

##可选，指定二进制日志（binlog）的记录格式为MIXED

##二进制日志（binlog）有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW（基于行）、MIXED（混合模式），默认格式是STATEMENT

server-id = 1

systemctl restart mysqld

ls -l /usr/local/mysql/data/mysql-bin.*

-rw-r----- 1 mysql mysql 154 8月 31 21:17 /usr/local/mysql/data/mysql-bin.000001

-rw-r----- 1 mysql mysql 19 8月 31 21:17 /usr/local/mysql/data/mysql-bin.index

②可每周对数据库或表进行完全备份

mysqldump -u root -p test test > /opt/test_test_$(date +%F).sql

#备份test库test表

mysqldump -u root -p --all-databases test > /opt/test_$(date +%F).sql

#备份test库

ls

all.sql mysql_test.sql test_test_2021-08-31.sql

mysql-5.7.20 rh test_test.sql

mysql_all_2021-08-31.tar.xz test_2021-08-31.sql

mysql-boost-5.7.20.tar.gz test.sql

③可每天进行增量备份操作，生成新的二进制日志文件（例如mysql-bin.000003）

mysqladmin -u root -p flush-logs

ls -l /usr/local/mysql/data/mysql-bin.*

-rw-r----- 1 mysql mysql 201 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000001

-rw-r----- 1 mysql mysql 154 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000002

-rw-r----- 1 mysql mysql 38 8月 31 21:19 /usr/local/mysql/data/mysql-bin.index

④插入新数据，以模拟数据的增加或变更

mysql -u root -p -e "insert into test.test values(3,'wangwu','male','game');"

mysql -u root -p -e "insert into test.test values(4,'zhaoliu','female','reading');"

mysql -u root -p -e "select * from test.test;"

⑤再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

ls -l /usr/local/mysql/data/mysql-bin.*

-rw-r----- 1 mysql mysql 201 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000001

-rw-r----- 1 mysql mysql 750 8月 31 21:22 /usr/local/mysql/data/mysql-bin.000002

-rw-r----- 1 mysql mysql 38 8月 31 21:19 /usr/local/mysql/data/mysql-bin.index

⑥查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

--base64-output=decode-rows：使用64位编码机制去解码并按行读取

-v：显示详细内容

（5）增量恢复--一般恢复

①模拟丢失更改的数据的恢复步骤

mysql -u root -p -e 'delete from test.test where id =3;'

mysql -u root -p -e 'delete from test.test where id =4;'

mysql -u root -p -e 'select * from test.test;'

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

mysql -u root -p -e 'select * from test.test;'

②模拟丢失所有数据的恢复步骤

mysql -u root -p -e 'drop table test.test;'

mysql -u root -p test < /opt/test_test_2021-08-31.sql

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

mysql -u root -p -e 'select * from test.test;'

（6）增量恢复--断点备份

①查看二进制文件，确定指令编号、时间

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

# at 294

#210831 21:22:32

##插入了"wangwu"的用户数据

# at 589

#210831 21:22:57

##插入了"zhaoliu"的用户数据

②基于位置恢复

仅恢复到操作ID为"589"之前的数据，即不恢复"zhaoliu"的数据

删除恢复

mysql -u root -p -e 'delete from test.test where id =3;'

mysql -u root -p -e 'delete from test.test where id =4;'

mysql -u root -p -e 'select * from test.test;'

重新恢复

mysqlbinlog --no-defaults --stop-position='589' /opt/mysql-bin.000002 | mysql -u root-p

mysql -u root -p -e 'select * from test.test;'

仅恢复"zhaoliu"的数据，跳过"wangwu"的数据恢复

删除恢复

mysql -u root -p -e 'delete from test.test where id =3;'

mysql -u root -p -e 'select * from test.test;'

重新恢复

mysqlbinlog --no-defaults --start-position='589' /opt/mysql-bin.000002 | mysql -u root-p

mysql -u root -p -e 'select * from test.test;'

③基于时间点恢复

仅恢复到21:22:57之前的数据，即不恢复"wangwu"的数据

删除恢复

mysql -u root -p -e 'delete from test.test where id =4;'

mysql -u root -p -e 'select * from test.test;'

重新恢复

mysqlbinlog --no-defaults --stop-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p

mysql -u root -p -e 'select * from test.test;'

仅恢复"zhaoliu"的数据，跳过"wangwu"的数据恢复

删除恢复

mysql -u root -p -e 'delete from test.test where id =3;'

mysql -u root -p -e 'select * from test.test;'

重新恢复

[mysqlbinlog --no-defaults --start-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p

mysql -u root -p -e 'select * from test.test;'

④总结

如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点

如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

 

一、实例准备--制表

1. 表1（商店区域表）

mysql

use test;

create table location (region char(20),store_name char(20));

insert into location values('North','Beijing');

insert into location values('Eaet','shanghai');

insert into location values('South','Guangzhou');

insert into location values('South','Shenzhen');

select * from location;

2. 表2（商店销售表）

create table store_info (store_name char(20),sales int(10),date char(10));

insert into store_info values('Guangzhou',1500,'2020-12-05');

insert into store_info values('Shenzhen',250,'2020-12-07');

insert into store_info values('Guangzhou',300,'2020-12-08');

insert into store_info values('Beijing',700,'2020-12-08');

select * from store_info;

3. 表3（城市表）

create table city(city_name char(20));

insert into city values('beijing'),('nanjing'),('shanghai');

insert into city values();

insert into city values();

insert into city values('');

select * from city;

4. 表4（total_sales）

create table total_sales (name char(20),sales int(5));

insert into total_sales values('zhangsan',10);

insert into total_sales values('lisi',15);

insert into total_sales values('wangwu',20);

insert into total_sales values('zhaoliu',40);

insert into total_sales values('sunqi',50);

insert into total_sales values('zhouba',20);

insert into total_sales values('wujiu',30);

select * from total_sales;

二、SQL语句

1. select

显示表格中一个或数个栏位的所有资料

语法：select "栏位" from "表名";

select store_name from store_info;

2. distinct

不显示重复的资料

语法：select dstinct "栏位" from "表名";

select distinct store_name from store_info;

3. where

有条件查询

语法：select "栏位" from "表名" where "条件";

select store_name from store_info where sales>1000;

4. and|or

且、或

语法：select "栏位" from "表名" where "条件1" {[and|or] "条件2"}...;

select store_name from store_info where sales>1000 or (sales<500 and sales>200);

5. in

显示已知的值的资料

语法：select "栏位" from "表名" where "栏位" in ("值1","值2",...);

select * from store_info where store_name in ('Beijing','Shenzhen');

6. between

显示两个值范围内的资料

语法：select "栏位" from "表名" where "栏位" between '值1' and '值2';

select * from store_info where date between '2020-12-06' and '2020-12-10';

7. limit

（1）显示前几行内容

语法：select "栏位" from "表名" limit '行数';

select * from store_info limit 2;

（2）显示第几行后的前几行

语法：select "栏位" from "表名" limit "第几行后,显示行数";

select * from store_info limit 2,2;

8. 通配符

通常通配符都是跟like一起使用的

 

常用通配符    说明

%    百分号表示零个、一个或多个字符

_    下划线表示单个字符

例如：

'A-Z'

表示所有以'A'起头，另一个任何值的字符，且以'Z'为结尾的字符串。例如，'ABZ'和'A2Z'都符合这一个模式，而'ABCZ'并不符合（因为A和Z之间有两个字符，而不是一个字符）

'ABC%'

表示所有以'ABC'起头的字符串。例如，'ABCD'和'ABCABC'都符合这个模式。

'%XYZ'

表示所有以'XYZ'结尾的字符串。例如，'WXYZ'和'ZZXYZ'都符合这个模式。

'%AN%'

表示所有含有'AN'这个模式的字符串。例如，'SHANGHAI'和'XIAN'都符合这个模式。

'_AN%'

表示第二个字母为'A'且第三个字母为'N'的字符串。例如，'HANGZHOU'和'LANZHOU'都符合这个模式，而'SHANGHAI'和'XIAN'则不符合这个模式。

9. like

匹配一个模式来搜查资料

语法：select "栏位" from "表名" where "栏位" like {模式};

select * from store_info where store_name like '%e%';

10. order by

按关键字排序

语法：select "栏位" from "表名" [where "条件"] order by "栏位" [ASC,DESC];

ASC是按照升序进行排序的，是默认的排序方式。

DESC是按降序方式进行排序。

select store_name,sales,date from store_info order by sales desc;

三、函数

1. 数学函数

常用的数学函数	说明
abs(x)	返回x的绝对值
rand()	返回0-1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数，即四舍五入到个位
round(x,y)	返回x的y位小数且四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字X截断为y位小数的值
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x1,x2...)	返回集合中最大的值
least(x1,x2...)	返回集合中最小的值

（1）abs(x)

返回x的绝对值

select abs(-2.3);

（2）rand()

返回0-1的随机数

select rand();

（3）mod（x,y）

返回x除以y以后的余数

select mod(5,2);

（4）power(x,y)

返回x的y次方

select power(2,8);

（5）round(x)

返回离x最近的整数,即四舍五入到个位

select round(3.1415);

select round(3.5415);

（6）round(x,y)

返回x的y位小数且四舍五入后的值

select round(3.1415,2);

select round(3.1415,3);

（7）sqrt(x)

返回x的平方根

select sqrt(9);

select sqrt(8);

（8）truncate(x,y)

返回数字X截断为y位小数的值

select truncate(3.1415,2);

select truncate(3.1415,3);

（9）ceil(x)

返回大于或等于x的最小整数

select ceil(3.1415);

（10）floor(x)

返回小于或等于x的最大整数

select floor(3.1415);

（11）greatest(x1,x2...)

返回集合中最大的值

select greatest(3.1415,3,4,5.2);

（12）least(x1,x2...)

返回集合中最小的值

select least(3.1415,3,4,5.2);

2. 聚合函数

常用的聚合函数	说明
avg()	返回指定列的平均值
count()	返回指定列中非NULL值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum()	返回指定列的所有值之和

（1）avg()

返回指定列的平均值

select avg(sales) from store_info;

（2）count()

返回指定列中非NULL值的个数

select count(store_name) from store_info;

返回指定列中非NULL值且去重的个数

mysql> select count(distinct store_name) from store_info;

count()

count()包括了所有的列的行数，在统计结果的时候，不会忽略值为NULL的行

select count(*) from city;

count("列名")

count("列名")只包括列名那一列的行数，在统计结果的时候，会忽略值为NULL的行

select count(city_name) from city;

（3）min()

返回指定列的最小值

select min(sales) from store_info;

（4）max()

返回指定列的最大值

select max(sales) from store_info;

（5）sum()

返回指定列的所有值之和

select sum(sales) from store_info;

3. 字符串函数

常用的字符串函数	说明
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数x和y拼接成一个字符串
substr(x,y)	获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)	获取从字符串x中第y个位置开始长度为z的字符串
length(x)	返回字符串x的长度
replace(x,y,z)	将字符串z替代字符串x中的字符串y
upper(x)	将字符串x的所有字母变成大写字符
lower(x)	将字符串x的所有字母变成小写字符
left(x,y)	返回字符串x的前y个字符
right(x,y)	返回字符串x的后y个字符
reprat(x,y)	将字符串x重复y次
space(x)	返回x个空格
strcmp(x,y)	比较x和y，返回的值可以为-1,0,1
reverse(x)	将字符串x反转

（1）trim()

返回去除指定格式的值

语法：select trim([[位置] [要移除的字符串] from] 字符串);

[位置]：该值可以为leading（起头），trailing（结尾），both（起头及结尾）。缺省时为both。

[要移除的字符串]：从字串的起头、结尾，或起头即结尾移除的字符串。缺省时为空格。

select trim('g' from 'guangdong');

select trim(leading 'g' from 'guangdong');

select trim(trailing 'g' from 'guangdong');

select trim(both 'g' from 'guangdong');

（2）concat(x,y)

将提供的参数x和y拼接成一个字符串

select concat (region,' ',store_name) from location where store_name='Beeijing';

如sql_mode开启了PIPES_AS_CONCAT（可使用"select @@SESSION.sql_mode;"或"select @@GLOBAL.sql_mode;"进行查看），"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数相类似，这和Oracle数据库使用方法一样。

select store_name || ' ' || sales from store_info where store_name='Guanngzhou';

（3）substr(x,y)

获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同

select substr(store_name,3) from location where store_name='Guangzhou';

（4）substr(x,y,z)

获取从字符串x中第y个位置开始长度为z的字符串

select substr(store_name,3,4) from location where store_name='Guangzhou'';

（5）length(x)

返回字符串x的长度

select *,length(store_name) from location;

（6）replace(x,y,z)

将字符串z替代字符串x中的字符串y

select replace(store_name,'ng','xx') from location;

（7）upper(x)

将字符串x的所有字母变成大写字符

select upper(store_name) from location;

（8）lower(x)

将字符串x的所有字母变成小写字符

select lower(store_name) from location;

（9）left(x,y)

返回字符串x的前y个字符

select left('Beijing',3);

（10）right(x,y)

返回字符串x的后y个字符

select right('Beijing',3);

（11）reprat(x,y)

将字符串x重复y次

select repeat('Beijing ',3);

（12）space(x)

返回x个空格

select space(10);

select space(15);

（13）strcmp(x,y)

比较x和y，返回的值可以为-1,0,1

x=y，返回0

select strcmp(1,1);

x<y，返回-1

select strcmp(1,2);

x>y，返回1

select strcmp(2,1);

（14）reverse(x)

将字符串x反转

select reverse(sales) from store_info;

四、SQL查询语句

1. group by

对group by后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的。

group by有一个原则，就是select后面的所有列中，没有使用聚合函数的列，必须出现在group by 后面。

语法：select "栏位1",sum("栏位2") from "表名" group by "栏位1";

select store_name,sum(sales) from store_info group by store_name order by sales desc;

"Guangzhou"有两条属性信息，通过group by分组后，同名项将被合并，可通过以下方式确定是否有重名项以及重名次数。

select store_name,count(store_name),sum(sales) from store_info group by store_name order by sales desc;

也可通过前后sales是否变化，判断是否有重名项被合并。

select store_name,sales,sum(sales) from store_info group by store_name order by sales desc;

2. having

用来过滤由group by语句返回的记录集，通常与group by语句联合使用。

having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被select的只有函数栏，那就不需要group by子句。

语法：select "栏位1",sum("栏位2") from "表名" group by "栏位1" having (函数条件);

select store_name,sum(sales) from store_info group by store_name having sum(sales)>1500;

3. [as] 别名

栏位别名/表格别名

语法：select "表格别名"."栏位1" [as] "栏位别名" from "表格名" [as] "表格别名";

select A.store_name as STORE,sum(A.sales) as 'TOTAL_SALES' from store_info as A group by STORE;

4. 子查询

连接表格，在where子句或having子句中插入另一个SQL语句

语法：slect "栏位1" from "表格1" where "栏位2" [比较运算符] (select "栏位1" from "表格2" where "条件");

其中：

slect "栏位1" from "表格1" where "栏位2" [比较运算符] 为外查询，

(select "栏位1" from "表格2" where "条件")为内查询。

[比较运算符]可以是符号的运算符，例如=、>、<、>=、<=；也可以是文字的匹配符，例如like、in、between等。

select sum(sales) from store_info where store_name in (select store_name from location where region='North');

select sum(A.sales) from store_info A where A.store_name in

(select store_name from location B where B.store_name=A.store_name);

注：匹配两个表的列名可以不相同，但列中需有相同内容，否则将返回NULL值。

 

alter table location change store_name name char(20);

 

select sum(sales) from store_info where store_name in (select name from location where region='North');

 

select sum(sales) from store_info where sales in (select name from locattion where region='North');

5. exists

用来测试内查询有没有产生任何结果，类似布尔值是否为真。

如果有的话，系统就会执行外查询中的SQL语句。若没有的话，那整个SQL就不会产生任何结果。

语法：select "栏位1" from "表格1" where exists (select * from "表格2" where "条件");

mysql> select sum(sales) from store_info where exists

(select * from location where region='North');

6. join--连接查询

inner join（内连接）：只返回两个表中联结字段相等的行

select * from location A inner join store_info B on A.store_name=B.store_name;

left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录

select * from location A left join store_info B on A.store_name=B.store__name;

right join（右连接）：返回包括右表中所有记录和左表中联结字段相等的记录

select * from location A right join store_info B on A.store_name=B.store_name;

也可以通过from多表后where添加同名条件达到与连接查询相同的效果

select * from location A,store_info B where A.store_name=B.store_name;

也可对其进一步分组操作，实现去重

select A.region,sum(B.sales) from location A,store_info B

where A.store_name=B.store_name group by A.region;

select A.store_name,sum(B.sales) from location A,store_info B

where A.store_name=B.store_name group by A.store_name;

五、create view--视图

1. 视图的定义

视图，可以被当作是虚拟表或存储查询。

 

2. 视图和表格的区别

视图和表格的不同是，表格中有实际存储资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

 

3. 视图的特点

视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对净额表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

 

4. 视图的语法

create view "视图表名" as "select 语句";

create view V_REGION_SALES as

select A.region REGION,sum(B.sales) SALES from location A

inner join store_info B on A.store_name=B.store_name

group by REGION;

5. 查看视图

视图保存于当前库的表列表中，查询方式同表。

show tables;

也可使用selct语句对视图进行查看

select * from V_REGION_SALES;

6. 删除视图

可使用drop view对视图进行删除操作。

drop view V_REGION_SALES;

show tables;

六、union--联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类。

union：生成结果的资料值将没有重复，且按照字段的顺序进行排序。

语法：[select 语句1] union [select 语句2];

select store_name from location union select store_name from store_info;

union all：将生成结果的资料值都列出来，无论有误重复

语法：[select 语句1] all union [select 语句2];

select store_name from location union all select store_name from store_iinfo;

七、有/无交集值

取两个SQL语句结果的交集

1. 出现重复的行

可以使用on语句匹配指定列中的相同行

select A.store_name from location A inner join store_info B on A.store_name=B.store_name;

当两张表的列名相同时，也可适应using语句进行匹配

select A.store_name from location A inner join store_info B using(store_name);

也可使用union all将两张表的指定列合并显示，然后再通过group by分组去重

select A.store_name from

(select store_name from location union all select store_name from store_info) A

group by A.store_name;

2. 两张表都出现的重复的行

两张表其中的一个表没有指定的行，而另一个表这个行有重复时，导致以上方法仍会返回该行内容，因此我们需要使用以下几种方式将其修正为两张表都有指定行的时候才会返回该行。

也可使用union all将两张表的指定列合并显示，然后再通过group by分组去重，最后使用having语句匹配该列出现次数大于1的行

select A.store_name from

(select store_name from location union all select store_name from store_info) A

group by A.store_name having count(*)>1;

使用内连接的方式匹配两张表中指定列都出现的行，然后通过group by分组去重

select A.store_name from

(select B.store_name from location B inner join store_info C on B.store_name=C.store_name) A

group by A.store_name;

使用distinct语句进行去重后再连接两张表，并使用using语句匹配指定列（两张表指定列名需相同）

select distinct A.store_name from location A inner join store_info B using(store_name);

先用distinct将两张表都去重后再去匹配指定列的相同行

select distinct store_name from location where (store_name) in

(select distinct store_name from store_info);

使用distinct去重后左连接后using指定列去匹配

select distinct A.store_name from location A

left join store_info B using(store_name)

where B.store_name is not null;

3. 无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

select distinct store_name from location where (store_name) not in

(select store_name from store_info);

select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is null;

select A.store_name from

(select distinct store_name from location union all select distinct store_name from store_info) A

group by A.store_name having count(*)=1;

八、case

1. case概述

case是SQL用来做if-then-else之类逻辑的关键字

 

2. case语法

select case ("栏位名")

when "条件1" then "结果1"

when "条件2" then "结果2"

...

[else "结果N"]

end

from "表名";

条件：可以是一个数值或是一个公式，else子句并不是必须的

3. case使用实例

select store_name,case store_name

when 'Beijing' then sales *2

when 'Shenzhen' then 1000

else sales

end

'new sales',date

from store_info;

then 数字，将直接返回该数字，即重新为匹配行赋值

'new sales'是用于case字段的字段名

九、常用算法

1. 排名

表格自我连结（self join），然后将结果依序列出，算出每一行之前（包含那一行本身）有多少行数

select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or

(A1.sales = A2.sales and A1.name = A2.name)

group by A1.name,A1.sales order by A1.sales desc;

统计sales字段的值是比自己本身的值小的以及sales字段和name字段都相同的数量，比如zhangsan为6+1=7。

由于wangwu和zhouba的sales列都为20，并列第四，若需将他俩分出第四和第五，可用以下方法（由于sales相同，只能根据name进行区分排序）

select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or

(A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

2. 中位数

中位排名取值1：通过ceil取(行数/2)后的最小整数

select name,sales middle from

(select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc) A3

where A3.rank = (select ceil(count(rank)/2) from total_sales);

中位排名取值2：将(行数+1)/2取商

select name,sales middle from

(select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc) A3

where A3.rank = (select (count(*)+1) div 2 from total_sales);

div是在MySQL中算出商的方式

每个派生表必须有自己的别名，所以别名A3必须要有

也可使用view视图进行操作

create view V1 as

select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

select name,sales middle from V1

where rank = (select ceil(count(rank)/2) from total_sales);

3. 累计总计

表格自我连结（self join），然后将结果依序列出，算出每一行之前（包含哪一行本身）的总合

select A1.name,A1.sales,sum(A2.sales) sum_total,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.namee)

group by A1.name,A1.sales order by A1.sales desc;

4. 总合百分比

select A1.name,A1.sales,A1.sales/(select sum(sales) from total_sales) peer_total,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

select sum(sales) from total_sales这一段子查询用来算出总合；

总合算出来后，我们就能够将每一行除以总合来求出每一行的总和百分比。

若需要显示小数后两位的百分数，可使用"round(A1.sales/(select sum(sales) from total_sales)*100,2) || '%'"的操作。

select A1.name,A1.sales,round(A1.sales/(select sum(sales) from total_sales)*100,2) || '%' per_total,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

5. 累计总合百分比

select A1.name,A1.sales,sum(A2.sales)/(select sum(sales) from total_sales) per_total,count(A2.sales) rank from total_sales A1,total_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

显示小数后两位的百分数

select A1.name,A1.sales,round(sum(A2.sales)/(select sum(sales) from total_salal_sales)*100,2) || '%' per_total,count(A2.sales) rank from total_sales A1,totaal_sales A2

where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)

group by A1.name,A1.sales order by A1.sales desc;

十、空值(NULL)和无值('')的区别

无值的长度为0，不占用空间；而NULL值的长度是NULL，占用空间。

select length(NULL),length(''),length('1');

is null或者is not mull，是用来判断字段是不是为NULL，不能查出是不是无值。

select * from city where city_name is null;

select * from city where city_name is not null;

无值的判断使用=''、<>''或!=''来处理，<>和!=代表不等于。

select * from city where city_name = '';

select * from city where city_name <> '';

select * from city where city_name != '';

注：<>''和!=''为不是无值的行，但NULL本质上也没有实际值，因此也会被忽略。

在通过count()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。

select count(*) from city;

select count(city_name) from city;

十一、正则表达式

匹配模式	描述	实例
^	匹配文本的开始字符	'^a'匹配以a开头的字符串
$	匹配文本的结束字符	'a$'匹配以a结尾的字符串
.	匹配任何单个字符	'a.c'匹配任何a和c之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	'ye*p'匹配p前面有任意个e
+	匹配前面的字符1次或多次	'hi+'匹配以hi开头，后面至少一个i的字符串
字符串	匹配包含指定的字符串	'test'匹配含有test的字符串
a|b	匹配a或b	'test|wrok'匹配test或者work
[...]	匹配字符集合中的任意一个字符	'[abc]'匹配a或者b或者c
[^...]	匹配不在括号中的字符	'[abc]'匹配不包含a或者b或者c的字符串
{n}	匹配前面的字符串n次	'a{2}'匹配含有2个a的字符串
{n,m}	匹配前面的字符串至少n次，至多m次	'a{1,2}'匹配a至少1次，至多2次

语法：select "字段" from "表名" where "字段" regexp {模式};

select * from store_info where store_name regexp 'ng';

select * from store_info where store_name regexp '^[A-C]';

select * from store_info where store_name regexp 'ei|en';

十二、存储过程

1. 存储过程的定义

存储过程是一组为了完成特定功能的SQL语句集合，类似于shell中的函数。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

 

2. 存储过程的优点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率。

SQL语句加上控制语句的集合，灵活性高。

在服务器端存储，客户端调用时，降低网络负载。

可多次重复被调用，可随时修改，不影响客户端调用。

可完成所有的数据库操作，也可控制数据库的信息访问权限。

3. 创建存储过程

delimiter $$

#将语句的结束符号从分号;临时改为$$(可以是自定义)

create procedure test()

#创建存储过程，过程名为test，不带参数

begin

#过程体以关键字begin开始

select * from store_info;

#过程体语句

end $$

#过程体以关键字end结束

delimiter ;

#将语句的结束符号恢复为分号;

4. 调用存储过程

call test;

5. 查看存储过程

show create procedure [数据库.]存储过程名

查看某个存储过程的具体信息

show create procedure test;

begin

select * from store_info;

也可查看procedure状态并使用like语句进行匹配

show procedure status like '%test%'\G

6. 存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT 输出参数：表示过程向调用者传出值（传出值只能是变量）

INOUT 输入输出参数：即表示调用者向过程传入值，也表示过程向调用者传出值

delimiter $$

create procedure test1(in inname char(20))

begin

select * from store_info where store_name=inname;

end $$

delimiter ;

call test1('Beijing');

7. 删除存储过程

存储过程内容到的修改方法使用过删除原有存储过程，之后再以相同的名称创建新的存储过程。

drop procedure if exists test;

仅当存在时删除，不添加if exists时，如果指定过程不存在，则产生一个错误

drop procedure test;

drop procedure if exists test;

8. 存储过程的控制语句

制表

create table t (id int(10));

insert into t values(10);

select * from t;

（1）条件语句if-then-else...end if

delimiter $$

create procedure test(in pro int)

begin

declare var int;

set var=pro*2;

if var>=10 then

update t set id=id+1;

else

update t set id=id-1;

end if;

end $$

delimiter ;

call test(6);

select * from t;

call test(4);

select * from t;

（2）循环语句while...end while

delimiter $$

create procedure test2()

begin

declare var int(10);

set var=0;

while var<6 do

insert into t values(var);

set var=var+1;

end while;

end $$

delimiter ;

call test2;

select * from t;