MySQL全总结三-DML, DDL和TCL 语言 1.99

文章目录

三, Mysql命令之DML语言
四, Mysql命令之DDL语言
五, Mysql命令之TCL语言

三, 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在删除之后回滚无效.

posted @ 2022-05-26 20:31 青松城阅读(65) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

sha-Pao-Zi

MySQL全总结三-DML, DDL和TCL 语言 1.99

文章目录

三, 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 在事务使用时的区别

公告