数据库语句操作

创建学生表：姓名(长度为10)， 年龄，身高(保留小数点2位)

create table students(
 id int unsigned primary key auto_increment,
 name varchar(20),
 age int unsigned,
 height decimal(5,2)
)

删除表

drop table 表名

drop table if exits 表名

数据操作

添加：

1、所有字段设置值，顺序需要对应：insert into 表名 values(...)

2、部分字段设置值：insert into 表名(字段1，字段2) values(值1，值2)

3、添加多条语句：

• 写多条insert语句，用英文分号“;”隔开

insert into student(name) values('lisa');
insert into student values(1,'lisa',22,168);

• 一条insert语句，设置多条数据，数据之前用英文分号“,”隔开

insert into student(name) values('lisa'),values('lili')

修改：

update 表名 set 列1=值1,列2=值2列3=值3... where 条件

删除：

delete from 表名 where 条件

逻辑删除：对于重要的数据，不能轻易执行delete语句进行删除，一旦删除，数据无法恢复，这时可以进行逻辑删除。

​ 1、给表添加字段，代表数据是否删除，一般起名isdelete，0代表未删除，1代表删除，默认值为0

​ 2、当要删除某条数据时，只需要设置这条数据的isdelete字段为1

​ 3、以后在查询数据时，只查询出isdelete为0的数据

-- 1、给学生添加字段(isdelete)，默认值为0，如果表中已经有数据，需要把所有数据的isdelete字段更
set isdelete=0
update students set isdelete=0

-- 2、删除id为1的学生
update students set isdelete=1 where id=1

-- 3、查询未删除的数据
select * from students where isdelete=0

查询：

1、查询所有字段：selece * from 表名

2、查询指定字段（在select后面的列名部分，可以使用as为列其别名，这个别名会出现在结果集中）：

select 列1，列2 from 表名

-- 表名.字段名：
select students.name,students.age from students

-- 可以通过 as 给表起别名：
select s.name,s.age from students as s

-- 如果是单表查询 可以省略表明：
select name,age from students

-- 使用as给字段起别名：
select studentNo as 学号,name as 名字,sex as 性别 from students

-- 消除重复行（distinct）
-- 在select后面列前使用distinct可以消除重复行
select distinct col1... from table

条件查询 where

使用where进行筛选

select col1,col2... from table where 条件

where后面支持多种运算符，进行条件的处理：比较运算、逻辑运算、模糊查询、范围查询、空判断

比较运算符

等于: =

大于: >

大于等于: >=

小于: <

小于等于: <=

不等于: != 或 <>

1、查询学号是'007'的学生的身份证号
select id from student where id='007'

2、查询'1班'以外的学生信息
select * from student where class!='1班'

3、查询年龄大于20的学生的姓名和性别
select name,age from student where age>20

4、查询lisa年龄
select age from student where name='lisa'

逻辑运算符：

and、or、not

-- 1、查询河南或河北的学生
select * from student where hometown='河南' or hometown='河北'

-- 2、查询'1班'的'上海'的学生
selece * from student class='1班' and hometown='上海'

-- 3、查询非20岁的学生
select * from student where not age=20

模糊查询 like

%表示任意多个字符

_表示一个任意字符

-- 1、查询姓名为两个字的学生
select * from student where name='__'

-- 2、查询姓百且年龄大于20的学生
select * from student where name='百%' and age>20

-- 3、查询学号以1结尾的学生
select * from student where stuno='%1'

范围查询 in between

in表示在一个非连续的范围内

between...and...表示在一个连续的范围内

-- 1、查询年龄在18或19或22的女生
select * from student where age in(18,19,22)

-- 2、查询年龄在20到25以外的学生
select * from student where age not in (20,25)

空判断 null

注意：null与“”是不同的

判空 is null

判非空 is not null

-- 1、查询没有填写身份证的学生
select * from student where idcard is null

-- 2、查询填写了身份证的学生
select * from student where idcard is not null

连接查询

多表

等值连接

方法一：select * from table1,table2 where table1.col1=table2.col1

方法二（内连接）：select * from table1 inner join table2 on table1.col=table2.col

左连接

select * from 表1 left join 表2 on 表1.列=表2.列

-- 查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select *
from students stu
left join scores sc on stu.studentNo = sc.studentNo
left join courses cs on cs.courseNo = sc.courseNo

右连接

select * from 表1 right join 表2 on 表1.列=表2.列

-- 查询所有课程的成绩，包括没有成绩的课程
select *
from scores sc
right join courses cs on cs.courseNo = sc.courseNo

-- 查询所有课程的成绩，包括没有成绩的课程，包括学生信息
select*
from scores sc
right join courses cs on cs.courseNo = sc.courseNo
left join students stu on stu.studentNo = sc.studentNo

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询：第一条select语句

主查询和子查询关系

​ 子查询是嵌入到主查询中

​ 子查询是辅助主查询的,要么充当条件,要么充当数据源

​ 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

标量子查询: 子查询返回的结果是一个数据(一行一列)

列子查询: 返回的结果是一列(一列多行)

行子查询: 返回的结果是一行(一行多列)

表级子查询: 返回的结果是多行多列

标量子查询

-- 查询班级学生平均年龄：
select avg(age) from students

-- 查询大于平均年龄的学生：
select * from students where age > 21.4167
select * from students where age > (select avg(age) from students);

列级子查询

-- 查询18岁的学生的成绩，要求显示成绩
-- 学生表中查询18岁的学生的学号
select studentNo from students where age=18

-- 成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006')
select * from scores where studentNo in (select studentNo from students where age=18)

行级子查询

-- 查询男生中年龄最大的学生信息
select * from students where sex='男' and age=(select max(age) from students)

select * from students where (sex,age)=('男',30)
select * from students where (sex,age) = (select sex,age from students where sex='男' order by age desc limit 1)

表级子查询

-- 查询数据库和系统测试的课程成绩
select  * 
from scores s
inner join 
     (select * from courses where name in ('数据库','系统测试')) c 
on s.courseNo = c.courseNo

子查询中特定关键字

in 范围

格式: 主查询 where 条件 in (列子查询)

any | some 任意一个

格式: 主查询 where 列 = any (列子查询)

在条件查询的结果中匹配任意一个即可,等价于 in

all

格式: 主查询 where 列 = all(列子查询) : 等于里面所有

格式: 主查询 where 列 <>all(列子查询): 不等一其中所有

select * from students where age in (select age from students where age between 18 and 20)

排序

为了方便查看数据，可以对数据进行排序

select * from table order by col1 asc|desc,col2 asc|desc,...

将行数据按照col1进行排序，如有某些行列1的值相同，则按照列2排序

默认按照列值从小到大排序

asc从小到大，升序

desc从大到小，降序

-- 1、查询所有学生信息，按班级从小到大排序，班级相同时，再按学号再按学号从小到大排序
select * from student order by class asc,stuno asc

聚合函数

聚合函数不能在 where 中使用

count(*)表示计算总行数，括号中写星与列名，结果是相同的

-- 查询学生总数
select count(*) from students;

max(列)表示求此列的最大值

-- 查询女生的最大年龄
select max(age) from students where sex='女';

min(列)表示求此列的最小值

-- 查询1班的最小年龄
select min(age) from student where class='1班'

sum(列)表示求此列的和

-- 例4：查询北京学生的年龄总和
select sum(age) from student where hometown='北京'

avg(列)表示求此列的平均值

-- 查询女生的平均年龄

select avg(age) from student where sex='女'

-- 1、查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from student 

-- 2、一班共有多少个学生
select count(*) from student

-- 3、查询3班年龄小于18岁的同学有几个
select count(*) from student where age<18 and class='3班'

分组

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

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

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

select col1,col2(聚合)...from table group by col1,col2...

-- 查询各种性别的人数：
select sex,count(*) from student group by sex

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

1、查询各个班级学生的平均年龄、最大年龄、最小年龄

select class,avg(age),max(age),min(age) from student group by age

分组后的数据筛选

select col1,col2,聚合...from table

group by col1,col2,col3...

having col1,...聚合...

having后面的条件运算符与where的相同

-- 1、查询男生总人数
select count(*) from student where sex='男'
select sex,count(*) from student group by sex having sex='男'

-- 2、查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
select class,avg(age),max(age),min(age) from student group by age having class not in('1班')

对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选

having是对group by的结果进行筛选

获取部分行

当数据量过大时，在一页中查看数据是一件麻烦的事情

select * from table limit start,count

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

查询前3行学生信息：select * from student limit 0,3

-- 1、查询第4到第6行学生信息
select * from student limit 3,3

分页

每页显示m条数据，求显示第n页的数据

select * from student limit (n-1)*m,m

求总页数：
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总数页
如果不整除则p2+1为总页数

-- 1、每页显示5条数据，显示每一页的数据
select *from student limit 0,5

自关联

设计省信息表：id，ptitle

设计市信息表：id，ctitle，proid

上面两个表结构相似，可以定义表areas：id，atitle，pid（综合上面两个表），省信息没有proid可以设置为null

因为省没有所属的省份，所以可以填写为null

城市所属的省份pid，填写省所对应的编号id

这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id

实战演练

= any 或者 =some 等价 in

!=all 等价于 not in

指路牛客