SQL学习总结（基本增删改查CURD）

 学习总结如下

# ddl--data definition language
show databases;
create database mydb1;
create database if not exists mydb1;
use mydb1;
drop database if exists mydb1;
create table if not exists students
(
    sid     int unsigned,
    name    varchar(20),
    gender  varchar(20),
    age     int,
    birth   date,
    address varchar(20),
    score   double
);
drop table if exists students;
show tables;
show create table students;
# 查看表结构
desc students;
drop table students;
# 修改表结构--添加
alter table students
    add dept varchar(20);
alter table students
    change dept department varchar(30);
alter table students
    drop department;
# 修改表名
rename table students to stu;
rename table stu to students;
# -----------------------------------------------
# DML -- data manipulation language 数据操作语言 --操作数据 对数据进行增删改没有查

# 插入数据--两种格式
# 可以插入多行用逗号链接
insert into students(mydb1.students.sid, mydb1.students.name, mydb1.students.gender, mydb1.students.age,
                     mydb1.students.birth, mydb1.students.address, mydb1.students.score)
    value (1001, '张三', '男', 18, '2001-12-13', '北京', 85.5),
    (1002, '张四', '男', 18, '2000-12-13', '北京', 78.5)
;
insert into students(sid)
values (1004);
insert into students(sid, mydb1.students.name)
values (1000, '马牛逼');

insert into students
values (1001, '小鸡鸡', '无', 21, '1999-01-08', '广州', 79);
#数据修改(更新)
#将所有学生的地址都修改为
update students
set mydb1.students.address='重庆';
# 将id为1004的学生修改
update students
set mydb1.students.address='深圳'
where sid = 1004;
# 将id为1000的学生地址修改为重庆 成绩修改为100
update students
set mydb1.students.address='东莞',
    mydb1.students.score=100
where sid = 1000;
# 数据删除
# 清空整个表
delete
from students;
truncate table students;
truncate students;
# 删除一整行
delete
from students
where sid = 1004;
delete
from students
where sid > 1004;

#mysql约束(constraint) 创建表的时候保证表中的数据的完整性和有效性 比如手机号不能为空，身份证号不能重复
# 主键约束(primary key)PK
/*一个列或多个列的组合，单列主键和联合主键，其值可以唯一的标识表中的每一行，能够方便的在RDBMS中尽快的找到某一行
  提高效率
  pk相当于唯一约束加非空约束的组合，不允许重复也不允许空
  每个表最多允许一个主键
  关键字是(primary key)
  当创建主键的约束时，系统会默认在所在的列和列上建立对应的唯一索引
 */
-- 添加单列主键
-- 定义字段中添加
create table test1
(
    demo int primary key
);
-- 定义字段后添加
create table test2
(
    demo1 varchar(20),
    eid   int,
    constraint zhujianmingzi primary key (eid)
);
# 自增长约束(auto_increment)
# 非空约束(not null)
# 唯一性约束(unique)
# 默认约束
# 零填充约束
# !!!!外键约束

#DQL 查询
-- 准备一下数据
create database if not exists mydb2;
use mydb2;
create table product
(
    pid         int primary key auto_increment,
    pname       varchar(20) not null,
    price       double,
    category_id varchar(20)
);
insert into product
values (null, '海尔洗衣机', 5000, 'c001'),
       (null, '美的冰箱', 3000, 'c001'),
       (null, '格力空调', 5000, 'c001'),
       (null, '九阳电饭煲', 5000, 'c001');
insert into product
values (null, '啄木鸟衬衣', 300, 'c002'),
       (null, '恒源祥西裤', 800, 'c002');

#简单查询 -----语句会先执行from
# 1 查询所有的商品
select *
from product;
# 2 查询商品名和商品价格
select pname, price
from product;
# 3 别名查询 使用关键字as 可省略
-- 表起别名
select *
from product as p;
select *
from product p;
-- 列起别名
select pname as p
from product;
# 4.去掉重复值关键字distinct
select distinct price
from product;
# 5.查询结果是表达式（运算查询）
select pname, price + 10 as new_price
from product;

# 运算符
-- + - * / %
-- = < <= > >= <=>(完全等于，主要用于比较null值) ISNULL ISNOTNULL LEAST GREATEST BEETWEENAND IN NOTIN LIKE(模糊匹配) REGEXP(正则表达式的匹配)
-- ! && || XOR(异或不同为真)
-- | % ^ >> << ~
select *
from product
where pname = '海尔洗衣机';
select *
from product
where price between 200 and 1000;
select *
from product
where price >= 200 && price <= 1000;
select *
from product
where price in (200, 800);
select *
from product
where pname like '%鞋%'; -- %代表任意内容
select *
from product
where pname like '%裤';
select *
from product
where pname like '_海%'; -- 一个下划线是单个字符
select *
from product
where category_id is null;
select least(10, 5, 20) as small_number;
select greatest(10, 20, 30) as big_number;
-- 如果求值的数中有null那就不会进行比较，结果直接为null

# 排序查询 order by asc是升序 desc是降序默认升序 order by 支持单个字段 多个字段 表达式 函数 别名 - order by 子句 防在查询语句的最后面 limit除外
select *
from product
order by price asc;
# 在价格排序的基础上 以分类排序
select *
from product
order by price asc, category_id desc;
# 显示商品的价格（去重复）
select distinct price
from product
order by price desc;
-- price 前无法加别的列因为去重后不是一一对应导致错误

# 聚合查询（与之前的查询不同他是一列一列的）
# 聚合函数 count() 统计不为null的记录行数 sum() max() min() avg() 其中sum和avg中若不是数值类型则结果为零
select count(pid)
from product;-- 因为pid是主键列所以就可以代表行数
select count(*)
from product; -- count(1)等价于count(*)
select count(*)
from product
where price >= 200;
select sum(price)
from product
where category_id = 'c001';
select max(price)
from product;
select min(price)
from product;
select max(price), min(price)
from product;
select avg(price)
from product;
-- 除count外都是直接忽略null值的所在行

#### 分组查询 使用group by关键字having
select category_id, count(*)
from product
group by category_id;
-- 会切分表格 对每一部分进行查询
# having 分组之后对统计结果进行筛选 where子句用来筛选from语句中指定的操作所产生的行
# group by 用来分组where语句的输出 having 用来从分组的结果中筛选行
select category_id, count(*)
from product
group by category_id
having count(*) > 3;
-- 如果出现select price 会报错 因为不是一一对应

# 分页查询 limit 关键字
-- 显示前5条
select category_id
from product
where category_id = 'c001'
limit 5;
-- 分页显示
select pid
from product
limit 3,5;
-- 表示从第3+1开始 查询五条

# insert into select 语句 将一张表的数据导入到其他表当中
create table product2
(
    pid         int primary key auto_increment,
    pname       varchar(20) not null,
    price       double,
    category_id varchar(20)
);
insert into product2
select *
from product;
insert into product2(pid, pname)
select pid, pname
from product;
select *
from product2;

create table cnttable
(
    category_id varchar(20),
    cnt         int
);
insert into cnttable
select category_id, count(*)
from product
group by category_id;
select *
from cnttable;

# 正则表达式 REGEXP regular expression描述了一种字符串匹配的规则 正则表达式本身是一个字符串 自己定了一个规则 用自己这个规则来匹配符合这个规则的字符串
-- ^ 在字符串开始处进行匹配
select 'abc' regexp '^a';
select *
from product
where pname regexp '^海';
-- $ 在字符串末尾开始匹配
select 'abc' regexp 'c$';
-- . 匹配任意单个字符
select 'abc' regexp '.b';
select 'abc' regexp '.c';
select 'abc' regexp '.a';
-- []匹配括号内的任意单个字符
select 'abc' regexp '[xaz]';
select 'abc' regexp '[sss]';
-- [^]匹配除了括号内的任意单个字符 ^代表取反 就是类似补集;
select 'abc' regexp '[^ddd]';
-- 差不多像[abce......f]
-- (.a.)* 匹配0个或多个a，包括空字符串。可以作为占位符使用，有没有指定字符都可以匹配到数据
select 'stab' regexp '.ta*b';
select 'stb' regexp '.ta*b';
select '' regexp 'a*';
select 'stab' regexp '(ta)*';
-- (a)+表示a至少出现1次
select 'staaaab' regexp 'a+';
-- (a)?匹配0 或1个a
select 'staaaaab' regexp 'a';-- 这种情况只要有a就真
select 'staab' regexp 'sta?b';
-- 这就是假
-- a1|a2匹配a1或a2
select 'a' regexp 'a|b';
-- a{m} 匹配m个a
select 'auuuuc' regexp 'u{3}';
-- a{m,n}匹配m到n个a 左右闭区间
select 'aiuuuuuc' regexp 'u{2,10}';
-- (abc)abc作为一个整体去匹配
select 'aiuuuc' regexp '(aiu)+'


#表关系之一对多
# 关于外键约束 外键是用来让多个表之间建立链接 保证数据的一致性和完整性
-- 部门表
create table dept
(
    id       int primary key auto_increment,
    dep_name varchar(20),
    addr     varchar(20)
);
-- 员工表
create table emp
(
    id     int primary key auto_increment,
    name   varchar(20),
    age    int,
    dep_id int
);
insert into dept(dep_name, addr)
VALUES ('研发部', '广州'),
       ('销售部', '深圳');
insert into emp(name, age, dep_id)
VALUES ('张三', 20, 1),
       ('李四', 20, 1),
       ('王五', 20, 1),
       ('赵六', 20, 2),
       ('孙七', 22, 2),
       ('周八', 18, 2);
drop table dept;
drop table emp;
select *
from emp;
-- 添加约束
-- 1.创建表时添加外键约束
create table dept
(
    id       int primary key auto_increment,
    dep_name varchar(20),
    addr     varchar(20)
);
create table emp-- 从表
(
    id     int primary key auto_increment,
    name   varchar(20),
    age    int,
    dep_id int,
    -- 添加外键dept_id 关联dept表的 id主键 references--关联
    constraint fk_emp_dept foreign key (dep_id) references dept (id)
);
-- 2.建完表后添加外键约束
alter table emp
    add constraint fk_emp_dept foreign key (dep_id) references dept (id);
-- 删除约束      constraint ->约束
alter table emp drop foreign key fk_emp_dept;

-- 添加之后只有从表的数据全部被删除主表的对应数据才能删除


# 多表查询 和单表类似
select * from emp,dept;
-- 直接这样写sql会产生笛卡尔积：有a，b两个集合，取a，b的所有组合情况
-- 消除无效数据
-- 查询emp和dept的数据，emp.dep_id = dept.id
select * from emp,dept where emp.dep_id = dept.id;

-- 1.连接查询
-- 内连接 交集
-- 隐式内连接
select * from emp,dept where emp.dep_id = dept.id;
-- 显式内连接 ->嫌长可以起别名 inner可以省略
select * from emp inner join dept on emp.dep_id = dept.id;
-- 外连接 分为左外连接和右外连接 相当于查询a/b表所有数据和交集数据
-- 左外连接
select * from emp left join dept on emp.dep_id = dept.id;
-- 右外连接
select * from emp right join dept on emp.dep_id = dept.id;
-- 2.子查询 --查询中嵌套查询 比如查询张三的年龄 再查询年龄高于张三的员工信息
# 单行单列 :作为条件值，使用= != > < 等进行条件判断

select * from emp where age > (select age from emp where name = '张三');