mysql关键字与连表操作理论

最新推荐文章于 2022-05-07 22:53:21 发布
最新推荐文章于 2022-05-07 22:53:21 发布
阅读量131 收藏
点赞数
分类专栏： python 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46466247/article/details/105953687
版权

1、关键字

重点中的重点：关键字的执行优先级

	from
	where
	group by
	having
	select
	distinct
	order by
	limit
	
1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数
  • 前期表准备
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);

mysql> select * from emp;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 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 |
|  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 |
| 11 | 西西      | female |  18 | 2011-03-12 | sale                        | NULL         |    1000.37 |    402 |         2 |
| 12 | 乐乐      | female |  18 | 2016-05-13 | sale                        | NULL         |    3000.29 |    402 |         2 |
| 13 | 拉拉      | female |  28 | 2017-01-27 | sale                        | NULL         |    4000.33 |    402 |         2 |
| 14 | 僧龙      | male   |  28 | 2016-03-11 | operation                   | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation                   | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation                   | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation                   | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation                   | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
  • where 筛选条件
# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6;  两者等价
'''
mysql> select id,name,age from emp where id >=3 and id <= 6;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  3 | kevin |  81 |
|  4 | tony  |  73 |
|  5 | owen  |  28 |
|  6 | jack  |  18 |
+----+-------+-----+
'''
# 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);
'''
mysql> select * from emp where salary in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         | 18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
'''
# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	like
		%  匹配任意多个字符
		_  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
'''
mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name  | salary     |
+-------+------------+
| jason |    7300.33 |
| tom   | 1000000.31 |
| tony  |    3500.00 |
| owen  |    2100.00 |
+-------+------------+
'''
# 4.查询员工姓名是由四个字符组成的 姓名和薪资  char_length()   _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
'''
mysql> select name,salary from emp where name like  '____';
+------+----------+
| name | salary   |
+------+----------+
| tony |  3500.00 |
| owen |  2100.00 |
| jack |  9000.00 |
| sank | 10000.00 |
+------+----------+
'''
# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
'''
mysql> select * from emp where id not between 3 and 6;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 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 |
|  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 |
| 11 | 西西      | female |  18 | 2011-03-12 | sale                        | NULL         |    1000.37 |    402 |         2 |
| 12 | 乐乐      | female |  18 | 2016-05-13 | sale                        | NULL         |    3000.29 |    402 |         2 |
| 13 | 拉拉      | female |  28 | 2017-01-27 | sale                        | NULL         |    4000.33 |    402 |         2 |
| 14 | 僧龙      | male   |  28 | 2016-03-11 | operation                   | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation                   | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation                   | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation                   | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation                   | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'''
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
'''
mysql> select *from emp where salary not in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 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 |
|  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 |
| 11 | 西西      | female |  18 | 2011-03-12 | sale                        | NULL         |    1000.37 |    402 |         2 |
| 12 | 乐乐      | female |  18 | 2016-05-13 | sale                        | NULL         |    3000.29 |    402 |         2 |
| 13 | 拉拉      | female |  28 | 2017-01-27 | sale                        | NULL         |    4000.33 |    402 |         2 |
| 14 | 僧龙      | male   |  28 | 2016-03-11 | operation                   | NULL         |   10000.13 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation                   | NULL         |   19000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'''

# 7.查询岗位描述为空的员工姓名和岗位名  针对null不用等号 用is
select name,post from emp where post_comment = NULL;
'''
mysql> select name,post from emp where post_comment=NULL;
Empty set (0.00 sec)
'''
select name,post from emp where post_comment is NULL;
'''
mysql> select name,post from emp where post_comment is NULL;
+-----------+-----------------------------+
| name      | post                        |
+-----------+-----------------------------+
| jason     | 张江第一帅形象代言          |
| tom       | teacher                     |
| kevin     | teacher                     |
| tony      | teacher                     |
| owen      | teacher                     |
| jack      | teacher                     |
| jenny     | teacher                     |
| sank      | teacher                     |
| 哈哈      | sale                        |
| 呵呵      | sale                        |
| 西西      | sale                        |
| 乐乐      | sale                        |
| 拉拉      | sale                        |
| 僧龙      | operation                   |
| 程咬金    | operation                   |
| 程咬银    | operation                   |
| 程咬铜    | operation                   |
| 程咬铁    | operation                   |
+-----------+-----------------------------+
'''
  • group by分组
# 分组实际应用场景  分组应用场景非常的多
	男女比例
    部门平均薪资
    部门秃头率
    国家之间数据统计

# 1	按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 不再是组内的单个数据
	上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
	如果设置了严格模式 那么上述命令会直接报错 
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';

设置严格模式之后  分组 默认只能拿到分组的依据
select post from emp group by post;  
'''
mysql> select * from emp group by post;
ERROR 1055 (42000): 'day47.emp.id' isn't in GROUP BY
'''
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)

"""
什么时候需要分组啊？？？
	关键字 
		每个 平均 最高 最低 
	
	聚合函数
		max  #最大
		min	#最小
		sum	#求和
		count	#计数
		avg #平均
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
'''
mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 张江第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
'''
select post as '部门',max(salary) as '最高薪资' from emp group by post;
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 |
+-----------------------------+--------------+
'''
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱

# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
'''
mysql> select post,min(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | min(salary) |
+-----------------------------+-------------+
| operation                   |    10000.13 |
| sale                        |     1000.37 |
| teacher                     |     2100.00 |
| 张江第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
'''
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
'''
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.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
'''
mysql> select post,sum(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | sum(salary) |
+-----------------------------+-------------+
| operation                   |    84000.13 |
| sale                        |    13001.47 |
| teacher                     |  1062900.31 |
| 张江第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
'''
# 5.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
'''
mysql> select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post                        | count(id) |
+-----------------------------+-----------+
| operation                   |         5 |
| sale                        |         5 |
| teacher                     |         7 |
| 张江第一帅形象代言          |         1 |
+-----------------------------+-----------+
'''
select post,count(post_comment) from emp group by post;  null不行
'''
mysql> select post,count(post_comment) from emp group by post;
+-----------------------------+---------------------+
| post                        | count(post_comment) |
+-----------------------------+---------------------+
| operation                   |                   0 |
| sale                        |                   0 |
| teacher                     |                   0 |
| 张江第一帅形象代言            |                   0 |
+-----------------------------+---------------------+
'''
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
'''
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                                          |
+-----------------------------+------------------------------------------------+
'''
#进行拼接操作
select post,group_concat(name,'_DDD') from emp group by post;
'''
mysql> select post,group_concat(name,'_DDD') from emp group by post;
+-----------------------------+--------------------------------------------------------------------+
| post                        | group_concat(name,'_DDD')                                          |
+-----------------------------+--------------------------------------------------------------------+
| operation                   | 程咬铁_DDD,程咬铜_DDD,程咬银_DDD,程咬金_DDD,僧龙_DDD               |
| sale                        | 拉拉_DDD,乐乐_DDD,西西_DDD,呵呵_DDD,哈哈_DDD                       |
| teacher                     | sank_DDD,jenny_DDD,jack_DDD,owen_DDD,tony_DDD,kevin_DDD,tom_DDD    |
| 张江第一帅形象代言          | jason_DDD                                                          |
+-----------------------------+--------------------------------------------------------------------+
'''
#进行字段内容拼接
select post,group_concat(name,':',salary) from emp group by post;
'''
mysql> select post,group_concat(name,':',salary) from emp group by post;
+-----------------------------+--------------------------------------------------------------------------------------------------+
| post                        | group_concat(name,':',salary)                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
| operation                   | 程咬铁:17000.00,程咬铜:18000.00,程咬银:19000.00,程咬金:20000.00,僧龙:10000.13                    |
| sale                        | 拉拉:4000.33,乐乐:3000.29,西西:1000.37,呵呵:2000.35,哈哈:3000.13                                 |
| teacher                     | sank:10000.00,jenny:30000.00,jack:9000.00,owen:2100.00,tony:3500.00,kevin:8300.00,tom:1000000.31 |
| 张江第一帅形象代言          | jason:7300.33                                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
'''

concat不分组的时候用 

select concat('NAME:',name),concat('SAL:',salary) from emp;
'''
mysql> select concat('NAME:',name),concat('SAL:',salary) from emp;
+----------------------+-----------------------+
| concat('NAME:',name) | concat('SAL:',salary) |
+----------------------+-----------------------+
| NAME:jason           | SAL:7300.33           |
| NAME:tom             | SAL:1000000.31        |
| NAME:kevin           | SAL:8300.00           |
| NAME:tony            | SAL:3500.00           |
| NAME:owen            | SAL:2100.00           |
| NAME:jack            | SAL:9000.00           |
| NAME:jenny           | SAL:30000.00          |
| NAME:sank            | SAL:10000.00          |
| NAME:哈哈            | SAL:3000.13           |
| NAME:呵呵            | SAL:2000.35           |
| NAME:西西            | SAL:1000.37           |
| NAME:乐乐            | SAL:3000.29           |
| NAME:拉拉            | SAL:4000.33           |
| NAME:僧龙            | SAL:10000.13          |
| NAME:程咬金          | SAL:20000.00          |
| NAME:程咬银          | SAL:19000.00          |
| NAME:程咬铜          | SAL:18000.00          |
| NAME:程咬铁          | SAL:17000.00          |
+----------------------+-----------------------+
'''
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;

'''
mysql> select name,salary*12 from emp;
+-----------+-------------+
| name      | salary*12   |
+-----------+-------------+
| jason     |    87603.96 |
| tom       | 12000003.72 |
| kevin     |    99600.00 |
| tony      |    42000.00 |
| owen      |    25200.00 |
| jack      |   108000.00 |
| jenny     |   360000.00 |
| sank      |   120000.00 |
| 哈哈      |    36001.56 |
| 呵呵      |    24004.20 |
| 西西      |    12004.44 |
| 乐乐      |    36003.48 |
| 拉拉      |    48003.96 |
| 僧龙      |   120001.56 |
| 程咬金    |   240000.00 |
| 程咬银    |   228000.00 |
| 程咬铜    |   216000.00 |
| 程咬铁    |   204000.00 |
+-----------+-------------+
'''
  • 分组注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000;
select max(salary) from emp;  # 不分组 默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
	1 先求所有年龄大于30岁的员工
    	select * from emp where age>30;
    2 再对结果进行分组
     	select * from emp where age>30 group by post;
    
    select post,avg(salary) from emp where age>30 group by post;
'''
mysql> select post,avg(salary)from emp where age>30 group by post;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| sale    |   2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
'''
  • having分组之后的筛选条件
"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
		where age>30 
    	group by post
        having avg(salary) > 10000
        ;
'''
mysql> select post,avg(salary) from emp where age >30 group by post having avg(salary) >10000;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
'''
  • distinct去重

一定要注意 必须是完全一样的数据才可以去重！！！
一定不要将主键忽视了 有主键存在的情况下 是不可能去重的
#不能成功去重

[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]

select distinct id,age from emp; #包含主键，不会成功去重
'''
mysql> select distinct id,age from emp;

+----+-----+
| id | age |
+----+-----+
|  1 |  18 |
|  2 |  78 |
|  3 |  81 |
|  4 |  73 |
|  5 |  28 |
|  6 |  18 |
|  7 |  18 |
|  8 |  48 |
|  9 |  48 |
| 10 |  38 |
| 11 |  18 |
| 12 |  18 |
| 13 |  28 |
| 14 |  28 |
| 15 |  18 |
| 16 |  18 |
| 17 |  18 |
| 18 |  18 |
+----+-----+
'''
select distinct age from emp;

'''
mysql> select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+
'''
  • order by排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序  asc 该asc可以省略不写
也可以修改为降序     desc
"""
# 先按照age降序排  如果碰到age相同 则再按照salary升序排
select * from emp order by age desc,salary asc;
'''
mysql> select * from emp order by age desc,salary asc;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
|  3 | kevin     | male   |  81 | 2013-03-05 | teacher                     | NULL         |    8300.00 |    401 |         1 |
|  2 | tom       | male   |  78 | 2015-03-02 | teacher                     | NULL         | 1000000.31 |    401 |         1 |
|  4 | tony      | male   |  73 | 2014-07-01 | teacher                     | NULL         |    3500.00 |    401 |         1 |
|  9 | 哈哈      | female |  48 | 2015-03-11 | sale                        | NULL         |    3000.13 |    402 |         2 |
|  8 | sank      | male   |  48 | 2010-11-11 | teacher                     | NULL         |   10000.00 |    401 |         1 |
| 10 | 呵呵      | female |  38 | 2010-11-01 | sale                        | NULL         |    2000.35 |    402 |         2 |
|  5 | owen      | male   |  28 | 2012-11-01 | teacher                     | NULL         |    2100.00 |    401 |         1 |
| 13 | 拉拉      | female |  28 | 2017-01-27 | sale                        | NULL         |    4000.33 |    402 |         2 |
| 14 | 僧龙      | male   |  28 | 2016-03-11 | operation                   | NULL         |   10000.13 |    403 |         3 |
| 11 | 西西      | female |  18 | 2011-03-12 | sale                        | NULL         |    1000.37 |    402 |         2 |
| 12 | 乐乐      | female |  18 | 2016-05-13 | sale                        | NULL         |    3000.29 |    402 |         2 |
|  1 | jason     | male   |  18 | 2017-03-01 | 张江第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
|  6 | jack      | female |  18 | 2011-02-11 | teacher                     | NULL         |    9000.00 |    401 |         1 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation                   | NULL         |   17000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation                   | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation                   | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation                   | NULL         |   20000.00 |    403 |         3 |
|  7 | jenny     | male   |  18 | 1900-03-01 | teacher                     | NULL         |   30000.00 |    401 |         1 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'''


# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
	select post,avg(salary) from emp 
		where age>10 
    	group by post
        having avg(salary) > 1000
        order by avg(salary) desc
        ;

'''
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限制展示条数
select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5;
'''
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 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'''
select * from emp limit 5,5;

'''
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 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
'''
第一个参数是起始位置
第二个参数是展示条数
  • 正则
select * from emp where name regexp '^j.*(n|y)$';
'''
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、多表操作

  • 前期表准备
#建表
create table dep(
id int,
name varchar(20) 
);

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

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

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
  • 表查询
select * from dep;
'''
mysql> select * from dep;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
|  205 | 行政         |
+------+--------------+
'''
select * from emp;
'''
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   18 |    201 |
|  4 | nick  | male   |   28 |    202 |
|  5 | owen  | male   |   18 |    203 |
|  6 | jerry | female |   18 |    204 |
+----+-------+--------+------+--------+
'''
select * from dep,emp;  # 结果   笛卡尔积
'''
mysql> select * from dep,emp;
+------+--------------+----+-------+--------+------+--------+
| id   | name         | id | name  | sex    | age  | dep_id |
+------+--------------+----+-------+--------+------+--------+
|  200 | 技术         |  1 | jason | male   |   18 |    200 |
|  201 | 人力资源     |  1 | jason | male   |   18 |    200 |
|  202 | 销售         |  1 | jason | male   |   18 |    200 |
|  203 | 运营         |  1 | jason | male   |   18 |    200 |
|  205 | 行政         |  1 | jason | male   |   18 |    200 |
|  200 | 技术         |  2 | egon  | female |   48 |    201 |
|  201 | 人力资源     |  2 | egon  | female |   48 |    201 |
|  202 | 销售         |  2 | egon  | female |   48 |    201 |
|  203 | 运营         |  2 | egon  | female |   48 |    201 |
|  205 | 行政         |  2 | egon  | female |   48 |    201 |
|  200 | 技术         |  3 | kevin | male   |   18 |    201 |
|  201 | 人力资源     |  3 | kevin | male   |   18 |    201 |
|  202 | 销售         |  3 | kevin | male   |   18 |    201 |
|  203 | 运营         |  3 | kevin | male   |   18 |    201 |
|  205 | 行政         |  3 | kevin | male   |   18 |    201 |
|  200 | 技术         |  4 | nick  | male   |   28 |    202 |
|  201 | 人力资源     |  4 | nick  | male   |   28 |    202 |
|  202 | 销售         |  4 | nick  | male   |   28 |    202 |
|  203 | 运营         |  4 | nick  | male   |   28 |    202 |
|  205 | 行政         |  4 | nick  | male   |   28 |    202 |
|  200 | 技术         |  5 | owen  | male   |   18 |    203 |
|  201 | 人力资源     |  5 | owen  | male   |   18 |    203 |
|  202 | 销售         |  5 | owen  | male   |   18 |    203 |
|  203 | 运营         |  5 | owen  | male   |   18 |    203 |
|  205 | 行政         |  5 | owen  | male   |   18 |    203 |
|  200 | 技术         |  6 | jerry | female |   18 |    204 |
|  201 | 人力资源     |  6 | jerry | female |   18 |    204 |
|  202 | 销售         |  6 | jerry | female |   18 |    204 |
|  203 | 运营         |  6 | jerry | female |   18 |    204 |
|  205 | 行政         |  6 | jerry | female |   18 |    204 |
+------+--------------+----+-------+--------+------+--------+
'''


select * from emp,dep where emp.dep_id = dep.id;
'''
mysql> select * from emp,dep where emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
'''

"""
MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 
所以特地给你开设了对应的方法
	inner join  内连接
	left join   左连接
	right join  右连接
	union		全连接
"""
# inner join  内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分
'''
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
'''
# left join   左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL
'''
mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | 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         |
+----+-------+--------+------+--------+------+--------------+
'''
# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL
'''
mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 行政         |
+------+-------+--------+------+--------+------+--------------+
'''

# union		全连接  左右两表所有的数据都展示出来
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

'''
mysql> select * from emp left join dep on emp.dep_id = dep.id
    -> union
    -> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | 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         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 行政         |
+------+-------+--------+------+--------+------+--------------+
'''
  • 子查询
"""
子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
	1 先获取部门的id号
    2 再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name = '人力资源';
    
    select name from emp where dep_id in (200,201);
    
    
    select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

'''
mysql> select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   18 |    201 |
+----+-------+--------+------+--------+
'''

总结

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联

"""
多表查询就两种方式
	先拼接表再查询
	子查询 一步一步来
"""
听说这有个小白
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL —— 多表联合查询
weixin_46540606的博客
01-28 1673
介绍 多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候，需要显示的数据来自多张表，多表查询有以下分类： 交叉连接查询： select * from A,B； 内连接查询(使用的关键字inner join --inner可以省略) #隐式内连接(SQL92标准) select * from A,B where 条件; #显示内连接(SQL99标准) select * from A inner join B on 条件; 外连接查询(使用的关键字outer join -
MySQL-连表查询详
Meiko记录
12-27 5979
MySQL连表查询是一把双刃剑，一方面通过连表查询可以简化SQL的数量；但另一方面如果连接的表数据量较大，或者连接的表较多，其产生的笛卡尔积会成指数级增长，所以有些公司会禁用连表查询。 一、连表查询简介 连表查询即两张表或多张表联合查询，联合查询得到的结果称为笛卡尔积，假设A表中有n条记录，B表表中有m条记录，则它们联合查询得到的笛卡尔积为：n*m 大家可能听过很多种连接方式，比如内连接、外连接、自然连接、交叉连接、左连接以及右连接，但MySQL的连表查询其实只有3种：内连接、外连接、自然连接。 其中
MySql多表查询关键字与思路
weixin_64178950的博客
05-07 1541
今日内容概要1 > 查询关键字1.1 > 关键字之having过滤1.2 > 关键字之distinct去重1.3 > 关键字之order by排序1.4 > 关键字之limit分页1.5 > 关键字之regexp正则2 > 多表查询思路3 > 可视化软件之Navicat3.1 > 下载3.2 > 使用4 > 多表查询练习题 1 > 查询关键字 1.1 > 关键字之having过滤    having与wher
SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)
热门推荐
TBDBTUO的博客
03-20 5万+
连表总结 什么是连接表？ 多表查询原理：将多个表通过笛卡尔积形成一个虚表，再根据查询条件筛选符合条件的数据。 在关系数据库中，数据分布在多个逻辑表中。 要获得完整有意义的数据集，需要使用连接来查询这些表 中的数据。 SQL Server支持多种 连接包括 INNER JOIN:内连接,关键字在表中存在至少一个匹配时返回行。 left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。 right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的
mysql中多表之间的连接之join关键字
WritingPoems的博客
04-07 2160
在数据库中，表与表的连接是通过主外键来连接的，一张表的主键关联另一张表的外键。 在mysql中有三种连接方式 1、内连接（inner join） 内连接，理解为多种表关联，只显示多张表中匹配到的结构，没有匹配到的不会出现在查找结构中。 内连接又分为显示内连接和隐式内连接，区别在于有没有用join关键字。 隐式内连接（使用where实现） 现有如下两张表员工表（emp）和部门表（dept）： ...
3 MySQL多表查询与事务的操作 让初学者自学的
06-13
在MySQL中，多表查询通常涉及到表与表之间的关系。表与表之间有三种主要的关系：一对一、一对多和多对多。一对一关系意味着一个表的记录对应另一个表的单一记录，这通常通过添加唯一约束来实现。一对多关系是一个表...
MySql学习day03：数据表之间的连接、查询详解
09-09
在MySQL学习中，数据表之间的连接和查询是数据库操作的核心部分，对于理解和操作复杂的数据至关重要。本节将深入探讨这些概念。 首先，我们来了解主键。主键是表中的一个或一组字段，用于唯一标识每条记录。它的...
MySQL性能分析与优化调整
07-29
2. **EXPLAIN分析**：通过在SQL语句前添加`EXPLAIN`关键字，可以查看查询执行计划，分析表扫描方式、索引使用情况等，帮助优化查询结构。 3. **性能_schema**：MySQL 5.5及以上版本引入了性能_schema模块，用于收集...
Mysql Mysql学习资料
最新发布
01-13
- 关键字与约束：主键（PRIMARY KEY）、唯一键（UNIQUE）、外键（FOREIGN KEY）等概念及其应用。 2. **MySQL高级特性** - 视图（View）：创建和使用视图来简化复杂的查询，提供数据抽象。 - 存储过程（Stored ...
MySQL从入门到精通
m0_56581769的博客
03-08 1万+
Mysql各种关键字总结以及使用
mysql中一些常用关键字的使用以及联表查询（用学生考试举例）
weixin_45715012的博客
05-06 554
去重 distinct 作用：去除select查询出来的结果中重复的数据，只显示一条。 -- 查询一下有哪些同学参加了考试，成绩 select * from result -- 查询全部的考试成绩 select `StudentNo` from result -- 查询有哪些同学参加了考试 -- 发现重复数据 select distinct 'StudentNo' from result -- 去重 select用法 select VERSION() -- 查询系统版本 select 1...
MySQL的join关键字详解
pankhong blog
05-09 337
1.内连接 inner join 最频繁使用的和重要的联接是INNER JOIN。它也被称为一个等值连接。 INNER JOIN通过结合基于联接谓词两个表(表1和表2)列值创建一个新的结果表。查询比较表1中的每一行与表2中的每一行，找到所有满足联接谓词的行。 当联接谓词被满足时，对于每个匹配的一对A和B的列值被组合成一个结果行。 INNER JOIN基本语法如下： SELECT table1.column1, table2.column2... FROM table1 INNER JOIN ta
8、数据库
m0_45016196的博客
03-05 3890
数据库 1、数据库操作 1.查看当前系统中存在的数据库：SHOW DATABASES; 2.创建数据库：CREATE DATABASE 数据库名称; 3.选择数据库：USE 数据库名称; 4.删除数据库：DROP DATABASE 数据库名称; 2、数据表操作 1.创建表 CREATE TABLE table_name( col_name date_type [Constraints], ... col_namen date_type [Constraints] ); 2.设置主键约束 （1）
MYSQL 连表查询及别名用法
weixin_33717117的博客
09-13 3537
MYSQL连表查询是两个表之间的查询或者更多表之间查询，通过外键的方式查询所有的数据，在查询过程中产生字段的重复，为了区分这种情况数据库设计别名，有的表很长，也可以用别名。 1，连表查询 INNER JOIN ，LEFT JOIN，RIGHT JOIN INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的...
MySQL基础（三）：SQL查询语句中的关键字讲解、多表连接操作
一点一滴铺就人生
06-15 1248
下面是小凰凰的简介，看下吧！ ????人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活 ????学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中) ????您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！ 有相关技能问题可以写在下方评论区，我们一起学习，一起进步。 后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。 文章目录1、几个重要关键字的执行顺序2、where过滤3、group by分组、as别名（1）group by分
MySQL表的插入、更新、删除、多表更新和删除、复制：INSERT、UPDATE、DELETE、IGNORE/LOW_PRIORITY关键字
董哥的黑板报
04-20 1704
一、删除概述 删除的情景： 从表中删除特定的行 从表中删除所有行 ①删除特定的行 通过where设置筛选条件，来删除特定的行 delete from customers where cust_id=10006; ②删除所有的行 不加where，默认删除所有的行 delete from customers; 二、删除的其它事项 不要省略where子句 在使用d...
mysql关联查询关键字_MySQL数据高级查询之连接查询、联合查询、子查询
weixin_29790771的博客
01-19 2241
连接查询: 将多张表(>=2)进行记录的连接(按照某个指定的条件进行数据拼接)。连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.连接查询: join, 使用方式: 左表 join 右表；左表: 在join关键字左边的表；右表: 在join关键字右边的表连接查询分类：SQL中将连接查询分成四类:内连接,外连接,自然连接和交叉连接交叉连接：交叉连接: cross join, ...
mysql多表 关键字查询
freedom_fd的博客
07-07 559
mysql多表 关键字查询 表 rf_art_author rf_active rf_news rf_exhibition sql DROP VIEW IF EXISTS rf_news_search; CREATE VIEW rf_news_search AS SELECT id AS id,created_at AS created_at,title AS `value`,'art_author' AS search_type FROM rf_art_author UNION ALL SELECT id
MySQL全文本搜索与SQL基础操作详解
本章节深入探讨了02-结构化查询语言SQL（Structured Query Language）的介绍与基本操作，主要集中在MySQL环境下的应用。首先，我们关注全文本搜索，这是MySQL中的一种高级数据查询功能，特别适用于需要复杂文本匹配...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值