SQL语言

数据定义语言DDL

创建数据库

CREATE DATABASE <数据库名>;

选择数据库

USE <数据库名>;

删除数据库

DROP DATABASE <数据库名>;

创建表

定义表结构来创建表

• CREATE TABLE <表名>(<列名> <数据类型> [DEFAULT<默认值>] [AUTO_INCREMENT] [PRIMAEY KEY]);

• AUTO_INCREMENT为自增策略，设为自增的列必须为主键且为数值类型

  利用子查询创建表（子查询即嵌套查询）

• CREATE TABLE <表名> SELECT 语句;

  修改表的结构

  一个表中添加新一列

• ALTER TABLE <表名> ADD [COLUMN] <列名> <数据类型> [DEFAULT<默认值>] [AUTO_INCREMENT] [PRIMAEY KEY]);

  修改一个表中的已有列

• ALTER TABLE <表名>MODIFY [COLUMN] <列名> <数据类型> [DEFAULT<默认值>] [AUTO_INCREMENT] [PRIMAEY KEY]);

  从一个表中删除一列

• ALTER TABLE <表名>DROP [COLUMN] <列名>;

  截断表和删除表

  截断-即清空数据不删除表结构

• TRUNCATE TABLE <表名>

  删除-即清空数据同时删除表结构

• DROP TABLE <表名>

  重命表名

  RENAME TABLE <原表名> TO <新表名>

  完整性约束

  主键约束(值唯一，非空，一表一主键)

• 创建主键约束

  • 列级主键约束
    • CREATE TABLE TB(empno DECIMAL(2) PRIMAEY KEY，name VARCHAR(8));
  • 表级主键约束
    • CREATE TABLE employee(empno DECIMAL(2),name VARCHAR(8),age DECIMAL(3),deptno DECIMAL(2),CONSTRAINT pk_no PRIMARY KEY(empno,deptno)
    • CONSTRAINT关键字可为主键约束指定名称、主键由多个字段组成（联合组件）必须使用上述表级约束定义。
• 删除主键约束
  • ALTER TABLE employeeDROP PRIMARY KEY;

• 创建表后添加主键约束

  • ALTER TABLE amployeeADD CONSTRAINT pl_no PRIMARY KEY(empno);

    唯一约束（设置唯一约束的值唯一，唯一约束的列可有且仅有一个空值，一表多唯一（一表多候选键））

• 创建唯一约束

  • CREATE TABLE employee(empno DECIMAL(2),name VARCHAR(8),age DECIMAL(3),deptno DECIMAL(2),phone varchar(12),CONSTRAINT emp_phone UNIQUE(phone));
• 删除主键约束
  • ALTER TABLE employeeDROP INDEX emp_phone;

• 创建表后添加主键约束

  • ALTER TABLE amployeeADD CONSTRAINT emp_phone UNIQUE(phone);

    检查约束（符合条件的数据才能输入到表）

• 创建列级检查约束

  • CREATE TABLE employee(empno DECIMAL(2) PRIMARY KEY.name VARCHAR(8),age DECIMAL(3),phone VARCHAR(12) UNIQUE,deptno DECIMAL(2),CONSTRAINT agt_CK CHECK(age > 20 AND age < 60));#限制age字段值大于20小于60

• 创建表级检查约束

  • 为已有的employee表创建新字段addressALTER TABLE employeeADD address VARCHAR(30)#为employee表创建检查约束，限制每条记录中age的值>20且小于60#而且address 的值以’北京市’为开头ALTER TABLE employeeADD CONSTRAINT age_add_CKCHECK (age > 20 AND age< 60 AND address LIKE’北京市%’)

    外键约束

• 创建employee 和department表，实现两表间的外键约束，并指定为级联更新CREATE TABLE department(deptno DECIMAL(5) PRIMARY KEY,dept_name VARCHAR(16));CREATE TABLE employee(empno DECIMAL(5) PRIMARY KEY,name VARCHAR(10) NOT NULL,age DECIMAL(3),deptno DECIMAL(5),CONSTRAINT FK_IDFOREIGN KEY (deptno) REFERENCES department(deptno)ON UPDATE CASCADE#若设置级联删除则 ON DELETE CASCADE);

  • CASCADE关键字，主表被引用列被更新或删除，子表响应应用列也将被删除或更新。
  • SET NULL关键字，主表被引用列的值被删除，子表相应引用列将被置为空值
  • NO ACTION关键字，删除主表中被引用列的数据将违反外键约束，该操作会被禁止执行

    触发器

    CREATE TRIGGER 触发器名 BEFORE | AFTER INSERT | DELETE | UPDATE ON 表名 FOR EACH ROW <触发体>

• 创建触发器记录用户删除的数据和时间CREATE TABLE merch_log(who VARCHAR(30),oper_date DATE);CREATE TRIGGER delete_trigger AFTER DELETE ON department INSERT INTO merch_log(who,oper_date) VALUES(USER(),SYSDATE());

  访问受触发语句变化前的值通过OLD.列名

  发访问受触发语句变化后值通过NEW.列名

  #删除触发器DROP TRIGGER 触发器名;

  数据操纵语言DML

  数据查询

  基本语法

• SELECT [ALL | DISTINCT] * | <列名| 列表达式> [as 别名][<列名| 列表达式>[as 别名]] ···FROM <表名或视图名> [as 别名][,<表名或视图名> [as 别名]] ···[WHERE <条件表达式>][HAVING <组条件表达式>][ORDER BY <排序列名1[ASC | DESC]> [,<排序列名2[ASC | DESC]>]···];

  SELECT 子句

• ALL为默认，即输出全部记录（包括重复项）DISTINCT表示输出去重后的记录

• • 表示选取表中所有字段

• AS操作符可为列或表另命名，改名后语句其他部分涉及的对应列或表也需沿用，否则易报错

  UNION 子句

• 用于合并查询的结果

  FROM 子句

• 连接查询

  • 相等连接、简单连接、内连接(将两表指定列值相等的行连接)
    • SELECT 表名.列名1[,列名2,列名3…]FROM 表名1 INNER JOIN 表名2ON 表名1.列名 = 表名2.列名;
  • 自身连接(一表映射两表)
    • SELECT 表名.列名1[,列名2,列名3…]FROM 表名1 INNER JOIN 表名1ON 表名1.列名 = 表名.列名;
  • 不等连接
  • 左外连接
    • SELECT 表名.列名1[,列名2,列名3…]FROM 表名1 LEFT OUTER JOIN 表名2ON 表名1.列名 = 表名2.列名;
  • 右外连接
    • SELECT 表名.列名1[,列名2,列名3…]FROM 表名1 RIGHT OUTER JOIN 表名2ON 表名1.列名 = 表名2.列名;

      WHERE 子句

• 算数比较判断

  • <
  • <=
  • >

  • =

  • —不等于<> 或 !=

  • =
• 逻辑比较判断（优先级由高到低非、与、或）

  • —非NOT

  • —与AND

  • —或OR

• 之间判断
  • [NOT] between <值1> to <值2>
• 字符串模糊判断
  • <字符串> [NOT] LIKE <匹配模式>

    • —姓名第一个字是张的WHERE 姓名 Like ‘张%’

    • 匹配模式内通配符”%”表示任意数量个字符

    • —姓名第二个字是张的WHERE 姓名 Like ‘_张%’

    • 匹配模式内通配符“_”表示任意一个字符
• 空值判断
  • <表达式> IS [NOT] NULL

• 之内判断

  • <表达式> [NOT] IN (<集合>)

    • —学号为2和3的WHERE 学号 IN (2,3)

      GROUP BY子句

• 分组、汇总聚合函数统计

• 聚合函数（不能出现在WHERE、GROUP BY子句中）

  • —计算记录的个数COUNT(*)

  • —计算该列有值的记录个数COUNT(<列名>)

  • —该列数值总和SUM(<列名>)

  • —该列数值平均值AVG(<列名>)

  • —该列数值平均值MAX(<列名>)

  • —该列数值平均值MIN(<列名>)

    HAVING子句

• 用于限定查询结果值只显示分组后的、其聚合函数满足指定条件的哪些组

  ORDER BY子句

• SELECT包含多个子句时，ORDER BY 子句必须是最后一个子句

• DESC降序、ASC升序

  数据维护

  插入数据

• —基本INSERT语句插入INSERT INTO 表名[(列名1[,列名2···])]VALUE(值1[,值2···]) [ ,(值1[,值2···])，···];

• —利用子查询语句插入INSERT INTO 表名[(列名1[,列名2···])]SELECT 语句;

  更新数据

• UPDATE 表名SET 列名 = 值[,列名 = 值,···][WHERE<条件>];

  删除数据

• —按行或利用子查询删除数据，若无WHERE子句限定则删除全部行DELETE FROM 表名[WHERE <条件>[= 子查询语句]]

  索引和视图

  索引

• 作用

  • 提高查询速度，类似于书中的目录。便于数据筛选
  • 但会降低DML操作的速度。
• 使用情况
  • 表中大量NULL值
  • 数据取值范围广，随机分布
  • 某列经常在WHERE、连接、GROUP BY 或 ORDER BY 语句中
  • 表大，但查询的内容少

• 语句格式

  • CREATE [UNIQUE] INDEX 索引名 ON 表名(列名[,列名]……);
    • UNIQUE index唯一索引：在表上建立主键或唯一约束时，会自动创建
    • NONUNIQUE index非唯一索引：需手动创建
  • DROP INDEX 索引名 ON 表名
    • 删除表内索引，不需要时删除以释放磁盘空间

      视图（View）

• 作用

  • 是SELECT 子查询语句定义的一个逻辑表，虚表。（无数据）
  • 提供各种数据表现形式，简化查询语句
  • 提供安全性保护，简化用户权限管理
• 类型
  • 简单视图
    • 单表
    • 不包含函数和分组
    • 可进行DML操作
  • 复杂视图
    • 多表提取
    • 包含函数或分组
    • DML操作规则
      • 包含分组函数，或GROUP BY 子句或DISTINCT关键字则不能通过视图进行曾删改操作（INSERT/DELETE/UPDATE）。
      • 包含表达式组成的列或视图不包含引用表中那些不能为空的列则不能进行INSERT操作

• 语句格式

  • 创建视图
    • CREATE [OR REPLAC] VIEW 视图名[(别名[,别名]…))ASSELECT 语句[WITH CHECK OPTOIN]
      • OR REPLACE——如果视图存在则重建视图
      • 别名——创建视图所产生的列定义的列名
      • WITH CHECK OPTION——所插入或修改的数据行必须满足视图所定义的约束条件
  • 修改视图
    • ALTER VIEW 视图名[(别名[,别名]…))
      • 也可用ALTER 修改视图结构
  • 删除视图
    • DROP VIEW 视图名

      常量

      类型

• 字符型常量

• 数值常量
• 日期和时间常量
  • ‘2020-12-31 10:30:21’
• 布尔型常量
  • 1表示true
  • 0表示false

• NULL值

  • 适用各种字段类型，表示不确定的值
  • NULL值参与运算，结果仍为NULL值

    变量

• 类型

  • 全局变量：由系统定义和维护，无法自定义
    • 通常在名字前加@@
  • 局部变量：作用限制在程序内部，作为计数器、临时保存数据
    • 定义方式
      • SET @局部变量名 = 表达式1[,@局部变量名 = 表达式 2，······]
    • 显示方式
      • SELECT @局部变量名[,@局部变量名，···]

        常用系统函数

        字符串函数

• CHAR_LENGTH(str)

  • 返回字符串所包含的字符个数

• LENGTH(str)

  • 返回字符串的字节长度，一个汉字3字节，一个数字/字母1字节

    合并字符串函数

• CONCAT(s1,s2,······)

  • SELECT CONCAT(‘mysql版本：’,@@version) 版本信息;

    大小写转换函数

• LOWER(str)

  • 转换小写

• UPPER(str)

  • 转换大写

    删除空格函数

• LTRIM(str)

  • 删除前导空格的字符串str
• RTRIM(str)
  • 删除尾部空格的字符串str

• TRIM(str)

  • 删除前后两侧空格的字符串str

    取子串函数

• SUBSTRING(str,start,length)

• 返回emp表中ename值为s开头的雇员的信息

  • SELECT * FROM emp WHERE SUBSTRING(ename,1,1) = ‘S’；

    数字函数

• ABS(x)

  • 返回x绝对值
• PI()
  • 圆周率Π
• SQRT()
  • 返回非负数的二次方根
• MOD(m,n)
  • 返回m被n除后的余数

• ROUND(x,y)

  • x四舍五入y指定的精度返回

    日期和时间函数

• CURDATE()

  • 返回当前系统的日期，返回格式为’YYYY-MM-DD’
• YEAR(d)
  • 返回日期或日期时间d的年的值
• MONTH(d)
  • 返回日期或日期时间d的月的值

• DAY(d)

  • 返回日期或日期时间d的日的值

    系统信息函数

• USER()

  • 返回当前登录的用户名
• DATABASE()
  • 返回当前使用的数据库名

• VERSION()

  • 返回当前MySQL服务器版本号

    条件控制函数

• IF(条件表达式,v1,v2)

• CASE 表达式WHEN V1 THEN R1WHEN V2 THEN R2……[ELSE rn]END

  • 表达式= vn 则执行对应THEN 后面的结果。如果都不相等，则执行ELSE后面的结果

    程序控制流语句

    语句块

• 定义的语法格式

  • BEGINSQL 语句|SQL语句块END

• 可嵌套语句块，语句块需要封装到存储程序内部才有意义

  注释

• 单行语句注释

  • 注释语

• 多行语句注释

  • /注释语/

    重置命令结束标记

• DELIMITER 符号

  • 重置命令结束标记为@@DELIMITER @@#恢复命令结束标记为分号DELIMITER ;

    存储函数

• 创建存储函数

  • CREATE FUNCTION 函数名([参数名 参数数据类型[,···]])RETURNS 函数返回值的数据类型BEGIN 函数体； RETURN 语句;END
• 调用存储函数
  • SELECT 存储函数名([参数值[,···]])

• 删除存储函数

  • DROP FUNTION 函数名

    条件判断语句

• 在存储程序（例如存储函数、存储过程、触发器等）需要使用DECLARE语句声明局部变量

  • DECLARE 局部变量名[,局部变量名,···] 数据类型 [DEFAULT 默认值]
    • DECLARE 定义变量，变量名前不能加@
    • DECLARE定义的局部变量初始默认值为NULL
• IF语句
  • IF <条件> THEN SQL 语句块1;[ELSE SQL语句块2;]END IF;
  • IF <条件> THEN SQL 语句块1;ELSEIF <条件2> THEN SQL语句块2;···ELSE SQL语句块n;END IF;

• CASE语句

  • CASE <表达式> WHEN <表达式值 1> THEN SQL语句块1； WHEN <表达式值 2> THEN SQL语句块2；……… WHEN <表达式值 n> THEN SQL语句块n； [ELSE SQL 语句块n+1;]END;

    循环语句

• LOOP循环

  • 即无条件循环，如果没有LEAVE语句，将成为死循环（一直循环）
  • 语法格式
    • 标签:LOOP #指明LOOP循环标签 SQL语句块; IF<条件表达式> THEN #条件为真时 LEAVE 标签;#通过标签结束LOOP循环 END IF;END LOOP;

      WHILE 循环

• WHILE<条件表达式> DO SQL语句块;END WHILE;

  • 每次执行WHILE都先判断条件表达式，满足则执行循环，FALSE则立即停止。

    REPEAT循环

• REPEAT SQL 语句块; UNTIL <条件表达式>END REPEAT;

  • REPEAT循环每次执行首先执行循环内部的循环语句块结束时判断UNTIL后的条件表达式是否为true，true则结束循环否则重复内部语句块

    存储过程

• 概念性质

  • 即一条或多条SQL语句的集合
  • 存储过程可被赋予参数，可被用户、编程语言调用
  • 存储过程是已编译好的代码，调用无需再次编译，提高程序运行效率
• 创建存储过程
  • CREATE PROCEDURE 存储过程名()BEGIN 过程体;ENG
  • 存储过程的参数
    • CREATE PROCEDURE 存储过程名([IN|OUT|INOUT[参数1 数据类型,[IN|OUT|INOUT[参数1 数据类型,……)BEGIN 过程体;END
      • IN为输入参数
      • OUT为输出参数
      • INOUT参数
        • IN参数可以传入值，但不能修改。OUT参数调用时为空，执行后返回值INOUT同时具有IN和OUT参数的特性可读取写入
• 调用存储过程
  • CALL 存储过程名();

• 删除存储过程

  • DROP PROCEDURE 存储过程名;

    游标

• SELECT语句查询返回是一个由多行记录组成的集合，程序设计语言并不能处理集合形式返回的数据。SQL游标机制，游标充当指针的作用。使一次处理查询结果中的一行。

• 如果希望处理一组记录，可以使用显式游标

  • 游标处理数据的过程
    • 1. 声明游标
         • DECLARE 游标名 CURSOR FOR SELECT 语句
         • 用于获取SELECT查询结果集，包含要处理的数据，方便用户进行逐行处理
         • SELECT语句是对表或视图的查询语句，可以带WHERE/ORDER BY/GROUP BY等子句，但不能带INTO子句
    • 1. 打开游标
         • OPEN 游标名;
         • 打开游标后，游标内的查询结果集才会被传送到游标工作区，便于用户读取
    • 1. 提取数据
         • FETCH 游标名 INTO 变量名1[,变量名2……];
         • 打开游标后游标指针指向第一行之前,FETCH 语句可将游标指向下一行，第一次执行FETCH将游标中的数据保存到变量中。通过循环可逐列读取
    • 1. 关闭游标
         • CLOSE 游标名;
         • 游标使用完需要关闭游标

           异常处理

• DECLARE 错误处理类型 HANDLER FOR 错误触发条件 自定义错误处理程序;

  数据控制语言DCL