SQL语句总结篇

################## SQL语句总结 ##################

-- DCL数据库控制语言

# 创建用户
create user 'hyb1'@localhost identified by 'hyb1';
create user 'hyb2'@'%' identified by 'hyb2';

# 授予权限
# 权限包括 create alter drop insert update delete select
# 控制多个权限的时候用都好隔开
grant create on hyb_test1 to 'hyb1'@'localhost';
grant all on hyb_test1 to 'hyb2'@'%';

# 撤销授权
revoke create on hyb_test1 from 'hyb1'@'localhost';

# 查看用户权限
show grants for 'hyb2'@'%';

# 删除用户
drop user 'hyb1'@'localhost';

# 修改密码（以root身份）
alter user 'hyb2'@'%' identified with mysql_native_password by '123';

-- DDL数据库定义语言

---- 数据库的操作

# 创建新数据库
CREATE database hyb_test2;
CREATE database IF NOT EXISTS hyb_test1;

# 删除数据库
DROP database hyb_test1;
drop database if exists hyb_test1;

# 使用数据库
use hyb_test;

# 展示所有数据库
show databases;

---- 表操作

# 展示该库的所有表
show tables;

# 创建学生表
/* 字段属性：
 * default 默认值
 * not null 非空
 * auto_increment 自增
 * unique 唯一，不能重复
 * check 检查，必须符合条件
 * primary key 主键
 * foreign key 外键
 */
create table student (
    id int auto_increment primary key comment '学生id',
    name varchar(10) not null comment '学生姓名',
    gender varchar(4) comment '学生性别'
) comment '学生表';

# 展示表结构
desc hyb_test1;

# 复制表结构
create table student_copy like student;

-- DML数据库操作语言

# 插入
insert into student(name, gender) values('张三', '男'), ('李婷婷', '女');

# 更新表
UPDATE student SET id=1 WHERE name='张三';

# 删除表
delete from student;
drop table aaa;

# 清空表
truncate table bbb;

# 删除行
delete from student where id=1;

# 修改字段值
UPDATE students SET name='sam' WHERE id=1;

# 添加字段
alter table student add class int;
alter table student add class int after gender;
alter table student add column class int;

# 修改字段
alter table student change gender height int;
alter table student modify height float;

# 字段重命名
ALTER TABLE student RENAME TO students;

# 删除字段
ALTER TABLE students DROP COLUMN height;

# 添加字段约束
alter table person alter column gender set default 'boy';
alter table student add constraint pk_id primary key(id);
alter table student add constraint fk_grade_id_student_id foreign key(id) references student(id);
alter table student modify name varchar(10) not null;
alter table student add constraint uq_name unique(name);

-- DQL数据库查询语言

# 查看表所有数据
select * from student;

# 只查看name和gender字段
select name, gender from student;

# 加条件
select * from student where id=3
/* p.s. 操作符
 * >
 * >=
 * <
 * <=
 * =
 * <> 或者 !=
 * between ... and ...
 * is null
 * is not null
 * like '模式'：% 代表任意长度字符，_ 表示单个字符
 */

# 子查询
# 有学生表和课程表，查询参加数据库课程的学生名字
select name from student where course_id in (
    select id from class where name='数据库');
# p.s. 子查询会用到 in 或 not in 或 exists 或 not exists

# 连接
select * from a join b on a.id=b.id where a.id is not null
/* p.s. 连接方式
 * join 或 inner join
 * left join
 * right join
 * outer join
 */

# 分组
# 查看每个部门最大薪资
select name, max(salary) from employee group by department;
/* p.s. 分组一般配合聚合函数使用
 * max
 * min
 * count
 * avg
 */

# 对分组做限定
# 查看部门人数大于2人的部门最高薪资
select name, max(salary), department from employee group by department having count(*)>2;
# having 必须在 group by 之后使用

# 排序
# 查看员工信息，按照薪水降序排列
select * from employee order by salary desc;
# p.s. order by 默认升序（asc）

# 分页
# 查看第10到第20条数据
select * from student limit 10 offset 9;
select * from student limit 9, 10;

# 去重
select distinct * from student;

# 求并集
# 查看既选了数据库又选了python课的学生名
select name from student where course_id in (select id from class where name='数据库')
union
select name from student where course_id in (select id from class where name='python');
# p.s. 求并集操作自动去重

-- 函数

---- 内置函数

# concat() 拼接函数
SELECT concat('hello', ' ', 'world');

# lower() 全改为小写
select lower('ADFJSOjdfiso');

# upper() 全改为大写
select upper('ADFJSOjdfiso');

# substring() 截取函数
# 从第七个字符向后截取五个字符
select substring('hello world', 7, 5);

# trim() 删除左右两边的空格
select trim('  jio jio    jio     ');

# lpad() 左填充
# 用后面的字符串对前面的字符串的左边进行填充，达到指定长度为止
select lpad('sleep', 10, '%');

# rpad() 右填充
# 用后面的字符串对前面的字符串的右边进行填充，达到指定长度为止
select rpad('sleep', 10, '%');

# ceil() 向上取整
select ceil(3.4);
select ceil(3.7);

# floor() 向下取整
select floor(3.4);
select floor(3.7);

# round() 四舍五入，保留指定位小数
select round(3.455576213, 4);

# mod() 取余数
select mod(343,223);

# rand() 随机数（0-1）
select rand();

# 有关时间的函数
/* curdate()
 * curtime()
 * now()
 * month()
 * year()
 * day()
 */

---- 自定义函数

# 创建号码段表
create table operator (id int primary key auto_increment, number varchar(20));
# 插入电话段用例
insert into operator(number) values
("135"),("136"),("137"),("138"),("139"),("144"),("147"),("148"),("150"),("151"),("152"),("157"),("158"),("159"),("172"),("178"),("182"),("183"),("184"),("187"),
("188"),("195 "),("197"),("198"),("133"),("149"),("153"),("173"),("177"),("180"),("181"),("189"),("190"),("191"),("193"),("199"),("130"),("131"),("132"),("145"),
("155"),("156"),("166"),("167"),("171"),("175"),("176"),("185"),("186"),("196");
# 函数：生成随机电话号码
create function random_phone()
returns varchar(20)
begin
     declare ret, a varchar(20);
    declare b varchar(20) default "";
    declare i int default 0;
    set a = (select number from operator order by rand() limit 1);
    while i<8 do
          set b = concat(b, substring("0123456789", floor(rand()*10+1), 1));
        set i = i + 1;
     end while;
    set ret = concat(a, b);
    return ret;
end;
# 验证函数
select random_phone();

-- 索引

# 建表时定义索引
create table student(
    id int primary key,
    name varchar(10),
    gender varchar(4),
    class int,
    index idx_student_id(id)
    )
    
# 添加索引
create unique index idx_student_id on student(id);
create index idx_student_name_gender on student(name, gender);

# 修改表添加索引
alter table student add index idx_student_id(id);

# 展示该表的索引
show index from student;

# 删除索引
drop index from idx_student_id on student;

-- 视图

# 创建视图
CREATE or replace VIEW view_hyb AS SELECT * FROM student WHERE name="郝一博";
CREATE VIEW view_sam(name, class, course) AS
    SELECT student.name sn, student.class sc, course.name cn FROM student s join course c ON s.id=c.id where sn="sam";
SELECT * FROM view_hyb;

# 查看视图结构
DESC view_hyb;

# 查看视图基本信息
show TABLE status LIKE "view_hyb"\G;

# 查看视图详细信息
show CREATE VIEW view_hyb\G;

# 查看数据库内所有视图信息
SELECT * FROM hyb_test.views;

# 修改视图定义
alter view view_hyb as select name from student where name="郝一博";

# 修改视图数据
update view_hyb set gender="女" where name="郝一博";

# 删除视图
drop view if exists view_hyb;

-- 触发器

# 准备两个表，一个主表，一个备份表
CREATE TABLE main (id int PRIMARY KEY, name varchar(10));
CREATE TABLE copy (id int PRIMARY KEY, name varchar(10));
# 当主表插入数据，备份表也插入
CREATE TRIGGER trigger_insert_copy
AFTER INSERT ON main
FOR EACH ROW
BEGIN
    INSERT INTO copy values(NEW.id, NEW.name);
END;
# 当主表删除数据，备份表也删除
CREATE TRIGGER trigger_delete_copy
AFTER DELETE ON main
FOR EACH ROW
BEGIN
    DELETE FROM copy WHERE id=OLD.id and name=OLD.name;
END;
# 当主表修改数据，备份表也修改
CREATE TRIGGER trigger_update_copy
AFTER UPDATE ON main
FOR EACH ROW
BEGIN
    UPDATE copy SET id=NEW.id, name=NEW.name WHERE id=OLD.id AND name=OLD.name;
END;

# 展示所有触发器
show triggers;

# 查看所有库的触发器
select * from information_schema.TRIGGERS t ;

# 删除触发器
DROP TRIGGER IF EXISTS trigger_insert_copy

# 有两个表，商品表和订单表
CREATE TABLE goods(id int PRIMARY KEY, name varchar(20), count bigint);
CREATE TABLE order1(id int PRIMARY KEY, goods_name varchar(20), mount bigint);
# 当所需货物不存在、或者数量多于库存将提示报错，否则正常进行
CREATE TRIGGER check_goods
BEFORE INSERT ON order1
FOR EACH row
BEGIN
    DECLARE c bigint;
    IF NOT EXISTS(SELECT name FROM goods WHERE name=NEW.goods_name) THEN
        signal SQLSTATE '45000' SET message_text='您所下单的商品不存在';
    END IF;
    
    SET c = (SELECT count FROM goods WHERE name=NEW.goods_name);
    IF NEW.mount>c THEN
        signal SQLSTATE '45000' SET message_text='您下单的商品库存不足';
    END IF;
    
    UPDATE goods SET count=c-NEW.mount WHERE name=NEW.goods_name;
END;
# 日志表
create table log (id int primary key auto_increment, type1 varchar(10), time1 timestamp);
# 每当添加订单之后记录数据
## 5.7版本不支持语句级触发器
create trigger record_log
after insert on order1
for each statement
begin
    insert into log (type1, time1) values('insert', now());
end;

-- 存储过程

# 创建存储过程
## 插入n条数据
create procedure circu_ins (n int)
begin
    declare i,j int default 1;
    declare v varchar(20) default "";
    while i<=n do
        while j<=i do
            set v = concat(v, "a");
            set j = j + 1;
        end while;
        insert into bbb (id, a) values(i, v);
        set i = i + 1;
        set j = 0;
        set v = "";
    end while;
    select * from bbb;
end;

# 调用存储过程
call circu_ins(5);

# 删除存储过程
drop procedure if exists circu_ins;

# 查询所有存储过程的状态
show procedure status;

# 查看所有存储过程
select * from mysql.proc;

# 查看存储过程的定义（编写脚本）
show create procedure loop_insert;

-- 游标

# 将person1表和person2表的b字段都合成一个变量
create table person1(a int primary key auto_increment, b varchar(10));
create table person2(a int primary key auto_increment, b varchar(10));
insert into person1(b) values('aaa'), ('bbb'), ('ccc');
insert into person2(b) values('ddd'), ('eee'), ('fff');
select * from person1 n1;
select * from person2;
# 创建函数实现
create function merge_field()
returns varchar(100)
begin
    declare ret varchar(100) default '';
    declare v varchar(10);

    # 标识符，标识是否查找完数据，0代表没有查找完，1代表查找完
    declare flat int default 0;
    
    # 声明定义游标
    declare cur1 cursor for select b from person1;
    declare cur2 cursor for select b from person2;

    # 异常处理
    declare continue handler for not found set flat = 1;

    open cur1;
    a: loop
        fetch cur1 into v;
        if flat=1 then
            leave a;
        end if;
        set ret = concat(ret, v, ' ');
    end loop a;
    close cur1;

    set flat = 0;

    open cur2;
    b: loop
        fetch cur2 into v;
        if flat=1 then
            leave b;
        end if;
        set ret = concat(ret, v, ' ');
    end loop b;
    close cur2;

    return ret;
end;
# 检验函数
select merge_field();

-- 预处理语句

# 定义预处理语句
prepare sql_1 from "select * from person1";
prepare sql_2 from "select * from person2 where a = ?";

# 定义变量
set @a = 2;

# 执行语句
execute sql_1;
execute sql_2 using @a;

# 删除语句
drop prepare sql_2;

-- 事务

# 开启事务
start transaction;

# 设置保存点
savepoint s1;

# 回滚
rollback; # 回到事务开始的状况
rollback to s1; # 回到保存点的事务状况

# 提交
commit;

-- 数据类型

/* 整型
 * tinyint：1字节
 * smallint：2字节
 * mediumint：3字节
 * int 或 integer：4字节
 * bigint：8字节
 */

/* 浮点类型
 * float(m, d)：4字节，8位精度
 * double(m, d)：8字节，16位精度
 */

/* 日期与时间类型
 * date：YYY-MM-DD
 * time：HH:MM:SS
 * year：YYYY
 * datetime：YYY-MM-DD HH:MM:SS
 * timestamp：YYYYMMDD HHMMSS
 */

/* 字符串类型
 * char：定长字符
 * varchar：变长字符
 * tinyblob：不超过255个字符的二进制字符串
 * tinytext：短文本字符串
 * blob：二进制长文本数据
 * text：长文本数据
 * mediumblob：二进制中等长度文本数据
 * mediumtext：中等长度文本数据
 * longblob：二进制极大文本数据
 * longtext：极大文本数据
 */