MySql -- 必知必会

一、数据库语言分类

SQL语言一共分为4大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL。
DDL是数据定义语言的缩写，主要对数据库内部的对象进行创建、删除、修改等操作的语言。
DML只对表内部数据进行操作，而不涉及表的定义、结构的修改。
DDL更多由数据库管理员（DBA）使用，开发人员一般很少使用。
DQL是数据查询语言。
DCL是数据控制语言，是用来设置或更改数据库用户或角色权限的语句。

1.数据定义语言DDL(Data Definition Language)

(1) CREATE — 创建

CREATE	作用	语法
CREATE DATABASE	创建数据库	CREATE DATABASE db_name
CREATE FUNCTION	创建用户定义的 SQL函数	CREATE FUNCTION func_name([func_parameter]) RETURNS type func_body
CREATE INDEX	创建索引	CREATE INDEX index_name ON table_name(column_list)
CREATE PROCEDURE	创建存储过程	CREATE PROCEDURE procedure_name
CREATE TABLE	创建表	CREATE TABLE table_name(column_name data_type(size),…)
CREATE TRIGGER	触发器，一种特殊的存储过程	CREATE TRIGGER trigger_name trigger_time trigger_event FOR EACH ROW trigger_stmt
CREATE USER	创建新用户	CREATE USER user_name IDENTIFY BY password
CREATE VIEW	创建视图	CREATE VIEW view_name AS query

自定义函数示例

DELIMITER //
CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)
BEGIN
RETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);
END //
DELIMITER ;

-- 调用函数
SELECT GetEmployeeInformationByID(1);

创建存储过程示例

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
   SELECT *  FROM products;
END //
DELIMITER ;

-- 调用存储过程
CALL GetAllProducts();

创建触发器示例

-- tirgger_time：触发时机，为BEFORE或者AFTER
-- trigger_event：触发事件，为INSERT、DELETE或者UPDATE
delimiter //
DROP TRIGGER IF EXISTS delete_trigger;
CREATE TRIGGER delete_trigger BEFORE DELETE
ON products FOR EACH ROW
BEGIN
INSERT INTO products_delete 
VALUES(OLD.prod_id,OLD.vend_id,OLD.prod_name,OLD.prod_price,OLD.prod_desc);
END //
delimiter ;

-- 删除测试，执行删除命令后，删除表会新增删除的信息
SET foreign_key_checks = 0; -- 先设置外键约束检查关闭
DELETE FROM products WHERE prod_id = 'FB';
SET foreign_key_checks = 1; -- 开启外键约束检查，以保持表结构完整性

创建视图示例

DROP VIEW IF EXISTS products_view;
CREATE VIEW products_view AS
SELECT products.*,productnotes.note_id,productnotes.note_date 
FROM products,productnotes 
WHERE products.prod_id = productnotes.prod_id;

(2) SHOW — 展示

SHOW
SHOW CREATE DATABASE
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SHOW CREATE VIEW

(3) DROP — 删除

DROP
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP TABLE
DROP TRIGGER
DROP USER
DROP VIEW

(3) ALTER — 修改

ALTER
ALTER TABLE table_name add column
ALTER TABLE table_name change old_columnName new_columnName new_type
ALTER TABLE table_name modify column new_type
ALTER TABLE table_name drop columns
ALTER DATABASE
ALTER FUNCTION
ALTER PROCEDURE
ALTER VIEW

(4) TRANCATE-- 删除再创建拥有相同结构的表

2.数据操纵语言DML(Data Manipulation Language)

(1) INSERT — 插入值

INSERT INTO table_name(column_name) VALUES(values);
INSERT INTO table_name1(column_name) SELECT column_name FROM table_name2;  

(2) UPDATE – 更新值

UPDATE table_name SET column_name = new_value WHERE column_name = value;
UPDATE table_name1,table_name2 
SET table1_name.column1_name = table2_name.column_name2 
WHERE table1_name.column_name0 = table2_name.column_name0;

(3) DELETE – 删除值

DELETE FROM table_name WHERE column_name = value;

3.数据查询语言DQL(Data Query Language)

SELECT column_list FROM tabel_name;
SELECT column_list INTO table2_name FROM table1_name;

4.数据控制语言DCL(Data Control Language)

GRANT --- 创建用户并赋予权限
GRANT ALL ON dbname.* TO 'user'@'192.168.12.9' IDENTIFIED BY 'password';
GRANT ALL ON *.* to 'user'@localhost; 
GRANT ALL ON *.* to 'user'@localhost WITH GRANT OPTION; -- 同时赋予GRANT权限
REVOKE --- 回收权限 
REVOKE ALL ON *.* to 'user'@localhost;
SHOW GRANTS FOR 'user'@localhost;

二、通配符和正则表达式

1.通配符

(1) LIKE 操作符

SELECT * FROM products WHERE prod_name LIKE 'Jet%';  -- 匹配Jet开头
SELECT * FROM products WHERE prod_name LIKE '%Jet';  -- 匹配Jet结尾
SELECT * FROM products WHERE prod_name LIKE '%Jet%';  -- 匹配包含Jet

(2) 通配符（%和_）
% 表示任何字符出现的任意次数
_ 表示只匹配单一字符

2.正则表达式

. 表示匹配任意一个字符
| 和 [] 都表示or匹配
\\ 表示匹配特殊字符的前导，如\\. , \\f , \\n , \\r 等

字符类

类	说明
[:alnum:]	任意字符和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表(同[\t])
[:cntrl:]	ASCII控制字符(ASCII 0到31和127) 任意数字(同[0-9])
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母(同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（同{1,}）
?	0个或1个匹配（同{0,1}）
{n}	指定数目匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数码范围内

定位符	说明
^	文本的开始
$	文本的结束
[[:<:]]	词的开始
[[::>]]	词的结束

SELECT * FROM products WHERE prod_name REGEXP '000'; -- 包含000
SELECT * FROM products WHERE prod_name REGEXP '0{3}'; -- 作用同上
SELECT * FROM products WHERE prod_name REGEXP '.000'; -- 包含四个字符，其中后三位为0
SELECT * FROM products WHERE prod_name REGEXP '1 ton|2 ton|5 ton'; -- | 表示或
SELECT * FROM products WHERE prod_name REGEXP '[1-5] ton'; -- []区间表示，作用同上
SELECT * FROM products WHERE prod_name REGEXP '\\.'; -- 匹配包含圆点的
SELECT * FROM products WHERE prod_name REGEXP '[:digit:]{3}'; -- 包含三位数字
SELECT * FROM products WHERE prod_name REGEXP '^\\.'; -- 匹配圆点开头
SELECT * FROM products WHERE prod_name REGEXP '\\)$'; -- 匹配)结尾

三、函数

1.字符串函数

函数	功能
CONCAT(S1,S2,…,SN)	连接S1，S2，…Sn为一个字符串
INSERT(STR,x,y,instr)	将字符串str从第x位置开始，y长度的子串替换为字符串instr
LOWER(str)	将字符串str中所有字符字符变为小写
UPPER(str)	将字符串str中所有字符字符变为大写
LEFT(str,x)	返回字符串str最左边的x个字符
RIGHT(str,x)	返回字符串str最右边的x个字符
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，填充长度为n
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，填充长度为n
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str右侧的空格
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str, x,y)	返回从字符串str x位置起y个字符长度的字符串

2.数值函数

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x)	返回x/y的模
RAND(x)	返回0～1内的随机值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

3.日期和时间函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的时间和日期
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME()	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时
MINUTE(time)	返回time的分钟
MONTHNAME(date)	返回date的月份名
DATE_FORMATE(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数。

4.流程函数

函数	功能
IF(value, t,f)	如果value是真，返回t，否则f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则value2
CASE WHEN value1 THEN result1 else default END	如果value1是真，返回result1，否则default
CASE expr WHEN value1 THEN result1 else default END	如果expr等于value1，返回result1，否则default

四、事务

成功运行则提交，否则回滚。

DELIMITER $$  
BEGIN  
DECLARE t_error INTEGER DEFAULT 0;  
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1 ;        
START TRANSACTION;  
UPDATE table1 SET a = '111';
INSERT INTO table2 (b) VALUES ('222');
INSERT INTO table3 (c) VALUES ('333');   
IF t_error = 1 THEN  
   ROLLBACK;  
ELSE  
   COMMIT;  
END IF;      
END$$  
DELIMITER ;