目录
MySQLDDL:操作数据库、表DML:增删改表中的数据DQL(重要)：查询表中的记录约束数据库的设计数据库设计的范式数据库的备份和还原(跑路预定)多表查询多表查询练习事务DCL：MySQL
1.什么是SQL？Structured Query Language：结构化查询语言其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。2.SQL通用语法1. SQL 语句可以单行或多行书写，以分号结尾。2. 可使用空格和缩进来增强语句的可读性。3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。4. 3 种注释* 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) * 多行注释: /* 注释 */3. SQL分类1) DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等2) DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等3) DQL(Data Query Language)数据查询语言用来查询数据库中表的记录(数据)。关键字：select, where 等4) DCL(Data Control Language)数据控制语言(了解)用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
DDL:操作数据库、表
1. 操作数据库：CRUD1. C(Create):创建* 创建数据库：* create database 数据库名称;* 创建数据库，判断不存在，再创建：* create database if not exists 数据库名称;* 创建数据库，并指定字符集* create database 数据库名称 character set 字符集名;* 小综合练习： 创建db4数据库，判断是否存在，并制定字符集为gbk* create database if not exists db4 character set gbk;2. R(Retrieve)：查询* 查询所有数据库的名称:* show databases;* 查询某个数据库的字符集:查询某个数据库的创建语句* show create database 数据库名称;3. U(Update):修改* 修改数据库的字符集* alter database 数据库名称 character set 字符集名称;4. D(Delete):删除* 删除数据库* drop database 数据库名称;* 判断数据库存在，存在再删除* drop database if exists 数据库名称;5. 使用数据库* 查询当前正在使用的数据库名称* select database();* 使用数据库* use 数据库名称;2. 操作表1. C(Create):创建1. 语法：create table 表名(列名1 数据类型1,列名2 数据类型2,....列名n 数据类型n);* 注意：最后一列，不需要加逗号（,）* 数据库类型：1. int：整数类型* age int,2. double:小数类型* score double(5,2)3. date:日期，只包含年月日，yyyy-MM-dd4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss5. timestamp:时间错类型包含年月日时分秒 yyyy-MM-dd HH:mm:ss* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值6. varchar：字符串* name varchar(20):姓名最大20个字符* zhangsan 8个字符 张三 2个字符* 创建表create table student(id int,name varchar(32),age int ,score double(4,1),birthday date,insert_time timestamp);* 复制表：* create table 表名 like 被复制的表名; 2. R(Retrieve)：查询* 查询某个数据库中所有的表名称* show tables;* 查询表结构* desc 表名;3. U(Update):修改1. 修改表名alter table 表名 rename to 新的表名;2. 修改表的字符集alter table 表名 character set 字符集名称;3. 添加一列alter table 表名 add 列名 数据类型;4. 修改列名称 类型alter table 表名 change 列名 新列别 新数据类型;alter table 表名 modify 列名 新数据类型;5. 删除列alter table 表名 drop 列名;4. D(Delete):删除* drop table 表名;* drop table if exists 表名 ;
DML:增删改表中的数据
1、添加数据
* 语法：* insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);* 注意：1. 列名和值要一一对应。2. 如果表名后，不定义列名，则默认给所有列添加值insert into 表名 values(值1,值2,...值n);3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
#插入数据INSERT INTO teacher VALUE(24,'王力宏',32,NULL,NULL);INSERT INTO teacher VALUE(23,'周杰伦',32,'1993-11-22',NULL);INSERT INTO teacher(age,NAME,id)VALUE(22,'liuzeyu',33);
2、删除数据
* 语法：* delete from 表名 [where 条件]* 注意：1. 如果不加条件，则删除表中所有记录。2. 如果要删除所有记录1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
#删除表记录DELETE FROM teacher WHERE age=22;DELETEFROM teacher; #逐行删除表记录TRUNCATE TABLE teacher; #drop table teacher + create table teacher 两步操作
3、修改数据
* 语法：* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];* 注意：1. 如果不加任何条件，则会将表中所有记录全部修改。
#修改表内容UPDATE teacher SET age=55 WHERE id=32;UPDATE teacher SET age=77,birthday="1998-12-12" WHERE id=33;UPDATEteacher SET age=100;
DQL(重要)：查询表中的记录
CREATE TABLE student(id INT, -- 编号NAME VARCHAR(20), -- 姓名age INT, -- 年龄sex VARCHAR(5), -- 性别address VARCHAR(100), -- 地址math INT, -- 数学english INT -- 英语);INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
#基础查询SELECT *FROM student;#查询所有数据SELECT NAME,age FROM student; #查询所有数据的NAME,age所列的字段数据SELECT NAME,address FROM student;#查询所有数据的NAME,address所列的字段数据SELECT address FROM student;#查询所有数据的address所列的字段数据SELECT DISTINCT address FROM student;#查询所有数据的address所列的字段数据去除重复字段DROP TABLE student;#删除学生表SELECT NAME,math,english,math+english FROM student; #计算英语和数学的分数SELECT NAME,math 数学,english 英语,math+IFNULL(english,0) 总分 FROM student;#计算英语和数学的分数并取别名SELECT NAME,math AS 数学,english AS 英语,math+IFNULL(english,0) 总分 FROM student;#计算英语和数学的分数并取别名#条件查询SELECT *FROM student; #查询所有数据SELECT *FROM student WHERE age>20; #查询表中所有数据，并且age>20的数据SELECT *FROM student WHERE age>=20 AND age<=100;#查询表中所有数据，并且age>=20 <=100的数据SELECT *FROM student WHERE age BETWEEN 20 AND 100;SELECT *FROM student WHERE age!=20;#查询表中所有数据，并且age不等于20的数据SELECT *FROM student WHERE age<>20;SELECT *FROM student WHERE age=20;#查询表中所有数据，并且age等于20的数据SELECT *FROM student WHERE age=55 OR age=20 OR age=57;#查询age=55，age=20,age=57的数据SELECT *FROM student WHERE age IN(55,45,57); #类似上面操作SELECT *FROM student WHERE english IS NULL;#查询表中英语成绩为null的数据SELECT *FROM student WHERE english IS NOT NULL;#查询表中英语成绩不为null的数据#模糊查询SELECT *FROM student WHERE NAME LIKE '马%';#查询名字为马开头的数据SELECT *FROM student WHERE NAME LIKE '马'; #查询名字为马的数据SELECT *FROM student WHERE NAME LIKE "_化%"; #查询名字第二个字为化的数据SELECT *FROM student WHERE NAME LIKE "___";#查询名字三个字的数据SELECT *FROM student WHERE NAME LIKE "__";#查询名字两个字的数据SELECT *FROM student WHERE NAME LIKE "%德%";#分组查询(重点难点)/*分组之后查询的字段：1、分组字段，聚合函数2、where和having的区别：where在group by分组前面进行限定，如果不满足限定条件，则不参与分组。having 位于group by后面，如果不满足结果，分组则不会查询到。3、where之后不能跟聚合函数，having后面可以跟聚合函数*/SELECT sex,AVG(math) FROM student GROUP BY sex;#统计男女数学平均分，GROUP BY按性别分组SELECT sex,AVG(english) FROM student GROUP BY sex;#统计男女英语平均分，GROUP BY按性别分组SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;#统计男女英语平均分，GROUP BY按性别分组并且统计分组的人数SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;#统计男女英语平均分，GROUP BY按性别分组，并且只统计数学分数>70的人数#统计男女英语平均分，GROUP BY按性别分组，并且只统计数学分数>70的人数,且只统计分组之后的人数>2的组SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;#分页查询 limit 开始索引，每页开始的条数#公式：开始的索引 = (当前页码 - 1)*每页显示的条数SELECT *FROM student LIMIT 0,3;SELECT *FROM student LIMIT 3,3SELECT *FROM student LIMIT 6,3;
约束
**概念：**对表中的数据进行限定，保证数据的正确性，有效性和完整性。
分类;
1. 非空约束：not null
2. 唯一约束：unique
3. 主键约束：primary key
4. 外键约束：foreign key
1. 非空约束：not null
SELECT DATABASE();CREATE TABLE employe(id INT ,NAME VARCHAR(20) NOT NULL); #在创建表的时候就进行约束SELECT *FROM employe;INSERT INTO employe(id,NAME)VALUE(33,NULL); INSERT INTO employe(id,NAME)VALUE(33,"liuzeyu");ALTER TABLE employe MODIFY NAME VARCHAR(20) ; #修改为name值可为null
ALTER TABLE employe MODIFY NAME VARCHAR(20) NOT NULL; #创建表之后修改为name值可为not null
此时会出现一个错误：
这个问题待解决请指教。
最后是通过度娘解决了这个问题：
UPDATE employe SET NAME=0 WHERE NAME IS NULL;
然后就可以创建表之后修改为name值可为not null，至于为什么将空值替换成0作为初学者表示没看懂。
而且我操作完这一条语句后，创建其它的表执行类似上述的操作再也没遇到1265错误代码了？？
已解决:
出现问题的原因是：
1、首先创建employe 表，name非空
2、其次分别插入两条数据来测试，一条设置name可以为空，一条则设置不为空
3、结果发现只有不为空的才能被添加到数据表中。
4、然后通过alter语句将约束条件清除，在添加一条name可以为空的数据
5、添加正常
6、最后妄想在改为name非空，则就会报出1265错误代码，原因就是表中已经存在了name为空的数据。
7、UPDATE employe SET NAME=0 WHERE NAME IS NULL;语句则就是将名字为空的name数据重置为0，解决了所有问题。
8、然后name就可以重新改为非空了，因为表中已经没有了name为null的数据了。
2. 唯一约束：unique
概念：同一字段不存在添加的数据值相同。
注意mysql的唯一性约束可以运行多个null重复
#唯一约束CREATE TABLE employe2(id INT,num VARCHAR(32) UNIQUE);INSERT INTO employe2(id,num)VALUE(11,"10086");SELECT *FROM employe2;INSERT INTO employe2(id,num)VALUE(22,"10086");SELECT *FROM employe2;#alter table employe2 modify num varchar(32); 这一条语句并不能删除唯一性约束#删除唯一性约束ALTER TABLE employe2 DROP INDEX num ;SELECT *FROM employe2;INSERT INTO employe2(id,num)VALUE(22,"10086");SELECT *FROM employe2;ALTER TABLE employe2 MODIFY num VARCHAR(32) UNIQUE;#创建表之后添加唯一约束SELECT *FROM employe2;INSERT INTO employe2(id,num)VALUE(22,"10086");SELECT *FROM employe2;
3. 主键约束：primary key
概念：主键是表中记录的唯一标识，一张表只能有一个主键。
#主键约束(非空且唯一)CREATE TABLE employe3(id INT PRIMARY KEY,NAME VARCHAR(20) );INSERT INTO employe3(id,NAME)VALUE(111,"liuzeyu");SELECT *FROM employe3;INSERT INTO employe3(id,NAME)VALUE(111,"jay");ALTER TABLE employe3 DROP PRIMARY KEY;#删除主键约束SELECT *FROM employe3;INSERT INTO employe3(id,NAME)VALUE(111,"jay");SELECT *FROM employe3;INSERT INTO employe3(id,NAME)VALUE(111,"jackMa");ALTER TABLE employe3 MODIFY id INT PRIMARY KEY; #创建表之后加入主键约束SELECT *FROM employe3;UPDATE employe3 SET id=222 WHERE NAME="liuzeyu"; #将name=liuzeyu的数据修改idDELETE FROM employe3 WHERE id=111; #删除id值为222的数据SELECT *FROM employe3;INSERT INTO employe3(id,NAME)VALUE(111,"jay");INSERT INTO employe3(id,NAME)VALUE(111,"jackMa");SELECT *FROM employe3;CREATE TABLE employe4(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));#主键自动增长SELECT *FROM employe4;INSERT INTO employe4 VALUE(1,"liuzeyu");SELECT *FROM employe4;INSERT INTO employe4 VALUE(NULL,"jay");INSERT INTO employe4 VALUE(4,"jay");SELECT *FROM employe4;INSERT INTO employe4 VALUE(NULL,"jay");ALTER TABLE employe4 MODIFY id INT; #删除自动增长，此处仍然是主键ALTER TABLE employe4 MODIFY id INT AUTO_INCREMENT;#创建表并设置主键后，添加自动增长SELECT *FROM employe4;INSERT INTO employe4 VALUE(NULL,"jay");SELECT *FROM employe4;
4. 外键约束：foreign key
#创建员工表并添加数据CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(30),age INT,dep_name VARCHAR(30), -- 部门名称dep_location VARCHAR(30) -- 部门位置);-- 添加数据INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳')
查看表格出现冗余数据，后期还会出现增删改的问题
解决方案：将emp表格拆分成两个表department,department,拆分如下：
-- 创建部门表(id,dep_name,dep_location)SELECT *FROM emp;CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,dep_name VARCHAR(20),dep_location VARCHAR(20));SELECT *FROM department;-- 创建员工表(id,name,age,dep_id)-- 多方，从表CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,dep_id INT -- 外键对应主表的主键)SELECT *FROM employee;
添加数据
-- 添加 2 个部门INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');SELECT * FROM department;-- 添加员工,dep_id 表示员工所在的部门INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);17 / 26INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);SELECT * FROM employee;
查看拆分后的表数据(部门表和员工表)：
概念:foreign key外键约束就是将表与表直接产生关系，从而保证数据的准确性和安全性。这里体现在将员工表的外键dep_id与部门表的主键id相关联起来，从而保证数据的一一对应关系。
目标：需要约束dep_id只能是部门表中已存在的id
解决方式：使用外键约束
什么是外键：就是就是主表与从表对应的那一列，如员工表的dep_id
主表：一方，用来约束别人的表
从表：另一方，被别人约束的表
现在测试外键约束，在创建表的时候再从表中添加外键约束
-- 创建部门表(id,dep_name,dep_location)主表CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,dep_name VARCHAR(20),dep_location VARCHAR(20));SELECT *FROM department;-- 创建员工表(id,name,age,dep_id)-- 多方，从表CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,dep_id INT, -- 外键对应主表的主键CONSTRAINT fk_depid FOREIGN KEY (dep_id) REFERENCES department(id))SELECT *FROM employee;
格式：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
添加上述测试数据后，如果想要在员工表中添加dep_id为3的数据将添加失败！！原因是外键约束的存在。
ALTER TABLE employee DROP FOREIGN KEY fk_depid;#删除外键约束
测试一下外键约束是否还存在？
发现数据已经可以成功添加了，说明外键约束已经不存在了。
ALTER TABLE employee ADD CONSTRAINT fk_depid FOREIGN KEY(dep_id) REFERENCES department(id);#创建表后添加外键约束
如果遇到
错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (db1.#sql-104c_7, CONSTRAINTfk_depidFOREIGN KEY (dep_id) REFERENCESdepartment(id))
则极有可能是因为在生成外键的同时，表中的数据已经违反了形成外键的规则，例如上述案例中，员工表存在dep_id=100的就会出现错误代码1452，并且不能形成外键。
补充：表的级联操作
概念：在表和表之间有关系的前提下(外键约束)，通过操作外键可以影响另一张表的数据。
在创建外键的同时加入级联操作
ALTER TABLE employee ADD CONSTRAINT fk_depid FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE; #为外键同时增加级联更新和级联删除
此时如果修改
相对应的员工表数据也会相应更新==>
删除部门表的id对应的某条数据，员工表的数据也会相应的删除，不演示了。
故级联操作虽然方便，但是存在安全隐患，再很大数据库中，往往很多表直接都会存在一些关联，如果操作不当极有可能删库跑路了，所以还是慎用！！
重点：要学会在表的时候添加约束，删除表的约束，创建表之后添加约束这几个重要的操作。
数据库的设计
1、多表之间的关系
1）分类：
一对一关系：如人和身份证，一个人只有一个身份证，一个身份证只能对应一个人。一对多关系：部门和员工，一个部门对应多个员工，一个员工从属于一个部门。多对多关系：学生和课程，一个课程可以被多个学生选择，一个学生也可以选择多个课程。
2）实现关系：
一对一：如人和身份证，可以在任意一方添加唯一的外键执行另一方的主键。
一对多：如部门和员工，在多的一方建立外键指向少的一方的主键。
多对多：如学生和课程，多对多关系需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为中间表的外键，分别指向两张表的主键。
案例
例子：数据库有三个实体，分别是旅游线路分类，旅游线路，用户。
通过分析设计数据库：
旅游线路分类------<一对多>---------旅游线路
旅游线路------<多对多>---------用户
由于用户和旅游线路之间是多对多的关系，它们中间需要一张表收藏表来建立关系，表中有两个字段，一个是用户id，另一个是线路id，充当表的外键，分别指向线路表和用户表的主键。
建立表之间关系：
SQL语句：
旅游分类表：
CREATE DATABASE db2;USE db2;SHOW TABLES;-- 创建旅游线路分类表 tab_category-- cid 旅游线路分类主键，自动增长-- cname 旅游线路分类名称非空，唯一，字符串 100CREATE TABLE tab_category (cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(100) NOT NULL UNIQUE)-- 添加旅游线路分类数据：INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');SELECT * FROM tab_category;
旅游线路表：
-- 创建旅游线路表 tab_route/*rid 旅游线路主键，自动增长rname 旅游线路名称非空，唯一，字符串 100price 价格rdate 上架时间，日期类型cid 外键，所属分类*/CREATE TABLE tab_route(rid INT PRIMARY KEY AUTO_INCREMENT,rname VARCHAR(100) NOT NULL UNIQUE,price DOUBLE,rdate DATE,cid INT,FOREIGN KEY (cid) REFERENCES tab_category(cid));-- 添加旅游线路数据INSERT INTO tab_route VALUES(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,'-01-27', 1),(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '-02-22', 3),(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】 ', 1699, '-01-27', 2),(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】 ', 2399, '-12-23',2),(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】 ', 799, '-04-10', 4);SELECT * FROM tab_route;
用户表：
/*创建用户表 tab_useruid 用户主键，自增长username 用户名长度 100，唯一，非空password 密码长度 30，非空name 真实姓名长度 100birthday 生日sex 性别，定长字符串 1telephone 手机号，字符串 11email 邮箱，字符串长度 100*/CREATE TABLE tab_user (uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(100) UNIQUE NOT NULL,PASSWORD VARCHAR(30) NOT NULL,NAME VARCHAR(100),birthday DATE,sex CHAR(1) DEFAULT '男',telephone VARCHAR(11),email VARCHAR(100));INSERT INTO tab_user VALUES(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@'),(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@');SELECT * FROM tab_user;
用户表和线路表的中间表(收藏表)
/*创建收藏表 tab_favorite(多对多的中间表)rid 旅游线路 id，外键date 收藏时间uid 用户 id，外键rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次*/CREATE TABLE tab_favorite (rid INT, -- 线路idDATE DATETIME,uid INT, -- 用户id-- 创建复合主键PRIMARY KEY(rid,uid), -- 联合主键FOREIGN KEY (rid) REFERENCES tab_route(rid),FOREIGN KEY(uid) REFERENCES tab_user(uid));INSERT INTO tab_favorite VALUES(1, '-01-01', 1), -- 老王选择厦门(2, '-02-11', 1), -- 老王选择桂林(3, '-03-21', 1), -- 老王选择泰国(2, '-04-21', 2), -- 小王选择桂林(3, '-05-08', 2), -- 小王选择泰国(5, '-06-02', 2); -- 小王选择迪士尼SELECT * FROM tab_favorite;
表与表之间建立完成之后：
数据库设计的范式
概念：设计数据库时需要遵守的一些规范，要遵守后面的规范要求，必须遵守前面的规范要求。
设计数据库时，遵守不同的规范要求，设计出合理的关系数据库，这些不同的规范要求称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
分类：
第一范式：每一列都是不可分割的数据项。
不满足第一范式，需要将系这一列合并为一列，如下：
第二范式：在第一范式的基础上，非码属性必须完全依赖于码（在第一范式的基础上消除了非主属性对主码的部分函数依赖）
几个概念：
函数依赖：A–>B ,如果通过A属性值(属性组)可以唯一确定B属性的值，则称B依赖于A。例如：学号—>姓名，（学号，课程名称）—>分数。完全函数依赖：A–>B，如果A是一个属性组，则B的属性值确定需要A中所有的属性值。例如：（学号，课程名称）—>分数。部分函数依赖：A–>B，如果A是一个属性组，则B的属性值确定需要A中某些的属性值。例如：（学号，课程名称）—>姓名。传递函数依赖：A–>B，B—>C,则A—>C,如果通过A属性或属性组可以唯一确定B属性的值，通过B属性（属性组）可以唯一确定C属性的值, 则C传递依赖于A。例如：学号—>系名，系名—>系主任。码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。例如：上表的码是（学号，课程名称）主属性：码属性组中的所有属性。非主属性：除了码外的属性。
第三范式：在2NF的基础上，任何非主属性不依赖于其它的非主属性，即在2NF上，消除传递函数依赖。
看出上表中并没有达到第二范式的要求，出现的问题如下：
1)数据冗余非常严重，特别在姓名，系名，系主任处。
2)数据添加的时候出现的问题，如果有新增的系和系主任时，直接加入其中表格将出现不合法。
3)删除数据的时候也将出现问题，如果要删除张无忌同学的数据，则系的数据也将一起删除掉，显然不合法。
解决方法：消除非主属性对码的部分函数依赖
分析：
表中的码（学号，课程名称），只有分数是完全依赖于码的，姓名，系名，系主任只是部分依赖于码的(因为这三个属性只依赖于学号即可)。所以要消除姓名，系名，系主任的部分依赖关系，需要拆分表，如下
可见，将姓名，系名，系主任拆分出来，已经消除了刚刚的问题1），数据不再冗余。此时问题2）3）还未被解决，需要借助于第三范式来解决。2NF上，消除传递函数依赖，学生表中存在的传递函数依赖是 学号—>系名，系名—>系主任，因此拆分如下：
此时的问题2）3）还就已经被解决了，达到第三范式。
数据库的备份和还原(跑路预定)
命令行： 语法 备份：mysqldump -u用户名 -p密码 数据库名称 > 保存路径还原： 登录数据库创建数据库使用数据库执行文件：source 文件路径C:\Windows\system32>mysqldump -uroot -p809080 db1 > C:\backup_sql/my.sql #备份--进入删除db1mysql> create database db1;Query OK, 1 row affected (0.00 sec)mysql> use db1;Database changedmysql> source C:\backup_sql/my.sql等待备份成功...
图形化工具：
备份：
恢复：
多表查询
查询语法 select 列名 from 表名 where 条件准备sql# 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dept_id INT,-- 部门idFOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键));INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'-02-24',1);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'-12-02',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'-08-08',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'-10-07',3);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'-03-14',1);SELECT *FROM emp;
# 创建部门表CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');SELECT *FROM dept;
笛卡尔积：
有两个集合A,B，取这两个集合的所以组成情况。要完成多表查询，需要消除无用的数据。
例如执行：
SELECT *FROM emp,dept;
将出现无用的数据
多表查询的分类：
多表查询得目的就是为了消除笛卡儿积出现无效数据的结果。
1、内连接查询（显式和隐式结果一样）
隐式内连接查询(where)
-- 查询所有员工信息和对应的部门信息SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;-- 查询员工表的名称，性别,部门表的名称SELECT emp.`name`,emp.gender,dept.`name` FROM emp,dept WHERE emp.`dept_id`= dept.`id`;#标准的sql写法#正确写法SELECT t1.`name`,t1.`gender`,t2.`name`FROMemp t1,dept t2WHEREt1.`dept_id` = t2.`id`;
显式内连接查询(on)
-- 查询所有员工信息和对应的部门信息SELECT *FROM emp INNER JOIN dept WHERE emp.`dept_id`=dept.`id`;SELECT *FROM emp JOIN dept WHERE emp.`dept_id`=dept.`id`;
2、外连接查询
左外连接
#左外连接：查询左表全部+左右表交集部分SELECT *FROM emp;SELECT *FROM dept;-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
右外连接
#右外连接：查询右表全部+左右表交集部分SELECT *FROM dept t1 RIGHT JOIN emp t2 ON t2.`dept_id`=t1.`id`;
3、子查询
概念：查询中嵌套查询，称嵌套的查询为子查询。
子查询可以作为条件，使用运算符去判断，> >= < <= =
子查询的结果是单行单列的：
-- 查询员工工资大于平均工资的人SELECT AVG(salary) FROM emp; #求平均工资SELECT *FROM emp WHERE (SELECT AVG(salary) FROM emp)<emp.salary;#子查询的结果是单行单列的：
子查询的结果是多行单列的：
子查询可以作为条件，使用in来判断
-- 查询'财务部'和'市场部'所有的员工信息SELECT *FROM dept WHERE NAME='财务部' OR NAME='市场部';SELECT *FROM emp WHERE dept_id=2 OR dept_id=3; SELECT *FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME='财务部' OR NAME='市场部');
子查询的结果是多行多列的：
-- 子查询的结果可以作为一张虚拟表进行查询-- 查询员工入职日期是-11-11日之后的员工信息和部门信息SELECT *FROM dept t1,(SELECT *FROM emp WHERE join_date > '-11-11')t2 WHERE t1.`id` = t2.`dept_id`;#也可以通过普通的内连接查询SELECT *FROM dept t1,emp t2 WHERE t2.`join_date`> '-11-11' AND t1.`id`=t2.`dept_id`;
多表查询练习
准备sql:
CREATE DATABASE db4;USE db4;-- 部门表CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门所在地);-- 添加4个部门INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'),(20,'学工部','上海'),(30,'销售部','广州'),(40,'财务部','深圳');SELECT *FROM dept;-- 职务表，职务名称，职务描述CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50));-- 添加4个职务INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单'),(2, '经理', '管理部门员工'),(3, '销售员', '向客人推销产品'),(4, '文员', '使用办公软件');SELECT *FROM job;-- 员工表CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id));-- 添加员工INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),(1008,'猪八戒',4,1004,'-04-19','30000.00',NULL,20),(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),(1011,'沙僧',4,1004,'-05-23','11000.00',NULL,20),(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);SELECT *FROM emp;-- 工资等级表CREATE TABLE salarygrade (grade INT PRIMARY KEY, -- 级别losalary INT, -- 最低工资hisalary INT -- 最高工资);-- 添加5个工资等级INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000),(2,1,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990);SELECT *FROM salarygrade;
-- 需求：-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述SELECT t1.`id`,t1.`ename`,t1.`salary`,t2.`jname`,t2.`description`FROM emp t1,job t2WHEREt2.`id` = t1.`job_id`;-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置SELECT t1.`id`,t1.`ename`,t1.`salary`,t2.`jname`,t2.`description`,t3.`dname`,t3.`loc`FROM emp t1,job t2,dept t3WHERE t1.`job_id`=t2.`id` AND t1.`dept_id` = t3.`id`;-- 3.查询员工姓名，工资，工资等级SELECT grade FROM salarygrade WHERE 10000 BETWEEN losalary AND hisalary;#用于测试SELECT t1.`ename`,t1.`salary`,t2.`grade`FROMemp t1,salarygrade t2WHEREt1.salary BETWEEN losalary AND hisalary;-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级SELECTt1.`ename`,t1.`salary`,t2.`jname`,t2.`description`,t3.`dname`,t3.`loc`,t4.`grade`FROMemp t1,job t2,dept t3,salarygrade t4WHEREt1.`job_id`= t2.`id`ANDt1.`dept_id`=t3.`id`AND t1.salary BETWEEN losalary AND hisalary;
事务
1、事务的基本介绍
概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。当业务操作没有事务的情况下，例子
CREATE DATABASE db1;USE db1;CREATE TABLE account(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),balance DOUBLE);INSERT INTO account(NAME,balance) VALUE('zhangsan',1000),('lisi',1000);SELECT * FROM account;-- 需求：张三给李四转账 500 元UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';SELECT * FROM account;-- 因为DML语句是自动提交事务的，所以这个操作将会被成功实现
但是如果遇到异常情况下，如
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';出错了.... -- 很明显这一条SQL语句将会报错，导致下面的语句都不会继续执行UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';SELECT * FROM account;
这时就会遇到zhangsan的钱少了500，而lisi的钱没有变化，这在现实生活中是不会被允许的，所以在此引入事务。
操作：
开启事务：START TRANSACTION; – 如果只开启事务没有提交，则事务的操作将不会被执行，如
START TRANSACTION;-- 张三给李四转账 500 元（DML语句是自动提交事务的）UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';SELECT * FROM account;
虽然查询数据的时候实现了功能，但是在数据库断开后重连，数据将被还原到START TRANSACTION;之前，这说明修改的数据只是临时改变的。
提交事务：COMMIT;
START TRANSACTION;-- 张三给李四转账 500 元（DML语句是自动提交事务的）UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 发现执行没有问题，提交事务COMMIT;
由于DML语句是自动提交事务的，所以在正常没出错情况小和没添加事务操作是一样的。
但是如果出错呢？
START TRANSACTION;-- 张三给李四转账 500 元（DML语句是自动提交事务的）UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';出错了...UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 如果有问题，回滚ROLLBACK; -- 保证了数据的安全性-- 发现执行没有问题，提交事务COMMIT;SELECT * FROM account;
使用回滚事务必须在提交事务之前，从而保证事务的安全性。
2、事务提交
事务提交的两种方式：
* 自动提交：* mysql就是自动提交的* 一条DML(增删改)语句会自动提交一次事务。* 手动提交：* Oracle 数据库默认是手动提交事务* 需要先开启事务，再提交* 修改事务的默认提交方式：* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交* 修改默认提交方式： set @@autocommit = 0;
注意：如果设置事务为手动提交，则需要去开启事务，提交事务，SQL语句才能被执行，在错误的地方还要设置回滚事务，从而保证数据的安全性。
3、事务的四大基本特征
原子性：是不可分割的最小单位，要么同时成功，要么同时失败。持久性：当数据提交或回滚后，数据库回持久化保存数据。隔离性：当多个事务之间相互独立。一致性：事务操作前后，数据总量不变。
4、事务的隔离级别概念：多个事务之间是相互隔离，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，类似于线程的并发操作，，设置不同的隔离级别可以解决这些问题。存在问题： 脏读：一个事务读取到另一个事务中没有提交的事务。不可重复读(虚读)：在同一事务中，两次读取到的数据不一样。幻读：一个事物操作(DML)数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。 隔离级别： read uncommitted:读未提交
read committed :读已提交 (Oracle默认)还会产生问题：脏读，不可重复读，幻读。
repeatable read: 可重复读 (MySQL默认)还会产生问题：不可重复读，幻读。
serializable:串行化还会产生问题：幻读。
解决所有的问题。
注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
查询数据库隔离级别：
SELECT @@tx_isolation; -- 查询隔离级别
数据库设置隔离级别：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别
设置隔离级别后需要重启回话才能查询查询成功。
案例：张三给李四转账500块使用的隔离级别：read uncommitted
步骤：
1）将事务的隔离级别设置为：read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别
2）操作转账操作：
START TRANSACTION; -- 开启事务1-- 张三给李四转账 500 元（DML语句是自动提交事务的）UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
未提交事务，开启另外一个会话并开启事务2，查询数据，发现数据发生变化。
mysql> select * from account;
±—±---------±--------+
| id | name | balance |
±—±---------±--------+
| 1 | zhangsan | 500 |
| 2 | lisi | 1500 |
±—±---------±--------+
出现了脏读：事务2读取到另一个事务中没有提交的事务1。
3）此时如果张三执行事务的回滚操作，数据就会被重新恢复到开启事务前。
mysql> select * from account;
±—±---------±--------+
| id | name | balance |
±—±---------±--------+
| 1 | zhangsan | 1000 |
| 2 | lisi | 1000 |
±—±---------±--------+
2 rows in set (0.00 sec)
这个时候也出现了不可重复读：在同一事务中，两次读取到的数据不一样。
这种情况下有什么危害：举个例子：
如果张三借给李四500块，借完之后跟张三说钱已经转过去了，李四去查一下真的有了，而且还写了欠条并拿给了张三，这时如果张三执行了回滚操作，钱被偷偷的转回去了，欠条还在，这时李四就哭晕在厕所，明明没借钱，却要去还欠条上借的500块。
解决方案：
将事务的隔离级别设置为：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别SELECT @@tx_isolation; -- 查询隔离级别
再次执行转账操作(两边都要开启事务，另一方是用来查询的:
START TRANSACTION; -- 开启事务1UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
张三再次执行回滚操作rollback
mysql> select * from account;
±—±---------±--------+
| id | name | balance |
±—±---------±--------+
| 1 | zhangsan | 500 |
| 2 | lisi | 1500 |
±—±---------±--------+
骚操作将不会成功，于是就解决的脏读问题。
但是不可重复读的问题还是没解决，两边开启事务后马上查询的和另一方事务执行后提交的结果不一致，在一些特定项目中是不会被允许的，如报表系统，即时性比较强的系统。
如何解决不可重复读的问题呢？将事务的隔离级别设置为repeatable read:
SET GLOBAL TRANSACTION ISOLATION LEVEL repeatable read; -- 设置隔离级别START TRANSACTION;-- 事务1UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
在新的窗口开启连接，并开启新的事务2，此时不管事务1有没有提交事务，在事务2中查询到的结果都是一样的（都是初始数据1000 1000），只有当事务2commit之后数据才发生变化，钱才转过去了，这就解决的不可重复读的问题。
最最后引入串行化，serializable可以解决一切问题，类似于一个线程锁一样
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL serializable;
同样执行转账操作：
START TRANSACTION; -- 事务1-- 张三给李四转账 500 元（DML语句是自动提交事务的）UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
当事务2要查询事务1的操作结果时，发现光标在闪烁，只有当事务1提交事务后，事务2的查询结果才会出现，这就解决了上述存在的各个问题了。
DCL：
SQL分类：
1）DDL:操作数据库和表
2)DML:对表中的数据进行增删改操作
3）DQL：查询表数据
4）DCL：管理用户，授权
DBA数据库管理员
DCL：管理用户，授权
– 查询用户，创建用户，用户，修改用户密码。
USE mysql;SELECT *FROM USER;CREATE USER 'liuzeyu'@'localhost' IDENTIFIED BY '123'; -- 创建liuzeyu 密码 123的用户CREATE USER 'liuzeyu2'@'localhost' IDENTIFIED BY '456';DROP USER 'liuzeyu2'@'localhost';-- 删除用户SELECT *FROM USER;-- 修改用户密码-- 修改用户密码（两种方法）UPDATE USER SET PASSWORD = PASSWORD('222') WHERE USER = 'liuzeyu';SELECT *FROM USER;UPDATE USER SET PASSWORD = PASSWORD('333') WHERE USER = 'liuzeyu';SELECT *FROM USER;SET PASSWORD FOR 'liuzeyu'@'localhost' = PASSWORD('000');
* 通配符： % 表示可以在任意主机使用用户登录数据库
mysql中忘记了root用户的密码？
1. cmd -- > net stop mysql 停止mysql服务* 需要管理员运行该cmd2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功4. use mysql;5. update user set password = password('你的新密码') where user = 'root';6. 关闭两个窗口7. 打开任务管理器，手动结束mysqld.exe 的进程8. 启动mysql服务9. 使用新密码登录。
权限管理：
1. 查询权限：-- 查询权限SHOW GRANTS FOR '用户名'@'主机名';SHOW GRANTS FOR 'lisi'@'%'; --使用任意主机上登陆的list用户权限2. 授予权限：-- 授予权限grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';-- 给张三用户授予所有权限，在任意数据库任意表上GRANT ALL ON *.* TO 'zhangsan'@'localhost';3. 撤销权限：-- 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
