文章目录
- 三, Mysql命令之DML语言
- 3.1 插入语句
- 3.1.1 插入方法一
- 3.1.2 插入方法二
- 3.1.3 两种插入方式的比较
- 3.2 修改语句
- 3.3 删除语句(delete + truncate)
- 3.3.1 单表的删除
- 3.3.2 级联删除(多表的删除)
- 3.3.3 truncate 语句
- 四, Mysql命令之DDL语言
- 4.1 库的管理
- 4.2 表的管理
- 4.2.1 表的创建
- 4.2.2 表的修改
- 4.2.3 表的复制
- 4.3 常见的数据类型
- 4.3.1 无符号和有符号
- 4.3.2 DOUBLE(M,D)
- 4.3.3 字符型
- 4.3.4 枚举和set
- 4.4 约束
- 4.4.1 创建表时添加约束
- 4.4.1.1 添加列级约束
- 4.4.1.2 添加表级约束
- 4.4.1.3 约束的通用写法
- 4.4.2 修改表时添加约束
- 4.4.3 修改表时删除约束
- 4.5 主键和唯一的区别(重要) (▷)
- 4.6 对外键的补充说明(▷)
- 4.7 标识列(自增长列)
- 五, Mysql命令之TCL语言
- 5.1 事务
- 5.1.1, 事务的创建
- 5.1.2 事务的并发及其出现的问题
- 5.1.3 事务的隔离级别
- 5.1.4 在mysql中设置隔离级别(======)
- 5.2 视图
- 5.2.1 视图的创建( create view as)
- 5.2.2 视图的修改(create or replace, alter view as )
- 5.2.3 视图的删除(drop)
- 5.2.4 视图的查看(desc 视图名;)
- 5.2.5 视图的更新
- 5.2.6 视图和表的对比
- 5.2.7 delete 和 truncate 在事务使用时的区别
三, Mysql命令之DML语言
DML (Data Manipulate Language)-数据操纵语言
DML语言
插入-insert , 删除-delete, 更新-update
3.1 插入语句
3.1.1 插入方法一
语法
insert into 表名(列名1, 列名2,...)
values(值1,值2,.....)
特点:
1、要求值的类型和字段的类型要一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(18,'王兰贤','女','1988-1-2',13292962709,NULL,9);
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
INSERT INTO beauty(id,NAME,phone,boyfriend_id,sex)
VALUES(14,'周周洲',1232432454,1,'男')
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
- ①字段和值都省略
- ②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
3.1.2 插入方法二
语法:
insert into 表名
set 列名=值, 列名=值...
3.1.3 两种插入方式的比较
方式1支持插入多行数据,方式2不支持
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES
(18,'王兰贤','女','1988-1-2',1321962709,NULL,9),
(19,'如花','女','1988-1-2',132934462709,NULL,9),
(20,'花花','女','1988-1-2',13267962709,NULL,9);
方式一还支持子查询,方式二不支持
举个栗子:
- 把select对应的结果集对应的插入到 insert语句中的列名中去. 为什么叫子查询? 因为子句是select查询语句,主句可以是select,也可以是insert等等.
Insert语句和子查询的奇妙结合: ☆
3.2 修改语句
语法:
- 修改单表记录
update 表名
set 列=新增, 列=新增,....
where 筛选条件
举个栗子:
#修改表中姓周的电话号为 110
UPDATE beauty
SET phone=110
WHERE beauty.name LIKE '%周%';
- 修改多表记录( 修改多表一定别忘了多表的连接!) ★
语法:
## SQL92 语法
update 表1 别名 ,表2 别名
set 列=值
where 连接条件
and 筛选条件;
## SQL99 语法
update 表1 别名
inner | left | right 表2 别名
on 连接条件
set 列=值
where 筛选条件
举个栗子:
#修改没有男朋友的女神的男朋友编号都为2号
####读题我们可知,处理的是两个表的信息,并且女神表beauty是主表,boys是从表,需要用到外连接查询.
### 又进一步,说是限定没有男朋友的女神,这就需要我们明白目的是查询外链接中主表的特有部分.
UPDATE boys bo
RIGHT OUTER JOIN beauty be
ON bo.`id` = be.`boyfriend_id`
SET be.`boyfriend_id`= 2
WHERE bo.`id` IS NULL;
3.3 删除语句(delete + truncate)
特点: 删除的是整行,不是说特定的字段值
3.3.1 单表的删除
格式:
delete from 表名 where 筛选条件
3.3.2 级联删除(多表的删除)
格式:
/* 注意!!! delete后面的字段是**你想对之执行删除操作的表名或表的别名**, 想删表1中的数据, 就把表1的别名写上,想两个表的数据都删除,就把两个表的别名分别都写上
*/
sql92 语法:
delete 表1的别名, 表2的别名
from 表1 别名, 表2 别名
where 连接条件
and 筛选条件
sql99 语法:
delete 表1的别名, 表2的别名
from 表1 别名
inner | left | right join 表2 别名
on 连接条件
where 筛选条件
- 举个栗子:
# 删除张无忌女朋友的信息
DELETE beauty
FROM beauty
LEFT OUTER JOIN boys
ON beauty.`boyfriend_id` = boys.`id`
WHERE boys.`boyName`='张无忌';
# 删除黄晓明以及其女朋友的信息
DELETE bo, be
FROM boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`boyName`='黄晓明';
3.3.3 truncate 语句
用于删除整个表(table)的信息
,所以也叫清空.
等同于 delete from 表名.格式: truncate table xx;
delete 与 truncate 的辨析
- where语句–delete
可以加上 where语句
, truncate 不能- 效率–truncate删除,
效率
高一丢丢- 自增列–若加入要删除的表中有
自增长列
, 如果用delete删除后,再插入数据,自增长列的值从断点开始
,而用truncate删除后,再插入数据,自增长列的值从1开始
.- 返回值—truncate 删除没有
返回值
, delete 删除有返回值- 回滚—truncate删除没有
回滚
, 而 delete 删除可以回滚对返回值的解释:
truncate删除没有返回值
delete 删除有返回值
四, Mysql命令之DDL语言
DDL(data definition language)-数据定义语言
DDL中 包括库和表的管理:创建(create)
修改(alter)
删除(drop)
4.1 库的管理
- 库的创建(create)
语法
create database 数据库名;
## 为了提高容错性,可以增加一个判断,防止新建已经存在的数据库而报错.
create databases if not exists 数据库名
- 库的修改(alter)
因为修改数据库的名字不安全,
已废除的命令: rename databases xx to yy
实在想修改的话,可以直接修改数据库对应的文件夹
- 我们可以修改数据库的字符集
alter database 数据库名 character set gbk;
- 库的删除(drop)
语法:
drop database [if exists] 数据库名;
4.2 表的管理
4.2.1 表的创建
语法:
create table 表名(
* 列名 列的数据类型[数据长度] [约束],
列名 列的数据类型[数据长度] [约束],
列名 列的数据类型[数据长度] [约束],
...
列名 列的数据类型[数据长度] [约束]
)
举个栗子:
# 注意: mysql中的double数据类型的数据长度有两个参数,
# 第一个是数据总长度,第二个是保留小数的位数
## 创建新表
CREATE TABLE book(
id INT(10),
bname VARCHAR(20),
author VARCHAR(20),
price DOUBLE (10,2)
);
4.2.2 表的修改
格式:
alter table 表名 add | drop | modify | change column | 列名 [数据类型 约束];
- 修改列名(change column)
alter table 表名 change column 旧列名 新列名 列的数据类型
#栗子:
ALTER TABLE stuinfo CHANGE COLUMN address addr VARCHAR(20);
- 修改列的类型和约束(modify column)
alter table 表名 modify column 列名 修改后的数据类型/修改后的约束;
#栗子:
ALTER TABLE emp MODIFY COLUMN bossID VARCHAR(12) DEFAULT 2;
- 添加新列(add column)
alter table 表名 add column 新的列名 列的数据类型;
#栗子:
ALTER TABLE emp ADD COLUMN manager_id VARCHAR(20) DEFAULT 001;
- 删除列(drop column)
alter table 表名 drop column 要删除的列名;
#栗子:
ALTER TABLE emp DROP COLUMN boosID;
- 修改表名(rename to)
alter table 表名 rename to 新表名;
#栗子:
ALTER TABLE my_employees RENAME TO emp;
- 表的删除(drop 表名/数据库名)
drop table 表名;
4.2.3 表的复制
1, 仅仅复制表的结构(copy like)
create table copy LIKE 表名;
2, 复制表的结构和数据
## 1, 把表1的数据和表结构复制到表1
create table 表2
select * from 表1;
# 我们也可以利用筛选条件只复制表的部分数据
## 2, 仅仅复制某些字段
#### 只需让筛选条件为空即可
栗子:
create table copy1
select id,name
from author
where 0;
4.3 常见的数据类型
- 浮点型和定点型的比较: 定点型的精度较高.
4.3.1 无符号和有符号
特点:
- 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字(当创建表插入数据时,在数据类型后面添加unsigned关键字即可)
- 如果数据超出范围,则会报 out of range异常,并把插入的值变为临界值.
4.3.2 DOUBLE(M,D)
特点
- M: 整数部分+小数部分 D: 小数部分
如果输入的数据超过范围,则插入临界值(比如整数部分 > M-D, 则整数会变成M-D位的最大数.)- M和D可以省略,但是decimal(10,0)是decimal的默认写法.
4.3.3 字符型
4.3.4 枚举和set
- 跟数据类型一样使用即可.
4.4 约束
约束是一种限制,用于限制表中的数据,目的是为了保证表中的数据的准确性和可靠性.
分类:
约束 | 解释 |
not null | 非空,用于保证该字段的值不为空. |
default | 默认, 用于保证该字段有默认值 |
primary key | 主键,用于保证该字段的值具有唯一性,不能为空 |
unique | 唯一,用于保证该字段的值具有唯一性,可以为空 |
check | 检查约束, mysql不支持, 用于限制数据的取值,比如性别只能男 和 女 |
foreign key | 外键, 用于限制两个表的关系,用于保证该字段的值必须来自主表的关联列的值 |
添加约束的时机(必须在数据添加之前)
- 创建表时
- 修改表时
约束的添加分类:
- 列级约束: 六大约束语法上都支持,但是
检查,外键约束没有效果.- 表级约束: 除了
检查,非空,默认, 其他的都支持.一个列名可以是添加多个约束的, 无顺序要求, 只需空格即可
格式:
create table 表名{
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
}
4.4.1 创建表时添加约束
4.4.1.1 添加列级约束
- 语法:
直接在列名和类型后面追加约束类型即可.
只支持: 默认, 非空,主键,唯一.
mysql不支持 检查约束 以及 外键 在列级约束定义
- 举个栗子:
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空约束
gender CHAR(1) CHECK( gender='男' OR '女'), #检查约束
seat INT UNIQUE, #唯一约束
age INT DEFAULT, #默认约束
majorId INT REFERENCES major(id) #外键, major是表名,id是他的主键
#mysql不支持 检查约束 以及 外键 在列级约束定义
)
)
4.4.1.2 添加表级约束
- 语法:
在所有定义的字段下面添加表级约束
[constraint 自定义的约束名] 约束类型(字段名)
constraint 及其后的自定义约束可以省略
mysql不支持 非空, 默认 以及 检查约束 在表级约束定义
- 举个栗子:
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
##添加表级约束
# 我们可以在constrint关键字后自定义约束的别名
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一约束
CONSTRAINT ck CHECK(gender='男' OR '女'), #检查约束
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
## 列级约束中 非空约束,默认约束,检查约束 不支持
)
4.4.1.3 约束的通用写法
- mysql中, 列级约束不支持外键 和检查约束,
- 而在表级约束中不支持 非空, 默认, 和检查约束.
- 所以我们可以选择列级约束中定义 主键,非空,唯一,默认, 而在表级约束中定义 外键.
create table if exists stuinfo(
id int primary key,
stuname varchar(10) not null,
sex char(1),
age int default 19,
seat int unique,
majorid int,
constraint fk_当前表名_外键所在的表名 foreign key(majorid) references major(id)
)
4.4.2 修改表时添加约束
- 格式
#1. 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
#2. 添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名);
### 添加外键
alter table 表名 add [constraint 约束名] 约束类型(字段名) 外键的引用;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
4.4.3 修改表时删除约束
4.5 主键和唯一的区别(重要) (▷)
primary key && unique
- 区别:
- 一个表最多只能有一个主键,但可以有多个唯一
- 主键不允许为空,唯一可以为空(同一列的话,只能有一行为NULL值)
- 相同点
- 主键和唯一都具有唯一性
- 都支持组合键(多个列,共同组成一个主键或唯一),但不推荐
4.6 对外键的补充说明(▷)
外键的特点
我们把一个主键的表叫主表,相应的,引用了这个主键作为外键的表叫从表.
1、外键用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
3、主表的被引用列要求是一个key(可以为 唯一,主键约束, 但一般是主键)
4、插入数据时,先插入主表,再插从表; 删除数据时,先删除从表可以通过以下两种方式来删除主表的记录
方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
4.7 标识列(自增长列)
标识列又称为自增长列. 可以不用手动的插入值,系统提供默认的序列值,并按照步长递增.
关键字: auto_increment
在新增数据的时候,我们只需要将主键设置为null,0或者不设置该字段,数据库就会为我们自动生成一个主键值。
- 创建表时设置标识列
DROP TABLE increTest;
CREATE TABLE increTest(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)AUTO_INCAREMENT=3 ##设置自增长从3开始
INSERT INTO increTest
VALUES(NULL,'sb')
标识列的特点
- 标识列一定和主键搭配吗? 不一定,要求这个列是个key就行,比如
主键,外键和唯一都可以
在定义语句后加auto_increment
; - 一个表中最多
只能有一个标识
(自增列); - 标识列的
类型只能是数值型
(int,float,double等等),一般是int型. - SET auto_increment_increment=2; 我们通过这条语句可以把自增步长变为2.
- 修改表时设置标识列
举个栗子:
ALTER TABLE increTest MODIFY COLUMN id INT UNIQUE AUTO_INCREMENT;
- 修改表时删除标识列
ALTER TABLE increTest MODIFY COLUMN id INT;
五, Mysql命令之TCL语言
TCL( Transaction Control Language)- 事务控制语言
5.1 事务
事务:
一个或一组sql组成一个执行单元,这个执行单元要么全部执行,要么全部不执行.事务的特性(ACID属性):
A: atomicity,原子性.
- 指
事务是一个不可分割的工作单元,事务中的操作要么都发生,要么都不发生
.C: consistency,一致性.
事务必须使数据库从一个一致性状态转换到另外一个一致性状态
.I: isolation, 隔离性.
- 事务的隔离性是指
一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间相互隔离,互不干扰
.D: durability 持久性.
- 持久性是指一个
事务一旦被提交, 它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响.
5.1.1, 事务的创建
- 概念
- 隐式事务: 事务没有明显的开始和结束的标记. 比如(insert, update, delete)
- 显式事务: 事物具有明显的开启和结束的标记. 前提是必须先设置自动提交功能为禁用.
显式事务的创建:
# 步骤1. 开启事务
set autocommit =0;
start transaction;[可写可不写]
## 步骤2. 编写事务中的sql语句(主要是 select, insert,update,delete)
语句1;
语句2;
###步骤3. 结束事务
commit; 提交事务
//或
rollback; 回滚事务
举个栗子:
5.1.2 事务的并发及其出现的问题
- 事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时- 并发问题都有哪些? =======脏读,幻读和不可重复读
脏读: 对于两个事务t1, t2, t1读取了已经被t2更新(update)但还没有被提交的字段, 若
t2回滚
,则 t1读取的内容就是临时且无效的.不可重复读: 对于两个事务t1, t2, t1读取了一个字段,然后
t2更新了该字段之后
, t1再次读取同一个字段,值就不同了幻读: 对于两个事务t1, t2, t1从一个表中读取了一个字段, 然后
t2在该表中插入(insert)了一些新的行
之后, 如果t1再次读同一个表,就会多出几行.
DB数据并发问题 | 精炼 |
脏读 | 读取未提交数据,读到的是其他事务’更新’的数据 |
不可重复读 | 前后多次读取,数据内容不一致 |
幻读 | 前后多次读取,数据总量不一致 |
- 如何解决并发问题
通过设置隔离级别来解决并发问题
5.1.3 事务的隔离级别
- 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,使他们不会相互影响,避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别
.数据库规定了多种事务隔离级别,不同的隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱.
隔离级别从低到高:
隔离级别 | 描述 |
读未提交数据(read uncommitted) |
|
读已提交数据(read committed) |
|
可重复读(repeatable read) |
|
串行化(serialzable) |
|
5.1.4 在mysql中设置隔离级别(======)
================实践补一下啊!
savepoint-保存点; 搭配rollback使用.供其回滚到保存点位置;
举个栗子:
//xx为回滚保存点
rollback to xx;
5.2 视图
视图的定义: mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成。
好处:
- 简化sql语句
- 提高了sql的重用性
- 保护基表的数据,提高了安全性.
视图的应用场景:
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂.
5.2.1 视图的创建( create view as)
- 创建语法
create view 视图名
as
查询语句
举个栗子:
5.2.2 视图的修改(create or replace, alter view as )
方式1:
create or replace view 视图名
as
查询语句;
方式2:
alter view 视图名
举个栗子:
CREATE VIEW v1
AS
SELECT employee_id, last_name, jo. job_id,job_title
FROM employees emp
INNER JOIN jobs jo
ON emp.job_id = jo.job_id
CREATE OR REPLACE VIEW v1
//或者
ALTER VIEW v1
AS
SELECT department_name
FROM departments
WHERE department_id =50;
SELECT * FROM v1;
5.2.3 视图的删除(drop)
drop view 视图1, 视图2,...;
5.2.4 视图的查看(desc 视图名;)
5.2.5 视图的更新
视图支持增删改,并且数据的增删改也会同步到原始表.
注意:
视图一般用于查询,而不是更新, 具备以下特点的视图都不允许更新:
- 包含分组函数(聚集函数,max,min,sum,avg),group by, distinct, having, union
- join
- 常量视图
- where后的子查询用到了from中的表
- 用到了不可更新的视图
5.2.6 视图和表的对比
5.2.7 delete 和 truncate 在事务使用时的区别
delet删除数据之后支持回滚, 而truncate在删除之后回滚无效.