• myspl数据库基础

     

    mysql数据库sql 语句大全收录---sql语句不区分大小写

    mysql安装

    1. 前往官网下载mysql对应版本,例如Windows 的压缩包 mysql-5.6.42-winx64.zip
    2. 下载的zip文件解压，将解压之后的文件夹放到任意目录下，这个目录就是mysql的安装目录。
    3. 打开目录，会看到my-default.ini配置文件，复制这个配置文件可以重命名为my.ini或者my.cnf 
      [mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:Program Filesmysql-5.6.39-winx64 
# 设置mysql数据库的数据的存放目录
datadir=C:Program Filesmysql-5.6.39-winx64data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
      my.ini
    4. 整理添加环境变量,在系统变量PATH后面添加: 你的mysql bin文件夹的路径（如C:Program Filesmysql-5.6.41-winx64in）
    5. 安装Mysql服务,以管理员身份打开cmd窗口后，将目录切换到你解压文件的bin目录，输入mysqld install回车运行.
    6. 启动mysql,以管理员身份在cmd中输入:net start mysql
    7. 服务启动成功之后，就可以登录了，输入mysql -u root -p（第一次登录没有密码，直接按回车过）

    启动/停止

    net start   mysql

    net stop   mysql

    用户登录

    mysql -uroot -p    登录本地数据库 root初始没有密码,回车登录

    mysql -u用户名 -p密码       登录本地数据库

    mysql -u用户名 -p密码  -h网段      登录指定网段机器的数据库

    使用权限较高的root用户进行授权(DCL语句)

    注意:所有的用户信息都在mysql.user中

    • grant all on mysql.user to 'Sheldon'@'localhost';   　　               赋予Sheldon mysql库所有使用权限
    • grant select on mysql.user to 'Sheldon'@'localhost';                   设置Sheldon对mysql库的查询权限
    • grant select ,insert on mysql.user to 'Sheldon'@'localhost';       设置Sheldon对mysql库的查询,修改的权限
    • grant select,insert on mysql.* to 'Sheldon'@'localhost';              设置Sheldon 对mysql库下所有的表的查询和修改权限
    • grant all on *.* to 'Sheldon'@'localhost';                                        设置Sheldon 所有数据库的权限

    库操作之增删改查

    　　增----> create database  db charset utf8;                 创建一个db的库

    　　删----> drop database db;                     　　　　　　 删除db库

    　　改----> alter database db charset gbk;　　　　　　 修改db库的编码为GBK

    　　查----> show databases;                                             查看所有的库

    表操作之增删改查

    　操作表需要切换到当前库中的表    ---> use db    切换到db库下

    　　增 ---> create table tt(id int,name  char);          增加一个结构为id 和name 组成的表

    　　删---> drop table tt;                                            删除tt的表

    　　改 ---> alter table tt modify name char(10)      将tt表的name 字段长度修改到10

    　　查 ---> show tables;                                           查看所有的表

    记录(表中的内容)操作之增删改查 (DML语句)

    　　增 ---> 方式1:insert into tt values(1,'Sheldon'),(2,'Raj'),(3,'Penny');         　　　　values之后--->按照向tt表结构位置顺序增加 3行数据

    　　　---> 方式2:insert into tt (id,name) values (1,'Sheldon'),(2,'Raj'),(3,'Penny');      values之前 --->给指定的字段增加  向tt表中增加3条记录

    　　删 --->  delete from tt where id=1;         删掉tt中id=1的数据

    　　改 --->  update  tt set  name = 'Amy' where id=2;                      将tt表中id=2的name 修改成  Amy

    　　查 ---> select * from tt;                                      查询tt表中所有的记录信息

        　　----> select id,name from tt;             在tt表中查询id 和 name的记录    有数据返回数据,没有返回Empty 

    清空表记录 --->   delete from tt;                      清空tt表中所有记录 ,如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始

    　　　　　　　 truncate  table tt;                  也是清空,数据量大,删除速度快,且从0 开始.

    表操作之详言

    　　注:在同一个表中,字段名是不相同的/宽度和约束条件可选/字段名和数据类型必须填写

    查看表结构的两种方式 

    　1. desc系列

    • describe tt;
    • desc tt;           两者都是查看表结构,只能查看字段信息  但是输出的数据结构不是很全面 

        2.show table tt G;       

    　　使用show create table tt; 语法除了可以看表的定义,还可以看到engine(存储引擎)和charset(字符集),自增起始值等信息(G含义就是记录竖行排列,以便显示内容较长记录)

       3. 修正表字段中文显示

    　　    alter table tableName convert to charset utf8; 

    修改表结构

    　　1.修改表名 

    　　　　　　alter table tt rename  TnT;        通过rename 将旧表名字修改成新表名字  TnT  当然呢sql语句是不区分大小写呢   也就是 tnt 啦

    　　2. 增加字段(注意是新字段)

    　　　　alter table tt add age int job char(19);      增加两个新字段age  和 job

    　　　　增加新字段顺序:

    　　　　　　alter table tt add num int first;                 增加num新字段到最前面;   

    　　　　　　alter table tt add habby char(20)   after id;        增加habby 字段 插入在  字段id的后面       

    　　3.删除字段(指定字段删除)

    　　　　alter table tt drop num;             指定删除num字段

    　　4.修改字段(modify 修改新/原字段都可以,change 修改原字段)

    　　　　alter table tt modify name varchar(12); [约束条件]        将name char(12)   改为 name varchar(12)  ---->也可改成新字段

    　　　　alter table tt change name name1 char(10);     将name字段名改成name1 数据类型为char(10) 

    　　　　修改原字段顺序:

    　　　　　　alter table tt change name name1 char(10) after id;   将name字段名改成name1 数据类型为char(10) 并插在 字段id后面

    　　　　　　alter table tt modify name1 varchar(10) first;             将name1 字段数据类型修改为varchar(10) 并插在最前面.

    表的完美性约束

    • not null       不能为空  >>> default  设置默认值

    • unique     唯一性,就是不能重复 ,不能约束null >>>auto_increment  自增

    • primary key 主键   特点:不能为空并且唯一

    • foreign key  外键 

    为什么要约束?

    　　因为一个表中的数据要想完整规范,就必须对字段有基础的约束,一般情况下,我们会根据程序的需求特点对数据库进行约束

     

    下面我们来创建表和添加约束性吧--- create table 表名 (字段名 数据类型(宽度) 约束条件)  以此格式扩展...

    约束1: not null

    　　create table newt(id int not null,sex enum('male','female') not null default 'male');     创建newt表结构为id字段 约束不为空,sex字段约束不为空,默认值male

    约束2:unique

    　　create table newt(id int unique);            创建newt表结构id字段约束插入的内容不能相同,除了null

    　　create table newt(id int not null unique);    创建newt 表结构id字段约束设置了唯一+非空,这时也不能插入null了,类似设置了主键primary key

    　　create table newt(ip char(15),port int ,unique(ip),unique(port));     创建newt表结构 ip字段和port字段 都要唯一

    　　create table newt(ip char(15),port int ,unique(ip,port));                    创建newt表结构ip字段和port字段联合唯一,表示只需要ip/port只要一个唯一即可

    约束3: primary key <------->not null + unique

    　　　　　　<注意一个表不能设置多个主键,只能存在一个主键,但是可以设置not null + unique>

    　　create table newt(id int primary key);                                      创建newt表结构 id为主键,不为空且唯一  

    　　create table newt(id int not null unique,name char(12) not null unique);             创建newt表结构id字段和name字段联合主键,谁在前面谁就是主键,后面的为伪主键

    　  auto_increment  自增----->数据类型只能为int    非常友好的一个属性

    　　对于自增来id而言,删除数据并不会影响自增

    　　create table newt(id int unique auto_increment)                         创建newt表结构id字段唯一且实现插入的值自增

    　　create table newt(id int primary key auto_increment)                创建newt表结构id字段为主键实现插入值自增

    约束4:foreign key

    　　<本表中的字段关联另一个表中唯一的字段,本表中的字段是外键,外表中的字段必须是唯一或主键>

    　　表与表的关联例子

    　　1.create table tt (id int unique,tt_name varchar(10));  创建tt表结构 id字段和tt_name字段

    　　alter table tt modify id int unique;         修改tt表中id字段唯一

    　　2.create table newt(id int,newt_name char(12),age int, tt_id int ,foreign key(tt_id) references tt(id)); 　 

    　　　　　　　　# 创建newt表结构id,newt_name,tt_id三个字段,其中tt_id字段作为外键和tt表中的id字段关联

    　　　　<注意:添加了外键约束,外键是newt(tt_id),那么newt(tt_id)和tt(id)都会受到约束!!!再修改数据相对麻烦>

    　　3.create table newtt(id int,newtt_name char(19),age int, tt_id int ,foreign key(tt_id) references tt(id) on delete cascade on update cascade);

    　　　　　　　　# 创建newtt表结构id,newtt_name,tt_id三个字段,其中tt_id字段作为外键和tt表中的id字段关联

    　　以上newt表和newtt表都有tt_id字段作为外键和tt表中的id字段实现关联 

    记录操作之单表查询/多表查询

    单表查询

    　　针对单个表的一系列操作

    基础sql语法和优先级

    select distinct 字段名 from 表
     where 条件
     group by 分组
     having  过滤
     order by 排序
     limit   m,n 取值

从from开始   where
            groupby
            having
            select
            distinct
            order by
            limit

1.找到from
2.where 指定的约束条件 去表中取出一条条记录
3.将拿到的一条条记录进行分组 group by 如果没有group by  那就整体一组
4.将分组后的结果进行过滤
5.执行select
6.去重 distinct
7.在进行排序 order by    升序 asc 降序 desc;
8.限制结果显示条数    limit 3;
    单表查询语法以及优先级

    先建立一个表来具体操作吧

    #员工表
create table employees(
    id int primary key auto_increment,
    name varchar(20) not null,
    sex enum('male','female') not null default 'male',
    age int,
    d_id  int,
    post  varchar(20),
    salary  double
);

# 员工信息
insert into employees (name,sex,age,d_id,post,salary) values
    ('海峰','male',27,100,'技术部',5900.10),
    ('永亮','male',29,100,'技术部',6300.22),
    ('小双','female',20,100,'技术部',5012.11),
    ('小东','male',25,103,'测试部',7300.10),
    ('鸿宇','male',26,103,'测试部',8123.55),
    ('青青','female',22,104,'行政部',5600.22),
    ('雨晴','female',18,104,'行政部',5800.10),
    ('会娟','female',25,105,'财务部',7766.21),
    ('刘晴','female',23,105,'财务部',8200.44),
    ('章总','male',32,101,'销售部',11000.56),
    ('赵伟','male',24,101,'销售部',6325.14),
    ('英杰','male',31,102,'研发部',11231.12),
    ('王博','male',27,102,'研发部',12653.05),
    ('小尹','male',28,102,'研发部',9863.51),
    ('康康','male',24,100,'技术部',8863.15),
    ('红英','female',19,108,'行政部',6845.56),
    ('小于','male',40,107,'技术部',1000.52);
    单表 employees -操作

    简单查询

        查询表的数据信息和表结构

    　　desc employees;

    　　select * from employees;

    # 表结构
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | NO   |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| d_id   | int(11)               | YES  |     | NULL    |                |
| post   | varchar(20)           | YES  |     | NULL    |                |
| salary | double                | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+

#表data

+----+--------+--------+------+------+-----------+----------+
| id | name   | sex    | age  | d_id | post      | salary   |
+----+--------+--------+------+------+-----------+----------+
|  1 | 海峰   | male   |   27 |  100 | 技术部    |   5900.1 |
|  2 | 永亮   | male   |   29 |  100 | 技术部    |  6300.22 |
|  3 | 小双   | female |   20 |  100 | 技术部    |  5012.11 |
|  4 | 小东   | male   |   25 |  103 | 测试部    |   7300.1 |
|  5 | 鸿宇   | male   |   26 |  103 | 测试部    |  8123.55 |
|  6 | 青青   | female |   22 |  104 | 行政部    |  5600.22 |
|  7 | 雨晴   | female |   18 |  104 | 行政部    |   5800.1 |
|  8 | 会娟   | female |   25 |  105 | 财务部    |  7766.21 |
|  9 | 刘晴   | female |   23 |  105 | 财务部    |  8200.44 |
| 10 | 章总   | male   |   32 |  101 | 销售部    | 11000.56 |
| 11 | 赵伟   | male   |   24 |  101 | 销售部    |  6325.14 |
| 12 | 英杰   | male   |   31 |  102 | 研发部    | 11231.12 |
| 13 | 王博   | male   |   27 |  102 | 研发部    | 12653.05 |
| 14 | 小尹   | male   |   28 |  102 | 研发部    |  9863.51 |
| 15 | 康康   | male   |   24 |  100 | 技术部    |  8863.15 |
| 16 | 红英   | female |   19 |  108 | 行政部    |  6845.56 |
| 17 | 小于   | male   |   40 |  107 | 技术部    |  1000.52 |
+----+--------+--------+------+------+-----------+----------+
    表结构和表data

    1.去重操作 

    　　select distinct post from employees;　

    +-----------+
| post      |
+-----------+
| 技术部    |
| 测试部    |
| 行政部    |
| 财务部    |
| 销售部    |
| 研发部    |
+-----------+
    按照部门去重

    2.通过四则运算查询

    　　练习1 查询所有员工的年薪

    　　select name,salary*12 as annual_salary from employees;

    +--------+--------------------+
| name   | salary*12          |
+--------+--------------------+
| 海峰   |  70801.20000000001 |
| 永亮   |           75602.64 |
| 小双   |  60145.31999999999 |
| 小东   |  87601.20000000001 |
| 鸿宇   |            97482.6 |
| 青青   |           67202.64 |
| 雨晴   |  69601.20000000001 |
| 会娟   |           93194.52 |
| 刘晴   |           98405.28 |
| 章总   |          132006.72 |
| 赵伟   |  75901.68000000001 |
| 英杰   |          134773.44 |
| 王博   | 151836.59999999998 |
| 小尹   |          118362.12 |
| 康康   | 106357.79999999999 |
| 红英   |           82146.72 |
| 小于   |           12006.24 |
+--------+--------------------+
    练习1答案

     练习2 新的一年,所有员工年龄又增长了一岁

    　　select name ,age+1 as new_age from employees;　

    +--------+---------+
| name   | new_age |
+--------+---------+
| 海峰   |      28 |
| 永亮   |      30 |
| 小双   |      21 |
| 小东   |      26 |
| 鸿宇   |      27 |
| 青青   |      23 |
| 雨晴   |      19 |
| 会娟   |      26 |
| 刘晴   |      24 |
| 章总   |      33 |
| 赵伟   |      25 |
| 英杰   |      32 |
| 王博   |      28 |
| 小尹   |      29 |
| 康康   |      25 |
| 红英   |      20 |
| 小于   |      41 |
+--------+---------+
    练习2答案

    3.定义显示格式

    　　concat()  该函数用于连接字符串

    　　select concat('姓名: ',name,'     年龄: ',age) as new  from employees;　　

    +----------------------------+
| new                        |
+----------------------------+
| 姓名: 海峰  年龄: 27       |
| 姓名: 永亮  年龄: 29       |
| 姓名: 小双  年龄: 20       |
| 姓名: 小东  年龄: 25       |
| 姓名: 鸿宇  年龄: 26       |
| 姓名: 青青  年龄: 22       |
| 姓名: 雨晴  年龄: 18       |
| 姓名: 会娟  年龄: 25       |
| 姓名: 刘晴  年龄: 23       |
| 姓名: 章总  年龄: 32       |
| 姓名: 赵伟  年龄: 24       |
| 姓名: 英杰  年龄: 31       |
| 姓名: 王博  年龄: 27       |
| 姓名: 小尹  年龄: 28       |
| 姓名: 康康  年龄: 24       |
| 姓名: 红英  年龄: 19       |
| 姓名: 小于  年龄: 40       |
+----------------------------+
    concat() 函数拼接字符串

    　  concat_ws()  第一个参数作为分隔符

    　　select concat_ws(":",name,age) as new1 from employees;

    +-----------+
| new1      |
+-----------+
| 海峰:27   |
| 永亮:29   |
| 小双:20   |
| 小东:25   |
| 鸿宇:26   |
| 青青:22   |
| 雨晴:18   |
| 会娟:25   |
| 刘晴:23   |
| 章总:32   |
| 赵伟:24   |
| 英杰:31   |
| 王博:27   |
| 小尹:28   |
| 康康:24   |
| 红英:19   |
| 小于:40   |
    concat_ws()使用方法

    4.CASE语句 

    　　sql语法　　

    　　　　case
    　　　　when 条件
    　　　　then 显示的内容
    　　　　when 条件
    　　　　then 显示的内容
    　　　　else
    　　　　显示的内容
    　　　　end

    　　

    SELECT
(
    CASE
    WHEN name = '小双' THEN
    name
    WHEN name = '雨晴' THEN
    CONCAT(name, '_妹妹')
ELSE
concat(name, '*-*')
END
) as new_name
FROM
employees;


+---------------+
| new_name      |
+---------------+
| 海峰*-*       |
| 永亮*-*       |
| 小双          |
| 小东*-*       |
| 鸿宇*-*       |
| 青青*-*       |
| 雨晴_妹妹     |
| 会娟*-*       |
| 刘晴*-*       |
| 章总*-*       |
| 赵伟*-*       |
| 英杰*-*       |
| 王博*-*       |
| 小尹*-*       |
| 康康*-*       |
| 红英*-*       |
| 小于*-*       |
+---------------+
    有趣的case语句

    综合练习 查询所有 岗位,员工名字,薪资

    　　select distinct post,concat_ws(':',name,salary) as emp_data from employees; 

    mysql> select distinct post,concat_ws(':',name,salary) as emp_data from employees;
+-----------+-----------------+
| post      | emp_data        |
+-----------+-----------------+
| 技术部    | 海峰:5900.1     |
| 技术部    | 永亮:6300.22    |
| 技术部    | 小双:5012.11    |
| 测试部    | 小东:7300.1     |
| 测试部    | 鸿宇:8123.55    |
| 行政部    | 青青:5600.22    |
| 行政部    | 雨晴:5800.1     |
| 财务部    | 会娟:7766.21    |
| 财务部    | 刘晴:8200.44    |
| 销售部    | 章总:11000.56   |
| 销售部    | 赵伟:6325.14    |
| 研发部    | 英杰:11231.12   |
| 研发部    | 王博:12653.05   |
| 研发部    | 小尹:9863.51    |
| 技术部    | 康康:8863.15    |
| 行政部    | 红英:6845.56    |
| 技术部    | 小于:1000.52    |
+-----------+-----------------+
    综合练习

     

    WHERE

    　　where 条件

    　　1.比较运算符 < / > / = / != or <> / >= / <= 

    　　2.范围  between  and

    　　3. in /not in  单个条件用 in

    　　4.like       '%'   /   '_'  两个通配符   %匹配多个 ,'_' 匹配一个 

    　　5.逻辑运算符 多个条件的时候可以使用逻辑运算符 and / or / not ...

    练习1 查询所有部门的美眉

    　　select post,name from employees where sex =  'female';　　

     1 select post,name from employees where sex =  'female';
 2 +-----------+--------+
 3 | post      | name   |
 4 +-----------+--------+
 5 | 技术部    | 小双   |
 6 | 行政部    | 青青   |
 7 | 行政部    | 雨晴   |
 8 | 财务部    | 会娟   |
 9 | 财务部    | 刘晴   |
10 | 行政部    | 红英   |
11 +-----------+--------+
    美眉

    练习2 查询技术部员工薪资大于8000的

    　　select post,name from employees where post = '技术部' and salary >8000;

    mysql> select post,name from employees where post = '技术部' and salary >8000;
+-----------+--------+
| post      | name   |
+-----------+--------+
| 技术部    | 康康   |
+-----------+--------+
    练习2 

    练习3 查询薪资在8800-12000之间的员工

    　　select name from employees where salary between 8800 and 12000;

    mysql> select name from employees where salary between 8800 and 12000;
+--------+
| name   |
+--------+
| 章总   |
| 英杰   |
| 小尹   |
| 康康   |
+--------+
    练习3

    练习4 查询年龄在18,20,23的美眉

    　　方式1 select name from employees where sex = 'female' and age = 18 or age = 20 or age = 23;

    　　方式2 select name from employees where sex = 'female' and age in(18,20,23);

    　　当然不在的话就这样写　　select name from employees where sex = 'female' and age not in(18,20,23);

    mysql> select name from employees where sex = 'female' and age = 18 or age = 20 or age = 23;
+--------+
| name   |
+--------+
| 小双   |
| 雨晴   |
| 刘晴   |
+--------+
3 rows in set (0.00 sec)

mysql> select name from employees where sex = 'female' and age in(18,20,23);
+--------+
| name   |
+--------+
| 小双   |
| 雨晴   |
| 刘晴   |
+--------+
    年轻的美眉

    练习5 查询所有员工名字是小开头的

    　　select name from employees where name like '小%';   % 匹配小后面n个单位

    　　select name from employees where name like '小_';   _ 匹配小后面1个单位

    mysql> select name from employees where name like '小%';
+--------+
| name   |
+--------+
| 小双   |
| 小东   |
| 小尹   |
| 小于   |
+--------+
4 rows in set (0.00 sec)

mysql> select name from employees where name like '小_';
+--------+
| name   |
+--------+
| 小双   |
| 小东   |
| 小尹   |
| 小于   |
+--------+
    小字辈

    GROUP BY

    　　1.单独使用group by 关键字 进行分组

    　　select post from employees group by post 注意我们按照post 分组那么select查询的只是post 想要获取其他信息,就要借助函数了

    mysql> select post from employees group by post
    -> ;
+-----------+
| post      |
+-----------+
| 财务部    |
| 测试部    |
| 技术部    |
| 销售部    |
| 行政部    |
| 研发部    |
+-----------+
    按照部门分组

    　　2.group by 和group_concat() 连用

    　　select post,group_concat(name) as all_name from employees group by post;

    mysql> select post,group_concat(name) from employees group by post;
+-----------+------------------------------------+
| post      | group_concat(name)                 |
+-----------+------------------------------------+
| 财务部    | 刘晴,会娟                          |
| 测试部    | 小东,鸿宇                          |
| 技术部    | 康康,海峰,小于,小双,永亮           |
| 销售部    | 章总,赵伟                          |
| 行政部    | 红英,雨晴,青青                     |
| 研发部    | 王博,小尹,英杰                     |
+-----------+------------------------------------+
    拿到每个部门的所有人姓名

    注意:如果用unique字段作为分组的依据,那么每条记录都是一组,这种分组没有意义,只有多条记录的某个字段值相同,该字段常用来做分组的依据　　

    聚合函数

    　　聚合函数一般配合group by 使用　　

    select post,count(*) from employees group by post;
select count(*) from employees where d_id = 100;
select max(salary) from employees;
select post, min(salary) from employees group by post;
select avg(salary) from employees;
select sum(salary) from employees;
select sum(salary) from employees where d_id =102;

#以下个别示例
mysql> select post,count(*) from employees group by post;
+-----------+----------+
| post      | count(*) |
+-----------+----------+
| 财务部    |        2 |
| 测试部    |        2 |
| 技术部    |        5 |
| 销售部    |        2 |
| 行政部    |        3 |
| 研发部    |        3 |
+-----------+----------+
6 rows in set (0.00 sec)

mysql> select count(*) from employees where d_id = 100;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select post, min(salary) from employees group by post;
+-----------+-------------+
| post      | min(salary) |
+-----------+-------------+
| 财务部    |     7766.21 |
| 测试部    |      7300.1 |
| 技术部    |     1000.52 |
| 销售部    |     6325.14 |
| 行政部    |     5600.22 |
| 研发部    |     9863.51 |
+-----------+-------------+
6 rows in set (0.00 sec)
    聚合函数示例

    HAVING--->过滤

    　　HAVING 与 WHERE的区别:　　　

    重要的是--->执行优先级 WHERE > GROUP BY > HAVING 
　　　　
1.where 发生在分组 group by 之前,因而where中可以有任意字段,但是绝对无法使用聚合函数

2.Having 发生在分组 group by 之后,因而 having中可以使用分组的字段,无法直接取到其他字段,可以用聚合函数

    练习1 查询各部门内包含的员工个数小于2的部门名、部门内包含员工名字、个数

    　　select post,group_concat(name),count(id) from employees group by post having count(id) < 3;

    mysql> select post,group_concat(name),count(id) from employees group by post having count(id) < 3;
+-----------+--------------------+-----------+
| post      | group_concat(name) | count(id) |
+-----------+--------------------+-----------+
| 财务部    | 刘晴,会娟          |         2 |
| 测试部    | 小东,鸿宇          |         2 |
| 销售部    | 章总,赵伟          |         2 |
+-----------+--------------------+-----------+
    练习1

    练习2 查询各部门平均薪资大于10000的部门名、平均工资

    　　select post,avg(salary) from employees group by post having avg(salary)>10000;

    mysql> select post,avg(salary) from employees group by post having avg(salary)>10000;
+-----------+--------------------+
| post      | avg(salary)        |
+-----------+--------------------+
| 研发部    | 11249.226666666667 |
+-----------+--------------------+
    练习2

    ORDER BY

    　　排序  ---> 默认升序 asc

    　　　　　　 降序 desc

    练习3 查询各部门平均薪资大于7000 小于10000的部门名、平均工资,结果按平均薪资升序排列

    　　select post, avg(salary) as avg_salary from employees group by post having avg_salary>7000 and avg_salary < 10000 order by avg_salary;

    mysql> select post,avg(salary) as avg_salary from employees group by post having avg_salary < 10000
    ->  and avg_salary > 7000 order by avg_salary;
+-----------+-------------------+
| post      | avg_salary        |
+-----------+-------------------+
| 测试部    | 7711.825000000001 |
| 财务部    | 7983.325000000001 |
| 销售部    |           8662.85 |
+-----------+-------------------+
    练习3

    练习4 查询所有员工信息，先按照age升序排序，如果age相同则按照薪资降序排序

    　　select * from  employees order by age,salary desc;

    mysql> select * from  employees order by age,salary desc;
+----+--------+--------+------+------+-----------+----------+
| id | name   | sex    | age  | d_id | post      | salary   |
+----+--------+--------+------+------+-----------+----------+
|  7 | 雨晴   | female |   18 |  104 | 行政部    |   5800.1 |
| 16 | 红英   | female |   19 |  108 | 行政部    |  6845.56 |
|  3 | 小双   | female |   20 |  100 | 技术部    |  5012.11 |
|  6 | 青青   | female |   22 |  104 | 行政部    |  5600.22 |
|  9 | 刘晴   | female |   23 |  105 | 财务部    |  8200.44 |
| 15 | 康康   | male   |   24 |  100 | 技术部    |  8863.15 |
| 11 | 赵伟   | male   |   24 |  101 | 销售部    |  6325.14 |
|  8 | 会娟   | female |   25 |  105 | 财务部    |  7766.21 |
|  4 | 小东   | male   |   25 |  103 | 测试部    |   7300.1 |
|  5 | 鸿宇   | male   |   26 |  103 | 测试部    |  8123.55 |
| 13 | 王博   | male   |   27 |  102 | 研发部    | 12653.05 |
|  1 | 海峰   | male   |   27 |  100 | 技术部    |   5900.1 |
| 14 | 小尹   | male   |   28 |  102 | 研发部    |  9863.51 |
|  2 | 永亮   | male   |   29 |  100 | 技术部    |  6300.22 |
| 12 | 英杰   | male   |   31 |  102 | 研发部    | 11231.12 |
| 10 | 章总   | male   |   32 |  101 | 销售部    | 11000.56 |
| 17 | 小于   | male   |   40 |  107 | 技术部    |  1000.52 |
+----+--------+--------+------+------+-----------+----------+
    练习4

    LIMIT

    　　限制查询的记录数--->针对在排序后取值的数量和范围.limit m,n

    练习5 查询所有员工年龄较年轻的前5个

    　　select name from employees order by age asc limit 5;

    mysql> select * from employees order by age asc limit 5;
+----+--------+--------+------+------+-----------+---------+
| id | name   | sex    | age  | d_id | post      | salary  |
+----+--------+--------+------+------+-----------+---------+
|  7 | 雨晴   | female |   18 |  104 | 行政部    |  5800.1 |
| 16 | 红英   | female |   19 |  108 | 行政部    | 6845.56 |
|  3 | 小双   | female |   20 |  100 | 技术部    | 5012.11 |
|  6 | 青青   | female |   22 |  104 | 行政部    | 5600.22 |
|  9 | 刘晴   | female |   23 |  105 | 财务部    | 8200.44 |
+----+--------+--------+------+------+-----------+---------+
    练习5

     练习6:查询男员工年龄较小的5个;

    　　select name from employees where sex = 'male' group by name order by age limit 0,5;

    mysql> select name from employees where sex = 'male' group by name order by age limit 0,5;
+--------+
| name   |
+--------+
| 康康   |
| 赵伟   |
| 小东   |
| 鸿宇   |
| 海峰   |
+--------+
    练习6

    练习7  继续取男员工年龄较小的后5个

     　　select name from employees where sex = 'male' order by age limit 5,5;

    mysql> select name from employees where sex = 'male' order by age limit 5,5;
+--------+
| name   |
+--------+
| 海峰   |
| 小尹   |
| 永亮   |
| 英杰   |
| 章总   |
+--------+
    练习7

    正则表达式查询

    　　按照正则规则进行匹配

    test1 查询所有员工名字以晴结尾的

    　　SELECT name FROM employees WHERE name REGEXP  '晴$';

    mysql> SELECT name FROM employees WHERE name REGEXP  '晴$';
+--------+
| name   |
+--------+
| 雨晴   |
| 刘晴   |
+--------+
    test1

    test2 查询所有员工名字以小开头,双或东结果的员工信息

    　　SELECT name FROM employees WHERE name REGEXP '小.*[双东]$';  

    mysql> SELECT name FROM employees WHERE name REGEXP '小.*[双东]$';
+--------+
| name   |
+--------+
| 小双   |
| 小东   |
+--------+
    test2

    多表查询

    1.连表查询

    　　连接一定是两个表,两个表具有关联条件才能连接

    首先我们先创建两个表来玩玩,看看他们的语法,重点是外连接,以及相关语句sql语句

     1 # 员工表
 2 create table employee(
 3     id int primary key auto_increment,
 4     name varchar(20) not null,
 5     sex enum('male','female') not null default 'male',
 6     age int,
 7     d_id  int
 8 );
 9 
10 
11 #部门表
12 create table department(
13     id int primary key ,
14     name varchar(20) not null
15 );
16 -------------------------------------------------------------------------
17 # 部门信息
18 insert into department values
19     (100,'技术支持部'),
20     (101,'销售部'),
21     (102,'研发部'),
22     (103,'测试部'),
23     (104,'行政部'),
24     (105,'财务部'),
25     (106,'规划部'),
26     (109,'电商部');
27 
28 # 员工信息
29 insert into employee (name,sex,age,d_id) values
30     ('海峰','male',27,100),
31     ('永亮','male',29,100),
32     ('小双','female',20,100),
33     ('小东','male',25,103),
34     ('鸿宇','male',26,103),
35     ('青青','female',22,104),
36     ('雨晴','female',18,104),
37     ('会娟','female',25,105),
38     ('刘晴','female',23,105),
39     ('章总','male',32,101),
40     ('赵伟','male',24,101),
41     ('英杰','male',31,102),
42     ('王博','male',27,102),
43     ('小尹','male',28,102),
44     ('飞哥','male',26,100),
45     ('康','male',24,100),
46     ('红英','female',19,108),
47     ('老于','male',40,107);
    建表,导入数据

     内连接/外连接

    　　其实这一系列操作都是基于"select 字段列表  from  表1 inner | left | right join 表2 on 表1.字段 = 表2.字段; " sql语句扩展

    交叉连接

    　　select * from employee,department;    不适合匹配任何条件,生成笛卡尔积,数据无法使用!

    +-----+-----------------+----+--------+--------+------+------+
| id  | name            | id | name   | sex    | age  | d_id |
+-----+-----------------+----+--------+--------+------+------+
| 100 | 技术支持部      |  1 | 海峰   | male   |   28 |  100 |
| 101 | 销售部          |  1 | 海峰   | male   |   28 |  100 |
| 102 | 研发部          |  1 | 海峰   | male   |   28 |  100 |
| 103 | 测试部          |  1 | 海峰   | male   |   28 |  100 |
| 104 | 行政部          |  1 | 海峰   | male   |   28 |  100 |
| 105 | 财务部          |  1 | 海峰   | male   |   28 |  100 |
| 106 | 规划部          |  1 | 海峰   | male   |   28 |  100 |
| 109 | 电商部          |  1 | 海峰   | male   |   28 |  100 |
| 100 | 技术支持部      |  2 | 永亮   | male   |   25 |  100 |
| 101 | 销售部          |  2 | 永亮   | male   |   25 |  100 |
| 102 | 研发部          |  2 | 永亮   | male   |   25 |  100 |
| 103 | 测试部          |  2 | 永亮   | male   |   25 |  100 |
| 104 | 行政部          |  2 | 永亮   | male   |   25 |  100 |
| 105 | 财务部          |  2 | 永亮   | male   |   25 |  100 |
| 106 | 规划部          |  2 | 永亮   | male   |   25 |  100 |
| 109 | 电商部          |  2 | 永亮   | male   |   25 |  100 |
| 100 | 技术支持部      |  3 | 小双   | female |   20 |  100 |
| 101 | 销售部          |  3 | 小双   | female |   20 |  100 |
| 102 | 研发部          |  3 | 小双   | female |   20 |  100 |
| 103 | 测试部          |  3 | 小双   | female |   20 |  100 |
| 104 | 行政部          |  3 | 小双   | female |   20 |  100 |
| 105 | 财务部          |  3 | 小双   | female |   20 |  100 |
| 106 | 规划部          |  3 | 小双   | female |   20 |  100 |
| 109 | 电商部          |  3 | 小双   | female |   20 |  100 |
| 100 | 技术支持部      |  4 | 小东   | male   |   25 |  103 |
| 101 | 销售部          |  4 | 小东   | male   |   25 |  103 |
| 102 | 研发部          |  4 | 小东   | male   |   25 |  103 |
| 103 | 测试部          |  4 | 小东   | male   |   25 |  103 |
| 104 | 行政部          |  4 | 小东   | male   |   25 |  103 |
| 105 | 财务部          |  4 | 小东   | male   |   25 |  103 |
| 106 | 规划部          |  4 | 小东   | male   |   25 |  103 |
| 109 | 电商部          |  4 | 小东   | male   |   25 |  103 |
| 100 | 技术支持部      |  5 | 鸿宇   | male   |   26 |  103 |
| 101 | 销售部          |  5 | 鸿宇   | male   |   26 |  103 |
| 102 | 研发部          |  5 | 鸿宇   | male   |   26 |  103 |
| 103 | 测试部          |  5 | 鸿宇   | male   |   26 |  103 |
| 104 | 行政部          |  5 | 鸿宇   | male   |   26 |  103 |
| 105 | 财务部          |  5 | 鸿宇   | male   |   26 |  103 |
| 106 | 规划部          |  5 | 鸿宇   | male   |   26 |  103 |
| 109 | 电商部          |  5 | 鸿宇   | male   |   26 |  103 |
| 100 | 技术支持部      |  6 | 青青   | female |   20 |  104 |
| 101 | 销售部          |  6 | 青青   | female |   20 |  104 |
| 102 | 研发部          |  6 | 青青   | female |   20 |  104 |
| 103 | 测试部          |  6 | 青青   | female |   20 |  104 |
| 104 | 行政部          |  6 | 青青   | female |   20 |  104 |
| 105 | 财务部          |  6 | 青青   | female |   20 |  104 |
| 106 | 规划部          |  6 | 青青   | female |   20 |  104 |
| 109 | 电商部          |  6 | 青青   | female |   20 |  104 |
| 100 | 技术支持部      |  7 | 雨晴   | female |   18 |  104 |
| 101 | 销售部          |  7 | 雨晴   | female |   18 |  104 |
| 102 | 研发部          |  7 | 雨晴   | female |   18 |  104 |
| 103 | 测试部          |  7 | 雨晴   | female |   18 |  104 |
| 104 | 行政部          |  7 | 雨晴   | female |   18 |  104 |
| 105 | 财务部          |  7 | 雨晴   | female |   18 |  104 |
| 106 | 规划部          |  7 | 雨晴   | female |   18 |  104 |
| 109 | 电商部          |  7 | 雨晴   | female |   18 |  104 |
| 100 | 技术支持部      |  8 | 会娟   | female |   18 |  105 |
| 101 | 销售部          |  8 | 会娟   | female |   18 |  105 |
| 102 | 研发部          |  8 | 会娟   | female |   18 |  105 |
| 103 | 测试部          |  8 | 会娟   | female |   18 |  105 |
| 104 | 行政部          |  8 | 会娟   | female |   18 |  105 |
| 105 | 财务部          |  8 | 会娟   | female |   18 |  105 |
| 106 | 规划部          |  8 | 会娟   | female |   18 |  105 |
| 109 | 电商部          |  8 | 会娟   | female |   18 |  105 |
| 100 | 技术支持部      |  9 | 刘晴   | female |   18 |  105 |
| 101 | 销售部          |  9 | 刘晴   | female |   18 |  105 |
| 102 | 研发部          |  9 | 刘晴   | female |   18 |  105 |
| 103 | 测试部          |  9 | 刘晴   | female |   18 |  105 |
| 104 | 行政部          |  9 | 刘晴   | female |   18 |  105 |
| 105 | 财务部          |  9 | 刘晴   | female |   18 |  105 |
| 106 | 规划部          |  9 | 刘晴   | female |   18 |  105 |
| 109 | 电商部          |  9 | 刘晴   | female |   18 |  105 |
| 100 | 技术支持部      | 10 | 章总   | male   |   30 |  101 |
| 101 | 销售部          | 10 | 章总   | male   |   30 |  101 |
| 102 | 研发部          | 10 | 章总   | male   |   30 |  101 |
| 103 | 测试部          | 10 | 章总   | male   |   30 |  101 |
| 104 | 行政部          | 10 | 章总   | male   |   30 |  101 |
| 105 | 财务部          | 10 | 章总   | male   |   30 |  101 |
| 106 | 规划部          | 10 | 章总   | male   |   30 |  101 |
| 109 | 电商部          | 10 | 章总   | male   |   30 |  101 |
| 100 | 技术支持部      | 11 | 赵伟   | male   |   22 |  101 |
| 101 | 销售部          | 11 | 赵伟   | male   |   22 |  101 |
| 102 | 研发部          | 11 | 赵伟   | male   |   22 |  101 |
| 103 | 测试部          | 11 | 赵伟   | male   |   22 |  101 |
| 104 | 行政部          | 11 | 赵伟   | male   |   22 |  101 |
| 105 | 财务部          | 11 | 赵伟   | male   |   22 |  101 |
| 106 | 规划部          | 11 | 赵伟   | male   |   22 |  101 |
| 109 | 电商部          | 11 | 赵伟   | male   |   22 |  101 |
| 100 | 技术支持部      | 12 | 英杰   | male   |   33 |  102 |
| 101 | 销售部          | 12 | 英杰   | male   |   33 |  102 |
| 102 | 研发部          | 12 | 英杰   | male   |   33 |  102 |
| 103 | 测试部          | 12 | 英杰   | male   |   33 |  102 |
| 104 | 行政部          | 12 | 英杰   | male   |   33 |  102 |
| 105 | 财务部          | 12 | 英杰   | male   |   33 |  102 |
| 106 | 规划部          | 12 | 英杰   | male   |   33 |  102 |
| 109 | 电商部          | 12 | 英杰   | male   |   33 |  102 |
| 100 | 技术支持部      | 13 | 王博   | male   |   27 |  102 |
| 101 | 销售部          | 13 | 王博   | male   |   27 |  102 |
| 102 | 研发部          | 13 | 王博   | male   |   27 |  102 |
| 103 | 测试部          | 13 | 王博   | male   |   27 |  102 |
| 104 | 行政部          | 13 | 王博   | male   |   27 |  102 |
| 105 | 财务部          | 13 | 王博   | male   |   27 |  102 |
| 106 | 规划部          | 13 | 王博   | male   |   27 |  102 |
| 109 | 电商部          | 13 | 王博   | male   |   27 |  102 |
| 100 | 技术支持部      | 14 | 小尹   | male   |   25 |  102 |
| 101 | 销售部          | 14 | 小尹   | male   |   25 |  102 |
| 102 | 研发部          | 14 | 小尹   | male   |   25 |  102 |
| 103 | 测试部          | 14 | 小尹   | male   |   25 |  102 |
| 104 | 行政部          | 14 | 小尹   | male   |   25 |  102 |
| 105 | 财务部          | 14 | 小尹   | male   |   25 |  102 |
| 106 | 规划部          | 14 | 小尹   | male   |   25 |  102 |
| 109 | 电商部          | 14 | 小尹   | male   |   25 |  102 |
| 100 | 技术支持部      | 15 | 飞哥   | male   |   26 |  100 |
| 101 | 销售部          | 15 | 飞哥   | male   |   26 |  100 |
| 102 | 研发部          | 15 | 飞哥   | male   |   26 |  100 |
| 103 | 测试部          | 15 | 飞哥   | male   |   26 |  100 |
| 104 | 行政部          | 15 | 飞哥   | male   |   26 |  100 |
| 105 | 财务部          | 15 | 飞哥   | male   |   26 |  100 |
| 106 | 规划部          | 15 | 飞哥   | male   |   26 |  100 |
| 109 | 电商部          | 15 | 飞哥   | male   |   26 |  100 |
| 100 | 技术支持部      | 16 | 康     | male   |   24 |  100 |
| 101 | 销售部          | 16 | 康     | male   |   24 |  100 |
| 102 | 研发部          | 16 | 康     | male   |   24 |  100 |
| 103 | 测试部          | 16 | 康     | male   |   24 |  100 |
| 104 | 行政部          | 16 | 康     | male   |   24 |  100 |
| 105 | 财务部          | 16 | 康     | male   |   24 |  100 |
| 106 | 规划部          | 16 | 康     | male   |   24 |  100 |
| 109 | 电商部          | 16 | 康     | male   |   24 |  100 |
| 100 | 技术支持部      | 17 | 红英   | male   |   26 |  108 |
| 101 | 销售部          | 17 | 红英   | male   |   26 |  108 |
| 102 | 研发部          | 17 | 红英   | male   |   26 |  108 |
| 103 | 测试部          | 17 | 红英   | male   |   26 |  108 |
| 104 | 行政部          | 17 | 红英   | male   |   26 |  108 |
| 105 | 财务部          | 17 | 红英   | male   |   26 |  108 |
| 106 | 规划部          | 17 | 红英   | male   |   26 |  108 |
| 109 | 电商部          | 17 | 红英   | male   |   26 |  108 |
| 100 | 技术支持部      | 18 | 老于   | male   |   24 |  107 |
| 101 | 销售部          | 18 | 老于   | male   |   24 |  107 |
| 102 | 研发部          | 18 | 老于   | male   |   24 |  107 |
| 103 | 测试部          | 18 | 老于   | male   |   24 |  107 |
| 104 | 行政部          | 18 | 老于   | male   |   24 |  107 |
| 105 | 财务部          | 18 | 老于   | male   |   24 |  107 |
| 106 | 规划部          | 18 | 老于   | male   |   24 |  107 |
| 109 | 电商部          | 18 | 老于   | male   |   24 |  107 |
+-----+-----------------+----+--------+--------+------+------+
    笛卡尔积

     内连接

    　　定义--->只有表一与表二中连接条件都满足才能显示出来.

    sql语法---> inner join on /where 条件

    　　方式1:select * from department,employee where department.id = employee.d_id;   插入where 条件 打破 笛卡尔积数据  取到两个表对应的数据　　 

    　　方式2:select * from  department inner join employee on department.id = employee.d_id;　　

    +-----+-----------------+----+--------+--------+------+------+
| id  | name            | id | name   | sex    | age  | d_id |
+-----+-----------------+----+--------+--------+------+------+
| 100 | 技术支持部      |  1 | 海峰   | male   |   28 |  100 |
| 100 | 技术支持部      |  2 | 永亮   | male   |   25 |  100 |
| 100 | 技术支持部      |  3 | 小双   | female |   20 |  100 |
| 103 | 测试部          |  4 | 小东   | male   |   25 |  103 |
| 103 | 测试部          |  5 | 鸿宇   | male   |   26 |  103 |
| 104 | 行政部          |  6 | 青青   | female |   20 |  104 |
| 104 | 行政部          |  7 | 雨晴   | female |   18 |  104 |
| 105 | 财务部          |  8 | 会娟   | female |   18 |  105 |
| 105 | 财务部          |  9 | 刘晴   | female |   18 |  105 |
| 101 | 销售部          | 10 | 章总   | male   |   30 |  101 |
| 101 | 销售部          | 11 | 赵伟   | male   |   22 |  101 |
| 102 | 研发部          | 12 | 英杰   | male   |   33 |  102 |
| 102 | 研发部          | 13 | 王博   | male   |   27 |  102 |
| 102 | 研发部          | 14 | 小尹   | male   |   25 |  102 |
| 100 | 技术支持部      | 15 | 飞哥   | male   |   26 |  100 |
| 100 | 技术支持部      | 16 | 康     | male   |   24 |  100 |
+-----+-----------------+----+--------+--------+------+------+
    部门表和员工表连接 

    　　显示左表和右表对应的行

    　　发现1.左表中的department.id在employee.d_id 中没出现,那么会抛弃这行数据,

    　　发现2.同理右表中employee.d_id,在department.id中没出现,也会抛弃这行数据!　

    练习1:取出所有部门中所有人的名字和性别  

    　　select emp.name,emp.sex,dep.name as dep_name  from department as dep,employee  as emp where dep.id = emp.d_id; 

    +--------+--------+-----------------+
| name   | sex    | dep_name        |
+--------+--------+-----------------+
| 海峰   | male   | 技术支持部      |
| 永亮   | male   | 技术支持部      |
| 小双   | female | 技术支持部      |
| 小东   | male   | 测试部          |
| 鸿宇   | male   | 测试部          |
| 青青   | female | 行政部          |
| 雨晴   | female | 行政部          |
| 会娟   | female | 财务部          |
| 刘晴   | female | 财务部          |
| 章总   | male   | 销售部          |
| 赵伟   | male   | 销售部          |
| 英杰   | male   | 研发部          |
| 王博   | male   | 研发部          |
| 小尹   | male   | 研发部          |
| 飞哥   | male   | 技术支持部      |
| 康     | male   | 技术支持部      |
+--------+--------+-----------------+
    练习1

    练习2:取得小双的年龄和部门

    　　select emp.name,dep.name as dep_name from employee as emp inner join department as dep on dep.id = emp.d_id where emp.name = '小双';

    +--------+-----------------+
| name   | dep_name        |
+--------+-----------------+
| 小双   | 技术支持部      |
+--------+-----------------+
    练习2

    练习3:找到年龄大于30的男员工和部门

    　　select emp.name,dep.name as dep_name from employee as emp inner join department as dep on dep.id = emp.d_id where age>30 and name is 'male';

    +--------+-----------+
| name   | dep_name  |
+--------+-----------+
| 章总   | 销售部    |
| 英杰   | 研发部    |
+--------+-----------+

    外连接

    • 左外连接--->一定显示左表中所有内容,如果右表中的内容没有符合条件就不连接
    • 右外连接---> 一定显示右表中所有内容,同理左表中的内容没有符合条件也不连接
    • 全外连接---> 显示左表和右表全部内容,

    sql语法 ---> left join /right join

    　　select *from employee as emp left join department as dep on dep.id = emp.d_id;

     1 +----+--------+--------+------+------+------+-----------------+
 2 | id | name   | sex    | age  | d_id | id   | name            |
 3 +----+--------+--------+------+------+------+-----------------+
 4 |  1 | 海峰   | male   |   27 |  100 |  100 | 技术支持部      |
 5 |  2 | 永亮   | male   |   29 |  100 |  100 | 技术支持部      |
 6 |  3 | 小双   | female |   20 |  100 |  100 | 技术支持部      |
 7 |  4 | 小东   | male   |   25 |  103 |  103 | 测试部          |
 8 |  5 | 鸿宇   | male   |   26 |  103 |  103 | 测试部          |
 9 |  6 | 青青   | female |   22 |  104 |  104 | 行政部          |
10 |  7 | 雨晴   | female |   18 |  104 |  104 | 行政部          |
11 |  8 | 会娟   | female |   25 |  105 |  105 | 财务部          |
12 |  9 | 刘晴   | female |   23 |  105 |  105 | 财务部          |
13 | 10 | 章总   | male   |   32 |  101 |  101 | 销售部          |
14 | 11 | 赵伟   | male   |   24 |  101 |  101 | 销售部          |
15 | 12 | 英杰   | male   |   31 |  102 |  102 | 研发部          |
16 | 13 | 王博   | male   |   27 |  102 |  102 | 研发部          |
17 | 14 | 小尹   | male   |   28 |  102 |  102 | 研发部          |
18 | 15 | 飞哥   | male   |   26 |  100 |  100 | 技术支持部      |
19 | 16 | 康     | male   |   24 |  100 |  100 | 技术支持部      |
20 | 17 | 红英   | female |   19 |  108 | NULL | NULL            |
21 | 18 | 老于   | male   |   40 |  107 | NULL | NULL            |
22 +----+--------+--------+------+------+------+-----------------+
    左外连接

    　　select * from employee as emp right join department as dep on dep.id = emp.d_id;

    +------+--------+--------+------+------+-----+-----------------+
| id   | name   | sex    | age  | d_id | id  | name            |
+------+--------+--------+------+------+-----+-----------------+
|    1 | 海峰   | male   |   27 |  100 | 100 | 技术支持部      |
|    2 | 永亮   | male   |   29 |  100 | 100 | 技术支持部      |
|    3 | 小双   | female |   20 |  100 | 100 | 技术支持部      |
|    4 | 小东   | male   |   25 |  103 | 103 | 测试部          |
|    5 | 鸿宇   | male   |   26 |  103 | 103 | 测试部          |
|    6 | 青青   | female |   22 |  104 | 104 | 行政部          |
|    7 | 雨晴   | female |   18 |  104 | 104 | 行政部          |
|    8 | 会娟   | female |   25 |  105 | 105 | 财务部          |
|    9 | 刘晴   | female |   23 |  105 | 105 | 财务部          |
|   10 | 章总   | male   |   32 |  101 | 101 | 销售部          |
|   11 | 赵伟   | male   |   24 |  101 | 101 | 销售部          |
|   12 | 英杰   | male   |   31 |  102 | 102 | 研发部          |
|   13 | 王博   | male   |   27 |  102 | 102 | 研发部          |
|   14 | 小尹   | male   |   28 |  102 | 102 | 研发部          |
|   15 | 飞哥   | male   |   26 |  100 | 100 | 技术支持部      |
|   16 | 康     | male   |   24 |  100 | 100 | 技术支持部      |
| NULL | NULL   | NULL   | NULL | NULL | 106 | 规划部          |
| NULL | NULL   | NULL   | NULL | NULL | 109 | 电商部          |
+------+--------+--------+------+------+-----+-----------------+
    右外连接

    　　select *from employee as emp left join department as dep on dep.id = emp.d_id

    　　union

    　　select * from employee as emp right join department as dep on dep.id = emp.d_id;

     1 +------+--------+--------+------+------+------+-----------------+
 2 | id   | name   | sex    | age  | d_id | id   | name            |
 3 +------+--------+--------+------+------+------+-----------------+
 4 |    1 | 海峰   | male   |   27 |  100 |  100 | 技术支持部      |
 5 |    2 | 永亮   | male   |   29 |  100 |  100 | 技术支持部      |
 6 |    3 | 小双   | female |   20 |  100 |  100 | 技术支持部      |
 7 |    4 | 小东   | male   |   25 |  103 |  103 | 测试部          |
 8 |    5 | 鸿宇   | male   |   26 |  103 |  103 | 测试部          |
 9 |    6 | 青青   | female |   22 |  104 |  104 | 行政部          |
10 |    7 | 雨晴   | female |   18 |  104 |  104 | 行政部          |
11 |    8 | 会娟   | female |   25 |  105 |  105 | 财务部          |
12 |    9 | 刘晴   | female |   23 |  105 |  105 | 财务部          |
13 |   10 | 章总   | male   |   32 |  101 |  101 | 销售部          |
14 |   11 | 赵伟   | male   |   24 |  101 |  101 | 销售部          |
15 |   12 | 英杰   | male   |   31 |  102 |  102 | 研发部          |
16 |   13 | 王博   | male   |   27 |  102 |  102 | 研发部          |
17 |   14 | 小尹   | male   |   28 |  102 |  102 | 研发部          |
18 |   15 | 飞哥   | male   |   26 |  100 |  100 | 技术支持部      |
19 |   16 | 康     | male   |   24 |  100 |  100 | 技术支持部      |
20 |   17 | 红英   | female |   19 |  108 | NULL | NULL            |
21 |   18 | 老于   | male   |   40 |  107 | NULL | NULL            |
22 | NULL | NULL   | NULL   | NULL | NULL |  106 | 规划部          |
23 | NULL | NULL   | NULL   | NULL | NULL |  109 | 电商部          |
24 +------+--------+--------+------+------+------+-----------------+
    全外连接

    总结:所谓连表,实际上就是把多张表通过条件连接,变成一张大表,然后所有的查询条件跟单表查询一样,可所谓大道至简,殊途同归.

    注意点:1.两张表中如果字段名不同,可以直接使用;字段名不同的话,就需要 表名.字段名 来表示具体的字段

    　　　 2.当表的名字过长的时候,可以对表进行临时重命名,重命名后在本条sql中就可以使用新名字代替原名字,　　　　

    字表查询 

    1.什么是子查询? 

    　　通俗来说是先查询一个表,得到一个结果,这个结果要么是N条,要么是1条,通常是一个字段,然后用这个结果作为条件去查询另外一张表

    　　定义:子查询是将一个查询语句嵌套在另一个查询语句中,内层查询语句的查询结果,可以作为外层查询语句的条件.

    2.子查询中的关键字和用法?

    　　in / not in/ any / all / exists /not exists等关键字,同时比较运算符 = / != / > / < / >=/<=都是.

    　　用法:1.如果最终的结果为n条,在外层查询可能用到 in/not in 条件

    　　　　2.如果结果是1条 在外层查询可能用到比较运算符

    子查询经验 ---> 结果在那张表,你最后查的一定是那张表,因此我们一定要提前构建好思路.　

    IN关键字

    练习1:查询平均年龄在24岁以上的部门名

    分析:步骤1 首先把查询每一个部门的平均年龄

    　　sql语句---> select d_id ,avg(age) from employee group by d_id;

    　　步骤2 再筛选出平均年龄大于24岁的部门筛选出来

    　　sql语句---> select d_id ,avg(age) from employee group by d_id having age>24;   

    　　步骤3 由于我们只需要部门名称,而跟部门名称相关的项就只有部门id,所以wo们只留下d_id字段

    　　sql语句---> select d_id from employee group by d_id having avg(age)>24;

    　　步骤4 查询部门表,找到id在上面这个查询结果内的内容

    　　sql语句---> select  name from department where id in(select d_id from employee group by d_id having avg(age)>24);

    +-----------------+
| name            |
+-----------------+
| 技术支持部      |
| 销售部          |
| 研发部          |
| 测试部          |
+-----------------+
    练习1 

    -- 接下来实战,不做详细拆分

    练习2:查看''技术部''员工姓名

    分析:需求 技术部 所有员工名字,那么内层语句就是部门,外层语句就是员工表

    　　sql语句---> select name from employee where d_id in(select id from department  where name = '技术支持部' );

    +--------+
| name   |
+--------+
| 海峰   |
| 永亮   |
| 小双   |
| 飞哥   |
| 康     |
+--------+
    练习2

    练习3 查看人员为空的部门　

    　　sql语句--->方式1 select name from department where id not in (select  d_id  from employee group by d_id );

    　　　　　　   方式2 select name from department where id not in (select  distinct d_id from employee ); 

    +-----------+
| name      |
+-----------+
| 规划部    |
| 电商部    |
+-----------+
    练习3

    比较运算符

    练习4 查询大于所有人平均年龄的员工名和年龄　　　　

    　　sql语句---> select name,age from employee where age > (select avg(age) from employee);

    mysql> select name,age from employee where age > (select avg(age) from employee);
+--------+------+
| name   | age  |
+--------+------+
| 海峰   |   27 |
| 永亮   |   29 |
| 鸿宇   |   26 |
| 章总   |   32 |
| 英杰   |   31 |
| 王博   |   27 |
| 小尹   |   28 |
| 飞哥   |   26 |
| 老于   |   40 |
+--------+------+
9 rows in set (0.00 sec)

mysql> select avg(age) from employee;
+----------+
| avg(age) |
+----------+
|  25.8889 |
+----------+
1 row in set (0.00 sec)
    练习4

    练习5:查询大于部门内平均年龄的员工name和age

    　　sql语句---> select age,name from employee inner join (select avg(age) as avg_age ,d_id from employee group by d_id) as a on employee.d_id = a.d_id where employee.age > a.avg_age;

    +------+--------+
| age  | name   |
+------+--------+
|   27 | 海峰   |
|   29 | 永亮   |
|   26 | 飞哥   |
|   32 | 章总   |
|   31 | 英杰   |
|   26 | 鸿宇   |
|   22 | 青青   |
|   25 | 会娟   |
+------+--------+
    练习5

    exists关键字

    exists 关键字表示存在,在使用exists关键字的时候,内层语句不返回查询记录,而是返回一个bool值True/False,

    当内层语句为True的时候,外层语句便会进行查询,否则,外层语句不进行查询.

    练习1 查询当部门id等于100的所有信息

    　　SELECT * FROM employee  WHERE EXISTS (SELECT d_id FROM employee WHERE d_id = 100);   # 当 d_id = 100 也就是内层查询为True的时候外层查询才会生效

    mysql> SELECT *FROM employee  WHERE EXISTS (SELECT d_id FROM employee WHERE d_id = 100);
+----+--------+--------+------+------+
| id | name   | sex    | age  | d_id |
+----+--------+--------+------+------+
|  1 | 海峰   | male   |   27 |  100 |
|  2 | 永亮   | male   |   29 |  100 |
|  3 | 小双   | female |   20 |  100 |
|  4 | 小东   | male   |   25 |  103 |
|  5 | 鸿宇   | male   |   26 |  103 |
|  6 | 青青   | female |   22 |  104 |
|  7 | 雨晴   | female |   18 |  104 |
|  8 | 会娟   | female |   25 |  105 |
|  9 | 刘晴   | female |   23 |  105 |
| 10 | 章总   | male   |   32 |  101 |
| 11 | 赵伟   | male   |   24 |  101 |
| 12 | 英杰   | male   |   31 |  102 |
| 13 | 王博   | male   |   27 |  102 |
| 14 | 小尹   | male   |   28 |  102 |
| 15 | 飞哥   | male   |   26 |  100 |
| 16 | 康     | male   |   24 |  100 |
| 17 | 红英   | female |   19 |  108 |
| 18 | 老于   | male   |   40 |  107 |
+----+--------+--------+------+------+
    练习1

    练习2 查询当部门id等于110的所有信息

    　　SELECT * FROM employee  WHERE EXISTS (SELECT d_id FROM employee WHERE d_id = 110);    内层语句查询weiFalse,外层查询不生效

    ...

    　　

    　　

    　　

    　　

                              

    CrazyShenldon
  • 相关阅读:
    第二次冲刺 03
    第二次冲刺 02
    第二次冲刺 01
    程序员修炼三部曲阅读笔记03
    程序员修炼三部曲阅读笔记02
    团队项目计划会议
    课堂练习—购书
    构建之法阅读笔记03
    学习进度条十二
    课堂作业找水王2
  • 原文地址：https://www.cnblogs.com/CrazySheldon1/p/10121444.html
Copyright © 2020-2023  润新知