MySQL 增改删查、多表查询

灰太狼 2023-02-13 14:54 18阅读 0赞

### 目录 ###

*   *   *  说明
         *  数据库、数据表常用操作
         *  记录的增改删查
         *  where子句
         *  查询常用
         *  多表查询
         *   *  union 结果集合并
             *  子查询
             *  join 连接查询
         *  说明
         *   *  别名
             *  多个条件
             *  空值判断、等于判断

### 说明 ###

mysql关键字不区分大小写

--注释
    
    /*注释*/

### 数据库、数据表常用操作 ###

select version();  --查看mysql版本
    
    
    show databases;  --查看所有的数据库
    use db_student;  --操作某个数据库之前要先用use指定要使用数据库
    drop database db_student;  --删除指定的数据库
    
    show tables;  #查看当前数据库中的表
    
    
    --删除表的3种方式
    drop table tb_student;  --删除表
    truncate table tb_student;  --清空表
    delete from tb_student; --删除表中的记录，如果不用where指定条件，会删除表中所有的记录

*  drop会删除记录、表定义（表结构），truncate、delete都只删除记录，仍保留表定义。
 *  truncate、delete只能对table使用，drop可以对table、view使用，delete可以只删除部分行（where筛选）。
 *  truncate、drop是一次性删除表的所有内容、对应的索引，不是作为事务执行，不能回滚，虽然可以恢复数据，但很麻烦；delete会作为事务执行，可以回滚。

记录数很多时，执行速度一般是 drop > truncate > delete

### 记录的增改删查 ###

select id,username,tel from tb_user;
    select * from tb_user;
    
    
    --value只能插入一条记录，values可以插入一条或多条记录，记录之间逗号隔开
    insert into tb_user (username,tel) value ('chy','188xxxxx'）; 
    insert into tb_user (username,tel) values ('chy1,'188xxxx'),('chy2','187xxxxx'); insert into tb_user values ('chy'，'188xxxxx'); --插入所有字段时可缺省字段名 update tb_user set username='chy',age=20 where id=1
    update tb_user set age=age+2 where id=1;  --在原值的基础上修改
    
    
    delete from tb_user where id=1

字符串、日期时间类型的值要引起来，mysql不分区单双引

### where子句 ###

where id=1
    
    where id<>1  -- <>是关系数据库通用的不等号
    where id!=1  --mysql也可以用!=
    
    -- > >= < <=
    
    
    -- 范围查询
    where id between 1 and 10  -- [1,10]，闭区间
    where id in(1,3,9)  -- id=1或id=3或id=9
    
    
    where id < all(1,3,9)  -- id要小于集合中的任何一个元素
    where id < all(select id from tb_user where age<20)  -- 一般和子查询搭配使用
    
    where id <any(1,3,9)  #id小于集合中的任意一个元素即可，id可以是[1,8]。一般和子查询搭配使用
    
    -- 与、或、非（not），优先级 not > and > or，可以通过加()明确优先级
    where score>60 and score<90
    where score<60 or score>90
    where dep not in ("cs","math")
    
    
    -- 判断是否为null，不能用=、!=、<>来判断
    where score is null
    where score is not null
    
    -- 等于、不等于
    where score=100
    where score<>100  --mysql也可以用!=表示不等
    
    
    -- 模糊查询
    where name like '张%'  --以张开头
    where name like '%伟'  --以伟结尾
    where name like '%国%' --含有国
    
    %  -- 一个或多个字符
    _  -- 任意一个字符
    
    [abcd]  -- abcd中的任意一个字符，[]只代表一个字符，eg. '张[杰伟]' 匹配张杰、张伟
    [!abcd]  -- 只要不是abcd中的任意一个字符就行，eg. '张[!杰伟]' 只要不是张杰、张伟就ok
    [^abcd]  -- !也可以写成^

### 查询常用 ###

select distinct username from tb_user;  -- distinct 去重，如果结果集中有完全相同的记录，只保留一条
    
    select price*number from tb_order_item where id=1;  -- 数值型可以进行数学运算
    
    
    
    -- 聚合函数：对结果集进行统计、分析的函数。在聚合函数中，值是null的记录不参与统计
    select count(*) from tb_user;  -- 记录总数
    select count(username) from tb_user;  -- 某一列的记录数
    select count(distinct username) from tb_user;  -- 去重，重复的记录只算做一条
    
    select max(age) from tb_user;
    select min(age) from tb_user;
    
    select sum(score) from tb_student;
    select avg(score） from tb_student;
    
    select first(username) from tb_user;  -- 该列的第一个值
    
    select last(username) from tb_user;  -- 该列的最后一个值
    
    select sum(score)，avg(score) from tb_student;  -- 可一起使用
    
    
    
    -- 其它常用函数，as不能省略
    select if(gender,'男','女') as gender from tb_user;  -- 相当于三元运算符
    
    select ifnull(money,0) as money from tb_user；  -- 如为空，返回设置的默认值
    
    
    
    -- order by子句，结果集排序
    select * from tb_user order by age;  -- 默认按升序（asc）排列
    select * from tb_user order by age desc;  -- desc降序
    select * from tb_user order by name,age desc;  -- 多个排序关键字，前面的关键字字段值相同时，使用后面的关键字排序
    
    
    
    -- group by 子句，结果集分组
    select * from tb_student group by dep;  -- 把dep相同的记录分为一组，返回的是分好的组，不是记录
    
    select count(*） from tb_student group by dep;  -- 统计每个系的学生人数。单独使用group by意义不大，一般搭配聚合函数使用，统计每组的数据
    select avg(score） from tb_student group by class;  -- 统计每个班的平均分
    
    
    
    -- having子句，一般和group by搭配使用，用来对分组进行过滤
    select * from tb_student [where子句] group by class having class between 0 and 9; #查询[1,10]班的学生信息
    -- group by后面不能使用where，且where子句中不能直接使用聚合函数，having用于弥补where的不足
    -- group by以哪个字段进行分组，having就只能以哪个字段进行过滤，把不符合要求的组去掉
    
    
    
    -- 常见的分页方式有三种，一般使用第一种
    -- 1、使用数据库本身提供的分页，mysql的limit、oracle的top，最常用
    -- 2、对于主键自增且中间没有删除记录的，可以用where限制区间，eg.where id>0 and where id<10
    -- 3、返回全部记录，由客户端实现分页，结果集很大时内存、时间开销大
    
    -- 从第n条记录开始取，取m条返回。[n,n+m),n是结果集中的记录下标，从0开始
    select * from tb_user [where子句] [order by子句] limit n,m;
    -- 只有一个参数时，默认从0开始取，相当于limit 0,m
    select * from tb_user [where子句] [order by子句] limit m;
    
    
    -- 多个子句的书写、执行顺序
    select ... from ... [where子句]  [group by子句]  [having子句]  [order by子句]  [limit子句]
    -- 先执行select ... from ... [where子句] ，将匹配的记录放到结果集中
    -- 再group by对结果集中的记录进行分组
    -- having对分组进行过滤，去掉不满足条件的分组
    -- orderby排序
    -- limit只取出结果集的某个区间，返回给客户端
    
    
    -- 常用的
    select ...  from ...  [where子句]  [group by子句]  [having子句]
    select ...  from ...  [where子句]  [order by子句]  [limit子句]
    
    
    -- 别名
    select username as '用户名' from tb_user;   -- 字段别名，返回时用别名替换字段名
    select tu.username from tb_user tu;  -- 表别名，常用于多表查询

### 多表查询 ###

#### union 结果集合并 ####

如果多个select分别查表，select取的字段数、字段名、字段类型相同，可以使用union把多个结果集合并为一个结果集。  
一个select返回m条记录，另一个select返回n条记录，合并之后就是m+n条记录

-- 查询全校师生的id、name
    
    -- union默认会去重，如果合并得到的结果集中有重复的记录，只保留一条，distinct可以缺省
    select id，name from tb_student union [distinct] select id,name from tb_teacher;  
    
    -- all，保留全部记录，不去重
    select id，name from tb_student union all select id,name from tb_teacher;   
    
    -- 如果要合并多个结果集，两两之间union连接
    select语句1 union [distinct|all] select语句2 union [distinct|all] select语句3 ...

#### 子查询 ####

-- 在where中使用子查询
    -- eg.有2张表，tb_dep 一条记录即一个系的信息，tb_student 一条记录即一个学生的信息，查询计算系所有学生的学号、姓名
    select id,name from tb_student where dep_id=(select id from tb_dep where dep_name='计算机系'); 
    
    
    
    -- 在having中使用子查询
    -- eg. 查询大于平均分的学生成绩
    select * from tb_score group by id having score>(select avg(score) from tb_score)；  
    -- group by 主键，按主键分组，一个分组就是一条记录，对分组的过滤就是对记录的过滤
    
    
    -- where、having的过滤中有2个常用关键字：any、all
    where xxx > any(子查询)  #大于任一个即可
    where xxx > all(子查询)  #大于所有才行
    
    
    
    -- 在from中使用子查询
    select 列名 from (select子查询);  -- 把子查询的结果集作为一张临时表
    select 列名 from （select子查询）[别名] [子句]； -- 临时表的使用方式和表名一样。在5.7版本中，临时表必须要有表别名

#### join 连接查询 ####

**笛卡尔积**

2个集合相乘，得到的是笛卡尔积

{ A,B,C} * { a,b,c} = {  (A,a)，(A,b)，(A,c)，(B,a)，(B,b)，(B,c)，(C,a)，(C,b)，(C,c) }

即用一个集合的每个元素去乘另一个集合的每个元素

**sql99 连接查询语法**

select  表1中的列, 表2中的列  from
    
    表1 [别名]  [连接类型] join  表2 [别名] on 连接条件
    
    [where子句]
    
    [group by子句]
    
    [having子句]
    
    [order by子句]
    
    [limit子句]

sql 99的连接查询带有关键字join，称之为显式写法。在sql 99之前还有一个sql语法版本：sql 92，顾名思义，92年制定的标准。sql 92的连接查询不带join，也具有相同的功能，现在还能用，称之为隐式写法。

**根据连接类型可以把连接查询分为4类**

*  交叉连接
 *  内连接：又分为等值连接、非等值连接、自连接
 *  外连接：又分为左外连接（左连接）、右外连接（右连接）、全外连接（全连接，mysql不支持，oracle支持）
 *  自然连接

假设tb\_student有30条记录，tb\_score有20条记录（10人因缺考、缓考等原因暂时没有成绩）

-- 交叉连接，得到的是笛卡尔积，基本不用
    select st.*,sc.* from tb_student st cross join tb_score sc;
    select * from tb_student cross join tb_score;  -- 可以用一个*表示多表的所有字段
    -- 30*20=600，返回600条记录
    
    
    
    
    -- 内连接，以少的为准（2张表中都要有）
    select * from tb_student st [inner] join tb_score sc on st.id=sc.student_id;  -- join默认就是内连接，inner可以缺省
    -- 只返回20条记录，成绩为空的10人不要了
    
    -- on指定2张表的连接条件（关联关系），使用on对笛卡尔积中的记录进行筛选。如果是单张表的条件，比如 st.id>10，写在where中
    
    -- 等值连接：on中使用等号进行筛选、判断
    -- 非等值连接：on中使用>、>=、<、<=、between and...连接2张表（不使用等号）
    
    
    
    -- 内连接的隐式写法
    select * from tb_student st,tb_score sc where st.id=sc.student_id;  -- 在where中指定连接条件
    
    
    
    -- 内连接之自连接
    -- 顾名思义，自己连接自己进行查询。有时候表是关联到自身的，需要连接自身来查询
    -- eg. 员工表，员工和直属上司都是员工，都在一张表里。查询员工信息及其直属上司的姓名，superior_id是直属上司的id
    select te1.*，te2.name as superior_name from tb_employees te1 [inner] join tb_employees te2 where te1.superior_id=te2.id;
    -- 都是同一张表，取不同的别名来区分
    
    
    
    
    -- 外连接
    -- 内连接以少的为准（作为主表），外连接可以指定以哪张表为准（作为主表），更灵活
    select * from tb_student st left [outer] join tb_score sc on st.id=sc.student_id;
    -- outer可以省略不写，left是左外连接，right是右外连接，full是全外连接（mysql不支持，oracle支持）
    
    -- 左外连接以join左边的表为准（主表），右外连接以join右边的表为准，全连接是2张表的记录都保留（在另一张表中找不到对应|关联的记录都保留）。
    
    
    
    
    -- 自然连接
    -- 自然连接可以看做是一种特殊的等值连接，会自动把2张表中相同的列（列名、数据类型都要相同）作为连接条件
    select * from tb_student natural join tb_score;
    -- 比如两张表都有id字段，会自动添加连接条件 on 表1.id=表2.id
    
    
    
    
    -- 多张表，写多个join就行
    select 表1的字段,表2的字段,表3的字段 from 表1 [别名]
    left join 表2 [别名] on 表1.id=表2.id
    left join 表3 [别名] on 表2.id=表3.id

左连接、右连接的左右是以join为标椎定义的，左连接以【left】 join左边的表为主表，右连接以【right】join右边的表为主表。

### 说明 ###

#### 别名 ####

w3c的标准：表名、列名使用别名时都要加as  
实际：表名、列名起别名时都可以不加as；有些数据库，eg. oracle，给表起别名时不能加as。

建议：给表起别名不加as，给列起别名可加可不加

#### 多个条件 ####

where中可以有多个and、or，同时存在and、or时，and的优先级高于or

#### 空值判断、等于判断 ####

判断是否为null要用is null、is not null，不能用=、!=、<>

等于、不等于用 =、<>，mysql等部分数据库也可以!=表示不等于。没有==这个符号。