mysql学习笔记（1）

MySQL初识

安装Mysql

1.本次安装的版本是Mysql5.6绿色免安装版，下载的百度云地址，提取码【 2yrr 】
2.解压到指定的文件目录下（不含中文和空格目录）
3.把MySQL\bin目录添加到环境变量path中
3.管理员权限打开CMD命令行工具，切换目录到MySQL的Bin目录下
4.CMD执行 mysqld install ，安装Mysql服务
5.CMD执行 net start mysql，启动MySQL服务。
- 如果遇到MySQL服务1067错误解决措施：
- a) 定位到安装好的Mysql根目录，复制 my.ini 到 C盘 windows文件夹
- b) 记事本打开my-default.ini，把里面 #注释去掉（说明不用去）
- c) 重启电脑，CMD执行 net start mysql，启动MySQL服务。
6.CMD执行 mysql -u root -p root 进入mysql编辑器

Mysql的命令行语句

mysql -u root -p 后换行输入密码，不让别人知道密码
mysql -h IP地址 -P 访问主机的Mysql端口 -u 用户名 -p 密码
- mysql -h localhost -p3306 -u root -p root
exit 或 quit 退出mysql
导入外部表 source 路径+文件
更改字符集,默认是拉丁文,中文输入会乱码;一般选择UTF-8
- 1.查看当前数据库字符集
  - show VARIABLES like 'character%';
- 2.修改数据库字符集
  - alter database 数据库名 character set utf8;
- (或)修改表字符集
  - ALTER TABLE 表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- 3.重启mysql服务即可
  - cmd执行
    - 停止服务: net stop mysql
    - 启动服务: net start mysql
查询时解决显示乱码(不是修改数据库字符集,只是用指定字符集显示,退出数据库后失效)
* set character_set_results = 字符集;

基础SQL语句

SQL操作

DQL	(date query language,数据检索语言),有:SELECT
DML	(date manipulation language,数据操纵语言),有:INSERT、UPDATE、DELETE
DDL	(data definition language,数据定义语言),有:CREATE、ALTER、DROP、TRUNCATE
DCL	(data controlLanguage,数据控制语言),有:GRANT、REVOKE、DENY

增删查数据库&表

查询所有数据库：SHOW DATABASES;
创建数据库：CREATE DATABASE databasename;
选择某个数据库 use databasename;
- 例如 USE mysql;
删除数据库
- DROP DATABASE databasename;
查询所有表
- SHOW TABLES;
创建表（if not exists：如果不存在就创建）
- CREATE TABLE [ IF NOT EXISTS ] tablename;
选择表
- SELECT * FROM tablename;
删除表
- DROP TABLE tablename;

插入数据&查询数据

插入数据库
- INSERT INTO tablename (columname1,...,columnnamen) VALUES (columnvalue1,...,columnvaluen),[(columnvalue1,...,columnvaluen)],...
查询数据
- 查询所有： SELECT * FROM tablename
- 查询单列： SELECT columnname FROM tablename;
- 查询多列： SELECT columnname1,columnname2,...,columnnameN FROM tablename;
去重 distinct
- 单条件去重：
  - SELECT DISTINCT vend_id FROM products;
- 多条件去重：
  - SELECT DISTINCT vend_id , prod_price FROM products;
排序 order by
- 默认情况升序（asc）
  - 多条件升序
  - SELECT vend_id,prod_price FROM products ORDER BY vend_id ASC,prod_price ASC;
- 降序（desc）
  - SELECT vend_id,prod_price FROM products ORDER BY prod_price DESC,vend_id ASC;
限制结果 limit
- 表示前n行 limit n
  - SELECT * FROM products LIMIT 5;
- 表示显示从（第n+1行）开始的n行 limit m,n
  - SELECT * FROM products LIMIT(0，5);
- limit 和 order by 连用可以查询最高、最低的n条记录
  - SELECT prod_price FROM products ORDER BY prod_price asc LIMIT 3;
  - SELECT prod_price FROM products ORDER BY prod_price desc LIMIT 3;
过滤条件：where
- 操作符： = ，> , < , >= ,<= ,!= , <> 不等于
- SELECT * FROM products WHERE prod_price <> 10;
- 等于
- SELECT * FROM products WHERE prod_price != 10;
- 等于
- SELECT * FROM products WHERE prod_price > 10 OR prod_price < 10;
SQL语句的顺序
- SELECT * FROM products WHERE prod_price>10 ORDER BY prod_price DESC LIMIT 3;
- 自己确定优先级加括号（）
  - SELECT * FROM products WHERE (prod_price>10 AND prod_price<20) AND vend_id=1001;
两者之间，包含边界
- SELECT * FROM products WHERE prod_price BETWEEN 10 AND 20 ;
- 等于
- SELECT * FROM products WHERE prod_price >=10 AND prod_price <= 20;
- SELECT * FROM products WHERE prod_price NOT BETWEEN 10 AND 20;
空值查询： is null ; 非空查询： is not null
- SELECT * FROM vendors WHERE vend_id IS NULL;
- SELECT * FROM vendors WHERE vend_id IS NOT NULL;
集合查询IN(n1,n2,...,n) ; NOT IN(n1,n2,...,n)
- SELECT * FROM products WHERE vend_id IN (1001,1002,1003);
- SELECT * FROM products WHERE vend_id NOT IN (1001,1002,1003);
模糊查询
- % : 代替一个或多个字符
- : 代替一个字符,替代几个字符就需要几个""
- SELECT * FROM products WHERE prod_name LIKE '%a%'; 产品名包含字母a
- SELECT * FROM products WHERE prod_name LIKE 'a%'; 产品名以a开头
- SELECT * FROM products WHERE prod_name LIKE '_a%'; 产品名第二个字母是a
- 【例】:产品名同时含有a和o两个字符但不知道其先后顺序
  - SELECT * FROM products WHERE prod_name LIKE "%a%" AND prod_name LIKE "%o%";
  - 等于
  - SELECT * FROM products WHERE prod_name LIKE "%a%o%" OR prod_name LIKE "%o%a%";
正则匹配 REGEXP
- SELECT * FROM PRODUCTS WHERE prod_name REGEXP 'o'; 产品名中包含字母'o'
- "." 点表示代替一个字符
  - SELECT * FROM products where prod_name REGEXP '.o'; 点表示代替一个字符，选择从第二位往后匹配‘o’
- " ^ " 表示开头
  - SELECT * FROM products where prod_name REGEXP '^o';
- " $ " 表示结尾
  - SELECT * FROM products where prod_name REGEXP '$o';
- " | " 表示或者
  - 【例】：选出产品名字包含01，或02或03
  - SELECT * FROM products where vend_id REGEXP '01|02|03';
  - 等于
  - SELECT * FROM products where prod_id REGEXP '0[1|2|3]';
  - 等于
  - SELECT * FROM products where prod_id REGEXP '0[123]';
  - 等于
  - SELECT * FROM products where prod_id REGEXP '0[1-3]';
- ' * ' 表示匹配前面字符任意字符（0-N）次
- ' + ' 表示匹配前面字符至少一次（1-N）次
- ' ? ' 表示匹配前面字符至多一次（0或1）次
- ' {n} ' 表示匹配前面字符N次
- ' {n,} ' 表示匹配前面字符至少N次
- ' {n,m} ' 表示配置前面的字符（n~m）次，m最大次数为255
- ' [0-9] ' 表示（0~9）任意一位数字
- ' [A-Z] ' 或' [a-z] ' 表示任意字母
- ' \ ' 转义符，表示把字符转为普通字符串
- ' dual ' 表示mysql的虚表
- ' ^ ' 表示开头
- ' $ ' 表示结尾
- ' [^] ' 表示取反

*1.以1开头，后10位任意数字
*	^1[0-9]<10>$
*	SELECT	1 FROM DUAL WHERE '13333333333' REGEXP '^1[0-9]<10>$';
*2.邮箱格式
*	^[0-9a-zA-Z]+@[0-9a-z]<2,>\\.[a-z]+$
*	SELECT	1 FROM DUAL WHERE '133@sina.com' REGEXP '^[0-9a-zA-Z]+@[0-9a-zA-Z]{2,}\\.[a-zA-Z]+$';
*3.取不是（0-8）之案件数字
*	^[^0-8]$
*	SELECT 1 FROM DUAL WHERE '1' REGEXP '^[^0-8]';

字符处理函数
- 拼接字符串 CONCAT
  SELECT CONCAT(prod_id,'的价格为'，prod_price) from products;
取别名（AS可省略） AS
SELECT prod_id AS 编号，prod_price AS 价格 from products;
列全名&表全名
- SELECT ceshi80.products.prod_id,products.prod_name FROM ceshi80.products;

SQL数值计算

*，+，/
- SELECT prod_name 商品名,prod_price 进价,prod_price*1.1 售价 FROM products;
取余函数 mod(n,m)
- SELECT MOD(4,3),MOD(1,3),MOD(4,0) FROM DUAL;

+----------+----------+----------+
| MOD(4,3) | MOD(1,3) | MOD(4,0) |
+----------+----------+----------+
|        1 |        1 |     NULL |
+----------+----------+----------+

取绝对值函数 ABS（n）
- SELECT ABS(3.14),ABS(0),ABS(-3.14) FROM DUAL;

+-----------+--------+------------+
| ABS(3.14) | ABS(0) | ABS(-3.14) |
+-----------+--------+------------+
|      3.14 |      0 |       3.14 |
+-----------+--------+------------+

向上取整 ceil(n)
- SELECT CEIL(1.2),CEIL(1.9),CEIL(-1.2),CEIL(-1.9) FROM dual;

+-----------+-----------+------------+------------+
| CEIL(1.2) | CEIL(1.9) | CEIL(-1.2) | CEIL(-1.9) |
+-----------+-----------+------------+------------+
|         2 |         2 |         -1 |         -1 |
+-----------+-----------+------------+------------+

向下取整 floor(n)
- SELECT FLOOR(1.2),FLOOR(1.9),FLOOR(-1.2),FLOOR(-1.9) FROM DUAL;

+------------+------------+-------------+-------------+
| FLOOR(1.2) | FLOOR(1.9) | FLOOR(-1.2) | FLOOR(-1.9) |
+------------+------------+-------------+-------------+
|          1 |          1 |          -2 |          -2 |
+------------+------------+-------------+-------------+

四舍五入 round(n)
- SELECT ROUND(1.4),ROUND(1.5),ROUND(-1.4),ROUND(-1.5) FROM DUAL;

+------------+------------+-------------+-------------+
| ROUND(1.4) | ROUND(1.5) | ROUND(-1.4) | ROUND(-1.5) |
+------------+------------+-------------+-------------+
|          1 |          2 |          -1 |          -2 |
+------------+------------+-------------+-------------+

截断函数 truncate（m,n） (去尾,n为负数往前截取数，n为正网后截取数)
- SELECT TRUNCATE(1.23456,3),TRUNCATE(1.23456,8),TRUNCATE(333.23456,-2),TRUNCATE(1.23,-5),TRUNCATE(-1.23456,3),TRUNCATE(1.23934,2) FROM DUAL;

+---------------------+---------------------+------------------------+-------------------+----------------------+---------------------+
| TRUNCATE(1.23456,3) | TRUNCATE(1.23456,8) | TRUNCATE(333.23456,-2) | TRUNCATE(1.23,-5) | TRUNCATE(-1.23456,3) | TRUNCATE(1.23934,2) |
+---------------------+---------------------+------------------------+-------------------+----------------------+---------------------+
|               1.234 |          1.23456000 |                    300 |                 0 |               -1.234 |                1.23 |
+---------------------+---------------------+------------------------+-------------------+----------------------+---------------------+

开根号函数 sqrt(n)
- SELECT SQRT(3),SQRT(-3),SQRT(0) FROM dual;

+--------------------+----------+---------+
| SQRT(3)            | SQRT(-3) | SQRT(0) |
+--------------------+----------+---------+
| 1.7320508075688772 |     NULL |       0 |
+--------------------+----------+---------+

圆周率 pi() (默认精确到后6位，最大精度后16位)
- SELECT PI(),TRUNCATE(PI(),8),TRUNCATE(PI(),20) FROM dual;

+----------+------------------+------------------------+
| PI()     | TRUNCATE(PI(),8) | TRUNCATE(PI(),20)      |
+----------+------------------+------------------------+
| 3.141593 |       3.14159265 | 3.14159265358979360000 |
+----------+------------------+------------------------+

随机数 rand() (随机数和rand括号里数字无关)
- SELECT RAND(10),RAND(),RAND()*10 FROM dual;

+--------------------+--------------------+-------------------+
| RAND(10)           | RAND()             | RAND()*10         |
+--------------------+--------------------+-------------------+
| 0.6570515219653505 | 0.3438523061050589 | 9.198721627889872 |
+--------------------+--------------------+-------------------+

字符处理函数

拼接字符串 concat(str1,str2,str3)
返回字符串字符数 char_length()
length() 返回字符串的长度（注：因版本不同中文占2或3个）
字符串大写 upper()
字符串小写 lower()
返回字符串s前n个字符 left(s,n)
返回字符串s后n个字符 right(s,n）
字符串左填充 lpad(s1,len,s2) （以s2左填充S1,使得s1的总长为len）（注：如果len长度少于s1长度，后面字符串会被截取）
字符串右填充 lpad(s1,len,s2) （以s2右填充S1,使得s1的总长为len）（注：如果len长度少于s1长度，后面字符串会被截取）
去左空格 ltrim(s) (去掉字符串左边的空格)
- SELECT LTRIM(' df '),LENGTH(LTRIM(' df ')) FROM DUAL;

+-----------------+------------------------+
| LTRIM('  df  ') | LENGTH(LTRIM(' df  ')) |
+-----------------+------------------------+
| df              |                      4 |
+-----------------+------------------------+

去右空格 rtrim(s) (去掉字符串右边的空格)
- SELECT RTRIM(' df '),LENGTH(LTRIM(' df ')) FROM DUAL;

+-----------------+-------------------------+
| RTRIM('  df  ') | LENGTH(LTRIM('  df  ')) |
+-----------------+-------------------------+
|   df            |                       4 |
+-----------------+-------------------------+

去两边空格 trim(s)
- SELECT TRIM(' df '),LENGTH(TRIM(' df ')) FROM DUAL;

+----------------+------------------------+
| TRIM('  df  ') | LENGTH(TRIM('  df  ')) |
+----------------+------------------------+
| df             |                      2 |
+----------------+------------------------+

去左右两端的字符 trim (str1 from str2)
- SELECT TRIM('aa' FROM 'aaabfaabfaa') FROM DUAL;

+-------------------------------+
| TRIM('aa' FROM 'aaabfaabfaa') |
+-------------------------------+
| abfaabf                       |
+-------------------------------+

替换字符 INSERT(str1,n,m,str2) 用str2替换str1第n个字符开始的后m个字符
- SELECT INSERT('aaaaaaaa',3,2,'hhhh') FROM DUAL;

+-------------------------------+
| INSERT('aaaaaaaa',3,2,'hhhh') |
+-------------------------------+
| aahhhhaaaa                    |
+-------------------------------+

查找和替换 replace(str1,str2,str3),用str3替换str1中的str2
- SELECT REPLACE('aabbdddff','bb','cc') FROM DUAL;

+--------------------------------+
| REPLACE('aabbdddff','bb','cc') |
+--------------------------------+
| aaccdddff                      |
+--------------------------------+

截取字符串 substring(str,n,len) 返回字符串str中第n个字符开始的len长度
- SELECT SUBSTRING('123456',3,2),SUBSTRING('123456',3,6) FROM DUAL;

+-------------------------+-------------------------+
| SUBSTRING('123456',3,2) | SUBSTRING('123456',3,6) |
+-------------------------+-------------------------+
| 34                      | 3456                    |
+-------------------------+-------------------------+

字符串比较函数 strcmp
- SELECT STRCMP('AB','AB'),STRCMP('AB','ab') FROM DUAL;

+-------------------+-------------------+
| STRCMP('AB','AB') | STRCMP('AB','ab') |
+-------------------+-------------------+
|                 0 |                 0 |
+-------------------+-------------------+

SELECT STRCMP('AB','ab'),STRCMP('abcd','abc') FROM DUAL;

+-------------------+----------------------+
| STRCMP('AB','ab') | STRCMP('abcd','abc') |
+-------------------+----------------------+
|                 0 |                    1 |
+-------------------+----------------------+

查找字符串位置(1) instr(str1,str2) str2在str1中位置，返回字符串中第一次出现位置
- SELECT instr ('chaina','a') FROM DUAL;

+----------------------+
| instr ('chaina','a') |
+----------------------+
|                    3 |
+----------------------+

查找字符串位置(2) locate(s1,s2) 返回s1在s2位置
- SELECT LOCATE('a','chaina') FROM DUAL;

+----------------------+
| LOCATE('a','chaina') |
+----------------------+
|                    3 |
+----------------------+

查找字符串位置(3) position(s1 in s2)返回s1在s2位置
- SELECT POSITION('h' in 'chinahis') FROM DUAL;

+-----------------------------+
| POSITION('h' in 'chinahis') |
+-----------------------------+
|                           2 |
+-----------------------------+

查找字符串位置(3) substring_index(s1,s2,n) 返s1开始的，以s2作为分割符第n个前面的字符。

SELECT SUBSTRING_INDEX('ni nihao ma, wo hen hao',' ',3) FROM DUAL;

+--------------------------------------------------+
| SUBSTRING_INDEX('ni nihao ma, wo hen hao',' ',3) |
+--------------------------------------------------+
| ni nihao ma,                                     |
+--------------------------------------------------+

查找字符串位置(4) ELT(n,str1,str2,str3...)返回第n个字符串
- SELECT ELT(3,'ZHANG3','LI4','WANGWU'),ELT(4,'ZHANG3','LI4','WANGWU') FROM DUAL;

+--------------------------------+--------------------------------+
| ELT(3,'ZHANG3','LI4','WANGWU') | ELT(4,'ZHANG3','LI4','WANGWU') |
+--------------------------------+--------------------------------+
| WANGWU                         | NULL                           |
+--------------------------------+--------------------------------+

日期函数

返回当前的日期 curdate()(注意只有一个r),current_date()
- SELECT CURDATE(),CURRENT_DATE() FROM DUAL;

+------------+----------------+
| CURDATE()  | CuRRENT_DATE() |
+------------+----------------+
| 2018-10-12 | 2018-10-12     |
+------------+----------------+

返回当前的时间 curtime(),current_time()
- SELECT CURTIME(),CURRENT_TIME() FROM DUAL;

+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 11:18:49  | 11:18:49       |
+-----------+----------------+

返回当前的日期和时间 now(),current_timestamp(),sysdate(),localtime(),localtimestamp()
- SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;

+---------------------+---------------------+---------------------+---------------------+---------------------+
| NOW()               | CURRENT_TIMESTAMP() | SYSDATE()           | LOCALTIME()         | LOCALTIMESTAMP()    |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2018-10-12 11:19:15 | 2018-10-12 11:19:15 | 2018-10-12 11:19:15 | 2018-10-12 11:19:15 | 2018-10-12 11:19:15 |
+---------------------+---------------------+---------------------+---------------------+---------------------+

返回日期date中的年 YEAR(date)
返回日期date中的月 month(date) 返回阿拉伯数字； monthname(date) 返回英文月份
- SELECT YEAR('2018-09-03'),MONTH('2018-10-9'),MONTHNAME('2018-10-9') FROM DUAL;

+--------------------+--------------------+------------------------+
| YEAR('2018-09-03') | MONTH('2018-10-9') | MONTHNAME('2018-10-9') |
+--------------------+--------------------+------------------------+
|               2018 |                 10 | October                |
+--------------------+--------------------+------------------------+

SELECT YEAR(now()),YEAR(CURTIME()) FROM DUAL;

+-------------+-----------------+
| YEAR(now()) | YEAR(CURTIME()) |
+-------------+-----------------+
|        2018 |            2018 |
+-------------+-----------------+

返回日期date中月份的第几天 dayofmonth(date)
- SELECT DAYOFMONTH(CURTIME()) FROM DUAL;

+-----------------------+
| DAYOFMONTH(CURTIME()) |
+-----------------------+
|                    12 |
+-----------------------+

返回日期date中年的第几天 dayofyear(date)

SELECT DAYOFYEAR(NOW()),DAYOFYEAR('2008-12-30'),DAYOFYEAR('2018-12-30') FROM DUAL;

+------------------+-------------------------+-------------------------+
| DAYOFYEAR(NOW()) | DAYOFYEAR('2008-12-30') | DAYOFYEAR('2018-12-30') |
+------------------+-------------------------+-------------------------+
|              285 |                     365 |                     364 |
+------------------+-------------------------+-------------------------+

hour(time) 返回时间time中的时
minute(time) 返回时间time中的分
second(time) 返回时间time中的秒
- SELECT CURTIME(), HOUR(CURTIME()),MINUTE(NOW()),SECOND(NOW()) FROM DUAL;

+-----------+-----------------+---------------+---------------+
| CURTIME() | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(NOW()) |
+-----------+-----------------+---------------+---------------+
| 11:24:28  |              11 |            24 |            28 |
+-----------+-----------------+---------------+---------------+

extract(type) 从日期date中返回指定type的值
- SELECT
  
      EXTRACT(YEAR FROM NOW()) 年,EXTRACT(MONTH FROM NOW()) 月,
  
      EXTRACT(DAY FROM NOW()) 日,EXTRACT(HOUR FROM NOW()) 时,
  
      EXTRACT(MINUTE FROM NOW()) 分,EXTRACT(SECOND FROM NOW()) 秒
  
  FROM DUAL;
```
+------+------+------+------+------+------+
| 年   | 月   | 日   | 时   | 分   | 秒   |
+------+------+------+------+------+------+
| 2018 |   10 |   12 |   11 |   30 |   59 |
+------+------+------+------+------+------+
```

adddate(date,n) 返回前面日期加上多少天；可用于计算产假，入职多少天等

SELECT ADDDATE(NOW(),50);

+---------------------+
| ADDDATE(NOW(),50)   |
+---------------------+
| 2018-12-01 11:31:27 |
+---------------------+

addtime(date,n) 返回前面日期加上多少秒，n最大59
- SELECT NOW(),ADDTIME(NOW(),59),ADDTIME(NOW(),60) FROM DUAL;

+---------------------+---------------------+-------------------+
| NOW()               | ADDTIME(NOW(),59)   | ADDTIME(NOW(),60) |
+---------------------+---------------------+-------------------+
| 2018-10-12 11:31:52 | 2018-10-12 11:32:51 | NULL              |
+---------------------+---------------------+-------------------+

datediff(date1,date2) 返回 date1 - date2 的值，有绝对值

SELECT DATEDIFF(NOW(),'1993-05-07'),DATEDIFF('1993-05-07',NOW()) FROM DUAL; #计算从出生到现在有多少天

+------------------------------+------------------------------+
| DATEDIFF(NOW(),'1993-05-07') | DATEDIFF('1993-05-07',NOW()) |
+------------------------------+------------------------------+
|                         9289 |                        -9289 |
+------------------------------+------------------------------+

格式化日期 date_format(date,str) str 有【 %Y%M%D】、【 %y%m%d 】、【 %y/%m/%d 】、【 %Y/%M/%D 】

SELECT DATE_FORMAT('2018-08-11','%Y%M%D') FROM DUAL;

+------------------------------------+
| DATE_FORMAT('2018-08-11','%Y%M%D') |
+------------------------------------+
| 2018August11th                     |
+------------------------------------+

SELECT DATE_FORMAT('2018-08-11','%y%m%d') FROM DUAL;

+------------------------------------+
| DATE_FORMAT('2018-08-11','%y%m%d') |
+------------------------------------+
| 180811                             |
+------------------------------------+

SELECT DATE_FORMAT('2018-08-11','%Y/%M/%D') FROM DUAL;

+--------------------------------------+
| DATE_FORMAT('2018-08-11','%Y/%M/%D') |
+--------------------------------------+
| 2018/August/11th                     |
+--------------------------------------+

SELECT DATE_FORMAT('2018-08-11','%y/%m/%d') FROM DUAL;

+--------------------------------------+
| DATE_FORMAT('2018-08-11','%y/%m/%d') |
+--------------------------------------+
| 18/08/11                             |
+--------------------------------------+

判断空否 ifnull(v1,v2) 如果v1值不为null，则返回v1，否则返回v2
- SELECT vend_state,IFNULL(VEND_STATE,'SZ') AS 城市 FROM vendors;

+------------+--------+
| vend_state | 城市   |
+------------+--------+
| MI         | MI     |
| OH         | OH     |
| CA         | CA     |
| NY         | NY     |
| NULL       | SZ     |
| NULL       | SZ     |
+------------+--------+

if(exp,v1,v2) 如果表达式exp成立，返回v1,否则返回v2
- SELECT vend_state,IF(VEND_STATE IS NULL,'SZ',VEND_STATE) AS 城市 FROM vendors; #(等同与上者)

+------------+--------+
| vend_state | 城市   |
+------------+--------+
| MI         | MI     |
| OH         | OH     |
| CA         | CA     |
| NY         | NY     |
| NULL       | SZ     |
| NULL       | SZ     |
+------------+--------+

SELECT prod_name,prod_price,IF(prod_price > 5,'贵','便宜') AS 评价 FROM products;

+----------------+------------+--------+
| prod_name      | prod_price | 评价   |
+----------------+------------+--------+
| .5 ton anvil   |       5.99 | 贵     |
| 1 ton anvil    |       9.99 | 贵     |
| 2 ton anvil    |      14.99 | 贵     |
| Detonator      |      13.00 | 贵     |
| Bird seed      |      10.00 | 贵     |
| Carrots        |       2.50 | 便宜   |
| Fuses          |       3.42 | 便宜   |
| JetPack 1000   |      35.00 | 贵     |
| TNT (5 sticks) |      10.00 | 贵     |
+----------------+------------+--------+

多重判断 case when exp1 than '别名' [when exp2 than '别名'] [...] end 列别名

SELECT vend_id,prod_name,prod_price,
	case
		when prod_price <= 10 then '便宜'
		when prod_price>10  && prod_price <= 20 then '比较贵'
		when prod_price>20 then '贵'
	end 评价
FROM products; 

运行结果：
+---------+----------------+------------+-----------+
| vend_id | prod_name      | prod_price | 评价      |
+---------+----------------+------------+-----------+
|    1001 | .5 ton anvil   |       5.99 | 便宜      |
|    1001 | 1 ton anvil    |       9.99 | 便宜      |
|    1001 | 2 ton anvil    |      14.99 | 比较贵    |
|    1003 | Detonator      |      13.00 | 比较贵    |
|    1003 | Bird seed      |      10.00 | 便宜      |
|    1005 | JetPack 1000   |      35.00 | 贵        |
|    1005 | JetPack 2000   |      55.00 | 贵        |
|    1002 | Oil can        |       8.99 | 便宜      |
|    1003 | Safe           |      50.00 | 贵        |
+---------+----------------+------------+-----------+

系统信息函数

返回数据库的版本号： version()
返回服务器的链接数： connection_id()
返回当前的数据库名： database(),schema()
返回当前用户： user(),system_user(),current_user()

加密函数： password(str),md5(str)

+-------------------------------------------+----------------------------------+
| password('root')                          | md5('root')                      |
+-------------------------------------------+----------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 63a9f0ea7bb98050796b649e85481845 |
+-------------------------------------------+----------------------------------+

格式化函数

将数字进行格式格式化：format(x,n) （n数字，n截取的小数位）
- SELECT FORMAT(1234.1234,2) FROM DUAL;

+---------------------+
| FORMAT(1234.1234,2) |
+---------------------+
| 1,234.12            |
+---------------------+

ascll(str) 返回字符串第一个字符的ASCII值
bin(str) 十进制转二进制

数值函数

最大值 max() 空值不会参与计算
最小值 min() 空值不会参与计算
总计 sum() 空值不会参与计算
平均数 avg() 空值不会参与计算
- SELECT MAX(prod_price)最大值,min(prod_price) 最小值,AVG(prod_price) 总计,AVG(prod_price)平均数 FROM products;

+-----------+-----------+-----------+-----------+
| 最大值    | 最小值    | 总计      | 平均数    |
+-----------+-----------+-----------+-----------+
|     55.00 |      2.50 | 16.133571 | 16.133571 |
+-----------+-----------+-----------+-----------+

计数 count()
count(*) 会统计非空； count(column) 空值不会参与计算
- SELECT COUNT(*),COUNT(VEND_STATE) FROM VENDORS;

+----------+-------------------+
| COUNT(*) | COUNT(VEND_STATE) |
+----------+-------------------+
|        6 |                 4 |
+----------+-------------------+

聚合函数与分组 GROUP BY (后面一列,以这列作为分组;如果多列,以多列会不相同作为分组)

本SQL语句数据下载地址:百度云,提取码【6gin】

1.测试的表数据

订单号表数据

SELECT * FROM orderitems;

+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
|     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 |          1 | FC      |       50 |       2.50 |
|     20009 |          1 | FB      |        1 |      10.00 |
|     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+------------+---------+----------+------------+

2.练习

分组函数 GROUP BY
select order_num,sum(quantity*item_price)应付款 from orderitems group by order_num;

选择商品号作为分组条件的商品号与应付款

+-----------+-----------+
| order_num | 应付款    |
+-----------+-----------+
|     20005 |    149.87 |
|     20006 |     55.00 |
|     20007 |   1000.00 |
|     20008 |    125.00 |
|     20009 |     38.47 |
+-----------+-----------+

选择以商品号和应付款分组条件的商品号和应付款

select order_num,sum(quantity*item_price)应付款 from orderitems group by order_num,prod_id;

+-----------+-----------+
| order_num | 应付款    |
+-----------+-----------+
|     20005 |     59.90 |
|     20005 |     29.97 |
|     20005 |     10.00 |
|     20005 |     50.00 |
|     20006 |     55.00 |
|     20007 |   1000.00 |
|     20008 |    125.00 |
|     20009 |     14.99 |
|     20009 |     10.00 |
|     20009 |      8.99 |
|     20009 |      4.49 |
+-----------+-----------+

总和 ROLLUP
- 选择商品号和应付款,以及所有商品号应付款的总额
- select order_num,sum(quantity*item_price)应付款 from orderitems group by order_num WITH ROLLUP;

+-----------+-----------+
| order_num | 应付款    |
+-----------+-----------+
|     20005 |    149.87 |
|     20006 |     55.00 |
|     20007 |   1000.00 |
|     20008 |    125.00 |
|     20009 |     38.47 |
|      NULL |   1368.34 |
+-----------+-----------+

过滤分组条件 HAVING (在GROUP BY后面)
- 选择应付款大于100的商品号和应付款
- select order_num,sum(quantity*item_price)应付款 from orderitems group by order_num HAVING 应付款>100;

+-----------+-----------+
| order_num | 应付款    |
+-----------+-----------+
|     20005 |    149.87 |
|     20007 |   1000.00 |
|     20008 |    125.00 |
+-----------+-----------+

分组条件的综合利用(注意语法的先后顺序)

选择订单号在(20005,20006,20007)内,以订单号作为分组,应付款大于100并以应付款作为降序条件,筛选出前一个的订单号与应付款.

代码:
SELECT ORDER_NUM,SUM(quantity*item_price) 应付款
	FROM 
orderitems 
	WHERE order_num in(20005,20006,20007)
	GROUP BY order_num
	HAVING 应付款>100
	ORDER BY 应付款 desc
	LIMIT 1;

显示结果:
+-----------+-----------+
| ORDER_NUM | 应付款    |
+-----------+-----------+
|     20007 |   1000.00 |
+-----------+-----------+

子查询

【例1】查看提供FB产品的供应商还提供哪些产品

--代码
SELECT vend_id,prod_id FROM products WHERE vend_id IN
(
	SELECT vend_id FROM products WHERE prod_id='FB'
);

-- 显示的结果
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1003 | DTNTR   |
|    1003 | FB      |
|    1003 | FC      |
|    1003 | SAFE    |
|    1003 | SLING   |
|    1003 | TNT1    |
|    1003 | TNT2    |
+---------+---------+

【例2】查看提供FB产品的供应商名字

--代码
SELECT vend_name FROM vendors WHERE vend_id IN
(
SELECT vend_id FROM products WHERE prod_id='FB'
);

--显示的结果
+-----------+
| vend_name |
+-----------+
| ACME      |
+-----------+

【例3】列出订购物品TNT2的所有客户

--代码:
*step1:根据TNT2查出ordernum
SELECT order_num FROM orderitems WHERE prod_id='TNT2';

*step2:根据ordernum查询出cust_id
SELECT  cust_id FROM orders WHERE order_num in
(
	SELECT order_num FROM orderitems WHERE prod_id='TNT2'
);

*step3:根据cust_id查询出cust_name
SELECT cust_id,cust_name FROM customers WHERE cust_id in
(
	SELECT  cust_id FROM orders WHERE order_num in
	(
		SELECT order_num FROM orderitems WHERE prod_id='TNT2'
	)
);

--显示的结果:
+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10004 | Yosemite Place |
+---------+----------------+

【例4】查询产品价格大于平均数的产品信息

--代码
SELECT * FROM products where prod_price >
(
	SELECT AVG(prod_price) FROM products 
);

--显示的结果
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 |      35.00 | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 |      55.00 | JetPack 2000, multi-use               |
| SAFE    |    1003 | Safe         |      50.00 | Safe with combination lock            |
+---------+---------+--------------+------------+---------------------------------------+

自然连接

表名可以取别名

【例1】：显示商品id为'FC'的供应商名称

--代码
SELECT 
	p.prod_id,v.vend_name
FROM 
	products p,vendors v
WHERE
	p.vend_id = v.vend_id
AND
	prod_id='FC';
--显示的结果
+---------+-----------+
| prod_id | vend_name |
+---------+-----------+
| FC      | ACME      |
+---------+-----------+

【例2】显示用户id为'10001',购买所有商品名,单价

--代码
SELECT
c.cust_id,c.cust_name,p.prod_name,p.prod_price 单价,oi.order_item,(prod_price*order_item)单个商品总价
FROM
customers c,orders o,orderitems oi,products p
WHERE
c.cust_id=o.cust_id
AND
o.order_num =  oi.order_num
AND
oi.prod_id = p.prod_id
HAVING
c.cust_id=10001;

--显示结果
+---------+-------------+----------------+--------+------------+--------------------+
| cust_id | cust_name   | prod_name      | 单价   | order_item | 单个商品总价       |
+---------+-------------+----------------+--------+------------+--------------------+
|   10001 | Coyote Inc. | .5 ton anvil   |   5.99 |          1 |               5.99 |
|   10001 | Coyote Inc. | 1 ton anvil    |   9.99 |          2 |              19.98 |
|   10001 | Coyote Inc. | TNT (5 sticks) |  10.00 |          3 |              30.00 |
|   10001 | Coyote Inc. | Bird seed      |  10.00 |          4 |              40.00 |
|   10001 | Coyote Inc. | Bird seed      |  10.00 |          1 |              10.00 |
|   10001 | Coyote Inc. | Oil can        |   8.99 |          2 |              17.98 |
|   10001 | Coyote Inc. | Sling          |   4.49 |          3 |              13.47 |
|   10001 | Coyote Inc. | 2 ton anvil    |  14.99 |          4 |              59.96 |
+---------+-------------+----------------+--------+------------+--------------------+

左连接&右外连接&内连接

左外连接(left join .. AND)&右外连接(right join .. AND)&内连接(inner join .. ON)
左外连接:左边的都显示,右边没有则显示NULL
右外连接(左外连接相反):右边都显示,左边没有则显示NULL

【例1】左连接:查询orderitems与items这两张表

--代码
SELECT orders.*,orderitems.*
	FROM  
orders
	LEFT JOIN
orderitems
	ON
orders.order_num = orderitems.order_num;

--显示结果
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
| order_num | order_date          | cust_id | order_num | order_item | prod_id | quantity | item_price |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          4 | FB      |        1 |      10.00 |
|     20006 | 2005-09-12 00:00:00 |   10003 |     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 | 2005-09-30 00:00:00 |   10004 |     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 | 2005-10-03 00:00:00 |   10005 |     20008 |          1 | FC      |       50 |       2.50 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          1 | FB      |        1 |      10.00 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+

【例2】内连接:查询orderitems与items这两张表

--代码
SELECT orders.*,orderitems.*
	FROM  
orders
	INNER JOIN
orderitems
	ON
orders.order_num = orderitems.order_num;

--显示结果
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
| order_num | order_date          | cust_id | order_num | order_item | prod_id | quantity | item_price |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          4 | FB      |        1 |      10.00 |
|     20006 | 2005-09-12 00:00:00 |   10003 |     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 | 2005-09-30 00:00:00 |   10004 |     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 | 2005-10-03 00:00:00 |   10005 |     20008 |          1 | FC      |       50 |       2.50 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          1 | FB      |        1 |      10.00 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+

【例3】右连接:查询orderitems与items这两张表

--代码
SELECT orders.*,orderitems.*
	FROM  
orders
	RIGHT JOIN
orderitems
	ON
orders.order_num = orderitems.order_num;

--显示结果
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
| order_num | order_date          | cust_id | order_num | order_item | prod_id | quantity | item_price |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 | 2005-09-01 00:00:00 |   10001 |     20005 |          4 | FB      |        1 |      10.00 |
|     20006 | 2005-09-12 00:00:00 |   10003 |     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 | 2005-09-30 00:00:00 |   10004 |     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 | 2005-10-03 00:00:00 |   10005 |     20008 |          1 | FC      |       50 |       2.50 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          1 | FB      |        1 |      10.00 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 | 2005-10-08 00:00:00 |   10001 |     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+---------------------+---------+-----------+------------+---------+----------+------------+

自连接

自连接比子查询快得多

【例1】查询生产'OL1'的厂家还生产哪些商品

--代码
SELECT 
	p1.vend_id,p1.prod_id
FROM 
	products p1,products p2
WHERE
	p1.vend_id = p2.vend_id
AND
	p2.prod_id = 'OL1';

--显示结果
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1002 | FU1     |
|    1002 | OL1     |
+---------+---------+

组合查询 union [all] (行拼接)

只要数据类型一样即可,表名不同也可以;4
默认以第一条数据的列名为组合查询的列名

union 默认自动去重复; union all 显示所有数据

--代码
SELECT * FROM products WHERE vend_id = 10001
UNION
SELECT * FROM products WHERE prod_price >10;

--显示的结果
+---------+---------+--------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                                                      |
+---------+---------+--------------+------------+----------------------------------------------------------------+
| ANV03   |    1001 | 2 ton anvil  |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator    |      13.00 | Detonator (plunger powered), fuses not included                |
| JP1000  |    1005 | JetPack 1000 |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000 |      55.00 | JetPack 2000, multi-use                                        |
| SAFE    |    1003 | Safe         |      50.00 | Safe with combination lock                                     |
+---------+---------+--------------+------------+----------------------------------------------------------------+

SQL语句结构

DML语句(增删改查w)

查询
- SELECT *|column(s)|exp
  
  FROM tablename
  
  WHERER exp
  
  GROUP BY ...
  
  HAVING ...
  
  ORDER BY ...
  
  LIMIT m,n
插入
- INSERT INTO tablename (columnname1,...,columnnameN) values (columnvalue1,...,columnvalueN),...
- (columnname1,...,columnname2)可以省略,但是建议写
删除(一):DELECT是要逐行删除表中数据.它只删除表内容,但不删除表本身.
- DELECT FROM tablename [ WHERE columnname=coluumnvalue ];
删除(二):TRUNCATE是删除整张表,重新创建一张新表.速度更快
- TRUNCATE tablename;
更新:
- UPDATE tablename SET (columnname1=value1,columnname2=value2,...) WHERE columnname=value;
查询结果插入到表中(插入查询的数据和表列名一致)

INSERT INTO tablename1 (SELECT column(s) FROM tablename2) WHERE exp;

【例】:
CREATE TABLE vendors1 (vend_id int primary key,vend_name char(50),vend_address char(50));

--vendors1表结构
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| vend_id      | int(11)  | NO   | PRI | NULL    |       |
| vend_name    | char(50) | YES  |     | NULL    |       |
| vend_address | char(50) | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+

INSERT INTO vendors1(SELECT vend_id,vend_name,vend_address FROM vendors WHERE vend_id in (1001,1002));

+---------+-------------+-----------------+
| vend_id | vend_name   | vend_address    |
+---------+-------------+-----------------+
|    1001 | Anvils R Us | 123 Main Street |
|    1002 | LT Supplies | 500 Park Street |
+---------+-------------+-----------------+

创建表和操纵管理

comment 给表添加申明
- CREATE TABLE tablename (columnname1 columntype1 COMMENT alias,...) ;
PRIMARY KEY 主键
- CREATE TABLE tablename (columnname1 columntype1 PRIMARY KEY ,...) ;
NOT NULL 不为空
- CREATE TABLE tablename (columnname1 columntype1 NOT NULL ,...) ;
AUTO_INCREMENT 自增
- CREATE TABLE tablename (columnname1 columntype1 AUTO_INCREMENT ,...) ;
DEFAULT 默认值
- CREATE TABLE tablename (columnname1 columntype1 DEFAULT defaultValues ,...) ;
ENUM 枚举类型
- CREATE TABLE tablename(columname1 ENUM('str1','str2'),...)

修改表 alter

ADD 增加列

ALTER TABLE tablename ADD(columnname1 columntype1 [COMMENT/PRIMARY KEY/NOT NULL/AUTO_INCREMENT/DEFAULT..],...)

--代码:
ALTER TABLE vendors1 ADD tell int(20) DEFAULT 0000;

--显示的结果:
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| vend_id      | int(11)  | NO   | PRI | NULL    |       |
| vend_name    | char(50) | YES  |     | NULL    |       |
| vend_address | char(50) | YES  |     | NULL    |       |
| tell         | int(20)  | YES  |     | 0       |       |
+--------------+----------+------+-----+---------+-------+

* **modify 可以修改列的类型** 不会改变原来的值,只会改变修改后的值. * ALTER TABLE tablename **MODIFY**(columnname1 columntype1 [COMMENT/PRIMARY KEY/NOT NULL/AUTO_INCREMENT/DEFAULT..],...)
* **CHANGE 可以用来修改列名和类型** 不会改变原来的值,只会改变修改后的值. * change需要两个列名,后面的列名替换前面的列名 * ALTER TABLE tablename columname old-columnname new-columnname newtype;

删除列

删除某一列
- ALTER TABLE tablename DROP columnname;

添加列的限制

如果是单列直接在列后面添加限制
- CREATE TABLE tablename (columnname1 columntype1 PRIMARY KEY ,...) ;
如果是多列,例如增加主键
PK_SC 主键
- CREATE TABLE tablename(columnname1 columntype1,..,PRIMARY KEY(columnname1 columntype1));
- 等于
- ALTER TABLE tablename ADD CONSTRAINT pk_sc PRIMARY KEY(columnname1,..);
添加外键
FK_SC_表名外键
- ALTER TABLE tablename1 ADD CONSTRAINT fk_sc_tablename2 FORGIN KEY(columnname) REFERENCES tablename2(tablename2);
添加唯一约束 UNIQUE

修改表名 rename

语法:
- RENAME TABLE old-tablename TO new-tablename;

DCL 给用户添加权限

创建新用户

创建连接数据库的权限
- CREATE USER '用户名' @'localhost'/IP地址 IDENTIFIED BY '密码'

销毁用户

DROP USER '用户名'@'localhost'/IP地址

修改用户名密码

SET PASSWORD FOR '用户名'@'localhost'/IP地址 = password('新密码');

授权

给用户授权
- GRANT [SELECT],[INSERT],[UPDATE],[DELETE],[CREATE],[ALTER],[DROP] ON [数据库名.表名]/[数据库名.*] to '用户名'@'localhost'/IP地址
- GRANT GRANT OPTION ON [数据库名.表名]/[数据库名.*] to '用户名'@'localhost'/IP地址
  - 设置权限有效期
- GRANT ALL ON [数据库名.表名]/[数据库名.*] to '用户名'@'localhost'/IP地址
  - 赋与全部权限
回收用户授权
- REVOKE [SELECT],[INSERT],[UPDATE],[DELETE],[CREATE],[ALTER],[DROP] ON [数据库名.表名]/[数据库名.*] FROM '用户名'@'localhost'/IP地址
- REVOKE ALL ON [数据库名.表名]/[数据库名.*] FROM '用户名'@'localhost'/IP地址

存储过程(存储过程是函数,存储过程名后需要有'()',括号里可以传递参数 )

概念:
- 存储过程是为了以后的使用而保存的一条或多条的MYSQL语句的结合
优点:
- 可以作为批文件,用于批处理。
- 通过复杂的SQL语句封装起来,方便使用.
- 统一使用统一存储过程,保证数据完整性和一致性.
- 为了安全性.如果表名、列名或业务逻辑等，只需要修改存储过程，而外部不知道里面变化。
- 存储过程比单个SQL语句要快。
- 可以用存储过程编写更强大，更灵活的SQL语句。

1.创建没有参数存储过程 PROCEDURE

DELIMITER 把结束符';'转义

DELIMITER $

CREATE PROCEDURE name() BEGIN SQL语句 END;

DELIMITER ;

--代码
DELIMITER $
CREATE PROCEDURE product_sql() 
BEGIN
	SELECT prod_id,prod_name FROM products;
END $
DELIMITER ;

2.调用过程 CALL

调用存储过程可以显示结果,也可以不显示结果

CALL name (..)

--代码
CALL product_sql();

--显示的结果
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| ANV01   | .5 ton anvil   |
| ANV02   | 1 ton anvil    |
| ANV03   | 2 ton anvil    |
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| FU1     | Fuses          |
| JP1000  | JetPack 1000   |
| JP2000  | JetPack 2000   |
| OL1     | Oil can        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

3.删除存储过程

DROP PROCEDURE [IF EXISTS] name

--代码
DROP PROCEDURE IF EXISTS product_sql ;

有参数的存储过程 PROCEDURE

1.创建存储过程函数

创建最低价格,最高价格,平均价格的存储函数

--代码
DELIMITER $
CREATE PROCEDURE products_sql
(
	OUT min_prod DECIMAL(8,2),
	OUT max_prod DECIMAL(8,2),
	OUT avg_prod DECIMAL(8,2)
)
BEGIN
	SELECT MIN(prod_price) INTO min_prod FROM products;
	SELECT MAX(prod_price) INTO max_prod FROM products;
	SELECT AVG(prod_price) INTO avg_prod FROM products;
END $
DELIMITER ;

2.调用存储过程 CALL(@param1,@param2,..)
存储过程中调用的变量必须与创建时的变量一致,变量顺序可以不同
```
--代码
CALL products_sql(@max_prod,@avg_prod,@min_prod);
```

3.使用存储过程函数

--代码
SELECT @min_prod,@max_prod,@avg_prod ;

--显示的结果
+-----------+-----------+-----------+
| @min_prod | @max_prod | @avg_prod |
+-----------+-----------+-----------+
|     16.13 |      2.50 |     55.00 |
+-----------+-----------+-----------+

存储过程中IN 和 OUT

INTO的位置既可以在 SELECT exp之后,也可以是在WHERE之后

【例】：传入订单号并返回该订单总金额

1.创建存储过程函数

--代码:
DELIMITER $
CREATE PROCEDURE  order_sql 
(
	IN  ordernum INT,
	OUT sum_order DECIMAL(8,2)
)
BEGIN
	SELECT SUM(item_price*quantity)  FROM orderitems WHERE order_num = ordernum INTO sum_order;
END $
DELIMITER ;

2.调用存储过程函数
注意此处ordernum需要传入值,而不是变量@param

--代码:
CALL order_sql(20005,@sum_order);