MySQL基本操作 | 小尹的博客

SQL 概述

简述：

• 提出：Boyce & Chamberlin in 1974
• 实现：IBM研制的System R系统
• 发展：1986年ANSI颁布 -> 1987年ISO采纳为国际标准 -> SQL-89 -> SQL-92(SQL2) -> SQL-99(SQL3)

组成：

• 数据定义语言（Data Definition Language, DLL）

  作用于数据库或数据库对象（表、约束、规则、视图、触发器、存储过程）

  CREATE 创建；ALTER 修改；DROP 删除

• 数据操纵语言（Data Manipulation Language, DML）

  作用于表或视图

  SELECT 检索；INSERT 插入；UPDATE 修改；DELETE 删除

• 数据控制语言（Data Control Language, DCL）

  用于安全（权限）管理

  GRANT 授予权限；REMOVE 收回权限

• 嵌入式和动态SQL规则：规定SQL语句在高级程序设计语言中使用的规范方法

• SQL调用和会话规则

  SQL调用包括SQL例程和调用规则，使SQL具有更多高级语言特征；SQL会话规则可使应用程序连接到多个SQL服务器中的某一个

MySQL中的SQL

遵循SQL标准部分

1. DDL、DML、DCL

2. 支持关系数据库三级模式结构：

   外模式：视图（View）和部分基本表（Table）

   模式：若干基本表

   内模式：若干存储文件

   一个或多个基本表对应一个存储文件，一个表的若干索引存放在存储文件，存储文件的逻辑结构组成了MySQL的内模式；

   数据库只存储视图的定义，不存储视图对应的数据，因此视图时一个虚表

扩展部分

1. 常量

   • 字符串常量，””或’’括起来，ASCLL和Unicode
   • 数值常量，整数和浮点数常量
   • 十六进制通常指定为字符串常量，在其最前面有一个X或x
   • 日期常量，’’
   • 位，b’value’，value替换为一个二进制值
   • 布尔值，TRUE(1) FLASE(0)
   • NULL, != 0 && != ‘’

2. 变量

   • 系统变量
     • 全局变量
     • 会话变量
   • 自定义变量
     • 用户变量
     • 局部变量

3. 运算符

   • 算术运算符：+ - * / %
   • 位运算符：& | ^ ~(位取反) >>(位右移) <<
   • 比较运算符：= > < >= >= <>(不等于) !=(不等于) <=>(相等或都等于空)
   • 逻辑运算符：NOT ! AND && OR || XOR(逻辑异或)

4. 表达式

   根据值的数据类型分为：字符型表达式、数值型表达式、日期型表达式

5. 内置函数

   • 数学函数：ABS()、SORT()…
   • 聚合函数：COUNT()…
   • 字符串函数：ASCII()返回字符串第一个字符的 ASCII 码…
   • 日期和时间函数：NOW()、YEAR()…
   • 加密函数：ENCODE()、ENCRYPT()…
   • 控制流程函数：IF()、IFNULL()…
   • 格式化函数：FORMATE()…
   • 类型转换函数：CAST()…
   • 系统信息函数：USER()、VERSION()….

数据定义

数据库定义

1. 创建数据库

   CREATE DATABASE IF NOT EXISTS db_name 
   CHARACTER SET utf8 
   COLLATE utf8_general_ci;

   CHARSET SET指定字符集，COLLATE指定字符集校对规则，IF NOT EXISTS可避免存在相同名称的数据库而报错。

   DATABASE可以替换为SCHEMA，在MySQL5中，这两个语法功能是一致的。

2. 选择数据库

   USE db_name;

3. 修改数据库

   ALTER DATABASE db_name
   CHARACTER SET gb2312
   COLLATE gb2312_chinese_ci;

4. 删除数据库

   DROP DATABASE IF EXISTS test;

5. 查看数据库

   SHOW DATABASES;

   将显示当前用户可查看的数据库列表，后面可跟上LIKE或WHERE关键字，实现模糊查询或条件查询。

常用数据类型

类型	用途	大小
INT	整型	4字节
TINYINT	短整型	1字节
BIGINT	长整型	8字节
FLOAT	单精度浮点型	4字节
DOUBLE	双精度浮点型	8字节
DECIMAL(M,D)	精确小数，M为有效数字精度，D为小数点后位数，D<=M	M(1-65); D(0-30)
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
CHAR	定长字符串	0-255字节
VARCHAR	变长字符串	0-65535字节

• 一个汉字占多少长度与编码有关：

  UTF-8: 3个字节

  GBK: 2个字节

• varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

表定义

1. 创建表

   USE db_name;
   CREATE TABLE IF NOT EXISTS tb_name(
      id INT UNSIGNED AUTO_INCREMENT,
      title VARCHAR(100) NOT NULL,
      author VARCHAR(40) NOT NULL,
      cTime DATETIME DEFAULT NOW(),
      PRIMARY KEY ( id )
   )ENGINE=InnoDB;

   • 若将CREATE TABLE改为CREATE TEMPORARY TABLE，可创建临时表，临时表只对创建者可见，并且在断开数据库连接时，MySQL将自动删除之
   • AUTO_INCREMENT，可以为表中整型列设置自增属性，当插入一个NULL或0到该列时，值会自动设置为该列最大值+1，初始值为1，每个表只能有一个AUTO_INCREMENT列，并且必须被索引。
   • 当INSERT一个值到AUTO_INCREMENT列时，INSERT的值会覆盖自增长的值，并且后续的增量将基于INSERT的值
   • DEFAULT可以为该列指定默认值
   • 若不指定NOT NULL，该列默认允许NULL
   • ENGINE=引擎类型（可选）

2. 更新表

   ALTER TABLE db_name.tb_name
   ADD COLUMN publisher VARCHAR(20) NOT NULL AFTER author;

   ADD [COLUMN, PRIMARY KEY, FOREIGN KEY, INDEX]，分别为表添加列，主键，外键，索引

   ALTER TABLE db_name.tb_name
   CHANGE COLUMN publisher pName CHAR(20) NULL;

   CHANGE COLUMN：修改列名、数据类型、是否为空、默认值、位置移动等

   ALTER COLUMN：设置或删除列的默认值（操作速度非常快），删除加上DROP DEFAULT

   MODIFY COLUMN：除了不能给列重命名之外，和CHANGE COLUMN是一样的

   ALTER TABLE db_name.tb_name
   DROP COLUMN publisher;

   DROP [COLUMN, PRIMARY KEY, FOREIGN KEY, INDEX]，分别为卸除表的列及其所有数据，主键，外键，索引

3. 重命名表

   RENAME TABLE tb1_name TO tb1_new_name, tb2_name TO tb2_new_name;

4. 删除表

   DROP TABLE IF EXISTS tb_name;

   • 删除临时表DROP TEMPORARY TABLE
   • 最后加上关键字RESTRICT表示，确保只有不存在相关视图和完整性约束的表才能删除
   • 最后加上关键字CASCADE表示，任何相关视图和完整性约束一并被删除

5. 查看表

   USE db_name;
   SHOW TABLES;

   显示数据库所有表。

   SHOW COLUMNS FROM tb_name FROM test;
   DESCRIBE tb_name;

   显示表结构的两种方式，第一种方式FROM和IN关键字可互换，第二种方式DESCRIBE可简写为DESC

索引

索引概述

索引实质上是，一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。

过多使用索引会增加系统开销，因为索引存在一些弊端：

1. 索引是以文件形式存储地，DBMS会将一个表地所有索引保存在同一个索引文件中。如果拥有大量索引，索引文件可能会比数据文件更快地达到最大文件尺寸。尤其是在一个大表上创建了多种组合索引，索引文件会膨胀得非常快。
2. 索引会降低表的更新速度，更新表中索引列上的数据时，索引会被自动更新，由此降低INSERT、UPDATE、DELETE和其他写入操作的效率。表中的所有越多，更新表的时间就会越长。

根据具体用途，索引在逻辑上通常包含如下几类：

1. 普通索引（INDEX）

   最基本的索引类型，没有任何限制，使用关键字INDEX或KEY

2. 唯一性索引（UNIQUE）

   和普通索引基本相同，并且索引列中所有值都只能出现一次，使用关键字UNIQUE

3. 主键（PRIMARY KEY）

   主键是一种唯一性索引，且不能有空值，每个表只能有一个主键，使用关键字PRIMARY KEY

单列索引很好理解，就是一个只包含原表的一个列；而组合索引（也称为多列索引或复合索引）就是原表中的多列共同组成的一个索引。

一个组合索引实质上为表的查询提供了多个索引，例如，在一个表上创建了一个组合索引(col1, col2, col3)，在实际的查询中，实际上可能是三个索引来加快查询速度：单列索引(col1)、组合索引(col1, col2)、组合索引(col1, col2, col3)

索引定义

1. 索引的创建

   CREATE INDEX index_name
   ON table_name(col_name1, col_name2,...);

• 若创建唯一性索引，使用CREATE UNIQUE INDEX

• 通常将查询语句中的WHERE子句和JOIN子句里出现的列作为索引列

• col_name (length)，指定使用列的前length个字符来创建索引，节省磁盘空间

• col_name ASC|DESC，指定索引按升序还是降序排列，默认ASC升序

  CREATE TABLE tb_name
  (
  	...,
  	INDEX index_name(col3),
  	PRIMARY KEY(col1, col2),
  	UNIQUE index_name(col4)
      FOREIGN KEY index_name(col5, ...)
  )

• 关键字KEY和INDEX为同义词，可以互换

• 定义主键和UNIQUE键或外键时，可以在前面加上CONSTRAINT key_name，定义一个名字

  ALTER TABLE tb_name
  ADD INDEX|UNIQUE|PRIMARY KEY|FOREIGN KEY ...

• …语法同上

2. 索引的查看

   SHOW INDEX FROM table_name FROM db_name

   • SHOW INDEX|INDEXES|KEYS
   • FROM可替换为IN
   • 最后可以跟WHERE

3. 索引的删除

   DROP INDEX index_name ON table_name

   ALTER TABLE table_name
   DROP PRIMARY KEY
   DROP INDEX index_name
   ...;

数据更新

1. 插入数据

   INSERT INTO tb_name(col1, col2, ...)
   VALUES (expr|DEFAULT)

   • expr表示一个常量、变量、NULL或表达式，数据类型须和列保持一致，字符型须用单引号括起
   • 如果列已指定默认值，可以用DEFAULT插入该列默认值，否则用DEFAULT会报错

   INSERT INTO tb_name
   SET col1=expr|DEFAULT, col2=expr|DEFAULT,...

   INSERT INTO tb_name(col1, col2, ...)
   SELECT col1, col2 ...

2. 删除数据

   DELETE FROM tb_name
   WHERE where_condition
   ORDER BY ...
   LIMIT row_count;

   • ORDER BY和LIMIT一般连用
   • ORDER BY，各行按照子句中指定的顺序进行删除
   • LIMIT row_count用于告知服务器在控制命令被返回到客户端前被删除的行的最大值（本次删除，最多删除多少条记录）

3. 修改数据

   UPDATE tb_name
   SET col1=expr|DEFAULT, col2=expr|DEFAULT,...
   WHERE where_condition
   ORDER BY ...
   LIMIT row_count;

数据查询

要素过多，暂略，待日后补充

LIMITE

LIMIT offset, rowcount
LIMIT rowcount OFFSET offset

• offset为可选项，默认为数字0，用于指定返回数据的第一行，在SELECT语句结果集中的偏移量
• rowcount用于指定返回数据的行数，大于实际能返回的行数时，返回能返回的数据行
• 综上，LIMIT语句含义是，从第offset+1行开始，取row_count行

例如，在数据库crm的表customers中，查找从第5位客户开始的3位客户的id和姓名

SELECT cust_id, cust_name FROM crm.customers
ORDER BY cust_id
LIMIT 4, 3;

视图

视图概述

• 视图(View)对应三级模式中的外模式
• 不是真实的表，是一张虚拟表，其结构和数据建立在对真实表的查询基础上
• 视图的数据式在引用视图时动态生成的，其自身不存储数据
• 与基本表一样可以增、删、改、查
• 视图的优点有：
  1. 集中分散数据
  2. 简化查询语句
  3. 重用SQL语句
  4. 保护数据安全
  5. 共享所需数据
  6. 更改数据格式

视图定义

1. 创建视图

   CREATE VIEW view_name (column_list)
   AS select_statement

   • view_name指定视图名称，不能与其他表和视图同名
   • column_list是可选项，为视图中每个列指定名称，列名数量等于SELECT语句结果集的列数
   • select_statment就是创建视图的SELECT语句

   例如创建一行王姓客户信息的视图

   CREATE VIEW customer_wang_view
   AS
   SELECT * FROM crm.customers
   WHERE cust_name LIKE '王%'
   WITH CHECK OPTION;

   • WITH CHECK OPTION是可选项，强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。 比如，如果用INSERT语句向这个视图添加一个名叫’张飞’的客户就会报错。
   • WITH CHECK OPTION一句话解释：通过视图进行的修改，必须保证能通过该视图看到修改后的结果。

2. 删除视图

   DROP VIEW IF EXISTS view_name1, viw_name2...

   • 最后可以加上关键字RESTRICT和CASCAD，和删除表时同样的效果

3. 修改视图定义

   和创建视图类似，吧CREATE改为ALTER即可

4. 查看视图定义

   SHOW CREATE VIEW view_name

5. 更新视图数据注意事项

   • INSERT：当视图依赖于多个表时，无法INSERT；当视图WITH CHECK OPTION时，不满足视图WHERE条件也无法INSERT
   • UPDATE：当视图依赖于多个表时，一次UPDATE只能改变一个基本表的数据
   • DELETE：当视图依赖于多个表时，无法DELETE

数据库编程

实现多条SQL，处理多个数据表的需求。

常用的两种数据库编程是存储过程和存储函数。

存储过程

存储过程概述

存储过程是一组为了完成某项特定功能的SQL语句集，实质是存储在数据库中的代码，由声明式SQL语句（CREATE、UPDATE、SELECT）和过程式SQL语句（IF…THEN…ELSE）组成。

这组语句集经过编译后存储在数据库中，用过通过指定的名字调用，如果该存储过程带有参数，则须给定参数。

在不同的应用程序或平台执行相同的功能时，存储过程尤为适合。

存储过程的优点：

1. 增强SQL语言的功能和灵活性
2. 良好的封装性
3. 高性能
4. 可减少网络流量
5. 存储过程作为一种安全机制来确保数据库的安全性和数据的完整性

存储过程操作

1. 创建

   DELIMITER $$

• 存储过程的结束符号时;，SQL语句的结束符号也是;，为了避免只执行一条SQL，通过DELIMITER命令，修改SQL语句的结束符，$$可以为任意符号，处理完存储过程后，再通过 DELIMITER ; 修改回来

  CREATE PROCEDURE sp_name(IN param_name1 type, OUT param_name2 type, INOUT param_name3 type, ...)
  BEGIN
  routine_body
  END

• sp_name指定存储过程名称

• 括号里面是参数列表，type为参数类型，没有参数时也需要加上括号

• IN、OUT、INOUT代表三种类型的参数：输入参数、输出参数、输入/输出参数

• 输入参数是传递给存储过程，输出参数是返回一个结果，输入/输出参数即可以充当输入也可以充当输出参数

• 参数的取名不要与数据表的列名相同

  DELIMITER @@
  CREATE PROCEDURE sp_update_sex(IN cid INT, IN csex CHAR(1))
  BEGIN
  	UPDATE customer SET cust_sex = csex WHERE cust_id = cid;
  END @@

2. 局部变量

   DECLARE var_name type DEFAULT value

   • var_name指定变量名称，type指定变量数据类型，DEFAULT指定默认值，若没有则默认为NULL
   • 局部变量只能在BEGIN…END语句块中的开头处声明
   • 其他语句块不能使用
   • 局部变量和用户变量区别是，用户变量声明时，在其名称前使用@符号，已声明的用户变量存在于整个会话之中

3. SET语句

   SET var_name = expr

   DECLARE i INT DEFAULT 0
   SET i = 1

4. SELECT INTO

   SELECT col_name, ... INTO var_name, ... table_expr

   • col_name指查询结果集的列名，var_name指要赋值的变量名，tale_expr指FROM..WHERE等部分
   • 返回的结果集只能有一行数据

5. IF…THEN…ELSE

   IF monthly_value <= 4000 THEN
       SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
   	SET income_level = 'Avg Income';
   ELSE
       SET income_level = 'High Income';
   END IF;

6. 循环

   # WHILE语句演示
   DELIMITER $$
   CREATE PROCEDURE sp_while(IN in_count INT)
   BEGIN
       DECLARE COUNT INT DEFAULT 0;
       DECLARE SUM INT DEFAULT 0;
       WHILE COUNT < in_count DO
           SET SUM = SUM + COUNT;
           SET COUNT = COUNT + 1;
       END WHILE;
       SELECT SUM;
   END $$
   DELIMITER ;
   
   CALL sp_while(10); # 45 输出
   
   DROP PROCEDURE sp_while; #删除

   #REPEAT语句演示
   REPEAT
   Statements;
   UNTIL expression
   END REPEAT;

   • 在REPEAT语句中不管是否满足给定条件，首先会执行一次statements, 然后再在UTILE中判断给定的条件是否成立，如果条件不成立会继续执行，如果条件成立则退出REPEAT循环。

   #LOOP语句演示
   DELIMITER $$
   CREATE PROCEDURE sp_loop()
   BEGIN
       DECLARE t INT DEFAULT 0;
       loop_name:LOOP
           SET t = t + 1;
           IF t > 10 THEN 
           	LEAVE loop_name;
           END IF;
       END LOOP loop_name;
       
       SELECT t;
   END $$
   DELIMITER ;

   • LEAVE相当于高级语言中的break，也可以使用ITERATE相当于continue

7. 游标

   • SELECT INTO只能返回一行数据，使用SELECT语句可以返回多行数据，这时就需要使用游标处理多行数据。

   • 游标是一个被SELECT语句检索出来的结果集。

   #声明游标
   DECLARE cursor_name CURSOR FOR select_statement
   #打开游标
   OPEN cursor_name
   #读取数据
   FETCH cursor_name INTO var_name1, var_name2, ...
   #定义CONTINUE句柄（游标循环结束条件）
   DECLARE CONTINUE HANDLER FOR NOT FOUND
   SET done=TRUE
   #关闭游标
   CLOSE cursor_name

   • 声明游标不能含有SELECT INTO子句
   • 使用游标前必须打开游标，cursor_name指定要打开的游标
   • 一个游标可能被打开多次，由于其他用户或应用程序可能谁是更新了数据，因此每次打开游标的结果集可能会不同
   • FETCH INTO是将游标指向的一行赋给一些变量
   • 游标相当于一个指针，指向当前行的数据
   • 当出现NOT FOUND时，done=TURE
   • 每个游标不再使用是都应该被关闭，CLOSE语句会释放游标所使用的全部资源，没有关闭的游标不会在END语句时自动关闭
   • 游标被关闭后如果重新OPEN，不需要再次声明
   • MySQL5中游标只能用于存储过程或存储函数
   • 一个BEGIN END语句块中，每个游标名字唯一

8. 调用

   CALL sp_name
   CALL another_sp_name(1, 'M')

9. 删除

   DROP PROCEDURE IF EXISTS sp_name

   • RESTRICT|CASECADE同上

存储函数

和存储过程区别：

• 存储函数不能拥有输出参数
• 调用存储函数不需要CALL语句
• 存储函数必须包含一条RETURN语句

创建存储函数：

DELIMITER $$
CREATE FUNCTION get_cust_name(id int) 
    RETURNS VARCHAR(50)
    #确定性函数用关键词DETERMINISTIC标识，表示函数的返回值完全由输入参数决定。
    DETERMINISTIC
BEGIN
   DECLARE cust_name VARCHAR(50);
   SET cust_name = (SELECT cust_name from customer where cust_id=id);
   RETURN (name);
end$$
DELIMITER ;

调用存储函数：

SELECT get_cust_name(8);

删除存储函数：

DROP FUNCTION IF EXISTS get_cust_name;