MySQL相关知识点

最新推荐文章于 2024-03-26 17:43:24 发布
最新推荐文章于 2024-03-26 17:43:24 发布
阅读量390 收藏 2
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48304192/article/details/116400430
版权

MySQL相关知识点

1.SQL语言

1.1概念

SQL(Structure Query Language)结构化查询语言，用于存储数据、更新、查询、和管理关系数据库系统的程序设计语言。

  • 经验：通常执行对数据库的“增删改查”，简称C(Create)R(Read)U(Update)D(Delete)
1.2MySQL应用

对于数据库的操作，需要在进入MySQL环境下进行指令输入，并在依据指令的末尾使用;结束

1.3基本命令

插卡MySQL中所有数据库

SHOW DATABASES;#显示当前MySQL中包含的所有数据库
数据库名称描述
information_schema信息数据库，其中保存着关于所有数据库的信息（元数据）。 元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql核心数据库，主要负责存储数据库的用户、权限设置、关键字等， 以及需要使用的控制和管理信息，不可以删除。
performance_schema性能优化的数据库，MySQL5.5版本中新增的一个性能优化的引擎。
sys系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库 便于发现数据库的多样信息，解决性能瓶颈问题。

创建自定义数据库

CREATE DATABASE mydb1;#创建mydb1数据库
CREATE DATABASE mydb2 CHARACTER SET gbk;#创建数据库并设置编码格式为gbk
CREATE DATABASE IF NOT EXISTS mydb4;#如果mydb4数据库不存在，则创建；如果存在，则不创建

查看数据库创建信息

SHOW CREATE DATABASE mydb2;#查看创建数据库时的基本信息

修改数据库

ALTER DATABASE mydb2 CHARACTER SET gbk;#将数据库mydb2的字符编码集修改为gbk

删除数据库

DROP DATABASE mydb1;#删除数据库mydb1

查看当前所使用的数据库

SELECT DATABASE();#查看当前使用的数据库

使用数据库

USE mydb2;#使用mydb2数据库

2.数据查询

2.1数据库表的基本结构

关系结构数据库是以表格(Table)进行数据存储，表格由“行”和“列”组成

  • 经验：执行查询语句返回的结果集是一张虚拟表。
2.2基本查询

语法：SELECT 列名 FROM 表名

关键字描述
SELECT指定要查询的列
FROM指定要查询的表
2.2.1查询部分列
#查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id,first_name,email
FROM t_employee;
2.2.2查询所有列
#查询员工表中所有员工的所有信息（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;
  • 注意：生产环境下，优先使用列名查询。*的方式需转换成全列名，效率低，可读性差。
2.2.3对列中的数据进行运算
#查询员工表中所有员工的编号、名字、年薪
SELECT employee_id,first_name,salary*12
FROM t_employee;
算数运算符描述
+两列做加法运算
-两列做减法运算
*两列做乘法运算
/两列做除法运算
  • 注意：%是占位符，而非模运算符。
2.2.4列的别名

列 as ‘列名’

#查询员工表中所有员工的编号、名字、年薪（列名均为中文）
SELECT employee_id as "编号",first_name as "名字",salary*12 as "年薪"
FROM t_employee;
2.2.5查询结果去重

DISTINCT 列名

#查询员工表中所有经理的ID
SELECT DISTINCT manager_id
FROM t_employees;
2.3排序查询

语法：SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]

排序规则描述
ASC对前面排序列做升序排序
DESC对前面排序列做降序排序
2.3.1依据单列排序
#查询员工的编号，名字，薪资。按照工资高低进行降序排序。
SELECT employee_id,first_name,salary
FROM t_employees
ORDER BY salary DESC;
2.3.2依据多列排序
#查询员工的编号，名字，薪资，按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）
SELECT employee_id,first_name,salary
FROM t_employees
ORDER BY salary DESC,employee_id ASC;
2.4条件查询

语法：SELECT 列名 FROM 表名 WHERE 条件

关键字描述
WHERE条件在查询结果中，筛选符合条件的查询结果，条件为布尔表达式
2.4.1等值判断（=）
#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id,first_name,salary
FROM t_employees
WHERE salary = 11000;
  • 注意：与java不同（==），mysql中等值判断使用=
2.4.2逻辑判断（and、or、not）
#查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
SELECT employee_id,first_name,salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;
2.4.3不等值判断（>、<、>=、<=、!=、<>）
#查询员工的薪资在6000~10000之间的员工信息（编号、名字、薪资）
SELECT employee_id,first_name,salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;
2.4.4区间判断（between and）
#查询员工的薪资在6000~10000之间的员工信息（编号、名字、薪资）
SELECT employee_id,first_name,salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000;#闭区间，包含区间边界的两个值
  • 注：在区间判断语法中，小值在前，大值在后，反之，得不到正确结果
2.4.5NULL值判断（IS NULL、IS NOT NULL）
  • IS NULL
    • 列名 IS NULL
  • IS NOT NULL
    • 列名 IS NOT NULL
#查询没有提成的员工信息（编号、名字、薪资、提成）
SELECT employee_id,first_name,salary,commission_pct
FROM t_employees
WHERE commission_pct IS NULL;
2.4.6枚举查询（IN（值1，值2，值3））
#查询部门编号为70、80、90的员工信息（编号、名字、薪资、部门编号）
SELECT employee_id,first_name,department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注：in的查询效率较低，可通过多条件拼接
2.4.7模糊查询
  • LIKE_（单个任意字符）
    • 列名 LIKE ‘张_’
  • LIKE%（任意长度的任意字符）
    • 列名 LIKE ‘张%’
  • 注意：模糊查询只能和LIKE关键字结合使用
#查询名字以"L"开头的员工信息（编号、名字、薪资、部门编号）
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE first_name LIKE 'L%';


#查询名字以"L"开头并且长度为4的员工信息（编号、名字、薪资、部门编号）
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE first_name LIKE 'L___';
2.4.8分支结构查询
CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END
  • 注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。
  • 经验：类似Java中的switch
#查询员工信息（编号、名字、薪资、薪资级别<对应条件表达式生成>）
SELECT employee_id,first_name,salary,department_id,
	CASE
		WHEN salary>=10000 THEN 'A'
		WHEN salary>=8000 AND salary<10000 THEN 'B'
		WHEN salary>=6000 AND salary<8000 THEN 'C'
		WHEN salary>=4000 AND salary<6000 THEN 'D'
	ELSE 'E'
		END as 'LEVEL'
FROM t_employees;
2.5时间查询

语法：SELECT 时间函数([参数列表])

  • 经验：执行时间函数查询，会自动生成一张虚表（一行一列）
时间函数描述
SYSDATE()当前系统时间(日、月、年、时、分、秒)
CURDATE()获取当前日期
CRUTIME()获取当前时间
WEEK(DATE)获取指定日期为一年中的第几周
YEAR(DATE)获取指定日期的年份
HOUR(TIME)获取指定时间的小时值
MINUTE(TIME)获取时间的分钟值
DATEDIFF(DATE1,DATE2)获取DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N)计算DATE加上N天后的日期
2.5.1获取当前系统时间
#查询当前时间
SELECT SYSDATE();

#查询当前时间
SELECT NOW();

#获取当前日期
SELECT CURDATE();

#获取当前时间
SELECT CURTIME();
2.6字符串查询

语法：SELECT 字符串函数([参数列表])

字符串函数说明
CONCAT(str1,str2,str…)将多个字符串连接
INSERT(str,pos,len,newStr)将str中指定pos位置开始len长度的内容替换为newStr
LOWER(str)将指定字符串转换为小写
UPPER(str)将指定字符串转换为大写
SUBSTRING(str,num,len)将str字符串指定num位置开始截取len个内容
2.6.1字符串应用
#拼接内容
SELECT CONCAT('My','S','QL');

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MySQL');#结果为这是MySQL数据库

#指定内容转换为小写
SELECT LOWER('mysql');#mysql

#指定内容转换为大写
SELECT UPPER('mysql');#MYSQL

#指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL
2.7聚合函数

语法：SELECT 聚合函数(列名) FROM 表名;

  • 经验：对多条数据的单列进行统计，返回统计后的一行结果、
聚合函数说明
SUM()求所有行中单列结果的总和
AVG()平均值
MAX()最大值
MIN()最小值
COUNT()求总行数
2.7.1单列总和
#统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees;
2.7.2单列平均值
#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;
2.7.3单列最大值
#统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees;
2.7.4单列最小值
#统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees;
2.7.5总行数
#统计员工总数
SELECT COUNT(*)
FROM t_employees;

#统计有提成的员工人数
SELECT COUNT(commission_pct)
FROM t_employees;
  • 注意：聚合函数自动忽略null值，不进行统计。
2.8分组查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列）；

关键字说明
GROUP BY分组依据，必须在WHERE之后生效
2.8.1查询各部门的总人数
#思路：
#1.按照部门编号进行分组（分组依据时department_id）
#2.再针对各部门的人数进行统计（count）
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
2.8.2查询各部门的平均工资
#思路：
#1.按照部门编号进行分组（分组依据department_id）。
#2.针对各个部门进行平均工资统计（avg）。
SELECT department_id,AVG(salary)
FROM t_employees
GROUP BY department_id
2.8.3查询各个部门、各个岗位的人数
#思路：
#1.按照部门编号进行分组（分组依据department_id）。
#2.按照岗位名称进行分组（分组依据job_id）。
#3.针对每个部门中的各个岗位进行人数统计（count）
SELECT department_id,job_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id,job_id;
2.8.4常见问题
#查询各个部门id、总人数、first_name
SELECT department_id,COUNT(*),first_name
FROM t_employees
GROUP BY department_id;#error
  • 注：分组查询中，select现实的列只能是分组依据列，或者聚合函数列，不能出现其他列。
2.9分组过滤查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

关键字说明
HAVING过滤规则过滤规则定义对分组后的数据进行过滤
2.9.1统计部门的最高工资
#统计60、70、90部门的最高工资
#思路：
#1）确定分组依据（department_id）
#2) 对分组后的数据处理，过滤出部门编号是60、70、90信息
#3)max()函数处理

SELECT department_id,MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)

#group确定分组依据department_id
#having过滤出60 70 90部门
#select查看部门编号和max函数
2.10限定查询

SELECT 列名 FROM 表名 LIMIT 起始行，查询行数

关键字说明
LIMIT offset_start,row_count限定查询结果的起始行和总行数
2.10.1查询前5行记录
#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;
  • 注意：起始行是从0开始的，代表了第一行。第二个参数代表的是从指定行开始查询几行
2.10.2查询范围记录
#查询表中从第四条开始，查询10行
SELECT * FROM t_employees LIMIT 3,10;
2.10.3LIMIT典型应用

分页查询：一页显示10条，一共查询三页

#思路：第一页是从0开始，显示10条
SELECT * FROM t_employees LIMIT 0,10;

#第二页是从第10条开始，显示10条
SELECT * FROM t_employees LIMIT 10,10;

#第三页是从20条开始，显示10条
SELECT * FROM LIMIT 20,10;
  • 经验：再分页应用场景中，起始行是变化的，但是一页显示的条数是不变的
2.11查询总结
2.11.1SQL语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 (ASC|DESC) LIMIT 起始行,总条数

2.11.2SQL语句执行顺序
1.FROM:指定数据来源表
2.WHERE:对查询数据做第一次过滤
3.GROUP BY:分组
4.HAVING:对分组之后的数据第二次过滤
5.SELECT:查询各字段的值
6.ORDER BY:排序
7.LIMIT:限定查询结果
2.12子查询（作为条件判断）

SELECT 列名 FROM 表名 WHERE 条件（子查询结果）

2.12.1查询工资大于Bruce的员工信息
#1.先查询到Bruce的工资（一行一列）
SELECT salary FROM t_employees WHERE first_name = 'Bruce';#工资是6000

#2.查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE salary > 6000;

#3.将1、2两条语句整合
SELECT * FROM t_employees WHERE salary > (SELECT salary FROM t_employees WHERE first_name = 'Bruce');
  • 注意：将子查询“一行一列”的结果作为外部查询的条件，做第二次查询
  • 子查询得道一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断
2.13子查询（作为枚举查询条件）

SELECT 列名 FROM 表名 WHERE 列名 IN(子查询结果);

2.13.1查询与名为’King’同一部门的员工信息
#思路：
#1.先查询‘King’所在部门编号（多行单列）
SELECT department_id
FROM t_employees
WHERE last_name='King';//部门编号：80、90

#2.在查询80、90号部门的员工信息
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE department_id in(80,90);

#3.SQL:合并
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE department_id in(SELECT department_id FROM t_employees WHERE last_name='King');#N行一列
  • 将子查询“多行一列”的结果作为外部查询的枚举查询条件，做第二次查询
2.13.2工资高于60部门所有人的信息
#1.查询60部门所有人的工资（多行多列）
SELECT salary FROM t_employees WHERE department_id = 60;

#2.查询高于60部门所有人的工资的员工信息（高于所有）
SELECT * FROM t_employees WHERE salary > ALL(SELECT salary FROM t_employees WHERE department_id = 60);

#3.查询高于60部门的工资的员工信息(高于部分)
SELECT * FROM t_employees WHERE salary > ANY(SELECT salary FROM t_employees WHERE department_id = 60);
  • 注意：当子查询结果集形式为多行单列时可以使用ANY或ALL关键字
7.14 子查询（作为一张表）

SELECT 列名 FROM (子查询的结果集) WHERE 条件;

7.14.1查询员工表中工资排名前5名的员工信息
#思路：
#1.先对所有员工的薪资进行排序（排序后的临时表）
SELECT employee_id,first_name,salary
FROM t_employees
ORDER BY salary DESC;

#2.再查询临时表中前5行员工信息
SELECT employee_id,first_name,salary
FROM (临时表)
LIMIT 0,5;

#SQL：合并
SELECT employee_id,first_name,salary
FROM(SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary DESC) AS temp
LIMIT 0,5;
  • 将 子查询“多行多列”的结果作为外部查询的一张表，做第二次查询。
  • 注意：子查询作为临时表，为其赋予一个临时表名。
2.15合并查询
  • SELECT * FROM 表名1 UNION SELECT * FROM 表名2
  • SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
2.15.1合并两张表的结果（去除重复记录）
#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
  • 注意：合并结果的两张表，列数必须相同，列的数据类型可以不同
2.15.2合并两张表的结果（保留重复记录）
#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  • 经验：使用UNION合并结果集，会去除两张表中重复的数据
2.16表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

2.16.1内连接查询（INNER JOIN ON）
#1.查询所有部门的员工信息（不包括没有部门的员工）SQL标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.job_id = t_jobs.job_id;

#2.查询所有有部门的员工信息（不包括没有部门的员工）MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.job_id = t_jobs.job_id;
  • 经验：再MySQL中，第二种方式也可以作为内连接查询，但是不符合SQL标准
  • 而第一种属于SQL标准，与其他关系型数据库通用
2.16.2三表连接查询
#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e
INNER JOIN t_departments d
ON e.department_id = d.department_id
INNER JOIN t_location l
ON d.location_id = l.location_id;
2.16.3左外连接（LEFT JOIN ON）
#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中，部门名称以NULL填充）
SELECT e_employee_id,e.first_name,e.salary,d.department_name FROM t_employees e
LEFT JOIN t_departments d
ON e.department_id = d.department_id;
  • 注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果
  • 匹配不到，则返回NULL值填充
2.16.4右外连接（RIGHT JOIN ON）
#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL填充）
SELECT e.employee_id,e.first_name,e.salary,d.department_name FROM t_employees e
RIGHT JOIN t_department d
ON e.department_id = d.department;
  • 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果
  • 匹配不到，则返回NULL值填充

3.DML操作

3.1新增（INSERT）

INSERT INTO 表名(列1，列2，列3…)VALUES(值1，值2，值3…)

3.1.1添加一条信息
#添加一条工作岗位信息
INSERT INTO t_jobs(job_id,job_title,min_salary,max_salary)VALUES('JAVA_Le','JAVA_Lecturer',2500,9000);

#添加一条员工信息
INSERT INTO `t_employees` (employee_id,first_name,last_name,email,phone_number,hire_date,job_id,job_id,salary,commission_pct,manager_id,department_id)
VALUES('194','samuel','mccain','smccain','650.501.3876','1998-07-01','sh_clerk','3200',NULL,'123','50')
  • 注意：表名后的列名和VALUES里的值要一一对应（个数、顺序、类型）
3.2修改（UPDATE）

UPDATE 表名 SET 列1=新值1，列2=新值2，…WHERE 条件;

3.2.1修改一条信息
#修改编号为100的员工的工资为25000
UPDATE t_employees SET salary = 25000 WHERE employee_id = '100';

#修改编号为135的员工信息工位编号为ST_MAN，工资为3500
UPDATE t_employees SET job_id=st_man,salary=3500 WHERE employee_id='135';
  • 注意：SET后多个列名=值，绝大多数情况下都要加WHERE条件，指定修改，否则为整表更新
3.3删除（DELETE）

DELETE FROM 表名 WHERE 条件;

3.3.1删除一条信息
#删除编号为135的员工
DELETE FROM t_employees WHERE employee_id='135';

#删除姓Peter，并且名为Hall的员工
DELETE FROM t_employees WHERE first_name='Peter' AND last_name='Hall';
  • 注意：删除时，如若不加WHERE条件，删除的是整张表的数据
3.4清空整张表数据（TRUNCATE）

TURNCATE TABLE 表名;

3.4.1清空整张表
#清空t_countries整张表
TRUNCATE TABLE t_countries;
  • 注意：与DELETE不加WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表

4.数据表操作

4.1数据表类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。对于我们约束数据的类型有很大的帮助

4.1.1数值类型
类型大小范围(有符号)范围(无符号)用途
INT4字节(-2147483648，2147483647)(0,4294967295)大整数
DOUBLE8字节(-1.797E+308，-2.22E-308)(0,2.22E-308,1.797E+308)双精度浮点数值
DOUBLE(M,D)8个字节，M表示长度，D表示小数位数同上，受M和D的约束DOUBLE(5,2)-999.99-999.99同上，受M和D的约束双精度浮点数值
DECIMAL(M,D)DECIMAL(M,D)依赖于M和D的值，M最大值为65依赖于M和D的值，M最大值为65小数值
4.1.2日期类型
类型大小范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间为第2147483647秒北京时间2038-1-19 11:14:07，格林尼治时间2038年1月19日凌晨03:14:07YYYYMMDDHHMMSS混合日期和时间值，时间戳
4.1.3字符串类型
类型大小用途
CHAR0-255字节定长字符串char(10)10个字符
VARCHAR0-65535字节变长字符串varchar(10)10个字符
BLOB0-65535字节二进制形式的长文本数据
TEXT0-65535字节长文本数据
  • CHAR和VARCHAR类型相似，但它们保存和检索的方式不同。他们最大长度和是否尾部空格被保留等方面也不同。再存储或检索过程中不进行大小写转换。
  • BLOB是一个二进制大对象，可以容纳可变数量的数据。有4中BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
4.2数据表的创建（CREATE）

CREATE TABLE 表名(

​ 列名 数据类型[约束],

​ 列名 数据类型[约束],

​ …

​ 列名 数据类型[约束]//最后一列的末尾不加逗号

)[charset=utf8];//可根据需要指定表的字符编码集

4.2.1创建表
列名数据类型说明
subjectINT课程编号
subjectNameVARCHAR(20)课程名称
subjectHoursINT课程时长
#依据上述表格创建数据表，并向表中插入3条测试语句
CREATE TABLE subject(
	subjectId INT,
    subjecName VARCHAR(20),
    subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours)VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours)VALUES(2,'MYSQL',20);
INSERT INTO subject(subjectId,subjectName,subjectHours)VALUES(3,'JavaScript',30);
4.3数据表的修改（ALTER）

ALTER TABLE 表名 操作;

4.3.1向现有表中添加列
#在课程表基础上添加gradeId列
ALTER TABLE subject ADD gradeId INT;
4.3.2修改表中的列
#修改课程表中课程名称长度为10个字符
ALTER TABLE subject MODIFY subjectName VARCHAR(10);
  • 注意：修改表中的某列时，也要写全列的名字，数据类型，约束
4.3.3删除表中的列
#删除课程表中gradeId列
ALTER TABLE subject DROP gradeId;
  • 注意：删除列时，每次只能删除一列
4.3.4修改列名
#修改课程表中subjectHours列为classHours
ALTER TABLE subject CHANGE subjectHours classHours INT;
  • 注意：修改列名时，在给定列新名称时，要指定列的类型和约束
4.3.5 修改表名
#修改课程表的subject为sub
ALTER TABLE subject RENAME sub;
4.3数据表的删除（DROP）

DROP TABLE 表名

4.4.1删除学生表
#删除学生表
DROP TABLE subject；

5.约束

  • 问题：在往已创建表中新增数据时，可不可以新增两行相同列值的数据？
  • 如果可行，会有什么弊端？
5.1实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复、实体唯一。

5.1.1主键约束

PRIMARY KEY唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL

#为表中适用主键的列添加主键约束
CREATE TABLE subject(
	subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为NULL
    subjectName VARCHAR(20),
    subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);#error主键1已存在
5.1.2唯一约束

UNIQUE 唯一，标识表中的一行数据，不可重复，可以为NULL

#为表中列值不允许重复的列添加唯一约束
CREATE TABLE subject(
	subjectId INT PRIMARY KEY,
    subjectName VARCHAR(20) UNIQUE,#课程名称唯一！
    subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(2,'Java',40);#error课程名称已存在
5.1.3自动增长列

AUTO_INCREMENT自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键配合。

#为比哦中主键列添加自动增长，避免忘记主键ID序号
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长，会从1开始根据添加数据的顺序依次加1
    subjectName VARCHAR(20) UNIQUE,
    subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES('Java',40);#课程编号自动从1增长
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES('Java',30);#第二条编号为2
5.2域完整性约束

限制列的单元格的数据正确性。

5.2.1非空约束

NOT NULL 非空，此列必须有值。

#课程名称虽然添加了唯一约束，但是有NULL值存在的可能，要避免课程名称为NULL
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREAMENT,
    subjectName VARCHAR(20) UNIQUE NOT NULL,
    subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectName,subjectHours) VALUES(NULL,40);#error,课程名称约束了非空
5.2.2默认值约束

DEFAULT 值 为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定默认值进行填充。

#当存储课程信息时，若课程时长没有指定值，则以默认课时20填充
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(20) UNIQUE NOT NULL,
    subjectHours INT DEFAULT 20
)charset=utf8;

INSERT INTO subject(subjectName,subjectHours) VALUES('Java',DEFAULT);#课程时长以默认值20填充
5.2.3引用完整性约束
  • 语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
  • 详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。
#创建专业表
CREATE TABLE speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
    specialName VARCHAR(20) UNIQUE NOT NULL
)charset=utf8;

#创建课程表（课程表的specialId引用专业表的id）
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(20) UNIQUE NOT NULL,
    subjectHours INT NOT NULL,
    CONSTRAINT fk_subject_specialId FOREIGN KEY(specialId) REFERENCES speciality(id)#引用专业表里的id作为外键，新增课程
)charset=utf8;

#专业表新增数据
INSERT INTO speciality(specialName) VALUES('Java');
INSERT INTO speciality(specialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',30,1);#专业id为1，引用的是专业表的Java
INSERT INTO subject(subjectName,subjectHours) VALUES('C#MVC',10,2);#专业id为2，引用的是专业表的C#
  • 注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表），在删除主表（被引用表）
5.3约束创建整合

创建带有约束的表

5.3.1创建表
列名数据类型约束说明
GradeINT主键、自动增长班级编号
GradeNameVARCHAR(20)唯一、非空班级名称
CREATE TABLE Grade(
	GradeId INT PRIMARY KEY AUTO_INCREAMENT,
    GradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=UTF8;
列名数据类型约束说明
student_idVARCHAR(50)主键学号
student_nameVARCHAR(50)非空姓名
sexCHAR(2)默认填充’男’性别
borndateDATE非空生日
phoneVARCHAR(11)无电话
GradeIdINT非空，外键约束：引用班级表的gradeid班级编号
CREATE TABLE student(
	student_id VARCHAR(50) PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    sex CHAR(2) DEFAULT '男',
    borndate DATE NOT NULL,
    phone VARCHAR(11),
    gradeId INT NOT NULL,
    CONSTRAINT fk_student_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(GradeId)#引用Grade表的GradeId列的值作为外键，插入时约束学生的班级编号必须存在。
);
  • 注意：创建关系表时，一定要先创建主表，再创建从表
  • 删除关系表时，先删除从表，再删除主表。

6.事务

6.1模拟转账

生活当中转账是转账方账户扣钱，收账方账户加钱。我们用数据库操作来模拟实现转账。

6.1.1数据库模拟转账
#A账户转账给B账户1000元
#A账户减1000元
UPDATE account SET money = money - 1000 WHERE id = 1;

#B账户加1000元
UPDATE account SET money = money + 1000 WHERE id = 2;
  • 上述代码完成了两个账户之间转账的操作。
6.1.2模拟转账错误
#A账户转账给B账户1000元。
#A账户减1000元
UPDATE account SET money = money - 1000 WHERE id = 1;
#断电、异常、出错...

#B账户加1000元
UPDATE account SET money = money - 1000 WHERE id = 2;
  • 上述代码再减操作后过程中出现了异常或价钱语句出错，会发现，减钱仍旧是成功的，而加钱失败了！
  • 注意：每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。
6.2事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL执行失败，整个事务都执行失败。

6.3事务的边界
  • 开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始
  • 结束：
    • 1)提交：
      • a.显式提交：commit;
      • b.隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）；
      • a.显式回滚：rollback；
      • b.隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。
6.4事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改查语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则 无论因为哪种原因失败，整个事务将回滚（rollback）。

6.5事务的特性
  • Atomicity（原子性）
    • 表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
  • Consistency（一致性）
    • 表示一个事务内有一个操作失败时，所有的更改过的数据都回滚到修改前的状态
  • Isolation（隔离性）
    • 事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
  • Durability（持久性）
    • 持久性事务完成之后，它对于系统的影响是永久性的。
6.6事务应用

应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚。

6.6.1事务完成转账
#A账户给B账户转账
#1.开启事务
START TRANSACTION;|setAutoCommit;#禁止自动提交setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE account SET money = money - 1000 WHERE id = 1;
UPDATE account SET money = money + 1000 WHERE id = 2;
#3.事务内语句都成功了，执行COMMIT；
COMMIT;
#4.事务内如果出现错误，执行ROLLBACK;
ROLLBACK;
  • 注意：开启事务后，执行的语句均属于当前事务，成功再执行COMMIT，失败要执行ROLLBACK

7.权限管理

7.1创建用户

CREATE USER 用户名 IDENTIFIED BY 密码

7.1.1创建一个用户
#创建一个zhangsan用户
CREATE USER `zhangsan` IDENTIFIED BY '123';
7.2授权
7.2.1用户授权
#将companyDB下的所有表的权限都赋给zhangsan
GRANT ALL ON companyDB.* TO `zhangsan`;
7.3撤销权限

REVOKE ALL ON 数据库.表名 FROM 用户名；

  • 注意：撤销权限后，账户要重新连接客户端才会生效
7.3.1撤销用户权限
#将zhangsan的companyDB权限撤销
REVOKE ALL ON companyDB.* FROM `zhangsan`;
7.4删除用户

DROP USER 用户名

7.4.1删除用户
#删除用户zhangsan
DROP USER `zhangsan`;

8.视图

8.1概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

8.2视图特点
  • 优点
    • 简单化，数据所见即所得。
    • 安全性，用户只能查询或修改他们所能见得到的数据。
    • 逻辑独立性，可以屏蔽这是表结构变化带来的影响。
  • 缺点
    • 性能先对较差，简单的查询也会变得稍显复杂。
    • 修改不方便，特别是复杂的聚合视图基本无法修改。
8.3视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句；

8.3.1创建视图
#查询t_empInfo视图中编号为101的员工信息
SELECT * FROM t_empInfo WHERE employee_id = '101';
8.4视图的修改
  • 方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
  • 方式二：ALTER VIEW 视图名 AS 查询语句
8.4.1修改视图
#方式1：如果视图存在则进行修改，反之，进行创建
CREATE OR REPLACE VIEW t_empInfo
AS
SELECT employee_id,first_name,last_name,email,salary,department_id FROM t_employees;

#方式2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS
SELECT employee_id,first_name,last_name,email,salary FROM t_employees;
8.5视图的删除

DROP VIEW 视图名

8.5.1删除视图
#删除t_empInfo视图
DROP VIEW t_empInfo;
  • 注意：删除视图不会影响原表
8.6视图的注意事项
  • 注意：
    • 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询功能。
    • 如果视图包含以下结构中的一种，则视图不可更新
      • 聚合函数的结果
      • DISTINCT去重后的结果
      • GROUP BY分组后的结果
      • HAVING筛选过滤后的结果
      • UNION、UNION ALL联合后的结果

9.SQL语言分类

  • 数据查询语言DQL(Data Query Language):select、where、order by、group by、having。
  • 数据定义语言DDL(Data Definition Language)：create、alter、drop。
  • 数据操作语言DML(Data Manipulation Language):insert、update、delete。
  • 数据控制语言DCL(Data Control Language):grant、revoke。
  • 事务处理语言TPL(Transaction Process Language):commit、rollback。
周景洛
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL系列专题(2)-MySQL的SQL语句和高级特性
2401_84008929的博客
04-19 1004
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数看完美团、字节、腾讯这三家的面试问题，是不是感觉问的特别多，可能咱们又得开启面试造火箭、工作拧螺丝的模式去准备下一次的面试了。开篇有提及我可是足足背下了1000道题目，多少还是有点用的呢，我看了下，上面这些问题大部分都能从我背的题里找到的，所以今天给大家分享一下互联网工程师必备的面试1000题。
数据库和数据表管理
Sneerface的博客
09-07 515
创建数据库和创建表 ----------------------- -- 数据定义语言 -- alter create drop --------------------------- -- 创建数据库 -- create database 数据库名 create database test2 -- create database if not exists 数据库名 create database if not exists test2 -- drop database 数据库名 drop databa
SQL语句简单复习
月阴荒的博客
06-05 300
mysql创建数据库 创建数据库为create命令，语法如下create database 数据库名删除数据库为drop，语法为drop database 数据库名创建数据库表 create table table_name(column_name column_type);删除数据表 DROP TABLE table_name;插入数据表 insert into table_name(field1,field2,field3) values (value1,value2,value3);查询数据 sele
数据库实验四 查询语句
Piink的博客
04-09 7792
#1.查询每个员工的所有数据 USE yggl; #查询departments和salary SELECT * FROM departments; SELECT *FROM salary; #2.查询每个员工的姓名、地址和电话 SELECT NAME,Address,phonenumber FROM employees; #查询departments表中的部门名称 SELECT departmentname FROM departments; #查询salary表中领取工资的人员编号 SELECT
MySQL基础入门学习笔记（二）
燕麦葡萄干的博客
03-23 1296
MySQL基础入门学习笔记（二）1、SQL、DB、DBMS分别是什么，它们之间的关系18、创建表：19、insert语句插入数据20、表的复制21、将查询结果插入到一张表中22、update修改数据23、delete删除数据24、约束：非空约束（not null）、唯一性约束(unique)、主键约束(primary key)、外键约束（foreign key）25、存储引擎（了解）26、事务的概念、事务的特性和事务的隔离级别27、索引：概念、作用、索引对象的创建和删除、索引的实现原理 1、SQL、DB、D
MySQL相关知识点资料
09-12
MySQL知识点涉及MySQL命令相关服务，SQL重要概念，创建SQL相关命令语句。
MySQL数据库 知识点归纳 考题
04-12
MySQL数据库的全套思维导图，各大高校考试必考知识点归纳，入门MySQL数据库必读。
MySQL必备的常见知识点汇总整理
09-08
MySQL是世界上最流行的关系型数据库管理...理解并熟练运用这些MySQL知识点，将有助于你在实际开发和管理数据库时更加得心应手。不断实践和深入学习，可以进一步提升你的数据库技能，应对更复杂的数据库设计和管理挑战。
数据库MySQL基础知识点1
12-09
MySQL数据类型：数值型、decimal浮点型、字符串、枚举、时间 SQL-命令-数据库操作 SQL-命令-表结构的创建 SQL-命令-表结构的修改 SQL-命令-表数据操作-查 SQL-命令-表数据操作-插 SQL-命令-表数据操作-改 ...
MySQL面试知识点.docx
11-02
MySQL 是一个广泛使用的关系型数据库管理系统，本文总结了 MySQL 面试中常见的知识点，涵盖了 MySQL 优化、MySQL 引擎、索引、锁等方面的内容，旨在帮助开发者更好地理解和掌握 MySQL 的相关知识。 一、MySQL 优化 ...
MySQL 高级语句（一）
最新发布
fantuan_sss的博客
03-26 913
ASC 是按照升序进行排序的，是默认的排序方式，即可以省略；SELECT语句中如果没有指定具体的排序方式，则默认按ASC方式进行排序。DESC是按降序方式进行排列。当然ORDER BY前面也可以使用 WHFRE 子句对查询结果进一步过滤。
数据库查询语句sql
goodgril_daling的博客
04-06 4958
                            数据库查询语句sqlSELECT * FROM emp;SELECT * FROM dept;--数据库语句中分号是分隔符，系统遇到分号才会执行语句--单行注释/*多行注释*//*select 列1，列2 ，……列n    --定义查询数据所在的列from 表1，表2 ，……表n     --定义查询数据所在的表where 限定条件1 and...
mysql 单表查询练习
on_the_way_to_travel的专栏
05-13 1953
原文地址sql语句练习的答案统一放在在文章底部employees表结构如下： 在 employees表中 使用别名 “First Name”, “Last Name” 展示 (first_name, last_name)字段。 查询结果如下： 在employees表中查询单列 department_id 查询结果如下： 在employees表中，查询所有雇员的详细信息并使用first nam
Oracle数据库 常见的SQL题，复习
weixin_30381793的博客
09-26 509
01.查询员工表所有数据，并说明使用*的缺点 select * from emp 02.查询职位（JOB）为'PRESIDENT'的员工的工资 select sal from emp where job='PRESIDENT' 03.查询佣金（COMM）为0或为NULL的员工信息 select * from emp where COMM=0 or comm=null 04.查询入...
SQL五十道经典题型——（七）查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
智十七的博客
05-25 4244
查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点） 解题思路 1、分析题目 这道题 最 最 最 关键的所在，（划重点）就是：把学过编号为“01”课程的同学查询出来，再把学过编号为“02”课程的同学查询出来，然后把两张表合并到一张表中。 2、通过课程表course查询课程编号为 “01”，“02”的课程 #课程编号为01的数据 SELECT * FROM course WHERE c_id = 01; #课程编号为02的数据 SELECT * FROM c
SQL函数查询语句
Lz133153的博客
05-09 2738
目录 重要函数 实例 函数练习 重要函数 length(str):返回一个字符串的长度 concat(str1,str2)：字符串连接函数,它和||实现的功能一样。 chr():将一个ASCII码转换成字符 ascii(字符)：将一个字符转换成ASCII码值 trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格 ltrim(str,):去掉左边空格 rtrim(str):去掉右边空格 pad(str,len,'符号'):填充函数,len表示长...
MySQL查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工
qq_42681203的博客
03-05 859
实现方法： select last_name,first_name,dept_no from employees as e left join dept_emp as d on e.emp_no=d.emp_no 笔记： 在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型： INNER JOIN：如果表中有至少一个匹配，则返回行 LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行 RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行 FULL JOIN：只要..
mysql复习知识点
08-19
当然，我可以帮你复习一些MySQL的知识点。以下是一些常见的MySQL知识点： 1. 数据库和表的创建：使用CREATE DATABASE和CREATE TABLE语句创建数据库和表。 2. 插入数据：使用INSERT INTO语句向表中插入数据。 3. 查询数据：使用SELECT语句从表中检索数据。可以使用WHERE子句来过滤数据。 4. 更新数据：使用UPDATE语句更新表中的数据。同样可以使用WHERE子句来指定要更新哪些行。 5. 删除数据：使用DELETE FROM语句删除表中的数据。也可以使用WHERE子句来指定要删除哪些行。 6. 排序和限制：使用ORDER BY子句对结果进行排序，使用LIMIT子句限制返回的行数。 7. 连接表：使用JOIN语句将多个表连接在一起。常见的连接类型有INNER JOIN、LEFT JOIN和RIGHT JOIN。 8. 聚合函数：使用聚合函数如SUM、AVG、COUNT等对数据进行统计和计算。 9. 分组数据：使用GROUP BY子句将数据分组，并可以在其上应用聚合函数。 10. 数据类型：MySQL支持多种数据类型，如整数、浮点数、字符串、日期等。 这只是MySQL的一些基本知识点，还有很多其他的功能和语法可以学习。如果你有具体的问题或需要进一步了解某个方面的知识，请随时告诉我。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

周景洛

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值