Mysql 笔记之SQL语句及查询语句详解
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 tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...批量插入
Mysql基于默认，
2、第二种INSERT 使用方法
INSERT tbl_name SET col_name={expr | DEFAULT}, ...通常一次插入一ci
3、第三种INSERT 使用方法
INSERT tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
例题创建表 tmp8 > MariaDB > 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 > 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: 3Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tmp8;
+----+-------------+--------+
|> +----+-------------+--------+
|1 | lin chong | m |
|2 | Wu song | m |
|3 | Sun erniang | f |
+----+-------------+--------+
3 rows in set (0.00 sec)
第一种方法
MariaDB > INSERT INTO tmp8 (name,gender)VALUES('Yang zhi','m');
Query OK, 1 row affected (0.01 sec)
MariaDB > SELECT * FROM tmp8;
+----+-------------+--------+
|> +----+-------------+--------+
|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 > INSERT INTO tmp8 SET name='Li kui',gender='m';
Query OK, 1 row affected (0.01 sec)
MariaDB > SELECT * FROM tmp8;
+----+-------------+--------+
|> +----+-------------+--------+
|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 > INSERT INTO tmp8( name,gender) VALUES('Gu dasao','f'),('Hu sanmiang','f');
Query OK, 2 rows affected (0.00 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tmp8;
+----+-------------+--------+
|> +----+-------------+--------+
|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 > 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 > INSERT INTO tmp7 VALUES(NULL,'Zhu geliang','m'),(NULL,'Hang yueying','f');
Query OK, 2 rows affected (0.01 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tmp7;
+----+--------------+--------+
|> +----+--------------+--------+
|1 | Zhu geliang| m |
|2 | Hang yueying | f |
+----+--------------+--------+
2 rows in set (0.00 sec)
第三种查找tmp8的gender=‘f’的所有人添加到tmp7中
MariaDB > 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 > INSERT INTO tmp7(name,gender) SELECT name,gender FROM tmp8 WHERE gender='f';
Query OK, 3 rows affected (0.01 sec)
Records: 3Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tmp7;
+----+---------------+--------+
|> +----+---------------+--------+
|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 > HELP UPDATE;
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
UPDATE table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
单表操作
判断某字段值为空IS NULL 不为空IS NOT NULL
把ClassID为空的行的年龄改为70
MariaDB > UPDATE students SET Age=70 WHERE>
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4Changed: 4Warnings: 0
MariaDB > SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender |> +-------+---------------+-----+--------+---------+-----------+
| 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 > SELECT * FROM students WHERE Age +-------+--------------+-----+--------+---------+-----------+
| 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 > UPDATE students SET>
Query OK, 1 row affected (0.01 sec)
Rows matched: 2Changed: 1Warnings: 0
再次查看
MariaDB > SELECT * FROM students WHERE Age +-------+--------------+-----+--------+---------+-----------+
| 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 > UPDATE students SET>
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3Changed: 3Warnings: 0
MariaDB > SELECT * FROM students WHERE Age +-------+--------------+-----+--------+---------+-----------+
| 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 > UPDATE students SET>
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2Changed: 2Warnings: 0
MariaDB > SELECT * FROM students WHERE Age +-------+--------------+-----+--------+---------+-----------+
| 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 > HELP DELETE;
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:
DELETE FROM tbl_name
Multiple-table syntax:
DELETE
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
Or:
DELETE
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
删除年龄大于60的用户
MariaDB > DELETE FROM students WHERE Age>60;
Query OK, 4 rows affected (0.01 sec)
MariaDB > SELECT * FROM students ORDER BY Age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender |> +-------+---------------+-----+--------+---------+-----------+
| 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 > DELETE FROM students ORDER BY Age LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
MariaDB > SELECT * FROM students ORDER BY Age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender |> +-------+---------------+-----+--------+---------+-----------+
| 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 > 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 > SELECT @@GLOBAL.query_cache_size;
+---------------------------+
| @@GLOBAL.query_cache_size |
+---------------------------+
| 16777216 |
+---------------------------+
1 row in set (0.00 sec)
查询当前时间
MariaDB > SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 18:25:45 |
+----------------+
1 row in set (0.00 sec)
或者
MariaDB > SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-08-30 18:26:54 |
+---------------------+
1 row in set (0.00 sec)
MariaDB > 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 > 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 > 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 > 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 /////// ISNOTNULL
LIKE 可以使用通配符
RLIKE 可使用正则表达式
事例
MariaDB > 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 > SELECT Name,Age FROM students WHERE Name RLIKE '^s.*u$';
+-------------+-----+
| Name | Age |
+-------------+-----+
| Shi Zhongyu |22 |
+-------------+-----+
1 row in set (0.00 sec)
逻辑操作符andor not
GROUP BY 做分组
MariaDB > SELECT>
+---------+----------+
|> +---------+----------+
| 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 > SELECT>
+---------+----------+
|> +---------+----------+
| 2 |36.0000 |
| 3 |26.0000 |
| 5 |28.0000 |
+---------+----------+
3 rows in set (0.00 sec)
MariaDB > SELECT>
GROUP BY :做聚合计算是使用，表示根据指定的字段，对符合条件的行做分组，而后对每分组做聚合计算； 聚合计算AVG()平局数；SUM()求和；MAX()最大； MIN() COUNT()统计个数。
HAVING: 对聚合计数做过滤。
ORDER BY 指定字段排序可以有多个。
MariaDB > SELECT>
+---------+----------------+
|> +---------+----------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 1 |
| 4 | 4 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
+---------+----------------+
7 rows in set (0.02 sec)
MariaDB > SELECT>
+---------+----------+
|> +---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 26 |
| 4 | 99 |
| 5 | 84 |
| 6 | 65 |
| 7 | 42 |
+---------+----------+
7 rows in set (0.02 sec)
使用HAVING
MariaDB > SELECT>
+---------+----------+
|> +---------+----------+
| 1 | 82 |
| 2 | 108 |
| 4 | 99 |
| 5 | 84 |
+---------+----------+
4 rows in set (0.01 sec)
按名字排序
MariaDB > 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 > 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 > 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 > 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,> 下面是等值链接
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M00/54/32/wKioL1R77rOBCGkwAACMYqjHJjM632.jpg
先下面是交叉链接这种操作很危险谨慎
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M00/54/34/wKiom1R77ivg76vVAAFjaZ3UjMs766.jpg
下面有个表 teachers
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M00/54/32/wKioL1R77rTjx3HlAACIfTDqSXU688.jpg
找到 students和teachers表中学生名和对应的老师的名字。
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M01/54/32/wKioL1R77rSTEQ0QAACzf6amM-o859.jpg
左外连接
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M01/54/34/wKiom1R77iuis-KhAAD8-4zsqCI005.jpg
右外连接
http://home.51cto.com/thumb.php?w=600&h=600&t=f&url=http://s3.51cto.com/wyfs02/M02/54/32/wKioL1R77rTjNz7UAACdB5QQVlk708.jpg
子查询：查询中嵌套着查询
基于某查询语句的结果再次进行的查询
用于WHERE子句的子查询
1、用于比较表达式中的子查询
要求子查询只能返回单个结果；
2、用于IN中的子查询
判断是否存在于指定的列表中
3、用于EXISTS中子查询
SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
查找表students 中平均年龄
MariaDB > SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
|25.3000 |
+----------+
1 row in set (0.37 sec)
使用子查询查找students中年龄大于平均年龄的同学
MariaDB > 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 > 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 > 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 > SELECT Name,Age FROM students WHERE Age >=40;
+-----------+-----+
| Name | Age |
+-----------+-----+
| Xie Yanke |53 |
| Shi Qing|46 |
+-----------+-----+
2 rows in set (0.00 sec)
MariaDB > 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 > CREATE VIEW stus AS SELECT StuID,Name,Age,Gender FROMstudents;
Query OK, 0 rows affected (0.10 sec)
MariaDB > SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
|> | coc |
| courses |
| scores |
| students |
| stus |
| teachers |
| tmp1 |
| toc |
+-------------------+
9 rows in set (0.01 sec)
MariaDB > 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 > 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 > 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 ***************************
> 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 > 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 > EXPLAIN SELECT Name FROM students WHERE Age > 25;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|> +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL| NULL | NULL | NULL | NULL | 20 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.07 sec)
MariaDB > EXPLAIN SELECT Name FROM students WHERE Age > 25\G;
*************************** 1. row ***************************
> 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 >>创建索引
Query OK, 0 rows affected (0.26 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > EXPLAIN SELECT Name FROM students WHERE Age > 25\G;
*************************** 1. row ***************************
> 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
> 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:
页:
[1]