03 数据增删改

MySQL中的标识符命名规则

数据库名，表明不得超过30个字符，变量名限制为29个
必须只能包含A-Z，a-z，0-9，_ 共63个字符
数据库名，表名，字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名，表明不能重复，同一个表中列名不能重复
必须保证字段名不和保留关键字冲突
保持字段名和类型的一致性

MySQL中的数据类型

类型	类型举例
整数类型	INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT
浮点累心	FLOAT, DOUBLE
定点数类型	DECLMAL
位类型	BIT
日期时间类型	YEAR, TIME, DATE, DATETIME, TIMESTAMP
文本字符串类型	CHAR, VARCHAR, TINYEXT, TEXT, MEDIUMTEXT, LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
JSON类型	JSON对象，JSON数组
空间数据类型	单值：GEOMETRY, POINT, LINESTRING, POLYGON 集合：MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

常用类型介绍

数据类型	描述
INT	从-2^31到2^31-1的整数数据。存储大小为4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，m=整数位+小数位，d=小数位。d<=m<=255,0<=d<=30，默认m+d<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECLMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式’YYYY-MM-DD
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

数据类型属性

关键字	含义
NULL	数据列可包含null值
NOT NULL	数据列不允许包含null值
DEFFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

数据库操作

创建数据库

新创建一个 mysqltest2 数据库，使用 IF NOT EXISTS 如果数据库不存在就创建，如果存在则不会创建

1	CREATE DATABASE IF NOT EXISTS mysqltest2;

查询已有数据库

1	SHOW DATABASES;

查询当前正在使用的数据库

1	SELECT DATABASE();

查询指定库下面所有的表

1	SHOW TABLES FROM atguigudb;

查看数据库的创建信息

1	SHOW CREATE DATABASE mysqltest1;

修改数据库的字符集

1	ALTER DATABASE mysqltest1 CHARACTER SET utf8;

删除指定数据库

1	DROP DATABASE IF EXISTS mysqltest2;

创建表的两种方式

方式一

CREATE TABLE IF NOT EXISTS myemp1(
	id INT,
	last_name VARCHAR(15),
	hire_date DATE	
);

方式二，基于已有表

基于现有表创建新的表,查询出来的结果会保存到新表中

CREATE TABLE IF NOT EXISTS myemp2 
AS
SELECT employee_id,last_name,salary
FROM employees;

查看表结构

1	DESC myemp2

查看建表语句

1	SHOW CREATE TABLE myemp2;

练习一：复制 employees 表的结构和数据

1
2
3

CREATE TABLE IF NOT EXISTS myemp3
AS
SELECT * FROM employees;

练习二：只复制 employees 表的结构

1
2
3

CREATE TABLE IF NOT EXISTS myemp4
AS
SELECT * FROM employees WHERE NULL;

修改表

追加一个列

语法：ALTER TABLE 表名 ADD 列名数据类型;

1	ALTER TABLE myemp1 ADD salary DOUBLE(10,2);

修改一个列

可以修改列的数据类型，长度，默认值和位置

语法：ALTER TABLE 表名 MODIFY 列名数据类型 DEFAULT 默认值;

1	ALTER TABLE myemp1 MODIFY last_name VARCHAR(25) DEFAULT '张三';

重命名一个列

语法：ALTER TABLE 表名 CHANGE 旧列名新列名数据类型;

1	ALTER TABLE myemp1 CHANGE `name` user_name VARCHAR(30);

删除一个列

语法：ALTER TABLE 表名 DROP 列名;

1	ALTER TABLE myemp1 DROP salary;

重命名表

语法：RENAME TABLE 旧表名 TO 新表名;

1	RENAME TABLE myemp1 TO newmyemp1;

删除表

语法：DROP TABLE IF EXISTS 表名;

1	DROP TABLE IF EXISTS myemp2;

清空表

保留表中字段，只清空数据

语法：TRUNCATE TABLE 表名;

1
2
3

TRUNCATE TABLE myemp3;
-- 或者
DELETE FROM myemp3;

TRUNCATE 和 DELETE对比

DELETE 可以回滚数据，但是 TRUNCATE 不能回滚
TRUNCATE 效率高，DELETE 效率低

DDL 和 DML 的说明

DDL的操作一旦执行，就不可回滚。指令 SET autocommit = FALSE 对 DDL 操作失效，因为在执行完 DDL 操作之后，一定会执行一次 COMMIT。二次 COMMIT 操作不收 SET autocommit = FALSE 影响
DML的操作默认情况，一旦执行，也不可以回滚，但是如果在执行DML操作之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚
数据的回滚只能回滚到最近一次commit 时的结果

演示，执行 DELETE FROM 后并对数据进行回滚

-- 1.执行前先执行一次commit
COMMIT;
-- 2.查询表中数据 107
SELECT * FROM myemp1;
-- 3.设置自动提交为false
SET autocommit = FALSE;
-- 4.清空表中数据
DELETE FROM myemp1;
-- 5.查询表中数据 0
SELECT * FROM myemp1;
-- 6.使用 ROLLBACK 进行数据回滚
ROLLBACK;
-- 7.再次查询数据恢复
SELECT * FROM myemp1;

执行 TRUNCATE 清空表

-- 1.执行前先执行一次commit
COMMIT;
-- 2.查询表中数据 107
SELECT * FROM myemp1;
-- 3.设置自动提交为false
SET autocommit = FALSE;
-- 4.清空表中数据
TRUNCATE TABLE myemp1;
-- 5.查询表中数据 0
SELECT * FROM myemp1;
-- 6.使用 ROLLBACK 进行数据回滚
ROLLBACK;
-- 7.再次查询数据,数据回滚失败
SELECT * FROM myemp1;

MySQL8.0 DDL的原子化

如果一个删除语句执行失败，则会回滚数据

示例

CREATE TABLE bool1 (
	id INT,
	book_name VARCHAR(30)
);

SHOW TABLES;

DROP TABLE book1,book2;

上面的代码依次执行后 book1 表不会被删除。

添加数据

首先新建表

USE atguigudb;

CREATE TABLE IF NOT EXISTS emp01(
id INT,
last_name VARCHAR(15),
salary DOUBLE(10,2),
hire_date DATE
);

单行逐条插入

默认插入的顺序要和建表时的字段顺序一致

1 2	INSERT INTO emp01 VALUES (001,'Tome',4500,'2022-2-24 11:11:22');

指明字段顺序

在表名后面指明要插入的字段顺序，在添加数据时也要和指明的顺序一致

1 2	INSERT INTO emp01(id,hire_date,last_name,salary) VALUES (002,'2022-2-24','Joary',6000);

批量插入数据

INSERT INTO emp01(id,hire_date,last_name,salary)
VALUES
(003,'2000-2-24','Koing',12000),
(004,'2002-6-18','Bokau',1356);

将查询结果插入到表中

我们也可以将一个查询语句查询出来的结果全部插入到表中，但是查询的字段显示要和插入表的顺序一致。

INSERT INTO emp01 ( id, hire_date, last_name, salary ) 
SELECT
	employee_id,
	hire_date,
	last_name,
	salary 
FROM
	employees 
WHERE
	department_id IN ( 70, 80 );

更新数据

修改 id 等于 1 的员工工资为6000

1	UPDATE emp01 SET salary=6000 WHERE id = 1;

批量修改，名字中包含a的人员工资提升百分之20

1	UPDATE emp01 SET salary = salary * 1.2 WHERE last_name LIKE '%a%';

删除数据

删除 id 等于 2 的数据

1	DELETE FROM emp01 WHERE id = 2;

批量删除数据

1	DELETE FROM emp01 WHERE id < 100;

删除操作如果没有加 WHERE 条件限制则会删除表中的全部内容

MySQL8 新特性：计算列

当某一列的数据是依赖于其他列数据计算而得时，我们可以吧这个列设置为计算列。在添加数据时不需要设置计算列的值，计算列会自动计算并赋值

创建表, c 是计算列

CREATE TABLE testsql(
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a+b) VIRTUAL
);

添加数据

1	INSERT INTO testsql(a,b) VALUES(10,20);

修改数据

1	UPDATE testsql SET a = 120 WHERE a = '10'

上面两个操作，c 列数据都会自动发生改变。

数据增删改练习

-- 练习一：

#1. 创建数据库dbtest11 
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

#2. 运行以下脚本创建表my_employees
USE dbtest11; 
CREATE TABLE my_employees (
	id INT ( 10 ),
	first_name VARCHAR ( 10 ),
	last_name VARCHAR ( 10 ),
	userid VARCHAR ( 10 ),
	salary DOUBLE ( 10, 2 ) 
);
CREATE TABLE users ( 
	id INT, 
	userid VARCHAR ( 10 ), 
	department_id INT 
);
SHOW TABLES;

#3. 显示表my_employees的结构
DESC my_employees;

#4. 向my_employees表中插入下列数据
INSERT INTO my_employees
VALUES 
(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

SELECT * FROM my_employees;

#5. 向users表中插入数据
INSERT INTO users
VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);

SELECT * FROM users;

#6. 将3号员工的last_name修改为“drelxer” 
UPDATE my_employees SET last_name='drelxer' WHERE id = 3;

#7. 将所有工资少于900的员工的工资修改为1000 
UPDATE my_employees SET salary = 1000 WHERE salary < 900;

SELECT * FROM my_employees;

#8. 将userid为Bbiri的user表和my_employees表的记录全部删除 
DELETE FROM my_employees WHERE userid = 'Bbiri';
DELETE FROM users WHERE userid = 'Bbiri';

#9. 删除my_employees、users表所有数据 
DELETE FROM my_employees;
DELETE FROM users;

#10. 检查所作的修正
SELECT * FROM users;
SELECT * FROM my_employees;

#11. 清空表my_employees
TRUNCATE my_employees;


-- 练习二：

# 1. 使用现有数据库dbtest11 
USE dbtest11;

# 2. 创建表格pet
CREATE TABLE pet(
	name VARCHAR(20),
	owner VARCHAR(20),
	species VARCHAR(20),
	sex CHAR(1),
	birth YEAR,
	death YEAR
);

SHOW TABLES;

SELECT * FROM pet;

# 3. 添加记录
INSERT INTO pet
VALUES
('Fluffy','harold','Cat','f',2003,2010),
('Claws','gwen','Cat','m',2004,NULL),
('Buffy',NULL,'Dog','f',2009,NULL),
('Fang','benny','Dog','m',2000,NULL),
('bowser','diane','Dog','m',2003,2009),
('Chirpy',NULL,'Bird','f',2008,NULL);

# 4. 添加字段:主人的生日owner_birth DATE类型。 
ALTER TABLE pet ADD owner_birth DATE;

# 5. 将名称为Claws的猫的主人改为kevin 
UPDATE pet SET owner = 'kevin' WHERE `name` = 'Claws';

# 6. 将没有死的狗的主人改为duck 
UPDATE pet 
SET OWNER = 'duck' 
WHERE
	species = 'Dog' 
	AND death IS NULL;

# 7. 查询没有主人的宠物的名字； 
SELECT `name`
FROM pet
WHERE owner IS NULL;

# 8. 查询已经死了的cat的姓名，主人，以及去世时间； 
SELECT name,owner,death
FROM pet
WHERE species = 'Cat' AND death IS NOT NULL;

# 9. 删除已经死亡的狗 
DELETE 
FROM
	pet 
WHERE
	species = 'Dog' 
	AND death IS NOT NULL;

# 10. 查询所有宠物信息
SELECT * FROM pet;

-- 练习三：

# 1. 使用已有的数据库dbtest11 
USE dbtest11;

# 2. 创建表employee，并添加记录
CREATE TABLE employee(
	id INT,
	name VARCHAR(25),
	sex CHAR(1),
	tel VARCHAR(18),
	addr VARCHAR(50),
	salary DOUBLE(8,2)
);

SELECT * FROM employee;

INSERT INTO employee
VALUES
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','女','020-1232133','广东广州',1405.16);

# 3. 查询出薪资在1200~1300之间的员工信息。
SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。 
SELECT id,name,addr
FROM employee
WHERE `name` LIKE '%刘%';

# 5. 将“李四”的家庭住址改为“广东韶关” 
UPDATE employee SET addr = '广东韶关' WHERE name = '李四';

# 6. 查询出名字中带“小”的员工
SELECT *
FROM employee
WHERE name LIKE '%小%';

数据类型

整数类型

UNSIGNED 无符号限制

创建表时如果添加了这个属性，则表示这个字段不能出现符号。例如当一个字段是 int 类型时，并且声明了 UNSIGNED 属性，则这个字段必须存储一个大于等于0的整数

CREATE TABLE test1(
	f1 INT,
	f2 INT UNSIGNED
);

-- 这个添加语句会执行失败，因为 f2 声明为 UNSIGNED 无符号的，只能添加大于等于0的整数
INSERT INTO test1
VALUES (123456,-123456);

ZEROFILL

这个属性只有指明了字段长度然后再添加这个属性才有意义。例如设置 int(5) ，当添加的整数不足5位时，会在前面用 0 补齐五位

CREATE TABLE test1(
	f1 INT,
	f2 INT UNSIGNED, 
	f3 INT(5) ZEROFILL 
);

INSERT INTO test1
VALUES
(123,123,123);

SELECT * FROM test1;

浮点类型

FLOAT

FLOAT 表示单精度浮点数

CREATE TABLE test2(
f1 FLOAT,
f2 FLOAT(5,2)
);

DOUBLE

DOUBLE 表示双精度浮点数

CREATE TABLE test3(
f1 DOUBLE,
f2 DOUBLE(5,2)
);

INSERT INTO test3
VALUES
(12.567,12.567);

SELECT * FROM test3

当设置小数点位数后，如果小数点的个数超过设定的数量，则小数点自动四舍五入

问题1：FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？
- FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。
问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？
- MySQL 存储浮点数的格式为：符号(S) 、尾数(M) 和阶码(E) 。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此，所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

定点数类型

上面的两个浮点类型都会有精度丢失问题，例如如下：

CREATE TABLE test4(
f1 FLOAT,
f2 FLOAT
);

INSERT INTO test4
VALUES
(1.3,1.6);

SELECT * FROM test4;

SELECT f1+f2 FROM test4;

我们往两个浮点类型的列中分别插入了 1.3 和 1.6，然后计算两个列的和，我们希望得到的是2.9，但是结果返回是无限接近2.9的一个数，这种我们称之为精度丢失问题。面对这种情况我们如何解决呢？那么就是采用定点数类型：DECIMAL

DECIMAL

数据类型	默认值	字节数	含义
DECIMAL(M,D)	DECIMAL(10,0)	M + 2	有效范围由M和D决定

DECIMAL(M,D) 表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

现在我们使用 DECIMAL 类型来解决上面的问题

CREATE TABLE test5(
f1 DECIMAL(5,2),
f2 DECIMAL(5,2)
);

INSERT INTO test5
VALUES
(1.3,1.6);

SELECT * FROM test5;

SELECT f1+f2 FROM test5;

由于默认 DECIMAL(10,0) 表示0位小数点，所以动态设置2位小数点，现在查看效果

可见精度没有丢失。因为 DECIMAL 底层采用的是字符串存储的，不会有精度丢失问题

日期和时间类型

DATE

存储年月日

TIME

存储时分秒

DATETIME

存储年月日时分秒

CREATE TABLE test6(
f1 DATE,
f2 TIME,
f3 DATETIME
);

SELECT * FROM test6;

INSERT INTO test6
VALUES(NOW(),NOW(),NOW());

分别设置列的类型为 DATE, TIME, DATETIME，然后插入当前时分秒，查询插入结果：

字符串格式类型

CHAR 和 VARCHAR

VARCHAR 必须指定长度，指定的是字符长度，表示字符串的长度不能超过几个

CREATE TABLE test7(
	f1 CHAR,
	f2 VARCHAR(5)	
);

INSERT INTO test7
VALUES
('男','12345');

SELECT * FROM test7;

ENUM 枚举类型

设置一个字段为枚举类型后只能插入枚举的值，同时一次只能插入一个数据

CREATE TABLE test8(
	f1 ENUM('春','夏','秋','冬')	
);

SELECT * FROM test8;

-- Data truncated for column 'f1' at row 1
INSERT INTO test8 
VALUES ('立春');

-- 也可以插入下标
INSERT INTO test8
VALUES 
('春'),
('2'),
(1);

SET 类型

设置 SET 类型后可以同时插入多条数据，如果有重复的数据会自动去重

CREATE TABLE test9(
	f1 SET('A','B','C')
);

INSERT INTO test9
VALUES ('A');

INSERT INTO test9
VALUES ('A,B');

INSERT INTO test9
VALUES ('A,B,A');

SELECT * FROM test9;

JSON

CREATE TABLE test10(
	f1 json
);

INSERT INTO test10
VALUES
('{"name":"张三","age":18,"salary":6000}');

SELECT * FROM test10;

--- MySQL读取json数据
SELECT
	f1 -> '$.name' AS NAME,
	f1 -> '$.age' AS age 
FROM
	test10;