Mysql 笔记之SQL语句及查询语句详解
DDL DATABASE | SCHEMA , TABLE , INDEX
BTREE 全指匹配 左前缀 列前缀 范围匹配
HASH 等值比较 = <=> IN()
索引加速查询降低修改
查询操作 DML
INSERT INTO UPDATE DELETE
查看INSERT 的帮助信息如下
MariaDB [(none)]> HELP INSERT;
Name: 'INSERT'
Description:
Syntax:
1、第一种INSERT 使用方法
INSERT [INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...批量插入
Mysql基于默认，
2、第二种INSERT 使用方法
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...通常一次插入一ci
3、第三种INSERT 使用方法
INSERT [INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
例题创建表 tmp8 id自增
MariaDB [tb_test2]> CREATE TABLE tmp8(id INT(11) AUTO_INCREMENT PRIMARY KEY,name CHAR(20),gender ENUM('m','f'));
Query OK, 0 rows affected (0.01 sec)
第一种方法
MariaDB [tb_test2]> INSERT INTO tmp8 VALUES(NULL,'lin chong', 'm'),(NULL,'Wu song','m'),(NULL,'Sun erniang','f');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [tb_test2]> SELECT * FROM tmp8;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | lin chong | m |
| 2 | Wu song | m |
| 3 | Sun erniang | f |
+----+-------------+--------+
3 rows in set (0.00 sec)
第一种方法
MariaDB [tb_test2]> INSERT INTO tmp8 (name,gender)VALUES('Yang zhi','m');
Query OK, 1 row affected (0.01 sec)
MariaDB [tb_test2]> SELECT * FROM tmp8;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | lin chong | m |
| 2 | Wu song | m |
| 3 | Sun erniang | f |
| 4 | Yang zhi | m |
+----+-------------+--------+
4 rows in set (0.00 sec)
第二中插入方法 set
MariaDB [tb_test2]> INSERT INTO tmp8 SET name='Li kui',gender='m';
Query OK, 1 row affected (0.01 sec)
MariaDB [tb_test2]> SELECT * FROM tmp8;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | lin chong | m |
| 2 | Wu song | m |
| 3 | Sun erniang | f |
| 4 | Yang zhi | m |
| 5 | Li kui | m |
+----+-------------+--------+
5 rows in set (0.00 sec)
使用第一种方法再加两位女英雄
MariaDB [tb_test2]> INSERT INTO tmp8( name,gender) VALUES('Gu dasao','f'),('Hu sanmiang','f');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [tb_test2]> SELECT * FROM tmp8;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | lin chong | m |
| 2 | Wu song | m |
| 3 | Sun erniang | f |
| 4 | Yang zhi | m |
| 5 | Li kui | m |
| 6 | Gu dasao | f |
| 7 | Hu sanmiang | f |
+----+-------------+--------+
7 rows in set (0.00 sec)
再创建个表tmp7
MariaDB [tb_test2]> CREATE TABLE tmp7 (id INT(11)AUTO_INCREMENT PRIMARY KEY,name CHAR(30),gender ENUM('m','f'));
Query OK, 0 rows affected (0.02 sec)
MariaDB [tb_test2]> INSERT INTO tmp7 VALUES(NULL,'Zhu geliang','m'),(NULL,'Hang yueying','f');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [tb_test2]> SELECT * FROM tmp7;
+----+--------------+--------+
| id | name | gender |
+----+--------------+--------+
| 1 | Zhu geliang | m |
| 2 | Hang yueying | f |
+----+--------------+--------+
2 rows in set (0.00 sec)
第三种查找tmp8的gender=‘f’的所有人添加到tmp7中
MariaDB [tb_test2]> INSERT INTO tmp7(name,gender) SELECT * FROM tmp8 WHERE gender='f';
ERROR 1136 (21S01): Column count doesn't match value count at row 1
报错了， 下面才可以
MariaDB [tb_test2]> INSERT INTO tmp7(name,gender) SELECT name,gender FROM tmp8 WHERE gender='f';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [tb_test2]> SELECT * FROM tmp7;
+----+---------------+--------+
| id | name | gender |
+----+---------------+--------+
| 1 | Zhu geliang | m |
| 2 | Huang yueying | f |
| 3 | Sun erniang | f |
| 4 | Gu dasao | f |
| 5 | Hu sanmiang | f |
+----+---------------+--------+
5 rows in set (0.00 sec)
UPDATE
MariaDB [tb_test2]> HELP UPDATE;
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
UPDATE table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
单表操作
判断某字段值为空 IS NULL 不为空 IS NOT NULL
把ClassID为空的行的年龄改为70
MariaDB [hellodb]> UPDATE students SET Age=70 WHERE ClassID IS NULL;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 70 | M | NULL | NULL |
| 25 | Sun Dasheng | 70 | M | NULL | NULL |
| 26 | Hong qigong | 70 | M | NULL | NULL |
| 27 | Yi Deng | 70 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
找出年龄小于等于20的
MariaDB [hellodb]> SELECT * FROM students WHERE Age<=20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
把年龄小于18的ClassID改成3
MariaDB [hellodb]> UPDATE students SET ClassID=3 WHERE Age<18;
Query OK, 1 row affected (0.01 sec)
Rows matched: 2 Changed: 1 Warnings: 0
再次查看
MariaDB [hellodb]> SELECT * FROM students WHERE Age<=20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 3 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
使用ORDER BY 默认升序排列，LIMIT限制前几行
MariaDB [hellodb]> UPDATE students SET ClassID=4 ORDER BY Age LIMIT 3;
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [hellodb]> SELECT * FROM students WHERE Age<20 ORDER BY Age;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 4 | NULL |
| 14 | Lu Wushuang | 17 | F | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 4 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
8 rows in set (0.01 sec)
按年龄逆序排 ORDER BY Age DESC
MariaDB [hellodb]> UPDATE students SET ClassID=5 WHERE Age<20 ORDER BY Age DESC LIMIT 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [hellodb]> SELECT * FROM students WHERE Age<20 ORDER BY Age DESC;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 5 | NULL |
| 10 | Yue Lingshan | 19 | F | 5 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 4 | NULL |
| 8 | Lin Daiyu | 17 | F | 4 | NULL |
| 14 | Lu Wushuang | 17 | F | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> HELP DELETE;
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:
DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
删除年龄大于60的用户
MariaDB [hellodb]> DELETE FROM students WHERE Age>60;
Query OK, 4 rows affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM students ORDER BY Age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 4 | NULL |
| 8 | Lin Daiyu | 17 | F | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 4 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 10 | Yue Lingshan | 19 | F | 5 | NULL |
| 7 | Xi Ren | 19 | F | 5 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
删除按年龄排序前三行
MariaDB [hellodb]> DELETE FROM students ORDER BY Age LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM students ORDER BY Age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 5 | NULL |
| 7 | Xi Ren | 19 | F | 5 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+---------------+-----+--------+---------+-----------+
20 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.01 sec)
查缓存，， 只要大于1的正数 表示启用查询缓存的功能
MariaDB [hellodb]> SELECT @@GLOBAL.query_cache_size;
+---------------------------+
| @@GLOBAL.query_cache_size |
+---------------------------+
| 16777216 |
+---------------------------+
1 row in set (0.00 sec)
查询当前时间
MariaDB [hellodb]> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 18:25:45 |
+----------------+
1 row in set (0.00 sec)
或者
MariaDB [hellodb]> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-08-30 18:26:54 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SELECT @@GLOBAL.query_cache_type;
+---------------------------+
| @@GLOBAL.query_cache_type |
+---------------------------+
| ON |
+---------------------------+
1 row in set (0.00 sec)
有三种情况DEMAND 表示按需缓存，意思是只有明确写明要缓存结果的SELECT语句的结果才会进行缓存。
注意几乎所有的缓存中，缓存的内容都是key-value格式
Key：查询语句的hash码；
Value：查询语句的执行result
缓存就是提高性能的。
客户端------》查询缓存------》客户端；
客户端------》查询缓存-----》解析器-------》预处理器-------》查询优化器-------》查询执行引擎-------》存储引擎（处理数据）--------》查询执行引擎-------》客户端
分组的目的就是聚合的，聚合后可处理如求平均值
SELECT values_to_diplay -------投影表示挑选出字段的，过滤列的
FROM table_name
WHERE expression ---- 挑选 选择符合条件的行
GROUP BY how_to_group
HAVING expression
ORDER BY how_to_sort
LIMIT row_count;
WHERE可使用算数运算符，关系运算符
如
MariaDB [hellodb]> SELECT Name,Age FROM students; 红色的挑选出列
+---------------+-----+
| Name | Age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
+---------------+-----+
20 rows in set (0.00 sec)
字段别名
MariaDB [hellodb]> SELECT Name AS student,age FROM students;
+---------------+-----+
| student | age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
+---------------+-----+
20 rows in set (0.00 sec)
DISTINCT 对应相同的值只显示一次
MariaDB [hellodb]> SELECT DISTINCT AGE FROM students;
+-----+
| AGE |
+-----+
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 20 |
| 23 |
| 33 |
| 21 |
| 25 |
+-----+
11 rows in set (0.00 sec)
BETWEEN 。。AND。。范围
IN 表示存在性测试 指定范围内存在性。 IN（10,100,1000）后接列表，
IS NULL /////// IS NOT NULL
LIKE 可以使用通配符
RLIKE 可使用正则表达式
事例
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Name LIKE 's%';
+-------------+-----+
| Name | Age |
+-------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Shi Qing | 46 |
+-------------+-----+
3 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Name RLIKE '^s.*u$';
+-------------+-----+
| Name | Age |
+-------------+-----+
| Shi Zhongyu | 22 |
+-------------+-----+
1 row in set (0.00 sec)
逻辑操作符 and or not
GROUP BY 做分组
MariaDB [hellodb]> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID;
+---------+----------+
| ClassID | AVG(Age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 26.0000 |
| 4 | 24.7500 |
| 5 | 28.0000 |
| 6 | 21.6667 |
| 7 | 21.0000 |
+---------+----------+
7 rows in set (0.08 sec)
MariaDB [hellodb]> SELECT ClassID ,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(Age)>25;
+---------+----------+
| ClassID | AVG(Age) |
+---------+----------+
| 2 | 36.0000 |
| 3 | 26.0000 |
| 5 | 28.0000 |
+---------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT ClassID ,AVG(Age) AS AVG FROM students GROUP BY ClassID HAVING AVG>25;
GROUP BY : 做聚合计算是使用，表示根据指定的字段，对符合条件的行做分组，而后对每分组做聚合计算； 聚合计算 AVG()平局数；SUM()求和；MAX()最大； MIN() COUNT()统计个数。
HAVING: 对聚合计数做过滤。
ORDER BY 指定字段排序可以有多个。
MariaDB [hellodb]> SELECT ClassID,COUNT(ClassID)FROM students GROUP BY ClassID;
+---------+----------------+
| ClassID | COUNT(ClassID) |
+---------+----------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 1 |
| 4 | 4 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
+---------+----------------+
7 rows in set (0.02 sec)
MariaDB [hellodb]> SELECT ClassID,SUM(Age)FROM students GROUP BY ClassID;
+---------+----------+
| ClassID | SUM(Age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 26 |
| 4 | 99 |
| 5 | 84 |
| 6 | 65 |
| 7 | 42 |
+---------+----------+
7 rows in set (0.02 sec)
使用HAVING
MariaDB [hellodb]> SELECT ClassID,SUM(Age)FROM students GROUP BY ClassID HAVING SUM(Age)>80;
+---------+----------+
| ClassID | SUM(Age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 4 | 99 |
| 5 | 84 |
+---------+----------+
4 rows in set (0.01 sec)
按名字排序
MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Name;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Diao Chan | 19 |
| Ding Dian | 32 |
| Duan Yu | 19 |
| Hua Rong | 23 |
| Huang Yueying | 22 |
| Lin Chong | 25 |
| Ma Chao | 23 |
| Ren Yingying | 20 |
| Shi Potian | 22 |
| Shi Qing | 46 |
| Shi Zhongyu | 22 |
| Tian Boguang | 33 |
| Wen Qingqing | 19 |
| Xi Ren | 19 |
| Xiao Qiao | 20 |
| Xie Yanke | 53 |
| Xu Zhu | 21 |
| Yu Yutong | 26 |
| Yuan Chengzhi | 23 |
| Yue Lingshan | 19 |
+---------------+-----+
20 rows in set (0.01 sec)
按年龄
MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Wen Qingqing | 19 |
| Yue Lingshan | 19 |
| Xi Ren | 19 |
| Duan Yu | 19 |
| Diao Chan | 19 |
| Ren Yingying | 20 |
| Xiao Qiao | 20 |
| Xu Zhu | 21 |
| Huang Yueying | 22 |
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Yuan Chengzhi | 23 |
| Hua Rong | 23 |
| Ma Chao | 23 |
| Lin Chong | 25 |
| Yu Yutong | 26 |
| Ding Dian | 32 |
| Tian Boguang | 33 |
| Shi Qing | 46 |
| Xie Yanke | 53 |
+---------------+-----+
20 rows in set (0.00 sec)
都是升序 可以后接DESC 降序
使用LIMIT 4，3表示隔四个取三个
MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age LIMIT 4,3;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Diao Chan | 19 |
| Ren Yingying | 20 |
| Xiao Qiao | 20 |
+--------------+-----+
3 rows in set (0.00 sec)
SELECT 执行时先执行 FROM（判断表） 再执行WHERE 而后GROUP BY 分组和HAVING过滤，若GROUP BY 和HAVING不存在则执行ORDER BY(排序) 如果排序不存在最后执行投影SELECT 最最后才LIMIT
如下事例先判断表而后条件WHERE 最后才投影
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age>30;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
+--------------+-----+
4 rows in set (0.00 sec)
join 多表查询
连接查询 事先将两张表相应的join操作，而后根据join的结果做查询
CROSS JOIN 交叉连接 笛卡尔积
INNER JOIN 内部连接
OUTER JOIN 外部连接，（左外右外） 以其中一个表为基准
NATURAL JOIN 等值连接
如果OUTER JOIN 以左表为基准则LEFT OUTER JOIN 右表没有的为空。以右表为基准
SELECT students.name, classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID AND name=’xu zhu’;
下面是等值链接
先下面是交叉链接 这种操作很危险 谨慎
下面有个表 teachers
找到 students和teachers表中学生名和对应的老师的名字。
左外连接
右外连接
子查询：查询中嵌套着查询
基于某查询语句的结果再次进行的查询
用于WHERE子句的子查询
1、用于比较表达式中的子查询
要求子查询只能返回单个结果；
2、用于IN中的子查询
判断是否存在于指定的列表中
3、用于EXISTS中子查询
SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
查找表students 中平均年龄
MariaDB [hellodb]> SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
| 25.3000 |
+----------+
1 row in set (0.37 sec)
使用子查询查找students中年龄大于平均年龄的同学
MariaDB [hellodb]> SELECT Name AS students FROM students WHERE Age > (SELECT AVG(Age) FROM students);
+--------------+
| students |
+--------------+
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Tian Boguang |
+--------------+
5 rows in set (0.00 sec)
2、用于IN中的子查询，判断是否存在于指定的列表中。
查出老师的编号使之与学生相同编号的学生的名字
MariaDB [hellodb]> SELECT Name FROM students WHERE StuID IN (SELECT TID FROM teachers);
+-------------+
| Name |
+-------------+
| Shi Zhongyu |
| Shi Potian |
| Xie Yanke |
| Ding Dian |
+-------------+
4 rows in set (0.14 sec)
3、用于EXISTS中子查询
用于FROM中的子查询：
SELECT alias.col,... FROM (SELECT statement) AS alias WHERE clause
例如：SELECT s.Name FROM (SELECT * FROM students WHERE Age > 20) AS s WHERE s.Name LIKE 's%';
联合查询：
SELECT statement UNION SELECT statement
将两外或多个返回值字段相同的查询的结果合并输出；
MariaDB [hellodb]> SELECT Name,Age FROM teachers WHERE Age >=40;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
4 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age >=40;
+-----------+-----+
| Name | Age |
+-----------+-----+
| Xie Yanke | 53 |
| Shi Qing | 46 |
+-----------+-----+
2 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age >= 40 UNION SELECT Name,Age FROM teachers WHERE Age >= 40;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Xie Yanke | 53 |
| Shi Qing | 46 |
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
6 rows in set (0.05 sec)
视图：VIEW
存储下来的SELECT语句，此语句有名称；此名称表示的对象类似表；
虚表：视图
基表：视图中的查询语句针对其进行查询的表
CREATE VIEW view_name AS SELECT statement;
DROP VIEW view_name;
MariaDB [hellodb]> CREATE VIEW stus AS SELECT StuID,Name,Age,Gender FROM students;
Query OK, 0 rows affected (0.10 sec)
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| stus |
| teachers |
| tmp1 |
| toc |
+-------------------+
9 rows in set (0.01 sec)
MariaDB [hellodb]> SELECT * FROM stus;
+-------+---------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
+-------+---------------+-----+--------+
20 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'stus'\G;
*************************** 1. row ***************************
Name: stus
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [hellodb]> DROP VIEW stus;、、删除
Query OK, 0 rows affected (0.00 sec)
EXPLAIN：
EXPLAIN SELECT Name,Age FROM students WHERE Name LIKE 's%' or Age > 28\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: index_merge
possible_keys: Name,Age
key: Name,Age
key_len: 152,1
ref: NULL
rows: 7
Extra: Using sort_union(Name,Age); Using where
MariaDB [hellodb]> SELECT Name FROM students WHERE Age > 25;
+--------------+
| Name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Tian Boguang |
+--------------+
5 rows in set (0.01 sec)
MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age > 25;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 20 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.07 sec)
MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age > 25\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE 、、select语句的类型，这里是简单查询
table: students 、、针对那张表
type: ALL 。。
possible_keys: NULL 可能会用到的索引
key: NULL 用到的索引
key_len: NULL 用到的索引的长度
ref: NULL 参考性结果
rows: 20 行数 从多少航中取得
Extra: Using where
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [hellodb]> ALTER TABLE students ADD INDEX(age); 创建索引
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age > 25\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: range
possible_keys: Age
key: Age
key_len: 1
ref: NULL
rows: 5
Extra: Using index condition
1 row in set (0.00 sec)
ERROR: No query specified
id: SELECT语句的标识符
select_type:
SIMPLE
PRIMARY
UNION
UNION RESULT：UNION的执行结果
SUBQUERY：
DERIVED：用于FROM子句中的子查询；
table:
查询语句所关系到的表的名字；
type: 访问到目标记录的方法
system: 表中仅有一行；
const: 表中至多有一行匹配；一般只有用于PRIMARY KEY或UNIQUE KEY（NOT NULL）索引时，此种结果才会出现；
eq_ref: 类似于const，表中至多有一个匹配到的行
ref:
fulltext:
ref_or_null:
index_merge:
unique_subquery: 通常出现于IN子查询中
index_subquery: 类似上一个，
range: 带有范围限制的索引；
index: 全索引扫描
ALL：全表扫描
Extra:
using where:
using index:
using index for group by:
