MySQL 基础（二）：数据操作与常用函数

分手后的思念是犯贱 2021-09-21 03:20 256阅读 0赞

### 文章目录 ###

*   *   *  添加记录
         *  更新记录
         *  删除记录
         *  查询记录
         *  WHERE 条件，筛选出符合条件的记录
         *  GROUP BY 分组
         *  多表查询
         *  创建外键
         *  ANY SOME ALL
         *  联合查询
         *  MySQL 常用的数学函数
         *  MySQL 常用的字符串函数
         *  MySQL 常用的日期函数
         *  其他常用函数

### 添加记录 ###

# 语法
    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;
 *  配合聚合函数使用

1.  COUNT()：统计表中的总记录数；  
    如果是 COUNT(col\_name) 字段中的值是 NULL 的话不会统计，写 COUNT(\*) 会统计 NULL 值；
2.  MAX()：求最大值；
3.  MIN()：求最小值；
4.  AVG()：求平均值；
5.  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 ###

![运算符][2020022315084030.png]

### 联合查询 ###

# 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;

[2020022315084030.png]: /images/20210920/d25f8622354b44509077fe3fe97844a4.png