MYSQL基础小记II

参考了大佬的博客,自己又整理是为了熟悉语句

MySQL基础小记上

MySQL基础小记下

MYSQL基础小记II

四 DML数据操作语言

DML语言
插入insert
修改update
删除delete

1.插入语句

/*
方式一：
insert into 表名(列名,...) values(值1，...);
*/
#1、插入的值的类型要与列的类型一致或兼容
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#2、不可以为null的列必须插入值，可以为null的列如何插入值？
#方式一：列名写上，值为null
#方式二：列名省略，值省略
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(15,'杨幂','女','1986-9-12','1898888888',2);

#3、列的顺序可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');

#4、列数和值的个数必须一致（以下错误）
INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUES('蒋欣','女',16,'110');

#5、可以省略列名，默认是所有列，且顺序是表中列的顺序

#方式二
/*
insert into 表名
set 列名=值，列名=值...
*/
INSERT INTO beauty
SET id=17,NAME='刘涛',phone='999';

两种方法比较

#1、方式一支持插入多行
INSERT INTO beauty
VALUES(13,'唐艺昕1','女','1990-4-23','1898888888',NULL,2),
VALUES(18,'唐艺昕2','女','1990-4-23','1898888888',NULL,2),
VALUES(19,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

#2、方式一支持子查询，二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';#把这个查询语句的结果集插入到beauty

2.修改语句

修改单表的记录

1.修改单表的记录☆
语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;

#案例1、修改beauty表中姓唐的女生电话为138277777
UPDATE beauty SET phone='138277777'
WHERE NAME LIKE '唐%';

#案例2：修改boys表中id号为2的名称为张飞，魅力值为10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

修改多表的记录

2、修改多表的记录【补充】
update 表1 别名
inner/left/right join 表2 别名
on 连接条件
set 列=值...
where 筛选条件;

#案例1：修改张无忌的女朋友的手机号为1123
UPDATE boys bo
INNER JOIN beauty be ON bo.id=be.`boyfriend_id`
SET be.phone='1123'
WHERE bo.`boyName`='张无忌';

#案例2：修改 没有男朋友的女神 的男朋友编号 都为张飞的编号
UPDATE boys bo
RIGHT JOIN beauty be ON bo.id=be.`boyfriend_id`
SET be.boyfriend_id=2
WHERE bo.id IS NULL;

3.删除语句

方式一:

1.单表的删除☆
delete from 表名 where 筛选条件

2、多表的删除
delete 表1的别名，表2的别名
from 表1 别名
inner/left/right join 表2 别名 on 连接条件
where 筛选条件;

方式二：truncate
truncate table 表名

#方式一：delete

#1、单表的删除

#案例1：删除手机号以9结尾的女生信息
DELETE FROM beauty WHERE phone LIKE '%9';

#2、多表的删除

#案例：删除张无忌的女朋友的信息
DELETE be
FROM beauty be
INNER JOIN boys bo ON be.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌';

#案例：删除黄晓明的信息，以及他女朋友的信息
DELETE be,bo
FROM beauty be
INNER JOIN boys bo ON be.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';


#方式二：truncate语句
#案例：将魅力值>100的男生信息删除
TRUNCATE TABLE boys;

delete和truncate区别【面试题】

• 1、delete可以加where条件，truncate不可以。
• 2、truncate删除，效率高一些。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
• 3、假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始。
  而truncate删除后，再插入数据，自增长列的值从1开始
• 4、truncate删除没有返回值，delete删除有返回值。
• 5、truncate删除不能回滚，delete删除可以回滚。

五.DDL数据定义语言

1.库的管理

#1、库的创建
/*
create database (if not exists) 库名; #如果不存在则创建
*/

#案例：创建库books
CREATE DATABASE IF NOT EXISTS books;

#2、库的修改
#不安全，基本不修改，要么删了重建
ALTER DATABASE 库名；
#修改库名直接去C盘的MySQL下的data文件夹改名

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除
DROP DATABASE IF EXISTS books;

2.表的管理

表的创建

creat table 表名(
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	...
	列名 列的类型【（长度）约束】，
)

#案例：创建表book
CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#书名，最多20个字符
	price DOUBLE,#价格
	authorId INT,#作者编号
	publishDate DATETIME#出版日期
	
);
DESC book;#查看

#案例：创建表author
CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);
DESC author;

表的修改

/*
alter table 表名 add/drop/modify/change column 列名 【列类型 约束】
*/

#①修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;

#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;

#④删除列
ALTER TABLE author DROP COLUMN annual;

#⑤修改表名
ALTER TABLE author RENAME TO book_author;

表的删除

DROP TABLE book_author;
#通用的写法
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

表的复制

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

#1、仅仅复制表的结构
CREATE TABLE copy LIKE author;#复制的表命名copy

#2、复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;

#3、只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';

#4、仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;#只想要结构，不要数据，所以复制数据的条件恒不成立

3.常见的数据类型

#常见的数据类型

数值型：
			整型
			小数（定点数，浮点数）
字符型：
			较短的文本：char、varchar
			较长的文本：text、blob（较长的二进制数据）
日期型

数值型

#1、整型
/*
分类：
tinyint、smallint、mediumint、int/interger、bigint
   1         2          3           4          8
 特点：
 ①如果不设置有符号还是无符号，默认是有符号，若想设置无符号，要加unsigned
 ②如果插入的数值超出整型范围，会报out of range异常，并插入临界值
 ③如果不设置长度，会有默认长度
*/

#如何设置有符号和无符号
CREATE TABLE tab_int(
	t1 INT,  #有符号
	t2 INT UNSIGNED  #无符号
);

# 长度
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,  #有符号
	t2 INT UNSIGNED  #无符号
);

INSERT INTO tab_int VALUES(123,123);
#第一个123会在前面填充四个0，并且加上填充是默认无符号

#2、小数
/*
分类：
①浮点型
float(M,D)
double(M,D)
②定点型
dec(M,D)
decimal(M,D)

特点：
①M：整数部位+小数部位 的长度
 D： 小数部位
 M和D都可以省略
 
 ②M和D都可以省略
 如果是decimal，默认M为10，D为0
 而float和double会根据插入的数值的精度来决定精度
 
 ③定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等，则考虑使用
*/
#测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DECIMAL(5,2)
);
SELECT * FROM tab_float;
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);#插入123.46
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);#报错

#原则：所选择的类型越简单越好，能保存数值的类型越小越好

字符型

/*
较短的文本：
char
varchar


较长的文本：
text
blob（较大的二进制）

特点:

	写法              M的意思	             特点	   空间的耗费    效率
char     char(M)	最大的字符数	  固定长度的字符    比较浪费	高
			   （char的M可以省略，默认为1）
varchar	  varchar(M)   最大的字符数       可变长度的字符     比较节省	      低
*/
#enum：要求插入的值必须属于列表中指定的值之一，不区分大小写
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');#插入失败
INSERT INTO tab_char VALUES('A');

SELECT * FROM tab_char;#表中只有四项，abca


#set：和enum类似，和enum最大的区别是，set类型一次可以选取多个成员，而enum只能选一个
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('a,b');
INSERT INTO tab_char VALUES('a,b,c');

日期型

/*
分类：
date只保存日期
time只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间

特点：
①datetime：字节8个，范围1000-9999年，不受时区影响
②timestamp：字节4个，范围1970-2038年，受时区影响
*/
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';

4.常见约束

#常见约束

CREATE TABLE 表名(
	字段名 字段类型 列级约束
	字段名 字段类型，
	表级约束
);

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

分类：六大约束
	not null:非空，用于保证该字段的值不能为空。比如姓名、学号等
	default：默认，用于保证该字段有默认值。比如性别
	primary key：主键，用于保证该字段的值有唯一性，并且非空。比如学号
	unique：唯一，保证该字段的值有唯一性，可以为空。比如座位号
	check：检查约束【mysql不支持】，比如年龄性别
	foreign key：外键，用于限制两个表的关系，保证该字段值必须来自于主表关联列的值
		    在从表添加外键约束，用于引用主表中某列的值
		    比如：员工表的部门编号、工种号

添加约束的时间：
	1、创建表时
	2、修改表时
约束的添加分类：
	1、列级约束：六大约束语法上都支持，但外键约束没有效果
	2、表级约束：除了非空、默认约束，其他都支持

创建表时添加约束

#1、添加列级约束
/*
语法：直接在字段名和字段类型后面追加约束类型即可
只支持：默认、非空、主键、唯一

一个列可以添加多个列级约束
*/
CREATE DATABASE students;
USE students;

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,#非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查，不过mysql没用
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18, #默认约束
	majorId INT 
)
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
DESC stuinfo;
#查看表中所有的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo;



#2、添加表级约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)	
);
SHOW INDEX FROM stuinfo;

#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);


INSERT INTO major VALUES(1,'java');
INSERT INTO major VALUES(2,'h5');
INSERT INTO stuinfo VALUES(1,'john','男',NULL,19,1);
INSERT INTO stuinfo VALUES(2,'lily','女',NULL,19,2);#错误，重复了

主键和唯一的对比

• 1、主键和唯一都能保证唯一性。但主键不允许为空，唯一允许。
• 2、一个表中最多只能有一个主键，而唯一键可以有多个。如果写成primary key(id,stuname)是联合主键的效果，要两个同时重复才会报错。
• 3、主键和唯一都允许组合使用，但不推荐。

外键

• 1、要求从表设置成外键关系
• 2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
• 3、主表的关联列必须是一个key（一般是主键、唯一）
• 4、插入数据时，必须先插入主表，再插入从表；删除时先删除从表，再删除主表（例子中，主表是major）

列级约束和表级约束

• 1、列级约束：位置—列的后面。支持的约束类型：语法都支持，但外键没有效果
• 2、表级约束：位置—所有列的下面。支持的约束类型：默认和为空除外
• 3、是否可以起约束名：列级约束不可以，表级约束可以（但是对主键起名无效）

修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名）;
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);
DESC stuinfo;
#1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

#2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3、添加主键（记一种就行）
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#列级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);#表级约束

#4、添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

#5、添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

修改表时删除约束

#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3、删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4、删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5、删除外键
ALTER TABLE stuinfo DROP FOREIGN  KEY fk_stuinfo_major;

5.标识列

/*
又称为自增长列
含义：可以不用手动插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key（主键外键唯一）
2、一个表中只可以有至多一个标识列
3、标识列的类型只能是数值型
4、标识列可以通过SET auto_increment_increment设置步长，也可以通过手动插入值设置起始值
*/

#1、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,#自动增长
	NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity;

INSERT INTO tab_identity VALUES(NULL,'john');#执行几次就会插入几次，会自动编号1,2,3...
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;#把自增长的步长改为3


#2、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;


#3、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

六TCL事务控制语言

transaction control language
事务：一个或一个SQL语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行。如果单元中某条sql语句一旦执行失败或产生错误，整个单元将会回滚。
（回滚：把操作撤销，回到最初状态）

存储引擎：在mysql中的数据用各种不同的技术存储在文件（或内存）中，通过show engines查看。（如，InnoDB，MyISAM，MEMORY）不是所有的存储引擎都支持事务，其中Innodb支持事务，MyISAM,MEMORY不支持。

/*
事务的创建

1、隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id=1；

2、显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
SHOW VARIABLES LIKE '%autocommit%';
set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;#可选的

步骤2：编写事务中的sql语句(select,insert,update,delete增删改查，不包括create，drop这些)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务
save point 节点名;#设置保存点

*/
CREATE DATABASE test;

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);

#演示事务的使用步骤
SET autocommit=0;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';

#结束事务
#commit;
ROLLBACK;#把500,1500改成1000,1000，执行回滚，发现还是500,1500

SELECT * FROM account;

#演示savepoint的使用
SET autocommit=0;
START TRANSACTION;

DELETE FROM account WHERE id=1;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=2;

ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;#1号删了，2号没删

1.事务的ACID属性

• 原子性(A)
  事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性©
  事务必须使数据库从一个一致性状态变换到另一个一致性状态。事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
• 隔离性(I)
  一个事务的执行不能被其他事物干扰，即一个事务内部的操作以及使用的数据对并发的其他事物是隔离的，并发执行的各个事务之间不能互相干扰，多个并发事务之间要相互隔离，同一时间，只允许一个事务请求同一数据。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
• 持久性(D)
  一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

2.事务的并发问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取有必要的隔离机制，会导致各种并发问题。

• 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的。
• 幻读：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行（和脏读有点相似，但脏读一般是针对更新，幻读针对插入、删除）
• 不可重复读：对于两个事物T1，T2，T1读取了一个字段，然后T2更新了该字段，之后，t1再次读取同一个字段，值就不同了

3. 四种事务隔离级别

通过设置事务的隔离级别来避免这些现象

• READ UNCOMMITTED（读未提交数据）：允许事务读取未被其他事物提交的变更，脏读、不可重复读和幻读的问题都会出现
• READ COMMITED（读已提交数据）:只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可避免不可重复读和幻读。
• REPEATABLE READ（可重复读）：确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事物对这个字段进行更新。可以避免脏读和不可重复读，但存在幻读。
• SERIALIZABLE（串行化）：确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表进行插入、更新和删除，所有并发问题都可以避免，但性能低下。

mysql中默认第三个隔离级别 repeatable read；
oracle中默认第二个隔离级别 read committed；

#查看当前隔离级别
SELECT @@transaction_isolation;

#设置当前连接的隔离级别
set transaction isolation level read committed;

#设置数据库系统全局的隔离级别
set global transaction isolation level read committed;

4.delete和truncate在事务使用时的区别

#演示delete

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;#回滚成功

#演示truncate

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;#没有回滚，实际删除了