记录操作 子查询 三表联查

内容:复制表、记录详细操作、group by关键字、having关键字、order by关键字、limit、多表查询、多对多 三表联查、子查询。

复制表

 复制表结构+记录(不会复制:主键、外键和索引)

  create table new_table select * from old_table;

 只复制表结构

  create table new_table select * from old_table where 1=2; #条件为假,查不到任何记录

记录详细操作

增:
    insert into 表名[字段名] value(字段值...)
    into 可以省略
    字段名可以省略,如果写了后面插入的值必须和字段匹配,不写的话后面的值必须和表的结构匹配
    value 插入一条记录
    values 插入多条记录,用逗号隔开
    例如: insert into 表名[字段名] value(第一条字段字段值...),(第二条记录字段值....)

改:
    update 表名 set 字段名 = 新的值 where 条件;
    可以同时修改多个字段,用逗号隔开,用法和上面的values一样
    where可以省略
        有就修改满足条件的记录
        没有where 就全部修改
    
删:
    delete from 表名 where 条件;

 where 条件;

  有就删除满足条件的记录

  没有就全部删除

  如果要删除全部,建议使用 truncate table 表名

  delete 是逐行对比的删除效率低

  delete删除的行号会保留

查询

  完整的查询语句

  select distinct  *|字段名|聚合函数|表达式 from 表名

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

  from

  where

  group by #分组

  having  #过滤 只能跟在group by 后面使用 相当于where

  select

  distinct  #去重

  order by  #排序 默认 asc升序  desc设置成降序

  limit    #限制结果的显示条数

  1.找到表:from

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

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

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

  5.执行select

  6.去重

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

  8.限制结果的显示条数

group by关键字

  用于给数据分组,主要是为了方便管理和方便统计

  可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

  

GROUP BY关键字和GROUP_CONCAT()函数一起使用 

SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名

SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; 

GROUP BY与聚合函数一起使用

select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人

聚合函数:
  将一堆数据经过计算,得到一个数据
  sum() 求和
  avg() 求平均数
  max()/min() 求最大值 / 最小值
  count() 个数

什么时候需要使用分组 只要你的需求中带有 每个 字眼的就需要分组,例如:每个部门,每个岗位等

having

  用于对分组后的数据进行过滤

  having不会单独出现,都是和group by 一起出现的

  与where的区别:

    执行优先级从高到低:where > group by > having

    1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。

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

例子:
    查询平均工资大于5000的部门
    select dept,avg(salary) from froup by dept having avg(salary)>500;

order by

  默认参数 asc 升序 desc降序

  select * from emp order by salary;

  select * from emp order by salary desc;

  按多列排序:先按照age排序,如果年纪相同,则按照薪资排序

  SELECT * from employee ORDER BY age, salary DESC;

limit

  用于限制显示的条数

  select * from table1 limit 2,3: 显示的是表里3-5条数据

  limit 2,3  2 表示起始的条数 但是不会包括第二条 3表示起始位置2开始往后显示的条数

  常用语数据的分页展示 比如新闻的加载新的一页

  select *from emp limit 0,10; 第一页 页数减1 乘以条数 得到起始位置
  select *from emp limit 10,10; 第2页
  select *from emp limit 20,10; 第3页

多表查询

  

重点:外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

 

交叉连接:不适用任何匹配条件。生成笛卡尔积

  

  用where 筛选出正确的数据

  

内连接:只连接匹配的行

  找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果

   select *from table1 inner join table2 on table1.dept_id = table2.id;

  

外链接之左连接:优先显示左表全部记录

以左表为准,即找出所有员工信息,当然包括没有部门的员工
本质就是:在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee
  ->   left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技术         |
|  5 | liwenzhou  | 技术         |
|  2 | alex       | 人力资源     |
|  3 | wupeiqi    | 人力资源     |
|  4 | yuanhao    | 销售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

外链接之右连接:优先显示右表全部记录

以右表为准,即找出所有部门信息,包括没有员工的部门
本质就是:在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee
->  right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技术         |
|    2 | alex      | 人力资源     |
|    3 | wupeiqi   | 人力资源     |
|    4 | yuanhao   | 销售         |
|    5 | liwenzhou | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

全外连接:显示左右两个表全部记录

全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意:mysql不支持全外连接 full JOIN
强调:mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
查看结果
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

#注意 union与union all的区别:union会去掉相同的纪录

多对多 三表联查:

找出 xxx 这个老师 教过的学生信息
思路:
    第一步 到关系表中去查询哪些老师教过哪些学生(学生的id)形成了一个临时表
    第二步 将上一步得到 临时表 与 学生表 进行连接
    第三步 加上额外的筛选条件,老师的name是xxx

    select teacher.name as teacher,student.name as student from teacher
    inner join s_t on teacher.id = s_t.t_id
    inner join student on s_t.s_id = student.id
    where teacher.name = "xxx" ;

子查询:

1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、<等

带IN关键字的子查询

查询平均年龄在25岁以上的部门名
select id,name from department
    where id in
        (select dep_id from employee group by dep_id having avg(age) > 25);

查看技术部员工姓名
select name from employee
    where dep_id in
        (select id from department where name=‘技术‘);

查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

带比较运算符的子查询

比较运算符:=、!=、>、>=、<、<=、<>
查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name    | age  |
+---------+------+
| alex    | 48   |
| wupeiqi | 38   |
+---------+------+
2 rows in set (0.00 sec)

查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

带EXISTS关键字的子查询

  EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。True或False,当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询。

#department表中存在dept_id=203,Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205,False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

原文地址:https://www.cnblogs.com/layerluo/p/9648598.html

时间: 09-14

记录操作 子查询 三表联查的相关文章

高级数据操作--子查询

一.子查询——标量子查询 子查询的概念 思考: 如何得到php_student表中成绩最高的学生的记录? 1 select * from php_student order by score desc limit 1; 但是,存在一个逻辑问题,如果最高分有很多人,怎么办! 1 insert into php_student values 2 (null,'观世音','female',17,5000,'南海',100), 3 (null,'如来','male',17,6000,'西天',100);

Oracle---SQL子查询---详解

子查询其实就是指嵌入到其他语句中的select语句,也称其为嵌套查询. 值得注意的在DDL语句中应用子查询的时候子查询可以使用order by 子句. 但是在DML语句中的where子句,set子句中,子查询是不能使用order by子句的. 简单例子:查询工作和SMITH一样的所有员工的信息(不包含SMITH) select ename ,sal from emp e where job = (select job from emp where ename = 'SMITH') and e.e

数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引

1.    oracle介绍 ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一.比如SilverStream就是基于数据库的一种中间件.ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能:作为一个关系数据库,它是一个完备关系的产品:作为分布式数据库它实现了分布式处理功能.但它的所有知识,只要在一种机型上学习

SQL里的子查询

子查询必须遵守的规则 子查询必须位于圆括号内. 除非子查询里有多个字段让子查询进行比较,否则子查询的SELECT子句里只能有一个字段. 子查询里不能使用ORDER BY子句.在子查询里,我们可以利用GROUP BY子句实现ORDER BY功能. 返回多条记录的子查询只能与多值操作符(比如IN)配合使用. SELECT列表里不能引用任何BLOB.ARRAY.CLOB或NCLOB类型的值. 子查询不能直接被包围在函数里 操作符BETWEEN不能用于子查询,但子查询内部可以使用它. SELECT CO

数据库编程3 Oracle 子查询 insert update delete 事务 回收站 字段操作 企业级项目案例

[本文谢绝转载原文来自http://990487026.blog.51cto.com] <大纲> 数据库编程3 Oracle 子查询 insert update delete 事务 回收站 字段操作 企业级项目案例 实验所用数据表 子查询,解决一步不能求解 查询工资比scott高的员工信息: 子查询知识体系搭建: 解释3,查询部门是sales的员工信息: 方法1:子查询 [方法2]:多表: 优化考虑: 解释4[select],只能放单行子查询 解释4[from] 考题:显示员工姓名,薪水 解释

Oracle基本语法&amp;&amp;函数&amp;&amp;子查询&amp;&amp;分页查询&amp;&amp;排序&amp;&amp;集合操作&amp;&amp;高级分组函数

一.  数据库 手工---文件管理---数据库 DB:Database 数据库. DBMS:管理数据库的软件.(oracle) 主流关系数据库: 1.      Oracle 2.      DB2 3.      SQL Server 基本没人使 4.      MySQL  基本没人用,免费 Linux 开源,可以发现漏洞补上 Windows服务器会有补丁,数据易泄漏 eclipse 日食 数据表(Table): 表的行(Row):记录 表的列(Column):字段 二.  关系型数据库 一

详细讲述MySQL中的子查询操作 (来自脚本之家)

继续做以下的前期准备工作: 新建一个测试数据库TestDB: ? 1 create database TestDB; 创建测试表table1和table2: ? 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE table1 (   customer_id VARCHAR(10) NOT NULL,   city VARCHAR(10) NOT NULL,   PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CH

走向面试之经典的数据库基础:二、SQL进阶之case、子查询、分页、join与视图

一.CASE的两种用法 1.1 等值判断->相当于switch case (1)具体用法模板: CASE expression WHEN value1 THEN returnvalue1 WHEN value2 THEN returnvalue2 WHEN value3 THEN returnvalue3  ELSE defaultreturnvalue END (2)具体使用示例: 假设我们有一个论坛网站,其中有一张User表{ UId,Name,Level },Level是一个int类型,代

mysql子查询慢的问题

当你在用explain工具查看sql语句的执行计划时,若select_type 字段中出现"DEPENDENT SUBQUERY"时,你要注意了,你已经掉入了mysql子查询慢的"坑"...下面我们来看一个具体的例子 有这样一条查询语句: SELECT gid,COUNT(id) as count FROM shop_goods g1 WHERE status =0 and gid IN (SELECT gid FROM shop_goods g2 WHERE si

Mysql——子查询

子查询的位置: select 中.from 后.where 中.group by 和order by 中无实用意义. 子查询分为如下几类: 1,标量子查询:返回单一值的标量,最简单的形式. 2,列子查询:返回的结果集是 N 行一列. 3,行子查询:返回的结果集是一行 N 列. 4,表子查询:返回的结果集是 N 行 N 列. 可以使用的操作符:= > < >= <= <> ANY IN SOME ALL EXISTS 标量子查询:是指子查询返回的是单一值的标量,如一个数字