mysql中的sql

Molet 常用工具下载 2023-02-26 810浏览 0

变量

用户变量: 在用户变量前加@

系统变量: 在系统变量前加@@

运算符

算术运算符有: +(加), -(减), * (乘), / (除) 和％ (求模) 五中运算
位运算符有: & (位于), | (位或), ^ (位异或), ~ (位取反), >> (位右移), << (位左移)
比较运算符有: = (等于), > (大于), < (小于), >= (大于等于), <= (小于等于), <> (不等于), != (不等于), <=> (相等或都等于空).
逻辑运算符有: NOT 或! (逻辑非), AND 或 && (逻辑于), OR 或|| (逻辑或), XOR (逻辑异或)

内置函数

mysql中包含了100多个内置函数: https://i.cnblogs.com/EditPosts.aspx?opt=1

数据定义

数据定义语句
	操作对象	操作方式
	创建	删除	修改
SQL	模式	CTREATE SCHEMA语句	DROP SCHEMA语句
	表	CREATE TABLE语句	DROP TABLE语句	ALTER TABLE语句
	视图	CREATE VIEW 语句	DROP VIEW 语句
mysql增加的	模式	ALTER SCHEMA
	模式
	视图	ALTER VIEW

创建数据库

CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] dbI_name  # [] 表示可选, | (或)用于分隔花括号中的选择项 , db_name 数据库名
, ISNOT EXISTS 表示待命名的数据库不存在才会创建

[DEFAULT]CHARACTRER SET [=] charse_name    # DEFAULT用于指定默认值, CHARACTER SET 用于指定字符集

|[DEFAULT] COLLATE[=] collation_name       #COLLATE用于指定字符集的小队规则

Integer rs = stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS mysql_test DEFAULT CHARACTER SET = utf8"); 

//结果集, executeQuery()执行select语句 返回结果在result里

选择数据库

USE db_name

Integer rs = stmt.executeUpdate("USE db_name");

修改数据库

ALTER DATABASE或ALTER SCHEMA修改数据库相关参数,其语法简略为

ALTER {DATABASE|SCHEMA} [db_name]
        alter_specification...

示例:

Integer rs = stmt.executeUpdate("ALTER DATABASE mysql_test DEFAULT CHARACTER SET gb2312"
                    + " DEFAULT COLLATE gb2312_chinese_ci");

删除数据库

DROP DATABASE 和 DROP SCHEMA 删除数据库 //数据无价,操作需谨慎

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name

Integer rs = stmt.executeUpdate("DROP DATABASE IF EXISTS mysql_test1");

查看数据库

SHOW {DATABASES 和SHOW SCHEMAS} {LIKE ‘pattern’| WHERE expr}

ResultSet rs1 = stmt.executeQuery("SHOW DATABASES");

表定义

创建表

CREATE TABLE语句创建表,基本格式是

CREATE [TEMPORARY] TABLE tbl_name
(
字段1 数据类型 [列级完整性约束条件] [默认值]
[, 字段2 数据类型 [列级完整约束条件][默认值]]
[,......]
  [,表级完整约束条件]
)(ENGINE=引擎类型)

USE mysql_test;
CREATE TABLE customers
(
cust_id INT NOT NULL AUTO_INCREMENT, #INCREMENT 可以为表中的数据类型为整形的列设置自增型
cust_name CHAR(50) NOT NULL,         #不允许null值的列
cust_set CHAR(1) NOT NULL DEFAULT 0, #不允许null值的列且默认值是0
cust_address CHAR(50) NULL,
cust_contact CHAR(50) NULL,
PRIMARY KEY(cust_id)   #设置cust_id 为主键
);

复制表结构

mysql> CREATE TABLE cus LIKE customer;
Query OK, 0 rows affected (0.73 sec)

复制整张表

mysql> CREATE TABLE cus1 AS SELECT * FROM customer;
Query OK, 99 rows affected (0.73 sec)
Records: 99  Duplicates: 0  Warnings: 0

更新表

1. ADD[COLUMN] 增加列子句

mysql> ALTER TABLE mysql_test.customers　　#改变表sustomers 增加(add) 列(column) cust_city 字符型(长度10)
    ->  ADD COLUMN cust_city char(10) NOT NULL DEFAULT 'Wuhan' AFTER cust_set;#不能为null 默认值为武汉 且位于 cust_set列之后
Query OK, 0 rows affected (1.55 sec)

mysql> ALTER TABLE mysql_test.customers ADD INDEX index1(sex1); //为列sex1增加索引index1
Query OK, 0 rows affected (0.57 sec)

mysql> ALTER TABLE mysql_test.cust ADD FOREIGN KEY(cust_address) REFERENCES customers(sex1);#为表cust的列cust_address添加外键参考表customers 的列sex1
Query OK, 0 rows affected (1.55 sec)

2. CHANGE[COLUMN] 子句修改表中列的名称或数据类型

mysql> ALTER TABLE mysql_test.customers #将表customers 的列cust_set名改为sex  数据类型改为char(1)
    -> CHANGE COLUMN sex sex1 char(1) NULL DEFAULT 'M';#可以为null 默认值为m
Query OK, 0 rows affected (0.21 sec)#需要注意不兼容数据类型不可以转换,兼容但长度不一样的数据类型,数据可能会被截断

3. ALTER[COLUMN]子句修改或删除指定列的默认值

mysql> ALTER TABLE mysql_test.customers #修改(alter)cust　　omers 的列 cust_city 的默认值(default)设置(set)为bj;
    -> ALTER COLUMN cust_city SET DEFAULT 'bj';
Query OK, 0 rows affected (0.14 sec)

4. MODIFY[COLUMN]子句修改指定列的数据类型,而不干涉列名,可以通过”FIRST”或”AFTER”修改指定类在表中的位置

mysql> ALTER TABLE mysql_test.customers   #moddify(修改) 列 cust_name 类型为char(20) ,位置改为 sex1后面
    -> MODIFY COLUMN cust_name char(20) AFTER sex1;
Query OK, 0 rows affected (1.80 sec)

5. DROP[COLUMN]子句删除列

mysql> ALTER TABLE mysql_test.customers DROP COLUMN cust_name;#删除列cust_name
Query OK, 0 rows affected (1.19 sec)

6.RENAME[TO]子句重命名表

mysql> ALTER TABLE mysql_test.cust RENAME TO mysql_test.cus;#将cust更名为cus
Query OK, 0 rows affected (0.40 sec)

重命名表

RENAME TABLE tbl_name TO new_tbl_name [tb2_name TO new_tb2_name] #rename可以批量重命名表

mysql> RENAME TABLE mysql_test.cu TO mysql_test.cus, mysql_test.customers TO customer;
Query OK, 0 rows affected (0.70 sec)

删除表

语法格式

DROP[TEMPORARY]TABLE[IF EXISTS]
    tb1_name[,tb2_name] ... #

restrict: 确保只有不存在相关视图和 完整性约束的表才能删除

[RESTRICT][CASCADE] #在删除一个表时，如果该表的主键是另一个表的外键，如果不用cascade关键字就会报错

mysql> DROP TABLE IF EXISTS mysql_test.cus CASCADE;#删除表cus 使用CASCADE删除完整性约束
Query OK, 0 rows affected (0.41 sec)

查看表

1.显示表名

SHOW [FULL] TABLES [{FROM|IN} db_name] [LIKE 'pattern' [WHERE expr]

mysql> SHOW TABLES FROM gc LIKE 'b%s';
+--------------------+
| Tables_in_gc (b%s) |
+--------------------+
| books              |
+--------------------+
1 row in set (0.00 sec)

2.显示表的结构

SHOW [FULL] COLUMNS {FROM|IN} tbl_name [{FROM|IN} db_name] [LIKE 'pattern'| WHERE expr]

DESCRIBE tbl_name;

mysql> SHOW COLUMNS FROM mysql_test.customer LIKE 's%1';
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sex1  | char(1) | YES  | MUL | M       |       |
+-------+---------+------+-----+---------+-------+

mysql> describe mysql_test.customer;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| sex1         | char(1)  | YES  | MUL | M       |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_contact | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

索引定义

1.普通索引(index) 最基本的索引,没有任何限制,通常使用关键字INDEX或KEY

2.唯一索引(unique) 和普通索引基本相同,只是有一点区别,即索引列中的所有值都只能出现一次,必须是唯一的,通常使用关键字UNIQUE

3.主键 PRIMARY KEY 主键是一种唯一索引,不能为控制,每个表只能有一个主键

索引的创建

1.使用CREATE INDEX语句创建

CREATE [UNIQUE] INDEX index_name ON tb1_name(index_col_name,...) 
#col_name用于指定要创建索引的列名,通常可考虑WHERE和JOIN子句里出现的列来作为索引列

其中,index_col_name的格式为

col_name[(length)][ASC|DESC] #asc升序 desc降序 默认asc , length用于指定前length个字符来创建索引

示例1:根据用户地址address的前3个字符建立索引

mysql> CREATE INDEX index_customers ON mysql_test.cus1(cust_address(3) DESC);
Query OK, 0 rows affected (0.48 sec)

示例2: 根据用户地址和ID创建一个组合索引

mysql> CREATE INDEX index_cust ON mysql_test.cus1(cust_address(3) DESC,cust_id);
Query OK, 0 rows affected (0.56 sec)

2. 使用CREATE TABLE语句创建索引

i) 语法项[CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…..),用于表示在创建新表的同时创建该表的索引

ii) 语法项{INDEX|KE} [index_name] (index_col_name,…), 用于表示创建新表的同时创建该表的索引

iii) 语法项[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,…),用于表示在创建新表的同时创建该表的唯一性索引

iv) 语法项[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……), 用于表示在创建新表的同时创建该表的外键

其中关键字KEY 时INDEX的同义字, CONSTRAINT 用于为主键,外键,UNIQUE键定义一个名字.

示例:

CREATE TABLE seller
(
seller_id int NOT NULL AUTO_INCREMENT,
seller_name char(50) NOT NULL,
seller_address char(50) NULL,
seller_contact char(50) NULL,
product_type int(5) not NULL,
sales int NULL,
CONSTRAINT a_key PRIMARY KEY(seller_id,product_type),
INDEX index_seller(sales)
);

3.)使用ALTER TABLE语句创建索引

i) 语法项 ADD {INDEX|KE} [index_name] (index_col_name,…), 用于表示修改表的同时为该表的添加索引

ii) 语法项[CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…..),用于表示修改表的同时为该表的添加索引

iii) 语法项[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,…),用于表示修改表的同时为该表的添加唯一性索引

iv) 语法项[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……), 用于表示在修改表的同时为该表的添加外键

示例:在表seller的姓名列上添加一个非唯一索引

mysql> ALTER TABLE mysql_test.seller ADD INDEX index_seller_name(seller_name);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql_test.seller1 ADD CONSTRAINT f_key FOREIGN KEY index_customers(seller_name) REFERENCES mysql_test.seller(seller_name);
Query OK, 0 rows affected (1.32 sec) #注意设置外键时另一个表必须有一个相同的属性,并且设置了索引
Records: 0  Duplicates: 0  Warnings: 0

索引的查看

SHOW {INDEX][INDEXES|KEYSK}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name}
    WHERE expr;

mysql> SHOW INDEXES FROM seller FROM mysql_test;
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name          | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| seller |          0 | PRIMARY           |            1 | seller_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| seller |          0 | PRIMARY           |            2 | product_type | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| seller |          1 | index_seller      |            1 | sales        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| seller |          1 | index_seller_name |            1 | seller_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.18 sec)

更犀利的产看方法

mysql>show create table 表名 //此方法可以列除所有约束

索引的删除

1.使用DROP INDEX语句删除索引

DROP INDEX index_name ON tb1_name

mysql> DROP INDEX index_seller ON mysql_test.seller;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.使用ALTER TABLE 语句删除索引

i) 选用DROP PRIMARY KEY 子句用于删除表中的主键,由于一个表中只有一个主键,其也是一个索引

ii) 选用DROP INDEX子句用于删除各种类型的索引

iii) 选用DROP FOREIGN KEY 子句用于删除外键

mysql> alter table seller1 drop INDEX index_seller_name;
Query OK, 0 rows affected (0.35 sec)

数据更新

插入数据

1.使用insert .. values 语句插入单行或多行元组

INSERT[INTO] tbl_name [(col_name,...)] 
    {VALUES | VALUE } (expr | DEFAULT},...),(...),...

i) 对应IDENTITY属性的列,系统会自动生成序号值来唯一表示该列

ii)AUTO_INCREMENT属性列的值,在其它类被复制后自动生成

iii)对于TIMESTAMP的列系统会自动赋值

mysql> INSERT INTO mysql_test.customer(cust_id,sex1,cust_address,cust_contact) VALUES(0,'李','武汉市',NULL);
Query OK, 1 row affected (0.09 sec)

2.使用INSERT…SET语句插入部分列数据

INSERT [INTO] tb1_name
    SET col_name={expr | DEFAULT},...

mysql> INSERT INTO mysql_test.customer SET cust_contact=DEFAULT,cust_address='武汉市',sex1=DEFAULT;
Query OK, 1 row affected (0.09 sec)

3.使用INSERT…SELECT 语句插入子查询数据

INSERT [INTO] tbl_name [(col_name,..)]
SELECT ...;

mysql> INSERT INTO mysql_test.customer(cust_address) SELECT cust_contact FROM mysql_test.customer WHERE cust_contact IS NOT NULL;
Query OK, 99 rows affected (0.14 sec)
Records: 99  Duplicates: 0  Warnings: 0

删除数据

在mysql中可以用DELETE语句删除一行或多行数据

DELETE FROM tbl_name
    [WHERE where_condition]
    [ORDER BY...]  #表示更好将按照ORDER BY子句指定的顺序删除
    [LIMIT row_count] #用于告知服务器在控制命令返回到客户端前被删除的行最大值

mysql> DELETE FROM mysql_test.customer WHERE cust_id < 20 ORDER BY cust_id LIMIT 20;
Query OK, 0 rows affected (0.00 sec)

修改数据

在mysql中可以用UPDATE语句修改更新一个表中的数据,实现对表中行的列数据进行修改,其语法格式是

UPDATE tbl_name  #tb1_name 表名
    SET col_name1={expr|DEFAULT} [,col_name2={expr2|DEAULT}] ... #SET子句用于知道要修改的列名集其列值
    [WHERE where_condition]
    [ORDER BY ..]
    [LIMIT row_count]

mysql> UPDATE mysql_test.customer SET cust_contact = '武汉' WHERE cust_contact IS NULL ORDER BY cust_id DESC LIMIT 20;
Query OK, 20 rows affected (0.11 sec) #对表customer 的列cust_contact赋值,从cust_id的反序倒数的20个
Rows matched: 20  Changed: 20  Warnings: 0

数据查询

一.SELECT 语句常用语法格式

SELECT  
    [ALL | DISTINCT | DISTINCTROW] # 此三个参数用于消除结果集中的重复行, 当指定了后,对所有查询结果生效
    select_expr [,select_expr ...]
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} # GROUP BY对检索到的结果分组
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]　　　　# HAVING 用于指定组的过滤条件
    [ORDER BY {col_name | expr | position}　　# ORDER BY 对查询结果排序
        [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}] #LIMIT 要检索的函数

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算集合时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

示例:

mysql> SELECT DISTINCT  cust_contact FROM customer WHERE cust_contact IS NOT NULL GROUP BY cust_id HAVING cust_contact = 'china' ORDER BY cust_contact DESC LIMIT 10;

二.列的选择与指定

1. 选择指定的列

mysql> SELECT sex1, cust_address FROM customer WHERE sex1 > 'a' and sex1 < 'c';
+------+------------------+
| sex1 | cust_address     |
+------+------------------+
| b    | www.feilong1.com |
| b    | www.feilong1.com |
| b    | www.feilong1.com |
| b    | www.feilong1.com |
+------+------------------+
4 rows in set (0.00 sec)

2. 定义并使用列的别名

格式

column_name [AS] column_alias

mysql> SELECT sex1 AS A, cust_address AS B FROM customer WHERE sex1 > 'a' and sex1 < 'c';
+------+------------------+
| A    | B                |
+------+------------------+
| b    | www.feilong1.com |
| b    | www.feilong1.com |
| b    | www.feilong1.com |
| b    | www.feilong1.com |
+------+------------------+
4 rows in set (0.00 sec)

3. 替换查询结果集中的数据

格式:

CASE
WHEN 条件 1 THEN 表达式 1
    WHEN 条件2 THEN 表达式 2
    ...
ELSE 表达式
END[AS] column_alias

示例:

mysql> SELECT cust_address, CASE WHEN (sex1 > 'a' AND sex1 < 'z') THEN 'male' ELSE 'woman' END AS 性别 FROM customer;#注意要加逗号
+------------------+--------+
| cust_address     | 性别   |
+------------------+--------+
| www.feilong1.com | male   |
| www.feilong1.com | male   |

4.计算列值

　　使用SELECT 语句对列进行查询时,在结果集中可以输出对列值计算后的值

mysql> SELECT cust_address, sex1,cust_id+100 FROM mysql_test.customer WHERE sex1 < 'b';
+------------------+------+-------------+
| cust_address     | sex1 | cust_id+100 |
+------------------+------+-------------+
| www.feilong1.com | a    |         125 |
| www.feilong1.com | a    |         151 |
| www.feilong1.com | a    |         177 |
| www.feilong1.com | a    |         199 |
+------------------+------+-------------+
4 rows in set (0.00 sec)

5. 集合函数

SELECT 语句的语法项 “select_expr” 也可以指定为集合函数, 集合函数通常是数据库系统中一类系统内置函数,常用于对一组值进行计算,然后返回单个值,它通常与GROUP BY 一起使用,如果SELECT 语句中有一个GROUP BY 子句,则这个聚合函数对所欲列起作用, 否则,SELECT 语句只产生一行作为结果,另外除了COUNT函数,集合函数都会忽略空值

MySQL中常用的集合函数
函数名	说明
COUNT	求组中项数, 返回INT类型整数
MAX	求最大值
MIN	求最小值
SUM	求表达式中所有值的和
AVG	求组中值的平均值
STD或STDDEV	返回给定表达式中所有值的标准差
VARIANCE	返回给定表达式中所有值的方差
GROUP_CONCAT	返回由属于一组的列值连接组合而成的结果
BIT_AND	逻辑或
BIR_OR	逻辑与
BIT_XOR	逻辑异或

mysql> SELECT COUNT(*) FROM customer WHERE sex1 = 'a' ;#count(*)：返回表中满足where条件的行的数量
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(sex1) FROM customer;#count(列)：返回列值非空的行的数量
+-------------+
| COUNT(sex1) |
+-------------+
|         101 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(cust_id > 50 OR NULL) FROM customer;#④count(expr)：根据表达式统计数据  OR NULL必加
+-----------------------------+
| COUNT(cust_id > 50 OR NULL) |
+-----------------------------+
|                          51 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(cust_id) FROM customer;# 求最大值
+--------------+
| MAX(cust_id) |
+--------------+
|          233 |
+--------------+
1 row in set (0.00 sec)

三 FROM子句与多表连接查询

1.交叉连接(笛卡尔积)

语法

SLECT * FROM tbl1 CROSS JOIN tbl2 CROSS JOIN tbl3 ......;#CROSS JOIN 可以省略,当存在大量数据时慎用

示例

9999 rows in set (0.05 sec)

mysql> SELECT * FROM customer CROSS JOIN customer1;

2. 内连接

内链接是一种最常用的连接类型,内连接是利用条件判断表达式中的比较来组合两张表的记录,其目的式为了消除交叉连接中的某些数据行

SELECT some_column #some_column用于指定需要检索的列的名称或列别名
FROM table1
INNER JOIN 　　　　　# INNER 可以省略
    table2
ON some_conditions;

some_conditions的一般语法格式

[<table1>.]<列名或列别名><比较运算符>[<table2>.]<列名或列别名>

示例

99 rows in set (0.00 sec)

mysql> SELECT * FROM customer A INNER JOIN customer1 B ON A.cust_id = B.cust_id;

(1) 等值连接即ON的运算符为”=”号

99 rows in set (0.00 sec)

mysql> SELECT * FROM customer A INNER JOIN customer1 B ON A.cust_id = B.cust_id;

(2) 非等值连接即ON的运算符为”=”号以外的符号

289 rows in set (0.01 sec)

mysql> SELECT * FROM customer A INNER JOIN customer1 B ON A.cust_id > B.cust_id + 80;

3. 外连接

外连接是首先将连接的两张表分为基表和参考表,然后再以基表为依据返回满足和不满足条件的记录

(1) 左外连接 (LEFT OUTER JOIN 或 LEFT JOIN)

444 rows in set (0.01 sec)

mysql> SELECT A.sex1,B.cust_address FROM customer A LEFT OUTER JOIN customer1 B ON A.sex1 = B.sex1;

(2)右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN)

431 rows in set (0.01 sec)

mysql> SELECT A.sex1,B.cust_address FROM customer A RIGHT OUTER JOIN customer1 B ON A.sex1 = B.sex1;

四 WHERE子句和条件查询

1.比较运算

　　当两个表达式的值均不为NULL时, 除了”<=>”运算符,其它比较运算符都返回TRUE或FALSE,而当两个表达式其中有一个为空或都为空时,则会返回UNKNOWN

109 rows in set (0.00 sec)

mysql> SELECT A.cust_contact,B.cust_contact FROM mysql_test.customer A,mysql_test.customer1 B WHERE A.cust_id = B.cust_id;

2.判断范围

(1)BETWEEN … ADN

expression1 [NOT] BETWEEN expression AND expression2 #表达式expression1的值不能大于表达式expression2的值

24 rows in set (0.00 sec)

mysql> SELECT A.cust_contact,B.cust_contact FROM mysql_test.customer A,mysql_test.customer1 B WHERE (A.cust_id  BETWEEN 10 AND 15) AND (B.cust_id BETWEEN 20 AND 23);

(2)IN

使用关键字”IN”可以指定一个枚举表,该表中列出所有可能的值,其语法格式为:

expression IN (expression[,...n])

mysql> SELECT A.cust_contact FROM mysql_test.customer A WHERE A.cust_id IN(SELECT cust_id FROM customer1 WHERE cust_id > 107);
+--------------+
| cust_contact |
+--------------+
| q            |
| p            |
+--------------+
2 rows in set (0.00 sec)

3.判断空值

expression IS [NOT] NULL

mysql> SELECT A.cust_contact FROM mysql_test.customer A WHERE A.cust_contact IS NULL;
+--------------+
| cust_contact |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

4. 子查询

通常,可以使用SELECT 语句创建子查询,即可以嵌套再其他 SELECT 查询中的SELECT 查询

i) 表子查询

ii) 行子查询

iii) 列子查询

iv) 标量子查询 , 即子查询的结果集仅仅是一个值

(1) 结合关键字”IN”使用的子查询

expression [NOT] IN (subquery)

mysql> SELECT A.cust_contact FROM mysql_test.customer A WHERE A.cust_id IN(SELECT cust_id FROM customer1 WHERE cust_id > 107);
+--------------+
| cust_contact |
+--------------+
| q            |
| p            |
+--------------+
2 rows in set (0.00 sec)

(2) 结合比较运算符使用的子查询

expression { = | < | <= | > | >= | <=> | <> | !=} {ALL | SOME| ANY}(subquery) 
 # ALL表示当表达式与子查询结果的每个值都满足比较关系时,会返回 TRUE 否则返回 FALSE, "SOME"和"ANY"表示当表达式与子查询结果的任何一个值满足时返回TRUE,否则返回NULL

109 rows in set (0.00 sec)

mysql> SELECT A.cust_contact FROM mysql_test.customer A WHERE A.cust_contact = SOME(SELECT cust_contact FROM customer) ;

(3) 结合关键字”EXIST”使用的子查询

mysql> SELECT A.cust_contact FROM mysql_test.customer A WHERE NOT EXISTS 
(SELECT B.cust_contact FROM mysql_test.customer1 B WHERE A.cust_contact = B.cust_contact);

五. GROUP BY 子句与分组数据

再SELECT 语句中,允许使用GROUP BY 子句,将结果集中的数据根据选择列的值进行逻辑分组,以便能汇总表内容的子集,即实现每个组的聚集计算.

GROUP BY 语法格式

GROUP BY {col_name | expr | position}[ASC | DESC], ... [WITH ROLLUP]
#col_name 指定分组的选择列,可以指定多个列,用逗号隔开
#expr 指定用于分组的表达式, 该表达式通常与集合函数一块使用,例如 COUNT(*) AS '人数'
#position 用于指定分组的选择列再SELECT语句结果集中的位置,通常时一个正整数,例如 GROUP BY 3
#WITH ROLLUP 用于指定再结果集中不仅包含GROUP BY 子句分组的数据,还包括各分组的汇总行,以及所有分组的整体汇总行,
#因此使用该关键字可以得到每个分组以及每个分组汇总级别的值

| www.feilong1.com | NULL |    109 |
| 武汉市           | 李   |      1 |
| 武汉市           | NULL |      1 |
| NULL             | NULL |    110 |
+------------------+------+--------+
31 rows in set (0.00 sec)

mysql> SELECT cust_address, sex1, COUNT(*) AS '人数' FROM mysql_test.customer 
GROUP BY cust_address,sex1 WITH ROLLUP;

六 HAVING子句

HAVING子句可以过滤分组,其语法格式为:

HAVING where_condition #where_codition为过滤条件 WHERE 过滤掉的行不包含再分组中

mysql> SELECT cust_address, sex1, COUNT(*) AS '人数' FROM mysql_test.customer GROUP BY cust_address,sex1 WITH ROLLUP HAVING COUNT(*)<= 3;
+------------------+------+--------+
| cust_address     | sex1 | 人数   |
+------------------+------+--------+
| www.feilong1.com | w    |      3 |
| www.feilong1.com | x    |      3 |
| www.feilong1.com | y    |      3 |
| www.feilong1.com | z    |      3 |
| 武汉市           | 李   |      1 |
| 武汉市           | NULL |      1 |
+------------------+------+--------+
6 rows in set (0.00 sec)

七. ORDER BY 子句

ORDER BY 子句可以将结果集中的数据按一定的顺序排序

ORDER BY {col_name | expr | position} [ASC | DESC], ......

i) DRDER BY 子句可以包含子查询

ii) 当对空值进行查询时,ORDER BY 子句会将空值当最小值对待

iii) 在ORDER BY 子句中指定多个列进行排序,则再MySQL中会按照这些列从左至右所罗列的次序进行排序

iv) 在使用GROUP BY子句时通常也会使用ORDER BY 子句

110 rows in set (0.00 sec)

mysql> SELECT sex1, cust_contact FROM mysql_test.customer ORDER BY  sex1 DESC,cust_contact ASC;

ORDER BY子句与GROUP BY 子句的差别汇总
ORDER BY 子句	GROUP BY 子句
排序产生输出	分组行,但输出可能不是分组的排序
任意列都可以使用	只能使用选择列或表达式
不一定需要	若与集合函数一起使用列或表达式,则必须使用

八. LIMIT 子句

LIMIT 可以限制SELECT 返回的行数

LIMIT {[offset,] rwo_count | row_count OFFSET offset}

i) offset 为可选项,默认值为0,用于指定返回数据的第一行在SELECT语句结果集中的偏移量,其必须是非负整数常量,注意,SELECT 语句结果集中第一行的偏移量为0而不是1.

ii) row_count: 用于指定返回数据的行数,其也必须是非负整数常量,若这个指定行数大于实际能返回的行数时,将返回它能返回的数据行

iii) row_count OFFSET offset: 从第offset + 1行开始, 取row_count行

mysql> SELECT sex1 FROM customer WHERE cust_id BETWEEN 10 AND 20 LIMIT 2,5;
+------+
| sex1 |
+------+
| n    |
| o    |
| p    |
| q    |
| r    |
+------+
5 rows in set (0.00 sec)

mysql> SELECT sex1 FROM customer WHERE cust_id BETWEEN 10 AND 20 LIMIT 2 OFFSET 2;
+------+
| sex1 |
+------+
| n    |
| o    |
+------+
2 rows in set (0.00 sec)

视图

一.在MySQL中,可以使用CREATEVIEW 语句来创建视图,其常用的语法格式是

CREATEVIEW view_name [colmun_list]
    AS select_statement
    [WITH [ CASCADED | LOCAL | CHECK OPTION]

i) view _name 用于指定视图的名称,且该名称在数据库中必须是唯一的,不能与其他表或视图同名

ii) column_list 是可选项, 用于为视图中的每个列指定明确的名称,且列名的数目必须等于SELECT 语句检索出的结果数据集中的列数,同时每个列名之间用逗号分隔, 可以省略column_list

iii) select_statement 用于指定创建视图的SELECT语句,这个SELECT 语句给出了视图的定义,它可以查询多个基本表或源视图

iv) WITH CHECK OPTION 是可选项,用于指定在可更新视图上所进行的修改都需要符合select_statment中所指定的限制条件,这样可以确保数据修改后,仍然可以通过视图看到修改后的数据. 当视图是根据另外一个视图定义时,关键字 WITH CHECK OPTION 给出两个参数, 即 CASCADED 和local,它们决定检查测试的范围,其中, 关键字” CASCADED “为默认值,他会对所有视图进行检查,而关键字”LOCAL” 则使CHECK OPTION 只对定义的视图进行检查.

例:创建视图customer_view ,要求该视图包含所有男客户的信息,并且保证以后对该视图的修改都必须符合客户为男性这个条件

mysql> CREATE OR REPLACE VIEW customer_view AS SELECT * FROM mysql_test.customer WHERE sex1 = '男' WITH CHECK OPTION;
Query OK, 0 rows affected (0.14 sec)

二. 删除视图

DROP VIEW [IF EXISTS] view_name[,view_name] ... [RESTRICT | CASCADE]

mysql> DROP VIEW IF EXISTS customer_view CASCADE;
Query OK, 0 rows affected (0.17 sec)

三. 修改视图定义

ALTER VIEW  view_name [(column_list)]
    AS select_statement
    [WITH | CASCADED | LOCAL] CHECK OPTION]

mysql> ALTER VIEW customer_view AS SELECT * FROM customer1 WHERE sex1 = '男' WITH CHECK OPTION;
Query OK, 0 rows affected (0.14 sec)

四. 查看视图定义

SHOW CREATE VIEW view_name

mysql> SHOW CREATE VIEW customer_view;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View          | Create View                                                                                                                                                                                                                                                                                                                                | character_set_client | collation_connection |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| customer_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `customer_view` AS select `customer1`.`cust_id` AS `cust_id`,`customer1`.`sex1` AS `sex1`,`customer1`.`cust_address` AS `cust_address`,`customer1`.`cust_contact` AS `cust_contact` from `customer1` where (`customer1`.`sex1` = '男') WITH CASCADED CHECK OPTION  | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

五. 更新视图数据

由于视图是一个虚拟表所以,通过插入,修改,删除等操作方式来更新视图的数据,实质上是在修改视图所引用的基本表中的数据

1. 使用INSERT 语句通过视图向基本表插入数据

mysql> INSERT INTO mysql_test.customer_view  VALUES(110,'男','www.zm.com','武汉');
Query OK, 1 row affected (0.11 sec)

2. 使用UPDATE 语句通过视图修改基本表的数据

mysql> UPDATE mysql_test.customer_view SET cust_address = '上海';
Query OK, 99 rows affected (0.14 sec)
Rows matched: 99  Changed: 99  Warnings: 0

六. 查询视图数据

可以如同查询数据库中的真实表一样对视图进行查询

mysql> SELECT cust_id, cust_contact FROM customer_view WHERE cust_id = 110 AND cust_contact = '武汉';
+---------+--------------+
| cust_id | cust_contact |
+---------+--------------+
|     110 | 武汉         |
+---------+--------------+
1 row in set (0.00 sec)

存储过程

一. 存储过程的基本概念

　　存储过程是一组为了完成某项特定功能的SQL语句集,其实质上就是一段存储在数据库中的代码,它可以由过程式的SQL语句(如CREATE , UPDATE 和SELECT等语句)和过程式SQL语句(如IF…THEN…ELSE控制结构语句)组成.

二. 创建存储过程

　MySQL数据库SQL语句默认是以分号作为语句结束标志,可以通过DELIMITER(分隔符) 将MySQL语句的结束标志改为替他符号,

DELIMITER 语法格式:

DELIMITER $$  # $$ 为用户定义的结束符

mysql> DELIMITER $$  # $$ 为用户定义的结束符
mysql> SELECT cust_id, cust_contact FROM customer_view WHERE cust_id = 110 AND cust_contact = '武汉'$$
+---------+--------------+
| cust_id | cust_contact |
+---------+--------------+
|     110 | 武汉         |
+---------+--------------+
1 row in set (0.00 sec)

在MySQL 中使用CREATE PROCEDURE来创建存储过程,其语法格式是:

CREATE PROCEDURE sp_name ([proc_parameter[,...]]}
        routine_body

其中,语法项proc_parameter语法格式是:

[IN | OUT | INOUT ] param_name type

i) sp_name 用于指定存储过程的名称,且默认在当前数据库创建

ii) proc_parameter 用于指定存储过程的参数列表,其中的语法项param_name 为参数名, 语法项type 为数据类型, 参数列表用逗号隔开,也可以没有参数. MySQL 存储过程支持三种类型的参数,即输入参数. 输出参数和输入输出参数, 分别用 “IN” “OUT” “INOUT”三个关键字标识, 需要注意参数的取名不要与数据表的列名相同.

iii) routine_body 表示存储过程的主体部分,也称为存储过程体,其包含了在过程调用的时候必须执行的SQL语句,这个部分是以关键字”BEGIN” 开始, 以关键字 “END” 结束. 如若存储过程体中只有一条SQL语句时,可以省略 BEGIN … END 标. 另外,在存储过程体中, BEGIN…END 符号语句还可以嵌套使用

例: 在数据库 mysql_test 中创建一个存储过程, 用于实现给定表 customer 中一个客户id 号即可修改customer中该客户的性别为指定的性别.

DROP PROCEDURE IF EXISTS sp_update_sex;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> use mysql_test
Database changed

DELIMITER $$
CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
BEGIN
UPDATE customer SET sex1 = csex WHERE cust_id = cid; #注意博客园的代码块的编码方式可能和mysql不一样, 如果报错,可以不要复制用手输
END $$

mysql> DELIMITER ; $$

三. 存储过程体

1.局部变量

　　在存储过程体中可以声明局部变量, 用来存储存储过程体中的临时结果,在MySQL中,可以使用DECLARE 语句来声明局部变量, 并同时还可以对该局部变量赋予一个初始值,其语法格式为:

DECLARE var_name[,...] type [DEFAULT value] #var_name 变量名,  type 数据类型, DEFALUT 为局部变量指定默认值

例声明一个局部变量cid

DECLARE cid INT(10);

i) 局部变量只能在存储过程体的BEGIN…END语句块中声明

ii) 局部变量必须在存储过程体的开头出声明

iii) 局部变量的作用范围仅限于声明它的 BEGIN…END 语句块,

iv) 局部变量不同于用户变量,两者的区别是: 局部变量声明时, 在其前面没有使用@符号,并且它只能被声明它的BEGIN…END块使用; 而用户变量在声明时, 会在其名称前面使用@符号,同时已声明的用户变量存在于整个会话之中.

mysql> DELIMITER $$
mysql> CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
    -> BEGIN
    -> DECLARE cid INT(10);
    -> UPDATE customer SET sex1 = csex WHERE cust_id = cid;
    -> END $$
Query OK, 0 rows affected (0.15 sec)

2. SET 语句

SET 语句为局部变量赋值,其语法格式是:

SET var_name = expr[, var_name = expr ] ...

例为cid1局部变量赋予一个整数值100

mysql> DELIMITER $$
mysql> CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
    -> BEGIN
    -> DECLARE cid1 INT(10);
    -> SET cid1 = 100;
    -> UPDATE customer SET sex1 = csex WHERE cust_id = cid1;
    -> END $$
Query OK, 0 rows affected (0.17 sec)

3. SELECT .. INTO 语句

可以直接用SELECT … INTO 语句把选定的值直接存储到局部变量中,其语法格式是

SELECT col_name INOT var_name[,...] table_expr

mysql> CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
    -> BEGIN
    -> DECLARE cid1 INT(10);
    -> SELECT cust_id INTO cid1 WHERE cust_id = 100;
    -> UPDATE customer SET sex1 = csex WHERE cust_id = cid1;
    -> END $$
Query OK, 0 rows affected (0.15 sec)

4.流程控制语句

(1) 条件判断语句

　　常用的有 IF…THEN…ELSE 语句和 CASE 语句它们的用法类似于高级语言

DELIMITER $$
    CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
    BEGIN
    DECLARE cid1 INT(10);
    SELECT cust_id INTO cid1 WHERE cust_id = 100;
    IF cid1 > 50 
    THEN SET cid1 = 50;
    ELSE SET cid1 = 101;
    END IF;
    UPDATE customer SET sex1 = csex WHERE cust_id = cid1;
        END $$

(2) 循环语句

　　常用的有 WHILE 语句, REPEAT 语句和 LOOP语句,它们的语法类似于高级语言; 可以使用ITERATE语句退出当前循环,开始下一个循环

DELIMITER $$
    CREATE PROCEDURE sp_update_sex( IN cid INT, IN csex CHAR(1))
    BEGIN
    DECLARE cid1 INT(10);
    SELECT cust_id INTO cid1 WHERE cust_id = 100;
    WHILE(cid1 > 70) DO
    SET cid1=cid1-1;
    END WHILE;
    IF cid1 > 50 
    THEN SET cid1 = 50;
    ELSE SET cid1 = 101;
    END IF;
    UPDATE customer SET sex1 = csex WHERE cust_id = cid1;
        END $$

5. 游标

　　在MySQL中,一条SELECT … INTO 语句执行成功后会返回带有中的一行数据,这行数据可以被直接读取; 然而在使用SELECT 语句进行数据检索时,若该语句成功执行,则会返回一组称为结果集的数据行,该结果集中可能拥有多行数据,这些数据无法被一行一行的读取,这时就要使用游标.

　　游标时一个被SELECT语句检索出来的结果集.

(1) 声明游标

DECLARE cursor_name CURSOR FOR select_statement #cursor_name 游标名, select_staement 用于指定一个SELECT 语句,

(2)打开游标

OPEN cursor_name #在定义游标后必须打开游标才能使用

(3) 读取数据

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

#cursor_name 用于指定已打开的游标
#FETCH...INTO 语句与 SELECT...INTO 语句具有相同的意义,FETCH 语句时将游标指向的一行数据赋给一些变量,
这些变量的数目必须等于声明游标时SELECT 子句中选择列的数目,游标相当于一个指针,它指向当前的一行数据

(4) 关闭游标

CLOSE cursor_name  # 每个游标在不需要使用时都应被关闭

DELIMITER $$  #将命令结束符改为$$
    CREATE PROCEDURE sp_sumofrow (OUT ROWS1 INT) #注意变量不要用Mysql的保留字 
    BEGIN
    DECLARE cid INT; #定义一个int变量用来接受FETCH 提取的值
    DECLARE FOUND BOOLEAN DEFAULT TRUE;  #定义FOUND 的值默认为true
    DECLARE cur_cid CURSOR FOR    #定义一个cursor(游标) 必须在所有DECLARE声明变量的语句之后
        SELECT cust_id FROM customer;  
        DECLARE CONTINUE HANDLER FOR NOT  FOUND  #定义CONTINUE HANDLER 句柄,必须在游标之后
        SET FOUND = FALSE;   
        SET ROWS1 =0;
        OPEN cur_cid;#打开游标
        FETCH cur_cid INTO cid; #提取数据
        WHILE FOUND DO
        SET ROWS1 = ROWS1+1;
        FETCH cur_cid INTO cid;
        END WHILE;
        CLOSE cur_cid; #关闭游标
        END $$

调用存储过程

mysql> call sp_sumofrow(@rows);
Query OK, 0 rows affected (0.00 sec)

查看调用存储过程后的结果

mysql> SELECT @rows;
+-------+
| @rows |
+-------+
|   111 |
+-------+
1 row in set (0.00 sec)

四. 调用存储过程

　　创建好的存储过程,可以在程序或其他存储过程用CALL调用

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

mysql> CALL sp_sumofrow(@rows1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @rows1;
+--------+
| @rows1 |
+--------+
|    111 |
+--------+
1 row in set (0.00 sec)

五. 删除存储过程

mysql> DROP PROCEDURE IF EXISTS sp_update_sex; #注意在删除之前必须确认该存储过程没有依赖关系
Query OK, 0 rows affected (0.14 sec)

六.查看存储过程

1.查看存储过程的状态

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

mysql> SHOW PROCEDURE STATUS LIKE 'sp%' \G; #查询所有sp开头的存储过程
*************************** 1. row ***************************
                  Db: mysql_test
                Name: sp_sumofrow
                Type: PROCEDURE
             Definer: root@%
            Modified: 2019-03-29 02:12:08
             Created: 2019-03-29 02:12:08
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: gb2312_chinese_ci
1 row in set (0.00 sec)

2. 查看存储过程的完整程序

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

mysql> SHOW CREATE PROCEDURE sp_sumofrow;
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                | character_set_client | collation_connection | Database Collation |
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| sp_sumofrow | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `sp_sumofrow`(OUT ROWS1 INT)
BEGIN
DECLARE cid INT;
DECLARE FOUND BOOLEAN DEFAULT TRUE;
DECLARE cur_cid CURSOR FOR
SELECT cust_id FROM customer;
DECLARE CONTINUE HANDLER FOR NOT  FOUND
SET FOUND = FALSE;
SET ROWS1 =0;
OPEN cur_cid;
FETCH cur_cid INTO cid;
WHILE FOUND DO
SET ROWS1 = ROWS1+1;
FETCH cur_cid INTO cid;
END WHILE;
CLOSE cur_cid;
END | utf8mb4              | utf8mb4_0900_ai_ci   | gb2312_chinese_ci  |
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

存储函数

存储函数和存储过程的区别

i) 存储函数不能有输出函数,因为存储函数本身就是输出函数

ii) 可以直接对存储函数进行调用,且不需要CALL 语句,而调用存储函数要 CALL 语句

iii) 存储函数中必须包含一条RETURN 语句,而这条特殊的SQL 语句不允许包含于存储过程中

一. 创建存储函数

CREATE FUNCTION sp_name{[ func_parameter[,...]}
RETURNS TYPE
routine_body

i)func_parameter用于指定参数,这里的参数只有名称,没有关键字IN, OUT, INOUT

ii) sp_name 用于指定存储函数名,不能和存储过程重名

iii) RETURNS子句用于声明存储函数返回值的数据类型, type 用于指定返回值的数据类型

iv) routine_body 函数体, 函数体中必须包含一个RETURN value语句,其中value用于指定函数的返回值

mysql> DELIMITER $$
mysql> CREATE FUNCTION fn_search(cid INT)
    -> RETURNS CHAR(10)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE SEX CHAR(10);
    -> SELECT
    ->     sex1
    -> INTO SEX FROM
    ->     customer
    -> WHERE
    ->     cust_id = cid;
    -> IF SEX IS NULL THEN
    -> RETURN(SELECT 'NO such customer');
    -> ELSE IF SEX = 'F' THEN
    -> RETURN(SELECT 'woman');
    -> ELSE RETURN ( SELECT 'man');
    -> END IF;
    -> END IF;
    -> END $$
Query OK, 0 rows affected (0.14 sec)

二调用存储函数

mysql> SELECT fn_search(99);
+---------------+
| fn_search(99) |
+---------------+
| woman         |
+---------------+
1 row in set (0.00 sec)

三删除存储函数

DROP FUNCTION IF EXISTS sp_name

mysql> DROP FUNCTION IF EXISTS customer.sp_search;
Query OK, 0 rows affected, 1 warning (0.06 sec)

数据库安全与保护

数据完整性

一. 完整性约束条件的作用对象

(1) 列级约束主要是指对列的类型,取值范围,精度等的约束

(2) 元组约束指元组中各个字段之间的相互约束,例如某个活动的开始日期小于结束日期

(3) 表级约束指若干元组之间,关系之间的联系的约束, 例如, 在学生成绩表 tb_score 表中学号 stdentNo 字段的取值受学生信息表 tb_student表中学号 studentNo 字段取值的约束

二. 定义与实现完整性约束

(1) 主键约束

i) 每个表只能有一个主键

ii) 主键的值不能重复,不能为空

iii) 复合主键不能包含不必要的多余列

iv) 一个列在复合主键的列表中只能出现一次

　　主键约束可以通过在 CREATE TABLE 语句中使用关键字 PARIMARY KEY 来实现

i) 作为列的完整性

mysql> CREATE TABLE Myhome(name VARCHAR(255) PRIMARY KEY,id int(20));
Query OK, 0 rows affected (0.55 sec)

ii)作为表的完整性, 需要在表中所有列的属性定义后添加PRIMARY KEY(index_col_name,…)格式的子句

(2) 候选键约束

候选键的值必须是唯一的,且不能为NULL,可以在CREATE TABLE语句中使用关键字” UNIQUE” 来实现

i) 一个表中只能有一个主键,但可以定义若干候选键

ii) 定义主键约束,系统会自动产生PRIMARY KEY 索引,而定义候选键约束 ,系统会自动产生UNIQUE索引

mysql> ALTER TABLE Myhome ADD UNIQUE KEY 身份证号(id);
Query OK, 0 rows affected (0.39 sec)

2.参照完整性

在MySQL中,参照完整性是通过在 CREATE TABLE 或更新表 ALTER TABLE 的同时定义一个外键声明来实现的,有两种方法

i) 在表中某个列的属性定义后直接加上 “reference_definition” 语法项

ii) 在表中所有列的属性定义后添加 “FOREIGN KEY (index_col_name,..) reference_definition” 子句来实现

REFERENCES tbl_name(index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference的语法格式是

RESTRICT | CASCADE | SET NULL | NO ACTION

i) 关键字 ” ON DELETE” 或 “ON UPDATE” 指定参照动作相关的SQL语句,这里可为每个外键指定的参照动作分别对应为DELETE语句和UPDATE语句

ii) reference_option语法项指定参照完整性约束的实现策略,默认为RESTRICT. 具体策略如下: 关键字 “RESTRICT” 表示限制策略,即当要删除或更新被参照表中被更新列上,并在外键中出现的值时,系统拒绝对被参照表的删除或更新操作; 关键字”CASCADE” 表示级联策略,即从被参照表中删除或更新记录时,自动删除或更新表中匹配的记录行; 关键字”SET NULL” 表示置空策略,即当被参照表中删除或更新记录行时,设置参照表中与之对应的外键的值为NULL,这个策略需要被参照表的外键列没有被声明限定词 NOT NULL; NO ACTION表示不采取策略,与RESTRICT语义相同

例传教订单表order1 要求商品订单order1中所有订购客户信息均已在customer中注册

 CREATE TABLE order1
 (
 order_id INT NOT NULL AUTO_INCREMENT,
 order_product CHAR(50) NOT NULL,
 order_product_type CHAR(50) NOT NULL,
 cust_id INT NOT NULL,
 order_price DOUBLE NOT NULL,
 order_amount INT NOT NULL,
 PRIMARY KEY(order_id),
 FOREIGN KEY(cust_id)
    REFERENCES customer(cust_id)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT
);

当指定一个外键时,需要遵守下列规则:

i)被参照表必须已经用一条CREATE TABLE 语句创建了,或者时当前正在创建的表,若时后一种情形,则被参照表与参照表时同一个表,这样的表叫自照表(self-referencing table),这种结构称为自参照完整性(self_referential integrity)

ii) 必须为被参照表定义主键

iii)必须在被参照表的表名后面知道列名或列组合,这个列或列组合必须时这个被参照表的主键或后候选键

iv)尽管主键不能包含空值,但允许在外键中出现一个空值,这意味着,只要外键的每个非空值出现指定的主键中,这个外键的内容就是正确的

v)文件到列的数目必须和被参照表的主键中的列的数目相同

vi) 外键的列的数据类型必须和被参照表的主键中的对应列的数据类型相同

3. 用户定义的完整性

(1)非空约束通过在CREATE TABLE 或 ALTER TABLE 语句中的某个列定义后面,加上关键字NOT NULL作为限定字来约束.

(2) CHECK约束与非空约束一样,CHECK也是通过在CREATE TABLE 或 ALTER TABLE 语句中的某个列定义后面,根据用户的完整性要求来限定约束.

CHECK(pxpr) #貌似MySQL 的CHECK语句无效

三.命名完整性约束

命名完整性约束的语法格式是:

CONSTRAINT [symbol] #symbol 是指定的约束名  只能给基于表的完整性约束命名,不能给基于列的完整性约束命名

查看约束

SHOW INDEX IN table_name;

四. 更新完整性约束

可以用ALTER TABLE语句来更新与列或表有关的各种越苏

i) 完整性约束不能直接修改,若要修改某个实际上是用ALTER TABLE 语句先删除该约束,然后再增加一个与该约束同名的约束

ii) 使用ALTER TABLE 语句, 可以独立地删除完整性约束,而不会删除表本身. 若使用DROP TABLE 语句删除一个表,则表中所有约束讲删除

mysql> ALTER TABLE order1 ADD CONSTRAINT unique1 UNIQUE(order_price);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

触发器

一. 创建触发器

CREATE TRIGGER trigger_name trigge_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

i) trigger_name 触发器名称

ii) trigger_time 指定触发的时间,有两个选项,即关键字 BEFORE 和关键字 AFTER ,用于表示触发器是在激活它的语句之前或者之后触发.

iii) trigger_event 指定触发事件, 即指定激活触发器的语句的种类,可以是下述值之一: INSERT, UPDATE, DELETE

iv) tb1_name 指定与触发器相关联的表名.

v) 关键字 ” FOR EACH ROW” 指定对于受触发事件影响的每一行都要激活触发器的动作.

vi) rigger_body 指定触发器动作体,如果要执行多个语句,可以使用BEGIN END;

每个表每个事件只允许一个触发器,因此,每个表最多只支持6个触发器; 单一触发器不能与多个事件或多个表关联

例创建一个触发器,当每次向表 customer 插入一行数据时, 讲用户变量设置为”one customer added!”

mysql> CREATE TRIGGER mysql_test.customer_insert_trigger AFTER INSERT ON mysql_test.customer FOR EACH ROW SET @str = 'one customer added!';
Query OK, 0 rows affected (0.15 sec)

mysql> SELECT @str;
+---------------------+
| @str                |
+---------------------+
| one customer added! |
+---------------------+
1 row in set (0.00 sec)

二. 删除触发器

为了修改一个触发器,必须先删除触发器,然后才能修改

DROP RIGGER [IF EXISTS] [schema_name] trigger_name #schema_name 指定数据库名称

例:

mysql> DROP TRIGGER IF EXISTS mysql_test.customer_insert_trigger;
Query OK, 0 rows affected (0.17 sec)

三. 使用触发器

i) 在 INSERT 触发器内,可引用一个名为NEW(不区分大小写) 的虚拟表,来访问被插入的行

ii) 在BEFORE INSERT触发器中, NEW 中的值也可以被更新, 即允许更改被插入的值(只要具有对于的操作权限)

iii) 对于AUTO_INCREMENT 列,NEW 在INSERT 执行之前包含的时0值,在INSERT执行之后将包含新的自动生成值

例创建一个触发器,当每次向表 customer 插入一行数据时, 讲用户变量设置为新插入客户的ID号

mysql> CREATE TRIGGER mysql_test.customer_insert_trigger AFTER INSERT ON mysql_test.customer FOR EACH ROW SET @str = NEW.cust_id;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO customer VALUES(151,'F','武汉','china');
Query OK, 1 row affected (0.23 sec)

mysql> SELECT @str;
+------+
| @str |
+------+
|  151 |
+------+
1 row in set (0.00 sec)

2. DELETE 触发器

i) 在DELETE 触发器代码内, 可以引用一个名为OLD(不区分大小写) 的虚拟表,来访问被删除的行

ii) OLD中的值全部时只读的,不能被更新

mysql> CREATE TRIGGER mysql_test.customer_delete_trigger AFTER DELETE ON mysql_test.customer FOR EACH ROW SET @str = OLD.cust_id;
Query OK, 0 rows affected (0.14 sec)

mysql> DELETE  FROM customer WHERE cust_id = 100;
Query OK, 1 row affected (0.10 sec)

mysql> SELECT @str;
+------+
| @str |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

3. UPDATE 触发器

i)在

UPDATE

i) 在UPDATE触发器代码内, 可以引用一个名为OLD(不区分大小写) 的虚拟表,来访问以前(UPDATE 语句执行前)的值,也可以引用一个名为NEW(不区分大小写)的虚拟表访问新更新的值.

ii) 在BEFORE UPDATE 触发器中, NEW 的值也可能被更新,即允许更改将要用于UPDATE语句中的值

iii) OLD中的字全部是只读的,不能被更新

iv) 当触发器涉及对触发器自身的更新操作时,只能使用BEFORE UPDATE触发器,二AFTER UPDATE触发器将不被允许

mysql> DELIMITER //
mysql> CREATE TRIGGER mysql_test.order1_update_trigger BEFORE UPDATE
    -> ON mysql_test.order1 FOR EACH ROW
    -> BEGIN
    -> IF
    -> NEW.order_id < 30
    -> THEN
    -> SET NEW.order_product = '哈';
    -> END IF;
    -> END;//
Query OK, 0 rows affected (0.15 sec)

mysql> UPDATE order1 SET order_product = 'computer' WHERE order_id = 11;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM order1;
+----------+---------------+--------------------+---------+-------------+--------------+
| order_id | order_product | order_product_type | cust_id | order_price | order_amount |
+----------+---------------+--------------------+---------+-------------+--------------+
|       11 | 哈            | 电器               |      10 |          20 |         3000 |
+----------+---------------+--------------------+---------+-------------+--------------+
1 row in set (0.00 sec)

安全性与访问控制

一. 账户管理

查看mysql数据库的使用者账户

mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

1.创建用户账号

CREATE USER user[IDENTIFIED BY [PASSWORD] 'password']

i) user 用于指定用户,其格式为’user_name’@’host name’, 默认为%表示一组主机

ii) IDENTIFIED BY 用于指定账号对应的口令,若省略则口令为空

iii) PASSWORD 用于指定散列口令使用方法SELETC PASSWORD(‘密码’), 此方法已弃用

mysql> CREATE USER 'jiang'@'192.168.1.104' IDENTIFIED BY '123','zou'@'192.168.1.104' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.12 sec)

2.删除用户

DROP USER user[,user....]

mysql> DROP USER zou@192.168.1.104;
Query OK, 0 rows affected (0.11 sec)

3.修改用户账号

RENAME USER old_user TO new_user [,old_user TO new_user] ...

mysql> RENAME USER jiang@192.168.1.104 TO jiang@localhost;#账户名也可以改
Query OK, 0 rows affected (0.11 sec)

4.修改用户口令(密码)

mysql> ALTER USER jiang@localhost IDENTIFIED BY '1';
Query OK, 0 rows affected (0.10 sec)

mysql> SET PASSWORD FOR jiang@localhost = '2'
    -> ;
Query OK, 0 rows affected (0.11 sec)

二. 账户权限管理

查看权限

mysql> SHOW GRANTS FOR 'jiang'@'localhost';  # "." 表示没有任何权限
+-------------------------------------------+
| s for jiang@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `jiang`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

1.权限授予

GRANT
    priv_type[(column_list)]
     [,priv_type[(column_list)]]...
    ON [object_type] priv_leve]
    TO user_specification[,user_specification] ...
    [WITH GRANT OPTION]

i) priv_type 用于指定权限,例如SELECT ,UPDATE ,INSERT等

ii) column_list 指定权限要授予表中那些具体的列

iii) ON 用于指定权限授予的对象和级别,例如可在关键字ON后面给出要授予权限的数据库名或表名

iv) object_type 用于指定权限授予的对象类型,包括表,函数,存储过程,分别用关键字”TABLE” “FUNCTION” 和”PROCEDURE”标识

v)priv_level 用于指定权限的级别,其可以授予的权限有这几个:列权限, 表权限,数据库权限和用户权限. 相应的,在GRANT 语句中可用于指定权限级别的值有这样几类格式: ” * ” 表示当前数据库的所有表; ” *.* “表示所有数据库的所有表; “db_name.*”表述某个数据库的所有表; “db_name.tbl_name”表示某个数据库的某个表或视图; “db_name.routine_name”表示某个数据库中的某个存储过程或函数

vi)TO 子句用来设定用户的口令,

vii) usr-specification 是 TO子句中的具体描述的一部分,其格式是

user[IDENTIFIED BY [PASSWORD] 'password']

viii) WITH 子句为可选项,用于实现权限的转移或限制 WITH GRANT OPTION 表示TO子句中的所有用户都可以转移自己的权限给其他用户

例1. 授予用户jiang在数据库mysql_test表customer上拥有对列cust_id和sex1 的SELECT权限

mysql> GRANT SELECT(cust_id,sex1) ON mysql_test.customer  TO 'jiang'@'LOCALHOST';
Query OK, 0 rows affected (0.12 sec)

mysql> SHOW GRANTS FOR 'jiang'@'localhost';
+------------------------------------------------------------------------------------+
| Grants for jiang@localhost                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jiang`@`localhost`                                          |
| GRANT SELECT (`cust_id`, `sex1`) ON `mysql_test`.`customer` TO `jiang`@`localhost` |
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

例2. 当前系统不存在liming和huang,要求创建相应的登陆口令,并授予他们在数据表mysql_test上拥有SELECT和UPDATE权限

mysql8.0创建账户和授予权限已分开

例3. 授予系统中已存在的用户jiang可以对数据库mysql_test中执行所有数据库操作的权限

mysql> GRANT ALL ON mysql_test.*    TO 'jiang'@'localhost';
Query OK, 0 rows affected (0.10 sec)

例4 授予用户jiang用于创建用户的权限

mysql> GRANT CREATE USER ON *.* TO 'jiang'@'localhost';
Query OK, 0 rows affected (0.15 sec)

例5 授予用户jiang全部权限

mysql> grant all privileges on *.* to jiang@'%';
Query OK, 0 rows affected (0.03 sec)

mysql刷新权限命令：

FLUSH PRIVILEGES;

2. 权限的转移

WITH GRANT OPTION 可以指定用户拥有权限转移的权力

例: 授予当前系统用户”name” 在数据库表customer上拥有SELECT 和UPDATE权限

mysql> GRANT SELECT, UPDATE ON mysql_test.customer   TO 'name'@'localhost'  WITH GRANT OPTION;
Query OK, 0 rows affected (0.10 sec)

3.权限的撤销

REVOKE 
    priv_type[(column_list)]
      [,priv_type[(column_list)]] ...
    ON[object_type] priv_level
    FROM user[,user] ...

回收全部权限

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user] ...

例回收用户name在数据库mysql_test的表customer上的SELECT权限

mysql> REVOKE SELECT ON mysql_test.customer    FROM 'name'@'localhost';
Query OK, 0 rows affected (0.13 sec)

事务与并发控制

一. 事务的概念

　　所谓事务是用户定义的一个数据操作序列,这些操作可作为一个完整的单元,要么全部执行,要么全部不执行,是一个不可分割的单元.

执行事务的语句有三条:

i) BEGIN TRANSACTION 表名事务的开始

ii) COMMIT　　提交事务

iii)ROLLBACK 回滚事务

二.事务的特征

(1) 原子性(Atomicity)

(2) 一致性(Consisitency)

(3) 隔离性(Isolation)

(4) 持续性(Durability)

例:

/* this is just a model */
BEGIN TRANSACTION
read(A);
A=A-S;
write(A);
if(A<0) ROLLBACK;
else{ read(B);
      B=B+S;
      write(B);
      COMMIT;}

三. 并发操作的问题

当多个事务交错执行时,可能出现不一致问题,典型有以下三种

(1) 丢失更新

(2) 不可复读性

(3) 读” 脏” 数据

解决并发操作带来的数据不一致性问题的方法有***, 时间戳, 乐观控制法和多版本控制等

四. ***

　　***时最常用的并发控制技术,它的基本思想是: 需要时, 事务通过向系统请求对它所希望的数据对象(如数据库中的记录)加锁, 以确保它不被非预期改变

1. 锁

基本的锁类型有两种:

i) 排他锁(Dxclusive lock, X 锁)

ii) 共享锁(Shared Lock, S 锁)

2.用***进行并发控制

1) 若事务T 对数据D 加了 X 锁, 则所有别的事物对数据D 的锁请求都必须等待直到事物T 释放锁

2) 若事务T 对数据D 加了 S 锁, 则别的事物还可以对数据D 请求 S锁, 而对数据D 的X 锁请求必须等待直到事物T 释放锁

3) 事物执行数据库操作时都要先申请相应的锁, 即对读请求 S 锁, 对更新(插入, 删除, 修改) 请求 X 锁,这个过程一般是由DBMS在执行操作时隐含地进行

4) 事物一直占有获得的锁直到结束(COMMIT 或 ROLLBACK) 时释放

3.***的粒度

4.***的级别

(1) 0级***

　　***的事物不重写其他非0级事物的未提交的更新数据. 这种状态实际上作用不大

(2) 1级***

　　被***的事务不允许重写未提交的更新数据, 这防止了丢失更新的可能

(3) 3级***

　　被***的事务不读未提交的更新数据,不写任何(包括读操作的) 未提交数据

5. 死锁和活锁

预防死锁的方法

(1) 一次性请求

　　每一事务在处理时一次提出所有的锁请求

(2) 锁请求排序

(3) 序列化处理

(4) 资源剥夺

(5) 不去防止,随时进行检测,一旦系统发生死锁再处理

6. 可串行性

7.两端***性

备份和恢复

1. 使用SELECT INTO … OUTFILE 语句备份数据

注意在备份之前在使用的表上使用 LOCK TABLES table_name READ 语句做一个读锁定

在恢复数据时使用 LOCK TABLES table_name WRITE 做一个写锁定

备份完毕后使用 UNLOCK TABLES 对该表进行解锁.

SELECT * INTO OUTFILE 'file_name' export_options
    | INTO DUMPEFILE 'file_name'

其中语法项”export_options”的格式是:

[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY]ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES TERMINATED BY 'string']

i) file_name 指定备份文件的名称, 在文件中导出的数据行会以一定的形式存储,其中空值是用”\N” 表示

ii) OUTFILE 表示导出,可以在语法项wxport_options加入以下两个自选的子句, 即 FIELDS 子句和 LINES 子句,它们的作用是决定数据行在备份文件中存储的格式,如果 FIELDS 和 LINES 都不指定,则默认声明的是子句 “FIELDS TERMINATED BY ‘\T’ ENCLOSED BY ” ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n'”.

iii) FIELDS 由三个亚子句,分别是”TERMINATED BY 子句” ” [OPTIONALLY] ENCLOSED BY子句”和”ESCAPED BY子句”., 如果指定了FIELDS 子句则这三个亚子句中至少要求指定一个

iv) LINES 子句使用关键字”OPTIONALLY” 指定一个数据行结束的标志.

v) 导出语句使用的关键字是”DUMPFILE” ,而非”OUTFILE”时,导出的备份文件所有的数据行都会彼此紧挨着放置,即值与行之间没有任何标记

查看secure-file-priv的当前值：

mysql> show variables like '%secure%';#为空则不能备份
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | NULL  |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

在mysql.cnf配置文件修改secure-file-priv的值

secure_file_priv = /var/lib/mysql/data;  #设置 = "" 空时可以传送到任意位置

重启后

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| secure_file_priv | /var/lib/mysql/data/ |
+------------------+----------------------+
1 row in set (0.00 sec)

例1

SELECT * FROM mysql_test.customer
      INTO OUTFILE '/var/lib/mysql/data/2019.txt'
      FIELDS TERMINATED BY ',' # 字段值之间用 , 号隔开
      OPTIONALLY ENCLOSED BY '"' #字段值如果时字符用双引号标注
      LINES TERMINATED BY "?";

2. 使用LOAD DATA … INFILE 语句恢复数据

在导入数据时要注意必须根据备份文件中数据行的格式来指定判断的符号

LOAD DATA INFILE 'file_name.txt'
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
]
[LINES 
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

i) LINES 子句中的TERMINATED BY 亚子句用来指定一行结束的标志; STARTING BY 亚子句则指定一个前缀,导入数据时, 忽略数据行中的该前缀和前缀之前的内容,如果某行不包括该前缀,则整个数据行被跳过.

例2. 将之前备份的” 2019.txt “恢复到数据库 mysql_test 中一个和customers 表结构相同的空表customer_copy 中:

mysql> LOAD DATA INFILE '/var/lib/mysql/data/2019.txt'
    -> INTO TABLE mysql_test.customer_copy
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '?';
Query OK, 112 rows affected (0.16 sec)