数据表操作（三）

一、创建表

create table 表名(
    字段名 类型 约束,
    字段名 类型 约束
    ...
)
#事例
create table students(
    id int unsigned primary key auto_increment,
    name varchar(20),
    age int unsigned,
    height decimal(5,2)
)

二、删除表

#格式1，未判断表是否存在
drop table 表名
#格式2，先判断表是否存在，存在则删除
drop table if exists 表名

三、简单查询

#查询表里所有数据
select * from 表名

四、添加数据

#添加一行数据
#格式1，插入值的顺序与表中字段顺序对应
insert into 表名 alues(....)
#事例：插入一个学生信息数据
insert into students values(0,'小明',22,99)
#格式2，插入值的顺序与前面填入的字段顺序对应
insert into 表名(字段1,...) values(值1,...)
#事例：插入一个学生，只设置姓名
insert into students(name) values('老夫子')

#添加多行数据
#方式1：写多条insert语句，语句之间用英文分号隔开
insert into students(name) values('老夫子2');
insert into students(name) values('老夫子3');
insert into students values(0,'亚瑟2',23,167.56)
#方式2：写一条insert语句，设置多条数据，数据之间用英文逗号隔开
#格式一：insert into 表名 values(...),(...)...
#例：插入多个学生，设置所有字段的信息
insert into students values(0,'亚瑟3',23,167.56),(0,'亚瑟4',23,167.56)
#格式二：insert into 表名(列1,...) values(值1,...),(值1,...)...
#例：插入多个学生，只设置姓名
insert into students(name) values('老夫子5'),('老夫子6')

五、修改数据

#格式：update 表名 set 列1=值1,列2=值2... where 条件
#例：修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20
update Students set name='狄仁杰',age=20 where id=5

六、删除数据（不可恢复）

#格式：delete from 表名 where 条件
#例：删除id为6的学生数据
delete from students where id=6

注：正常项目中不会轻易使用delete语句进行删除，一旦删除，数据将无法恢复，一般使用逻辑删除方式

七、逻辑删除（可恢复）

1、表添加字段，数据是否删除的状态标识，一般起名isdelete，0代表未删除，1代表删除，默认值为0

alter table 表名 add 字段名称  字段类型(字段长短 选填)  not null(是否不可为空)  default 0(默认值)  comment '备注内容'(备注) 

2、当要删除某条数据时，只需要设置这条数据的isdelete字段为1
3、以后在查询数据时，只查询出isdelete为0的数据即可

#给学生表添加字段(isdelete)，默认值为0，如果表中已经有数据，需要把所有数据的isdelete字段更新为0
update students set isdelete=0;
alter table students add isdelete tinyint not null default 0;
#删除id为1的学生
update students set isdelete=1 where id=1
#查询未删除的数据
select * from students where isdelete=0

八、as别名

as表示为列标题起别名，也可省略不写

#不省略as
select studentNo as 学号,name as 名字,sex as 性别 from students
#省略as
select studentNo,name,sex from students

九、消除重复行

select后加distinct可以消除重复行，如果distinct后有多个列，则需要所有列都重复才会消除

select distinct 列1，列2.... from 表名；
#事例1,消除sex字段列有重复的记录
select distinct sex from students;
#事例2，消除sex和age字段列都重复的记录
select distinct sex,age from students;

十、条件

where后面支持多种运算符进行条件处理

比较运算，逻辑运算，模糊运算，范围查询，空判断。

1.比较运算

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

#查询学生中年龄大于15岁的记录
select * from students where age>15;

2.逻辑运算

• and
• or
• not

#查询非1班学生的最大年龄，最小年龄和平均年龄
select avg(age),min(age),max(age) from students where not class='1班'
#查询1班且姓名为小明的学生信息
select * from students where class='1班' and name='小明'
#查询1班和2班学生信息
select * from students where class='1班' or class='2班'

3.模糊查询

• like
• %表示任意多个字符（0个以上，包括0）
• _表示一个任意字符

#查询姓孙的学生
select * from students where name like '孙%'
#查询姓孙且名字是一个字的学生
select * from students where name like '孙_'
#查询名叫乔的学生
select * from students where name like '%乔'
#查询姓名含白的学生
select * from students where name like '%白%'

4.范围查询

1.in：表示在一个非连续范围内

#查询家乡是北京或上海或广东的学生
select * from students where hometown in('北京','上海','广东')

2.between...and...：表示在一个连续范围内，两边都是闭合状态

#查询年龄为18至20的学生
select * from students where age between 18 and 20

5.空判断

注：null与''是不同的，一个表示空，一个表示空字符串

判断空：is null

判断非空：is not null

#查询没有填写身份证的学生
select * from students where card is null
#查询填写了身份证的学生
select * from students where card is not null

6.排序

为了方便查看数据，实际页面会用到排序

select * from 表名 order by 列1 asc|desc,列2 asc|desc,...

asc：从小到大排列，即升序（asc可省略不写，默认升序）
desc：从大到小排序，即降序

#查询所有学生信息，按年龄从小到大排序
select * from students order by age
#查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo

7.聚合函数

为了快速得到统计数据，经常会用到如下几个聚合函数

注：聚合函数不能在where中使用

1.count(*)，计算总行数，括号中可以写*或列名

2.max(列)，求此列的最大值

3.min(列)，求此列的最小值

4.sum(列)，求此列的和

5.avg(列)，求此列的平均值

#查询学生总数
select count(*) from students
#查询女生的最大年龄
select max(age) from students where sex='女'
#查询1班的最小年龄
select min(age) from students where class='1班'
#查询北京学生的年龄总和
select sum(age) from students where hometown='北京'
#查询女生的平均年龄
select avg(age) from students where sex='女'

8.分组

按照字段分组，表示此字段相同的数据会被分到一个组中

分组后，分组的依据列会显示在结果集中，其他列则不会显示在结果集中

可以对分组后的数据进行统计，做聚合运算

select 列1,列2,聚合... from 表名 group by 列1,列2...

#查询各种性别的人数
select sex,count(*) from students group by sex
#查询各种年龄的人数
select sex,count(*) from students group by age

9.分组后筛选数据

having 和 where 进行筛选，区别为：

        where：对from后面指定的表进行数据筛选，属于对原始数据的筛选
        having：对group by的结果进行筛选

select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,...聚合...

#查询男生总人数
#方案一
select count(*) from students where sex='男' 
-----------------------------------
#方案二：
select sex,count(*) from students group by sex having sex='男'

10.获取部分行

数据量过大时，一页显示所有数据会影响性能，加载速度变慢

select * from 表名 limit start,count

 从start开始，获取count条数据，start从0开始

#查询前3行学生信息
select * from students limit 0,3
#每页显示m条数据，求第n页的数据
select * from students limit (n-1)*m,m

十一、其他 

#创建时间为2021-09-21至2021-09-22之间的数据，创建时间往后推2天
update students set createTime=DATE_ADD(createTime,interval 2 day) where createTime between '2021-09-21' and '2021-09-22'

#创建时间为2021-09-21至2021-09-22之间的数据，创建时间往前推2天
update students set createTime=DATE_ADD(createTime,interval -2 day) where createTime between '2021-09-21' and '2021-09-22'