DDL语句
sql
/*
SQL的通用语法:
1. SQL语句可以写一行, 也可以写多行, 以 分号 结尾.
2. SQL语句不分区大小写, 建议: 关键字大写,其它小写.
3. 为了增加阅读性, 可以加入空格, 换行等操作SQL语句.
4. SQL语句注释写法:
/星
多行
注释
星/
-- 单行注释, 注意: --后边 必须加 空格.
# 单行注释, 注意: #后边 可以不加空格
SQL常用的数据类型:
整型: int
浮点型: double, decimal
字符型: varchar(n), char(n) 前者: 变长, 后者: 定长(不够用空格补齐)
日期型: date, datetime
*/
-- 我是单行注释
# 我是单行注释
# --------------------- 案例1: DDL语句操作数据库-增删改查(CURD) ---------------------
# Create: 增, Update: 改, Read: 查, Delete: 删
# 1. 查看所有的数据库. 格式: show databases;
show databases; # 查看所有的数据库
show create database day01; # 可以查看该(当前数据库)的码表.
# 2. 创建数据库. 格式: create database 数据库名 charset '码表';
create database day01; # 采用默认码表创建数据库.
create database day02 charset 'utf8'; # 采用 utf8码表创建数据库
create database day03 charset 'gbk'; # 采用 gbk码表创建数据库
create database if not exists day01; # 采用默认码表创建数据库, 如果不存在, 就创建数据表. 如果存在, 就啥都不做.
# 3. 修改数据库(的码表)
alter database day03 charset 'utf8'; # 修改day03数据库的码表为: u8
# 4. 删除数据库, 格式: drop database 数据库名;
drop database day02;
drop database day03;
# 5. 切换数据库.
use day01; # 切库, 表示后续的操作, 都是在当前这个库中完成的.
# 6. 查看当前使用的是 哪个数据库.
select database(); # day01
# 7. 查看数据库版本.
select version(); # 8.0.12
# --------------------- 案例2: DDL语句操作数据表-增删改查(CURD) ---------------------
# 前提: 先切库, 后续操作表, 都是在 数据库中操作的.
# 1. 查看数据表.
show tables; # 查看(当前数据库中)所有数据表
desc student; # 查看某张表的字段信息(字段名, 类型, 约束)
# 2. 创建数据表.
/*
格式:
create table [if not exists] 数据表名(
字段名 数据类型 [约束],
字段名 数据类型 [约束],
......
字段名 数据类型 [约束]
);
细节:
上述的中括号的内容是 可选项, 写不写都行.
*/
create table if not exists student(
sid int not null, # 学生id, int类型, 非空约束
name varchar(20), # 学生姓名, 字符串类型
gender varchar(10), # 学生性别.
age int # 学生年龄
);
# 3. 修改数据表的 名字. 格式: alter table 旧表名 rename to 新表名;
alter table student rename to stu;
# 4. 删除数据表.
drop table stu;
# --------------------- 案例3: DDL语句操作字段-增删改查(CURD) ---------------------
# 1. 查看表结构.
desc stu;
# 2. 修改表名.
alter table student rename to stu;
rename table student to stu; # 效果同上.
# 3. 给表 新增1列. 格式: alter table 表名 add 新列名 数据类型 [约束];
alter table stu add address varchar(10) not null;
# 4. 修改字段: 数据类型, 约束. address varchar(10) => address double
# 格式: alter table 表名 modify 旧列名 数据类型 [约束];
alter table stu modify address double;
# 5. 修改字段: 列名, 数据类型, 约束. address double => desc char(10) 非空约束
# 格式: alter table 表名 change 旧列名 新列名 数据类型 [约束];
alter table stu change address `desc` char(10) not null; # 列名如果和关键字重名了, 要用 反引号``包裹.
# 6. 删除某列. 例如: 删除 desc 列
# 格式: alter table 表名 drop 旧列名;
alter table stu drop `desc`;DML语句
sql
/*
DML语句介绍:
概述:
它主要是用来 操作 表数据的, 进行: 增, 删, 改 的操作.
增删改统称为: 更新语句.
关键词:
insert, delete, update
*/
# --------------------- 案例1: DML语句操作表数据-增 ---------------------
/*
添加表数据, 格式如下:
一次添加1条数据:
insert into 数据表名(列名1, 列名2...) values(值1, 值2...);
上述格式的语法糖, 如果是操作全列, 则: 可以简写成如下的写法.
insert into 数据表名 values(值1, 值2...);
一次性添加多条数据:
insert into 数据表名(列名1, 列名2...)
values
(值1, 值2...),
(值1, 值2...),
......
(值1, 值2...);
细节:
1. 列名 和 值, 个数, 对应类型等均要匹配.
2. 如果不写列名, 默认是: 全列名
3. 传值的时候, 数字可以直接写(小数, 整数均可), 其它要用单引号包裹.
*/
# 1. 切库
use day01;
# 2. 查看所有数据表.
show tables;
# 3. 建表, 如果表不存在.
create table if not exists stu(
sid int not null, # 学生id, int类型, 非空约束
name varchar(20), # 学生姓名, 字符串类型
gender varchar(10), # 学生性别.
age int # 学生年龄
);
# 4. 添加表数据
# 添加 单条数据, 1, 张三, 男, 23
insert into stu(sid, name, gender, age) values(1, '张三', '男', 23);
# 添加 单条数据, 2, 李四, 男, 24
insert into stu values(2, '李四', '男', 24); # 语法糖, 不写列名, 默认是: 全列名.
# 添加 单条数据, 3, 王五, 25
insert into stu(sid, name, age) values(3, '王五', 25);
insert into stu(name, age) values('赵六', 26); # 报错, sid列有非空约束, 必须传值, 不传就报错.
# 添加多条数据, (4, '阿朱', '女', 25), (5, '李清露', '女', 23), (6, '王语嫣', '女', 21)
insert into stu values
(4, '阿朱', '女', 25),
(5, '李清露', '女', 23),
(6, '王语嫣', '女', 21);
# 5. 查看表数据. 格式: select * from 数据表名;
select * from stu;
# --------------------- 案例2: DML语句操作表数据-改 ---------------------
# 格式: update 表名 set 列名1=新值, 列名2=新值, 列名3=新值... where 条件;
# 细节: 1. 改值的时候, 数据类型, 个数要匹配. 2. 进行修改,删除操作时, 一定一定一定要加where条件, 不然就是针对于全表数据做操作.
# 1. 查看表数据.
select * from stu;
# 2. 修改表数据 李四, 男, 24 => 杨过, 男, 31
update stu set name='杨过', age=31 where sid=2;
# 3. 一个非常危险的 "坐牢"命令.
update stu set name='杨过', age=31; # 不加where条件, 则会一次性更新表中所有的数据.
# --------------------- 案例3: DML语句操作表数据-删 ---------------------
/*
格式:
delete from 表名 where 条件;
细节:
1. 删除数据时, 一定一定一定要加where条件.
2. delete from 和 truncate table 都可以一次性清空表数据, 那么它们之间有什么区别?
delete from:
属于DML语句, 用于删除表数据的, 不会重置: 主键id.
可以结合事务一起使用.
truncate table:
属于DDL语句, 用于"清空"表数据的, 它相当于把表摧毁了, 然后创建1张和该表一模一样的新表.
即: 会重置之间id, 一般不结合 事务 一起使用.
*/
# 1. 查看表数据
select * from stu;
# 2. 删除 sid为奇数的 数据.
delete from stu where sid % 2 != 0;
# 3. 一次性删除所有的数据.
delete from stu;
truncate table stu; # table 还可以省略不写.
truncate stu; # 效果同上.
# --------------------- 案例4: DML语句操作表数据-删-是否会重置主键id ---------------------
# 1. 建表, 带: 主键约束(特点: 非空, 唯一), 且结合自动增长一起使用.
drop table student;
create table student(
sid int primary key auto_increment, # 学生id, 主键约束(非空, 唯一), 自动增长(只针对于数值有效)
name varchar(20), # 姓名
age int # 年龄
);
# 2. 查看表数据.
desc student;
select * from student;
# 3. 添加表数据.
insert into student values(1, '张三', 23);
insert into student values(1, '李四', 24); # 报错, 主键约束: 具有唯一性.
insert into student values(null, '李四', 24); # 报错, 主键约束: 具有非空性. 除非结合 自增 一起使用, 即: 不报错了
# 4. 两种删除的区别.
delete from student where sid >= 8;
delete from student; # 仅仅是清空表数据, 不会重置主键id
truncate student; # 会重置主键id.
# --------------------- 案例5: DML语句操作表数据-快速备份表数据 ---------------------
# 1. 查看表数据(即: 源表)
insert into student values(null, '王五', 25), (null, '赵六', 26);
select * from student;
# 2. 查看所有的数据表.
show tables;
# 3. 备份数据表.
# 场景1: 备份表不存在, 例如: 源表 student => 备份表 student_tmp
# 格式: create table 备份表名 select * from 原表名 where 条件;
create table student_tmp select * from student;
# 场景2: 备份表存在, 例如: 源表 student => 备份表 student_tmp
insert into student_tmp select * from student;
# 4. 查看备份表的数据.
truncate student_tmp;
select * from student_tmp;
# 5. 查看表结构.
desc student; # 源表结构
desc student_tmp; # 备份表结构
# --------------------- 案例6: 约束详解 ---------------------
/*
约束 介绍:
概述:
约束就使用来保证 数据的 完整性, 安全性 和 一致性的.
分类:
单表约束:
主键约束: primary key
特点: 非空, 唯一, 且一般结合自增(自动增长 auto_increment)一起使用.
非空约束: not null
特点: 不能为null, 可以重复.
唯一约束: unique
特点: 可以为空, 不能重复.
默认约束: default
特点: 如果添加数据时没有给该列指定值, 则用默认值.
多表约束:
外键约束: foreign key
特点: 外表的外键列 不能出现 主表的主键列 没有的数据.
*/
# 1. 建表, 英雄表hero(hid, name, age, phone, address)
create table hero(
id int primary key auto_increment, # id, 主键约束(非空, 唯一), 自增
name varchar(20) not null, # 名字, 非空约束.
age int, # 年龄
phone varchar(11) unique , # 手机号, 唯一约束.
address varchar(10) default '北京' # 住址, 默认约束
);
# 2. 查看表数据.
select * from hero;
# 3. 添加表数据.
insert into hero values(null, '乔峰', 39, '111', '上海'); # 对
insert into hero values(null, '乔峰', 39, '222', '广州'); # 对
insert into hero values(null, '虚竹', 31, null, '广州'); # 对
insert into hero values(null, '段誉', 29, null, '大理'); # 对
insert into hero(id, name) values(null, '小龙女'); # 对
insert into hero values(null, null, 23, '111', '上海'); # 报错, 姓名不能为空
insert into hero values(null, '乔峰', 39, '111', '广州'); # 报错, 手机号具有唯一性.
insert into hero values(null, '杨过', 26, null); # 报错, 值的个数 和 列的个数不匹配DQL语句
单表查询
sql
/*
前言:
我们学习SQL语句, 主要学习的就是DQL(数据查询语言), 又分为: 单表查询, 多表查询, 窗口函数.
一个完整的单表查询的语法格式如下:
select
[distinct] 列1 as 别名, 列2...
from
数据表名
where
组前筛选
group by
分组字段1, 分组字段2...
having
组后筛选
order by
排序的列1, 列2 [asc | desc]...
limit
起始索引, 数据条数;
*/
# --------------------- 案例1: 准备动作 ---------------------
# 1. 建库, 切库.
create database day02;
use day02;
show tables;
# 2. 建表, 添加表数据. ctrl + shift + 字母U 字母大小写切换.
# 创建商品表：
create table product(
pid int primary key auto_increment, # 商品id,
pname varchar(20), # 商品名
price double, # 商品价格
category_id varchar(32) # 商品所属的分类id
);
# 插入数据
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'海尔',3000, null);
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'真维斯',200,null);
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(null,'海澜之家',1,'c002');
# 3. 查询表数据.
select * from product;
# --------------------- 案例2: 简单查询 ---------------------
# 格式: select 列名1, 列名2... from 表名;
# 1.查询所有的商品.
select pid, pname, price, category_id from product;
select * from product; # 上述格式的语法糖, 即: *在这里代表 全列名
# 2.查询商品名和商品价格.
select pname, price from product;
# 3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname, price + 10 from product;
# 4. 别名查询, 列名, 表名都可以起别名. 格式: 列名/表名 as 别名.
select pname, price + 10 as price from product;
select pname, price + 10 price from product; # as 可以省略不写
select pname 商品名, price + 10 商品价格 from product; # 别名还可以是中文
# 5. 去重查询.
# 需求1: 查询所有的商品价格.
select distinct price from product;
# 需求2: 查看商品分类, 及其对应的价格.
# 这样写是把 商品分类id 和 商品价格 当做一个整体来去重的. 即: c001, 5000 和 c001,3000 不是同一条数据.
select distinct category_id, price from product;
# --------------------- 案例3: 条件查询 ---------------------
/*
条件查询, 格式为:
select ... from 数据表名 where 条件;
条件查询, 几种写法, 即: where后边可以跟什么:
比较运算符: >, >=, <, <=, =, !=, <>
范围查询:
区间校验: between 起始值 and 结束值
固定值校验: 列名 in (值1, 值2...)
模糊查询: 列名 like '_%'
非空查询: is null 和 is not null
逻辑运算符: and, or, not
*/
# 1.查询商品名称为“花花公子”的商品所有信息：
select * from product where pname = '花花公子';
# 2.查询价格为800商品
select * from product where price=800;
select * from product where price in (800);
# 3.查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800; # 效果同上
select * from product where not price=800; # 效果同上
select * from product where price not in (800); # 效果同上
# 4.查询商品价格大于60元的所有商品信息
select * from product where price > 60;
# 5.查询商品价格小于等于800元的所有商品信息
select * from product where price <= 800;
# 6.查询商品价格在200到800之间所有商品
select * from product where price between 200 and 800; # 包左包右
select * from product where price >= 200 and price <= 800;
# 7.查询商品价格是200或800的所有商品
select * from product where price in (200, 800);
select * from product where price=200 or price=800;
# 8.查询以'香'开头的所有商品
select * from product where pname like '香%'; # 模糊查询规则, _表示任意的1个字符, %表示多个任意的字符.
select * from product where pname like '香__'; # 查询以香开头, 一共3个字符的内容.
# 9.查询第二个字为'想'的所有商品
select * from product where pname like '_想%';
# 10.查询没有分类的商品
select * from product where category_id = null; # 不会报错, 结果不是我们要的.
select * from product where category_id is null; # 正确写法.
# 11.查询有分类的商品
select * from product where category_id is not null; # 正确写法.
# --------------------- 案例4: 排序查询 ---------------------
# 格式: select ... from 表名 where ... order by 排序字段1 [asc | desc], 排序字段2 [asc | desc]...
# 1.使用价格排序(降序)
select * from product order by price; # 默认是: 升序
select * from product order by price asc; # asc(ascending), 表示: 升序, 可以省略不写.
select * from product order by price desc; # desc: 降序
# 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc, category_id desc; # desc: 降序
# --------------------- 案例5: 聚合查询 ---------------------
/*
聚合查询 介绍:
概述:
之前我们写的查询都是一行行操作的, 聚合查询是 一列一列来操作的.
例如:
计算某列的非空值的数据总条数, 某列最大值/最小值...
分类:
count() 一般用于统计行数.
sum() 求和
max() 求最大值
min() 求最小值
avg() 求平均值
细节:
1. 面试题: count(1), count(*), count(列) 区别是什么?
区别1: 是否统计null值.
count(1), count(*): 统计
count(列): 不统计.
区别2: 效率问题
count(主键列) > count(1) > count(*) > count(普通列)
*/
# 1、查询商品的总条数
select count(*) as total_cnt from product; # 统计数据条数, 包括: null值, 13条
select count(1) as total_cnt from product; # 统计数据条数, 包括: null值, 13条
select count(pid) as total_cnt from product; # 统计数据条数, 只统计该列的 非空值, 13条
select count(category_id) as total_cnt from product; # 统计数据条数, 只统计该列的 非空值, 11条
# 2、查询价格大于200商品的总条数
select count(pid) from product where price > 200;
# 3、查询分类为'c001'的所有商品的总和
select sum(price) as total_price from product where category_id='c001';
# 4、查询分类为'c002'所有商品的平均价格
select * from product where category_id='c002';
select avg(price) as avg_price from product where category_id='c002';
select round(avg(price), 1) as avg_price from product where category_id='c002';
# 扩展: 四舍五入, 保留指定位数的小数.
select round(13.12345, 4); -- 13.1235
select round(13.12345, 2); -- 13.12
select round(810.25, 1); -- 810.3
# 5、查询商品的最大价格和最小价格
select max(price) max_price, min(price) min_price from product;
# --------------------- 案例6: 分组查询 ---------------------
/*
分组查询 介绍:
概述:
分组查询 = 按照分组字段, 把数据划分成n个组, 然后再各组中做 (聚合)统计, 例如: 每组的总条数, 某列最大值/最小值...
格式:
select
分组字段, 聚合函数...
from
数据表名
where
组前筛选
group by
分组字段
having
组后筛选;
细节:
1. 分组查询的查询列: 只能出现 分组字段 和 聚合函数.
2. where 和 having的区别:
区别1: 作用时机不同.
where: 组前筛选
having: 组后筛选.
区别2: 后边是否可以跟 聚合函数
where: 不可以
having: 可以
*/
# 1.统计各个分类商品的个数
select category_id, count(pid) total_cnt from product group by category_id;
# 2.统计各个分类商品的个数, 且只显示个数大于1的信息
select category_id, count(pid) total_cnt from product group by category_id having total_cnt > 1;
# 3. 统计商品表中, 各分类商品的总个数, 只显示个数小于3的信息, 且只统计价格在100以上的商品, 并按照商品个数降序排列, 如果(每个分类)商品个数一致, 按照分类id 升序排列.
select
category_id, count(pid) total_cnt
from
product
where
price > 100 # 组前筛选, 价格在100以上的
group by
category_id # 按 商品分类id 分组
having
total_cnt < 3 # 组后筛选, 只统计 商品数量 小于 3的 分组信息
order by
total_cnt desc, category_id; # 按照每个分类商品个数降序排列, 如一致, 按照分类id升序排列.
# 4. 统计商品表中, 各分类商品的总价格, 只统计单价在200以上的商品信息, 且只显示总金额大于 2000 的数据, 按照总金额降序排列.
select category_id, sum(price) total_price from product where price > 200 group by category_id having total_price > 2000 order by total_price desc;
# --------------------- 案例7: 分页查询 ---------------------
/*
分页查询:
概述:
就是分批次从数据表中获取数据, 一方面可以降低服务器压力, 另一方面可以降低浏览器端解析数据的压力, 且能提高用户体验.
格式:
limit 起始索引, 数据条数;
细节:
1. SQL表中, 每条数据都是有索引的, 且索引是从 0 开始的, 即: 第1条数据, 索引为0, 第2条数据, 索引为1...
2. 和分页相关的四个名词:
数据总条数: count()函数
每页数据条数: 产品经理, 项目经理, 你...
每页起始索引: (当前页数 - 1) * 每页的数据条数
总页数: (数据总条数 + 每页的数据条数 - 1) / 每页的数据条数 注意: 这里是整除, 即: 只保留整数部分.
例如: (13 + 3 - 1) / 3 = 15 / 3 = 5
例如: (13 + 5 - 1) / 3 = 17 / 5 = 3
*/
# 1. 查看原表数据.
select * from product;
# 2. 只获取前5条数据.
select * from product limit 0, 5; # 从索引0开始, 获取 5条数据
select * from product limit 5; # 语法糖, 索引如果从0开始, 则可以省略不写.
# 3. 每页5条, 获取第2页, 第3页数据.
select * from product limit 5, 5; # 从索引5(第6条)开始, 获取 5条数据
select * from product limit 10, 5; # 从索引10(第11条)开始, 获取 5条数据
# 4. 每页7条, 如何获取?
select * from product limit 0, 7; # 第1页
select * from product limit 7, 7; # 第2页
# 5. 每页3条, 如何获取?
select * from product limit 0, 3; # 第1页
select * from product limit 3, 3; # 第2页
select * from product limit 6, 3; # 第3页
select * from product limit 9, 3; # 第4页
select * from product limit 12, 3; # 第5页多表查询
sql
/*
回顾: MySQL是关系型数据库, 表与表之间是有关系的, 具体如下:
一对多:
建表原则:
在多的一方新建一列, 充当外键列, 去关联一的一方的主键列.
例如:
部门表 和 员工表, 分类和商品...
多对多:
建表原则:
新建中间表, 该表至少有2列, 充当外键列 分别去关联多的两方的主键列.
例如:
学生表 和 选修课表
一对一:
建表原则:
思路1: 主键对应
思路2: 唯一外键关联.
思路3: 把数据直接放到1张表.
例如:
1个人有1个身份证号
1家公司注册地址只能有1个
外键约束详解:
概述:
它属于多表约束, 用于保证 数据的完整性 和 一致性.
格式:
添加外键约束:
alter table 外表名 add constraint 外键约束名 foreign key(外键列名) references 主表名(主键列名);
删除外键约束:
alter table 外表名 drop foreign key 外键约束名;
特点:
外表的外键列 不能出现 主表的主键列, 没有的数据.
*/
# --------------------- 案例1: 多表约束-外键约束 ---------------------
# 1. 切库, 查表.
use day02;
show tables;
# 2. 创建 部门表(dept), id, name
drop table dept;
create table if not exists dept(
id int primary key auto_increment, # 部门id
name varchar(10) # 部门名
);
# 3. 创建 员工表(employee), id, name, gender, salary, dept_id
drop table employee;
create table if not exists employee(
id int primary key auto_increment, # 员工id
name varchar(20), # 员工姓名
gender varchar(2), # 员工性别
salary double, # 员工工资
dept_id int # 员工所属的部门id
# constraint fk01 foreign key (dept_id) references dept(id) # 添加外键约束, 属于: 建表时添加
);
# 4. 设置 部门表 和 员工表的 外键约束.
alter table employee add constraint fk01
foreign key (dept_id) references dept(id);
# 5. 往表中添加数据.
# 添加部门表 信息
insert into dept values(null, '人事部'), (null, '研发部'), (null, '财务部'), (null, '行政部');
# 添加员工信息
insert into employee values(null, '乔峰', '男', 5000.5, 1), (null, '虚竹', '男', 33333.3, 2);
insert into employee values(null, '段誉', '男', 50000.6, 10); # 非法数据, 因为根本就没有编号为10的部门
# 6. 查看表数据.
select * from dept; # 部门表
select * from employee; # 员工表
# 7. 删除外键约束(了解)
alter table employee drop foreign key fk01;
# --------------------- 案例2: 多表建表-多对多 ---------------------
# 原则: 新建中间表, 该表至少有2列, 充当外键列, 分别去关联多的两方的主键列.
# 1. 建表
# 学生表
create table student(
sid int primary key auto_increment, # 学生id
name varchar(10) # 学生姓名
);
# 选修课表
create table course(
cid int primary key auto_increment, # 课程id
name varchar(10) # 课程名
);
# 学生-选修课关系表, 即: 中间表.
create table stu_cur(
id int unique not null auto_increment, # 自身id, 伪主键(非空, 唯一), 自增
sid int, # 学生id
cid int # 选修课id
);
# 2. 添加外键约束.
# 2.1 学生表 和 中间表
alter table stu_cur add constraint fk_stu_mid foreign key (sid) references student(sid);
# 2.2 课程表 和 中间表, 这个我们不给外键约束起名字了, 让程序自动生成.
alter table stu_cur add foreign key (cid) references course(cid);
# 3. 设置中间表的 学生id(sid) 和 选修课id(cid) 为: 联合主键.
alter table stu_cur add primary key(sid, cid);
# 4. 添加表数据.
# 4.1 学生表.
insert into student values(null, '乔峰'), (null, '虚竹'), (null, '段誉');
# 4.2 选修课表
insert into course values(null, 'AI人工智能'), (null, 'Py大数据'), (null, '鸿蒙');
# 4.3 中间表.
insert into stu_cur values(null, 1, 1); # 乔峰, AI
insert into stu_cur values(null, 1, 2); # 乔峰, 大数据
insert into stu_cur values(null, 2, 1); # 虚竹, AI
insert into stu_cur values(null, 2, 1); # 虚竹, AI, 报错.
# 5. 查看表数据.
# 4.1 学生表.
select * from student;
# 4.2 选修课表
select * from course;
# 4.3 中间表.
select * from stu_cur;
/*
交叉查询:
格式:
select * from A, B;
结果:
两张表的 笛卡尔积.
连接查询:
内连接:
格式:
select * from A inner join B on 关联条件; # inner可以省略不写.
select * from A, B where 关联条件;
结果:
表的 交集.
外连接:
左外连接: select * from A left outer join B on 关联条件; # outer可以省略不写
结果:
左表的全集 + 交集
右外连接: select * from A right outer join B on 关联条件; # outer可以省略不写
结果:
右表全集 + 交集
子查询:
概述:
1个SQL语句的查询条件, 需要依赖另1个SQL语句的查询结果, 外边的查询称之为: 主查询(父查询)
里边的查询称之为: 子查询.
格式:
# 父查询(主查询) 子查询
select * from A where 列名 > (select 列名 from 表名 where...);
扩展: 自连接查询(自关联查询)
就是在连接查询的基础上, 表自己和自己做关联查询, 例如: select * from A left join A on 关联条件;
多表查询的精髓:
想办法把多张表"拼接成"一张表, 然后对该表 进行"单表查询".
*/
# --------------------- 案例3: 多表查询-交叉查询 ---------------------
# 1. 建表.
# 创建hero表
CREATE TABLE hero (
hid INT PRIMARY KEY,
hname VARCHAR(255),
kongfu_id INT
);
# 创建kongfu表
CREATE TABLE kongfu(
kid INT PRIMARY KEY,
kname VARCHAR(255)
);
# 2. 添加表数据.
# 插入hero数据
INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
# 插入kongfu数据
INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');
# 3. 查看表数据.
select * from hero; # 英雄表.
select * from kongfu; # 功夫表.
# 4. 演示 交叉查询. 查询结果是: 表的笛卡尔积, 即: 表A的总数 * 表B的总数.
select * from hero, kongfu; # 16条
# --------------------- 案例4: 多表查询-连接查询 ---------------------
# 场景1: 内连接, 查询结果是: 表的交集.
# 显式内连接, select * from A inner join B on 关联条件.
select * from hero h inner join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h join kongfu kf on h.kongfu_id = kf.kid; # 语法糖, inner可以省略不写.
# 隐式内连接, select * from A, B where 关联条件.
select * from hero h, kongfu kf where h.kongfu_id = kf.kid;
# 场景2: 外连接.
# 左外连接: 查询结果是 左表全集 + 交集.
# 格式: select * from A left outer join B on 关联条件.
select * from hero h left outer join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h left join kongfu kf on h.kongfu_id = kf.kid; # 语法糖, outer可以省略不写.
# 右外连接: 查询结果是 右表全集 + 交集.
# 格式: select * from A right outer join B on 关联条件.
select * from hero h right outer join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h right join kongfu kf on h.kongfu_id = kf.kid; # 语法糖, outer可以省略不写.
# --------------------- 案例5: 多表查询-子查询 ---------------------
# 概述: 一个SQL语句的查询条件, 需要依赖另1个SQL语句的查询结果, 这种写法就称之为: 子查询.
# 主查询(父查询) 子查询
# 格式: select ... from 表名 where 字段 in (select 列名 from 表名...);
# 1. 建表.
create table category ( # 商品分类表
cid varchar(32) primary key , # 分类id
cname varchar(50) # 分类名
);
create table products( # 商品表
pid varchar(32) primary key , # 商品id
pname varchar(50), # 商品名
price int, # 商品价格
flag varchar(2), # 是否上架标记为：1表示上架、0表示下架
category_id varchar(32), # 分类id
constraint products_fk foreign key (category_id) references category (cid) # 建表时, 添加: 外键约束
);
# 2. 添加表数据.
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
# 3. 查看表数据.
select * from category; # 分类表
select * from products; # 商品表
# 4. 查询哪些分类的商品已经上架.
select distinct c.cid, c.cname, flag from category c left join products p on c.cid = p.category_id;
select distinct c.cid, c.cname, flag from category c inner join products p on c.cid = p.category_id;
# 5. 查询所有分类商品的个数.
# 根据 分类商品的id, 名字 进行分组.
select cid, cname, count(cid) as total_cnt from category c left join products p on c.cid = p.category_id group by cid, cname;
# 6. 查询"化妆品"分类, 上架商品详情.
# select * from products where category_id = '化妆品 分类的id';
# step1: 获取化妆品的 分类id
select cid from category where cname='化妆品';
# step2: 把上边的查询结果, 作为下边SQL语句的 条件
select * from products where category_id = 'c003';
# 合并版: 子查询.
# 父查询/主查询 子查询
select * from products where category_id = (select cid from category where cname='化妆品');
# --------------------- 案例6: 自关联查询 ---------------------
/*
自关联查询介绍:
概述:
自关联查询也叫自连接查询, 即: 表自己 和 表自己做关联查询, 获取结果.
典型应用:
省市区三级联动.
格式:
select * from 表A join 表A on 关联条件...;
select * from 表A left join 表A on 关联条件...;
*/
# 1. 导入数据, area.sql, 记录的市: 行政区域表的信息.
# 2. 查看表数据.
select * from areas limit 10; # id: 自身id, title: 自身名称. pid:父级id
# 3. 初始表数据.
select * from areas where pid is null; # 省, 直辖市级别
# 河南省所有的市.
select * from areas where pid = '410000'; # 河南省所有的市
# 新乡市所有的 县区
select * from areas where pid = '410700'; # 新乡市所有的 县区
# 4. 查看 所有省, 所有市, 所有县区的信息.
select
province.id, province.title, # 省的id, 名字
city.id, city.title, # 市的id, 名字
county.id, county.title # 县区的id, 名字
from
areas as province # 省
left join areas as city on city.pid = province.id # 市, 市的父级id = 省的id
left join areas as county on county.pid = city.id; # 县区, 县区的父级id = 市的id
select
province.id, province.title, # 省的id, 名字
city.id, city.title, # 市的id, 名字
county.id, county.title # 县区的id, 名字
from
areas as province # 省
left join areas as city on city.pid = province.id # 市, 市的父级id = 省的id
left join areas as county on county.pid = city.id # 县区, 县区的父级id = 市的id
where county.id = '350427';
# --------------------- 案例7: 窗口函数 ---------------------
/*
窗口函数介绍:
概述:
它是MySQL8.0的特性, 窗口函数有很多, 常用的有:
row_number(): 可以理解为: 行号, 即: 1, 2, 3, 4...
rank(): (稀疏)排名, 会跳跃. 1, 2, 2, 2, 5...
dense_rank() (密集)排名, 不会跳跃. 1, 2, 2, 2, 3...
精髓:
窗口函数 = 给表新增1列, 至于新增的是什么, 取决于 用哪个窗口函数.
格式:
窗口函数 over(partition by 分组字段 order by 排序字段 asc | desc)
细节:
1. 学窗口函数, 首先要掌握两个知识点.
A. 分组排名.
B. 分组排名求TopN
2. where后边写的字段, 必须是表中已有的字段.
*/
# 1. 准备数据, 建表, 添加数据.
create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2));
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);
insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);
insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);
# 2. 查看数据.
select * from employee;
# 3. 分组排名. 假设数据是: 100, 90, 90, 80, 则: row_number是 1, 2, 3, 4 rank是 1, 2, 2, 4 dense_rank()是 1, 2, 2, 3
select
*,
row_number() over (partition by deptid order by salary desc) as rn, # 根据部门id分组, 组内按照工资降序排名
rank() over (partition by deptid order by salary desc) as rk, # 根据部门id分组, 组内按照工资降序排名
dense_rank() over (partition by deptid order by salary desc) as dr # 根据部门id分组, 组内按照工资降序排名
from employee;
# 4.分组排名, 求TopN, 例如: 每个部门, 工资最高的2人.
select * from (
select
*,
rank() over (partition by deptid order by salary desc) as rk # 根据部门id分组, 组内按照工资降序排名
from employee
) as t1 # 把查询结果, 封装成一张新表, 后续就可以用 rk这个字段了.
where rk <= 2;SQL的重点请看这里
DDL的创建表语句
sql
# 建表语句的语法
create table 表名(
字段1 字段1类型 [约束] [comment 字段1注释 ],
字段2 字段2类型 [约束] [comment 字段2注释 ],
......
字段n 字段n类型 [约束] [comment 字段n注释 ]
) [ comment 表注释 ] ;
# 创建学生表
create table if not exists student(
id int primary key auto_increment comment '主键id',
stu_no varchar(20) not null comment '学号',
name varchar(20) not null comment '姓名',
age tinyint unsigned comment '年龄',
gender char(1) default '男' comment '性别',
class_name varchar(30) not null comment '班级',
phone varchar(11) comment '手机号',
create_time datetime default now() comment '创建时间',
# on update是在修改的时候自动更新时间
update_time datetime default now() on update now()comment '更新时间'
) comment '学生表';在新建表的时候，有下面这几种约束：
| 约束 | 描述 |
|---|---|
| 非空约束 | 限制该字段值不能为null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 |
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
主键自增：auto_increment
- 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
- 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
DML语句-增删改
数据库表准备如下：
sql
-- 员工表
create table `employee` (
`id` int unsigned primary key auto_increment comment '主键',
`name` varchar(10) not null comment '姓名',
`gender` tinyint unsigned not null comment '性别, 1:男, 2:女',
`job` tinyint unsigned comment '职位, 1:班主任, 2:讲师, 3:学工主管, 4:教研主管, 5:咨询师',
`salary` int unsigned not null comment '薪资',
`entry_date` date comment '入职日期',
`create_time` datetime comment '创建时间',
`update_time` datetime comment '更新时间'
) comment '员工信息表';添加数据
sql
# 指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
# 全量字段添加数据，注意：在实际的最佳实践中不推荐
insert into 表名 values (值1, 值2, ...);
# 批量指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
# 批量全量字段添加数据
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
-- 1. 为 employee表的 name, gender, job, salary字段插入值
insert into employee(name, gender, job, salary) values('张三', 1, 1, 5000);
-- 2. 为 employee表的 所有字段插入值
insert into employee values (null, '花千骨', 1, 1, 5800, '2025-01-01', now(), now());
-- 3. 为 employee表的指定字段批量插入值
insert into employee(name, gender, job, salary) values
('窦燕山', 1, 1, 5000),
('霍元甲', 1, 1, 5500),
('钟武迪', 1, 1, 6000);
# 有些公司在开发中不允许省略字段名，因为如果后期针对这张表添加或者删除了一个字段，那么之前的insert语句就错了。
-- 4. 为 employee表的所有全量字段插入值
insert into employee VALUES (null, '李四', 1, 1, 5000, '2023-01-01', now(), now());
insert into employee(name, gender, job, salary) values
('窦燕山', 1, 1, 5000),
('霍元甲', 1, 1, 5500),
('钟武迪', 1, 1, 6000);修改数据
sql
# 语法
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
# - 案例1：将 employee 表的ID为1的员工 姓名更新为 '张三丰', 性别更新为男;
update employee set name = '张三丰', gender = 1, update_time=now() where id = 1;
# - 案例2：将 emp 表的所有员工的入职日期更新为 '2010-01-01'
update employee set entry_date='2010-01-01', update_time=now();
# - 案例3：将 employee 表的所有员工, 每人涨薪500
update employee set salary=salary+500, update_time=now();注意事项:
- 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
- 在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。
删除数据
sql
# 语法
delete from 表名 [where 条件] ;
# - 案例1：删除emp表中id为1的员工
delete from employee where id = 1;
# - 案例2：删除employee表中所有员工
delete from employee;注意事项:
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
- 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute(执行)即可。