42 KiB

Raw Permalink Blame History

小孩子4919

第一章 MYSQL的安装启动和关闭

windows上使用命令操作mysql

# MySQL80是服务名
net start MySQL80
net stop MySQL80

第二章 MYSQL初体验

2.1 客户端/服务器

SQL(Structured Query Language): 结构化查询语言

2.2 bin目录下的可执行文件

采用命令行启动mysqld服务

mysqld 
# 指定数据库数据存储目录
mysqld --datadir="C:/ProgramData/MySQL/Data"
# 查看运行日志
mysqld --console
# 如果出现无法写入  可能需要使用管理员权限，因为数据放在了c盘

mysql客户端

# -p何密码之间不能有空格
# 在unix系统上，不适用u参数，会默认使用用户操作系统的账户进入mysql
mysql -hlocalhost -uroot -p123456
mysql -h localhost -u root -p123456
mysql --host=localhost --user=root --password=123456
mysql -uroot -p123456 database_name
# 带上数据库名称进去默认是这个数据库

# 退出
quit
exit
\q

2.3 MYSQL语句注意事项

语句结束符

;
\g
\G
# \G会让一行中的每一列单行显示，对于列数多的情况下，看起来比较清晰

语句可以换行
可以提交多个语句
使用\c放弃操作
没有大小写限制
一般用单引号表示字符串（双引号也可以但是，在ANSI_QUOTES模式中双引号有其他作用）

第三章 MYSQL数据类型

3.1 数值类型

TINYINT 2^8 1byte
SMALLINT 2^16 2
MEDIUMINT 2^24 3
INT 2^32 4
BIGINT 2^64 8
FLOAT 2^32 4
DOUBLE 2^64 8
定点类型，将小数从小数点分开存储DECIMAL(M, D)MD分别表示小数左右数字十进制位数

3.2 日期时间类型

YEAR 1byte 1901-2155
DATE 3Byte 1000-01-01~9999-12-31
TIME 3Byte -838:59:59~838:59:59
DATETIME 8Byte 1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP 4Byte 1970-01-01 00:00:01~2038-01-19 03:14:07
DATETIME 5+sByte 1000-01-01 00:00:00[.000000]~9999-12-31 23:59:59[.000000] mysql5.6.4后增加后三个对微秒支持，可拓展
TIMESTAMP 可根据时区变化，而且截止到2038 所以以后不用他

3.3 字符串类型

定长编码格式 ASCII 1Byte，UCS2 2Byte
变长编码格式 UTF-8 1-4Byte，GB2312 1-2Byte
mysql的UTF-8是对多3byte，utf8mb4是1-4Byte

类型	描述
char(M)	0-255的长度，定长，不够的字符用空格填充
varchar(M)	变长，0-255：字符长度L * 编码的所占字节数W + 1Byte(这里用来写位置吧) 大于255位置大小变成2Byte，一般最大容量（不是长度）为65535，但是mysql规定单行数据不能超过65535Byte，所以肯定是小于65535Byte的，utf8mb4一个字符4Byte，所以。。。65535/4
more TEXT	此长度不受mysql单行最大长度限制
TINYTEXT	2^8-1Byte + 1Byte地址
TEXT	2^16 + 2Byte地址
MEDIUMTEXT	2^24 + 3Byte地址
LONGTEXT	2^32 + 4Byte地址
ENUM & SET	ENUM('str1', 'star2', 'str3') SET('str1', 'str2', 'str3')

3.4 二进制类型

BIT存2进制位，最大64
BINARY和VARBINARY区别前者定长后者变长参考varchar
BLOB有四种：TINY*，，MEDIUM， LONG*，一般不用数据库存文件，存文件系统的路径

第四章数据库的基本操作

4.1 查看数据库

SHOW DATABASES;

4.2 创建数据库

CREATE DATABASE database_name; -- 如果表存在会报错
/* 如果不存在再创建 */
CREATE DATABASE IF NOT EXISTS database_name;

4.3 切换默认数据库

USE database_name;
--     有些账户没有这个数据库的权限 切换会报错

4.4 删库

DROP DATABASE database_name;
--      如果数据库不存在会报错
DROP DATABASE IF EXISTS database_name;

第五章表操作

5.1 查数据库的表

SHOW TABLES FROM database_name;
-- 如果有默认的数据库 可以直接查看
SHOW TABLES;

5.2 创建表

CREATE TABLE IF NOT EXISTS table_name (
    列明1 数据类型 [列属性],
    列明2 数据类型 [列属性],
) COMMENT '这里是表的注释';

5.3 删除表

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

5.4 查看表结构

DESCRIBE table_name;
DESC table_name;
EXPLAIN table_name;
SHOW COLUMNS FROM table_name;
SHOW FIELDS FROM table_name;
-- 以上效果一样
--      查看建表语句
     SHOW CREATE TABLE table_name;

5.5 直接使用某个数据库中的某个表

SHOW CREATE TABLE database_name.tablename;

5.6 修改表

5.6.1 修改表名，移动表

-- 修改表明
ALTER TABLE old_table_name RENAME TO new_table_name;
RENAME TABLE database_name.old_table_name TO database_name.new_table_name;
RENAME TABLE table_a TO table_a_new, table_b TO table_b_new, ...;
RENAME TABLE old_table_name TO new_table_name;
-- 移动表
ALTER TABLE db_a.table_a RENAME TO db_b.table_a;
RENAME TABLE db_a.table_a TO db_b.table_a;

5.6.2 增加列

ALTER TABLE table_name ADD COLUMN 字段名 字段类型 [其他属性];
-- 增加到第一列
ALTER TABLE table_name ADD COLUMN 字段名 字段类型 [其他属性] FIRST;
-- 增加到指定列的后面
ALTER TABLE table_name ADD COLUMN 字段名 字段类型 [其他属性] AFTER 已存在的列名;

5.6.3 删除列

ALTER TABLE 表名 DROP 列名;

5.6.4 修改列信息

ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];
-- 修改列排序
ALTER TABLE 表名 MODIFY 列名 列类型 列属性 FIRST;
ALTER TABLE 表名 MODIFY 列名 列类型 列属性 AFTER 指定列名;

5.6.5 多个修改操作

ALERT TABLE 表名 操作1, 操作2, ...;

第六章列的属性

6.1 简单的查询和插入语句

6.1.1 简单的查询语句

SELECT * FROM table;

6.1.2 简单的插入语句

-- 如果没有指明某个字段的值，即为默认值
INSERT INTO 表名(列1, 列2, 列3) VALUES(列1值, 列2值, 列3值);

6.1.3 批量插入

INSERT INTO 表名(列1, 列2, 列3) VALUES(列1值, 列2值, 列3值), (列1值, 列2值, 列3值), (列1值, 列2值, 列3值);

6.2 列属性

6.2.1 默认值

CREATE TABLE table_name (
    first_column INT DEFAULT '0'
);

6.2.2 NOT NULL

列名列类型 NOT NULL

6.2.3 主键

-- 单字段主键
CREATE TABLE table_name (
    first_column INT PRIMARY KEY 
);

CREATE TABLE table_name (
    first_column INT PRIMARY KEY,
    PRIMARY KEY (first_column)
);

-- 多字段主键 只能这样设置
CREATE TABLE table_name (
    first_column INT,
    last_column INT,
    PRIMARY KEY (first_column, last_column)
);

6.2.4 UNIQUE约束

希望有其他列或列组合存储值唯一

-- 单个字段约束
CREATE TABLE table_name (
    first_column INT PRIMARY KEY,
    next_column INT UNIQUE ,
    last_column INT
);
-- 多个字段约束
CREATE TABLE table_name (
    first_column INT PRIMARY KEY,
    next_column INT,
    last_column INT,
    UNIQUE KEY 约束名 (next_column, last_column)
);
CREATE TABLE hutao.new_table (
     product_id INT PRIMARY KEY,
     product1 VARCHAR(50),
     product2 VARCHAR(20),
     product3 VARCHAR(50),
     product4 VARCHAR(50),
     product5 VARCHAR(50),
     product6 VARCHAR(50),
     UNIQUE KEY key1 (product1, product2),
     UNIQUE KEY key2 (product5, product6)
);

注意:

一张表只能有一个主键，但是可以有多个UNIQUE约束
主键不允许为NULL，但是UNIQUE可以存，并且允许存在多个，NULL代表没有填写
如果未定义主键，mysql会默认将第一个出现的NOT NULL UNIQUE设置为主键

6.2.3 外键

学生成绩表中携带了学生信息表中的学生id字段，如果学生成绩表中的学生id在学生信息表中不存在，那么此条学生成绩为无意义数据，为避免这种问题，设立外键。
以上学生信息表被称为主表，学生成绩表被称为子表，子表学生成绩表中的学生id为学生成绩表的外键，外键必须是主表的索引，主键和UNIQUE约束自动成索引

写法1(建议)：CONSTRAINT [外键名] FOREIGN KEY (字段1, 字段2, 字段3) REFERENCES 主表名(字段1, 字段2, 字段3)
写法2(简写)：FOREIGN KEY [外键名] (字段1, 字段2, 字段3) REFERENCES 主表名(字段1, 字段2, 字段3)

CREATE TABLE student_score (
    score_id INT PRIMARY KEY COMMENT '学生成绩表主键',
    student_id INT COMMENT '学生ID',
    score_number INT COMMENT '学生成绩',
    CONSTRAINT FOREIGN KEY (student_id) REFERENCES student_info(student_id)
)

6.2.4 自增 AUTO_INCREMENT

使用整型或浮点型
用法列名列类型 AUTO_INCREMENT
INSERT时将自增列设置为NULL时会自动插入自增值
NO_AUTO_VALUE_ZERO是默认不开启的，将0插入自增列也是自增值
如果INSERT显式的插入新的自增值，下一个默认自增值是在显式自增值的基础上增长的
一个表最多只能有一个自增列
自增列必须为索引，主键和UNIQUE默认为索引
自增列不能设置默认值DEFAULT
自增列一般作为主键

6.2.8 列注释

列名数据类型 COMMENT '注释'

6.2.9 显示宽度和ZEROFILL MYSQL8.0.17不推荐

ZEROFILL作用是无符号数字补零
没有显式说明UNSIGNED MYSQL会在建表的时候补上
不同的整数类型默认显示宽度不同
这不会影响存储

列名数据类型(显示宽度) UNSIGNED ZEROFILL

CREATE TABLE zerofille_table (
    id INT AUTO_INCREMENT ,
    number UNSIGNED INT(5) ZEROFILL
)

6.3 查看表结构时的列属性

表头	值	说明
Null	No/Yes	是否允许为Null
Key	PRE/UNI	标记键的信息
Default		默认值
Extra		额外信息，如自增列

6.4 标识符的命名

各种名字不能以空格结尾
可以使用``将带空格的或纯数字包起来，但是不建议使用这两个
不能用保留字如DESCRIBE
按照C/C++标准来，建议用下划线区分

第七章简单查询

7.1 准备

采用mysql是怎样使用的数据初始化7.0.sql导入数据

7.2 查询单个列

方法：SELECT 列名 FROM 表名

7.2.1 列的别名

方法：SELECT 列名 [AS] 列的别名 FROM 表名

SELECT number AS '学号' FROM student_info;
SELECT number '学号' FROM student_info;

7.3 查询多个列

方法：SELECT 列1,列2,列3,列4,列5 FROM 表名;

SELECT 
    number, name, id_number, major 
FROM 
    student_info;
SELECT 
    number '学号', name '姓名', id_number '身份证号', major '专业' 
FROM 
    student_info;

7.4 查询所有列

方法：SELECT * FROM student_info;

7.5 查询结果去重

方法：SELECT DISTINCT department[, 列名] FROM student_info

SELECT DISTINCT department FROM student_info;
SELECT DISTINCT department, major FROM student_info;

7.6 限制结果集合记录条数（分页）

方法：SELECT * FROM table_name LIMIT [起始下标, ]搜寻条数

注意:

LIMIT一般放在最后

-- 查询前两条
SELECT * FROM student_info LIMIT 2;
-- 查询两条，从5+1开始
SELECT * FROM student_info LIMIT 5, 2;

7.7 对查询结果排序

7.7.1 单列查询

方法：ORDER BY 列名 [ASC|DESC]

ASC为默认升序，DESC为倒序

SELECT student_info.number FROM student_info ORDER BY student_info.number ASC;
SELECT * FROM student_info ORDER BY student_info.number ASC;
SELECT * FROM student_info ORDER BY student_info.number DESC;

7.7.2 多列查询

方法：ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC],...

第八章带搜索条件的查询

8.1 简单搜索条件

运算符	示例	描述
=	a = b
<=>	a <=> b	相等，NULL值安全相等
<>或!=	a<>b 或 a!=b	不等
<		小于
<=		小于等于
>		大于
>=		大于等于
BETWEEN	a BETWEEN b AND C	a 属于[b, c]
NOT BETWEEN	a NOT BETWEEN b AND C	a 不属于[b, c]

-- 查id等于20210101的学生的所有成绩
SELECT * FROM student_score WHERE number = 20210101;
-- 查看除了这个学生的其他的学生的成绩
SELECT * FROM student_score WHERE number != 20210101;
SELECT * FROM student_score WHERE number <> 20210101;
-- 查成绩及格
SELECT * FROM student_score WHERE score >= 60;
-- 查成绩不及格
SELECT * FROM student_score WHERE score < 60;
-- 查成绩在60到80之间的成绩
SELECT * FROM student_score WHERE score BETWEEN 60 AND 80;
-- 查成绩不在60到80之间的成绩
SELECT * FROM student_score WHERE score NOT BETWEEN 60 AND 80;

8.2 匹配列表中的元素 IN

运算符	示例	描述
IN	a IN (b1, b2)	a是b1和b2中的某一个
NOT IN	a NOT IN (a1, a2)	a不是a1和a2中的任何一个

-- 匹配查询
SELECT * FROM student_info WHERE major IN ('软件工程', '飞行器设计');
SELECT * FROM student_info WHERE major NOT IN ('软件工程', '飞行器设计');

8.3 匹配NULL

NULL代表未确认，查找NULL不能简单用= NULL

运算符	示例	描述
IS NULL	a IS NULL	a的值是NULL
IS NOT NULL	a IS NOT NULL	a的值不为NULL

-- NULL
SELECT * FROM student_info WHERE name IS NULL;
SELECT * FROM student_info WHERE name IS NOT NULL;

8.4 多个搜索条件

8.4.1 AND运算符

-- AND
SELECT * FROM student_info WHERE sex = '男' AND department = '计算机学院';

8.4.2 OR运算符

-- OR
SELECT * FROM student_info WHERE sex = '男' OR department = '计算机学院';

8.4.3 更复杂的搜索条件组合

AND优先级高于OR

-- 复杂条件搜索
-- sorce < 60 + subject = 'MySQL是怎样运行的'
-- source > 90
SELECT * FROM student_score WHERE score > 90 OR score < 60 AND student_score.subject = 'MySQL是怎样运行的';
-- source > 90 + sorce < 60
-- subject = 'MySQL是怎样运行的'
SELECT * FROM student_score WHERE (score > 90 OR score < 60) AND student_score.subject = 'MySQL是怎样运行的';
SELECT * FROM student_score WHERE (score > 90 || score < 60) && student_score.subject = 'MySQL是怎样运行的';

8.5 通配符

支持模糊查询的两个运算符

运算符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT LIKE b	a不匹配b

%代表任意个字符
_代表一个字符

-- 通配符
SELECT *  FROM student_info WHERE name LIKE '狗%';
SELECT *  FROM student_info WHERE name LIKE '%甘%';
SELECT *  FROM student_info WHERE name LIKE '狗__';

8.5.1 转义通配符 \反斜杠

内容中包含%和_用\%和\_代替

-- 转义通配符
SELECT *  FROM student_info WHERE name LIKE '狗\__';
SELECT *  FROM student_info WHERE name LIKE '狗\%_';

第九章表达式和函数

9.1 表达式

9.1.1 操作数

9.1.2 运算符

/ 是除法
DIV 是除法取整
% 是除法取余
AND 同 &&
OR 同 ||
XOR异或，有且只有一个为真

9.1.3 表达式的使用

作为计算字段放在SELECT语句中

查成绩并且在原有基础上+100

-- 放在SELECT中
SELECT number,  subject, score + 100 FROM student_score;
SELECT number,  subject, score + 100 AS `score` FROM student_score;
-- ``用来装列名,''用来装普通字符串
SELECT name, '我是一个装饰', `sex`, 'sex' FROM student_info;
-- 不能用``将不是列明的内容装起来
SELECT name, `我是一个装饰` FROM student_info;
-- 计算器
SELECT 5 * 6 +128 -32;

作为搜索条件放在WHERE子句中

WHERE子句可以不包含列，只要结果为真就可以查出所有数据，不能为NULL和0

-- 放在WHERE中
-- 假 查不出来数据
SELECT * FROM student_info WHERE 0;
SELECT * FROM student_info WHERE NULL;
SELECT * FROM student_info WHERE 2 - 2;
SELECT * FROM student_info WHERE 2 < 1;
-- 真 查出所有数据
SELECT * FROM student_info WHERE 2 > 1;
SELECT * FROM student_info WHERE 2 + 2;
SELECT * FROM student_info WHERE 2;

9.1.4 表达式中的NULL

操作NULL时，所有结果都为NULL

除了 <=>, IS NULL, IS NOT NULL外的比较结果都为 NULL

-- 查出一行数据 结果全是NULL
SELECT 1>NULL, 2-NULL, NULL IS NULL;
-- 查处结果是一行1
SELECT NULL IS NULL;

9.2 函数

9.2.1 文本处理函数

名称	调用示例	示例结果	描述
LEFT	LEFT('abc123', 3)	abc	给定字符串从左边取指定长度的子串
RIGHT	RIGHT('abc123', 3)	123	给定字符串从右边取指定长度的子串
LENGTH	LENGTH('abc')	3	给定字符串的长度
LOWER	LOWER('ABC')	abc	给定字符串的小写格式
UPPER	UPPER('abc')	ABC	给定字符串的大写格式
LTRIM	LTRIM(' abc')	abc	给定字符串左边空格去除后的格式
RTRIM	RTRIM('abc ')	abc	给定字符串右边空格去除后的格式
SUBSTRING	SUBSTRING('abc123', 2, 3)	bc1	给定字符串从指定位置截取指定长度的子串
CONCAT	CONCAT('abc', '123', 'xyz')	abc123xyz	将给定的各个字符串拼接成一个新字符串

-- 字符处理函数
-- LEFT
SELECT LEFT('abc123', 4); -- abc1
-- RIGHT
SELECT RIGHT('abc123',2); -- 23
-- LENGTH
SELECT LENGTH('何先生'); -- 9
-- LOWER
SELECT LOWER('ABCDEF'); -- abcdef
-- UPPER
SELECT UPPER('abcdef'); -- ABCDEF
-- LTRIM
SELECT LTRIM('   ABC'); -- 'ABC'
SELECT '   ABC'; -- '   ABC'
-- RTRIM
SELECT RTRIM('   ABC   '); -- '   ABC'
-- TRIM
SELECT TRIM('   A B C   '); -- 'A B C'
-- SUBSTRING
SELECT SUBSTRING('何先生abcdefg何先生', 2, 5); -- '先生abc'
-- CONCAT
SELECT CONCAT('a_', 'b', 'CD21', 'AA'); -- 'a_bCD21AA'
-- CHAR_LENGTH
SELECT CHAR_LENGTH('何先生abcdefg何先生'); -- 13


-- 和数据库结合使用
SELECT CONCAT('学号为', number, '的同学在《', subject, '》课程上的成绩是：', score) AS '成绩描述' FROM student_score;
-- 学号为20210101的同学在《MySQL是怎样运行的》课程上的成绩是：88
-- 学号为20210101的同学在《计算机是怎样运行的》课程上的成绩是：78
-- 学号为20210102的同学在《MySQL是怎样运行的》课程上的成绩是：98
-- 学号为20210102的同学在《计算机是怎样运行的》课程上的成绩是：100
-- 学号为20210103的同学在《MySQL是怎样运行的》课程上的成绩是：61
-- 学号为20210103的同学在《计算机是怎样运行的》课程上的成绩是：59
-- 学号为20210104的同学在《MySQL是怎样运行的》课程上的成绩是：46
-- 学号为20210104的同学在《计算机是怎样运行的》课程上的成绩是：55

9.2.2 日期处理函数

常见日期处理函数

名称	调用示例	示例结果	描述
NOW	NOW()	2019-08-16 17:10:43	返回当前日期和时间
CURDATE	CURDATE()	2019-08-16	返回当前日期
CURTIME	CURTIME()	17:10:43	返回当前时间
DATE	DATE('2019-08-16 17:10:43')	2019-08-16	将给定日期和时间值的日期提取出来
DATE_ADD	DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-18 17:10:43	将给定的日期和时间值添加指定的时间间隔
DATE_SUB	DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY)	2019-08-14 17:10:43	将给定的日期和时间值减去指定的时间间隔
DATEDIFF	DATEDIFF('2019-08-16', '2019-08-17');	-1	返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
DATE_FORMAT	DATE_FORMAT(NOW(),'%m-%d-%Y')	08-16-2019	用给定的格式显示日期和时间

MYSQL支持的时间单位

时间单位	描述
MICROSECOND	毫秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	星期
MONTH	月
QUARTER	季度
YEAR	年

MYSQL常见日期时间通配符

格式符	描述
%b	简写的月份名称（Jan、Feb、...、Dec)
%D	带有英文后缀的月份中的日期（0th、1st、2nd、...、31st)）
%d	数字格式的月份中的日期(00、01、02、...、31)
%f	微秒（000000-999999）
%H	二十四小时制的小时 (00-23)
%h	十二小时制的小时 (01-12)
%i	数值格式的分钟(00-59)
%M	月份名（January、February、...、December）
%m	数值形式的月份(00-12)
%p	上午或下午（AM代表上午、PM代表下午）
%S	秒(00-59)
%s	秒(00-59)
%W	星期名（Sunday、Monday、...、Saturday）
%w	周内第几天（0=星期日、1=星期一、 6=星期六）
%Y	4位数字形式的年（例如2019）
%y	2位数字形式的年（例如19）

-- 时间格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i|%s 星期%w');
-- 2024年03月30日 06:07|31 星期6

9.2.3 数值处理函数

名称	调用示例	示例结果	描述
ABS	ABS(-1)	1	取绝对值
Pi	PI()	3.141593	返回圆周率
COS	COS(PI())	-1	返回一个角度的余弦
EXP	EXP(1)	2.718281828459045	返回e的指定次方
MOD	MOD(5,2)	1	返回除法的余数
RAND	RAND()	0.7537623539136372	返回一个随机数
SIN	SIN(PI()/2)	1	返回一个角度的正弦
SQRT	SQRT(9)	3	返回一个数的平方根
TAN	TAN(0)	0	返回一个角度的正切

9.2.4 流程控制表达式函数

用法1： CASE WHEN 表达式1 THEN 结果 [WHEN 表达式2 THEN 结果2] [ELSE 默认结果] END
用法2： CASE 待比较表达式 WHEN 表达式1 THEN 结果 [WHEN 表达式2 THEN 结果2] [ELSE 默认结果] END
注意： 必须要END结尾

-- 流程控制
SELECT
    *,
    CASE
        WHEN score < 60 THEN '不及格'
        WHEN score < 90 THEN '及格'
        ELSE '优秀'
        END AS '评价'
FROM
    student_score;

number	subject	score	评价
20210101	MySQL是怎样运行的	88	及格
20210101	计算机是怎样运行的	78	及格
20210102	MySQL是怎样运行的	98	优秀
20210102	计算机是怎样运行的	100	优秀
20210103	MySQL是怎样运行的	61	及格
20210103	计算机是怎样运行的	59	不及格
20210104	MySQL是怎样运行的	46	不及格
20210104	计算机是怎样运行的	55	不及格

SELECT
    name, department, major,
    CASE department
        WHEN '计算机学院' THEN '一级学院'
        WHEN '航天学院' THEN '二级学院'
        ELSE '未设定'
        END AS '学院类别'
FROM
    student_info;

name	department	major	学院类别
狗哥	计算机学院	计算机科学与工程	一级学院
猫爷	计算机学院	计算机科学与工程	一级学院
艾希	计算机学院	软件工程	一级学院
亚索	计算机学院	计算机科学与工程	一级学院
莫甘娜	航天学院	飞行器设计	二级学院
赵信	航天学院	电子信息	二级学院

IF: IF(表达式1, 表达式2, 表达式3) = 表达式1 ? 表达式2 : 表达式3
IFNULL: IFNULL(a, b) = a == NULL ? b : a
NULLIF: NULLIF(a, b) = a == b ? NULL : a

9.2.5 汇总函数

MAX
MIN
SUM
AVG
COUNT

-- 聚合函数、汇总函数
SELECT COUNT(number) AS '人数', MAX(student_score.score) AS '最高分', MIN(student_score.score) AS '最低分', AVG(student_score.score) AS '平均分', SUM(student_score.score) AS '总分' FROM student_score;

人数	最高分	最低分	平均分	总分
8	100	46	73.1250	585

9.3 隐式类型转换

只要某个值的类型与上下文要求的类型不符，MySQL就会根据上下文环境中需要的类型对该值进行类型转换，由于这些类型转换都是MySQL自动完成的，所以也可以被称为隐式类型转换。

把操作数类型转换为适合操作符计算的相应类型。

--     1 + 2       →   3
--     '1' + 2     →   3
--     '1' + '2'   →   3
SELECT 1 + 2, '1' + 2, '1' + '2';
-- +-------+---------+-----------+
-- | 1 + 2 | '1' + 2 | '1' + '2' |
-- +-------+---------+-----------+
-- |     3 |       3 |         3 |
-- +-------+---------+-----------+
-- 1 row in set (0.00 sec)

将函数参数转换为该函数期望的类型。

-- CONCAT('1', '2')    →   '12'
-- CONCAT('1', 2)      →   '12'
-- CONCAT(1, 2)        →   '12'
SELECT CONCAT('1', '2'), CONCAT('1', 2), CONCAT(1, 2);
-- +------------------+----------------+--------------+
-- | CONCAT('1', '2') | CONCAT('1', 2) | CONCAT(1, 2) |
-- +------------------+----------------+--------------+
-- | 12               | 12             | 12           |
-- +------------------+----------------+--------------+
-- 1 row in set (0.00 sec)

存储数据时，把某个值转换为某个列需要的类型。

-- i1和i2 都是数字类型 s是字符串
CREATE TABLE t (
    i1 TINYINT,
    i2 TINYINT,
    s VARCHAR(100)
);
-- 传入的数据不是按照约定类型的，但MYSQL进行了隐式类型转换
INSERT INTO t(i1, i2, s) VALUES('100', '100', 200);
-- Query OK, 1 row affected (0.01 sec)

第十章分组查询

10.1 分组数据

10.1.1 复杂的数据统计

面对需要统计多组数据，按照列的不同属性去统计，一般需要写多个查询语句，这样太慢了；

10.1.2 创建分组

注意：非分组列不要放在查询里，无意义如number列,我还报错了

SELECT subject, COUNT(subject) AS '考试人数' FROM student_score GROUP BY subject;

subject	考试人数
MySQL是怎样运行的	4
计算机是怎样运行的	4

10.1.3 带有WHERE子句的分组查询

SELECT subject, COUNT(subject) AS '及格人数' FROM student_score WHERE score > 60 GROUP BY subject;

subject	及格人数
MySQL是怎样运行的	3
计算机是怎样运行的	2

10.1.4 作用于分组的过滤条件

-- 作用于分组的过滤条件（查分数大于60分，最高分大于90分，平均分大于80分的各科平均成绩）
SELECT subject, AVG(score) FROM student_score WHERE score > 60 GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 80;
-- 下面两条作用效果一样（查除了《MySQL是怎样运行的》这门课，最高分大于90分，平均分大于70分的各科平均分）
SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 70 AND subject != 'MySQL是怎样运行的';
SELECT subject, AVG(score) FROM student_score WHERE subject != 'MySQL是怎样运行的' GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 70;

10.1.5 分组和排序

-- 分组排序
SELECT student_score.subject, AVG(student_score.score) FROM student_score GROUP BY student_score.subject ORDER BY AVG(score) DESC;

10.1.6 多个分组

-- 多个分组列
SELECT department, major, COUNT(number) AS '人数' FROM student_info GROUP BY department, major;

10.1.7 使用分组查询其他注意事项

如果存在NULL，NULL也会单独作为一组数据
GROUP BY 后面也可以跟表达式，但是不能是汇总函数
WHERE作用于分组之前的筛选，HAVING作用于分组之后的组筛选

-- 分组采用表达式
-- 分组查询项需要和分组依据一致，CONCAT('学科:', subject),
SELECT CONCAT('学科:', subject), COUNT(student_score.number) AS 人数 FROM student_score GROUP BY CONCAT('学科:', subject);

CONCAT('学科:', subject)	人数
学科:MySQL是怎样运行的	4
学科:计算机是怎样运行的	4

第十一章子查询

11.1 多表查询需求

多个表关联字段拼接

11.2 标量子查询

标量子查询代表的仅仅是一个值
子查询必须用括号括起来

-- 标量子查询 查名字叫狗哥的人的所有学科成绩
SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '狗哥');

-- 查比狗哥学号大的所有人的信息
SELECT * FROM student_info WHERE number > (SELECT number FROM student_info WHERE name = '狗哥');

11.3 列子查询

-- 列子查询 查计算机科学与工程的学生的成绩
SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');

11.4 行子查询

加入不知道子查询会出现几条记录，加上LIMIT 1吧
两边均要用括号

SELECT * FROM student_score WHERE (number, subject) = (SELECT number, 'MySQL是怎样运行的' FROM student_info LIMIT 1);

11.5 表子查询 (多行多列)

-- 此版本的MySQL还不支持“LIMIT&IN/ALL/AANY/SOME子查询” MYSQL8.3
SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, 'MySQL是怎样运行的' FROM student_info WHERE major = '计算机科学与工程');

11.6 `EXISTS` 和 `NOT EXISTS`子查询

-- 如果存在记录就查
SELECT * FROM student_info WHERE EXISTS(SELECT * FROM student_info WHERE number = 10086);
-- 如果不存在记录就查
SELECT * FROM student_info WHERE NOT EXISTS(SELECT * FROM student_info WHERE number = 10086);
-- 相等
SELECT * FROM student_info WHERE (SELECT COUNT(*) FROM student_info WHERE number != 10086) > 1;

11.7 不相关子查询和相关子查询

像上面的，外层查询和子查询不想管，独立运行叫做不相关查询
相关查询是，外层查询的值需要被子查询应用，外层查询每查到一行，再拿行数据和子查询进行匹配

-- 相关子查询 查存在成绩的学生信息
SELECT * FROM student_info a WHERE EXISTS(SELECT number FROM student_score b WHERE  a.number = b.number);
-- 显然这个效率很低
SELECT * FROM student_info a WHERE number = IF((SELECT COUNT(number) FROM student_score b WHERE  a.number = b.number) > 0, a.number, NULL);

11.8 对同一个表的子查询

-- 查大于平均分的成绩
SELECT * FROM student_score WHERE score > (SELECT AVG(score) FROM student_score);
-- 查和狗哥一个学院的人
SELECT * FROM student_info WHERE department = (SELECT department FROM student_info WHERE name = '狗哥');

第十二章连接查询

12.1 再次认识关系表

将两张表合起来的麻烦
- 浪费存储空间
- 维护麻烦，修改信息可能要修改多条数据

12.2 连接的概念

笛卡尔积

12.3 连接查询过滤条件

12.4 内连接和外连接

SELECT * FROM student_info a, student_score b WHERE a.number = b.number;

内连接：驱动表的数据在被驱动表中不存在，则不会展示出来
外连接：即使不存在也展示
内连接的ON和WHERE是等价的

12.4.1 连接语法

左外连接

用法： SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 过滤条件]

外连接的ON连接条件不能省略

SELECT * FROM student_info a LEFT OUTER JOIN student_score b ON a.number = b.number;

右外连接

用法： SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 过滤条件]

SELECT * FROM student_info a RIGHT OUTER JOIN student_score b ON a.number = b.number;

12.4.2 内连接语法

用法： SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 过滤条件]

等价的：

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2; -- 推荐使用
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1 , t2; -- 这里不能使用ON了，只能用WHERE

12.5 多表连接

用法： SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id INNER JOIN t3 ON t1.id = t3.id SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id = t2.id AND t1.id = t3.id

注意：内连接是取笛卡尔积，所以多张表的连接位置无限制，但是外连接不行，必须明确位置

12.6 表的别名

SELECT t1.id FROM table1 AS t1 ORDER BY DESC t1.id;
SELECT t1.id FROM table1 t1 ORDER BY DESC t1.id;

自连接

-- 错误写法
SELECT * FROM t1, t1; -- MYSQL不允许同名，要用别名
SELECT * FROM t1 AS tt1, t1 AS tt2;
-- 自连接，查看和狗哥专业相同的人员信息
SELECT * FROm student_info a JOIN student_info b ON a.major = b.major WHERE a.name = '狗哥';

12.8 连接查询和子查询的转换

mysql有时候会把子查询在内部转化为连接查询

第十三章并集查询（合并查询、组合查询）

13.1 涉及单表的组合查询

-- 单表组合查询
SELECT name, student_info.department, student_info.major FROM student_info WHERE sex = '男' OR number > 20210101;
SELECT name, student_info.department, student_info.major FROM student_info WHERE sex = '男' UNION SELECT student_info.department, name, student_info.major FROM student_info WHERE number > 20210101;

name	department	major
狗哥	计算机学院	计算机科学与工程
猫爷	计算机学院	计算机科学与工程
亚索	计算机学院	计算机科学与工程
赵信	航天学院	电子信息
计算机学院	猫爷	计算机科学与工程
计算机学院	艾希	软件工程
计算机学院	亚索	计算机科学与工程
航天学院	莫甘娜	飞行器设计
航天学院	赵信	电子信息

注意：
- 1. 查询列名类型最好一样，否则mysql会转换
- 1. 查询的列名需要一致，不然是没意义的，或者故意这样做，两个组合查询的查询内容是根据写好的列顺序来组合的，如上表名称和学院位置和第一个语句相反，查出来的内容位置也会错位
- 1. 列名以第一个查询为准

13.2 涉及不同表的组合查询

语法和单表一样,使用 UNION

13.3 包含和去除重复行

默认情况下会去掉重复的记录
想要保留重复记录使用 UNION ALL

13.4 组合查询中的 `ORDER BY` 和 `LIMIT`

最终结果排序，在结尾加就行
小查询单独加排序可能无效，加上LIMIT限定有限条数可能可以

第十四章数据的插入、删除和更新

14.1 准备工作

-- 建表
CREATE table first_table(
    first_column INT,
    second_column VARCHAR(100)
);

14.2 插入数据

14.2.1 插入完整的记录

-- 不写列明 默认按照建表的顺序 （每列必须明确写出值，可以用NULL替代）
INSERT INTO first_table VALUE (1, '哈哈');
-- 指定列名
INSERT INTO first_table(first_column, second_column) VALUE (2, '嘿嘿');
-- 可以调换列顺序
INSERT INTO first_table(second_column, first_column) VALUE ('嘿嘿嘿', 3);

14.2.2 插入记录的一部分

如果某列有默认值、或者允许为NULL，他就可以不插入，但是要注意插入顺序，如果在中间的话就要指定列名

14.2.3 批量插入记录

-- 批量插入数据 VALUE => VALUES （VALUES也可以只插入一条数据）
INSERT INTO
    first_table(second_column, first_column)
VALUE
    ('嘿哈', 5),
    ('嗯哼', 6),
    ('唉嘿', 7),
    ('嘻嘻', 8),
    ('呜呜呜', 9);

14.2.4 将查询的结果插入表中

-- 插入其他表的数据
INSERT INTO first_table SELECT * FROM first_table LIMIT 4;
INSERT INTO first_table(first_column, second_column)  SELECT first_column, second_column FROM first_table WHERE first_column = 3;

14.2.5 INSERT IGNORE

如果存在唯一约束或者主键，插入的数据有冲突的话，单使用INSERT会报错，采用INSERT IGNORE会忽略重复项

14.2.6 INSERT ON DUPLICATE KEY UPDATE

如果存在数据，选择更新，我们不需要这样，这样会出现逻辑混乱

14.3 删除数据

用法： DELETE FROM table_name [WHERE 条件],不带WHERE条件是不安全的，当前版本会被提示错误;

14.4 更新数据

用法： UPDATE table_name SET 列1=值1, 列2=值2 [WHERE 条件]

第十五章视图

15.1 创建视图

用法： CREATE VIEW 视图名 AS 查询语句

15.2 使用视图

可以把视图当作一个虚拟表，对正常表的操作，可以用到视图上

15.2.1 利用视图创建新的视图

用法和视图创建一样，查询语句作用于原来的视图

15.2.2 创建视图时指定自定义列名

用法： CREATE VIEW 视图名(新列名1, 新列名2) AS SELECT 对应原始列名1, 对应原始列名2 FROM table_name | view_name [查询条件]

15.3 查看和删除视图

15.3.1 查看所有视图

SHOW TABLES;视图和原始表是在一起的，注意视图创建时，视图名不能和表重名

15.3.2 查看视图的定义

SHOW CREATE VIEW 视图名;

15.3.3 删除视图

DROP VIEW 视图名;

15.4 可更新视图

如果视图是普通查询语句建立的，不包括汇总，去重，组合，查询中的子查询等，可以直接在视图上更新数据，会映射到真实数据

第十六章存储程序

存储例程（存储函数、存储过程）
触发器
事件

16.1 用户自定义变量

定义变量： SET @变量名 = 值，可以重新赋值

注意：如果不加@符号，mysql会将变量当作系统变量

查看变量： SELECT @变量名

将变量复制给变量 SET @a = @b，之后再修改b，不会影响a

将查询赋给变量，前提是查询语句的结果是一行一列，可以为NULL

16.2 存储函数

用法：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

16.2.1 创建存储函数

DELIMITER $
CREATE FUNCTION get_score_avg(subject_name VARCHAR(10))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(student_score.score) FROM student_score WHERE subject = subject_name);
END $
DELIMITER ;

16.2.2 存储函数的调用

SELECT get_score_avg('计算机是怎样运行的');

16.2.3 查看和删除存储函数

-- 查看已经定义的函数和属性
SHOW FUNCTION STATUS [LIKE 函数名的匹配]; -- 不写LIKE会查出来很多函数
-- 查看单个函数
SHOW CREATE FUNCTION 函数名;
-- 删除函数
DROP FUNCTION 函数名;

42 KiB Raw Permalink Blame History