SQL语句的用法

目录

关系统型数据库相

联系类型

数据库的正规化分析

第一范式：1NF  范式主要就是减产冗余

第二范式：2NF

第三范式：3NF

 字符串(char,varchar,text)

char和varchar的比较：

修饰符， 主键，唯一键

常见命令 

 创建表：

借鉴一个老表创建一个新表，的两种方式

​编辑

增删改查 （批量导入）

增 删

在name下面增加phone文件

在下面增加内容

改 

查 

select的用法     

单

多

内连接  inner join

联合查询union

交叉连接等cross join

左外连接left outer join

 右外连接right outer join

取没有交际的表

完全外连接 

自连接, 即表自身连接自身 

三个表内连接查询

别名

---

关系统型数据库相

• 关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
• 行row：表中的每一行，又称为一条记录record
• 列column：表中的每一列，称为属性，字段，域
• 主键Primary key只能有一个，内容不能相同
•        复合主键： 多列表加起来生成的主键
• 唯一键Unique key可以有多个 内容不能相同 空置可以null
• 域domain ：男女 年龄 0-150等

联系类型

• 一对一联系(1:1): 在表A或表B中创建一个字段﹐存储另一个表的主键值 如: 一个人只有一个身份证
• 一对多联系(1:n)：外键, 如: 部门和员工
• 多对多联系(m:n)：增加第三张表, 如: 学生和课

数据库的正规化分析

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般数据库只需满足第三范式(3NF）即可

• 规则是死的,人是活的,所以范式是否必须遵守,要看业务需要而定
• 掌握范式的目的是为了在合适的场景下违反范式

第一范式：1NF  范式主要就是减产冗余

• 无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列
• 说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库

第二范式：2NF

• 第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键

第三范式：3NF

• 满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系    （这主要是为了防止数据冗余，与其非主键依赖关系不如在创一个表）

 字符串(char,varchar,text)

• char(n) 固定长度，最多255个字符,注意不是字节
• varchar(n) 可变长度，最多65535个字符
• tinytext 可变长度，最多255个字符
• text 可变长度，最多65535个字符
• mediumtext 可变长度，最多2的24次方-1个字符
• longtext 可变长度，最多2的32次方-1个字符
• BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
• VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
• 内建类型：ENUM枚举, SET集合

char和varchar的比较：

char是指定大小，varchar是动态大小，char快，varchar节省空间，根据情况定义，如果字节大小类似可以varchat，

修饰符， 主键，唯一键

适用所有类型的修饰符：

NULL                       数据列可包含NULL值，默认值
NOT NULL              数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
DEFAULT                默认值
PRIMARY KEY       主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY          唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name       指定一个字符集

适用数值型的修饰符：    auto_increment

AUTO_INCREMENT      自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key
UNSIGNED                   无符号

常见命令 

•  status;               \s;              查看线程版本之类的 
• show databases                  查看数据库
• show tables;                        查看库里几个表
• desc xxx;                             查看表格创建信息
•  select * from students;       *代表着字段所有 查看所有表
• select database();              查看当前数据库
• select user();                      查看当前用户
• show create table student;                            查看以前创建表用的命令
• show table status like 'student'\G;                 查看表的详细信息存储过程，创建日期等
• \G    竖过来显示

• use mysql                        切换数据库
• create database db1;       创建数据库
•  drop table <表格名>        删除表格
• drop database <数据库名>;    删除库
• select * from syudent;     

set password for wordpress@'10.0.0.%'="654321";  修改密码
 

 创建表：

列：  

CREATE TABLE student (                                            创建表，名字
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, 名字，4字，全整，自动增加，主键
name VARCHAR(20) NOT NULL,                                    名字，限长20，不能为空，
age tinyint UNSIGNED,                                                    年龄，1字节，取整（最大255）
#height DECIMAL(5,2),                     
gender ENUM('M','F') default 'M'         

enum 限定值的取值范围，比如（男，女，未知）等    ENUM('M','F') default 'M'    男或女，默认男
)AUTO_INCREMENT=10;                   自动增长默认10开始，

CREATE TABLE students (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY comment "学生编号",
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
#height DECIMAL(5,2),
gender ENUM('M','F') default 'M'
)AUTO_INCREMENT=10 ;

 insert student (name,age)values('xiaoming',20);      写入到student里数据，小明，20岁

comment ‘描述干什么的’                                   描述干什么的可以加在最后

列：   id int UNSIGNED AUTO_INCREMENT PRIMARY KEY comment "学生编号",

借鉴一个老表创建一个新表，的两种方式

• create table custom like student;       
• 创建一个新表借鉴一个老表，表格内容不会集成下来，但是格式规则都会继承下来

• create table xxx as select * from students;   
• 创建一个新表借鉴一个老表，内容都会继承下来，个别规则会丢掉，如自动递增，主键等

增删改查 （批量导入）

增 删

尽量不要跟更改表结构，容易出问题

在name下面增加phone文件

• ALTER TABLE students ADD COLUMN phone varchar(11) AFTER name;
• 更改，表格，    x，       增加  列     （创建名字） 最多位  谁的下面（name下面phone文件）
• ALTER TABLE s1 DROP COLUMN mobile;  更改，表格，x，删除，列，x

#修改表名
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;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;
#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
#删除字段
ALTER TABLE students DROP age;
#查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;

虽然有自动增长，但是如果不写的话，就需要，写清楚赋值对象  需要一一对应，一行也可以加多行记录

在下面增加内容

列：

• insert into students value(26,'wei',18,'M',1,null);   
• insert students set name='wsd',age=22,gender='M';   这样亦可以

​​

insert students (name, age, gender) select name, age, gender from teachers 

把teachers的 name, age, gender 插入到students的最后面   

​

改 

update students set age=21,gender='F' where name='wei';

• 把wei的年龄改成21，性别女，指定修改name=wei的    where指定
• 也可以 where age >20;   把20岁以上的都改了

改后

 update students set teacherid=1 where TeacherID is null;

• 把teacherid的空值（null）改为1 ，这里需要用is 不能用=；

delete from students where stuid=30;          删除指定行 

查 

select的用法     

• 不能使用正则表达式，否则有的索引会失效

select * from students;   *代表着字段所有（就是把字段的所有内容打印粗来）可以把*换成列名

select 类似于echo有类似打印功能，如图；  如果后面跟文件名就是打印的行长

​

查看students表中年龄是10~20岁的stuid ,name,gender,age字段信息

•  select stuid ,name,gender,age from students where age between 18 and 20;
• select stuid ,name,gender,age from students where age >=18 and age <= 20;

​

单

• order  by age;  从小到大排序
• order  by age desc   排序从打到小
• avg(age)  统计括号里的平均年龄
• max(stuid)显示stuid最大值的编号
• group by gender 分组

mysql> select stuid,name,gender,age from students;
+-------+---------------+--------+-----+
| stuid | name          | gender | age |
+-------+---------------+--------+-----+
|     1 | Shi Zhongyu   | M      |  22 |
|     2 | Shi Potian    | M      |  22 |
|     3 | Xie Yanke     | M      |  53 |
|     4 | Ding Dian     | M      |  32 |
|     5 | Yu Yutong     | M      |  26 |        查询students下的stuid,name,gender,age信息
|     6 | Shi Qing      | M      |  46 |
|     7 | Xi Ren        | F      |  19 |
|     8 | Lin Daiyu     | F      |  17 |
|     9 | Ren Yingying  | F      |  20 |
|    10 | Yue Lingshan  | F      |  19 |
|    11 | Yuan Chengzhi | M      |  23 |
|    12 | Wen Qingqing  | F      |  19 |
|    13 | Tian Boguang  | M      |  33 |
|    14 | Lu Wushuang   | F      |  17 |
|    15 | Duan Yu       | M      |  19 |
|    16 | Xu Zhu        | M      |  21 |
|    17 | Lin Chong     | M      |  25 |
|    18 | Hua Rong      | M      |  23 |
|    19 | Xue Baochai   | F      |  18 |
|    20 | Diao Chan     | F      |  19 |
|    21 | Huang Yueying | F      |  22 |
|    22 | Xiao Qiao     | F      |  20 |
|    23 | Ma Chao       | M      |  23 |

mysql> select stuid ,name,gender,age from students where gender='F';
+-------+---------------+--------+-----+
| stuid | name          | gender | age |
+-------+---------------+--------+-----+
|     7 | Xi Ren        | F      |  19 |
|     8 | Lin Daiyu     | F      |  17 |
|     9 | Ren Yingying  | F      |  20 |         查询这里所有信息都是女的
|    10 | Yue Lingshan  | F      |  19 |
|    12 | Wen Qingqing  | F      |  19 |
|    14 | Lu Wushuang   | F      |  17 |
|    19 | Xue Baochai   | F      |  18 |
|    20 | Diao Chan     | F      |  19 |
|    21 | Huang Yueying | F      |  22 |
|    22 | Xiao Qiao     | F      |  20 |
|    26 | wei           | F      |  21 |
+-------+---------------+--------+-----+


(root@localhost) [hellodb]> select stuid ,name,gender,age from students where name like '%x%';
+-------+-------------+--------+-----+
| stuid | name        | gender | age |
+-------+-------------+--------+-----+
|     3 | Xie Yanke   | M      |  53 |
|     7 | Xi Ren      | F      |  19 |     模糊查询
|    16 | Xu Zhu      | M      |  21 |     x%代表x开头的name模糊查询
|    19 | Xue Baochai | F      |  18 |     %x%  代表有x的模糊查询
|    22 | Xiao Qiao   | F      |  20 |
|    24 | Xu Xian     | M      |  27 |
+-------+-------------+--------+-----+


root@localhost) [hellodb]> select distinct age from students;
+-----+
| age |
+-----+
|  22 |
|  53 |
|  32 |                 distinct   合并去重查询
|  26 |
|  46 |
|  19 |
|  17 |
|  20 |
|  23 |
|  33 |
|  21 |
|  25 |
|  18 |
|  27 |
| 100 |
|  45 |
|  94 |
|  77 |
+-----+

(root@localhost) [hellodb]> select distinct age from students order  by age desc;
+-----+
| age |
+-----+
| 100 |
|  94 |
|  77 |
|  53 |            order  by age;  从小到大排序
|  46 |
|  45 |         order  by age desc   排序从打到小
|  33 |
|  32 |
|  27 |
|  26 |
|  25 |
|  23 |
|  22 |
|  21 |
|  20 |
|  19 |
|  18 |
|  17 |
+-----+
18 rows in set (0.00 sec)

(root@localhost) [hellodb]> select distinct age from students order  by age;
+-----+
| age |
+-----+
|  17 |
|  18 |
|  19 |
|  20 |     order  by age;  从小到大排序
|  22 |
|  23 |
|  25 |
|  26 |
|  27 |
|  32 |
|  33 |
|  45 |
|  46 |
|  53 |
|  77 |
|  94 |
| 100 |
+-----+
18 rows in set (0.00 sec)
、root@localhost) [hellodb]> select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |       avg(age)  统计括号里的平均年龄
+--------+----------+       gender 显示年龄大小
| F      |  19.1818 |
| M      |  32.3125 |
+--------+----------+

select gender,max(stuid),avg(age) from students grouproup by gender;
+--------+------------+----------+
| gender | max(stuid) | avg(age) |
+--------+------------+----------+     max(stuid)显示stuid最大值的编号
| M      |         28 |  37.2941 |    
| F      |         29 |  24.0000 |   
+--------+------------+----------+

(root@localhost) [hellodb]> select gender,avg(age) from students group by gender having gender = 'M';
+--------+----------+
| gender | avg(age) |
+--------+----------+      group by gender 分组
| M      |  32.3125 |      having gender = 'M'; 分组后过滤，需要把他加在后面
+--------+----------+

mysql> select gender,avg(age) from students where gender = 'M' group by gender;
+--------+----------+
| gender | avg(age) |        where gender = 'M'  分组前过滤，需要把他加在过滤前面
+--------+----------+        
| M      |  37.2941 |
+--------+----------+




(root@localhost) [hellodb]> select classid,gender,max(age) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | max(age) |
+---------+--------+----------+
|       2 | M      |       53 |
|       1 | M      |       22 |   order by classid 在后面加这个可以对于班级进行排序
|       4 | M      |       32 |   取每个班性别的最大值 
|       3 | M      |       26 |
|       5 | M      |       46 |
|       3 | F      |       19 |     
|       7 | F      |       19 |    
|       6 | F      |       22 |
|       6 | M      |       23 |
|       1 | F      |       21 |
|       7 | M      |       23 |
|    NULL | M      |      100 |
|    NULL | F      |       77 |
+---------+--------+----------+

mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| 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 |
+-------+-------------+-----+--------+---------+-----------+

select * from students order by age desc limit 3; 
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |         1 |      排序取前三
|    28 | Zhang Sanfeng |  94 | M      |    NULL |         1 |
|    29 | Miejue Shitai |  77 | F      |    NULL |         1 |
+-------+---------------+-----+--------+---------+-----------+
select * from students order by age desc limit 3,4;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     6 | Shi Qing     |  46 | M      |       5 |         1 |   跳过前三，显示后4
|    27 | Song Jiang   |  45 | M      |    NULL |         1 |
|    13 | Tian Boguang |  33 | M      |       2 |         1 |
+-------+--------------+-----+--------+---------+-----------+

select * from students where age > (select avg(age) from teachers);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |         1 |
|    28 | Zhang Sanfeng |  94 | M      |    NULL |         1 |
+-------+---------------+-----+--------+---------+-----------+
   学生表的年龄比老师平均年龄大的是

select stuid,name,age from students union select tid,name,age from teachers;
+-------+---------------+-----+
| stuid | name          | age |
+-------+---------------+-----+
|     1 | Shi Zhongyu   |  22 |
|     2 | Shi Potian    |  22 |
|     3 | Xie Yanke     |  53 |
|     4 | Ding Dian     |  32 |
|     5 | Yu Yutong     |  26 |
|     6 | Shi Qing      |  46 |
|     7 | Xi Ren        |  19 |
|     8 | Lin Daiyu     |  17 |
|     9 | Ren Yingying  |  20 |
|    10 | Yue Lingshan  |  19 |
|    11 | Yuan Chengzhi |  23 |
|    12 | Wen Qingqing  |  19 |
|    13 | Tian Boguang  |  33 |
|    14 | Lu Wushuang   |  17 |       合并学生表和老师表的指定列
|    15 | Duan Yu       |  19 |
|    16 | Xu Zhu        |  21 |
|    17 | Lin Chong     |  25 |
|    18 | Hua Rong      |  23 |
|    19 | Xue Baochai   |  18 |
|    20 | Diao Chan     |  19 |
|    21 | Huang Yueying |  22 |
|    22 | Xiao Qiao     |  20 |
|    23 | Ma Chao       |  23 |
|    24 | Xu Xian       |  27 |
|    25 | Sun Dasheng   | 100 |
|    26 | wei           |  21 |
|    27 | Song Jiang    |  45 |
|    28 | Zhang Sanfeng |  94 |
|    29 | Miejue Shitai |  77 |
|     1 | Song Jiang    |  45 |
|     2 | Zhang Sanfeng |  94 |
|     3 | Miejue Shitai |  77 |
|     4 | Lin Chaoying  |  93 |
+-------+---------------+-----+


mysql> select * from students where age > (select avg(age) from teachers);
#从名为students的表中选择所有年龄大于teachers表中年龄平均值的记录。
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
mysql> update students set age=(select avg(age)from teachers) where stuid=25;
Query OK, 1 row affected (0.00 sec)
#将teachers表中age字段的平均值更新到students表中stuid为25的记录的age字段。
mysql> select * from students order by age desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   |  77 | M      |    NULL |      NULL |

多

联合查询union
交叉连接 cross join
内连接  inner join 
外连接  left outer join  right outer join 

内连接  inner join

inner join 内连接取多个表的交集

mysql> select * from students inner join teachers on students.gender=teachers.gender;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   1 | Song Jiang    |  45 | M      |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   2 | Zhang Sanfeng |  94 | M      |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |   1 | Song Jiang    |  45 

联合查询union

联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的.

mysql> select stuid,name,age from students
    -> union
    -> select tid,name,age from teachers;
#取两个表的tid,name,age 合并在一起  ，这样搞容易错乱
mysql> select stuid,name,age from students union select tid,name,age from teachers;
+-------+---------------+-----+
| stuid | name          | age |
+-------+---------------+-----+
|     1 | Shi Zhongyu   |  22 |
|     2 | Shi Potian    |  22 |
|     3 | Xie Yanke     |  53 |
|     4 | Ding Dian     |  32 |
|     5 | Yu Yutong     |  26 |
|     6 | Shi Qing      |  46 |
|     7 | Xi Ren        |  19 |
|     8 | Lin Daiyu     |  17 |
|     9 | Ren Yingying  |  20 |
|    10 | Yue Lingshan  |  19 |
|    11 | Yuan Chengzhi |  23 |
|    12 | Wen Qingqing  |  19 |
|    13 | Tian Boguang  |  33 |
|    14 | Lu Wushuang   |  17 |
|    15 | Duan Yu       |  19 |
|    16 | Xu Zhu        |  21 |
|    17 | Lin Chong     |  25 |
|    18 | Hua Rong      |  23 |
|    19 | Xue Baochai   |  18 |
|    20 | Diao Chan     |  19 |
|    21 | Huang Yueying |  22 |
|    22 | Xiao Qiao     |  20 |
|    23 | Ma Chao       |  23 |
|    24 | Xu Xian       |  27 |
|    25 | Sun Dasheng   |  77 |
|    26 | wei           |  21 |
|    27 | wsd           |  22 |
|     1 | Song Jiang    |  45 |
|     2 | Zhang Sanfeng |  94 |
|     3 | Miejue Shitai |  77 |
|     4 | Lin Chaoying  |  93 |
+-------+---------------+-----+

select * from students where age > (select avg(age) from teachers);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |         1 |
|    28 | Zhang Sanfeng |  94 | M      |    NULL |         1 |
+-------+---------------+-----+--------+---------+-----------+
   学生表的年龄比老师平均年龄大的是

select stuid,name,age from students union select tid,name,age from teachers;
+-------+---------------+-----+
| stuid | name          | age |
+-------+---------------+-----+
|     1 | Shi Zhongyu   |  22 |
|     2 | Shi Potian    |  22 |
|     3 | Xie Yanke     |  53 |
|     4 | Ding Dian     |  32 |
|     5 | Yu Yutong     |  26 |
|     6 | Shi Qing      |  46 |
|     7 | Xi Ren        |  19 |
|     8 | Lin Daiyu     |  17 |
|     9 | Ren Yingying  |  20 |
|    10 | Yue Lingshan  |  19 |
|    11 | Yuan Chengzhi |  23 |
|    12 | Wen Qingqing  |  19 |
|    13 | Tian Boguang  |  33 |
|    14 | Lu Wushuang   |  17 |       合并学生表和老师表的指定列
|    15 | Duan Yu       |  19 |
|    16 | Xu Zhu        |  21 |
|    17 | Lin Chong     |  25 |
|    18 | Hua Rong      |  23 |
|    19 | Xue Baochai   |  18 |
|    20 | Diao Chan     |  19 |
|    21 | Huang Yueying |  22 |
|    22 | Xiao Qiao     |  20 |
|    23 | Ma Chao       |  23 |
|    24 | Xu Xian       |  27 |
|    25 | Sun Dasheng   | 100 |
|    26 | wei           |  21 |
|    27 | Song Jiang    |  45 |
|    28 | Zhang Sanfeng |  94 |
|    29 | Miejue Shitai |  77 |
|     1 | Song Jiang    |  45 |
|     2 | Zhang Sanfeng |  94 |
|     3 | Miejue Shitai |  77 |
|     4 | Lin Chaoying  |  93 |
+-------+---------------+-----+


mysql> select * from students where age > (select avg(age) from teachers);
#从名为students的表中选择所有年龄大于teachers表中年龄平均值的记录。
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
mysql> update students set age=(select avg(age)from teachers) where stuid=25;
Query OK, 1 row affected (0.00 sec)
#将teachers表中age字段的平均值更新到students表中stuid为25的记录的age字段。
mysql> select * from students order by age desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   |  77 | M      |    NULL |      NULL |

交叉连接等cross join

cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加, "雨露均沾"
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用

union 横行结合 #横向合并，交叉连接（横向笛卡尔）


交叉连接 100和100的数据 是100x100的合成数据 尽量千万别这样做
select * from students inner join teachers on students.gender=teachers.gender;

+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  93 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   4 | Lin Chaoying  |  93 | F      |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   3 | Miejue Shitai |  77 | F      |
           ``````````````````````````

#从students表和teachers表中选择所有的列，并且只返回那些在teacherid列和tid列上具有相同值的行。
SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.列名 = 表2.列名;

mysql> select *from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+-

左外连接left outer join

学生表对应的老师表id
#获取所有学生的信息，并且将他们所属的教师信息也一并获取。如果某个学生没有对应的教师信息，则该学生的教师信息会显示为NULL。
mysql> select * from students left outer join teachers on students.teacherid=teachers.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL
                ···················· 

 右外连接right outer join

• 获取所有教师的信息，并且将他们所教授的学生信息也一并获取。如果某个教师没有对应的学生信息，则该教师的学生信息会显示为NULL。

mysql> select * from students right outer join teachers on students.teacherid=teachers.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

取没有交际的表

mysql> select * from students left outer join teachers on students.teacherid=teachers.tid is null;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |    1 | Song Jiang    |   45 | M      |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |    1 | Song Jiang    |   45 | M      |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |    1 | Song Jiang    |   45 | M      |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |    1 | Song Jiang    |   45 | M      |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |    1 | Song Jiang    |   45 | M      |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |    1 | Song Jiang    |   45 | M      |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |    1 | Song Jiang    |   45 | M      |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |    1 | Song Jiang    |   45 | M      |

完全外连接 

就是左连接加右链接组成的

ysql> select * from students left outer join teachers on students.teacherid=teachers.tid
    -> union
    -> select * from students right join teachers on students.teacherid=teachers.tid
    -> ;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |

自连接, 即表自身连接自身 

创建

写入

合并 e.leader_id=l.emp_id的对比  一个表格取俩个代号；

mysql> create table emp (emp_id int auto_increment primary key ,name varchar(10),leader_id int);
mysql> insert emp(name,leader_id)value('mage',null),('zhangsir',1),('wang',1),('li',3),('zhao',2);
mysql> select * from emp;
+--------+----------+-----------+
| emp_id | name     | leader_id |
+--------+----------+-----------+
|      1 | mage     |      NULL |
|      2 | zhangsir |         1 |
|      3 | wang     |         1 |
|      4 | li       |         3 |
|      5 | zhao     |         2 |
    ···········
合并
mysql> select * from emp e inner join emp l on e.leader_id=l.emp_id;
+--------+----------+-----------+--------+----------+-----------+
| emp_id | name     | leader_id | emp_id | name     | leader_id |
+--------+----------+-----------+--------+----------+-----------+
|      2 | zhangsir |         1 |      1 | mage     |      NULL |
|      3 | wang     |         1 |      1 | mage     |      NULL |
|      4 | li       |         3 |      3 | wang     |         1 |
|      5 | zhao     |         2 |      2 | zhangsir |         1 |

 null换名（无上级）

mysql> select e.name emp_name, l.name leader_name from emp e left join emp l on e.leader_id=l.emp_id;
+----------+-------------+
| emp_name | leader_name |
+----------+-------------+
| mage     | NULL        |
| zhangsir | mage        |
| wang     | mage        |
| li       | wang        |
| zhao     | zhangsir    |
| mage     | NULL        |
| zhangsir | mage        |
| wang     | mage        |
| li       | wang        |
| zhao     | zhangsir    |
+----------+-------------+
10 rows in set (0.00 sec)

mysql> select e.name emp_name, IFNULL(l.name, '无上级' ) leader_name from emp e left join emp l on e.leader_id=l.emp_id;
+----------+-------------+
| emp_name | leader_name |
+----------+-------------+
| mage     | 无上级      |
| zhangsir | mage        |
| wang     | mage        |
| li       | wang        |
| zhao     | zhangsir    |

三个表内连接查询

mysql> select * from scores;      #分数表                                                                                             
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  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 |
+----+-------+----------+-------+

mysql> 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   |
+----------+----------------+

mysql> 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       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   |  77 | M      |    NULL |      NULL |
|    26 | wei           |  21 | F      |       1 |      NULL |
|    27 | wsd           |  22 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

mysql> select students.name, Course, Score from students inner join scores on students.stuid = scores.stuid inner join courses on scores.CourseID = courses.CourseID;
+-------------+----------------+-------+
| name        | Course         | Score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+

FUNCTION 函数

count 统计多少行

 select count(*) from students;

mysql> select count(TeacherID ) from students;    #TeacherID的是数值空不算
+-------------------+
| count(TeacherID ) |
+-------------------+
|                 5 |
+-------------------+

 定义别名，执行别名

create view v_students_coures_scores as 定义view v_students_coures_scores为  别名

mysql> create view v_students_coures_scores as select students.name, Course, Score from students inner join scores on students.stuid = scores.stuid inner join courses on scores.CourseID = courses.CourseID;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------------------+
| Tables_in_hellodb        |
+--------------------------+
| classes                  |
| coc                      |
| courses                  |
| emp                      |
| scores                   |
| students                 |
| teachers                 |
| toc                      |
| v_students_coures_scores |
+--------------------------+

mysql> select * from v_students_coures_scores;
+-------------+----------------+-------+
| name        | Course         | Score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+

VIEW视图查看 

mysql> show table status like 'v_students_coures_scores'\G;
*************************** 1. row ***************************
           Name: v_students_coures_scores
         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)