3、数据操作、数据查询

内容：

数据库操作 create drop

数据表操作 create drop alter rename as

查看表： show tables

查看表结构： desc 表名

字段的操作 modify 

添加字段： alter table 表名 add 字段名 类型

删除字段 ：  alter table 表名 drop 字段名

修改字段：  alter table 表名 modify 字段名 新的数据类型

 alter table 表名 change 旧字段名 新字段名 新的数据类型

 

改表名： alter table 表名 rename as 新表名

约束的设置  外键 foreign key (bid) references book(bid)

索引 create index 索引名 on 字段名

创建表时设置约束：

Create table 表名(

字段名 类型 primary key auto_increment,

字段名 类型  unique not null,

字段名 类型 default 默认值,

字段名 类型,

Foreign key (字段名) references 另外一张表名（主键字段名）

)

 

修改表添加约束：

1、适用于主键、唯一键、非空、默认

Alter table 表名modify 字段名 类型 约束关键词

1、使用外键、主键、唯一键

Alter table 表名 add Foreign key (字段名) references 另外一张表名（主键字段名）

 

删除约束

删除主键： Alter table 表名 drop primary key

删除外键： Alter table 表名 drop Foreign key 约束名

删除唯一键： Alter table 表名 drop index 约束名

删除非空和默认： alter table 表名 modify 字段名 新的数据类型 [写上需要保留的约束]

 

Sql

DDL：用来定义和管理数据库对象 create drop alter

DML：数据操作语言，用来操作数据库对象中的数据

Insert (添加数据)

Delete(删除数据)

Update(修改数据)

DQL：查询数据 select

常见报错信息：

 

因为插入的外键值不符合外键约束

 

Newsid的值不能为空

 

Column count ：字段数量

doesn't match ： 不匹配

value count  ： 值得数量

一定要注意：添加数据时，值列表和字段列表一一对应

代码：

use newsdb

show tables

创建学生表

create table student (

sid int primary key auto_increment,

sname varchar(20),

sex varchar(20),

lilun double,

jineng double,

birthday datetime,

age int ,

cid int

)

添加/录入数据

不指定字段

insert into student values(0,'刘亚帅','男',90,89,'1999-10-01',20,1)

insert into student values(0,'刘徐泽','男',90,89,'1999-10-01',20,1)

 

指定字段：

insert into student(sname,lilun,jineng,birthday,cid) values('田晓峰',100,80,'1997-08-08',2)

批量添加：

insert into student values(0,'徐泽浩','女',59,100,'1995-11-11',26,1)

,(0,'徐泽浩02','男',89,19,'1998-11-11',26,1)

,(0,'徐泽浩03','女',96,98,'1996-11-11',22,2)

,(0,'徐泽浩04','男',99,93,'1995-11-11',23,1)

查看: select * from student

 

修改数据： update 表名  set 字段名=值,字段名=值,字段名=值 [where 字段 关系运算符 值]

需求： 修改学号为2的学生性别为女，理论分99，技能分97

update student set sex='女',lilun=99,jineng=97 where sid=2

update student set jineng=90

删除数据： delete from 表名 [where 字段 关系运算符 值]

 

需求： 删除姓名为徐泽浩2的学生信息

delete from student where sname='徐泽浩02'

delete from student  #慎用，会删除表中全部数据

####################各种查询########################################

查询起别名：只是查询的时候起别名并不会修改表结构

select * from student

只查询姓名性别，并且起别名 as 可省略： select sname as 姓名,sex 性别 from student

 

条件查询是通过WHERE子句进行检索的查询方式

查询编号为1的学生信息 : select * from student where sid = 1

 

多条件查询是利用逻辑运算符把多个条件组合起来的条件查询

查询姓名是田晓峰，性别是男的学生信息： select * from student where sname='田晓峰' and sex='男'

 

in查询符合括号内条件的数据:

查询学号是2，3，4的学生信息：   select * from student where sid in (2,3,4)

 或者： select * from student where sid =2 or sid =3 or sid =4

 

区间查询： 字段名 between 值1 and 值2

需求：查询年龄再18~25之间的学生信息

select * from student where age >=18 and age <=25

或者：

select * from student where age between 18 and 25

 

增加数据： insert into 表名[(字段列表)] values(值列表)

删除数据:  delete from 表名 where 条件

修改数据:  update 表名 set 字段=值,字段=值  where 条件

查询数据 :

基本查询： select * from 表名

条件查询： select * from 表名 where 字段 关系运算符 值

多条件查询： select * from 表名 where 字段 关系运算符 值   逻辑运算符  字段 关系运算符 值

 

模糊查询：

模糊查询是使用SQL 通配符替代一个或多个字符的条件查询

SQL通配符：

_ ： 代表一个字符  where 字段 like 'ab_'   字段的值必须是三个字符而且以AB开头

%：代表任意个字符 where 字段 like 'ab%' 字段的值必须是以ab开头

 

需求：查询学生表中学生姓名包含‘徐泽’的学生信息

select * from student where sname like '%徐泽%'

需求：查询学生表中学生姓名以‘徐泽’开头的学生信息

select * from student where sname like '徐泽%'

查询学生表中田某某的学生信息

select * from student where sname like '田__'

 

需求：查询姓名包含晓并且性别是男的学生信息

select * from student where sname like '%晓%' and sex='男'

 

去重查询：  distinct 去掉SELECT查询返回的记录结果中重复的记录， 相同记录只返回一条

需求：查询学生表中所有的班级编号

select distinct cid from student

 

排序查询： order by 字段 asc|desc(降序)  : 一组数据按照升序或降序排列

需求： 查询所有学生信息，并且根据理论分降序排序

select * from student order by lilun desc

需求： 查询名字包含泽的学生信息并且按照出生日期升序排序

select * from student where sname like '%泽%' order by birthday

 

分页查询：limit MySQL查询语句中使用LIMIT子句限制结果集

语法： limit [值1,]值2

值1：索引值，索引从0开始

值2：代表查几条

 

需求： 查询学生表中学生信息，从第3条开始查询，查3条

select * from student limit 2,3

 

需求：查询学生表中学生信息，从第4条开始查询，查到6条

select * from student limit 3,3

 

需求： 查询学生表中学生信息，每页展示5条，查询第五页

查询的前4页已经展示了4*5=20条，那么第5页从21条开始查，下标就是20

select * from student limit 20,5

 

null查询： 查询性别是空的学生信息

select * from student where sex is null

 

null查询： 查询性别不是空的学生信息

select * from student where sex is not null

 

查询田晓峰的性别、生日、年龄

select sex,birthday,age from student where sname='田晓峰'

 

聚合函数是可以对一组值进行计算，并返回单个值的函数

count() : 查询总记录数

sum() : 查询某一组数据的和

avg() : 平均值

max() : 最大值

min() : 最小值

 

需求：

查询学生表中的记录数： select count(*),count(0),count(sid) from student where sex='男'

查询学生表中理论总分： select sum(lilun),avg(lilun),max(jineng),min(lilun) from student

 

需求： 查询最近出生的日期  ： select max(birthday) from student

查询最近出生的一条学生信息 ：

方式一： select * from student where birthday = (select max(birthday) from student)

方式二： select * from student  order by birthday desc limit 1

 

分组查询： group by 字段   分组查询是按照指定的字段分类汇总结果的查询方式 

需求: 查询每个班有多少人

先查询班级编号是1的学生数量： select count(*) from student where cid=2

 

再转换为分组： 查询每个组的： select cid,count(*) from student group by cid

 

需求： 查询每个班的理论最高分： select max(lilun) from student where cid=1

select cid,max(lilun),min(lilun),max(jineng) from student group by cid

 

需求：查询班级分数大于3人的班级编号和班级人数

 

需要先直到每个班多少人： select cid,count(*) from student group by cid

在查人数大于3人的 ： 分完组之后过滤使用having

select cid,count(*) from student group by cid having count(*)>3

 

总结：

增加语法： insert into 表名[(字段列表)] values(值列表)

删除语法： delete from 表名 where 条件

修改语法： update 表名 set 字段=值,字段=值 where 条件

查询：

基本查： select * from 表名

条件查： select * from 表名 where 字段 = 值

多条件查： select * from 表名 where 条件1 逻辑运算符 条件2

区间查询： select * from 表名 where 字段 between 值1 and 值2

排序查询： select * from 表名  [where 条件]  order by 字段 asc|desc

模糊查询： select * from 表名 where 字段 like ‘%_’

聚合函数：

count() :

Sum(字段)

Max(字段)

Min（字段）

Avg(字段)

in查询： select * from 表名 where 字段 in (值列表)

Null查询： select * from 表名 where 字段 is 【not】 null

分页查询： select * from 表名 [where 条件] limit [值1,]值2

分组查询 ： select 字段,聚合函数 from 表 group by 字段