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查出ordernumSELECT order_num FROM orderitems WHERE prod_id='TNT2';*step2:根据ordernum查询出cust_idSELECT  cust_id FROM orders WHERE order_num in(    SELECT order_num FROM orderitems WHERE prod_id='TNT2');*step3:根据cust_id查询出cust_nameSELECT 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_nameFROM     products p,vendors vWHERE    p.vend_id = v.vend_idAND    prod_id='FC';--显示的结果+---------+-----------+| prod_id | vend_name |+---------+-----------+| FC      | ACME      |+---------+-----------+

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

  --代码SELECTc.cust_id,c.cust_name,p.prod_name,p.prod_price 单价,oi.order_item,(prod_price*order_item)单个商品总价FROMcustomers c,orders o,orderitems oi,products pWHEREc.cust_id=o.cust_idANDo.order_num =  oi.order_numANDoi.prod_id = p.prod_idHAVINGc.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 JOINorderitems    ONorders.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 JOINorderitems    ONorders.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 JOINorderitems    ONorders.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_idFROM     products p1,products p2WHERE    p1.vend_id = p2.vend_idAND    p2.prod_id = 'OL1';--显示结果+---------+---------+| vend_id | prod_id |+---------+---------+|    1002 | FU1     ||    1002 | OL1     |+---------+---------+

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

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

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

  --代码SELECT * FROM products WHERE vend_id = 10001UNIONSELECT * 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);

• 3.使用存储过程函数

  --代码:SELECT @sum_order;--显示的结果+------------+| @sum_order |+------------+|     149.87 |+------------+

  显示存储过程结构

• 显示某个存储过程详细信息

• SHOW CREATE PROCEDURE 存储过程名;

  --代码SHOW CREATE PROCEDURE order_sql;--显示的结果--------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode               | Create Procedure                                                                                                                                                                                              | character_set_client | collation_connection | Database Collation |

获得详细信息的存储过程

• SHOW PROCEDURE STATUS

--显示的结果+---------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------| Db      | Name           | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | +---------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------| ceshi80 | ordertotal     | PROCEDURE | root@localhost | 2018-10-17 11:45:00 | 2018-10-17 11:45:00 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | order_sql      | PROCEDURE | root@localhost | 2018-10-16 17:03:53 | 2018-10-16 17:03:53 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | order_sql1     | PROCEDURE | root@localhost | 2018-10-16 16:52:50 | 2018-10-16 16:52:50 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | proceorders    | PROCEDURE | root@localhost | 2018-10-17 09:40:56 | 2018-10-17 09:40:56 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | proceorders2   | PROCEDURE | root@localhost | 2018-10-17 10:36:11 | 2018-10-17 10:36:11 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | processorders3 | PROCEDURE | root@localhost | 2018-10-17 11:58:00 | 2018-10-17 11:58:00 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    || ceshi80 | products_sql   | PROCEDURE | root@localhost | 2018-10-16 16:08:10 | 2018-10-16 16:08:10 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |+---------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------

• 可以加'like',限制输出结果

游标

• 概念:
  • 游标:是一个存储在MYSQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集.在存储游标以后,可以滚动和浏览其中的数据.

• 【TIPS】：游标只能用于存储过程

  游标的过程

• 游标关闭后可以重新打开;如果不手动关闭,SQL语句遇到END会自动关闭.
• 步骤
• 1.创建游标 + 打开游标
  • a.创建游标
    • CREATE PROCEDURE 存储过程名()
      BEGIN
      DECLARE 游标名 CURSOR
      FOR
      SQL语句
      END;
  • b.打开和关闭游标
  • OPEN/CLOSE 游标名

  -- 代码DELIMITER $CREATE PROCEDURE proceorders()BEGIN    -- 创建游标    DECLARE ordernum CURSOR    FOR    SELECT order_num FROM orders;    -- 打开游标    OPEN ordernum;    -- 关闭游标    CLOSE ordernum;END $DELIMITER ;

• 2.用游标把数据检索出来
  • FETCH: 用于检索数据并存储起来
  • REPEAT: 对游标进行循环
    • FETCH如果在REPEAT内,FETCH会反复执行直到done为真

  REPEAT    FETCH ...UTIL done END REPEAT

• 2.1 如何使done设置为真?
  • 当SQLSTATE 'xxx'出现时,done设置为1;
  • 当SQLSTATE 'xxx'没有出现时,当REPEAT循环结束后退出.

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'XXX' set done=1;

  • DECLARE语句时有先后顺序
    • 局部变量 --> 游标 --> 句柄
  • CONTINUE HANDLER:是指条件出现时被执行的代码
  • 【例】：循环检索直至最后一行

  -- 步骤:--  1.首先定义变量和CONTINUE HANDLER    2.然后定义并打开游标,重复读取所有行    3.最后关闭游标--代码DELIMITER $CREATE PROCEDURE  proceorders2()BEGIN    -- 申明变量    DECLARE done BOOLEAN DEFAULT 0;    DECLARE o INT;    -- 创建游标    DECLARE ordernumbers CURSOR    FOR    SELECT order_num FROM orders;    -- 限制条件    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;    -- 打开光标    OPEN ordernumbers;    -- 循环语句    REPEAT        -- 检索的数据并存储起来        FETCH ordernumbers INTO o;    UNTIL done END REPEAT;    -- 关闭光标    CLOSE ordernumbers;END $DELIMITER ;

  游标的综合示例:

• 下面的例子包含:存储过程 + 游标 + 逐行处理 + 存储过程调用其他存储过程

  --代码-- 1.创建存储过程函数DELIMITER $CREATE PROCEDURE ordertotal(    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 $-- 2.把游标数据存储在新建表中CREATE PROCEDURE processorders3()BEGIN    -- 申明变量$    DECLARE done BOOLEAN DEFAULT 0;    DECLARE o INT;    DECLARE t DECIMAL(8,2);    -- 创建游标    DECLARE ordernumbers CURSOR    FOR    SELECT order_num FROM orders;    -- 限制条件    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;    -- 创建存储的表    CREATE TABLE IF NOT EXISTS order_total         (order_num INT,total DECIMAL(8,2));    -- 打开光标    OPEN ordernumbers;    -- 循环语句    REPEAT        -- 检索数据并存储起来        FETCH ordernumbers INTO o;        -- 调用存储过程        CALL ordertotal(o,t);        -- 在创建的表插入数据        INSERT INTO order_total (order_num,total)            VALUES(o,t);    UNTIL done END REPEAT;    -- 关闭光标    CLOSE ordernumbers;  END $DELIMITER ;-- 3.调用存储过程函数CALL processorders3();-- 4.查询创建的存储表SELECT * FROM order_total;-- 显示的结果+-----------+---------+| order_num | total   |+-----------+---------+|     20005 |  149.87 ||     20009 |   38.47 ||     20006 |   55.00 ||     20007 | 1000.00 ||     20008 |  125.00 ||     20008 |  125.00 |+-----------+---------+

事务管理

• 当SAVEPOINT、COMMIT或ROOLBACK执行之后,事务自动关闭.
• 【tips】:每种数据库引擎对事物支持的都不一样,InnoDB支持明确的事物处理引擎.
• 事务管理的目的:
  • 可以用来维护数据库的完整性.
• 事务关键字:
  • START TRANSACTION 开启事务
  • ROLLBACK 回滚
  • COMMIT 提交

  • SAVEPOINT 保存点.回退部分事物处理.

    ROLLBACK
• 格式:

START TRANSACTION;...ROLLBACK;

COMMIT

• 事物代码块语句都执行成功,才执行commit操作
• 格式:

START TARANSACTION;...COMMIT;

SAVEPOINT

• savepoint越多越好,因为可以灵活的回退

-- 创建保留点SAVEPOINT '保留点名称';-- 事物回滚到保留点ROLLBACK TO '保留点名称';-- 事物处理完,保留点会被自动释放.也可以手动释放,如下:RELEASE SAVEPOINT

SQL阶段学习的练习集

• 习题+答案 的,【提取码：fk83】