MySQL笔记(一)：基础知识

最新推荐文章于 2023-01-04 12:05:28 发布

咖啡不甜不好喝

最新推荐文章于 2023-01-04 12:05:28 发布

阅读量230

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/hsg_happyLearning/article/details/86001688

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL笔记(一)：基础知识

1、GBK中一个汉字占2个字节，一个字母占一个字节；UTF-8中一个汉字占3个字节，一个字母占一个字节

在Mysql中进行验证：

查看Mysql客户端的编码方式：

select variables like '%char%';

查看字符串的字节长度，length()方法获取的是字节的长度：

select length('张三丰hello');

MySQL中字节与字符是有区别的，一个字母和一个汉字都算是一个字符，但是二者所占的字节数是不一样的

2、MySQL中和分组函数一同查询出来的数据要求是group by后的数据

常见的分组函数：sum、avg、count、min、max

例如：查询各个部门的总人数和平均工资

# SELECT中的department_id必须是GROUP BY的字段，否则会报错
SELECT 
	department_id, count(*), AVG(salary)
FROM
	employees
GROUP BY department_id

3、DATEDIFF()函数可以求两个日期之间相差的天数

SELECT DATEDIFF(now(), '1995-5-20');

4、having可以对分组(group by)后的结果进行筛选

例如：查询员工数大于2的部门

SELECT
	department_id AS 部门编号,
	count( * ) AS 部门总人数 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	count( * ) > 2

having是对前面的查询结果再进行筛选，注意：count(*) > 2不能放在where中，where中只能查询from 表中存在的字段

分组筛选总结：

①分组筛选分为两大类：分组前筛选和分组后筛选

	数据源	位置	关键字
分组前筛选	原始表(from表)	group by子句的前面	where
分组后筛选	分组后得到的结果集	group by子句的后面面	having

②分组函数做条件肯定是放在having子句中，常见的分组函数：sum、max、min、count、avg

③能使用分组前筛选的，优先考虑使用分组前筛选，可以提高查询效率

④group by中支持多个字段分组，并且字段之间的先后顺序没有要求

⑤Mysql中，group by和having子句中都支持别名；Oracle中，group by和having子句中都不支持别名；所以，尽量不要在group by和having子句中使用别名

5、如果为表起了别名，则查询的字段就不能再使用原来的表名进行限定

例如：

select student.name from student s where student.id=1(错误)
select s.name from student s where s.id=1(正确)

6、ifnull函数，判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

例如：

select ifnull(commission_pct,0) from employees;

isnull函数，判断某字段或表达式是否为null，如果是，则返回1，否则返回0

7、like用于模糊查询，不仅可以用于字符，也可以用于数字。通配符：%任意多个字符，_任意单个字符

# slary是数字类型
select
	salary
from 
	employees
where
	salary like '_2%';

8、连接查询

①连接分为内连接和外连接，内连接分为等值连接、非等值连接、自连接，外连接分为左连接(left join)、右连接(right join)、全连接(full join)

②inner join的效果与内连接中的等值连接是一样的，并且inner关键字可以省略

一、内连接

特点：

①多表等值连接的查询结果为多表的交集部分

②n表连接至少需要n-1个连接条件

③查询时，多表的顺序没有要求

④尽量为表起别名

⑤可以搭配排序、分组、筛选等子句一起使用

分类：等值连接、非等值连接、自连接

下面的方式一和方式二的查询效果完全相同

（1）等值连接

# 方式一
SELECT
	last_name,
	department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id
	
	
# 方式二	
SELECT
	last_name,
	department_name 
FROM
	employees e
[INNER] JOIN 
	departments d 
ON 
	e.department_id = d.department_id

（2）非等值连接

# 案例：查询每个员工的工资级别
#方式一：
SELECT
	last_name,
	salary,
	grade_level 
FROM
	employees e,
	grade g 
WHERE
	e.salary BETWEEN g.lowest_sal AND g.highest_sal
	
# 方式二：
SELECT
	last_name,
	salary,
	grade_level 
FROM
	employees e
[INNER] JOIN 
	grade g 
ON 
	e.salary BETWEEN g.lowest_sal AND g.highest_sal

（3）自连接

# 案例：查询员工和员工领导的信息
# 方式一：
SELECT
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name 
FROM
	employees e,
	employees m 
WHERE
	e.manager_id = m.employee_id
	
# 方式二：
SELECT
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name 
FROM
	employees e
[INNER] JOIN
	employees m 
ON
	e.manager_id = m.employee_id

二、外连接

特点：

1、外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的数据，则查询匹配的值

如果从表中没有和它匹配的数据，则显示null

外连接查询结果=内连接查询结果+主表中有而从表中没有的数据

2、左外连接，left join左边的是主表；右外连接，right join右边的是主表

3、左外连接和右外连接交换两个表的顺序，可以实现同样的效果

分类：左外连接、右外连接

（1）左外连接

# 查询员工ID、员工名、员工工资和员工所在部门的部门名
SELECT
	employee_id, last_name, salary, department_name
FROM
	employees e
LEFT JOIN
	departments d
ON 
	e.department_id = d.department_id

（2）右外连接

# 查询员工ID、员工名、员工工资和员工所在部门的部门名，与上面的左外连接的效果是一样的
SELECT
	employee_id, last_name, salary, department_name
FROM
	departments d
RIGHT JOIN
	employees e
ON 
	e.department_id = d.department_id

9、子查询

一、按结果集的行列数分类

①标量子查询：结果集只有一行一列

②列子查询：结果集只有一列多行

③行子查询：结果集一行多列

④表子查询：结果集多行多列

二、按子查询出现的位置分类

（1）出现在select后面

仅仅支持标量子查询

# 查询每个部门的员工个数, employees是员工信息表，departments是部门信息表
SELECT d.*, (
	SELECT
		count(*)
	FROM
		employees e
	WHERE
		e.department_id = d.department_id
) count
FROM
	departments d
ORDER BY
	count 
DESC

# 查询每个专业的男生总数和女生总数(※)
SELECT
	majorid,
	(
		SELECT 
			count(*)
		FROM
			student t1
		where
			sex = '男'
		AND
			t1.majorid = t.majorid
	) boynum,
	(
		SELECT 
			count(*)
		FROM
			student t1
		where
			sex = '女'
		AND
			t1.majorid = t.majorid
	)
FROM
	student t
GROUP BY
	majorid

（2）出现在from后面

支持表子查询

# 查询每个部门的平均工资的工资等级，以及部门id和部门名
SELECT
	department_id, 
	(
		SELECT 
			department_name
		FROM
			departments d
		WHERE
			new_tab.department_id=d.department_id
	) avg_salary,
	CASE 
		WHEN avg_salary>20000 THEN '高等收入'
		WHEN avg_salary>10000 THEN '中等收入'
		ELSE '低等收入'
	END AS '收入等级'
FROM (
	SELECT
		department_id,  avg(salary) avg_salary
	FROM
		employees
	WHERE
		department_id IS NOT NULL
	GROUP BY
		department_id
) new_tab

（3）出现在where或having后面

支持标量子查询、列子查询，也支持行子查询

# 查询工资最高的员工所在的部门的部门名
SELECT
	department_name
FROM
	departments d
WHERE
	d.department_id in (
		SELECT
			department_id
		FROM
			employees e
		WHERE
			e.salary in (
				SELECT 
					max(salary)
				FROM
					employees
			)
	)
	
# 查询平均工资高于公司总平均工资的部门信息
SELECT
	*
FROM
	departments
WHERE
	department_id in (
		SELECT
			department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING
			AVG(salary) > (
				SELECT
					AVG(salary)
				FROM
					employees
			)
	)

（4）出现在exists后面（相关子查询）

支持表子查询

语法：exists(完整的查询语句);

结果：0或1，0表示括号中的查询结果中没有记录，1表示括号中的查询结果至少有一条记录

exists可以用in代替

# 是否有员工的工资等于30000
SELECT EXISTS(SELECT * FROM employees WHERE salary = 30000);

# 查询有员工的部门的部门名
SELECT
	department_name
FROM
	departments d
WHERE
	EXISTS(
		SELECT 
			*
		FROM
			employees e
		WHERE
			d.department_id = e.department_id
	)
	
# 查询有员工的部门的部门名，用in代替了exists
SELECT
	department_name
FROM
	departments d
WHERE
	d.department_id in (
		SELECT 
			department_id
		FROM
			employees e
	)

（5）出现在DML语句(insert、update、delete)中(※)

# 复制数据
INSERT INTO	beauty(name, sex, borndate, phone, photo, boyfriend_id)
SELECT name, sex, borndate, phone, photo, boyfriend_id FROM beauty;

# 修改张三的女朋友的电话号码为114
UPDATE 
	beauty b1 INNER JOIN boys b2 ON b1.boyfriend_id = b2.id 
SET b1.phone = '114' 
WHERE b2.boyName = '张三'

# 删除张三女朋友的信息
DELETE 
	b2
FROM 
	boys b1 INNER JOIN beauty b2 ON b1.id = b2.boyfriend_id
WHERE 
	b1.boyName = '张三'

# 删除张三的信息和张三女朋友的信息
DELETE	
	b1, b2
FROM 
	boys b1 INNER JOIN beauty b2 ON b1.id = b2.boyfriend_id
WHERE 
	b1.boyName = '张三'

10、SQL语句的执行顺序

select 查询列表 ⑦

from 表 ①

连接类型 join 表 ②

on 连接条件 ③

where 筛选条件 ④

group by 分组列表 ⑤

having 分组后的筛选 ⑥

order by 排序列表 ⑧

limit 偏移, 条目数 ⑨

11、联合查询

作用：将多条查询语句的结果合并成一个结果

语法：

查询语句1
union
查询语句2
union
...
查询语句n

特点：

①要求所有的查询语句的查询结果列数一致

②要求所有的查询语句的查询结果的每一列的类型和顺序最好是一致的

③union默认对联合查询之后的结果去重，如果不想对联合查询结果去重可以使用union all

应用场景：要查询的结果来自于多个表，并且表之间没有直接的连接关系，但是查询语句的查询结果一致

案例：

# 查询学生姓名中包含a字符或邮箱中包含b字符的学生信息
SELECT * FROM student WHERE name LIKE '%a%' 
UNION
SELECT * FROM student WHERE email LIKE '%b%'

12、truncate PK delete

①delete可以加where筛选条件，truncate不能加筛选条件，即truncate只能删除全部的数据不能筛选

②truncate删除效率高一些

③如果要删除的表中有自增长列，用delete删除后再插入数据，自增长列的值从断点开始；用truncate删除后再插入数据，自增长列的值从1开始

④delete删除有返回值，truncate删除没有返回值

⑤delete删除可以进行事务回滚，truncate删除不能进行事务回滚

# 删除admin表的所有数据
TRUNCATE TABLE admin

13、字段类型

（1）整型

整数类型	字节	范围
Tinyint	1	有符号：-2^7~27-1 无符号：0~2^8-1
Smallint	2	有符号：-2^15~216-1 无符号：0~2^16-1
Mediumint	3	无符号：-2^23~223-1 无符号：0~2^24-1
Int/Integer	4	无符号：-2^31~231-1 无符号：0~2^32-1
Bigint	8	无符号：-2^63~263-1 无符号：0~2^64-1

特点：

如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
如果不设置长度，会有默认的长度。长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

（2）小数

分类：
- 浮点型：float(M,D)、double(M,D)
- 定点型：dec(M，D)、decimal(M,D)
特点：
- M：整数部位+小数部位，D：小数部位，如果超过范围，则插入临界值
- M和D都可以省略，如果是decimal，则M默认为10，D默认为0；如果是float和double，则会根据插入的数值的精度来决定精度
- 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

（3）字符型

较短的文本：char、varchar
其他：
- binary和varbinary用于保存较短的二进制
- enum用于保存枚举
- set用于保存集合
较长的文本：text、blob(较大的二进制)

特点：

写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省低

注意：M的意思是存放的最大字符数(一个字母是一个字符，一个汉字也是一个字符)

（4）日期类型

分类：

类型数据
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
timestamp 保存日期+时间的时间戳
特点：

类型字节范围时区等的影响
datetime 8 1000——9999 不受
timestamp 4 1970-2038 受

类型	数据
date	只保存日期
time	只保存时间
year	只保存年
datetime	保存日期+时间
timestamp	保存日期+时间的时间戳

类型	字节	范围	时区等的影响
datetime	8	1000——9999	不受
timestamp	4	1970-2038	受

14、约束

六大约束：主键约束(primary key)、外键约束(foreign key)、非空约束(not null)、默认约束(default)、唯一约束(unique)、检查约束(check)

约束类型：

列级约束：可以加在字段的字段类型后面的约束，有主键约束、非空约束、默认约束、唯一约束

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user`(
	`id` INT PRIMARY KEY,
	`username` VARCHAR(64) NOT NULL,
	`gender` CHAR(1) DEFAULT '男' CHECK(`gender`='男' OR `gender`='女'), #mysql不支持检查约束
	`code_id` VARCHAR(32) UNIQUE
);

表级约束：可以加在表的最后面的约束，有主键约束、外键约束、唯一约束

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user`(
	`id` INT,
	`username` VARCHAR(64) NOT NULL,
	`gender` CHAR(1) DEFAULT '男' CHECK(`gender`='男' OR `gender`='女'),
	`code_id` VARCHAR(32),
	`major_id` INT,
	PRIMARY KEY (`id`),
	UNIQUE (`code_id`),
	FOREIGN KEY (`major_id`) REFERENCES major(`id`)
);

注意：MySQL不支持检查约束和列级外键约束

特点：

主键 PK 唯一键

保证唯一性是否允许为空是否允许有多个是否允许组合
主键可以不允许不允许允许
唯一键可以允许，但至多有一个为空允许允许
注意：

①使用唯一约束的字段，最多只能有一条记录的该字段为null

②unique(字段名1)，unique(字段名2)与unique(字段名1, 字段名2)是不一样的，前面是定义了两个唯一键，后面是定义了一个组合唯一键

	保证唯一性	是否允许为空	是否允许有多个	是否允许组合
主键	可以	不允许	不允许	允许
唯一键	可以	允许，但至多有一个为空	允许	允许

15、事务

①事务的ACID属性

原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

②并发问题

脏读：(针对未提交的数据)一个事务在更新一条记录，未提交前，第二个事务读到了第一个事务更新后的记录，那么第二个事务就读到了脏数据，会产生对第一个未提交数据的依赖。一旦第一个事务回滚，那么第二个事务读到的数据，将是错误的脏数据
不可重复读：（读取数据本身的对比）一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变
幻读：（读取结果集条数的对比）一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的）

③事务的隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

注意：

Mysql默认的隔离级别是REPEATABLE READ，Oracle默认的隔离级别是READ COMMITTED，级别越高效率越低
Mysql使用的比较多的存储引擎有innodb、myisam、memory等，其中innodb支持事务，myisam、memory不支持事务
事务中支持insert、update、delete、select(查询不需要事务)，不支持create、drop、alter

④查看和设置隔离级别的命令

# 查看隔离级别
SELECT @@tx_isolation;

# 设置当前会话的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ(隔离级别);

# 设置全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ(隔离级别);

⑤与事务有关的命令

# 关闭自动提交，同时开启事务
SET autocommit=0;

# 开启事务(可以省略)
START TRANSACTION;

# 提交事务
COMMIT;

# 事务回滚
ROLLBACK;

# 设置保存点，只能搭配rollback使用
SAVEPOINT P1;

# 回滚到保存点p1
ROLLBACK TO p1;

咖啡不甜不好喝

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记(一)：基础知识

MySQL笔记(一)：基础知识####1、GBK中一个汉字占2个字节，一个字母占一个字节；UTF-8中一个汉字占3个字节，一个字母占一个字节在Mysql中进行验证：查看Mysql客户端的编码方式：select variables like '%char%';查看字符串的字节长度，length()方法获取的是字节的长度：select length('张三丰hello');MySQL...
复制链接

扫一扫