MySQL 基础（二）：数据操作与常用函数-优快云博客

本文链接：https://blog.youkuaiyun.com/oschina_41790905/article/details/104460352

文章目录

添加记录

# 语法
INSERT [INTO] tbl_name[(col_name,....)] VALUE|VALUES(values...);

# 不指定字段名
INSERT INTO tbl_name VALUE(值....);

# 列出指定字段
INSERT INTO tbl_name(字段名....) VALUE(值....);

# INSERT .... SET 形式
INSERT INTO tbl_name SET 字段名=值,....;

# INSERT .... SELECT 形式
INSERT INTO tbl_name[(字段名....,)] SELECT [(字段名....,)] FROM tbl_name [WHERE 条件];

# 一次添加多条记录
INSERT INTO tbl_name (字段名,....) VALUES(值....),(值....),....;

更新记录

# 如果不指定条件，整个表的记录都会被更新
UPDATE tbl_name SET 字段名=值,字段名=值,....[WHERE 条件];

删除记录

# 如果不指定条件，整个表的记录都会被删除
# DELETE 清空数据表的时候不会重置 AUTO_INCREMENT 的值
DELETE FROM tnl_name [WHERE 条件]; 

# 彻底清空数据表，清空数据表并重置 AUTO_INCREMENT 的值
TRUNCATE TABLE tbl_name;

查询记录

# 语法
SELECT select_exxpr,.... FROM tbl_name 
   [WHERE条件] 
   [GROUP BY {col_name|position} HAVING 二次筛选] 
   [ORDER BY {col_name|position|expr}[ASC|DESC]] 
   [LIMIT 限制结果集的显示条数] 

# 查询所有记录
 SELECT * FROM tbl_name;  

# 查询表中指定字段的信息（别名一般不要超过三个字母）
SELECT 字段名 AS 别名,.... FROM tbl_name AS 别名;

WHERE 条件，筛选出符合条件的记录

比较运算符：> >= < <= != <> <=>， <=> 可以检测 NULL 值;
IS [NOT] NULL：检测值是否是 NULL 值;
指定范围：[NOT] BETWEEN.... AND....;
指定集合：[NOT] IN(值…);
逻辑运算符：AND（逻辑与） | OR（逻辑或）；
匹配运算符：[NOT] LIKE
%: 任意的字符串
_: 任意一个字符

GROUP BY 分组

把相同的值分到同一组，最终查询出的结果只会显示组中的一条记录；
分组配合 GROUP_CONCAT() 可以查看组某个字段的详细信息；
SELECT GROUP_CONCAT(col_name) FROM tbl_name GROUP BY col_name1;
配合聚合函数使用

COUNT()：统计表中的总记录数；
如果是 COUNT(col_name) 字段中的值是 NULL 的话不会统计，写 COUNT(*) 会统计 NULL 值；
MAX()：求最大值；
MIN()：求最小值；
AVG()：求平均值；
WITH ROLLUP：会在记录末尾添加一条记录，是上面所有记录的总和。

多表查询

# 内连接形式
SELECT 字段名,.... FROM tbl_name1 INNER JOIN tbl_name2 ON 条件;  

# 左外连接形式
SELECT 字段名,.... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 条件; 
(先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以 NULL 代替)  

# 右外连接形式
SELECT 字段名,.... FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON 条件;
(先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以 NULL 代替)

创建外键

注：只有 InnoDB 存储引擎支持外键。

# 建表时指定外键
# 子表的外键字段和主表字段类型要相似；如果是数值型，则要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同。
# 如果外键字段没有创建索引，MySQL 会自动帮我们添加索引。
# 子表的外键关联必须是主表的主键。
[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称);

# 外键约束的参照操作
{DELETE | UPDATE} CASCADE: 级联操作，从主表删除或更新记录，子表也跟着删除或更新记录。
SET NULL: 
NO ACTION | RESTRICT: 拒绝对主表做更新或删除操作。

# 动态添加外键  
ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCE 主表(主键字段);

注：动态添加外键之前表中的记录一定是合法的记录，没有脏数据，否则外键添加不成功。
  
# 动态删除外键
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;

ANY SOME ALL

运算符

联合查询

# UNION 
SELECT 字段名,.... FROM tbl_name1 UNION SELECT 字段名,... FROM tbl_name2

# UNION ALL
SELECT 字段名,.... FROM tbl_name1 UNION ALL SELECT 字段名,... FROM tbl_name2

注：UNION ALL 是简单的合并，UNION 会去掉表中重复记录

MySQL 常用的数学函数

# CEIL() 进一取整; 
SELECT CEIL(2.3) => 3

# FLOOR() 向下取整; 
SELECT FLOOR(2.3) => 2

# ROUND() 四舍五入; 
SELECT ROUND(2.456,2) => 2.46 (保留 2 位小数)

# TRUNCATE() 截取小数点后几位; 
SELECT TRUNCATE(3.4567,2) => 3.45

# MOD() 取余数; 
SELECT MOD(5,2) => 1

# ABS() 取绝对值; 
SELECT ABS(-1) => 1

# POWER() 幂运算; 
SELECT POWER(2,3) => 8

# PI() 圆周率; 
SELECT PI() => 3.141593

# RAND() 或 RAND(X) 0~1 之间的随机数; 
SELECT RAND(1) => 其中 1 是标识，标识相同的值是一样的

# SIGN(X) 得到数字的符号; 
SELECT SIGN(-12) => -1 (值是负数时返回 -1，正数返回 1，0 返回 0)

# EXP() 计算 e 的 x 次方; 
SELECT EXP(2) => E^2

MySQL 常用的字符串函数

# CHAR_LENGTH() 得到字符串的字符数; 
SELECT CHAR_LENGTH('abc') => 3; 
SELECT CHAR_LENGTH('你好') => 3;

# LENGTH() 得到字符串的长度;
SELECT LENGTH('你好') => 6; 一个汉字在 3 个字节（utf-8）

# CONCAT(s1,s2,....) 将字符串合并成一个字符串 
SELECT CONCAT('A','B','C'); => ABC

# CONCAT_WS(x,s1,s2,....) 以指定分割符拼接字符串
SELECT CONCAT_WS('-','A','B','C'); => A-B-C
注意：如果拼接的字符串中包含 NULL，拼接结果返回 NULL

UPPER() | UCASE() 将字符串转换成大写
LOWER() | LCASE() 将字符串转换成小写

# REVERSE() 将字符串反转
SELECT REVERSE('ABC'); => CBA 

# LEFT() | RIGHT() 返回字符串的前几个字符或后几个字符
SELECT LEFT('hello',2); => he
SELECT RIGHT('hello',3); => llo

# LPAD() | RPAD() 用字符串左或右填充到指定长度
SELECT LPAD('ABC',5,'?'); => ??ABC 左填充到 5 位
SELECT RPAD('ABC',5,'?'); => ABC?? 右填充到 5 位

# TRIM() | LTRIM() | RTRIM() 去掉空格
SELECT CONCAT('*',TRIM(' ABC '),'*'),CONCAT('*',LTRIM(' ABC '),'*'),CONCAT('*',RTRIM(' ABC '),'*');

# REPEAT() 重复字符串的指定次数
SELECT REPEAT('HELLO',3); => HELLOHELLOHELLO

# REPLACE() 替换指定的字符串
SELECT REPLACE('hello king','king','queen'); => hello queen

# SUBSTRING() 截取字符串(从 1 开始)
SELECT SUBSTRING('abcdt',1,3); => abc

# STRCMP() 比较字符串
SELECT STRCMP('B','A'); => 1

MySQL 常用的日期函数

# CURDATE(),CURRENT_DATE() 返回的当前日期
SELECT CURDATE(),CURRENT_DATE(); => 2018-12-02

# CURTIME(),CURRENT_TIME() 返回当前时间
SELECT CURTIME(),CURRENT_TIME(); => 21:16:56

# NOW(),CURRENT_TIMESTAMP(),SYSDATE() 返回当前的日期时间
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(); => 2018-12-02 21:19:20

# MONTH()、MONTHNAME() 返回日期中的月份和月份的名称
SELECT MONTH('2012-02-19'),MONTHNAME('2012-02-19'); => 2,February

# DAYNAME() 返回日期是星期几
SELECT DAYNAME('2012-02-19'); => Thursday

# DAYOFWEEK() 返回一周内的第一天
SELECT DAYOFWEEK('2012-02-19'); => 1

# WEEK() 返回一年的第几周
SELECT WEEK('2012-02-19'); => 8

# YEAR(),DAY(),HOUR(),MINUTE(),SECOND()
返回年、日、时、分、秒

# DATEDIFF() 计算两个日期相差的天数
SELECT DATEDIFF('2012-09-12','2012-09-18'); => -6

其他常用函数

# 返回版本
VERSION() 

# 返回连接数
CONNECTION_ID() 

# 返回当前登录用户
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER() 

# 返回上一步插入操作产生的 AUTO_INCREMENT 的值
LAST_INSERT_ID() 

#  md5 加密，得到 32 位的结果
MD5(s)

# PASSWORD() 密码加密算法
SELECT * FROM mysql.user WHERE user='root'\G;