数据表操作

补充：关系型数据库RDBMS，核心是以二维表存储数据
- 创建表
create table 表名(
字段名 类型 约束,
…
)

例： create table students( ID int unsigned primary key auto_increment,
name varchar(10), age int unsigned, height decimal(5,2) )

- 删除表
drop table 表名
drop table if exists 表名（不会报错）

- 增加数据
1.insert into 表名 values(0/default/null(占位使自增),‘字符串’,属性3,属性4,…) （属性与表一一对应,以students表为例）

2.指定字段插入数据
insert into 表名(字段名) values(字段信息)

3.一条语句插入多个数据
insert into 表名 values(字段信息1),(字段信息2),(字段信息3)

- 更新数据
update 表名 set 字段1=值1,字段2=值2…where 条件
例：update students age=21 where id=8
操作后将年龄修改了

- 删除数据
1.delete from 表名 where 条件
例：delete from students where id=6 操作成功后成功删除id为6的人

2.逻辑删除 不是真正删除。只是增加一个字段isdelete加以标记，1代表被删除，0代表没有被删除，删除某个数据是更新数据的isdelete。查询时只查询isdelete字段就可得到数据是否被删除。.

- 查询数据

1.基本查询语句

   select * from 表名(查询所有列）
  

2.别名查询

  select 字段1 as 别名1，字段2 as 别名2   from  表名 as 别名

3.去重查询

select distinct 字段1(,字段2...)  from 表名

4.条件查询

  select 字段1，字段2... from 表名 where 条件；

条件：

1. 比较运输
   =，>，<，>=，<=，!= equals <>

例：select card from studens where studentID=‘007’ 查询学号为7学生的身份证
select * from students where class != ‘1班’ 查询非一班学生信息
select name,sex from students where age >20 大于20岁学生的名字性别

2. 逻辑运算
   and ，or（或），not

例：select * from students where hometown='河南’ or hometown= '河北‘ 查询河南或河
北的学生 select * from students where class=’1班‘ and hometown=’上海‘
查询1班上海的学生 select * from students where not age=20 查询非20岁学生

4. 模糊查询
   like %(任意多个字符） _（任意一个字符）
   like 与% _配合使用

例：select * from students where name like ‘__’ 名字为两个字
select * from students where name like ‘百%’ and age>20 姓百且大于20岁
select *from students where studentID like ‘%1’ 学号以1结尾

5. 范围查询
   in(条件1，条件2…) equals or
   between 条件1 and 条件2 equals >= + <=

例：select * from students where age in(18,19,22) and sex='女’
查询18，19，22岁女
select * from students where not age between 20 and 25 查询20到25岁外的学生

7. 空判断
   注意：null与‘’不同，null为空，‘’为空字符串。

null的查询为    select * from students where card  **is**  null
 ‘’的查询为     select * from students where card =‘’

5.排序查询
**select * from 表名 order by 列1 asc|desc，列2 asc|desc....**
默认从小到大排序，asc从小到大升序排列，desc从大到小降序排列。
例：查询所有学生信息，按年龄你从小到大排，年龄相同时按学号从小到大排

select * from students order by age**,**studentID asc

注意：如果要排序中文，需要改变语言utf-8成gbk

select * from students order by convert(name using gbk)

6.聚合函数查询

1. 快速统计数量count()

. 例：快速统计表中学生数
select count(*) from students

2. 查询最大或最小的值max/min()

例：查询女生最大年龄的那个人
select max(age) from students where sex=‘女’

4. 查询列的总和sum()

5. 查询平均值 avg()

例：查询所有学生中最大，最小，平均年龄
select max(age) as 最大年龄,min(age) as 最小年龄 , avg(age) as 平均年龄 from students
一班共有多少名学生
select count( * ) from students where class=‘1班’
3班年龄小于18岁的同学
select count(*) from students where class='3班’ and age<18

7.分组查询
按照字段分组，表示此字段相同的数据会被放到同一个组中，与聚合函数配合使用。group by

例：1.查找各性别人数
select sex,count() from students group by sex
2.查询各个班级学生的平均年龄、最大和最小年龄
select class,avg(age) as 平均年龄，max(age) as 最大年龄，min(age) as 最小年龄 group by class
3.查询各班男女个数
select class,sex,count() from students group by class,sex

注意：having 是指在分组操作后再进行过滤的操作，必须跟在group by后

例：查询男生的个数（使用分组查询）
select sex,count(*) from students group by sex having sex='男‘

8.获取部分行查询（分页）
select * from 表名 limit start，count (从start开始，获取count条数据)

例：查询第四行到第六行
select * from students limit 4,3

实现分页功能
已知：每页显示m条数据，求：显示第n页数据

  select * from 表名 limit (n-1)*m,m

例：每页显示5条数据，显示每一页
select count(*) from students count==13
select * from students limit 0,5
select * from students limit 5,5
select * from students limit 10,5

9.多表查询

1. 等值连接：笛卡尔积加上where过滤，先连接再判断，产生临时表存放

 select * from 表名1,表名2 where 表名1.字段名 = 表名2.字段 where 条件

 例：查询学生信息及成绩
      select * from students as stu,scores as sco 
      where stu.studentID=sco.studentID

2. 内连接：inner join，先判断再连接，不会产生临时表**(多用）**

   select * from 表名1 inner join 表名2  on  表名1.字段=表名2.字段

若要指定字段则需要加上表名，即表名.字段来表示。

例：
查询学生及成绩
select * from students inner join scores on students.studentID=score.studentID

  查询王昭君学生所学数据库课程信息及成绩，显示姓名课程成绩
  select students.name,course.name,scores.score  from students
  inner join scores on students.studentID=scores.studentID
  inner join course on scores.courseID=course.courseID
  where name='王昭君’ and course.name='数据库‘

课后习题错题纠正
1.统计每个班级中每个性别的学生个数，并按照班级升序排序
  select class,sex,count(*) from students 
  group by class,sex order by class
  
  2.查询年龄最小学生的全部信息(其实是错误表示）
   select * from students order by age limit 1    
          

3. 左连接：把左边表所有数据进行显示

  select * from 表名1  left join  表名2 on 条件

例：显示所有学生成绩，包括没有成绩的学生，需要显示课程名
select * from students as stu
left join scores as sco on stu.studentID=sco.studentID
left join course as cou on cou.courseID=sco.courseID

4. 右连接：显示右边所有的数据

例：显示所有学生成绩，包括没有成绩的课程，需要显示学生
select * from scores
right join course on scores.courseID=course.course ID
left join student on course.studentID=student.studentID

10.自关联
定义：表中的某一列，关联了表中的另一列，但是他们的业务逻辑不一样。如：一个存储城市的表中，pid代表省的id。

例：查找河南省的所有城市
select * from areas as sheng inner join areas as shi
where sheng.id=shi.pid
and sheng.name=‘河南’
查找河南省所有区县(三级连接）
select * from areas as sheng inner join areas as shi inner join areas as qu
where sheng.id=shi.pid and sheng.name=‘河南’ and shi.id=qu.pid

11.子查询
关键字使用：
in
all
any/some:任何一个
select * from 表名 where 字段 >/=/<（不可用！=）(子查询结果）

例：查询大于平均年龄的学生
select * from students where age>(select avg(age) from students)
查询王昭君的成绩，要求显示
select * from scores where studentID=(select studentID from students where name=‘王昭君’)
查询王昭君的数据库成绩，要求显示成绩
select score from scores where studentID= (select studntID from students where name=‘王昭君’)
and courseID=(select courseID from course where name=‘数据库’)

1. 列子查询：返回结果为一列

select * from 表名1 where 字段1 in (select 字段1 from 表名2 where 条件（返回多个结果）)

例：select * from scores where studentID in(select studentID from
students where age=18)

2. 行子查询：使用不多，返回结果为一行

例：查询年龄最大男生信息
select * from students where (sex,age)=(‘男’,26)
改进：
select * from students
where (sex,age)=(select sex,age from students where sex=‘男’ order by age limit 1)

3. 表子查询：返回结果为多行多列,数据源需要起别名

例：查询数据库和系统测试的成绩
select * from scores inner join course on scores.courseID=course.courseID
where course.name in('数据库’,‘系统测试’)
改:
select * from scores
inner join (select * from course where name in('数据库’,‘系统测试’)) as c (必要步骤)
on scores.courseID=c.courseID