MySQL数据库

1|0基本概念

概念：用于存储和管理数据的仓库

数据库的特点：

持久化存储数据

方便存储和管理数据

使用了统一的方式操作数据库

2|0安装，卸载

安装

略，百度有，基本就两种方式：

绿色版压缩包

安装包

卸载

在MySQL安装目录下找到my.ini文件(MySQL的配置文件)
复制datadir地址（数据存放位置）

卸载MySQL

删除MySQL安装目录文件夹

3|0启动和退出

打开windows服务命令：services.msc

启动和关闭MySQL的服务：命令提示符(管理员)

启动：net start mysql

关闭：net stop mysql

MySQL的登录和退出（本地数据库）

先启动MySQL服务：net start mysql

root是管理员账号，一般管理员账号root的密码也设置为root

连接数据库方式1：mysql -uroot -proot

连接数据库方式2：mysql -uroot -p，然后输入密码

退出数据库方式1：exit

退出数据库方式2：quit

MySQL的登录和退出（远程数据库）

连接数据库方式1：mysql -h"ip地址" -uroot -p，然后输入连接目标的密码
如：mysql -h127.0.0.1 -uroot -p

连接数据库方式2：mysql --host="ip地址" --user="用户名" --password="密码"
如：mysql --host=127.0.0.1 --user=root --password=root

退出数据库方式1：exit

退出数据库方式2：quit

4|0目录结构

安装目录

MySQL的安装目录下有个配置文件：my.ini

MySQL的安装位置：my.ini文件中的basedir

数据目录

MySQL的数据存放位置：my.ini文件中的datadir

数据库：文件夹（相当于）

表：文件

数据：文件的内容

5|0SQL

SQL：结构化查询语言

其实就是定义了操作所有关系型数据库的规则

每种数据库操作的方式存在不同的地方，称为“方言”

SQL的通用语法

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

可以使用空格和缩进，来增强语句的可读性

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

注释
单行注释
-- 注释（--之后有空格）
# 注释（mysql特有）
多行注释
/* 注释 */

6|0SQL的分类

Data Definition Language (DDL 数据定义语言) 如：建库，建表

Data Manipulation Language(DML 数据操作语言)，如：对表中的记录操作增删改

Data Query Language(DQL 数据查询语言)，如：对表中的查询操作

Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

6|1DDL：操作数据库和表

操作数据库

CRUD

C: 创建
创建数据库：create database 数据库名称；
创建数据库前判断是否存在，如果存在就不创建，不存在再创建：create database if not exists 数据库名称;
创建数据库时设置字符集：create database 数据库名称 character set gbk;
创建数据库前判断是否存在并设置字符集：create database if not EXISTS db1 CHARACTER set gbk;

R: 查询
查询所有数据库的名称：show databases;
查询数据库的创建语句和字符集：show create database 数据库名称；

U: 修改
修改数据库的字符集：ALTER DATABASE 数据库名称 CHARACTER set utf8;

D: 删除
删除数据库：DROP DATABASE 数据库名称;
删除数据库之前判断是否存在，如果存在就删除，如果不存在就不删除：DROP DATABASE if EXISTS 数据库名称;

使用数据库：

查询当前正在使用的数据库：SELECT DATABASE();

使用数据库：USE 数据库名称;

操作表

创建
创建表：create table 表名(
列名1 数据类型1，
列名2 数据类型2，
……，
列名n 数据类型n
);
如：create table student(
id int,
NAME VARCHAR(32),
age int,
score double(4,1),
birthday DATE,
insert_time TIMESTAMP
);

查询
查询数据库中所有表的名称：show tables;
查询表结构：desc 表名

修改
修改表名：alter table 表名 rename to 新的表名;
如：alter table stu rename to stu1;
修改表的字符集：ALTER TABLE 表名 CHARACTER set gbk;
添加一列：ALTER TABLE 表名 add 列名数据类型；
修改列名称和类型：ALTER TABLE 表名 change 列名 新的列名 新的类型;
修改列类型：ALTER TABLE 表名 modify 列名 新的类型;
删除列：ALTER TABLE 表名 DROP 列名;

删除
删除表：drop table 表名;
删除表之前判断表是否存在：drop table if exists 表名;

复制
复制表：create table 目标表名 like 原始表名;
如：create table stu like student;

常用的数据类型：

int：整数
如：age int

double：小数类型
如：score double(5，2)
共5位小数，小数点后保留2位
最大就是999.99

date：日期，只包含年月日，yyyy-MM-dd

datetime：日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss

timestamp：时间戳类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
如果不给这个字段赋值或赋值为null，则默认使用当前系统时间来自动赋值

varchar：字符串类型
如：name varchar(20)
姓名最大20个字符

6|2DML：增删改表中的数据

添加数据

 #语法：insert into 表名(列名1,列名2……列名n) values(值1,值2……值n);	
insert into stu1(id,name,age) VALUES(3,"马保国",70);
#注意：
#1.列名 和 值 要一一对应
#2.如果表名后，不写列名，则默认要给所有列都添加值
insert into stu1 VALUES(4,"张左手",28,99.5,null,null);
#3.除了数字类型，其他类型赋值时要带上引号（单引号双引号都可以）
insert into stu1 VALUES(5,"李大头",30,95.5,'2023-3-22',null);

删除数据

 #按条件删除表中数据：delete from 表名 [where 条件]
delete from stu where id = 1;
#注意：
#如果不加where条件，则删除表中所有记录（有几条记录，就执行几次删除；效率低，不推荐使用）

 #删除表：TRUNCATE TABLE 表名;
TRUNCATE TABLE stu1;
#注意：此操作为删除表后，再创建一个一模一样的空表（效率高，推荐使用）

修改数据

 #语法：update 表名 set 列名1 = 值1,列名2 = 值2,……[where 条件];
UPDATE stu1 set name = "马保国666" where id = 3;
#注意
#如果不加条件，则会将表中所有记录全部修改

6|3DQL：查询表中的数据

 #语法
select 
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having 
	分组之后的条件
order by
	排序
limit
	分页限定

基础查询

多个字段的查询

去除重复

计算列

起别名

多个字段的查询

 # 查询所有字段 语法：select * from 表名
select * from student;
 
#多个字段查询，中间用逗号隔开
select 
	id,			-- 字段1
	name,		-- 字段2
	address 	-- 字段3
from 
	student;	-- 表名

去除重复

 #查询student表中的地址
select DISTINCT -- 去除重复：DISTINCT
	address 
from 
	student;

计算列

 #计算math列和english列的和：math + english 
select 
	name,
	math,
	english,
	math + english 	-- 计算
from 
	student;
	
#注意：如果有null参与计算,结果为null
#如果需要在计算时将null替换为0，可以使用IFNULL(原始值,替换值)
select 
	name,
	math,
	english,
	math + IFNULL(english,0)	-- 计算时，如果english的值为null，则替换为0
from 
	student;

起别名

 #计算math列和english列的和，并用sum代替
select 
	name,
	math,
	english,
	math + english as sum	-- 使用as起别名
	# math + english sum	-- 不写as也可以
from 
	student;

条件查询

where子句 + 条件

运算符：
> 、< 、<= 、>= 、= 、<>
BETWEEN...AND
IN( 集合)
LIKE ' 张%'
IS NULL
and 或 &&
or 或 ||
not 或 !

 #查询年龄大于20岁
SELECT * from student WHERE age > 20;
 
#查询年龄大于等于20岁
SELECT * from student WHERE age >= 20;
 
#查询年龄等于20岁
#等于用=，而不是==
SELECT * from student WHERE age = 20;
 
#查询年龄不等于20岁
#<>在SQL中表示不等于,在mysql中也可以使用!=
SELECT * from student WHERE age <> 20;
SELECT * from student WHERE age != 20;

 #查询年龄大于等于20岁，小于等于30岁
SELECT * from student WHERE age >= 20 && age <= 30; -- 不推荐
SELECT * from student WHERE age >= 20 AND age <= 30; 
SELECT * from student WHERE age BETWbeEEN 20 AND 30; 
# BETWbeEEN 20 AND 30，包括 20 和 30

 #查询年龄为18,20,22岁的记录
SELECT * from student WHERE age = 18 || age = 20 || age = 22; 
SELECT * from student WHERE age = 18 OR age = 20 OR age = 22; 
SELECT * from student WHERE age IN(18,20,22);

 #查询英语成绩为null的记录
select * from student where english = null;		-- 这是错误的语法；null值不能用运算符判断
select * from student where english is null;	-- 这是正确的语法；null值要用is判断
 
#查询英语成绩不为null的记录
select * from student where english is NOT null;	-- 使用 is not判断不为null

条件查询-模糊查询

_：表示单个任意字符

%：表示多个任意字符

 #模糊查询：查询姓马的人
#查询姓马的人
select * from student WHERE name LIKE '马%';
#查询姓马的，姓名为两个字的人
select * from student WHERE name LIKE '马_';
#查询姓名第二个字是“化”的人
select * from student WHERE name LIKE '_化%';
#查询姓名最后一个字是“涛”的人
select * from student WHERE name LIKE '%涛';
#查询姓名为3个字的人
select * from student WHERE name LIKE '___'; 	-- 三个下划线
#查询姓名中包含“德”的人
select * from student WHERE name LIKE '%德%';

排序查询

语法：order by 子句

order by 排序字段1 排序方式1,排序字段2 排序方式2……

排序方式：

ASC：升序，默认的

DESC：降序

 #按照升序的方式查询数学成绩
SELECT * FROM student ORDER BY math ASC;
SELECT * FROM student ORDER BY math;		-- 不写排序方式，默认为升序
 
#按照降序的方式查询数学成绩
SELECT * FROM student ORDER BY math DESC;
 
#按照升序的方式查询数学成绩;
#如果有几条记录的数学成绩相同，则这几条记录按照英语成绩升序排序
SELECT * 
FROM 
	student 
ORDER BY 
	math ASC, 			-- 第一条件
	english ASC;		-- 第二条件（只有当第一条件相同时，才会使用第二条件）

聚合函数

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

count：个数
一般使用主键：SELECT COUNT(*) FROM 表名;
count( * )：不推荐使用

max：最大值

min：最小值

sum：求和

avg：求平均数

注意：聚合函数计算时，null值不加入计算

计算个数

语法：SELECT COUNT(列名) FROM 表名;

 #计算student表中有多少个math
SELECT COUNT(math) FROM student;
 
#如果要计算包含null值的列数，使用IFNULL
select COUNT(IFNULL(math,0)) FROM student;

最大值

语法：select MAX(列名) FROM 表名;

 #计算student表中math列的最大值
select MAX(math) FROM student;

最小值

语法：select MIN(列名) FROM 表名;

 #计算student表中math列的最小值
select MIN(math) FROM student;

求和

语法：select SUM(列名) FROM 表名;

 #计算student表中math列的和
select SUM(math) FROM student;

平均值

语法：select AVG(列名) FROM 表名;

 #计算student表中math列的平均值
select AVG(math) FROM student;

分组查询

语法：group by 分组字段;
注意：

分组之后查询的字段：分组字段、聚合函数

where和having的区别？

where在分组之前进行限定，如果不满足限定条件，就不参与分组

having在分组之后进行限定，如果不满足限定条件，就不显示出来

where后不可以跟聚合函数，having后可以加上聚合函数的判断

 #按照性别分组，分别查询男同学和女同学的数学平均分
select 
	sex,				-- 性别
	AVG(math)			-- 数学平均分
FROM
	student				-- 表名
GROUP BY
	sex;				-- 按照性别分组
 
 
#按照性别分组，分别查询 男同学和女同学的 数学平均分和人数
select 
	sex,				-- 性别
	AVG(math), 			-- 数学平均分
	COUNT(id)			-- 人数
FROM
	student				-- 表名
GROUP BY
	sex;				-- 按照性别分组
 
 
#按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
#要求数学成绩低于70分的人，不参与分组
select 
	sex,				-- 性别
	AVG(math), 			-- 数学平均分
	COUNT(id)			-- 人数
FROM
	student				-- 表名
WHERE
	math > 70			-- 数学成绩大于70分的才参与分组
GROUP BY
	sex;				-- 按照性别分组
 
 
#按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
#要求：数学成绩低于70分的人，不参与分组
#要求：显示人数大于2的记录
select 
	sex,				-- 性别
	AVG(math), 			-- 数学平均分
	COUNT(id)			-- 人数
FROM
	student				-- 表名
WHERE
	math > 70			-- 数学成绩大于70分的才参与分组
GROUP BY
	sex					-- 按照性别分组
HAVING
	COUNT(id) > 2;		-- 聚合函数，显示人数大于2的记录
 
 
#按照性别分组，分别查询 男同学和女同学 的 数学平均分和人数
#要求：数学成绩低于70分的人，不参与分组
#要求：显示人数大于2的记录
select 
	sex,				-- 性别
	AVG(math), 			-- 数学平均分
	COUNT(id)	人数	-- 别名:人数
FROM
	student
WHERE
	math > 70			-- 数学成绩大于70分的才参与分组
GROUP BY
	sex					-- 按照性别分组
HAVING
	人数 > 2;			-- 显示别名人数大于2的记录

分页查询

语法：limit 开始的索引，每页的查询条数；

开始的索引：0代表第一条数据

开始的索引 = （当前页码 - 1） * 每页显示的条数

注意：limit是一个方言，只在MySQL中使用

 #查询student表中第一页，每页3条数据
#也就是前3条数据（第1到3条）
select * from  student LIMIT 0,3;	#第一页
 
#查询student表中第二页数据（第4到6条）
#开始的索引：（2 - 1） * 3 = 3
select * from  student LIMIT 3,3;	#第二页
 
#查询第三页数据，（3-1）*3=6
select * from  student LIMIT 6,3;	#第三页

6|4DCL：管理用户权限

查询用户

 # 查询用户
#1.切换数据库到mysql
USE mysql;
#2.查询user表
SELECT * FROM USER;
 
# host的值
# localhost 表示当前主机可登录
# % 表示任意主机可登录,可用于远程操作

添加用户

 # 添加用户
# 语法：create user '用户名'@'主机名' identified by '密码';
CREATE USER 'JHF'@'localhost' IDENTIFIED BY '123';
CREATE USER 'JHF'@'%' IDENTIFIED BY '123';

修改密码

注意：

旧版需要 password()函数给密码加密

新版取消了password关键字，改为了authentication_string关键字

新版取消了password()函数

 # 修改用户密码
# 需求：修改用户JHF的密码为abc
 
# 方式一
# 旧版语法：update user set password = password('新密码') where user = '用户名';
# 新版语法：update user set authentication_string = '新密码' where user = '用户名';
UPDATE USER SET authentication_string = 'abc' WHERE USER = 'JHF';
 
# 方式二
# 旧版语法：set password for '用户名'@'主机名' = password('新密码'); 
# 新版语法：set PASSWORD for '用户名'@'主机名' = 'abc'; 
SET PASSWORD FOR 'JHF'@'localhost' = 'abc123';

如果忘记root用户的密码，该怎么办？

在命令提示符管理员的权限下，停止mysql服务：net stop mysql;

使用无验证方式，启动MySQL服务：mysqld --skip-grant-tables，回车；

新打开一个命令提示符窗口输入：MySQL，回车；

登录之后，使用命令更改root账户的密码；

使用任务管理器，手动结束mysqld.exe进程；

在命令提示符管理员的权限下，打开mysql服务：net start mysql;

通过更改之后的 root账户的密码，登录MySQL数据库；

删除用户

 # 删除用户
# 语法：drop user '用户名'@'主机名';
DROP USER 'JHF'@'localhost';
DROP USER 'JHF'@'%';

管理用户权限

 # 查询用户权限
# 语法：SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'JHF'@'localhost';

授予用户的权限（权限列表）

如 CREATE、ALTER、SELECT、INSERT、UPDATE 等；

如果要授予所有的权限则使用 ALL；

 # 授予用户权限
# 语法：grant  权限列表 on 数据库名.表名 to '用户名'@'主机名';
 
# 给Tom设置只能查看demo1数据库中的account表，并进行（权限列表）操作。
GRANT  SELECT ON demo1.`account` TO 'Tom'@'localhost';
GRANT  ALL ON demo1.`account` TO 'Tom'@'localhost';
 
# 给Jerry设置所有数据库所有表的所有权限
GRANT  ALL ON *.* TO 'Jerry'@'%';	
# 一般来讲，要先在mysql数据库中的user表下，使用CREATE USER语法创建用户，再进行权限授权。

 # 撤销权限
# 语法：REVOKE 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE ALL ON demo1.`account` FROM 'Tom'@'localhost';
REVOKE UPDATE ON *.* FROM 'Jerry'@'%';

7|0约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束的分类：

主键约束：primary key

非空约束：not null

唯一约束：unique

外键约束：foreign key

非空约束

非空约束：某一列的值不能为null

添加非空约束的两种方式

创建表时，添加非空约束

创建完表之后，添加非空约束

 #1.创建stu表时,对name列添加非空约束
CREATE table stu(
	id INT,
	NAME VARCHAR(20) NOT NULL 	#NAME非空
);
#2.测试：给name列插入null数据
INSERT INTO stu(id) VALUES(3);
#3.结果报错
#报错原因：添加非空约束的字段不能为null
Field 'NAME' doesn't have a default value
 
#但如果name列没有设置非空约束，则可以添加null值
#1.更改name列的类型，删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
#2.执行成功，name为null时可以插入数据
INSERT INTO stu(id) VALUES(3);
 
#创建完stu表之后，对name列添加非空约束
#1.删除name为null的数据
DELETE FROM stu where id = 3;
#2.更改name列的类型，设置非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
#3.测试：给name列插入null数据
#结果报错，添加非空约束的字段不能为null
INSERT INTO stu(id) VALUES(3);

唯一约束

某一列的值不能重复

唯一约束可以有null值，但只能有一条记录为null

删除唯一约束

ALTER TABLE 表名 DROP INDEX 列名;

添加唯一约束的两种方式

创建表时，添加唯一约束

创建完表之后，添加唯一约束

 #1.创建stu表时，对phoneNumber列添加唯一约束
CREATE table stu(
	id INT,	
	phoneNumber VARCHAR(20) UNIQUE #电话号码不能重复
);
#2.添加数据
INSERT INTO stu(id,phoneNumber) VALUES(1,15291011111);
#3.执行报错
INSERT INTO stu(id,phoneNumber) VALUES(2,15291011111);
#报错原因：设置唯一约束的列不能重复
Duplicate entry '15291011111' for key 'phoneNumber'
#4.执行成功，phoneNumber列的值不重复
INSERT INTO stu(id,phoneNumber) VALUES(2,15291011112);
 
#1.删除唯一约束，更改phoneNumber列的类型
ALTER TABLE stu MODIFY phoneNumber VARCHAR(20);
#2.添加数据
#执行失败，以更改类型的方式删除唯一约束，是不行的
INSERT INTO stu(id,phoneNumber) VALUES(1,15291011111);
#3.DROP INDEX：以删除索引的方式，删除唯一约束
ALTER TABLE stu DROP INDEX phoneNumber;
#4.添加数据成功
#没有添加唯一约束时，数据可以重复
INSERT INTO stu(id,phoneNumber) VALUES(3,15291011111);
 
#创建完表之后，添加唯一约束
#1.删除phoneNumber列有重复的数据
DELETE FROM stu WHERE id=3;
#2.更改phoneNumber列的类型，添加唯一约束
ALTER TABLE stu MODIFY phoneNumber VARCHAR(20) UNIQUE;
#3.添加数据失败
#设置唯一约束的列不能重复
INSERT INTO stu(id,phoneNumber) VALUES(3,15291011111);

主键约束

主键：非空且唯一

一张表只能有一个字段为主键

主键就是表中记录的唯一标识

添加主键约束的两种方式

创建表时，添加主键约束

创建完表之后，添加主键约束

删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;
4.主键自动增长：auto_increment

自动跟随上一条记录的最大值进行+1

 #1.创建表时，添加主键约束
create table stu(
	id int PRIMARY KEY,	#给id添加主键约束
	name VARCHAR(30)
);
#2.添加记录
INSERT INTO stu(id,name) VALUES(1,"马保国");
#添加失败
INSERT INTO stu(id,name) VALUES(1,"马保国");
#报错原因：主键重复
Duplicate entry '1' for key 'PRIMARY'
#3.主键不重复，则添加记录成功
INSERT INTO stu(id,name) VALUES(2,"马保国");
 
#1.删除主键
ALTER TABLE stu MODIFY id INT;		#错误方式
#2.添加记录失败
INSERT INTO stu(id,name) VALUES(1,"马保国");
#3.删除主键
ALTER TABLE stu DROP PRIMARY KEY;	#正确方式
#4.id重复时，没有设置主键约束，可以添加记录成功
INSERT INTO stu(id,name) VALUES(1,"马保国");
 
#创建完表之后，添加主键约束
#1.删除表中所有记录
delete FROM stu;
#2.更改id类型，添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
#3.添加记录
INSERT INTO stu(id,name) VALUES(1,"马保国");
#4.添加记录失败
INSERT INTO stu(id,name) VALUES(1,"马保国");
#报错原因：主键重复
Duplicate entry '1' for key 'PRIMARY'

主键约束-自动增长

如果某列是数值类型，可以使用auto_increment实现自动增长

自动增长一般配合主键id使用

主键id自动增长，会自动跟随上一条记录的id最大值进行+1

 #1.删除旧表
drop table stu;
#2.创建新表
create table stu(
	id int PRIMARY KEY auto_increment,	#设置主键自动增长
	name VARCHAR(30)
);
#3.插入数据
INSERT INTO stu VALUES(null,"aaa");
INSERT INTO stu VALUES(null,"bbb");
#4.查询表中所有数据
select * from stu;

运行结果：id自动增长成功

 #删除自动增长
#1.更改id类型，删除自动增长
ALTER TABLE stu MODIFY id INT;
#2.添加数据失败
INSERT INTO stu VALUES(null,"aaa");
#报错原因：删除自动增长之后，主键不能为空
Column 'id' cannot be null
 
#添加自动增长
#1.更改id类型，添加自动增长
ALTER TABLE stu MODIFY id INT auto_increment;
#2.添加数据成功
#设置自动增长后，id虽然为null，但可以根据上一条记录的值，实现自动增长
INSERT INTO stu VALUES(null,"aaa");

外键约束

外键约束：让表与表产生关系，从而保证数据的正确性

外键可以为null（没有关联），但是不可以为主表中不存在的值

添加外键约束的方式

在创建表时，添加外键约束
create table 表名(
……
外键列，
constraint 外键名称 foreign key （外键列名称） references 主表名称(主表列名称)
);

在创建表之后，添加外键约束
ALTER TABLE 表名 ADD constraint 外键名称 foreign key （外键列名称） references 主表名称(主表列名称);

删除外键

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

案例
创建emp表，并添加数据

 #1.创建emp表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
#2.添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
 
#3.查询表的所有记录
select * from emp;

发现emp表中有多条重复的内容

dep_name：部门名称

dep_location：部门位置

使用外键约束进行优化

1.创建部门表department，并添加数据

 #1.创建部门表
create table department(				#部门表
	id int primary key auto_increment, 	#主键id自增
	dep_name varchar(20),				#部门名称
	dep_location varchar(20)			#部门地址
);
#2.添加部门数据
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
#3.查询部门表的所有内容
select * from department;

这样就没有了重复内容

2.创建员工表employee

 #1.创建员工表
create table employee(					#员工表
	id int primary key auto_increment,	#主键id自增
	name varchar(20),					#员工名称
	age int,							#员工年龄
	dep_id int 							#部门表的id(外键对应主表的主键)
)
#2.添加员工数据
#添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
#3.查询员工表所有内容
select * from employee;

dep_id关联department表中的部门信息

但是这样并没有真正的起作用

让我们使用外键约束来重新设计表吧

3.在创建表时，添加外键约束

使用外键约束，重新设计数据库表

 #1.删除emp表、department表和employee表
drop table emp;
drop table department;
drop table employee;
 
#1.重新创建部门表department
create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);
 
#2.重新创建employee表，加入外键约束
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,					#外键列
	CONSTRAINT 					#约束
	emp_dep_fk 					#自定义的外键名称
	FOREIGN KEY (dep_id) 		#外键：此表的外键列
	REFERENCES department(id)	#关联：主表的主键列
);
 
#3.添加数据
#添加两个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
#添加员工信息
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

select * from department;

select * from employee;

此时employee表已经和主表department通过外键约束关联起来了

 #1.删除主表department中的一条记录
DELETE FROM department WHERE id = 1;
#报错：主表的数据正在通过外键被其他表关联着，所以删不掉
Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
#2.向employee表中添加一条记录，关联department中不存在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
#报错：使用了外键约束的表，只能关联主表中存在的记录
Cannot add or update a child row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

删除外键

 #1.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
#2.向employee表中添加一条记录，关联department中不存在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
#执行成功，删除了外键之后，可以向表中添加记录关联主表中不存在的记录

在创建表之后，添加外键约束

 #1.删除之前向employee表中添加的，关联主表中不存在记录的那条数据
DELETE FROM employee WHERE id = 8;
#2.添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);
#3.向employee表中添加一条记录，关联department中不存在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('马保国', 70, 3);
#报错：使用了外键约束的表，只能关联主表中存在的记录
Cannot add or update a child row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

外键约束-级联操作

主表中的列，被外键约束之后，就不能随意更改了；如果想要更改，就要设置级联操作

添加外键时，设置级联操作：
ALTER TABLE 表名 ADD constraint 外键名称 foreign key （外键列名称） references 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

级联操作的分类

级联更新：ON UPDATE CASCADE
表的外键列，随着关联的主表列的改变，而自动改变

级联删除：ON DELETE CASCADE
表中的记录，随着主表数据的删除，而自动删除

 #添加外键时，设置级联更新
#1.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
#2.添加外键,设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
#3.设置级联更新之后，就可以将主表中的关联列数据进行更改了
UPDATE department SET id = 5 WHERE id = 1;

查看更改后的主表department数据
SELECT * FROM department;

主表数据更改之后，employee表中的外键也随之更新了
SELECT * FROM employee;

 #添加外键,设置级联更新，设置级联删除
#1.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
#2.添加外键,设置级联更新，设置级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
#3.设置级联删除后，删除主表department中的记录
DELETE FROM department WHERE id = 5;

查看删除数据之后的主表department数据
SELECT * FROM department;

可以看到删除主表department中的数据之后，employee表中与之相关联的数据也被删除了
SELECT * FROM employee;

8|0数据库的设计

多表之间的关系

一对一

一对多（多对一）

多对多

数据库设计的范式

8|11. 多表之间的关系

一对一： 一个人只有一个身份证，一个身份证只能对应一个人

一对多（多对一）： 一个部门有多个员工，一个员工只对应一个部门

多对多： 一个学生可以选择多门课程，每门课程也可以被多个学生选择

1|0多表关系：一对多关系实现

如：部门和员工

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

1|0多表关系：多对多关系实现

如：学生和课程

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

联合主键：PRIMARY KEY(a,b);

1|0多表关系：一对一关系实现（了解）

如：人和身份证

实现方式：任意一方添加唯一外键，指向另一方的主键

注意：一对一的关系，一般设计成一张表

8|22. 范式

1|0概念

设计数据库时，需要遵循的一些规范；首先要遵循三大范式。

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

1|0分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。

一般说来，数据库只需满足第三范式(3NF）就行了。

1|0详解

第一范式

每一列不可再拆分，称为原子性

第二范式

在 1NF 的基础上所有列完全依赖于主键列
特点：

一张表只描述一件事情。

表中的每一列都完全依赖于主键

函数依赖：A-->B，如果通过A(属性或属性组)的值，可以确定唯一 B属性的值。则称B依赖于A

完全函数依赖：A-->B，A是一个属性组，如果B属性的值需要依赖于A中所有的属性值，则称B完全依赖于A

部分函数依赖：A-->B，A是一个属性组，如果A中的某些属性值就能确定B属性的值，则称B部分依赖于A

传递函数依赖：A-->B-->C，如果通过A(属性或属性组)的值，可以确定唯一 B属性的值；通过B(属性或属性组)的值，可以确定唯一 C属性的值。则称C传递依赖于A

码：如果在一张表中，一个属性或者属性组被其他所有属性完全依赖，则称这个属性或属性组为该表的码。
主属性：码属性组中的所有属性
非主属性：除过码属性组中的属性

第三范式

在满足 2NF 的基础上，任何非主列不得传递依赖于主键

三大范式小结

9|0数据库的备份和还原

9|1命令行方式

 #备份：
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
 
#还原：
#1.登录数据库
#2.创建数据库库
#3.使用数据库
#4.执行备份的文件
source 文件路径

10|0多表查询

多表查询的分类

内连接：隐式内连接、显式内连接

外连接：左外连接、右外连接

子查询

 # 多表查询语法
select 
	列名列表
from
	表名列表
where......

创建部门表和员工表，并添加信息

 # 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- 性别
	salary DOUBLE, -- 工资
	join_date DATE, -- 入职日期
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
);
# 添加员工信息
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

部门表dept

员工表emp

查询部门表和员工表中的笛卡尔积

笛卡尔积：A集合与B集合的所有的组合结果

 SELECT * FROM dept,emp;

查询结果：15行，但是有无用数据

使用多表查询可以消除无用信息

10|11.内连接

从哪些表中查询数据

条件是什么

查询哪些字段

隐式内连接：使用where条件消除无用数据

 # 查询所有员工的名称、性别，以及对应的部门名称
SELECT 
	t1.`NAME`,
	t1.`gender`,
	t2.`NAME`
FROM
	emp t1,
	dept t2		-- 别名
WHERE
	t1.`dept_id` = t2.`id`;

查询结果：

显式内连接

语法：select 字段列表 from 表名1 inner join 表名2 on 条件

 # 查询所有员工的名称、性别，以及对应的部门名称
SELECT 
	t1.`NAME`,t1.`gender`,t2.`NAME`
FROM
	emp t1
INNER JOIN	-- inner可以省略
	dept t2
ON
	t1.`dept_id` = t2.`id`;

10|22.外连接

左外连接

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;

查询的是左表所有数据以及与右表的交集部分

 #查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显示部门名称
SELECT
	t1.*,t2.`NAME`
FROM 
	emp t1
LEFT OUTER JOIN	-- outer可以省略
	dept t2
ON
	t1.`dept_id`=t2.`id`;

右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;

查询的是右表所有数据以及与左表的交集部分

 #查询所有部门信息，如果部门有员工则查询员工信息，没有部门则不显示员工信息
SELECT
	t1.*,t2.`NAME`
FROM 
	emp t1
RIGHT OUTER JOIN	-- outer可以省略
	dept t2
ON
	t1.`dept_id`=t2.`id`;

10|33.子查询

概念：查询中嵌套查询，称嵌套查询为子查询

 #查询工资最高的员工信息
SELECT
	*
FROM
	emp
WHERE
	salary = (SELECT MAX(salary) FROM emp);

情况1

子查询的结果是单行单列的

子查询可以作为条件，使用运算符(>, <, =, >=, <=) 去判断。

 #查询员工工资小于平均工资的员工信息
SELECT
	*
FROM
	emp
WHERE
	salary < (SELECT AVG(salary) FROM emp);

情况2

子查询的结果是多行单列的

子查询可以作为条件，使用运算符 in 去判断。

 #查询所有财务部和市场部的员工信息
SELECT
	*
FROM
	emp
WHERE	-- in(A,B)满足括号内任一条件即可
	dept_id IN (SELECT id FROM dept WHERE NAME = "财务部" OR NAME = "市场部");

情况3

子查询的结果是多行多列的

子查询可以当做一张虚拟表来参与查询

 #查询2011-11-11之后入职的员工信息和部门信息
SELECT
	*
FROM
	dept t1,(SELECT * FROM emp WHERE join_date > "2011-11-11") t2
WHERE
	t1.`id` = t2.dept_id;

11|0事务

11|1基本介绍

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作，要么同时成功，要么同时失败。

开启事务：START TRANSACTION;

回滚：ROLLBACK;

提交事务：COMMIT;

事务提交的两种方式

手动提交：

需要先开启事务，再提交；

自动提交：

MySQL数据库中事务默认自动提交，一条DML(增删改)语句会自动提交一次事务；

Oracle数据库中事务默认手动提交；

事物的默认提交方式

 #查看事务的默认提交方式
SELECT @@autocommit;	-- 0手动提交，1自动提交
 
#修改事物的默认提交方式
SET @@autocommit = 0;	-- 修改事务提交方式为手动提交

11|2事务四大特征

原子性：不可分割的最小操作单位，要么同时成功，要么同时失败；

持久性：事务一旦提交或者回滚，数据库将持久化访问数据；

隔离性：多个事物之间，相互独立；

一致性：事务操作前后，数据总量不变；

11|3事务隔离级别

概念

多个事物之间相互独立，如果多个事务操作同一批数据，则会引发一些问题；设置不同的隔离级别，就可以解决这些问题；

存在问题

脏读：一个事务读取到另一个事务中，没有提交的数据；

不可重复读（虚读）：在同一个事物中，两次读取到的数据不一样；

幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，导致第一个事务查询不到自己的修改；

隔离级别

read uncommited：读未提交

产生的问题：脏读、不可重复读、幻读；

read commited：读已提交（Oracle默认）

产生的问题：不可重复读、幻读；

repeatable read：可重复读（MySQL默认）

产生的问题：幻读；

serializable：串行化

可以解决所有的问题；

注意：隔离级别从小到大，安全性递增，效率递减；

数据库查询 / 设置隔离级别

查询隔离级别：select @@tx_isolation；

设置隔离级别：set global transaction isolation level 隔离级别；

__EOF__

本文作者：茶碗儿
本文链接：https://www.cnblogs.com/chawaner/p/17396669.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！

posted @ 2023-05-13 05:39 茶碗儿阅读(115) 评论(3) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Java集合

· File类和IO流

· MySQL数据库

· MySQL

公告

MySQL数据库

发表于 2023-05-13 05:39阅读：115评论：3推荐：0

MySQL

关注

跳至底部

昵称：茶碗儿
园龄： 4年11个月
粉丝： 3
关注： 1

+加关注

	#语法：insert into 表名(列名1,列名2……列名n) values(值1,值2……值n);
	insert into stu1(id,name,age) VALUES(3,"马保国",70);
	#注意：
	#1.列名和值要一一对应
	#2.如果表名后，不写列名，则默认要给所有列都添加值
	insert into stu1 VALUES(4,"张左手",28,99.5,null,null);
	#3.除了数字类型，其他类型赋值时要带上引号（单引号双引号都可以）
	insert into stu1 VALUES(5,"李大头",30,95.5,'2023-3-22',null);

	#按条件删除表中数据：delete from 表名 [where 条件]
	delete from stu where id = 1;
	#注意：
	#如果不加where条件，则删除表中所有记录（有几条记录，就执行几次删除；效率低，不推荐使用）

	#删除表：TRUNCATE TABLE 表名;
	TRUNCATE TABLE stu1;
	#注意：此操作为删除表后，再创建一个一模一样的空表（效率高，推荐使用）

	#语法：update 表名 set 列名1 = 值1,列名2 = 值2,……[where 条件];
	UPDATE stu1 set name = "马保国666" where id = 3;
	#注意
	#如果不加条件，则会将表中所有记录全部修改

	#语法
	select
	字段列表
	from
	表名列表
	where
	条件列表
	group by
	分组字段
	having
	分组之后的条件
	order by
	排序
	limit
	分页限定

	# 查询所有字段语法：select * from 表名
	select * from student;

	#多个字段查询，中间用逗号隔开
	select
	id, -- 字段1
	name, -- 字段2
	address -- 字段3
	from
	student; -- 表名

	#查询student表中的地址
	select DISTINCT -- 去除重复：DISTINCT
	address
	from
	student;

	#计算math列和english列的和：math + english
	select
	name,
	math,
	english,
	math + english -- 计算
	from
	student;

	#注意：如果有null参与计算,结果为null
	#如果需要在计算时将null替换为0，可以使用IFNULL(原始值,替换值)
	select
	name,
	math,
	english,
	math + IFNULL(english,0) -- 计算时，如果english的值为null，则替换为0
	from
	student;

	#计算math列和english列的和，并用sum代替
	select
	name,
	math,
	english,
	math + english as sum -- 使用as起别名
	# math + english sum -- 不写as也可以
	from
	student;

	#查询年龄大于20岁
	SELECT * from student WHERE age > 20;

	#查询年龄大于等于20岁
	SELECT * from student WHERE age >= 20;

	#查询年龄等于20岁
	#等于用=，而不是==
	SELECT * from student WHERE age = 20;

	#查询年龄不等于20岁
	#<>在SQL中表示不等于,在mysql中也可以使用!=
	SELECT * from student WHERE age <> 20;
	SELECT * from student WHERE age != 20;

	#查询年龄大于等于20岁，小于等于30岁
	SELECT * from student WHERE age >= 20 && age <= 30; -- 不推荐
	SELECT * from student WHERE age >= 20 AND age <= 30;
	SELECT * from student WHERE age BETWbeEEN 20 AND 30;
	# BETWbeEEN 20 AND 30，包括 20 和 30

	#查询年龄为18,20,22岁的记录
	SELECT * from student WHERE age = 18 \|\| age = 20 \|\| age = 22;
	SELECT * from student WHERE age = 18 OR age = 20 OR age = 22;
	SELECT * from student WHERE age IN(18,20,22);

	#查询英语成绩为null的记录
	select * from student where english = null; -- 这是错误的语法；null值不能用运算符判断
	select * from student where english is null; -- 这是正确的语法；null值要用is判断

	#查询英语成绩不为null的记录
	select * from student where english is NOT null; -- 使用 is not判断不为null

	#模糊查询：查询姓马的人
	#查询姓马的人
	select * from student WHERE name LIKE '马%';
	#查询姓马的，姓名为两个字的人
	select * from student WHERE name LIKE '马_';
	#查询姓名第二个字是“化”的人
	select * from student WHERE name LIKE '_化%';
	#查询姓名最后一个字是“涛”的人
	select * from student WHERE name LIKE '%涛';
	#查询姓名为3个字的人
	select * from student WHERE name LIKE '___'; -- 三个下划线
	#查询姓名中包含“德”的人
	select * from student WHERE name LIKE '%德%';

	#按照升序的方式查询数学成绩
	SELECT * FROM student ORDER BY math ASC;
	SELECT * FROM student ORDER BY math; -- 不写排序方式，默认为升序

	#按照降序的方式查询数学成绩
	SELECT * FROM student ORDER BY math DESC;

	#按照升序的方式查询数学成绩;
	#如果有几条记录的数学成绩相同，则这几条记录按照英语成绩升序排序
	SELECT *
	FROM
	student
	ORDER BY
	math ASC, -- 第一条件
	english ASC; -- 第二条件（只有当第一条件相同时，才会使用第二条件）

	#计算student表中有多少个math
	SELECT COUNT(math) FROM student;

	#如果要计算包含null值的列数，使用IFNULL
	select COUNT(IFNULL(math,0)) FROM student;

	#计算student表中math列的最大值
	select MAX(math) FROM student;

	#计算student表中math列的最小值
	select MIN(math) FROM student;

	#计算student表中math列的和
	select SUM(math) FROM student;

	#计算student表中math列的平均值
	select AVG(math) FROM student;

茶碗儿

时间就像海绵里的水，只要愿挤，总还是有的。

MySQL数据库

1|0基本概念

2|0安装，卸载

3|0启动和退出

4|0目录结构

5|0SQL

6|0SQL的分类

6|1DDL：操作数据库和表

6|2DML：增删改表中的数据

6|3DQL：查询表中的数据

6|4DCL：管理用户权限

7|0约束

8|0数据库的设计

8|11. 多表之间的关系

1|0多表关系：一对多关系实现

1|0多表关系：多对多关系实现

1|0多表关系：一对一关系实现（了解）

8|22. 范式

1|0概念

1|0分类

1|0详解

9|0数据库的备份和还原

9|1命令行方式

10|0多表查询

10|11.内连接

10|22.外连接

10|33.子查询

11|0事务

11|1基本介绍

11|2事务四大特征

11|3事务隔离级别

公告

茶碗儿

MySQL数据库

搜索

常用链接

最新随笔

积分与排名

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论

	#按照性别分组，分别查询男同学和女同学的数学平均分
	select
	sex, -- 性别
	AVG(math) -- 数学平均分
	FROM
	student -- 表名
	GROUP BY
	sex; -- 按照性别分组


	#按照性别分组，分别查询男同学和女同学的数学平均分和人数
	select
	sex, -- 性别
	AVG(math), -- 数学平均分
	COUNT(id) -- 人数
	FROM
	student -- 表名
	GROUP BY
	sex; -- 按照性别分组


	#按照性别分组，分别查询男同学和女同学的数学平均分和人数
	#要求数学成绩低于70分的人，不参与分组
	select
	sex, -- 性别
	AVG(math), -- 数学平均分
	COUNT(id) -- 人数
	FROM
	student -- 表名
	WHERE
	math > 70 -- 数学成绩大于70分的才参与分组
	GROUP BY
	sex; -- 按照性别分组


	#按照性别分组，分别查询男同学和女同学的数学平均分和人数
	#要求：数学成绩低于70分的人，不参与分组
	#要求：显示人数大于2的记录
	select
	sex, -- 性别
	AVG(math), -- 数学平均分
	COUNT(id) -- 人数
	FROM
	student -- 表名
	WHERE
	math > 70 -- 数学成绩大于70分的才参与分组
	GROUP BY
	sex -- 按照性别分组
	HAVING
	COUNT(id) > 2; -- 聚合函数，显示人数大于2的记录


	#按照性别分组，分别查询男同学和女同学的数学平均分和人数
	#要求：数学成绩低于70分的人，不参与分组
	#要求：显示人数大于2的记录
	select
	sex, -- 性别
	AVG(math), -- 数学平均分
	COUNT(id) 人数 -- 别名:人数
	FROM
	student
	WHERE
	math > 70 -- 数学成绩大于70分的才参与分组
	GROUP BY
	sex -- 按照性别分组
	HAVING
	人数 > 2; -- 显示别名人数大于2的记录

	#查询student表中第一页，每页3条数据
	#也就是前3条数据（第1到3条）
	select * from student LIMIT 0,3; #第一页

	#查询student表中第二页数据（第4到6条）
	#开始的索引：（2 - 1） * 3 = 3
	select * from student LIMIT 3,3; #第二页

	#查询第三页数据，（3-1）*3=6
	select * from student LIMIT 6,3; #第三页

	# 查询用户
	#1.切换数据库到mysql
	USE mysql;
	#2.查询user表
	SELECT * FROM USER;

	# host的值
	# localhost 表示当前主机可登录
	# % 表示任意主机可登录,可用于远程操作

	# 添加用户
	# 语法：create user '用户名'@'主机名' identified by '密码';
	CREATE USER 'JHF'@'localhost' IDENTIFIED BY '123';
	CREATE USER 'JHF'@'%' IDENTIFIED BY '123';

	# 修改用户密码
	# 需求：修改用户JHF的密码为abc

	# 方式一
	# 旧版语法：update user set password = password('新密码') where user = '用户名';
	# 新版语法：update user set authentication_string = '新密码' where user = '用户名';
	UPDATE USER SET authentication_string = 'abc' WHERE USER = 'JHF';

	# 方式二
	# 旧版语法：set password for '用户名'@'主机名' = password('新密码');
	# 新版语法：set PASSWORD for '用户名'@'主机名' = 'abc';
	SET PASSWORD FOR 'JHF'@'localhost' = 'abc123';

	# 删除用户
	# 语法：drop user '用户名'@'主机名';
	DROP USER 'JHF'@'localhost';
	DROP USER 'JHF'@'%';

	# 查询用户权限
	# 语法：SHOW GRANTS FOR '用户名'@'主机名';
	SHOW GRANTS FOR 'JHF'@'localhost';

	# 授予用户权限
	# 语法：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

	# 给Tom设置只能查看demo1数据库中的account表，并进行（权限列表）操作。
	GRANT SELECT ON demo1.`account` TO 'Tom'@'localhost';
	GRANT ALL ON demo1.`account` TO 'Tom'@'localhost';

	# 给Jerry设置所有数据库所有表的所有权限
	GRANT ALL ON . TO 'Jerry'@'%';
	# 一般来讲，要先在mysql数据库中的user表下，使用CREATE USER语法创建用户，再进行权限授权。

	# 撤销权限
	# 语法：REVOKE 权限列表 on 数据库名.表名 from '用户名'@'主机名';
	REVOKE ALL ON demo1.`account` FROM 'Tom'@'localhost';
	REVOKE UPDATE ON . FROM 'Jerry'@'%';

	#1.创建stu表时,对name列添加非空约束
	CREATE table stu(
	id INT,
	NAME VARCHAR(20) NOT NULL #NAME非空
	);
	#2.测试：给name列插入null数据
	INSERT INTO stu(id) VALUES(3);
	#3.结果报错
	#报错原因：添加非空约束的字段不能为null
	Field 'NAME' doesn't have a default value

	#但如果name列没有设置非空约束，则可以添加null值
	#1.更改name列的类型，删除非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20);
	#2.执行成功，name为null时可以插入数据
	INSERT INTO stu(id) VALUES(3);

	#创建完stu表之后，对name列添加非空约束
	#1.删除name为null的数据
	DELETE FROM stu where id = 3;
	#2.更改name列的类型，设置非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
	#3.测试：给name列插入null数据
	#结果报错，添加非空约束的字段不能为null
	INSERT INTO stu(id) VALUES(3);