SQL语句简介及练习

renran421

　　SQL语言的兴起与语法标准
　　20世纪70年代，IBM开发出SQL，用于DB2
　　1981年，IBM推出SQL/DS数据库
　　业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
　　SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
　　SQL：ANSI SQL
　　SQL-86, SQL-89, SQL-92, SQL-99, SQL-03
　　SQL语言规范
　　在数据库系统中，SQL语句不区分大小写(建议用大写)
　　但字符串常量区分大小写
　　SQL语句可单行或多行书写，以“;”结尾
　　关键词不能跨多行或简写
　　用空格和缩进来提高语句的可读性
　　子句通常位于独立行，便于编辑，提高可读性
　　注释：
　　SQL标准：
　　/*注释内容*/ 多行注释
　　-- 注释内容 单行注释，注意有空格
　　MySQL注释：
　　除了SQL标准外还可以用#
　　数据库对象
　　数据库的组件(对象)：
　　数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
　　命名规则：
　　必须以字母开头
　　可包括数字和三个特殊字符（# _ $，#和$尽量别用）
　　不要使用MySQL的保留字
　　同一database(Schema)下的对象不能同名，即使是不同类型的对象
　　SQL语句分类
　　SQL语句分类：
　　DDL: Data Defination Language

　　CREATE, DROP,>　　DML: Data Manipulation Language
　　INSERT, DELETE, UPDATE
　　DCL：Data Control Language
　　GRANT, REVOKE
　　DQL：Data Query Language
　　SELECT
　　SQL语句构成
　　SQL语句构成：
　　Keyword组成clause（子句）
　　多条clause组成语句
　　示例：
　　SELECT * SELECT子句
　　FROM products FROM子句
　　WHERE price>400 WHERE子句
　　说明：此为一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字
　　数据库操作
　　创建数据库：
　　CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; #创建数据库
　　CHARACTER SET 'character set name' #指定字符集
　　COLLATE 'collate name' #指定排序方式
　　删除数据库
　　DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
　　查看支持所有字符集：SHOW CHARACTER SET;
　　查看支持所有排序规则：SHOW COLLATION;
　　获取命令使用帮助：
　　mysql> HELP KEYWORD;
　　例：HELP CREATE DATABASE;
　　看到基本用法及帮助文档的链接，可以去看
　　URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html
　　查看数据库列表：
　　mysql> SHOW DATABASES;
　　[root@centos7 ~]#cat /var/lib/mysql/db1/db.opt
　　default-character-set=latin1
　　default-collation=latin1_swedish_ci
　　表
　　表：二维关系
　　设计表：遵循规范
　　定义：字段，索引
　　字段：字段名，字段数据类型，修改符
　　约束，索引：应该创建在经常用作查询条件的字段上
　　创建表
　　创建表：CREATE TABLE
　　(1) 直接创建
　　(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]
　　[partition_options] select_statement
　　(3) 通过复制现存的表的表结构创建，但不复制数据
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
　　注意：
　　Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
　　同一个库中表建议要使用同一种存储引擎类型
　　创建表
　　CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...) #[IF NOT EXISTS]字段在脚本中一般要加
　　字段信息
　　·col type1
　　·PRIMARY KEY(col1,...)
　　·INDEX(col1, ...)
　　·UNIQUE KEY(col1, ...)
　　表选项：
　　·ENGINE [=] engine_name
　　SHOW ENGINES;查看支持的engine类型
　　·ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
　　获取帮助：mysql> HELP CREATE TABLE;
　　表操作
　　查看所有的引擎：SHOW ENGINES
　　查看表：SHOW TABLES [FROM db_name]
　　查看表结构：DESC [db_name.]tb_name
　　删除表：DROP TABLE [IF EXISTS] tb_name
　　查看表创建命令：SHOW CREATE TABLE tbl_name
　　查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'
　　查看库中所有表状态：SHOW TABLE STATUS FROM db_name
　　数据类型
　　数据类型：
　　数据长什么样？
　　数据需要多少空间来存放？
　　系统内置数据类型和用户定义数据类型
　　MySql支持多种列类型：
　　数值类型
　　日期/时间类型
　　字符串(字符)类型
　　https://dev.mysql.com/doc/refman/5.5/en/data-types.html
　　选择正确的数据类型对于获得高性能至关重要，三大原则：
　　更小的通常更好，尽量使用可正确存储数据的最小数据类型
　　简单就好，简单数据类型的操作通常需要更少的CPU周期
　　尽量避免NULL，包含为NULL的列，对MySQL更难优化（可填个默认值）
　　数据类型
　　
　　
　　数据类型
　　1、整型
　　tinyint(m) 1个字节 范围(-128~127)
　　smallint(m) 2个字节 范围(-32768~32767)
　　mediumint(m) 3个字节 范围(-8388608~8388607)
　　int(m) 4个字节 范围(-2147483648~2147483647)
　　bigint(m) 8个字节 范围(+-9.22*10的18次方)
　　取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)
　　int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
　　BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假。非zero值视为真
　　2、浮点型(float和double)，近似值
　　float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
　　double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
　　设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
　　3、定点数
　　在数据库中存放的是精确值,存为十进制
　　decimal(m,d) 参数m　　修改表示例
ALTER TABLE students RENAME s1;　　
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
　　
ALTER TABLE s1 MODIFY phone int;
　　
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
　　
ALTER TABLE s1 DROP COLUMN mobile;
　　
Help ALTER TABLE 查看帮助
　　修改表示例
ALTER TABLE students ADD gender ENUM('m','f')（enum单选/枚举，set多选）　　
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
　　
ALTER TABLE students ADD UNIQUE KEY(name);
　　
ALTER TABLE students ADD INDEX(age);
　　
DESC students;
　　
SHOW INDEXES FROM students;
　　
ALTER TABLE students DROP age;
　　DML语句
　　DML:INSERT, DELETE, UPDATE, SELECT
　　INSERT：
　　一次插入一行或多行数据
　　语法
　　INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
　　[INTO] tbl_name [(col_name,...)]
　　{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
　　[ ON DUPLICATE KEY UPDATE 如果重复更新之
　　col_name=expr
　　[, col_name=expr] ... ]
　　简化写法：
　　INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
　　DML语句
　　INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
　　[INTO] tbl_name
　　SET col_name={expr | DEFAULT}, ...
　　[ ON DUPLICATE KEY UPDATE
　　col_name=expr
　　[, col_name=expr] ... ]
　　INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
　　[INTO] tbl_name [(col_name,...)]
　　SELECT ...
　　[ ON DUPLICATE KEY UPDATE
　　col_name=expr
　　[, col_name=expr] ... ]
　　DML语句
　　UPDATE：
　　UPDATE [LOW_PRIORITY] [IGNORE] table_reference
　　SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
　　[WHERE where_condition]
　　[ORDER BY ...]
　　[LIMIT row_count]
　　注意：一定要有限制条件，否则将修改所有行的指定字段
　　限制条件：
　　WHERE
　　LIMIT
　　Mysql 选项：--safe-updates| --i-am-a-dummy|-U
　　DML语句
　　DELETE:
　　DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
　　[WHERE where_condition]
　　[ORDER BY ...]
　　[LIMIT row_count]
　　可先排序再指定删除的行数
　　注意：一定要有限制条件，否则将清空表中的所有数据
　　限制条件：
　　WHERE
　　LIMIT
　　TRUNCATE TABLE tbl_name; 清空表
　　DQL语句
　　SELECT
　　[ALL | DISTINCT | DISTINCTROW ]
　　[SQL_CACHE | SQL_NO_CACHE]
　　select_expr [, select_expr ...]
　　[FROM table_references
　　[WHERE where_condition]
　　[GROUP BY {col_name | expr | position}
　　[ASC | DESC], ... [WITH ROLLUP]]
　　[HAVING where_condition]
　　[ORDER BY {col_name | expr | position}
　　[ASC | DESC], ...]
　　[LIMIT {[offset,] row_count | row_count OFFSET offset}]
　　[FOR UPDATE | LOCK IN SHARE MODE]
　　SELECT
　　字段显示可以使用别名：
　　col1 AS alias1, col2 AS alias2, ...（AS可不写）
　　WHERE子句：指明过滤条件以实现“选择”的功能：
　　过滤条件：布尔型表达式
　　算术操作符：+, -, *, /, %
　　比较操作符：=, !=, , , >=,  select * from students order by -score desc; 顺序，但NULL在后
　　LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
　　对查询结果中的数据请求施加“锁”
　　FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
　　LOCK IN SHARE MODE: 读锁，共享锁，同时多个读（但只能有一个写）
　　示例
DESC students;　　
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
　　
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
　　
SELECT * FROM students WHERE id < 3;
　　
SELECT * FROM students WHERE gender='m';
　　
SELECT * FROM students WHERE gender IS NULL;
　　
SELECT * FROM students WHERE gender IS NOT NULL;
　　
SELECT * FROM students ORDER BY name DESC LIMIT 2;
　　
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
　　
SELECT * FROM students WHERE id >=2 and id  use hellodb  #使用hellodb数据库
　　
MariaDB [hellodb]> show tables;  #共有7张表
　　+-------------------+
　　| Tables_in_hellodb |
　　+-------------------+

　　|>　　| coc               |
　　| courses           |
　　| scores            |
　　| students          |
　　| teachers          |
　　| toc               |
　　+-------------------+
MariaDB [hellodb]> 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       |  27 | M      |    NULL |      NULL |
　　|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
　　+-------+---------------+-----+--------+---------+-----------+
MariaDB [hellodb]> select * from teachers;  #老师表　　+-----+---------------+-----+--------+
　　| TID | Name          | Age | Gender |
　　+-----+---------------+-----+--------+
　　|   1 | Song Jiang    |  45 | M      |
　　|   2 | Zhang Sanfeng |  94 | M      |
　　|   3 | Miejue Shitai |  77 | F      |
　　|   4 | Lin Chaoying  |  93 | F      |
　　+-----+---------------+-----+--------+
MariaDB [hellodb]> select * from classes;  #班级表　　+---------+----------------+----------+

　　|>　　+---------+----------------+----------+
　　|       1 | Shaolin Pai    |       10 |
　　|       2 | Emei Pai       |        7 |
　　|       3 | QingCheng Pai  |       11 |
　　|       4 | Wudang Pai     |       12 |
　　|       5 | Riyue Shenjiao |       31 |
　　|       6 | Lianshan Pai   |       27 |
　　|       7 | Ming Jiao      |       27 |
　　|       8 | Xiaoyao Pai    |       15 |
　　+---------+----------------+----------+
MariaDB [hellodb]> select * from courses;  #课程表　　+----------+----------------+
　　| CourseID | Course         |
　　+----------+----------------+
　　|        1 | Hamo Gong      |
　　|        2 | Kuihua Baodian |
　　|        3 | Jinshe Jianfa  |
　　|        4 | Taiji Quan     |
　　|        5 | Daiyu Zanghua  |
　　|        6 | Weituo Zhang   |
　　|        7 | Dagou Bangfa   |
　　+----------+----------------+
MariaDB [hellodb]> select * from coc;  #班级选课表　　+----+---------+----------+

　　|>　　+----+---------+----------+
　　|  1 |       1 |        2 |
　　|  2 |       1 |        5 |
　　|  3 |       2 |        2 |
　　|  4 |       2 |        6 |
　　|  5 |       3 |        1 |
　　|  6 |       3 |        7 |
　　|  7 |       4 |        5 |
　　|  8 |       4 |        2 |
　　|  9 |       5 |        1 |
　　| 10 |       5 |        9 |
　　| 11 |       6 |        3 |
　　| 12 |       6 |        4 |
　　| 13 |       7 |        4 |
　　| 14 |       7 |        3 |
　　+----+---------+----------+
MariaDB [hellodb]> select * from scores;  #分数表　　+----+-------+----------+-------+

　　|>　　+----+-------+----------+-------+
　　|  1 |     1 |        2 |    77 |
　　|  2 |     1 |        6 |    93 |
　　|  3 |     2 |        2 |    47 |
　　|  4 |     2 |        5 |    97 |
　　|  5 |     3 |        2 |    88 |
　　|  6 |     3 |        6 |    75 |
　　|  7 |     4 |        5 |    71 |
　　|  8 |     4 |        2 |    89 |
　　|  9 |     5 |        1 |    39 |
　　| 10 |     5 |        7 |    63 |
　　| 11 |     6 |        1 |    96 |
　　| 12 |     7 |        1 |    86 |
　　| 13 |     7 |        7 |    83 |
　　| 14 |     8 |        4 |    57 |
　　| 15 |     8 |        3 |    93 |
　　+----+-------+----------+-------+
MariaDB [hellodb]> select * from toc;  #空表　　Empty set (0.04 sec)
　　以下是练习题：
　　(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> SELECT name,age FROM students WHERE age>25 AND gender='M';　　+--------------+-----+
　　| name         | age |
　　+--------------+-----+
　　| Xie Yanke    |  53 |
　　| Ding Dian    |  32 |
　　| Yu Yutong    |  26 |
　　| Shi Qing     |  46 |
　　| Tian Boguang |  33 |
　　| Xu Xian      |  27 |
　　| Sun Dasheng  | 100 |
　　+--------------+-----+
　　(2) 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> SELECT classid,avg(age) FROM students GROUP BY classid;　　+---------+----------+

　　|>　　+---------+----------+
　　|    NULL |  63.5000 |
　　|       1 |  20.5000 |
　　|       2 |  36.0000 |
　　|       3 |  20.2500 |
　　|       4 |  24.7500 |
　　|       5 |  46.0000 |
　　|       6 |  20.7500 |
　　|       7 |  19.6667 |
　　+---------+----------+
　　(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> SELECT classid,avg(age) FROM students　　
GROUP BY classid HAVING avg(age)>30;
　　+---------+----------+

　　|>　　+---------+----------+
　　|    NULL |  63.5000 |
　　|       2 |  36.0000 |
　　|       5 |  46.0000 |
　　+---------+----------+
　　(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE name like 'L%';　　+-------+-------------+-----+--------+---------+-----------+

　　| StuID | Name        | Age | Gender |>　　+-------+-------------+-----+--------+---------+-----------+
　　|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
　　|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
　　|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
　　+-------+-------------+-----+--------+---------+-----------+
　　(5) 显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> SELECT * FROM students WHERE teacherid is not null;　　+-------+-------------+-----+--------+---------+-----------+

　　| 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) 以年龄排序后，显示年龄最大的前10位同学的信息
MariaDB [hellodb]> SELECT * FROM students ORDER BY -age LIMIT 10;　　+-------+--------------+-----+--------+---------+-----------+

　　| StuID | Name         | Age | Gender |>　　+-------+--------------+-----+--------+---------+-----------+
　　|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
　　|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
　　|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
　　|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
　　|     4 | Ding Dian    |  32 | M      |       4 |         4 |
　　|    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
　　|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
　　|    17 | Lin Chong    |  25 | M      |       4 |      NULL |
　　|    23 | Ma Chao      |  23 | M      |       4 |      NULL |
　　|    18 | Hua Rong     |  23 | M      |       7 |      NULL |
　　+-------+--------------+-----+--------+---------+-----------+
　　(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE age >=20 and age 　　+-------+---------------+-----+--------+---------+-----------+
　　|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
　　|     2 | Shi Potian    |  22 | M      |       1 |         7 |
　　|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
　　|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
　　|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
　　|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
　　|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
　　|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
　　|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
　　|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
　　+-------+---------------+-----+--------+---------+-----------+
　　SQL JOINS
　　表连接图示

　　多表查询
　　交叉连接：笛卡尔乘积
　　内连接：
　　等值连接：让表之间的字段以“等值”建立连接关系；
　　不等值连接
　　自然连接:去掉重复列的等值连接
　　自连接
　　外连接：
　　左外连接：
　　FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
　　右外连接
　　FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
　　#要求掌握内连接，左外连接，右外连接
　　示例：
　　由于输出结果太长，就不贴上来了，感兴趣的可以试试体会一下
　　#内连接INNER JOIN
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from teachers as t inner join students as s on s.teacherid=t.tid;
　　#交叉连接，笛卡尔乘积CROSS JOIN
MariaDB [hellodb]> select * from students cross join teachers;　　#左外连接LEFT OUTER JOIN
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from students as s left outer join teachers as t on s.teacherid=t.tid;
　　#对调过来左外连接
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from teachers as t right outer join students as s on s.teacherid=t.tid;
　　#右外连接
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from students as s right outer join teachers as t on s.teacherid=t.tid;
　　#左外连接去除交集
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from students as s left outer join teachers as t
　　
on s.teacherid=t.tid and t.name is null;
　　#因MySQL不支持FULL OUTER JOIN，这里用UNION联合查询
MariaDB [hellodb]> select stuid,name from students union　　
select tid,name from teachers;
　　#完全外连接，左外连接union右外连接即可
MariaDB [hellodb]> select s.name as studentname,t.name as teachername　　
from students as s left outer join teachers as t on s.teacherid=t.tid
　　
UNION select s.name as studentname,t.name as teachername from
　　
students as s right outer join teachers as t on s.teacherid=t.tid;
　　#完全外连接去除交集
MariaDB [hellodb]> SELECT * FROM (select s.name as studentname,　　
t.name as teachername from students as s left outer join teachers as t
　　
on s.teacherid=t.tid UNION select s.name as studentname,t.name as teachername
　　
from students as s right outer join teachers as t on s.teacherid=t.tid)
　　
as aWHERE studentname is null or teachername is null;
　　#自内连接
MariaDB [hellodb]> select s1.name as emp, s2.name as leader from students　　
as s1 inner join students as s2 on s1.teacherid=s2.stuid;
　　#自左外连接
MariaDB [hellodb]> select s1.name as emp, s2.name as leader from students　　
as s1 left outer join students as s2 on s1.teacherid=s2.stuid;
　　多表查询
　　子查询：在查询语句嵌套着查询语句，性能较差
　　基于某语句的查询结果再次进行的查询
　　例：MariaDB [hellodb]> select st.name,sc.score from students as st inner join scores
　　as sc on st.stuid=sc.stuid and score > ( select avg(score) from scores); #成绩大于平均成绩的学生的姓名和分数
　　用在WHERE子句中的子查询：
　　用于比较表达式中的子查询；子查询仅能返回单个值
　　SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
　　用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
　　SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
　　用于EXISTS
　　多表查询
　　用于FROM子句中的子查询
　　使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
　　示例：

　　SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE>　　联合查询：UNION
　　SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
　　#联合查询的字段数和字段类型必须一致
　　SELECT语句执行流程

　　练习
　　导入hellodb_innodb.sql，以下操作在students表上执行
　　1、以ClassID分组，显示每班的同学的人数
MariaDB [hellodb]> SELECT classid,count(stuid) FROM students GROUP BY classid;　　+---------+--------------+

　　|>　　+---------+--------------+
　　|    NULL |            2 |
　　|       1 |            4 |
　　|       2 |            3 |
　　|       3 |            4 |
　　|       4 |            4 |
　　|       5 |            1 |
　　|       6 |            4 |
　　|       7 |            3 |
　　+---------+--------------+
　　2、以Gender分组，显示其年龄之和
MariaDB [hellodb]> SELECT gender,sum(age) FROM students GROUP BY gender;　　+--------+----------+
　　| gender | sum(age) |
　　+--------+----------+
　　| F      |      190 |
　　| M      |      495 |
　　+--------+----------+
　　3、以ClassID分组，显示其平均年龄大于25的班级
MariaDB [hellodb]> SELECT classid,sum(age) FROM students　　
GROUP BY classid HAVING avg(age)>25;
　　+---------+----------+

　　|>　　+---------+----------+
　　|    NULL |      127 |
　　|       2 |      108 |
　　|       5 |       46 |
　　+---------+----------+
　　4、以Gender分组，显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> SELECT gender,sum(age) FROM students　　
WHERE age>25 GROUP BY gender;
　　+--------+----------+
　　| gender | sum(age) |
　　+--------+----------+
　　| M      |      317 |
　　+--------+----------+
　　5、显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> SELECT st.name,co.course,sc.score FROM (SELECT *　　
FROM students WHERE stuid  SELECT st.name,co.course FROM students st　　
INNER JOIN scores sc INNER JOIN courses co ON st.stuid=sc.stuid
　　
AND sc.courseid=co.courseid AND sc.score>80;
　　+-------------+----------------+
　　| name        | course         |
　　+-------------+----------------+
　　| Shi Zhongyu | Weituo Zhang   |
　　| Shi Potian  | Daiyu Zanghua  |
　　| Xie Yanke   | Kuihua Baodian |
　　| Ding Dian   | Kuihua Baodian |
　　| Shi Qing    | Hamo Gong      |
　　| Xi Ren      | Hamo Gong      |
　　| Xi Ren      | Dagou Bangfa   |
　　| Lin Daiyu   | Jinshe Jianfa  |
　　+-------------+----------------+
　　7、求前8位同学每位同学自己两门课的平均成绩，并按降序排列
MariaDB [hellodb]> SELECT st.name,avg(sc.score) as avg_score　　
FROM students st INNER JOIN scores sc ON st.stuid=sc.stuid
　　
GROUP BY name ORDER BY avg_score desc;
　　+-------------+-----------+
　　| name        | avg_score |
　　+-------------+-----------+
　　| Shi Qing    |   96.0000 |
　　| Shi Zhongyu |   85.0000 |
　　| Xi Ren      |   84.5000 |
　　| Xie Yanke   |   81.5000 |
　　| Ding Dian   |   80.0000 |
　　| Lin Daiyu   |   75.0000 |
　　| Shi Potian  |   72.0000 |
　　| Yu Yutong   |   51.0000 |
　　+-------------+-----------+
　　8、显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> SELECT co.course,count(stuid) FROM students st　　
INNER JOIN courses co INNER JOIN coc ON st.classid=coc.classid
　　
AND coc.courseid=co.courseid GROUP BY co.course;
　　+----------------+--------------+
　　| course         | count(stuid) |
　　+----------------+--------------+
　　| Dagou Bangfa   |            4 |
　　| Daiyu Zanghua  |            8 |
　　| Hamo Gong      |            5 |
　　| Jinshe Jianfa  |            7 |
　　| Kuihua Baodian |           11 |
　　| Taiji Quan     |            7 |
　　| Weituo Zhang   |            3 |
　　+----------------+--------------+
　　9、如何显示其年龄大于平均年龄的同学的名字
MariaDB [hellodb]> SELECT name FROM students WHERE age>(SELECT　　
avg(age) FROM students);
　　+--------------+
　　| name         |
　　+--------------+
　　| Xie Yanke    |
　　| Ding Dian    |
　　| Shi Qing     |
　　| Tian Boguang |
　　| Sun Dasheng  |
　　+--------------+
　　10、如何显示其学习的课程为第1、2，4或第7门课的同学的名字
MariaDB [hellodb]> SELECT s.name FROM students AS s INNER JOIN　　
(SELECT * FROM coc WHERE courseid IN (1,2,4,7)) AS co ON
　　
s.classid=co.classid GROUP BY s.name;
　　+---------------+
　　| name          |
　　+---------------+
　　| Diao Chan     |
　　| Ding Dian     |
　　| Duan Yu       |
　　| Hua Rong      |
　　| Huang Yueying |
　　| Lin Chong     |
　　| Lin Daiyu     |
　　| Lu Wushuang   |
　　| Ma Chao       |
　　| Ren Yingying  |
　　| Shi Potian    |
　　| Shi Qing      |
　　| Shi Zhongyu   |
　　| Tian Boguang  |
　　| Wen Qingqing  |
　　| Xi Ren        |
　　| Xiao Qiao     |
　　| Xie Yanke     |
　　| Xu Zhu        |
　　| Xue Baochai   |
　　| Yu Yutong     |
　　| Yuan Chengzhi |
　　| Yue Lingshan  |
　　+---------------+
　　23 rows in set (0.01 sec)
　　11、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> SELECT s.name,s.age,s.classid FROM students s　　
INNER JOIN (SELECT avg(age) as avg_age,classid FROM students
　　
GROUP BY classid HAVING count(stuid)>=3) AS a ON s.classid=a.classid
　　
WHERE age>avg_age;
　　+---------------+-----+---------+

　　| name          | age |>　　+---------------+-----+---------+
　　| Shi Potian    |  22 |       1 |
　　| Xie Yanke     |  53 |       2 |
　　| Ding Dian     |  32 |       4 |
　　| Yu Yutong     |  26 |       3 |
　　| Yuan Chengzhi |  23 |       6 |
　　| Xu Zhu        |  21 |       1 |
　　| Lin Chong     |  25 |       4 |
　　| Hua Rong      |  23 |       7 |
　　| Huang Yueying |  22 |       6 |
　　+---------------+-----+---------+
　　12、统计各班级中年龄大于全校同学平均年龄的同学
MariaDB [hellodb]> SELECT name,age,classid FROM students　　
WHERE age >(SELECT avg(age) FROM students);
　　+--------------+-----+---------+

　　| name         | age |>　　+--------------+-----+---------+
　　| Xie Yanke    |  53 |       2 |
　　| Ding Dian    |  32 |       4 |
　　| Shi Qing     |  46 |       5 |
　　| Tian Boguang |  33 |       2 |
　　| Sun Dasheng  | 100 |    NULL |
　　+--------------+-----+---------+
　　视图
　　视图：VIEW，虚表，保存有实表的查询结果
　　创建方法：
　　CREATE VIEW view_name [(column_list)]
　　AS select_statement
　　[WITH [CASCADED | LOCAL] CHECK OPTION]
　　查看视图定义：SHOW CREATE VIEW view_name
　　删除视图：
　　DROP VIEW [IF EXISTS]
　　view_name [, view_name] ...
　　[RESTRICT | CASCADE]
　　视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制
　　视图是虚拟的，相当于一个别名
　　物化视图：把视图生成为一个实体表，MySQL不支持，某些别的数据库支持
　　例：
　　创建视图：MariaDB [hellodb]> create view view_students as select stuid,name from students;
　　查看视图信息：MariaDB [hellodb]> show table status like 'view_students'\G
　　查看视图定义：MariaDB [hellodb]> show create view view_students\G