Mysql第二部分

1.DML Manipulation 增删改

数据操纵语言  	  Data Manipulation Language
插入 				insert into   
删除 				delete 
更新 				update 表 set

1 insert

语法

insert into 表 (字段1，字段2，…) values(值1，值2，…)

1 方式1 insert into 表名(列名,...) values(值1,...);
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2),
(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

2 方式2 insert into 表名 values(值1,...);
INSERT INTO beauty VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2),
(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2),

3 方式3 set    不支持多行
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

2 update

语法

1 单表

update 表 set 字段1=值1,字段2=2值,… where 条件;

2 多表

update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;

1 单表
    UPDATE beauty SET phone = '13899888899'
    WHERE NAME LIKE '唐%';

2 多表
    UPDATE boys bo
    RIGHT JOIN beauty b 
    ON bo.`id`=b.`boyfriend_id`
    SET b.`boyfriend_id`=2
    WHERE bo.`id` IS NULL;

3 delete truncate

1 delete

• 单表的删除【★】
  delete from 表名 where 筛选条件

• 多表删除 : 多表删除要删除哪个表就将别名写在delete后面

  delete 表1别名, 表2别名
  from 表1 起别名
  inner|left|right join 表2 起别名
  on 连接条件
  where 筛选条件;

1 单表案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

2 多表案例：删除张无忌的女朋友的信息(删除beauty表中记录)
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';

2 truncate truncate table 表名;

	where筛选	自增长	返回值	回滚	效率
delete	√	继续原来	有	能	低点
truncate	×	从新开始	无	不能	数据多 高

truncate不会影响事务

2.DDL definition 定义

data definition language 数据定义语言

约束

primary key    		#主键  只有一个
	auto_increament					#自增 多伴随主键
	set auto_increment_increment=3;	#设置增长步长
foreign key     	#外键
	on delete cascade			#外键级联删除
	on delete set null 			#外键级联置空
unique  			#唯一(可以为空。但只能有一个null，多了就不唯一了)
default         	#默认
not null 			#非空
check  				#检查

标记约束没有默认非空

1 create

建表常用格式

constraint 名称 foreign key()  reference 表(key)

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY auto_increment,
	stuname VARCHAR(20),
	sex CHAR(1) check(sex='男' or sex='女'),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT  fk_stuinfo_major 
    FOREIGN KEY(majorid) REFERENCES major(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;;

建中间表格式

用于多对多建中间表，中间表两个属性都为主键和外键

CREATE TABLE `users_role` (
    `userId` int(11) NOT NULL DEFAULT '0',
    `roleId` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`userId`,`roleId`),
    CONSTRAINT `users_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`),
    CONSTRAINT `users_role_ibfk_2` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

索引

1 索引分类

InnoDB默认B-tree

• 主键索引（primary key）
  • 唯一标识，不可重复，只能一个主键
• 唯一索引（unique key）
  • 避免重复列出现，多个列都可以被标记为唯一索引（可重复）
• 常规索引（key/index）
  • 不设置默认
• 全文索引（FullText）
  • 快速定位数据

2 索引的使用

显示所有的索引：show index from table

1 建表时给字段增加索引

2 创建完成后修改增加索引 alert

2 alert

通用语法

alter table 表名 
add添加|drop删除|modify修改约束|change修改列名	
column 列名 【列类型 约束】;

1 修改约束

通用修改约束语法
alter table 表名 modify column 字段名 字段类型 新约束;
修改列级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

---

列级约束（默认非空） 表级约束（其他）

• chekc mysql不支持检查

• 表级约束没有默认和非空

  主键 外键 唯一 检查

主键

注：主键只有一个不需要加字段名，而外键(forign key)、唯一(index)多个需加名

1 添加
alter table stuinfo modify column id int primary key;
alter table stuinfo add column primary key(id);
2 删除
alter table stuinfo drop  primary key

唯一

1 添加
alter table stuinfo modify column seat int unique;

alter table stuinfo add column unique(seat);
2 删除
alter table stuinfo drop index seat;

外键

alter table 表名 drop foreign key 外键名

1 添加
    alter table stuinfo 
    add constraint fk_stuinfo_major 
    foreign key(majorId) 
    references major(id);
2 删除
alter table stuinfo drop foreign key fk_stuinfo_major;

外键的级联删除 on delete cascade

先将外键 加上 on delete cascade 然后再删除一个外键表的一个id

另一个表中对应的都一起删除

alter table testu add 
CONSTRAINT fk_testu_major 
FOREIGN KEY(majorId) 
REFERENCES major(id) 
ON DELETE CASCADE;
进行删除测试
delete from major where id = 1;

外键的级联置空 on delete set null

alter table testu 
ADD CONSTRAINT fk_testu_major 
FOREIGN KEY(majorId) 
REFERENCES major(id) 
ON DELETE SET NULL;
进行删除测试
delete from major WHERE id = 1;

---

列级约束（默认非空）

alter table 表名 modify column 字段名 字段类型 新约束;

非空

1 添加
alter table stuinfo modify column stuname varchar(20) not null;
2 删除
alter table stuinfo modify column stuname varchar(20) null;

默认

1 添加
alter table stuinfo modify column age int default 18;
2 删除
alter table stuinfo modify column age int;

2 修改列名 change column

alter table book change column bname bName VARCHAR(20);

3 修改表名 rename to

alter table author rename to book_author;

4 添加列 add column

alter table add column annual double;

5 删除列 drop column

alter table book_author DROP COLUMN  annual;

2 drop删除表

语法：

drop table [if exists] 表名;

DROP TABLE IF EXISTS book_author;

3.TCL Transaction事物

TCL（Transaction Control Language）事务控制语言
COMMIT 提交
ROLLBACK 回滚
SAVEPOINT 在事务中设置保存点，可以回滚到此处
SET TRANSACTION 改变事务选项

一、事务

事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行

二、特点（ACID）

A 原子性 atomicity ：
一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性 consistency ：
一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性 isolation ：
一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性 durability ：
一个事务一旦提交了，则永久的持久化到本地

三步骤

①开启事务
set autocommit = 0;
②编写一组逻辑sql语句
insert、update、delete
设置回滚点：
savepoint 回滚点名;
③结束事务
提交：commit;
回滚：rollback;

隔离级别

oracle 默认 read committed
mysql 默认 repeatable read

 						脏读		不可重复读	幻读

read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √

解释：

read uncommitted读未提交脏读：

​ 事物1开启事物，插入/更新/删除 完 但不提交
​ 事物2 查询1次显示为事物1更新的结果 然后1回滚 2在查询一次为原来结果 为脏读 读未提交

不可重复读：

​ 事物1开启事物 事物2查询1次但权限问题只能查询到事物1未更新的结果 事物1commit 提交 事物2再查询一次 结果为更新的结果 为不可重复读

幻读：事物1开启事物准备查询或更新 事物2开启事物并插入一条 事物1提交发现多查出/更新了 一条记录 为幻读

事务相关命令

设置隔离级别

set transaction isolation level read committed;

设置数据库全局的隔离级别

set global transaction isolation level read committed;

创建mysql数据库用户

create user tom identified by ‘abc123’;

授权

所有库所有表全部权限
grant all privileges on *.* to tom@'%' identified by 'abc123'

授予某个表的插删改查权限
grant select,insert,delete,update on 表.* to tom@localhost identified by 'abc123'

4.其他

用户管理

本质对mysql.user表进行增删改查

1 创建用户

CREATE USER hlc IDENTIFIED BY '123456'
identified:确认;认出;鉴定;找到;发现;显示;说明身份
修改当前用户密码
SET PASSWORD - PASSWORD(‘123456’);
修改指定用户密码
SET PASSWORD for hlc - PASSWORD(‘123456’);
重命名用户
rename user hlc to hlcc;
删除用户
drop user hlcc

2 授权

on表 to用户
GRANT ALL PRIVILEGES ON *.* TO hlcc;
privileges 特权

查看用户权限
show grants for hlc;
SHOW GRANTS FOR root@localhost;
#root用户的授予权限GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

3 撤销权限

revoke ALL PRIVILEGES ON *.* FROM hlcc

视图

mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。

用法跟表一样 分定义和操纵用法

好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性

视图 类似DDL

创建or修改

create or replace view 视图名 AS 正常查询语句

创建、修改视图

CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

修改的另一种方式

alter view 视图名 AS 查询语句

CREATE OR REPLACE VIEW myv2
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

ALTER VIEW myv2
AS
SELECT * FROM employees;

不可以更新的视图

• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

删除

drop view 视图1，视图2…；

DROP VIEW emp_v1,emp_v2,myv3;

desc 视图名

show create table/view procedure/funciton 名

视图 dml

1.插入

insert

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

2.修改

update

UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

3.删除

delete

DELETE FROM myv1 WHERE last_name = '张无忌';

4.查看/使用

select

使用
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

备份

备份可以：保证数据不丢失，数据转移

主要方式：

• 拷贝物理文件data文件夹
• 可视化工具手动导出
• cmd命令行 mysqldump

命令行方式：

-h主机地址  -u用户名  -p密码 数据库 数据表 > 物理磁盘位置
C:\Users\AdminH>mysqldump -hlocalhost -uroot -proot dev user > A:/user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导出多张表
C:\Users\AdminH>mysqldump -hlocalhost -uroot -proot dev user role > A:/a.sql

导出整个数据库
C:\Users\AdminH>mysqldump -hlocalhost -uroot -proot dev  > A:/dev.sql

导入

先登录mysql
mysql -uroot -proot
mysql> source a:/dev.sql

变量

• 系统变量
  • 全局变量 作用于一次服务所有会话 关闭后恢复默认 (配置文件改)
  • 会话变量 作用于当前连接(会话)
• 自定义变量
  • 用户变量 作用于当前会话
  • 局部变量 begin end 之间

系统变量用法 统一

 	session 可以省略
查看变量     
	show gloable|session variables ;
查看部分     
	show gloable variables like 'auto' ;	
查看具体
	select @@gloable.autocommit;
设置值
	set @@gloable.autocommit = 1;

自定义变量

• 用户变量

  复制/更新值
  	SET @变量名=值;
  	SET @变量名:=值;
  	SELECT @变量名:=值;
  更新值
  	select 字段 INTO @变量名
  	from 表.......
  查看值
  	SELECT @变量名;
  

• 局部变量

定义变量
DECLARE 变量名 类型 【DEFAULT 值】;
其余跟自定义变量使用一致 

存储过程

存储过程和函数 ：类似于java中的方法

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法 存储过程不已应要有返回值

delimiter 结束标记
可以空参
in  out  inout

create procedure 存储过程名(参数模式 参数名 参数类型)
 BEGIN
	存储体
 END
 
 使用
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),
	('john2','0000'),
	('john3','0000'),
	('john4','0000'),
	('john5','0000');
END $
#调用
CALL myp1()$

函数

函数有且只有1个返回
参数列表(参数名 参数类型)

create function 函数名(参数列表) returns 返回类型
begin
函数体
returns 返回类型
end

调用   select
DELIMITER $
 CREATE FUNCTION f1() RETURNS INT
 BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
 END $
 
 SELECT f1()$

流程控制

1.if函数
语法：if(条件,值1，值2) 条件成立 返回值1
功能：实现双分支
应用在begin end中或外面

3.if结构 只能应用在begin end 中
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
功能：类似于多重if

2.case结构
语法：
情况1：类似于switch 实现等值判断
case 变量或表达式
when 要判断的值1 then 返回的值1;
when 值2 then 语句2;
…
else 语句n;
end

情况2：类似if 实现区间判断 成绩判断
case
when 条件1 then 语句1;
when 条件2 then 语句2;
…
else 语句n;
end
应用在begin end 中或外面

创建存储过程根据传入的成绩 显示等级
CREATE PROCEDURE test_case(IN score INT)
BEGIN
    CASE 
      WHEN score>=90 AND score<=100 THEN SELECT 'A';
      WHEN socre>=80 THEN SELECT 'B';
      WHEN score>=60 THEN SELECT 'C';
      ELSE SELECT 'D';
      END CASE;
END $
      
CALL test_case(90)$

CREATE FUNCTION test_f1(score INT) RETURNS CHAR
BEGIN
	IF score>=90 AND score<=100 THEN RETURN 'A';
	ELSEIF score>=80 THEN RETURN 'B';
	ELSEIF score>=60 THEN RETURN 'c';
	ELSE RETURN 'D';
	END IF;
END $
    
SELECT test_f1(88);

范式

范式：设计表的依据 避免冗余
1nf:要求表的每列是不可再分的原子
2nf: 在1nf基础上，消除非主属性对键的部分依赖
3nf: 在12nf基础上，非主键字段直接依赖主键，不能产生传递依赖

第一范式

任何一张表都有主键：要求表的每列是不可再分的原子性数据项

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wd45znXq-1615510280882)(assets/1614673034372.png)]

比如：可再分：家庭、学校应该设计为单独的表对应自己的主键id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SioR9uSb-1615510280893)(assets/1614673104978.png)]

第二范式

满足第一的基础上 非主键字段完全依赖主键 (每张表只做一件事)不能产生部分依赖。

比如：订单表中出现产品信息，不满足完全依赖，应该将产品单独作为一张表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iE33tg9h-1615510280899)(assets/1614673350995.png)]

衍生建表规范：

多对多 三张表 两个外键

第三范式

二范式的基础 非主键字段直接依赖主键 不能产生传递依赖

比如：

原表具有传递依赖

编号 姓名 编号 名称

1 张1 01 1班

2 张2 02 2班

3 张3 02 2班

---

拆：

学生表

no cname

1 张1

2 张2

年级表

cno(pk) cname

1 1班

2 2班

中间表

sno(pk) sname classno(fk)

01 张1 1

02 张2 1

03 张3 2

一对多 多建表设外键

为满足用户需求 常常牺牲冗余换取执行速度

规范和性能问题

关联的表不能超过三张表

考虑商业化的需求和目标，数据库的性能更为重要

• 在规范性能时，考虑规范性
• 故意增加一些字段冗余（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降为小数据量的查询：索引）