Mysql-SQL基础应用

1 SQL介绍

  1. SQL是一门结构化的查询语言，关系型数据库通用的语言。遵循SQL92的标准(SQL_MODE)

2 SQL常用种类

  1. DDL：数据定义语言
  2. DCL：数据控制语言
  3. DML：数据操作语言
  4. DQL：数据查询语言

3 数据类型、表属性、字符集

3.1 数据类型

3.1.1 作用

  1. 保证数据的准确性和标准型

3.2.1 种类

3.2.1.1 数据类型

image-20210418154655544.png

  1. 经常使用到的整型类型：
  2. TINYINT：-128~127
  3. INT：-2^31~2~31-1
  4. 说明：手机号码是无法存储到int的，所以我们一般存手机号都是用char来存储手机号。

3.2.1.2 字符类型

image-20210418155034477.png

  1. char(11)：
  2. 定长的字符串类型，在存储字符串时，最大字符串长度为11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
  4. varchar(11)：
  5. 变长的字符串类型，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间
  7. enum("male","fmale")：
  8. 枚举类型，比较适合于将来此列的值是固定范围内的，比如二选一这种的，可以使用enum，可以很大程度的优化我们的索引结构

3.2.1.3 时间类型

image-20210418160012236.png

  1. 常用的：
  2. datetime 
  3. 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
  5. timestamp
  6. 范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999

3.2.1.4 二进制类型

image-20210418181431770.png

3.2 表属性

3.2.1 列属性

  1. 约束(一般建表时添加):
  2. primary key：主键约束
  3. 设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
  5. not null：非空约束
  6. 列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
  8. unique key：唯一键
  9. 列值不能重复
  11. unsigned：无符号
  12. 针对数字列，非负数。
  14. 其他属性:
  15. key:索引
  16. 可以在某列上建立索引，来优化查询,一般是根据需要后添加
  18. default：默认值
  19. 列中，没有录入值时，会自动使用default的值填充
  21. auto_increment：自增长
  22. 针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
  24. comment: 注释

3.2.2 表的属性

  1. 存储引擎:
  2. InnoDB（默认的）
  3. 字符集和排序规则:
  4. utf8       
  5. utf8mb4

3.3 Mysql字符集详解

3.3.1 字符集和校验规则

字符集是一套符合和编码，校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。MySQL可以使用对种字符集和检验规则来组织字符。

Mysql服务器可以支持多种的字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，Mysql明显存在更大的灵活性。

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，并且每个校对规则只是针对某个字符集，和其他的字符集么有关系。

在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。

3.3.2 查看Mysql字符集方法

  1. 1、查看Mysql服务器支持的字符集
  2. 第一种方法：
  3. mysql> show character set;
  5. 第二种方法：
  6. mysql> select * from information_schema.character_sets;
  8. 2、查看当前数据库的字符集
  9. mysql> show variables like "character%";
  10. +--------------------------+-----------------------------------------+
  11. | Variable_name            | Value                                   |
  12. +--------------------------+-----------------------------------------+
  13. | character_set_client     | utf8                                    |
  14. | character_set_connection | utf8                                    |
  15. | character_set_database   | latin1                                  |
  16. | character_set_filesystem | binary                                  |
  17. | character_set_results    | utf8                                    |
  18. | character_set_server     | latin1                                  |
  19. | character_set_system     | utf8                                    |
  20. | character_sets_dir       | /usr/local/mysql-5.7.31/share/charsets/ |
  21. +--------------------------+-----------------------------------------+
  22. 名词解释：
  23. character_set_client：客户端请求数据的字符集
  24. character_set_connection：客户机/服务器连接的字符集
  25. character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
  27. character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的
  28. character_set_results：结果集，返回给客户端的字符集
  29. character_set_server：数据库服务器的默认字符集
  30. character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

3.3.3 查看当前数据库的校对规则

  1. mysql> show variables like 'collation%';
  2. +----------------------+-------------------+
  3. | Variable_name        | Value             |
  4. +----------------------+-------------------+
  5. | collation_connection | utf8_general_ci   |
  6. | collation_database   | latin1_swedish_ci |
  7. | collation_server     | latin1_swedish_ci |
  8. +----------------------+-------------------+
  9. 3 rows in set (0.00 sec)
  11. 名词解释：
  12. collation_connection 当前连接的字符集。
  13. collation_database    当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。
  14. collation_server 服务器的默认校对。
  16. 排序方式的命名规则为：字符集名字_语言_后缀，其中各个典型后缀的含义如下：
  17. 1、_ci：不区分大小写的排序方式
  18. 2、_cs：区分大小写的排序方式
  19. 3、_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

3.3.4 设定字符集的注意事项

  • my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用mysql库的应用程序产生任何作用
  • 对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响
  • SQL语句中的裸字符会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全不同的结果

3.3.5 默认情况下字符集选择规则

  • 编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1
  • 安装MySQL 时，可以在配置文件 (my.cnf) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的
  • 启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;
  • 当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server
  • 当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集
  • 在这个数据库里创建一张表时，表默认的字符集被设定为character_set_database，也就是这个数据库默认的字符集
  • 当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集

字符集更加详细的可以参照：https://www.cnblogs.com/wcwen1990/p/6917109.html

3.4 数据库相关操作

  1. #1、创建数据库
  2. mysql> create database school;
  4. #创建数据库并指定字符集
  5. mysql> create database test charset utf8;
  7. 建库规范：
  8. 1.库名不能有大写字母
  9. 2.建库要加字符集
  10. 3.库名不能有数字开头
  11. 4.库名要和业务相关
  13. #2、删除（生产中禁止使用）
  14. mysql> drop database test;
  16. #3、修改数据库字符集
  17.  mysql> alter database school charset utf8
  18.  注意：修改字符集，修改后的字符集一定是原字符集的严格超集
  20.  #4、查询库相关信息
  21.  mysql> show create database school； #查询数据库相关信息
  22.  mysql> show databases;  #查询有几个数据库

3.5 数据库表的定义

  1. #1、创建一个表
  2. create table stu(
  3. 列1 属性(数据类型、约束、其他属性)
  4. 列2 属性,
  5. 列3 属性
  6. )
  8. #2、建表
  9. USE school;
  10. CREATE TABLE stu(
  11. id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  12. sname   VARCHAR(255) NOT NULL COMMENT '姓名',
  13. sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
  14. sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
  15. sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
  16. intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
  17. ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
  19. 建表规范：
  20. 1. 表名小写字母,不能数字开头,
  21. 2. 不能是保留字符,使用和业务有关的表名
  22. 3. 选择合适的数据类型及长度
  23. 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
  24. 5. 每个列设置注释
  25. 6. 表必须设置存储引擎和字符集
  26. 7. 主键列尽量是无关列数字列,最好是自增长
  27. 8. enum类型不要保存数字,只能是字符串类型
  1. #1、查看建表信息
  2. show tables;
  3. show create table stu;
  4. desc stu;
  6. #2、创建一个表结构一样的表
  7. create table test like stu;
  9. #3、创建一个数据、表结构一样的表
  10. create table new_test select * from test;
  12. #4、创建一个和旧表一样的结构，但是不要数据
  13. create table city_bak select * from city where 1 < 0; 
  15. #5、删表(不代表生产操作)
  16. drop table test;
  18. #6、修改
  19. #6.1 在stu表中添加QQ列
  20. desc stu;
  21. alter table stu add qq varchar(20) not null comment 'qq号';
  23. #6.2在sname后加微信列
  24. ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
  26. #6.3 在第一列ID前加一个新列num
  27. ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST;
  29. #6.4 把刚才添加的列都删掉（危险行为，谨慎操作）
  30. ALTER TABLE stu DROP num;
  31. DESC stu;
  32. ALTER TABLE stu DROP qq;
  33. ALTER TABLE stu DROP wechat;
  35. #6.5 修改sname数据类型的属性
  36. DESC stu;
  37. ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
  39. #6.6 将gender改为sex 数据类型改为char类型
  40. ALTER TABLE stu  CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';

3.6 数据库权限相关(DCL)

  1. grant
  2. revoke

3.7 对表中的数据行进行增删改(DML)

3.7.1 insert

  1. --- 最标准的insert语句
  2. INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
  3. VALUES
  4. (1,'zs',18,'m','123456',NOW());
  5. SELECT * FROM stu;
  6. --- 省事的写法
  7. INSERT INTO stu 
  8. VALUES
  9. (2,'ls',18,'m','1234567',NOW());
  10. --- 针对性的录入数据
  11. INSERT INTO stu(sname,sfz)
  12. VALUES ('w5','34445788');
  13. --- 同时录入多行数据
  14. INSERT INTO stu(sname,sfz)
  15. VALUES 
  16. ('w55','3444578d8'),
  17. ('m6','1212313'),
  18. ('aa','123213123123');
  19. SELECT * FROM stu;

3.7.2 update

  1. UPDATE stu SET sname = 'zhao4' #这个语句很危险，会把表里面所有为sname这一列的数据改成zhao4
  2. UPDATE stu SET sname='zhao4' WHERE id=2;
  3. 注意：update语句必须要加where。

3.7.3 delete(危险！！！)

  1. DELETE FROM stu  WHERE id=3;

全表删除

  1. DELETE FROM stu
  2. truncate table stu;
  3. 区别:
  4. delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
  5. truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

  1. 1.添加状态列
  2. ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
  3. SELECT * FROM stu;
  4. 2. UPDATE 替代 DELETE
  5. UPDATE stu SET state=0 WHERE id=6;
  6. 3. 业务语句查询
  7. SELECT * FROM stu WHERE state=1;

3.8 SQL-select

select的准备工作：数据库对应world这个数据库，可以去mysql官方去下载：https://dev.mysql.com/doc/index-other.html

后面的所有查询练习，都是基于这个数据库的

3.8.1 单独使用

  1. #用select查看系统参数
  2. select @@port;  #查看端口
  3. select @@basedir;  #查看安装路径
  4. select @@datadir;  #查看数据路径
  5. select @@socket;  #查看socket文件
  6. select @@server_id;  #查看server id

3.8.2 select 函数

  1. select NOW();  #打印当前时间
  2. select DATABASE();  #打印当前数据库名称
  3. select USER();  #打印当前用户
  4. select CONCAT("hello","world")  #拼接字符串
  5. select LENGTH() #计算字段的长度，一个汉字算三个字符，一个数字或字母算一个字符
  6. select REPLACE() #替换函数，有3个参数，分别为：要替换的表达式或字段名、想要查找的被替换字符串、替换成哪个字符串
  7. select SUBSTRING() #截取字符串，有3个参数，分别为：待截取的表达式或字段名、开始截取的位置、想要截取的字符串长度
  9. 列子：
  10. SELECT CONCAT('abc', 123)，运行结果为 abc123。
  11. SELECT LENGTH('你好')，运行结果为 6。
  12. SELECT REPLACE('fabcd', 'abc', 123)，运行结果为 f123d。
  13. SELECT SUBSTRING('fabcd', 1,3)，运行结果为 fab

3.8.3 单表子句-from

  1. #语法结构
  2. select 列1,列2 from table;  #查字段1、字段2的数据
  3. select * from table;
  5. #列子：
  6. -- 查询stu中所有的数据（不要对大表进行操作）
  7. select * from stu;
  9. -- 查询stu表中，学生姓名和入学时间
  10. select sname,intime from stu;

注意：尽量少使用select *来查询数据，虽然使用方便了，实际上这样是增加了数据库的负担。所以如果我们不需要把所有列都检索出来，还是先指定出所需的列名，因为写清列名，可以减少数据表查询的网络传输量，而且考虑到在实际的工作中，我们往往不需要全部的列名，因此你需要养成良好的习惯，写出所需的列名。

3.8.4 单表子句-where

  1. select 列1,列2 from where 字段 条件;

3.8.4.1 where配合等值查询
  1. 列子1：
  2. -- 查询中国（CHN）所有城市信息
  3. select * from city where countrycode='CHN';
  5. 列子2：
  6. -- 查询北京市的信息
  7. select * from city where name = 'beijing';

3.8.4.2 where配合比较操作符（> >= < <= <>）
  1. 列子：
  2. select * from city where population < 100;

3.8.4.3 where配合逻辑运算符（and or）
  1. 列子1：
  2. -- 中国人口数量大于500
  3. select * from city where countrycode = 'CHN' AND population > 5000000;
  5. 列子2：
  6. -- 中国或美国城市信息
  7. SELECT * FROM city WHERE countrycode = 'CHN' OR countrycode = 'USA';

3.8.4.4 where配合模糊查询
  1. 列子1：
  2. -- 查询省的名字前面带guang开头的
  3. SELECT * FROM city WHERE district LIKE 'guang%';    
  4. 注意:%不能放在前面,因为不走索引.

3.8.4.5 where配合in语句
  1. -- 中国或美国城市信息
  2. select * from city where countrycode IN ('CHN' ,'USA');

3.8.5 group by + 常用聚合函数

3.8.5.1 作用
  1. 根据 group by后面的条件进行分组，方便统计，by后面跟一个列或多个列

3.8.5.2 常用聚合函数
  1. max()      ：最大值
  2. min()      ：最小值
  3. avg()      ：平均值
  4. sum()      ：总和
  5. count()    ：个数
  6. group_concat() : 列转行

3.8.5.3 例子

例子1：统计世界上每个国家的总人口数.

  1. USE world
  2. SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例子2： 统计中国各个省的总人口数量(练习)

  1. SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例子3：统计世界上每个国家的城市数量(练习)

  1. SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

3.8.5.4 having

例子4：统计中国每个省的总人口数，只打印总人口数小于100

  1. SELECT district,SUM(Population)
  2. FROM city
  3. WHERE countrycode='chn'
  4. GROUP BY district
  5. HAVING SUM(Population) < 1000000 ;

3.8.5.5 order by + limit

3.8.5.5.1 作用
  1. 实现先排序，by后添加条件列

3.8.5.5.2 应用案例

1.查看中国所有的城市，并按人口数进行排序(从大到小)

  1. SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

2.统计中国各个省的总人口数量，按照总人口从大到小排序

  1. SELECT district AS 省 ,SUM(Population) AS 总人口
  2. FROM city
  3. WHERE countrycode='chn'
  4. GROUP BY district
  5. ORDER BY 总人口 DESC ;

3.统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

  1. SELECT  district, SUM(population)  FROM  city 
  2. WHERE countrycode='CHN'
  3. GROUP BY district 
  4. HAVING SUM(population)>5000000
  5. ORDER BY SUM(population) DESC
  6. LIMIT 3 ;
  8. LIMIT N ,M --->跳过N,显示一共M行
  9. LIMIT 5,5
  11. SELECT  district, SUM(population)  FROM  city 
  12. WHERE countrycode='CHN'
  13. GROUP BY district 
  14. HAVING SUM(population)>5000000
  15. ORDER BY SUM(population) DESC
  16. LIMIT 5,5;

3.8.5.6 distinct：去重复
  1. SELECT countrycode FROM city ;
  2. SELECT DISTINCT(countrycode) FROM city  ;

3.8.5.7 联合查询- union all
  1. -- 中国或美国城市信息
  3. SELECT * FROM city 
  4. WHERE countrycode IN ('CHN' ,'USA');
  6. SELECT * FROM city WHERE countrycode='CHN'
  7. UNION ALL
  8. SELECT * FROM city WHERE countrycode='USA'
  10. 说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
  11. UNION     去重复
  12. UNION ALL 不去重复

3.8.6 多表连接查询

  • 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
  • 联合查询UNION
  • 交叉连接：笛卡尔乘积
  • 内连接：
    • 等值连接：让表之间的字段以”等值”建立连接关系
    • 不等值连接
    • 自然连接：去掉重复列的等值连接
  • 外连接：
    • 左外连接
    • 右外连接
  • 自连接：本表和本表进行连接查询

3.8.6.1 子查询

常用在where子句中的子查询

  1. 1、用于比较表达式中的子查询，子查询仅能返回单个值
  2. select name,age from students where age > (select avg(age) from teachers);
  4. 2、用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
  5. select name,age from students where age in (select age from teachers);

3.8.6.2 内连接
  1. 1、内连接ineer join
  2. select * from students inner join teachers on students.teacherid = teachers.tid;
  4. 2、内连接后过滤数据
  5. select * from students s inner join teachers t on s.teacherid = t.tid and s.age > 30;

3.8.6.3 左和右外连接

范例：左，右外连接

  1. 1、left join 左外连接：返回包括左表中的所有记录和右表中联结字段相等的记录（右表中没有的字段或者数据，则由null替代）
  2. select * from students as s left join teachers as t on s.teacherid = t.tid;
  3.  StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
  4. +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
  5. |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
  6. |     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
  7. |     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
  8. |     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
  9. |     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
  10. |     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
  11. |     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  12. |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  13. |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  14. |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  15. |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  16. |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  17. |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
  18. |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  19. |    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  20. |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  21. |    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  22. |    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  23. |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  24. |    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  25. |    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  26. |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  27. |    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  28. |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
  29. |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
  30. +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
  32. 2、right join 右外连接：返回包括右表中的所有记录和左表中联结字段相等的记录（没有的字段或者数据则用null表示）
  33. select * from students as s right join teachers as t on s.teacherid = t.tid;
  34. +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
  35. | StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
  36. +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
  37. |     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
  38. |     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
  39. |     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
  40. |  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
  41. +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

3.8.6.4 完全外连接

就是左连接和右连接合并之后的结果，当然当中的联合需要union

  1. #完全外连接
  2. select * from students as s left join teachers as t on s.teacherid = t.tid
  3.     -> union
  4.     -> select * from students as s right join teachers as t on s.teacherid = t.tid;
  5. +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
  6. | StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
  7. +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
  8. |     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
  9. |     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
  10. |     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
  11. |     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
  12. |     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
  13. |     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
  14. |     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  15. |     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  16. |     9 | Ren Yingying  |   20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  17. |    10 | Yue Lingshan  |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  18. |    11 | Yuan Chengzhi |   23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  19. |    12 | Wen Qingqing  |   19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  20. |    13 | Tian Boguang  |   33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
  21. |    14 | Lu Wushuang   |   17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
  22. |    15 | Duan Yu       |   19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  23. |    16 | Xu Zhu        |   21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  24. |    17 | Lin Chong     |   25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  25. |    18 | Hua Rong      |   23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  26. |    19 | Xue Baochai   |   18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  27. |    20 | Diao Chan     |   19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
  28. |    21 | Huang Yueying |   22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
  29. |    22 | Xiao Qiao     |   20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
  30. |    23 | Ma Chao       |   23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
  31. |    24 | Xu Xian       |   27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
  32. |    25 | Sun Dasheng   |  100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
  33. |  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
  34. +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+

3.8.7 别名

  1. 列别名,表别名
  2. SELECT 
  3. a.Name AS an ,
  4. b.name AS bn ,
  5. b.SurfaceArea AS bs,
  6. a.Population AS bp
  7. FROM city AS a  JOIN country AS b
  8. ON a.CountryCode=b.Code
  9. WHERE a.name ='shenyang';
  11. 表别名 所有都可以使用
  12. 列别名 having，order by 可以使用

4 补充：show命令

  1. show  databases;                          #查看所有数据库
  2. show tables;                                          #查看当前库的所有表
  3. SHOW TABLES FROM                        #查看某个指定库下的表
  4. show create database world                #查看建库语句
  5. show create table world.city                #查看建表语句
  6. show  grants for  root@'localhost'       #查看用户的权限信息
  7. show  charset；                                   #查看字符集
  8. show collation                                      #查看校对规则
  9. show processlist;                                  #查看数据库连接情况
  10. show index from                                 #表的索引情况
  11. show status                                         #数据库状态查看
  12. SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
  13. SHOW VARIABLES                             #查看所有配置信息
  14. SHOW variables LIKE '%lock%';          #查看部分配置信息
  15. show engines                                       #查看支持的所有的存储引擎
  16. show engine innodb status\G               #查看InnoDB引擎相关的状态信息
  17. show binary logs                                    #列举所有的二进制日志
  18. show master status                                 #查看数据库的日志位置信息
  19. show binlog evnets in                             #查看二进制日志事件
  20. show slave status \G                             #查看从库状态
  21. SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
  22. desc  (show colums from city)               #查看表的列定义信息

5 设计数据表的原则

我们在设计数据表的时候，经常会考虑到各种问题，比如：用户都需要什么数据？需要在数据表中保存哪些数据？哪些数据是经常访问的数据？如何提升检索效率？

如何保证数据表中数据的正确性，当插入、删除、更新的时候该进行怎样的约束检查？

如何降低数据表的数据冗余度，保证数据表不会因为用户量的增长而迅速扩张？

如何让负责数据库维护的人员更方便地使用数据库？

除此以外，我们使用数据库的应用场景也各不相同，可以说针对不同的情况，设计出来的数据表可能千差万别。那么有没有一种设计原则可以让我们来借鉴呢？这里我整理了一个“三少一多”原则：

1、数据表的个数越少越好

RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

2、数据表中的字段个数越少越好

字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

3、数据表中联合主键的字段个数越少越好

设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4、使用主键和外键越多越好

数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

你应该能看出来“三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。