当前位置： 首页 > news >正文

数据库入门实战版

news 2025/9/3 9:47:26

基础篇

数据库相关概念及SQL语句

名称简称
数据库DB
数据库管理系统DBMS
SQLSQL

SQL：操作关系型数据库的编程语言，定义了标准

安装：

1). 双击官方下的安装包文件

2). 根据安装提示进行安装（全部点继续就可以）

安装MySQL的相关组件，这个过程可能需要耗时几分钟，耐心等待。

输入MySQL中root用户的密码,一定记得记住该密码 （已设置为123456）

打开方式：

方法一：srvices.msc 任务管理器

方法二：命令行输入net start mysql80

若要停止，start改为stop

连接方式：

方法一：打开mysql前提下，打开8.0命令行输入密码即可

方法二：需要配置环境变量，然后命令行mysql -u root -p

配置MySQL环境变量方法：查看高级系统设置，Path，点，复制Mysql里面sever里面bin的地址，点确定

数据类型：

关系型数据库：由多张相互连接的二维表组成的数据库
特点：

1.使用表存储数据，格式统一，便于维护

2.使用SQL语言操作，标准统一，使用方便

非关系型数据库

SQL通用语法好处：

1.SQL语句可以单行或多行书写，以分号结尾

2.SQL语句可以使用空格/缩进，美观易懂

3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

4.注释标记：–注释内容

​ ##注释内容

​ /**/

SQL分类：

分类说明
DDL数据定义语言，用来定义数据库对象（数据库，表，字段）
DML数据操作语言，用来对数据库表中的数据进行增删改
DQL数据查询语言，用来查询数据库中表的记录
DCL数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL的数据库和表的增删改查：

查询：

查询所有数据库： SHOW DATABASES;

查询当前数据库：SELECT DATABASE();

查询当前数据库所有表：SHOW TABLES;（前提是use进入到这个表）

查询表结构：DESC 表名；(就是看tb_user里面有哪些字段）

查询指定表的建表语句：SHOW CREATE TABLE 表名；（就是具体 name，gender，age分别的解释）

创建：

CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集] [COLLATE 排序规则]； （字符集可以省略）

CREATE TABLE 表名（

​ 字段1 字段1类型[COMMENT 字段1注释]，

​ 字段1 字段2类型[COMMENT 字段2注释]，

​ 字段1 字段3类型[COMMENT 字段3注释]，

​ 字段4 字段1类型[COMMENT 字段4注释]

)[COMMENT 表注释]；

删除：

DROP DATABASE[IF EXISTS]数据库名；

使用：

USE 数据库名；

表操作
1.添加

添加字段： ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束]；

2.修改

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）；

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束]；

修改表名：

ALTER TABLE 表名 RENAME TO 新表名；

3.删除

删除字段：ALTER TABLE 表名 DROP 字段名；

删除表：DROP TABLE [IF EXISTS] 表名；

删除指定表，并重新创建表：TRUNCATE TABLE 表名；

MySQL数据类型

1.数值类型
分类类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述
数值类型TINYINT1 byte(-128，127)(0，255)小整数值
数值类型SMALLINT2byte(-32768，32767)(0，65535)大整数值
数值类型MEDIUMINT3 byte(-8388608，8388607)(0，16777215)大整数值
数值类型INT或INTEGER4 byte(-2147483648，2147483647)(0，4294967295)大整数值
数值类型BIGINT8 byte(-263，263-1)(0，2^64-1)极大整数值
数值类型FLOAT4 byte(-3.402823466 E+38，3.402823466351 E+38)0 和 (1.175494351 E-38，3.402823466 E+38)单精度浮点数值
数值类型DOUBLE8 byte(-1.7976931348623157 E+308，1.7976931348623157 E+308)0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)双精度浮点数值
数值类型DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)

例如：

age TINYINT UNSIGNED
score double(4, 1)
2.字符串类型
分类类型大小描述
字符串类型CHAR0-255 bytes定长字符串
字符串类型VARCHAR0-65535 bytes变长字符串
字符串类型TINYBLOB0-255 bytes不超过255个字符的二进制数据
字符串类型TINYTEXT0-255 bytes短文本字符串
字符串类型BLOB0-65 535 bytes二进制形式的长文本数据
字符串类型TEXT0-65 535 bytes长文本数据
字符串类型MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
字符串类型MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
字符串类型LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
字符串类型LONGTEXT0-4 294 967 295 bytes极大文本数据
char(10) -----------> 性能好用户名 username varchar(50)
varchar(10) ---------> 性 能较差性别 gender char(1)
3.日期时间类型
分类类型大小范围格式描述
日期类型DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值
日期类型TIME3-838:59:59 至 838:59:59HH: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:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳
样本：
create table tb_user(id int comment '编号',name varchar(50) comment '姓名',age int comment '年龄',gender varchar(1) comment '性别')comment '用户表';

DDL-数据库操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| icast              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tb_user            |
+--------------------+
6 rows in set (0.01 sec)mysql> create  database itest;
Query OK, 1 row affected (0.03 sec)mysql> use icast;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| icast      |
+------------+
1 row in set (0.00 sec)mysql>
mysql> drop database tb_user;
Query OK, 0 rows affected (0.05 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| icast              |
| information_schema |
| itest              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)mysql> drop database icast;
Query OK, 1 row affected (0.05 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itest              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

DDL-表操作

mysql> use itest;
Database changed
mysql> show tables;
Empty set (0.00 sec)mysql> create table tb_user(->      id int comment '编号',->     name varchar(50) comment '姓名',->      age int comment '年龄',->     gender varchar(1) comment '性别'->     )comment '用户表';
Query OK, 0 rows affected (0.17 sec)mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)mysql> show create table tb_user;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (`id` int DEFAULT NULL COMMENT '编号',`name` varchar(50) DEFAULT NULL COMMENT '姓名',`age` int DEFAULT NULL COMMENT '年龄',`gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL图形化界面：

DataGrip 安装与基础使用

DML的数据的增删改

添加数据 （INSERT）

1.给指定字段添加数据

INSERT INTO表名（字段1，字段2，···）VALUES(值1，值2，···）；

2.给全部字段添加数据

INSERT INTO表名VALUES（值1，值2····）；

3.批量添加数据

INSERT INTO 表名（字段名1，字段名2，····）VALUES（值1,值2，····），（值1，值2），（值1，值2，···）；

INSERT INTO 表名 VALUES（值1，值2，···），（值1，值2），（值1，值2）；

  • 插入数据时，指定的字段顺序需要与值的顺序是一 一对应的
  • 字符串和日期型数据应该包含在引号中
  • 插入的数据大小，应该在字段的规定范围内
-- 删除已存在的表（谨慎，删表数据就没了）
DROP TABLE IF EXISTS tables; 
-- 重新创建表，包含新的出生时间字段
CREATE TABLE tables(id INT COMMENT '编号',name VARCHAR(50) COMMENT '姓名',age INT COMMENT '年龄',gender VARCHAR(1) COMMENT '性别',birth_time DATETIME COMMENT '出生时间' -- 新增出生时间字段
) COMMENT '用户表';
-- 插入数据记录：李明，男，21岁，出生于2000-01-01
INSERT INTO tables (id, name, age, gender, birth_time)
VALUES (1, '李明', 21, '男', '2000-01-01 00:00:00');insert into tables value(3,'周瑜',56,'男','2003-09-08');-- 查询验证插入结果
SELECT * FROM tables;

修改数据（UPDATE）

UPDATE 表名 SET 字段名1=值1，字段名2=值2，···[WHERE 条件]；

update tables set name ='zhouyu' where id=3;update tables set name ='yu',gender ='女' where id=3;

删除数据（DELETE）

DELETE FROM 表名 [WHERE 条件]

  • DELETE语句不能删除某一个字段的值（可以使用UPDATE）
delete from tables where gender='男';
delete from tables;

DQL的查询

DQL-语法

编写顺序

SELECT

​ 字段列表

FROM

​ 表名列表

WHERE

​ 条件列表

GROUP BY

​ 分组字段列表

HAVING

​ 分组后条件列表

ORDER BY

​ 排序字段列表

LIMIT

​ 分页参数

执行顺序

FROM

WHERE

GROUP BY

SELECT

ORDER BY

LIMIT

基本查询

1.查询多个字段

SELECT 字段1，字段2，字段3···FROM 表名；

SELECT * FROM 表名；

2.设置别名

SELECT 字段1[AS 别名1]，字段2[AS 别名2]···FROM 表名；

3.去除重复记录

SELECT DISINCT 字段列表 FROM 表名；

查询实战：
-- 如果表已存在则删除（可选，按需决定是否保留该语句）
DROP TABLE IF EXISTS emp;
-- 创建 emp 表
CREATE TABLE emp(id int comment '编号',workno varchar(10) comment '工号',name varchar(10) comment '姓名',gender char comment '性别',age tinyint unsigned comment '年龄',idcard char(18) comment '身份证号',workaddress varchar(50) comment '工作地址',entrydate date comment '入职时间'
) comment '员工表';-- 向 emp 表插入多条数据
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),(2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),(3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),(4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),(5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),(6, '6', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),(7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),(8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),(9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),(10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),(11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),(12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),(13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),(14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),(15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),(16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');
1查询指定字段name,workno,age返回
select name,workno,age from emp;
2.查询所有字段返回
select *from emp;
3.查询所有员工的工作地址，起别名
select workaddress  as '工作地址' from emp;
select workaddress  '工作地址' from emp;
select workaddress from emp;
4.查询公司员工上班地址（不要重复）
select  distinct  workaddress  '工作地址' from emp;

条件查询：

SELECT 字段列表 FROM 表名 WHERE 条件列表；

实战演练：

1.查询年龄等于88的员工

select * from emp where age=88;

2查询年龄小于20的员工信息

select * from emp where age<20;

3.查询年龄小于等于20的员工信息。

select * from emp where age<=20;

4.查询没有身份证号的员工信息。

select * from emp where idcard is null;

5.查询有身份证号的员工信息。

select * from emp where idcard is not null;

6.查询年龄不等于88的员工信息。

select * from emp where age !=88;
select * from emp where age <>88;

7查询年龄在15岁(包含)到20岁(包含)之间的员工信息。

select * from emp where age >=15&& age<=20;
select * from emp where age >=15 and age<=20;
select * from emp where age  between  15 and 20;

8.查询性别为女，而且年龄小于25岁的员工信息。

select * from emp where gender='女' and age <25;

9.查询年龄等于18或20或40的员工信息。

select * from emp where age=18 or age=20 or age=40;
select * from emp where age in(18,20,40);

10.查询姓名为两个字的员工信息。

select * from emp where name like '__';select * from emp where name like '___';

11.查询身份证号最后一位是X的员工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';(17个下划线)

聚合函数

定义：将一列数据作为一个整体，进行纵向计算

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和

语法：

SELECT 聚合函数（字段列表） FROM 表名；

NULL不参与计算，所以有差距

实战演练：

1.统计该企业员工数量。

select  count(*) from emp ;    -- 16条
select  count(id) from emp ;    -- 16条
select  count(idcard) from emp ; -- 15条

2.统计该企业员工的平均年龄。

 
select  avg(age) from emp ;

它要求参数必须是具体的数值列（如 age、salary 等），否则会报错。

  1. 为什么不能用 *？

* 代表所有列，如果使用 AVG(*)，SQL 引擎会困惑：“你想计算哪个列的平均值？id、name 还是其他列？”
此外，AVG() 只能处理数值类型（如 INT、DECIMAL），而其他列可能是字符串（如 name）、日期（如 entrydate），这些类型无法计算平均值。

3.统计该企业员工的最大年龄。

select  max(age) from emp ;

4.统计该企业员工的最小年龄。

select  min(age) from emp ;

5.估计该企业员工的年龄之和。

select  sum(age) from emp ;

6.统计西安员工年龄之和

select sum(age) from emp where workaddress ='西安'  ;

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件]GROUP BY 分组字段名 [HAVING 分组后过滤条件]；

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

实战演练：

1.根据性别分组统计男性员工和女性员工的数量。

select gender,count(*) from emp group by  gender;

2.根据性别分组统计男性员工和女性员工的平均年龄。

select gender,avg(age) from emp group by gender;

3.查询年龄小于45的员工，并根据工作地址分组获取员工数量大于等于3的工作地址。

select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3 ;
select workaddress,count(*) address_count from emp where age<45 group by workaddress having address_count>=3 ;
  • 执行顺序：where>聚合函数>having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

ASC 升序 默认

DESC 降序

多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

实战演练：

  1. 根据年龄对公司的员工进行升序排序

     select * from emp order by age asc; 

    select * from emp order by age desc; 
select * from emp order by age;根据入职时间，对员工进行降序排序

    2.根据入职时间，对员工进行降序排序

select * from emp order by entrydate desc;
  1. 根据年龄对公司的员工进行升序排序 ，年龄相同 ，再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意

  • 起始索引从0开始，起始索引 =（查询页码-1）* 每页显示记录数。

  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

实战演练

1.查询第1页员工数据，每页展示10条记录

select * from emp limit 0,10; 
select * from emp limit 10;

2.查询第二页员工数据，每页展示十条记录

select * from emp limit 10,10; ```

案例练习

1.查询年龄为20,21,22,23岁的女性员工信息。

select * from emp where gender = '女' and age in(20,21,22,23);

2.查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。

 select * from emp where gender = '男' and ( age between 20 and 40 ) and name like '___';

3.统计员工表中，年龄小于60岁的 ，男性员工和女性员工的人数。

 select gender, count(*) from emp where age < 60 group by gender;

4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name , age from emp where age <= 35 order by age asc , entrydate desc;

5.查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from emp where gender = '男' and age between 20 and 40 order by age asc , entrydate asc limit 5;

DCL

管理数据库用户，控制数据库访问权限

管理用户

1.查询用户

USE mysql；
SELECT * FROM user;

2.创建用户

CREATE USER '用户名@主机名' IDENTIFIED BY '密码';

3.修改用户密码

ALTER USER '用户名@主机名' IDENTIFIED WITH mysql_native_password BY '新密码'；

4.删除用户

 DROP USER '用户名@主机名';

实战演练：

1.创建用户itcast，只能够在当前主机localhost访问，密码123456；

create  user 'itest'@'localhost' identified  by '123456';

2.创建用户heima，可以在任意主机访问该数据库，密码123456；

create  user 'itest'@'%' identified  by '123456';

3.修改用户heima的访问密码为1234；

alter user  'itest'@'localhost' identified with mysql_native_password by '1234';

4.删除itcast@localhost用户

 drop user 'itest'@'localhost' ;

权限控制

权限说明
ALL, ALL PRIVILEGES所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表

1.查询权限

SHOW GRANTS FOR ‘用户名’@‘主机名’；

2.授予权限

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’；

3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’；

  • 多个权限之间用逗号分隔
  • 授权时，数据库名和表名都可以用*统配，代表所有

实战演练

查看权限

show grants  for 'itest'@'%' ;

授予权限

grant all on itcast.* to 'itest'@'%';

撤销权限

revoke  all on itcast.*  from  'itest'@'%';

函数

一段可以直接被另一段程序调用的程序或代码

字符串函数

函数功能
CONCAT(S1,S2,…Sn)字符串拼接，将S1，S2，… Sn拼接成一个字符串
LOWER(str)将字符串str全部转为小写
UPPER(str)将字符串str全部转为大写
LPAD(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)返回从字符串str从start位置起的len个长度的字符串
-- concat
select concat('hello',' Mysql');-- lower
select  lower('HELLO');-- upper
select  upper('hello');-- lpad
select lpad('01',5,'-');-- rpad
select rpad('01',5,'-');-- trim
select trim(' hello Mysql ');-- substring
select substring('hello Mysql',1,5);
练习

企业员工工号，统一为五位数，目前不足为五位数的全部在前面补0

update emp set workno = lpad(workno,5,'0');

数值函数

常见的数值函数如下：

函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x,y)返回x/y的模
RAND()返回0~1内的随机数
ROUND(x,y)求参数x的四舍五入的值，保留y位小数
-- ceil
select ceil(1.5);-- floor
select floor(1.5);-- mod
select mod(3,4);-- rand
select rand();-- round
select round(2.345,2);

-- 通过数据库的函数，生成一个六位数的随机验证码；
select  lpad( round(rand()*1000000,0),6,'0');

日期函数

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date, INTERVAL expr_type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
-- curdate();
select curdate();-- curtime
select curtime();-- now
select now();-- year,month,day
select YEAR(now());
select MONTH(now());
select DAY(now());-- DATA_ADD
select DATE_ADD(now(),INTERVAL 70 DAY);
select DATE_ADD(now(),INTERVAL 70 MONTH);-- DATADIFF
select DATEDIFF('2021-12-01','2021-11-01');
\-- 查询所有员工的入职天数，并根据入职天数排序倒序
select name,datediff(curdate(),entrydate) as 'enterydays' from emp;

流程函数

函数功能
IF(value, t, f)如果value为true，则返回t，否则返回f
IFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END如果val1为true，返回res1，… 否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END如果expr的值等于val1，返回res1，… 否则返回default默认值
-- if
select if(true,'ok','error');-- ifnull
select ifnull('ok','default');
select ifnull('','default');
select ifnull(null,'default');-- case when then else end
-- 需求： 查询emp表的员工姓名和工作地址（北京/上海 ----> 一线城市，其他 ----->二线城市）
selectname,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)as '工作地址'from emp;统计班级各个学员的成绩，展示的规则如下：>=85 展示优秀>=60 展示及格否则，展示不及格selectid,name,(case when math >=85 then '优秀' when math >=60 then '及格' else '不及格' end)'数学',(case when english >=85 then '优秀' when math >=60 then '及格' else '不及格' end)'英语',(case when chinese >=85 then '优秀' when math >=60 then '及格' else '不及格' end)'语文'
from score;

约束

  1. 概念: 约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  2. 目的: 保证数据库中数据的正确、有效性和完整性。
约束描述关键字
非空约束限制该字段的数据不能为nullNOT NULL
唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE
主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KEY
默认约束保存数据时，如果未指定该字段的值，则采用默认值DEFAULT
检查约束(8.0.16版本之后)保证字段值满足某一个条件CHECK
外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性FOREIGN KEY

约束是作用于表中字段上的，可以在创建/修改表的时候添加约束

实战演练

字段名字段含义字段类型约束条件
idID唯一标识int主键，并且自动增长 PRIMARY KEY，AUTO_INCREMENT
name姓名varchar(10)不为空，并且唯一 NOT NULL,UNIQUE
age年龄int大于0，并且小于等于120 CHECK
status状态char(1)如果没有指定该值，默认为1 DEFAULT
gender性别char(1)无
create table user(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment  '姓名',age int check ( age>0 && age<=120 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
)comment '用户表';insert into user(name, age, status, gender) values ('tom3','22','1','男');insert into user(name, age, status, gender) values ('tom4','22','1','男');insert into user(name, age, status, gender) values ('tom4','-12','1','男');insert into user(name, age,  gender) values ('tom6','120','男');

外键约束

添加外键

CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表名(主表列名)
);

ALTER TABLE 表名
ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名)
REFERENCES 主表名(主表列名);

外键起名：fk_从表名_主表名_字段名

-- 创建部门表
CREATE TABLE dept(id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';-- 插入部门数据
INSERT INTO dept (id, name) VALUES(1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部'),(5, '总经办');-- 创建员工表
CREATE TABLE emp(id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,name VARCHAR(50) NOT NULL COMMENT '姓名',age INT COMMENT '年龄',job VARCHAR(20) COMMENT '职位',salary INT COMMENT '薪资',entrydate DATE COMMENT '入职时间',managerial INT COMMENT '直属领导ID',dept_id INT COMMENT '部门ID'
) COMMENT '员工表';INSERT INTO emp (id, name, age, job, salary, entrydate, managerial, dept_id) VALUES(1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),(3, '杨过', 33, '开发工程师', 8400, '2000-11-03', 2, 1),(4, '黄蓉', 48, '开发工程师', 11000, '2002-02-05', 2, 1),(5, '小龙女', 43, '测试工程师', 10500, '2004-09-07', 3, 1),(6, '郭靖', 19, '实习生', 6600, '2004-10-12', 2, 1);alter table emp add constraint fk_emp_dept_id foreign key  (dept_id) references dept(id);ALTER TABLE emp DROP FOREIGN KEY  fk_emp_dept_id;

删除外键

行为说明
NO ACTION当在父表中删除/更新对应记录时，检查该记录是否有对应外键，有则不允许操作（与RESTRICT一致）
RESTRICT当在父表中删除/更新对应记录时，检查该记录是否有对应外键，有则不允许操作（与NO ACTION一致）
CASCADE当在父表中删除/更新记录时，同步删除/更新子表中关联的记录
SET NULL当在父表中删除记录时，将子表中关联的外键值设为null（需外键允许为null）
SET DEFAULT父表变更时，子表外键列设为默认值（Innodb引擎不支持）

ALTER TABLE 表名
ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段)
REFERENCES 主表名(主表字段名)
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_id
FOREIGN KEY (dept_id)
REFERENCES dept(id)
ON UPDATE set null
ON DELETE set null ;

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称 和

ALTER TABLE 表名
ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段)
REFERENCES 主表名(主表字段名)
ON UPDATE CASCADE
ON DELETE CASCADE;

区别

  • 从 emp 表中删除名为 fk_emp_dept_id 的外键约束
  • 仅移除约束关系，不会删除任何数据
  • 执行后，emp.dept_id 字段仍然存在，但不再强制关联 dept.id
  1. 建立约束：强制 外键字段 的值必须存在于 主表字段名 中
  2. 级联行为：
    • ON UPDATE CASCADE：若主表的主键值更新，自动同步更新子表的外键值
    • ON DELETE CASCADE：若主表记录被删除，自动删除子表关联记录

事务

create table account(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',money int comment '余额'
) comment '账户表';insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);

1,张三,2000
2,李四,2000

张三给李四转2000

1.查询张三余额

select * from account where name =‘张三’;

2.将张三余额减1000

update account set money = money - 1000 where name ='张三';

3.李四余额加一千

– ---------------------------- 事务操作 ----------------------------

– 数据准备
create table account(
id int auto_increment primary key comment ‘主键ID’,
name varchar(10) comment ‘姓名’,
money int comment ‘余额’
) comment ‘账户表’;
insert into account(id, name, money) VALUES (null,‘张三’,2000),(null,‘李四’,2000);

– 恢复数据
update account set money = 2000 where name = ‘张三’ or name = ‘李四’;

查看/设置事务提交方式
select @@autocommit;

set @@autocommit = 0; – 设置为手动提交

– 转账操作 (张三给李四转账1000)
– 1. 查询张三账户余额
select * from account where name = ‘张三’;

– 2. 将张三账户余额-1000
update account set money = money - 1000 where name = ‘张三’;

程序执行报错 …

– 3. 将李四账户余额+1000
update account set money = money + 1000 where name = ‘李四’;

– 提交事务
commit;

– 回滚事务

rollback ;

– 方式二
– 转账操作 (张三给李四转账1000)
start transaction ;

或者

begin；

-- 1. 查询张三账户余额
select * from account where name = '张三';-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';程序执行报错 ...-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

– 提交事务
commit;

– 回滚事务
rollback;

– 查看事务隔离级别
select @@transaction_isolation;

– 设置事务隔离级别
set session transaction isolation level read uncommitted ;

set session transaction isolation level repeatable read ;

区别

1. select @@autocommit;
  • 作用：查询当前会话的自动提交模式状态
  • 返回值：1（开启）或 0（关闭）
  • 示例结果：如果是默认设置，通常返回 1
2. set @@autocommit = 0;
  • 作用：关闭当前会话的自动提交模式（这才是设置 autocommit 的正确语法）
  • 效果：执行后需要手动执行 commit 或 rollback 来提交或回滚事务
  • 持久性：只影响当前会话，不影响其他连接
3. commit;
  • 作用：手动提交当前事务
  • 使用场景：当 autocommit=0 时，需要显式执行 commit 才能使更改永久生效
  • 关联性：与 autocommit 设置密切相关

事务四大特性

  1. 原子性（Atomicity）
    事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

  2. 一致性（Consistency）
    事务完成时，必须使所有的数据都保持一致状态。

  3. 隔离性（Isolation）
    数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

  4. 持久性（Durability）
    事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

问题描述
脏读一个事务读到另外一个事务还没有提交的数据。
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影"。

事务隔离级别

隔离级别赃读不可重复读幻读
Read uncommitted√√√
Read committed×√√
Repeatable Read (默认)××√
Serializable×××
一、查看事务隔离级别  
SELECT @@TRANSACTION_ISOLATION  二、设置事务隔离级别  
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

多表查询

多表关系

  1. 一对多

    一个部门有很多员工

实现：在多的一方建立外键，指向一的一方的主键

2.多对多

学生和课程

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

3.一对一

用户与用户详情的关系

一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的

INSERT INTO emp (id, name, age, position, salary, hire_date, manager_id, dept_id) VALUES
(1, '金庸', 66, '总裁', 20000.00, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500.00, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400.00, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000.00, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500.00, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600.00, '2004-10-12', 2, 1),
(7, '灭绝', 60, '财务总监', 8500.00, '2002-09-12', 1, 3),
(8, '周芷若', 19, '会计', 48000.00, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出纳', 5250.00, '2009-05-13', 7, 3),
(10, '赵敏', 20, '市场部总监', 12500.00, '2004-10-12', 1, 2),
(11, '鹿枝客', 56, '职员', 3750.00, '2006-10-03', 10, 2),
(12, '鹤笔翁', 19, '职员', 3750.00, '2007-05-09', 10, 2),
(13, '方东白', 19, '职员', 5500.00, '2009-02-12', 10, 2),
(14, '张三丰', 88, '销售总监', 14000.00, '2004-10-12', 1, 4),
(15, '俞莲舟', 38, '销售', 4600.00, '2004-10-12', 14, 4),
(16, '宋远桥', 40, '销售', 4600.00, '2004-10-12', 14, 4),
(17, '陈友谅', 42, NULL, 2000.00, '2011-10-12', 1, NULL);

select *
from emp,dept where emp.dept_id =dept.id;

多表查询分类

连接查询

  • 内连接：相当于查询A、B交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

内连接

## 隐式内连接 
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

内连接查询的是两张表交集的部分

查询每一个员工的姓名，及关联的部门的名称

显式

select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

隐式

select emp.name,dept.name
from emp,dept where emp.dept_id =dept.id;select e.name,d.name
from emp e,dept d where e.dept_id =d.id;

外连接

左外连接

SELECT 字段列表 from 表1 LEFT OUTER JOIN 表二 on 条件;

相当于查询表1(左表)的所有数据包含表一和表二交集部分的数据

右外连接

SELECT 字段列表 from 表1 RIGHT OUTER JOIN 表二 on 条件;

相当于查询表1(左表)的所有数据包含表一和表二交集部分的数据

左外连接

select e.*,d.name from emp e left join dept d on d.id = e.dept_id;

右外连接

select d.*,e.* from emp e right join dept d on d.id = e.dept_id;
select d.*,e.* from dept d left join emp e on e.dept_id=d.id;

自连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件;

自连接查询，既可以是内连接查询，也可以是外连接查询

-- 1.查询员工 及其所属领导的名字(内连接，查询交集）
select a.name,b.name
from emp a,emp b where a.manager_id=b.id ;-- 2.查询所有员工emp及其领导名字emp，·如果员工没有领导，也要查询出来（外连接）
select a.name '员工',b.name '领导' from emp a left join emp b on a.manager_id=b.id;

联合查询

select 字段列表 from 表A···
union
select 字段列表 from 表B··· ;

-- 将薪资低于5000的员工，年龄大于50的员工查询
select *
from emp
where salary<5000
union
select *
from emp
where age>50;

对于联合查询的多张表的列表必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，

union 会对合并之后的数据去重

子查询

SQL语句中嵌套select语句，成为嵌套查询，又称子查询

select * from t1 where column1 =(select column1 from t2);

根据子查询结果不同，分为

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

根据子查询位置，分为 where之后 from之后 select之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符包括：=、<>、>、>=、<、<=。

-- 查询销售部门所有员工信息
select id from dept where name='销售部';
select * from emp where dept_id=4;select * from emp where dept_id=(select id from dept where name='销售部');

-- 查询在东方白在入职之后的员工
select  hire_date from emp where name='方东白';
select  * from emp where hire_date>'2009-02-12';select  * from emp where hire_date>(select  hire_date from emp where name='方东白');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为 列子查询。

常用的操作符包括：

  • IN
  • NOT IN
  • ANY
  • SOME
  • ALL

这些操作符用于将主查询与子查询返回的列数据进行比较。

操作符描述
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回的表中，有任意一个满足即可
SOME与 ANY 等同，使用 SOME 的地方都可以使用 ANY
ALL子查询返回的表的所有值都必须满足
-- 1.查询销售部和市场部的所有员工信息
select  id from dept where name ='销售部' or name = '市场部';
select  * from emp where dept_id in (2,4);select  * from emp where dept_id in (select  id from dept where name ='销售部' or name = '市场部');-- 2.查询比财务部所有人工资都高的员工信息select id from dept where name='财务部';
select  salary from emp where dept_id=3;
select  salary from emp where dept_id=(select id from dept where name='财务部');select * from emp where salary >all(select  salary from emp where dept_id=(select id from dept where name='财务部'));-- 3.查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id =(select id from dept where name='研发部');
select * from emp where salary >any(select salary from emp where dept_id =(select id from dept where name='研发部'));

行子查询

行子查询是指子查询返回的结果是一行（可以包含多列）。

常用操作符：

  • =（等于）
  • <>（不等于）
  • IN（在集合内）
  • NOT IN（不在集合内）
-- 查询与张无忌的薪资及直属领导相同的员工薪资
select salary,manager_id from emp where name='张无忌';
select * from emp where (salary,manager_id)=(select salary,manager_id from emp where name='张无忌') ;

表子查询

表子查询是指子查询返回的结果是多行多列（即一个完整的临时表），这种子查询通常用作主查询的数据源或连接条件。

常用操作符：

  • IN（在查询结果范围内）
特点：
  1. 子查询返回的是一个 临时表（多行多列），而非单个值或单列。
  2. 通常用于 FROM 子句、JOIN 或 IN 条件中。
常见用法：

  1. 作为临时表（FROM 子句）

    SELECT * 
FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id
) AS dept_avg
WHERE avg_salary > 10000;

   
2. **与 IN 操作符联用**  ```sqlSELECT * FROM empWHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM emp GROUP BY dept_id);

  1. 与 JOIN 联用

    SELECT e.* 
FROM emp e
JOIN (SELECT dept_id FROM dept WHERE location = '上海') AS d
ON e.dept_id = d.dept_id;
注意事项：
  • 如果子查询返回多列，主查询的列必须与子查询的列 数量匹配。
  • 在 IN 操作中，通常用于多列联合匹配（如 (col1, col2) IN (子查询)）。
-- 查询与鹿杖客，宋远桥 职位薪资相同的员工信息
select position,salary from emp where  name ='鹿枝客' or name='宋远桥';
select  * from emp where  (position,salary) in (select position,salary from emp where name='鹿枝客' or name ='宋远桥');-- 查询入职日期是’2006-01-01‘之后的员工信息，及其部门信息
select *
from emp where hire_date>'2006-01-01';select e.*,d.* from (select *
from emp where hire_date>'2006-01-01') e left join  dept d on e.dept_id=d.id;

实战训练

  1. 查询员工的姓名、年龄、职位、部门信息。
  2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
  3. 查询拥有员工的部门ID、部门名称。
  4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
  5. 查询所有员工的工资等级。
  6. 查询“研发部”所有员工的信息及工资等级。
  7. 查询“研发部”员工的平均工资。
  8. 查询工资比“灭绝”高的员工信息。
  9. 查询比平均薪资高的员工信息。
  10. 查询低于本部门平均工资的员工信息。
  11. 查询所有的部门信息，并统计部门的员工人数。
  12. 查询所有学生的选课情况，展示出学生名称、学号、课程名称。
-- 1. 查询员工的姓名、年龄、职位、部门信息(隐式内连接）。
select e.name, e.age, e.position, d.name
from emp e,dept d
where e.dept_id = d.id;-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显式内连接）
select e.name, e.age, e.position, d.name
from emp einner join dept d on e.dept_id = d.id
where e.age < 30;
--  3. 查询拥有员工的部门ID、部门名称。
select distinct d.id, d.name
from emp e,dept d
where e.dept_id = d.id;-- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
select e.*, d.name
from emp eleft join dept d on d.id = e.dept_id
where e.age > 40;
-- 5. 查询所有员工的工资等级。
select e.name, e.salary, s.grade, s.hisal, s.losal
from emp e,salgrade s
where e.salary >= s.losaland e.salary <= s.hisal;
-- 6. 查询“研发部”所有员工的信息及工资等级。
select e.*, s.grade
from emp e,dept d,salgrade s
where e.dept_id = d.idand (e.salary between s.losal and s.hisal)and d.name = '研发部';
-- 7. 查询“研发部”员工的平均工资。
select avg(e.salary) from emp e,dept  d where e.dept_id=d.id and d.name ='研发部';
-- 8. 查询工资比“灭绝”高的员工信息。\
select salary from emp where name='灭绝';select * from emp where salary >8500;select * from emp where salary >(select salary from emp where name='灭绝');-- 9. 查询比平均薪资高的员工信息。
select avg(salary) from emp;
select * from  emp where salary >(select avg(salary) from emp);
-- 10. 查询低于本部门平均工资的员工信息。
select avg(e1.salary) from emp e1 where  e1.dept_id =1;
select avg(e1.salary) from emp e1 where  e1.dept_id =2;
select avg(e1.salary) from emp e1 where  e1.dept_id =3;
select avg(e1.salary) from emp e1 where  e1.dept_id =4;
select avg(e1.salary) from emp e1 where  e1.dept_id =5;
select avg(e1.salary) from emp e1 where  e1.dept_id =6;select * from emp e2 where  e2.salary<(select avg(e1.salary) from emp e1 where  e1.dept_id =e2.dept_id);-- 11. 查询所有的部门信息，并统计部门的员工人数。
select d.id,d.name,(select count(*) from emp e where e.dept_id =d.id) '人数' from dept d;select COUNT(*) from  emp where dept_id=1;-- 12. 查询所有学生的选课情况，展示出学生名称、学号、课程名称。
select s.name,s.no,c.name from  student s,student_course sc ,course c where s.id=sc.studentid and sc.courseid =c.id;
http://www.xdnf.cn/news/1433377.html

相关文章：

  • 基于YOLOv4的无人机视觉手势识别系统：从原理到实践
  • 货运系统源码 货运物流小程序 货运平台搭建 货运软件开发
  • C19T1
  • Elasticsearch常用DSL快速查询指南
  • Vue.js 中深度选择器的区别与应用指南
  • 从零开始学Vue3：Vue3的生命周期
  • 前端架构知识体系：常见图片格式详解与最佳实践
  • [密码学实战]逆向工程常见工具合集及下载地址（四十七）
  • 23种设计模式-抽象工厂模式
  • CICD实战(3) - 使用Arbess+GitLab+Hadess实现Java项目构建/上传制品库
  • qt使用笔记三之 QGraphicsView、QGraphicsScene 和 QGraphicsPixmapItem 详解
  • 大模型常用的数据类型FP32,BF16,FP16
  • 基于arm芯片的驱动开发——温湿度传感器dht11
  • Java 垃圾回收机制（GC算法、GC收集器如G1、CMS）的必会知识点汇总
  • Java面试实战系列【JVM篇】- JVM内存结构与运行时数据区详解（共享区域）
  • JavaSE丨异常处理详解，高效应对程序中的“意外”
  • 结构抗震与土木工程研究
  • Windows控制台颜色修改
  • 移动端网页设计vm+rem，和px实现方式对比
  • 【设计模式】三大原则 单一职责原则、开放-封闭原则、依赖倒转原则
  • Javascript》》JS》》ES6》 Map、Set、WeakSet、WeakMap
  • 【MATLAB绘图进阶】（3.1）从基础到高级的图形样式控制
  • Android14 init.rc各个阶段的主要操作详解2
  • gbase8s之导出mysql导入gbase8s
  • 良策金宝AI：电力工程的“最强大脑”，如何重塑设计新范式？
  • css中的v-bind 动态变化
  • 技术架构设计--资源与链接、安全灾备
  • Android URC 消息透传 MTK 代码方案
  • T40N君正/INGENIC专业嵌入式CPU计算能力,集成XBurst2双核处理器（1.2GHz）、RISC-V协处理器和神经网络加速器（2TOPS算力）
  • 防止应用调试分析IP被扫描加固实战教程