SQL分类

  • DDL (数据定义语言)，这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  • DML (数据操作语言)，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  • DCL (数据控制语言)，用于定义数据库、表、字段、用户的访问权限和安全级别。

    DDL:数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE DML:数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT （重中之重） DCL:数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

SQL语言汇总 - 图1

数据库的操作

  1. -- 【建库】创建名为test的数据库，默认字符集设置为utf8，排序规则设置为utf8_general_ci。
  2. -- 加入IF NOT EXISTS后，如果同名的数据库存在也不会报错，语句就不会执行。
  3. CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8 COLLATE utf8_general_ci;
  5. -- 【删库】删除数据库test
  6. -- 加if exists后，数据库不存在也不会报错
  7. DROP DATABASE IF EXISTS test;
  9. -- 【修改数据库】修改数据库test的字符集设置为uft8，排序规则设置为utf8_bin。
  10. ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
  12. -- 【查库】查询数据库的相关信息
  14. -- 显示创建的数据库有哪些
  15. SHOW DATABASES;
  17. -- 【使用指定数据库】
  18. USE test;
  20. -- 显示数据库中的表
  21. SHOW TABLES;
  23. -- 显示建库语句
  24. SHOW CREATE DATABASE test;
  26. -- 显示建表语句
  27. SHOW CREATE TABLE ttt;

表的操作

建表

建表的基本语法：

create table <表名> ( <字段1> <数据类型1> [<列级完整性约束条件>]， <字段2> <数据类型2> [<列级完整性约束条件>]， […..]， [<表级完整性约束条件1>], [<表级完整性约束条件2>], [……] );

注：[ ]中的内容可以省略。

  • 列级完整性约束条件：对单一列的值进行约束
    • PRIMARY KEY：指定该字段为主键(实体完整性约束)
    • NULL/NOT NULL：指定的字段允许为空/不允许为空，如果没有约束条件，则默认为NULL。
    • UNIQUE：指定字段取值唯一，即每条记录的指定字段的值不能重复。
    • DEFAULT<默认值>：指定设置字段的默认值。
    • CHECK(条件表达式)(用户自定义完整性约束)：用于对输入值进行检验拒绝接受不满足条件的值。
    • AUTO_INCREMENT：指定设置字段值自动增加。
  • 表级完整性约束条件：对多列值进行约束
    • PRIMARY KEY用于定义表级主键约束（维护实体完整性约束），语法格式如：
    • FOREIGN KEY用于设置参照完整性规则，即指定某字段为外键，语法格式如下：
      • CONSTRAINT<约束名>FOREIGN KEY<外键>REFERENCES<被参照表(主键)>
    • UNIQUE既可用于列级完整性约束，也可用于表级完整性约束，语法格式如下:
      • CONSTRAINT <约束名>UNIQUE(<字段名>) ``sql -- 【建表】 -- COMMENT 用于添加注释内容 CREATE TABLE IF NOT EXISTSteacher(t_idint(15) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '班主任id',namechar(20) NOT NULL COMMENT '姓名',phonechar(11) UNIQUE NOT NULL COMMENT '电话号',ageint(3) NULL COMMENT '年龄',sexchar(4) NULL COMMENT '性别', CONSTRAINTsexCHECK(sex= "男" orsex` = “女”) )ENGINE = InnoDB CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS student ( u_id int(15) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘学号’, name char(20) NOT NULL COMMENT ‘姓名’, age int(3) NULL COMMENT ‘年龄’, sex char(4) NULL COMMENT ‘性别’, t_id int(15) NULL COMMENT ‘班主任id’, CONSTRAINT sex CHECK(sex = “男” or sex = “女”), CONSTRAINT FOREIGN KEY (t_id) REFERENCES teacher ( t_id ) )ENGINE = InnoDB CHARACTER SET = utf8;

  1. <a name="BSDCs"></a>
  2. ## 修改表结构
  4. - 修改表名：ALTER TABLE 旧表名 RENAME 新表名;
  5. - 修改字段名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
  6. - 修改字段类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型;
  7. - 添加字段：ALTER TABLE 表名 ADD 字段名 新字段类型 [约束条件];
  8. - 删除字段：ALTER TABLE 表名 DROP 字段名;
  9. - 修改表的存储引擎：ALTER TABLE 表名 ENGINE = 存储引擎名;
  10. ```sql
  11. -- 修改表名
  12. -- 前者是原本的表名，后者是新表名
  13. ALTER TABLE `new_teacher` RENAME `xin_teacher`;
  15. -- 修改字段名
  16. ALTER TABLE `xin_teacher` CHANGE `phone` `number` char(12);
  18. -- 修改一个字段的类型
  19. ALTER TABLE `xin_teacher` MODIFY `number` int(11);
  21. -- 添加一个字段
  22. -- 默认添加到最后一个字段的位置
  23. ALTER TABLE `xin_teacher` ADD `qq` int(15);
  25. -- 删除字段
  26. ALTER TABLE `xin_teacher` DROP `qq`;
  28. -- 修改表的存储引擎
  29. ALTER TABLE `xin_teacher` ENGINE = InnoDB;

删表

  1. -- 删除表（表存在时执行）
  2. DROP TABLE IF EXISTS student;

查看表

  1. -- 查看表结构
  2. DESC `student`;
  3. -- 查看建表语句
  4. SHOW CREATE TABLE student;

复制表

  1. -- 复制表的数据和结构到新表
  2. -- as可以省略
  3. CREATE TABLE `new_teacher`
  4. AS
  5. SELECT * FROM `teacher`;
  7. -- 仅复制表的结构到新表
  8. -- as可以省略
  9. CREATE TABLE `new_teacher`
  10. AS
  11. SELECT * FROM `teacher`；
  12. WHERE FALSE;
  14. -- 仅复制表的结构到新表
  15. CREATE TABLE `new_teacher` LIKE `teacher`;

表中数据相关

添加数据

  1. # 添加数据
  3. -- 按照声明的顺序添加数据
  4. INSERT INTO `student`
  5. VALUES
  6. (1, 'WTY2002', 20, '男', 1),
  7. (2, 'WTY', 20, '男', 1);
  9. -- 按自定义的顺序添加数据
  10. -- 没有进行赋值的age和t_id的值为 null
  11. INSERT INTO `student`(`u_id`, `name`, `sex`)
  12. VALUES
  13. (3, 'WTY', '男'),
  14. (4, 'hello', '女');
  16. -- 将其它表中查询的结构添加到该表中
  17. -- 注意：查询的字段一定要与添加到的表的字段一一对应
  18. INSERT INTO `student`(`u_id`, `name`, `sex`)
  19. #查询语句
  20. SELECT `u_id`, `name`, `sex`
  21. FROM `person`
  22. WHERE `age` IN (19, 21);

修改数据

  1. # 修改数据
  3. -- 修改指定字段
  4. UPDATE `student`
  5. SET `name` = 'hi'
  6. WHERE `u_id` = 4;
  8. -- 同时修改多个字段
  9. UPDATE `student`
  10. SET `sex` = '女'
  11. WHERE `name` = 'WTY';
  13. -- 带条件是指根据指定条件修改，不带条件则修改整个表。

删除数据

  1. # 删除数据
  3. -- 指定删除条件
  4. DELETE FROM `student` where `u_id` = 4;
  6. -- 不指定删除条件为删除所有数据
  7. DELETE FROM `student`;
  9. -- 删除所有数据
  10. TRUNCATE `student`;

查询（大大的重点！！！）

一般格式：

select 字段名，… from 表名 where 筛选条件；

要多练习，这里我就不一一列举了😜

  1. # 查询数据
  2. SELECT * FROM `student`;

视图

创建视图

  1. # 创建视图
  3. -- 视图中的字段与基表中的字段对应
  4. CREATE VIEW `student_view1`
  5. AS
  6. SELECT `u_id`, `name` FROM `student`;
  8. -- 查询语句中字段的别名会作为视图中字段的名称出现
  9. CREATE VIEW `student_view2`
  10. AS
  11. SELECT `u_id` `id`, `name` `my_name` FROM `student`;
  13. -- 给定视图的字段来创建视图
  14. -- 小括号内字段个数与SELECT中字段个数相同
  15. CREATE VIEW `student_view3`(`id`, `name`)
  16. AS
  17. SELECT `u_id`, `name`
  18. FROM `student`
  19. WHERE `age` >= 20;

查看视图

  1. # 查看视图
  3. -- 查看数据库的表对象、视图对象
  4. SHOW TABLES;
  6. -- 查看视图的结构
  7. DESCRIBE `student_view3`;
  9. -- 查看视图的详细定义信息
  10. SHOW CREATE VIEW `student_view3`;

删除视图

  1. # 删除视图
  3. DROP VIEW `student_view3`;
  5. DROP VIEW IF EXISTS `student_view3`;

更新视图

操作和表的一样，当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。
在某些情况下，视图不支持更新。

  1. -- 更新视图
  2. UPDATE `student_view1`
  3. SET `name` = 'WTY2002'
  4. WHERE `u_id` = 3;

索引

  • 创建索引的一般格式：CREATE INDEX 索引名 ON 表名(列名1， 列名2……);
  • 删除索引的一般格式：DROP INDEX 索引名 ON 表名;
  • 查看索引的一般格式：SHOW INDEX FROM 表名; ``sql -- 【创建索引】 CREATE INDEXindexNameONstudent(u_id,name`);

— 【删除索引】 DROP INDEX indexName ON student;

— 【查看索引】 SHOW INDEX FROM student;

  1. <a name="TzPal"></a>
  2. # 存储过程
  3. 一般语法：in可以省略，默认为输入参数
  4. > CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
  5. > [characteristics ...]
  6. > BEGIN
  7. >     存储过程体
  8. > END
  10. ```sql
  11. -- 创建存储过程，无参数的。
  12. CREATE PROCEDURE add_age()
  13. BEGIN
  14.     UPDATE `student` SET `age` = `age` + 1;
  16. END;
  18. -- 调用存储过程
  19. CALL add_age();
  21. -- 查看存储过程
  22. SHOW PROCEDURE STATUS LIKE 'add_age';
  24. SHOW CREATE PROCEDURE `add_age`;
  26. -- 删除存储过程
  27. DROP PROCEDURE `add_age`;

自定义函数

一般语法：

CREATE FUNCTION 函数名(参数名 参数类型,…) RETURNS 返回值类型 [characteristics …] BEGIN 函数体 #函数体中肯定有 RETURN 语句

END

  1. #创建函数前执行此语句，保证函数的创建会成功
  2. SET GLOBAL log_bin_trust_function_creators = 1;
  4. -- 创建自定义函数
  5. CREATE FUNCTION find_per(id int)
  6. RETURNS char(20)
  7. BEGIN
  8.     RETURN (SELECT `name` FROM `student` where `id` = `u_id`);
  9. END;
  12. -- 调用函数
  13. SELECT find_per(3);

image.png

触发器

一般语法：

CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 触发器执行的语句块;

  1. -- 创建触发器
  2. CREATE TRIGGER add_age
  3. AFTER INSERT ON `student`
  4. FOR EACH ROW
  5. BEGIN
  6.     UPDATE `teacher` SET `name` = 'ttt' WHERE new.t_id = t_id;
  7. END;
  9. -- 查看触发器
  11. -- 看当前数据库的所有触发器的定义
  12. SHOW TRIGGERS;
  14. -- 查看某个触发器的定义
  15. SHOW CREATE TRIGGER add_age;
  17. -- 删除触发器
  18. DROP TRIGGER IF EXISTS add_age;

数据库管理

创建用户：

create user ‘用户名’@’允许登录的主机地址’ identified by 密码; 

  1. #创建指定ip为 192.168.1.1 的lyn用户登录  
  2. create user 'lyn'@'192.168.1.1' identified by '123';
  3. #创建指定ip为 192.168.1 开头的lyn用户登录  
  4. create user 'lyn'@'192.168.1.%' identified by '123';
  5. #创建指定任何ip的lyn用户登录  
  6. create user 'lyn'@'%' identified by '123';

删除用户：

drop user ‘用户名’@’允许登录的主机地址’;

修改用户名：

rename user ‘用户名’@’IP地址’ to ‘新用户名’@’IP地址’;

修改密码：

set password for ‘用户名’@’IP地址’ = Password(‘新密码’);

查看用户权限：

show grants for ‘用户’@’IP地址’

授权：

grant 权限列表 on 库.表 to 用户名@’ip’ identified by “密码”;
.表示所有库的所有表

  1. grant insert, update, select on *.* to 'casual_user'@'localhost' identified by "123456";

撤销权限：

revoke 权限列表 on 库.表 from 用户名@’ip’; 

  1. revoke all on mydb1.table1 from 'user1'@'localhost';
  3. revoke insert, select, update, delete, create, alter
  4. on mydb2.* from 'user2'@'localhost';

数据库备份与恢复：

  1. mysqldump -uroot -p123123 -h127.0.0.1 db1 --tables emp dept> /home/db1/backup/emp-dept.sql;
  3. use db1;
  4. source /home/db1/backup/sala.sql;

最后教你个操作：

DROP DATABASE test;

删库跑路🤣