数据库相关操作

以前使用数据库都是用的比较零散，最近重新系统复习了一下MySQL的相关操作，在此做一个笔记。

MySQL的数据类型

MySQL有很多的数据类型，可以参考这里来仔细阅读，这里就不搬运了。
重点记一下数值类型、字符串类型和时间类型。

  • INT或者INTEGER是大整数类型，占用4个字节。FLOAT占用4个字节，DOUBLE8字节。与普通的数据类型没太大的区别。
  • 字符串类型常用的主要是CHAR和VARCHAR。前者是定长字符串，后者长度不定，比较灵活。区别主要是查找的时候前者略快，总体上还是VARCHAR更为常用。此外，还有TEXT这种类型，可以储存长文本。
  • 日期类型常用的主要是DATE、TIME和DATETIME。分别表示日期、时间和日期时间。

数据库常用命令

显示所有数据库：

1
SHOW DATABASES;

创建数据库：

1
CREATE DATABASE db_book;

删除数据库：

1
DROP DATABASE db_book;

表操作

表是数据库存储数据的基本单位。每个表包含若干个字段。

创建表

创建表的操作是这样的：

1
2
3
CREATE TABLE 表名 (属性名 数据类型 [完整的约束条件]，
		属性名 数据类型 [完整的约束条件]...
		);

注意，表中有一些约束条件：

约束条件 说明
PRIMARY KEY 标识该属性为该表的主键，可以唯一的标识对应的记录
FOREIGN KEY 标识该属性为该表的外键，与某表的主键关联
NOT NULL 标识该属性不能为空
UNIQUE 标识该属性的值是唯一的
AUTO_INCREMENT 标识该属性的值自动增加
DEFAULT 为该属性设置默认值

下面来看一看创建表的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE t_bookType (
	id INT PRIMARY KEY AUTO_INCREMENT ,
	bookTypeName VARCHAR(20),
	bookTypeDesc VARCHAR(200)
	);
	
CREATE TABLE t_book(
	id INT PRIMARY KEY AUTO_INCREMENT,
	bookName VARCHAR(20),
	author DECIMAL(6,2),
	bookTypeId INT,
	#外键关联
	CONSTRAINT `fk` FOREIGN KEY (`bookTypeId`) REFERENCES `t_bookType`(`id`)
	);

改查删

查看表的结构：

1
2
3
4
#查看表的结构
DESC t_bookType;
#查看创建表的语句
SHOW CREATE TABLE t_bookType;

修改表：

1
2
3
4
修改表名: ALTER TABLE 旧表名 RENMAE 新表名 ；
修改字段: ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型
增加字段: ALTER TABLE 表名 ADD 属性名 1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名 2]
删除字段: ALTER TABLE 表名 DROP 属性名

删除表：

1
删除表: DROP TABLE 表名；

查询数据

查询数据主要使用的就是SELECT语句，结构是这样的：

1
SELECT 查询字段1,2，... FROM 表名 [约束条件];

查询后显示出的结果为：
查询字段1 查询字段2 查询字段3
… … …

单表查询

  1. 查询所有字段

    1
    2
    SELECT 字段 1，字段 2，字段 3...FROM 表名；
    SELECT * FROM 表名；

  2. 查询指定字段

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名；

  3. Where 条件查询 

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 条件表达式；

  4. 带 IN 关键字 查询（表示多个字段同时查询）

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1，元素 2，元素 3)；

  5. 带 BETWEEN AND 的 范围查询 

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2；

  6. 带 LIKE 的 模糊查询 

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’；

  7. 空值查询

    1
    SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL；

  8. 带 AND 的多条件查询

    1
    SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]

  9. 带 OR 的多条件查询

    1
    SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]

    “%”代表任意字符；
    “_” 代表单个字符；

  10. DISTINCT 去重复查询 

    1
    SELECT DISTINCT 字段名 FROM 表名；

  11. 对 查询结果排序 （注意，order必须放在一条语句的最后）ASC为升序，DESC为降序。

    1
    SELECT 字段 1，字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC]

  12. GROUP BY 分组查询 

    1
    GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
    1. 单独使用(毫无意义)，一般与聚合函数一起使用；
    2. 与 GROUP_CONCAT()函数一起使用；
    3. 与 HAVING 一起使用(限制输出的结果)；
    4. 与 WITH ROLLUP 一起使用(最后加入一个总和行)；

  13. LIMIT 分页查询 

    1
    SELECT 字段 1，字段 2...FROM 表名 LIMIT 初始位置，记录数；

聚合函数查询

聚合函数是汇总数据的函数。主要有求记录条数、求和、求平均值、求最值几个方法。
聚合函数一般使用形式如下：

1
SELECT 函数(字段) [AS 别名] FROM 表；

  1. COUNT()函数:统计记录的条数
    与 GOUPE BY 关键字一起使用，表示对每个分组元素的统计信息：

    1
    SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

  2. SUN()函数：求和函数

    1
    2
    SELECT stuName, SUM(score) AS total FROM t_grade WHERE stuName = '张三';
    SELECT stuName, SUM(score) AS total FROM t_grade GROUP BY stuName;

  3. AVG()函数:求平均值的函数；

    1
    2
    SELECT stuName, AVG(score) AS total FROM t_grade WHERE stuName = '张三';
    SELECT stuName, AVG(score) AS total FROM t_grade GROUP BY stuName;

  4. MAX()函数:求最大值的函数

  5. MIN()函数:求最小值的函数

连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

  • 内连接获取两个表中字段匹配关系的记录，典型用法是这样的：
    1
    SELECT * FROM t_book, t_booktype WHERE t_book.`bookTypeId` = t_booktype.`id`;

此时，显示左表中bookTypeId和右表的id相同的部分。

  • 但我们需要更多的功能，比如右表中即使没有相同的一项，也要列出来，那么就需要外连接。外连接分为左连接和右连接。分别是对左表取并集和对右表取并集。
    这是左连接，如果右表没有对应的id，那么就用null代替。

    1
    SELECT * FROM t_book LEFT JOIN t_booktype ON t_book.`bookTypeId` = t_booktype.`id`;

  • 多条件连接查询就是在内连接的基础上，增加筛选语句，使用AND并列查询。

    1
    SELECT tb.bookName, tb.price, tb1.`bookTypeName` FROM t_book tb, t_booktype tb1 WHERE tb.`bookTypeId` = tb1.`id` AND tb.`price` > 80;

子查询

子查询是一个复查询，即将一个语句的运算结果带入另一个语句的查询之中。

  1. In关键字:范围性的子查询，一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。

    1
    SELECT * FROM t_book WHERE bookTypeId IN( SELECT id FROM t_booktype );

  2. 带比较运算符的子查询：

    1
    SELECT * FROM t_pricelevel WHERE price <= (SELECT price FROM t_pricelevel WHERE priceLevel = 1);

  3. exists子查询：判断性的子查询，若子查询查到记录，则进行外层查询，否则不执行外层查询。

    1
    SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

  4. any子查询：表示满足任一条件即可查询

    1
    SELECT * FROM t_pricelevel WHERE price >= ANY (SELECT price FROM t_pricelevel);

  5. all子查询：表示满足所有的条件才可查询

合并查询结果

将两个查询结果放在一起

1
SELECT id FROM t_book UNION SELECT id FROM t_booktype;

注意，UNION会自动去重，而UNION ALL则不会去重，将所有结果都显示。

别名

在查询的时候对表或字段起一个别名，从而可以直观方便的使用
为表取别名： 表名 表的别名
为字段取别名： 属性名 [AS] 别名

数据操作

在上一节学习了查询的相关操作，本次继续学习增删改的操作。

插入数据

  1. 给表的所有字段插入数据

    1
    INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n);

  2. 给表的指定字段插入数据

    1
    INSERT INTO 表名(属性 1，属性 2，...，属性 n) VALUES(值 1，值 2，值 3，...，值 n);

  3. 同时插入多条记录

    1
    INSERT INTO 表名 [(属性列表)] VALUES(取值列表 1)，(取值列表 2)...;

更新数据

1
2
3
4
UPDATE 表名 
SET 属性名 1=取值 1，属性名 2=取值 2，...，
属性名 n=取值 n
WHERE 条件表达式；

删除数据

1
DELETE FROM 表名 [WHERE 条件表达式]

索引

索引是数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度，类似于图书中的目录，方便快速定位，寻找指定的内容。

索引分类

  1. 普通索引
    这类索引可以创建在任何数据类型中；
  2. 唯一性索引
    使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；
  3. 全文索引
    使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；
  4. 单列索引
    在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引；
  5. 多列索引
    多列索引是在表的多个字段上创建一个索引；
  6. 空间索引
    使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数
    据的效率；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

创建索引

  1. 创建表的时候创建索引

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
    	属性名 数据类型 [完整性约束条件],
    	....
    	属性名 数据类型
    	[UNIQUE | FULLTEXT | SPATIAL ] INDEX| KEY
    	[别名]  (属性名 1 [(长度)] [ASC | DESC])
    	);

  2. 在已经存在的表上创建索引

    1
    CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [(长度)] [ ASC | DESC])；

5.3 用 ALTER TABLE 语句来创建索引

1
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC])

删除索引

1
DROP INDEX 索引名 ON 表名 ；

更改索引

即删除+创建：

1
ALTER TABLE `db_book`.`t_user4` DROP INDEX `index_userName_password`, ADD INDEX `index_userName_password` (`userName`);

视图

视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图使操作简便化，增加数据的安全性以及提高表的逻辑独立性。

创建视图

1
2
3
4
CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
	VIEW 视图名 [ ( 属性清单) ]
	AS SELECT 语句
	[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
  • ALGORITHM 是可选参数，表示视图选择的算法；
  • “视图名”参数表示要创建的视图的名称；
  • “属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同；
  • SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；
  • WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内；

ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中，UNDEFINED 选项表示 MySQL 将自动选择所要使用的算法；MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分；TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句；CASCADED是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；LOCAL 表示更新视图时，要满足该视图本身的定义条件即可。

查看视图

  1. DESCRIBE 语句查看视图基本信息
  2. SHOW TABLE STATUS LIKE ‘视图名’ 语句查看视图基本信息
  3. SHOW CREATE VIEW 视图名 语句查看视图详细信息
  4. 在 views 表中查看视图详细信息

修改视图

修改视图实际上就是修改视图中显示的字段

  1. CREATE OR REPLACE VIEW 语句创建或修改视图

    1
    2
    3
    4
    CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
    VIEW 视图名 [( 属性清单 )]
    AS SELECT 语句
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

  2. ALTER 语句修改视图

    1
    2
    3
    4
    ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
    VIEW 视图名 [( 属性清单 )]
    AS SELECT 语句
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

更改视图

更新视图是指通过视图来插入（INSERT） 、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新，也就是在原表中进行更改。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。
增删改的操作同对普通表的操作类似。

删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；

1
DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]

触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。
当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

创建与使用触发器

  1. 创建只有一个执行语句的触发器
    1
    CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

典型用法是这样的：

1
2
3
CREATE TRIGGER trig_bookType AFTER INSERT
	ON t_book FOR EACH ROW
	UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.`id`;

其中需要注意的是，new表示过程对象，就是指代正在插入的这条语句。相应的，old表示正在删除的对象。

  1. 创建有多个执行语句的触发器
    1
    2
    3
    4
    5
    CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 
    ON 表名 FOR EACH ROW
    BEGIN
    执行语句列表
    END

查看触发器

1
SHOW TRIGGERS;

删除触发器

1
DROP TRIGGER 触发器名;

MySQL相关函数

有很多的内置具体的用法可以在手册中详细查询。这里举几个典型的例子：

日期和时间函数

  • NOW() 当前时间
  • CURDATE() 返回当前日期；
  • CURTIME() 返回当前时间；
  • MONTH(d) 返回日期 d 中的月份值，范围是 1~12

字符串函数

  • CHAR_LENGTH(s) 计算字符串 s 的字符数；
  • UPPER(s) 把所有字母变成大写字母；
  • LOWER(s) 把所有字母变成小写字母；

数学函数

  • ABS(x) 求绝对值
  • SQRT(x) 求平方根
  • MOD(x,y) 求余

加密函数

  • PASSWORD(str) 一般对用户的密码加密 不可逆
  • MD5(str) 普通加密 不可逆
  • ENCODE(str，pswd_str) 加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它；
  • DECODE(crypt_str，pswd_str) 解密函数；

存储过程和函数

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。 存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。 而且，存储过程和函数是在 MySQL 服务器中存储和执行的，可以减少客户端和服务器端的数据传输。
总而言之，存储过程类似于方法的封装，存储过程简单、安全、高性能。

创建存储过程

创建存储过程的操作是这样：

1
2
CREATE PROCEDURE sp_name([proc_parameter[,...]])
	[characteristic...] routine_body

其中：

  • sp_name 参数是存储过程的名称；
  • proc_parameter 表示存储过程的参数列表；
  • characteristic 参数指定存储过程的特性；
  • routine_body 参数是 SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束。
  • proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。
    [ IN | OUT | INOUT ] param_name type
    其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
    存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型；
  • Characteristic 参数有多个取值。其取值说明如下：

创建存储函数

存储函数和存储过程非常相似，主要区别是存储函数更偏向函数

1
2
3
CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body

其中：

  • sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；
  • RETURNS type 指定返回值的类型；
  • characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；
  • routine_body 参数是 SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束；
  • func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
  • param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型，该类型可以是 MySQL 数据库的任意数据类型；

变量的使用

变量是在存储过程和函数中定义的中间变量。注意，只有在存储过程和存储函数中存在的中间变量才有意义，变量不能单独的存在。
定义变量

1
DECLARE var_name [,...] type [ DEFAULT value ]

赋值：

1
2
SET var_name = expr [,var_name=expr] ...
SELECT col_name[,...] INTO var_name[,...] FROM table_name WHERE condition

游标

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。
游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

  • 声明游标

    1
    DECLARE cursor_name CURSOR FOR select_statement ;

  • 打开游标

    1
    OPEN cursor_name;

  • 使用游标

    1
    FETCH cursor_name INTO var_name [,var_name ... ]；

关闭游标

1
CLOSE cursor_name；

流程控制的使用

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。这里就不再展开，详细的用法可以在手册进行查询。

调用存储过程和函数

  • 调用存储过程

    1
    CALL sp_name( [parameter[,...]] )

  • 调用存储函数

    1
    fun_name( [parameter[,...]] )

查看存储过程和函数

  • SHOW STATUS 语句查看存储过程和函数的状态

    1
    SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;

  • SHOW CREATE 语句查看存储过程的函数的定义

    1
    SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;

修改存储过程和函数

一般来说修改比较麻烦，不如重新建立函数关系。

1
2
3
4
5
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’

  • sp_name 参数表示存储过程或函数的名称；
  • characteristic 参数指定函数的特性。
  • CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；
  • NO SQL 表示子程序中不包含 SQL 语句；
  • READS SQL DATA表 示 子 程 序 中 包 含 数 据 的 语 句 ；
  • MODIFIES SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。
  • SQLSECURITY{ DEFINER | INVODER } 指明谁有权限来执行。
  • DEFINER 表示只有定义者自己才能够执行；
  • INVODER 表示调用者可以执行。
  • COMMENT ‘string’ 是注释信息。

删除存储过程和函数

1
DROP {PROCEDURE | FUNCTION } sp_name ;

数据备份与还原

备份数据可以保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份；

数据备份

在命令行中，执行这个语句：

1
mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

  • dbname 参数表示数据库的名称；
  • table1 和 table2 参数表示表的名称，没有该参数时将备份整个数据库；
  • BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。

数据还原

1
Mysql -u root -p [dbname] < backup.sql

dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。