带你系统学习mysql

近期复习数据库知识，做出以下整理

1.数据表的设计

数据表的设计包括ER图、主键、数据类型、约束关系等

E-R图

E-R图也称为实体关系模型，主要用于定义数据的储存需求。该模型已经广泛用于关系数据库设计中。

E-R图由实体，属性，关系三个基本的元素构成。

实体间的联系由三种情况：

1-1:一对一

1-n：一对多

n-m：多对多

主键（primary Key）：

数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同 的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键 （primary key）。数据库表中主键有以下两个特征：

1. 表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）
2. 数据表中的主键的值具有唯一性并且不能取空值，当数据库表中的主键由多个字段构成时，每个字段的值不能去null值。

外键（Foreign Key）：

在A表中，它是主键；在B表中，它不是主键，那么，在B表中，这个键就是外键。作用：用来连接两张或多张表的。

常见的数据类型：

数值类型：

类型	用途
TINYINT	小整数值
SMALLINT	大整数值
INT或INTEGER	大整数值
BIGINT	极大整数值
FLOAT	单精度，浮点数值
DOUBLE	双精度，浮点数值
DECIMAL	10进制小数

文本型：

类型	用途
CHAR	定长字符串
VARCHAR	变长字符串
TEXT	长文本数据

日期时间型：

类型	格式	用途
DATA	日期值	日期值
TIME	HH:MM:SS	时间值或持续时间
DATATIME	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

约束：

约束是定义在表上的一种强制规则。当为某个表定义约束后，对该表做的所有SQL操作都必须满足约束的规则 要求，否则操作将失败。

约束类型：

约束	说明
NOT NULL	非空约束，指定某一列的所有数据不能为空
UNIQUE	唯一性约束，指定的列或者列的组合的所有行数据必须唯一，可以有一个空值
PRIMARY KEY	主键约束，指定列或者列的组合 的所有行数据必须唯一，且不能再现空值
FOREIGN KEY	外键约束，在列和引用列上建立一种强制依赖关系
CHECK	检查性约束，在列上指定一个 必须满足的条件

1.1 常用操作

创建数据库：

 create database mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

查看所有数据库：

show databases;

切换数据库：

use 数据库;

查看正在使用那个数据库：

select database();

删除数据库：

drop database 数据库;

1.2 创建数据表

建表：

查看当前数据库中的表：

show tables;

创建数据表：

create table 表名(列1 类型 [宽度] 约束，列2 类型 [宽度] 约束，……)

例子：

create table studentsinfo(sno char(10) primary key,sname varchar(10) not null,sage tinyint(2),saddress varchar(20));

查看建表语句：

show create talble studentsinfo

查看表中所有信息：

desc studentsinfo;

修改表：

添加字段：

alter table 表名 add 列名 类型。。。

修改字段：

以一种（不修改字段名）

alter table 表明 modify 列名 类型...

以二种（修改字段名）

alter table 表明 change 原名 新名...

删除字段：

alter table 表名 drop 列名

删除表：

drop table 表明；

1.3 构建一个学生管理表

CREATE DATABASE stumanager DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE stumanager
#创建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
	sno VARCHAR(3) PRIMARY KEY COMMENT '学号',
	sname VARCHAR(4) not NULL,
	ssex CHAR(2) DEFAULT('男'),
	sbirthday VARCHAR(10) DEFAULT NULL,
	class VARCHAR(5) not null
)ENGINE=INNODB DEFAULT charset=utf8;
#创建教师表
DROP TABLE IF EXISTS teacher;
CREATE table teacher(
	tno VARCHAR(3) PRIMARY KEY,
	tname VARCHAR(5) NOT NULL,
	tsex VARCHAR(2) NOT null,
	tbirthday VARCHAR(10) NOT NULL,
	prop VARCHAR(6) DEFAULT NULL,
	depart VARCHAR(10) DEFAULT null
)ENGINE=INNODB DEFAULT charset=utf8
#创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course(
	cno VARCHAR(5) PRIMARY KEY,
	cname VARCHAR(10) NOT NULL,
	credit INT NOT NULL CHECK(credit>0 AND credit<10),
	tno VARCHAR(10) NOT NULL,
	FOREIGN KEY(tno) REFERENCES teacher(tno)
)ENGINE=INNODB DEFAULT charset=utf8
#创建成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score(
	sno VARCHAR(10) NOT NULL,
	cno VARCHAR(10) NOT NULL,
	grade INT,
	PRIMARY key(sno,cno),
	FOREIGN KEY(sno) REFERENCES student(sno),
	FOREIGN KEY(cno) REFERENCES course(cno)
)ENGINE=INNODB DEFAULT charset=utf8

E-R图：

2.基本操作

2.1 基本查询

查询语句：

查询所有列：

SELECT * FROM 表名

查询特定列：

SELECT 字段名 FROM 表名

2.2 插入

全列插入：

insert into table values(...)

部分插入：

insert into table(字段1，...) values (...)

全列多行插入：

insert into table value(...),(...),(...);

部分列多行插入：

insert into  table(字段1，...) value (...),(...)

2.3 更新

更新操作：

不使用where限定则修改全表。

undate 表名 set 属性='' where 属性=''

注意：修改某一行内容一定要加where限定条件，否则会造成全表修改，除非你想要修改整张表。

2.4 删除

delete

可以删除指定的数据或所有数据，表结构还存在。

delete from 表名 [where <条件>]

truncate

只能删除所有数据，表结构还存在。

truncate table 表名;  -- 等价于delete from 表名

2.5 备份与导入

1.图形界面手动备份与恢复（有坑）

2.命令行备份

备份所有表：

mysqldump -uroot -p 数据库名>文件路径/备份文件名.sql

备份指定表：

mysqldump -uroot -p 数据库名 表名>文件路径/备份文件名.sql

导入我们可以使用图形界面导入。或者使用命令行

mysql -uroot -p 数据库名<文件路径/备份文件名.sql

3. Mysql数据查询

查询的基本语法：

select [distinct] *|列名表|聚合函数 [as <别名>]
from <表名列表> [as <别名>]
where <条件>
group by <列名表>
having <分组条件>
order by 列1 [asc|desc],列2 [asc|desc],…
limit [起始下标,]<条数>

3.1 条件查询

关系运算：>,>=,<,<=,<>,!=,=

**逻辑运算：**not,and,or

模糊查询：

1. like：表示模糊查询
2. %表示任意多个字符
3. _表示一个字符
4. rlike：表示可以匹配正则
5. in：包含在里面的
6. between and 表示一段区间
7. 判断为null值
8. 排序：order by 字段 [desc/asc]

实例：

use stumanager2;
#查询考试成绩为90分以上的学生
SELECT * FROM score WHERE grade>90;
SELECT * FROM student;
#找20031班的女生
SELECT * FROM student WHERE NOT(class='20031'AND ssex='女');
#查询姓张的学生
SELECT * FROM student WHERE sname LIKE '王%';
#in的用法
SELECT * from student WHERE sname in('王芳');
#分数判定
SELECT * FROM score WHERE grade BETWEEN 80 and 100;

排序order by：

#对成绩进行降序排列
SELECT * from score ORDER BY grade DESC;
#对班级的按姓名降序排列。
SELECT * FROM student ORDER BY class DESC;

3.2 聚合函数

聚合函数可以帮助我们快速的得到结果，我们经常会使用到的几个聚合函数：

1. count() :统计行数
2. max(): 计算最大值
3. min(): 计算最小值
4. sum():求和
5. avg():求平均值
6. round():保留几位小数

可以稍稍练习一下：

#计算班级人数
SELECT COUNT(*) from student;
#查询一下3-105的最高成绩
SELECT cno,MAX(grade) from score WHERE cno='3-105'
#查询一下3-105的平均成绩
SELECT cno,AVG(grade) from score WHERE cno='3-105'

3.3 分组查询

分组：

group by 字段 --以字段作为分组的关键字

可以做一下练习：

#分别统计男女生人数
SELECT ssex,COUNT(*) as '性别' FROM student GROUP BY ssex;
#统计每位学生的考试总分
SELECT sno,SUM(grade) as '总分' FROM score GROUP BY sno;
#统计每位学生的考试最高分
SELECT sno,MAX(grade) as '最高分' FROM score GROUP BY sno;
#统治每位学生的考试平均分
SELECT sno,avg(grade) as '平均分' FROM score GROUP BY sno;
#统治每位学生的考试平均分(保留一位小数)
SELECT sno, ROUND(avg(grade),1) as '平均分' FROM score GROUP BY sno;

having子句：

分组后的条件筛选

带有集合函数的条件，只能放在having后面，而不能放在where后面。

使用having子句时，必须先分组，也就是说having是必须要和group by连用的。

我们来举一个例子：

#查询选修两门课以上的学生。
SELECT * from score GROUP BY sno HAVING COUNT(sno)>2;

having子句和where的区别：

1. where用在from后面作为条件筛选，表示从所有数据中筛选出部分数据，以完成分组的要求，不允许使用统计函数
2. having用在分组之后的条件筛选，位置不同。表示对分组统计后的数据执行再次过滤，可以用统计函数

3.4 分页查询

当数据量很大时，一次性把所有的数据全部输出比较不妥，这样即影响体验而且耗费传输带宽

那么我们这时就用到了分页功能，一次只查询一页的数据。

语法：

select * from table limit begin,count;  -- begin从第几条数据开始，count表示获取几条数据

例子：

#以分页的方式输出学生成绩，每页输出5条数据
SELECT * from score LIMIT 5;
SELECT * from score LIMIT 5,5;
#查询成绩表的第3-6条记录
SELECT * from score LIMIT 2,4
#查询考试前3名的学生
SELECT * from score ORDER BY grade DESC LIMIT 3

3.5 连接查询（多表查询）

mysql有两种连接查询方式，分别是内部查询和外部查询，外部查询又分为左外查询和右外查询。

内部查询：

实际上就是等值连接，通常把多张表通过外键两两连接起来，形成一张逻辑表。

最终查询到的结果是两个表匹配到的数据，两个表都能匹配上的数据将返回给结果集。

select * from 表1 inner join 表2 on 表1.列=表2.列；

实例：

-- 查询每位学生的成绩及其代课老师
SELECT sname,grade,cname,tname  FROM student stu 
INNER JOIN score sco on stu.sno=sco.sno
INNER JOIN course sour ON sour.cno=sco.cno
INNER JOIN teacher teac ON teac.tno=sour.tno

右外查询：

结果为左右表所有满足条件的数据。

select * from 表1 right join 表2 on 表1.列=表2.列

实例：

-- 学生表和成绩表右外连接
SELECT * FROM student 
RIGHT JOIN score on student.sno=score.sno;

左外查询：

结果为左表的所有数据，右表满足条件的有数据，不满足条件的将以null填充。

select * from 表1 left join 表2 on 表1.列=表2.列

实例：

-- 学生表和成绩表左外连接
SELECT * FROM student 
LEFT JOIN score on student.sno=score.sno;

3.6 子查询（嵌套查询）

在一个select语句中嵌入了另外一个select语句，嵌入的这个select语句就是子查询语句。

子查询是辅助主查询的，充当数据源，或者充当条件。子查询是一条独立的语句，即使单独拿出子查询也是可 以正常执行的。

子查询分为以下几种情况：

1. 查询返回一行一列的数据，称之为标量子查询
2. 返回一列多行的数据，称之为列级子查询（使用in做条件判断。）
3. 询返回的是一行多列，称之为行级子查询
4. 子查询返回多行多列，称之为表级子查询

实例：

-- 查询计算机导论考试成绩高于平均分的学生的姓名
SELECT sname,score.grade FROM student
INNER JOIN  score ON student.sno=score.sno 
WHERE score.cno='3-105' 
and
grade>(SELECT AVG(grade) FROM score INNER JOIN course ON score.cno=course.cno WHERE score.cno='3-105')

3.7 联合查询（union）

联合查询时将查询的两张表联合起来，最终显示两张表的数据，要求是两张表的列数要相同。

通常使用union和union all

1. union all 将两次查询的结果集合并到一起显示。
2. union 将两个查询的结果集先去重后合并到一起显示。

实例：

-- 查询男生的信息和女生的信息
SELECT * FROM student WHERE ssex='男'
UNION  ALL
SELECT * FROM student WHERE ssex='女'

-- 查询老师和学生的相关信息
SELECT sno,sname FROM student 
UNION  ALL
SELECT tno,tname FROM teacher 

3.8 保存当前结果

保存当前的运算结果实际上就是把当前查询的结果放在另外一张表中。

大概的语法就是这样

insert into table (select....)

一个实例：

-- 将每位每位学生的成绩及其代课老师保存在新表中
DROP TABLE IF EXISTS newstuandteacher;
CREATE TABLE newstuandteacher(sname VARCHAR(20),grade int,cname VARCHAR(20),tname VARCHAR(20))
INSERT INTO newstuandteacher (
SELECT sname,grade,cname,tname  FROM student stu 
INNER JOIN score sco on stu.sno=sco.sno
INNER JOIN course sour ON sour.cno=sco.cno
INNER JOIN teacher teac ON teac.tno=sour.tno
)
SELECT * FROM newstuandteacher;

4. 用户与权限管理

4.1 MySQL账户管理账户

Mysql的账户管理包括登录和退出Mysql服务器、创建用户、删除用户、密码管理和权限管理等内容。通过账户管理可以保证MySQL数据库的安全性。

Mysql中的root账户拥有最高的权限，包括删库，删表。所以在生产环境下一般一会使用root账户登录数据库的。Mysql中的用户信息都保存在mysql库下的user表中。

查看mysql库中所有用户信息：

use mysql
select * from user;

4.2 创建用户

创建本地用户

create user '用户名'@'localhost' identified by '密码';

实例：

create user 'test'@'localhost' identified by 'admin';

4.3 分配用户权限

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码'

MySql中权限有create、alter、drop、insert、update、delete、select等，如果要分配所有权限直接使用：all privileges。

下面给数据库创建一个python用户分配select权限，允许所有主机登录，密码为root。

grant select on *.* To 'python @'%' identified by 'root' with grant option;
flush privileges;

注意：

1. .：表示所有数据库的所有表
2. with grant option：表示它具有grant权限， 可以创建用户。
3. flush privileges：表示让赋予的权限立即生效。

4.4 查看、回收权限

查看权限：

show grants for 用户名 @'主机名' 

实例：

show grants for test@'localhost';

回收权限：

revoke 权限名 on *.*  from '用户名'@'主机名';

4.5 修改密码

1.用DOS命令实现

 mysqladmin -u用户名 -p密码 password 新密码

2.在mysql环境下修改

set password for '用户名'@'主机' = password('密码');

3.修改mysql数据库下的user表(必须有刷新操作)

update mysql.user set password=password('新密码') where user='用户名';
flush privileges; //刷新

4.6 删除用户

drop user '用户名'@'主机';

例子：

  drop user 'test'@'localhost';

5.事务

事务：也称为工作单元，是由一个或者多个SQL语句所组成的一个完整的工作单元，要么全部执行成功，要么全部执行失败，在数据库中，通过事务来保证数据的一致性。

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

mysql是支持事务的，MySQL默认使用innodb引擎支持事务。

事务的四大特征：

1. 原子性：事务就像原子一样不可分割，自称事务的DML操作语句要么全部执行成功，要么全部失败，不可能出现部分失败部分失败的情况。
2. 隔离性：一个事务的执行不会被另外一个事务的执行而影响。比如两个人同时从一个账户存取钱，通过事务的隔离性确保账户余额的正确性
3. 统一性：一旦事务完成，不管是成功的还是失败的，整个系统处于数据一致的状态。
4. 持久性：也称为永久化，指事务一旦提交，对于数据的改变就是永久的，不可以再被回滚。

5.1 手动处理事务

用begin,rollback,commit来实现，用begin开启事务后在没有commit提交之前执行修改命令，变更会维护到 本地缓存中，而不维护到务理表中，只有在commit提交后才会更新到务理表中。如果中间执行错误，那么用 rollback回滚事务，恢复到执行事务前的状态。

处理过程：

1. 开启事务
2. 事务要执行的语句序列
3. 提交事务或者回滚事务

BEGIN;
DELETE FROM stu WHERE sname='李军';
SELECT * from stu;
COMMIT;

5.2 自动提交模式

MySQL默认是自动提交的，也就是你提交一个sql，就直接执行。可以通过 set autocommit = 0 禁止自动提交，set autocommit = 1 开启自动提交， 来实现事务的处理。

但要注意当用set autocommit = 0 的时候，以后所有的sql都将作为事务处理，直到用commit确认或 rollback 结束，注意当结束这个事务的同时也开启了新的事务。按第一种方法只将当前的做为一个事务。

6.视图和索引

6.1 视图

对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修 改，维护起来非常麻烦。这种情况下可以定义视图解决，视图本质上就是对查询语句的封装。视图实际上就是查 询。

创建视图

create view 视图名称 as select语句

实例：

CREATE VIEW stusrore as SELECT sname,grade FROM student,score WHERE student.sno=score.sno

查看视图

show tables;
show table status;

删除视图

drop view 视图名称

调用视图

select * from viewtable

注意：修改视图中的数据会影响到原始数据。

6.2 索引

一般的应用对于数据库的需求都是查询，所以查询速度显得尤为重要。当数据库中的量很大时，查询数据就会变得很慢，这个时候就需要做想应的优化处理。

建立索引是一个有效的优化方案，索引就好像是一本词典的目录，这会让我们更快的查找到内容。当然索引也不是越多越好，这时占据我们的磁盘空间的。

选择索引的数据类型，越小的数据类型越好，越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空 间，处理起来更快。简单的数据类型更好，整型数据比起字符串，处理开销更小，因为字符串的比较更复杂。

Mysql常见的索引有：唯一索引、主键索引、普通索引、全文索引、组合索引

6.2.1 创建索引

创建索引一般有两种方式，第一种是再创建表结构的时候直接创建，第二种是修改表结构添加索引。

语法：

alter talbe 表名 add 索引类型... (列名)

主键索引

其实在我们创建表结构时，一旦给我们的表结构创建主键，就自动创建了主键索引。

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

唯一索引

alter table 表名 add unique (列名); 

普通索引

alter table 表名 add index 索引名称 （列名);

全文索引

基于相似度的查询

alter table add fulltext (列名); 

注意：

1. 每张表只能有一个全文索引；
2. 由多列组合而成的全文索引的列必须使用相同的字符集或排序规则；
3. 如果字段中含有汉字，要使用mysql5.6以后的版本；
4. 只允许在char,varchar和text数据类型的列上创建全文索引
5. 全文索引不能跨表查询；
6. 全文索引要将数据缓存到内存中，所以内存要求32M以上，可以根据innodb_ft_cache_size> 调整大小；
7. 全文索引是根据频词来查询的；
8. mysql5.6+，MyISAM和InnoDB存储引擎都支持全文索引。

使用：

 select * from teacher where match(tname) against('张旭阳刚'); 

使用注意：
i）match后的列必须要跟创建该索引的列完全一样，against后是要查询的文本内容；
ii）通过show variables like ‘%ft%’;测试，MyISAM查询的字符数在4-84个之间，而InnoDB在3-84个字符之间，如果低于3个，将查不到结果。

组合索引

alter table 表名 add index 索引名(列1，列2，列3)

6.2.2 查看和删除索引

查看索引

show index from 表名

删除索引

drop index 索引名 on 表名

7.存储过程

存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合，可以视为批处理，但是其作用不仅仅局限于 批处理。

7.1基本操作

创建：

delimiter //     
create procedure 存储过程名称(参数列表) 
    begin 
   sql语句 
    end 
// 
delimiter; 

注意：delimiter主要是起到了换行符作用，这时避免在CMD下使用此语句时回车键换行造成冲突。

调用：

call 存储过程名(参数列表)

删除：

drop procedure 存储过程名称

实例：

CREATE PROCEDURE proserach()
BEGIN 
	SELECT * FROM student;
END 

CALL proserach();

7.2 声明变量

要在存储过程中声明一个变量，可以使用DECLARE语句，如下所示：

DECLARE variable_name datatype DEFAULT default_value; 

设置变量值：

SET variable_name = 10; 

除了SET语句之外，还可以使用SELECT INTO语句将查询的结果分配给一个变量。

DECLARE total_products INT DEFAULT 0;
SELECT COUNT(*) INTO total_products FROM students;

实例：

DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test(str VARCHAR(10))
BEGIN 
	DECLARE aa VARCHAR(10);
	set aa='我是。。';
	SELECT aa 结果,str 参数;
END
CALL test('123');

7.2 条件语句和循环语句

条件分支

语法：

//单
IF expression THEN 
   statements;
END IF;
//双
IF expression THEN 
   statements;
ELSE 
   else-statements; 
END IF;
//多
IF expression THEN 
   statements;
ELSEIF elseif-expression THEN 
   elseif-statements; 
... 
ELSE 
   else-statements; 
END IF; 

循环语句

语法：

WHILE expression DO 
   statements
END WHILE

例子：

给表中使用循环添加数据

 drop table if exists temp;
      create table temp(id int auto_increment primary key,content varchar(20));
      drop procedure if exists proc_adddata;
      create procedure proc_adddata()
      begin	
        declare i int default 1;
        while i <= 400000 do
          insert into temp(content) values(concat('data',i));
          set i = i + 1;
        end while;
      end
      call proc_adddata();

7.3 一些函数

7.3.1 内置函数（不再赘述）

1. 查看字符的ascii码值ascii(str)，str是空串时返回0。
2. 查看ascii码值对应的字符char(数字)
3. 拼接字符串concat(str1,str2…)
4. 包含字符个数length(str)
5. 截取字符串substring()
6. 去除空格trim()
7. 获取日期 now();

7.3.2 自定义函数

delimiter $$
create function 函数名称(参数列表) returns 返回类型 
    begin 
   sql语句 
    end 
$$ 
delimiter; 

实例：

 create function py_trim(str varchar(100)) returns varchar(100) 
    begin 
        declare x varchar(100); 
        set x=ltrim(rtrim(str)); 
        return x; 
    end 

调用：

select py_trim(' ss'); 

删除：

DROP FUNCTION py_trim