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
一对多 多建表设外键
为满足用户需求 常常牺牲冗余换取执行速度
规范和性能问题
关联的表不能超过三张表
考虑商业化的需求和目标,数据库的性能更为重要
- 在规范性能时,考虑规范性
- 故意增加一些字段冗余(从多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降为小数据量的查询:索引)