SQL笔记纯干货

AI入门修炼

已于 2024-08-29 21:38:34 修改

阅读量1k

点赞数 32

文章标签： oracle 数据库 sql

于 2024-08-29 21:34:44 首次发布

本文链接：https://blog.csdn.net/jam433/article/details/141680220

版权

软件：DataGrip 2023.2.3，phpstudy_pro, MySQL8.0.12

1. DDL语句（数据定义语句）

操作数据库，数据表，字段

CURD（create，drop，alter，show）

1.1 数据库操作语言

show databases;                         # 查看数据库
create database if not exists sql01 character set 'utf8';   # 建库，并采用指定码表（默认码表为utf8）
create database if not exists sql01 charset 'utf8';         #效果同上
create database if not exists sql01 charset 'gbk';          #如果数据库存在，就什么都不做，即不报错
alter database sql01 charset 'utf8';    # 修改数据库（的码表）
drop database sql01;                    # 删除数据库
select database();                      # 查看当前在使用哪个数据库
show create database sql01;             # 查看当前数据库的码表
use sql01;                              # 切换数据库sql01

1.2 数据表操作语言

show tables;                            # 查看当前数据库中所有的数据表
create table if not exists users(
    id int primary key auto_increment,     # id列，int类型，    单表约束：主键（默认非空），自增
    name varchar(10) unique not null,      # 姓名                     唯一（可以为空），非空（可以重复）
    age int default 18,                    # 年龄                       默认约束为default 不传值时用默认值18
    gender varchar(4)                      # 性别              多表约束：外键约束foreign key
);
show create table users;                # 查看数据表的详细信息（建表过程）
desc users;                             # 查看数据表的结构（列名，数据类型，约束等）
alter table users rename users01;       # 修改表名为users01
rename table users01 to users;          # 修改表名为users
drop table users;                       # 删除数据表
alter table users add address varchar(10) not null;                  # 新增字段，add新列名 数据类型 [约束]
alter table users modify address int;                                # 只修改字段的数据类型和约束
alter table users change address 'desc' varchar(50) not null;        # 修改列名 数据类型 约束，如果列名和关键字重名，用反引号
alter table users drop `desc`;                                       # 删除指定的列

2. DML语句（数据操作语言）

操作表数据，增、删、改，统称为更新语句

insert，delete，update

2.1 增删改

insert into users(id, name, age, gender) values(1, '乔峰', 33, '男');        # 添加全列
insert into users values(2, '虚竹', null, null), (null, '段誉', 34, '男');    # 语法糖（语法格式的简化版），同时添加多条数据
insert into users(name, gender) values('段誉', '男');                        # 给指定的列添加数据
update users set name = '段誉', age = 25 where id = 3;                       # 修改表数据，一定要加where
delete from users where id % 2 = 0;                                          # 删除表数据，一定要加where
delete from users;                                                           # 删除表，不重置主键，DML语句，可以结合事务使用
truncate users;                                                              # 删除表，重置主键，DDL语句，不可以结合事务使用

2.2 题

delete from 和 truncate table之间的区别是什么？

区别1：是否会重置主键id

delete from ：仅仅是删除数据，不会重置主键id

truncate table ：相当于把表摧毁了，然后创建一张和原表一模一样的新表，会重置主键id

区别2：本质不同

delete from ：属于DML语句，一般可以结合事务一起使用

truncate table ：属于DDL语句，一般不结合事务一起使用

2.3 备份表

create table users01 select * from users;                     # 备份表不存在，备份表
insert into users01 select * from users where id >= 2;        # 备份表存在，备份表

3. DQL语句（数据查询语言）

操作表数据，查询操作

select，from，where

3.1 查询操作

简单查询、条件查询、聚合查询、排序查询、分组查询、分页查询

select * from users;              # 简单查询

select
    distinct id, name, age,       # 去重查询，按照distinct之后的字段 去重
    gender - 1  as 性别,
    round(avg(age),2)             # 聚合查询，max min sum avg count，round()保留3位小数
from
    users
where                             # 条件查询，比较运算符> >= < <= = != <> 范围筛选in() not in() between and（包左包右）
    gender = '男'                 # 模糊查询like '_值'; like '%值'; 非空查询is null; is not null 逻辑运算符and or not
group by                          # 分组查询，格式为select 分组字段,聚合函数 from 数据表名 where 组前筛选
    age                           # group by 分组的列,和分组列一一对应的字段 having 组后筛选
having
    age > 20
order by                          # 排序查询，升序asc、降序desc，默认升序
    id desc, name asc
limit                             # 分页查询，起始索引,数据条数，编号从0开始，总页数=(总条数+每页的数据条数-1)/（整除）每页的数据条数
    1, 3;                         # 语法糖，limit 5; 不写起始索引，默认从0开始

select ceil(3.1);                 # 天花板数，向上取整，即比这个数字大的所有数字中（包括自身），最小的那个整数

3.2 题一

count(1), count(列), count(*)的区别是什么？

区别1：是否统计null值

count(1), count(*)：会统计null值

count(列)：不会统计null值，即只统计非空值的个数

区别2：效率问题

count(主键列) > count(1) > count(*) > count(列)

3.3 题二

having和where的区别是什么？

where：组前筛选，后边不能跟聚合函数

having：组后筛选，后边可以跟聚合函数

4. 多表详解

一对多：一个部门可以有多个员工，1个员工只能属于1个部门

建表原则：在多的一方新建1列，充当外键列，去关联少的一方的主键列

多对多: 学生和选修课

建表原则: 新建中间表, 该表至少有2列, 分别去关联多的两方的主键列. 需要设置中间表的两个外键列为联合主键,防止出现重复值的情况.

如果中间表也想有自己的主键列, 可以把它设置成伪主键.

4.1 一对多

# 新建部门表
create table dept(
    id int primary key auto_increment,  # 部门id
    name varchar(20)                    # 部门名称2
);
# 往部门表中添加数据  添加结果为1,2,3,4号部门
insert into dept values(null, '行政部'), (null, '研发部'), (null, '财务部'), (null, '人事部');
# 新建员工表
create table employee(
    id int primary key auto_increment,  # 员工id
    name varchar(10),                   # 员工姓名
    age int,                            # 员工年龄
    did int                             # 员工的部门id
    ,constraint fk01 foreign key(did) references dept(id)                              # 场景1: 建表时, 直接添加外键约束.
);          # [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名)
            # alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名)
alter table employee add constraint fk_dept_emp foreign key(did) references dept(id);  # 场景2: 建表后, 添加约束
# 往 员工表添加数据.
insert into employee values(null, '乔峰', 31, 4);
insert into employee values(null, '鸠摩智', 50, 10);          # 没有 10号部门.
delete from dept where id = 1;                               # 可以删.
delete from dept where id = 4;                               # 不能删, 4部门有人, 即: 外表还在用这个值.
alter table employee drop foreign key fk_dept_emp;           # 删除外键约束.

4.2 多对多

# 创建学生表和课程表
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 not null unique auto_increment,  # 自身id, 伪主键(非空, 唯一) + 自增
    sid int,        # 学生id
    cid int,        # 课程id
    primary key (sid, cid)                  # 情景1：建表时直接设置 学生id, 课程id为: 联合主键
);
# 添加外键约束，关联中间表和学生表，关联中间表和课程表
alter table stu_cur add constraint fk_mid_stu foreign key(sid) references student(sid);     # 场景2：建表后，添加约束
alter table stu_cur add foreign key(cid) references course(cid);                            # 这里不指定外键约束名字，由系统自动生成
alter table stu_cur add primary key(sid, cid) ;                                             # 设置中间表的2个外键列为联合主键
# 尝试往上述的三张表中添加数据.
# 学生表
insert into student values(null, '张三'), (null, '李四'), (null, '王二麻子');
# 课程表
insert into course values(null, 'AI人工智能'), (null, 'Py大数据'), (null, '鸿蒙');
# 中间表
insert into stu_cur values(null, 1, 1), (null, 1, 2), (null, 2, 1);
insert into stu_cur values(null, 2, 1);     # 不行, 因为(2,1)已经存在了, 它们是 联合主键

5. 多表查询

交叉查询: 两张表的笛卡尔积, 表A的总数 * 表B的总数

连接查询: 内连接, 两张表的交接

外连接, 左外连接表A的全集 + 两张表的交集, 右外连接表B的全集 + 两张表的交集

子查询: 一个sql语句的查询条件, 需要依赖另一个sql语句的查询结果. 里面的查询叫子查询, 外面的查询叫父查询(主查询).

# 建表
create table hero(
    hid   int primary key auto_increment,   # 英雄id
    hname varchar(255),     # 英雄名字
    kongfu_id int           # 功夫id
);                                                          # 创建英雄表
create table kongfu(
    kid     int primary key auto_increment, # 功夫id
    kname   varchar(255)        # 功夫名
);                                                          # 创建功夫表
insert into hero values(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);           # 在英雄表添加数据
insert into kongfu values(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');      # 在功夫表添加数据
select * from hero, kongfu;                                                          # 交叉查询，笛卡尔积
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 hero h, kongfu kf where h.kongfu_id = kf.kid;                          # 隐式内连接
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;                          # 语法糖
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;                         # 语法糖
select * from hero where kongfu_id = (select kid from kongfu where kname = '降龙十八掌');      # 子查询
# 自关联（自连接）查询，即使用一张表充当表副本1、表副本2...进行连接
select
    province.id, province.title,    # 省的信息
    city.id, city.title,            # 市的信息
    county.id, county.title         # 县区的信息
from
    areas as province
join
    areas as city on city.pid = province.id     # 市的父id = 省的id
join
    areas as county on county.pid = city.id;     # 县区的父id = 市的id
# case when，语法糖：将category_id提取到when之前
select
    *,
    case
        when category_id = 'c001' then '电脑'
        when category_id = 'c002' then '衣服'
        when category_id = 'c003' then '护肤品'
        when category_id = 'c004' then '零食'
        when category_id = 'c005' then '饮品'
        else '未知'
    end as category_name
from
    day02.product;

6. 窗口函数

窗口函数也叫开窗函数, mysql8.x的新特性, 主要用于分组排名, 聚合, 获取指定值, 给表新增1列

排序函数有, row_number(), rank(), dense_rank(), ntile(n)

聚合函数相关有, sum/max/min/count/avg() over(...)

其他函数相关有, lag(), lead(), first_value(), last_value()

select *, row_number() over(partition by age order by name) from employee;

7. 拓展: upsert

upsert语法介绍:

可以看作是insert + update的结合体, 如果主键不重复就添加, 重复就修改

insert into hero values(4, '王五', 16) on duplicate key update name = '李四', weight = 5; # 主键id为4时，已有数据，因此修改为name为李四，weight为5

8. sql注入攻击演示

概述: 实际开发中,如果我们的sql语句是拼接的, 即有些内容是需要用户传入的, 如果用户输入一些非法值, 就会改变我们的sql语句结构, 从未引发一系列安全问题, 这个安全问题就叫做: sql注入攻击

例子: 模拟用户登录

格式: select * from users where uname = '用户录入的账号' and pwd = '用户录入的密码';

用户传入的值:

听话的用户录入的账号: asdf 录入的密码: qweqwe

程序员小白录入的账号: asdf 录入的密码: qwe' or '1 = 1

程序员大白录入的账号: asdf 录入的密码: qwe' or '1 = 1'; drop datebase day01;'

# 1. 创建用户表.
drop table users01;
create table users01(
    uid int primary key auto_increment,
    uname varchar(20),
    pwd varchar(20)
   # ,tmp1 varchar(100),
   # tmp2 varchar(100)
);
# 2. 添加表数据.
insert into users01 values(null, 'admin01', 'pwd111'), (null, 'admin02', 'pwd222');
# 3. 查看表数据.
select * from users01;
# 4. 模拟登陆.
select * from users01 where uname = '用户录入的账号' and pwd = '用户录入的密码';
# 5.传入用户录入的值
# select * from users where unmae = 'asf' and pwd = 'qwer ' or ' 1=1';drop database day03;'';

9. 拆表

# 1. 建表.
drop table goods;
create table goods(
    id int primary key auto_increment,
    name varchar(30),
    cate_name varchar(20),
    barnd_name varchar(20),
    price int,
    is_show int,
    is_saleoff int
);
# 2. 插入表数据.
insert into goods values
    (null, 'mac book pro1', '台式机', 'Apple', 18888, 1, 0),
    (null, 'mac book pro2', '台式机', 'Apple', 16666, 1, 0),
    (null, '拯救者15', '笔记本', '联想', 10000, 1, 0);

# 3. 查看表数据.
select * from goods;

# 4.拆表：把goods表的cate_name => cate_id
# 4.1 创建 商品分类表 good_cates
create table if not exists good_cates(
    id int unsigned primary key auto_increment,
    name varchar(10)
);
select * from good_cates;
# 4.2从商品信息表中，抽取出所有的分类信息，并添加到商品分类表中
insert into good_cates(name) select cate_name from goods group by cate_name;
# 获取所有分类的信息，两种思路 distinct 分组去重
select distinct cate_name from goods;
select cate_name from goods group by cate_name;
# 4.3 核心：基于商品分类表的分类id，来修改商品信息表goods的分类的信息
update goods g join good_cates gc on g.cate_name = gc.name set g.cate_name = gc.id;
# 4.4修改商品信息表goods中商品分类的列名和数据类型
alter table goods change cate_name cate_id int not null;
# 5.查看商品表数据及表结构
select * from goods;
desc goods;
# 6.修改brand_name
# 创建品牌商表goods_brand
create table if not exists goods_brand(
    brand_id int unique primary key auto_increment,
    name varchar(20)
);
# 查看表
select * from goods_brand;
# 从goods表中抽出brand_name并添加到goods_brand中
insert into goods_brand(name) select barnd_name from goods group by barnd_name;
# 获取所有分类
select barnd_name from goods group by barnd_name;
# 基于goods_brand修改goods中的brand_name. 提示：这里只修改数据，不修改字段名
update goods g join goods_brand gb on g.barnd_name = gb.name set g.barnd_name = gb.brand_id;
# 修改goods中的brand_id的数据类型
alter table goods change barnd_name brand_id int not null ;
# 查看修改后的数据及数据类型
select * from goods;
desc goods;