MySQL

Posted on Edited on

DAY1

使用指令创建数据库：CREATE DATABASE zcl_db01;

删除数据库指令：DROP DATABASE zcl_db01;

创建一个使用utf8字符集的zcl_db02数据库：CREATE DATABASE zcl_db02 CHARACTER SET utf8

创建一个使用utf8字符集，并带校对规则的zcl_db03数据库：CREATE DATABASE zcl_db03 CHARACTER SET utf8 COLLATE utf8_bin utf8_bin区分大小写，utf8_general_ci不区分大小写

1
2
3
4
5
6
7
8
#查看当前数据库服务器中的所有数据库
SHOW DATABASES

#查看前面创建的zcl_db01数据库的定义信息
SHOW CREATE DATABASE `zcl_db01`

#在创建数据库、表的时候，为了规避关键字，可以使用``解决
CREATE DATABASE `CREATE`
1
2
3
4
5
6
7
8
#备份数据库
mysqldump -u root -p -B zcl_db03 > e:\\tmp.sql (文件名)

#恢复数据库
source e:\\tmp.sql

#备份数据库的表
mysqldump -u root -p zcl_db03 t1 t2 (t1和t2是表名) > e:\\tmp1.sql

DAY2

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 创建表
# create table table_name
# (
# 	filed1 datatype,
#	filed2 datatype,
#	filed3 datatype
# )character set 字符集 collate 校对规则 engine 存储引擎
#  filed：指定列名	   datatype：指定列类型（字段类型）
#  character set：如不指定则为所在数据库字符集
#  collate：如不指定则为所在数据库校对规则

# 实操
create table `user`(
	id int,
    `name` varchar(255),
    `passward` varchar(255),
    `birthday` date
)character set utf8 collate utf8_general_ci engine innodb;

Mysql常用数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
# Mysql列类型就是Mysql的数据类型
# 数值类型：
# 整型：tinyint(1个字节)	smallint(2个字节)	mediumint(3个字节)	int(4个字节)	bigint(8个字节)
# 小数类型：float(单精度 4个字节)	double(双精度 8个字节)	decimal[M,D](大小不确定，M代表长度，D代表小数位数)

# 文本类型（字符串类型）
# char(0-255)	varchar(0~65535)	text(0~2^16-1)	longtext(0~2^32-1)

# 二进制数据类型
# blob[0~2^16-1]	longblob[0~2^32-1]

# 日期类型
# date[日期 年月日] time[时间 时分秒]	datetime[年月日 时分秒 YYYY-MM-DD HH:MM:SS]	timestamp[时间戳]

在满足需求的情况下，尽量选择占用空间小的类型

1
2
3
4
5
6
7
8
9
CREATE TABLE t3(
	id TINYINT
);
# INSERT INTO t3 VALUES(128); 错误：越界了，超过了tinyint范围。 
INSERT INTO t3 VALUES(127);
# 定义无符号的整数 指定unsigned
CREATE TABLE t4(
	id TINYINT UNSIGNED
);
1
2
3
4
5
6
7
8
# BIT(M), M在1-64。显示按照bit。
CREATE TABLE t5(
	num BIT(8)
);
INSERT INTO t5 VALUES(255);
SELECT * FROM t5;
# 输出：11111111
SELECT * FROM t5 WHERE num = 255;
1
2
3
4
5
6
7
8
9
# DECIMAL[M, D] 
# M是小数位数（精度）的总数，D是小数点（标度）后面的位数。
# 如果D是0，则值没有小数点或分数部分，M最大是65，D最大是30。如果D被省略，则默认是0，如果M被省略，默认是10。
CREATE TABLE t6(
	num1 FLOAT,
    num2 DOUBLE,
    num3 DECIMAL(30, 20)
);
INSERT INTO t6 VALUES(88.123456789123456, 88.123456789123456, 88.123456789123456)
1
2
3
4
5
6
7
8
9
# char(4) 这个4表示字符数（最大255），不是字节数，不管是中文还是英文都是放四个，按字符计算，定长，即使你插入'aa'，也会占用分配的4个字符的空间
# varchar(4) 这个4表示字符数，是变长，如果你插入'aa'，实际占用空间并不是4个字符，而是实际占用的空间。
# utf-8：一个字符占三个字节
CREATE TABLE t11(
	'name' CHAR(4)
);
# INSERT INTO t11 VALUES('ABCDE') 报错，太长
INSERT INTO t11 VALUES('ABCD')
INSERT INTO t11 VALUES('你好')
1
2
3
4
5
6
7
# 日期类型 date datetime timestamp
CREATE TABLE birthday11(
	birthday DATE,
    worktime DATETIME,
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO bithday11(birthday, worktime) VALUES('2022-11-11', '2022-11-11 10:10:10');

创建表的课堂练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE 'emp'(
	id INT,
    'name' VARCHAR(32),
    sex CHAR(1),
    birthday DATE,
    entry_date DATETIME,
    job VARCHAR(32),
    saraly DOUBLE,
    'resume' TEXT
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO 'emp' VALUES(
	100, '小妖怪', '男', '2000-11-11', '巡山的', 3000, '大王叫我来巡山'
);
SELECT * FROM 'emp';

# 增加列
ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME

# 显示表结构
DESC emp

# 修改job长度
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT ''

# 删除列
ALTER TABLE emp DROP sex

# 修改表名
RENAME TABLE emp TO employee

# 修改表的字符集
ALTER TABLE employee CHARACTER SET utf8

# 修改列名
ALTER TABLE employee CHANGE 'name' user_name VARCHAR(64) NOT NULL DEFAULT ''

数据库CRUD语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 使用INSERT语句向表中插入数据
CREATE TABLE 'goods'(
	id INT,
    goods_name VARCHAR(10),
    price DOUBLE
);
INSERT INTO 'goods' (id, goods_name, price) VALUES (10, '华为手机', 2000);
INSERT INTO 'goods' (id, goods_name, price) VALUES (20, '苹果手机', 20000);

# INSERT语句的细节
-- 插入的数据应与字段的数据类型相同，比如把'abc'添加到int类型会错误。
-- 数据的长度应在列的规定范围内，不能将一个长度为80的字符串加入到长度为40的列中。
-- 在VALUES中列出的数据位置必须与被加入的列的排列位置相对应。
-- 字符和日期类型应包含在单引号中
-- 列可以插入空值，前提是该字段允许为空（没有NOT NULL）
-- INSERT INTO TABLE_NAME (列名) VALUES (),(),()，添加多条数据一次性
-- 如果是给表中的所有字段添加数据，(id, goods_name, price)这个可以省略
-- 默认值的使用，当不给某个字段赋值时，如果有默认值就会添加，否则报错。
-- 如果我们希望指定某个列的默认值，可以在创建表时指定。
CREATE TABLE 'goods2'(
	id INT,
    goods_name VARCHAR(10),
    price DOUBLE NOT NULL DEFAULT 100
);
1
2
3
4
5
6
7
8
9
10
-- 演示update语句
-- employee是表名，salary是列名
-- 将所有员工薪水修改为5000，如果没有带where条件，会修改所有的记录，因此要小心
UPDATE employee SET salary = 5000 
-- 将姓名为小妖怪的员工薪水修改为3000
UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪'
-- 将老妖怪的薪水在原有基础上增加1000
INSERT INTO employee VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000,  '给大王捶背的');
UPDATE employee SET salary = salary + 1000 WHERE user_name = '老妖怪'
UPDATE employee SET salary = salary + 1000, job = '出主意的' WHERE user_name = '老妖怪'
1
2
3
4
5
6
7
8
9
-- 演示delete语句
-- 删除表中名称为‘老妖怪’的记录
DELETE FROM employee WHERE user_name = '老妖怪';
-- 删除表中所有记录
DELETE FROM employee
-- DELETE删除不了一列的值，但可以使用UPDATE语句置为null
UPDATE employee SET job = NULL WHERE user_name = '老妖怪';
-- 要删除这个表
DROP TABLE employee
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- select指定查询哪些列的数据
-- *代表查询所有列
-- from指定查询哪张表
-- DISTINCT可选，指显示结果时，是否去掉重复数据
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
    NAME VARCHAR(20) NOT NULL DEFAULT '',
    chinese FLOAT NOT NULL DEFAULT 0.0,
    english FLOAT NOT NULL DEFAULT 0.0,
    math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id, NAME, chinese, english, math) VALUES(1, '韩顺平', 89, 78, 90);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(2, '张飞', 67, 98, 56);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(3, '宋江', 87, 78, 77);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(4, '关羽', 88, 98, 90);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(5, '赵云', 82, 84, 67);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(6, '欧阳锋', 55, 85, 45);
INSERT INTO student(id, NAME, chinese, english, math) VALUES(7, '黄蓉', 75, 65, 30);

SELECT * FROM student;

-- 查寻表中所有学生的信息
SELECT * FROM student;

-- 查询表中所有学生的姓名和对应的英语成绩
SELECT 'NAME', english FROM student;

-- 过滤表中重复数据distinct
-- 要查询的记录每个字段都相同，才会去重
SELECT DISTINCT english FROM student;

-- 统计每个学生的总分
SELECT 'NAME', (chinese + english + math) FROM student;

-- 在所有学生总分加10分
SELECT 'NAME', (chinese + english + math + 10) FROM student;

-- 使用别名表示学生分数
SELECT 'NAME', (chinese + english + math + 10) AS total_score FROM student;
SELECT 'NAME' AS '名字', (chinese + english + math + 10) AS total_score FROM student;

-- 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE 'NAME' = '赵云';

-- 查询英语成绩大于90分的同学
SELECT * FROM student WHERE english > 90;

-- 查询总分大于200分的所有同学
SELECT * FROM student WHERE (chinese + english + math) > 200;

-- 查询math大于60并且id大于4的学生成绩
SELECT * FROM student WHERE math > 60 AND id > 4;

-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english > chinese;

-- 查询总分大于200分并且数学成绩小于语文成绩的姓韩的学生
-- 韩% 表示名字以韩开头的就可以
SELECT * FROM student WHERE (chinese + english + math) > 200 AND 
						  math < chinese AND 'NAME' LIKE '韩%';
						  
-- 查询英语分数在80-90之间的同学
SELECT * FROM student WHERE english >= 80 AND english <= 90;
SELECT * FROM student WHERE english BETWWWN 80 AND 90; -- BETWEEN AND 是闭区间

-- 查询数学分数为89，90，91的同学
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student WHERE math IN (89, 90, 91);