MySQL 总结速查

名词速查

SQL：Structure QUERY Language，结构查询语言
DBMS： database management system 数据库管理系统（SQL、MySQL、MongoDB、Oracle）
DB： 数据库
用户访问顺序： DBMS-->DB-->Table-->DATA

DDL: database Defined Language – 数据定义语句

• create
• alter
• drop

DML: database Manipulation language, 数据操作语句

• insert
• insert ignore
• replace
• update
• on duplicate key update
• delete、truncate、drop

DQL：database query language, 数据查询语句

• select

句点符号： database.table
IF NOT EXIST: 如果存在一个具有相同名字的表，并且指定了这个语句，MySQL指挥抛出一个警告，告知表已存在，否则将抛出一个错误。
AUTO_INCREMENT: 自动生成线性递增序列，因此不必担心为每一行的id分配值
PRIMARY_KEY: 每行都有一个非空的UNIQUE 列标识，只有一列应该在表中定义。
ENGINE：存储引擎，常见的：InnoDB、MyISAM、FEDERATED、BLACKGHOLE、CSV、MEMERY。默认为InnoDB。

连接数据库客户端： mysql -h <ip> -P 3306 -u <username> -p <pwd>

DQL

操作符

= 　　　　　　　　　相等
<> / != 　　　　　　 不相等
< 　　　　　　　 　小于
<= 　　　　　　 　 小于等于
＞ 　　　　　　　 　大于
= 　　　　　　　 　大于等于
between and 　　　 位于两值之间
IN(A，B) 　　　　 　A 和 B 之间
AND 　　　　　 　连接多个表达式 并且的关系
NOT 否定操作符结果

简单的模式匹配（LIKE）

可以使用LIKE运算符来实现简单的模式匹配。使用 _来精准的匹配一个字符，使用%来匹配任意数量的字符。

-- 找出以Ch开头的，ed结尾的
SELECT COUNT(*) FROM employees WHERE first_name LIKE "Ch%ed";

-- 包含sri的
SELECT COUNT(*) FROM employees WHERE first_name LIKE "%sri%";

-- 找出以任意两个字符开头，后面跟随ka、在后面跟任意字符的
SELECT COUNT(*) FROM employees WHERE first_name LIKE "__ka%";

正则表达式

官方示例

• RLIKE
• REGEXP

-- 查询以ed结尾的
SELECT COUNT(*) FROM employees WHERE first_name RLIKE/REGEXP "ed$"; 

使用以上两个运算符在WHERE子句中使用正则表达式

LIMIT ORDER BY(DESC ASC-默认)

-- 查询薪水最高的前5人

SELECT * FROM salary ORDER BY salary DESC LIMIT 5;

聚合函数

group by进行分组，然后使用AGGREGATE聚合函数，exp: COUNT MAX MIN AVERAGE等。

count

mysql> SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender;
+--------+--------+
| gender | count  |
+--------+--------+
| M      | 179973 |
| F      | 120051 |
+--------+--------+
2 rows in set (0.14 sec)

SUM

YEAR(‘2021-01-01’) —> 2021

-- 查询每年发放的工资总额
mysql> select YEAR(from_date), SUM(salary) as sum from salaries group by YEAR(from_date) order by sum desc;
+-----------------+------------+
| YEAR(from_date) | sum        |
+-----------------+------------+
|            2000 | 4872800232 |
|            2001 | 4865055245 |
|            1999 | 4819071522 |
|            1998 | 4512943072 |
|            1997 | 4193934028 |
|            1996 | 3863964696 |
|            1995 | 3519568111 |
|            1994 | 3186854762 |
|            1993 | 2853411421 |
|            2002 | 2850670689 |
|            1992 | 2517858216 |
|            1991 | 2171138966 |
|            1990 | 1841868858 |
|            1989 | 1510027035 |
|            1988 | 1189364829 |
|            1987 |  869113734 |
|            1986 |  567672827 |
|            1985 |  269080290 |
+-----------------+------------+

AVERAGE

-- 查询平均工资最高的10个员工
mysql> select emp_no,AVG(salary) as avg from salaries GROUP BY emp_no order by avg desc limit 10;
+--------+-------------+
| emp_no | avg         |
+--------+-------------+
|  43624 | 138492.9444 |
|  37558 | 138215.8571 |
|  46439 | 135747.7333 |
|  44188 | 132560.6667 |
|  66793 | 132440.1667 |
|  51531 | 132186.4286 |
|  47978 | 131890.2222 |
|  80823 | 131565.0000 |
|  39964 | 131024.3333 |
|  53402 | 129764.3750 |
+--------+-------------+
10 rows in set (0.29 sec)

DISTINCT

mysql> select distinct(emp_no) from salaries limit 10;
+--------+
| emp_no |
+--------+
|  10001 |
|  10002 |
|  10003 |
|  10004 |
|  10005 |
|  10006 |
|  10007 |
|  10008 |
|  10009 |
|  10010 |
+--------+

HAVING

可以通过添加HAVING子句 来过滤GROUP By子句的结果

Tips

克隆表结构

CREATE TABLE new_table_name LIKE exist_table_name;

INSERT、INSERT IGNORE、REPLACE、ON DUPLICATE KEY UPDATE区别

1. insert 和 insert ignore
   都为插入语句，会根据 主键或者唯一约束 判断是否插入
   insert 在插入的时候，如果与主键或唯一键重复，会报错
   insert ignore 会忽略此次插入，主键索引 +1， 否则直接插入，等同于insert
2. replace
   根据主键或者唯一约束判断，如果不存在，则等同于insert，如果存在，则会删除原来的一行，重新新增一行数据。
3. on duplicate key update (修改数据，与insert/insert ignore用，replace会报错)
   在已知该项存在时，处理重复项，使用 on duplicate key update
   exp: insert into student(name,age,addr) values(‘大娃’, 33, ‘beijing’) on duplicate key update name=values(name),addr=values(addr);
   注释： 在执行该语句的时候，如果不存在，则只执行前半部分。如果存在，则只修改后半部分的值，未指定不修改。
   ignore的风险，有可能会对联合唯一索引的值修改，不报错。