MySQL表查询关键字

对表的SQL语句补充

• 修改表的名字

  alter table 旧名字 rename 新名字；
  

• 新增字段

  alter table 表名 add 字段 字段类型(数字) 约束条件;
  alter table 表名 add 字段 字段类型(数字) 约束条件 after 已经存在的字段名;  # 在已经存在的字段后面新增
  alter table 表名 add 字段 字段类型(数字) 约束条件 first;  # 字段信息新增到第一行
  

• 修改字段

  alter table 表名 change 旧字段 新字段 新字段类型(数字) 约束条件;
  alter table 表名 modify 字段名 字段类型(数字) 约束条件;
  

• 删除字段

  alter table 表名 drop 字段名；
  

整体知识实操：

mysql> create table t1(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.06 sec)

mysql> alter table t1 rename ttt;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table ttt add pwd int default 123;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ttt add gender enum('male', 'female') after name;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ttt add age int not null first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ttt change pwd password int not null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ttt drop age;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ttt;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | int(11)               | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32)           | YES  |     | NULL    |                |
| gender   | enum('male','female') | YES  |     | NULL    |                |
| password | int(11)               | NO   |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

表的查询关键字

例题数据准备：

1.数据准备(直接拷贝)
	create table emp(
      id int not null unique auto_increment,
      name varchar(20) not null,
      sex enum('male','female') not null default 'male', #大部分是男的
      age int(3) unsigned not null default 28,
      hire_date date not null,
      post varchar(50),
      post_comment varchar(100),
      salary double(15,2),
      office int, #一个部门一个屋子
      depart_id int
    );
	#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
    ('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
    ('tom','male',78,'20150302','teacher',1000000.31,401,1),
    ('kevin','male',81,'20130305','teacher',8300,401,1),
    ('tony','male',73,'20140701','teacher',3500,401,1),
    ('owen','male',28,'20121101','teacher',2100,401,1),
    ('jack','female',18,'20110211','teacher',9000,401,1),
    ('jenny','male',18,'19000301','teacher',30000,401,1),
    ('sank','male',48,'20101111','teacher',10000,401,1),
    ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
    ('呵呵','female',38,'20101101','sale',2000.35,402,2),
    ('西西','female',18,'20110312','sale',1000.37,402,2),
    ('乐乐','female',18,'20160513','sale',3000.29,402,2),
    ('拉拉','female',28,'20170127','sale',4000.33,402,2),
    ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
    ('程咬金','male',18,'19970312','operation',20000,403,3),
    ('程咬银','female',18,'20130311','operation',19000,403,3),
    ('程咬铜','male',18,'20150411','operation',18000,403,3),
    ('程咬铁','female',18,'20140512','operation',17000,403,3);

查询关键字之select与from

select
	自定义查询表中字段对应的数据
from
	指定操作的对象(到底是哪张表 也可能是多张)

强调：

SQL语句的关键字编写顺序与执行顺序是不一致的!!!

eg:  select name from emp;
肯定是先支持from确定表 之后执行select确定字段

编写SQL语句针对select和from可以先写个固定模板

select * from 表名 其他操作
select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改

查询关键字之where筛选

1. 按条件表达式筛选

   条件运算符：< > <= >= = !=

2. 按逻辑表达式筛选

   逻辑运算符：and or not

3. 模糊查询

   like between in

模糊查询：

查询条件不清晰，不明确的情况下，这种查询我们统称为模糊查询
关键字：like(开启模糊查询的关键字)
关键符号：
	'%'：匹配任意个数的任意字符
	'_'：匹配单个个数的任意字符

举例说明：

1.查询id大于等于3小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);

3.查询员工姓名中包含o字母的员工姓名和薪资
select name, salary from emp where name like '%o%';

4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';

5.查询id小于3或者大于6的数据
select * from emp where not id between 3 and 6;

6.查询薪资不在20000，18000，17000范围的数据
select * from emp where not salary in (20000, 18000, 17000);

7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name, post from emp where post_comment is null;

查询关键字之group by分组

分组：按照一些指定的条件将单个单个的数据分为一个个整体。

关键字：group by(后面接列名，通常与聚合函数一起使用)

语法结构：

select	字段名	from	表名	group by	以什么字段分组;

• 聚合函数

  1.max(列名)>>>最大值
  2.min(列名)>>>最小值
  3.sum(列名)>>>求总和
  4.count(列名)>>>计数
  5.avg(列名)>>>求平均值

小技巧：

我们写SQL是否需要使用分组 可以在题目中得到答案
	每个、平均、最大、最小

举例说明：

获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;

补充:在显示的时候还可以给字段取别名
mysql> select post as '部门名', max(salary) as '最高工资' from emp group by post;
+-----------------------------+--------------+
| 部门名                      | 最高工资     |
+-----------------------------+--------------+
| operation                   |     20000.00 |
| sale                        |      4000.33 |
| teacher                     |   1000000.31 |
| 浦东第一帅形象代言          |      7300.33 |
+-----------------------------+--------------+
4 rows in set (0.00 sec)
# as也可以省略 但是不推荐省 因为寓意不明确

查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
mysql> select post, group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale                        | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher                     | sank,jenny,jack,owen,tony,kevin,tom            |
| 浦东第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.01 sec)

查询关键字之having过滤

where与having的功能其实是一样的 都是用来筛选数据
只不过where用于分组之前的筛选 而having用于分组之后的筛选
为了人为的区分 所以叫where是筛选 having是过滤

举例说明：

1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
mysql> select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)

查询关键字之distinct去重

前提

存在的数据必须是一模一样

注意：如果存在主键则无法去重

举例说明：

mysql> select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+-----+
7 rows in set (0.00 sec)

查询关键字之order by排序

注意：order by 在默认的情况下是升序(asc)，降序的设置是desc。

举例说明：

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| teacher                     | 151842.901429 |
| operation                   |  16800.026000 |
| 浦东第一帅形象代言          |   7300.330000 |
| sale                        |   2600.294000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

查询关键字之limit分页

limit分页：限制展示条数

mysql> select * from emp limit 3;  # 只有一个数字的时候为条数
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
|  1 | jason | male |  18 | 2017-03-01 | 浦东第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
|  2 | tom   | male |  78 | 2015-03-02 | teacher                     | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male |  81 | 2013-03-05 | teacher                     | NULL         |    8300.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
3 rows in set (0.00 sec)

查询工资最高的人的详细信息
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | tom  | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

如果出现两个数字情况：

mysql> select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
|  1 | jason | male |  18 | 2017-03-01 | 浦东第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
|  2 | tom   | male |  78 | 2015-03-02 | teacher                     | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male |  81 | 2013-03-05 | teacher                     | NULL         |    8300.00 |    401 |         1 |
|  4 | tony  | male |  73 | 2014-07-01 | teacher                     | NULL         |    3500.00 |    401 |         1 |
|  5 | owen  | male |  28 | 2012-11-01 | teacher                     | NULL         |    2100.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
5 rows in set (0.07 sec)

mysql> select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jenny  | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sank   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
| 10 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)

查询关键字之regexp正则

mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                        | post_comment | salary   | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
|  1 | jason | male |  18 | 2017-03-01 | 浦东第一帅形象代言          | NULL         |  7300.33 |    401 |         1 |
|  7 | jenny | male |  18 | 1900-03-01 | teacher                     | NULL         | 30000.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
2 rows in set (0.06 sec)

# 以j开头的所有任意字符，以n和y结尾的名字

多表查询思路

建表：

create table dep1(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp1(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;

insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

子查询

• 本质

  为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫作子查询。
  一般根据子查询的嵌入位置分为：where型子查询,from型子查询，exists型子查询。

• 理解

  将一张表的查询结果括号括起来当做另外一条SQL语句的条件
      eg:类似以日常生活中解决问题的方式
            第一步干什么
            第二步基于第一步的结果在做操作 ...
  

• 何时应用？

  某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。

• 举例说明

  查询jason的部门名称
  mysql> select name from dep1 where id=(select dep_id from emp1 where name='jason');
  +--------+
  | name   |
  +--------+
  | 技术   |
  +--------+
  1 row in set (0.05 sec)
  

连表查询

本质

从一张表中单独查询，称为单表查询
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字，
这种跨表查询，多张表联合起来查询数据，被称为连接查询

理解

先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据

根据表的连接方式分类：

• 内连接(inner join)

  • 等值连接
  • 非等值连接
  • 自连接

• 外连接

  • 左连接(left join)：以左表为基准 展示所有的数据 没有对应则NULL填充
  • 右连接(right join)：以左表为基准 展示所有的数据 没有对应则NULL填充

• 全连接(union)

  左连接+union+右连接

下面总结一下两表连接查询选择方式的依据：

1、 查两表关联列相等的数据用内连接。

2、 左表是右表的子集时用右外连接。

3、 右表是左表的子集时用左外连接。

4、 左表和右表彼此有交集但彼此互不为子集时候用全外连接（全连接）。

5、 求差操作的时候用联合查询。

注意：当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积
这种现象被称为：笛卡尔积现象（笛卡尔发现的，这是一个数学现象）

怎么避免笛卡尔积现象:

连接时加条件，满足这个条件的记录才会被筛选出来

mysql> select * from emp1,dep1 where dep1.id = emp1.dep_id;
+----+--------+--------+------+--------+-----+--------------+
| id | name   | gender | age  | dep_id | id  | name         |
+----+--------+--------+------+--------+-----+--------------+
|  1 | jason  | male   |   18 |    200 | 200 | 技术         |
|  2 | dragon | female |   48 |    201 | 201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 | 201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 | 202 | 销售         |
|  5 | owen   | male   |   18 |    203 | 203 | 运营         |
+----+--------+--------+------+--------+-----+--------------+
5 rows in set (0.05 sec)

但是我们可以发现，两张表结合在一起之后，都少东西了，因为有数据没有关联起来。

左连接：

mysql> select * from emp1 left join dep1 on dep1.id = emp1.dep_id;
+----+--------+--------+------+--------+------+--------------+
| id | name   | gender | age  | dep_id | id   | name         |
+----+--------+--------+------+--------+------+--------------+
|  1 | jason  | male   |   18 |    200 |  200 | 技术         |
|  2 | dragon | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 |  202 | 销售         |
|  5 | owen   | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry  | female |   18 |    204 | NULL | NULL         |
+----+--------+--------+------+--------+------+--------------+
6 rows in set (0.05 sec)

右连接：

mysql> select * from dep1 right join emp1 on dep1.id = emp1.dep_id;
+------+--------------+----+--------+--------+------+--------+
| id   | name         | id | name   | gender | age  | dep_id |
+------+--------------+----+--------+--------+------+--------+
|  200 | 技术         |  1 | jason  | male   |   18 |    200 |
|  201 | 人力资源     |  2 | dragon | female |   48 |    201 |
|  201 | 人力资源     |  3 | kevin  | male   |   18 |    201 |
|  202 | 销售         |  4 | nick   | male   |   28 |    202 |
|  203 | 运营         |  5 | owen   | male   |   18 |    203 |
| NULL | NULL         |  6 | jerry  | female |   18 |    204 |
+------+--------------+----+--------+--------+------+--------+
6 rows in set (0.00 sec)

总结

1.⾸先根据要做的需求,先分析⼀下,需要⽤到哪些查询,例如要⽤到关联查询,就先把用到的表列举出来。

2.前⾯的内容可以先放着不⽤谢,然后找三张表的之间的关联关系，这⾥要看的连接⽅式是⾃然连接,还是左连接,右连接等。

3.关联关系写好了,然后再写查询的内容。

注意：

1.应尽量避免在+where+⼦句中使⽤+!=+或+<>+操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描。

2.应尽量避免在+where+⼦句中使⽤+or+来连接条件，如果⼀个字段有索引，⼀个字段没有索引，将导致引擎放弃使⽤索引⽽进⾏全表扫描。

今日作业

1. 查询岗位名以及岗位包含的所有员工名字

mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale                        | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher                     | sank,jenny,jack,owen,tony,kevin,tom            |
| 浦东第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.01 sec)

2. 查询岗位名以及各岗位内包含的员工个数

mysql> select post, count(id) from emp group by post;
+-----------------------------+-----------+
| post                        | count(id) |
+-----------------------------+-----------+
| operation                   |         5 |
| sale                        |         5 |
| teacher                     |         7 |
| 浦东第一帅形象代言          |         1 |
+-----------------------------+-----------+
4 rows in set (0.01 sec)

3. 查询公司内男员工和女员工的个数

mysql> select sex,count(id) from emp group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+
2 rows in set (0.00 sec)

4. 查询岗位名以及各岗位的平均薪资

mysql> select post, avg(salary) from emp group by post;
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| operation                   |  16800.026000 |
| sale                        |   2600.294000 |
| teacher                     | 151842.901429 |
| 浦东第一帅形象代言          |   7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

5. 查询岗位名以及各岗位的最高薪资

mysql> select post, max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.01 sec)

6. 查询岗位名以及各岗位的最低薪资

mysql> select post as '岗位名', min(salary) as '最低薪资' from emp group by post;
+-----------------------------+--------------+
| 岗位名                      | 最低薪资     |
+-----------------------------+--------------+
| operation                   |     10000.13 |
| sale                        |      1000.37 |
| teacher                     |      2100.00 |
| 浦东第一帅形象代言          |      7300.33 |
+-----------------------------+--------------+
4 rows in set (0.00 sec)

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

mysql> select sex as '性别', avg(salary) as '平均薪资' from emp group by sex;
+--------+---------------+
| 性别   | 平均薪资      |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)

8. 统计各部门年龄在30岁以上的员工平均工资

mysql> select post, avg(salary) from emp where age > 30 group by post;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| sale    |   2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.01 sec)

9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于10000的部门，然后对平均工资进行排序

mysql> select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| sale                        |   2600.294000 |
| 浦东第一帅形象代言          |   7300.330000 |
| operation                   |  16800.026000 |
| teacher                     | 151842.901429 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)