一、数据的管理 ：增、删、改、查

#创建一个新的数据库 mydb2

create database mydb2;

#查看下当前连接下所有数据库

show databases;

#切换到新创建的数据库下进行操作

use mydb2;

#创建表

create table student(

id int primary key auto_increment,

NAME varchar(20) not null,

grade int,

gender varchar(6)

);

desc student;

1.数据的“增”：插入 —— insert into

l 语法一：一次插入1行数据

insert into 表名(字段1,字段2,...字段n)

values(值1,值2,...值n);

例：

insert into student(name,grade,gender) values('zhangsan',100,'male');

select * from student;

insert into student(name,grade,gender) values('lisi',60,'male');

注意：

1、字段名和字段的值要一一对应。

2、id的值设置了 auto_increment自增约束，因此可以自动生成并增长（+1），不需要专门去输入。

3、字段的值若是字符串类型，则需要加单引号''。

l 语法二：一次插入多行数据

insert into 表名(字段1,字段2,...字段n)

values(第一行 值1,值2,...值n)，

(第二行 值1,值2,...值n)，

............

(第n行 值1,值2,...值n)，

 例：

insert into student(name,grade,gender)

values('xiaohong',80,'female'),

('xiaoming',79,'male'),

('xiaowang',70,'male');

2.数据的“删”：删除 —— delete  from

语法：

delete from 表名 [条件];

注意：条件为可选项，如果不加条件，则会将表里所有记录都删除（表结构被保留），如果加上条件，则只有满足条件的记录会被删除。

例：

#删除整张学生表中的所有记录

delete from student;

#删除姓名为tom的记录

delete from student where name='tom';

3.数据的“改“：更新 —— update ... set...

语法：update 表名 set 修改的内容 [条件];

注意：条件是可选项，如果不加条件，则表中所有记录都会更新，如果加上条件，则只有符合条件的记录才会被更新。

例：

#将所有人的成绩减去5分

update student set grade=grade-5;

#将猪八戒的成绩加上10分

update student set grade=grade+10 where name='猪八戒';

#将猪八戒的成绩加上5分，性别改为女生

update student set grade=grade+5,gender='女生' where name='猪八戒';

4.数据的“查”：查询 —— select...from...(*****)

创建了三张表：

xsb——学生表

　　xh 学号 --主键

　　xm 姓名

　　xb 性别

　　jq 籍贯

　　nl 年龄

　　bj 班级

　　sfzh 身份证号

　　zcrq 注册日期

kcb——课程表

　　kch 课程号 --主键

　　kcm  课程名

cjb——成绩表

　　kch 课程号      --外键

　　xh 学号 --外键

　　cj 成绩

 

2.1简单查询：——  select ... from ...

语法：select *|字段名1,字段名2  from 表名；

注意：从指定表内查出所有字段（*）或特定字段。

例：

#查询学生表所有记录

select * from xsb;

#查询学生表所有记录中的学号、姓名、性别、年龄、班级

select xh,xm,xb,nl,bj from xsb;

2.2.条件查询：—— where

语法：

select  *|字段名1,字段名2  

from 表名

where 查询条件;

注释：

select子句过滤出满足条件的列（字段）；

from子句确定数据来源于哪张表；

where子句过滤出满足条件的行（记录）；

例：

#查询成绩表中所有及格了的记录

select *

from cjb

where cj>=60;

#查询成绩表中不及格的课程号和成绩

select kch,cj

from cjb

where cj<60;

l 多条件查询：—— and、or、not

逻辑运算符：与、或、非

与：并且的意思 —— and

或：或者的意思 —— or

非：取反的意思 —— not

例：

#查询学生表姓名是张三或李四的信息

SELECT *

from xsb

where xm='张三' or xm='李四';

#查询学生表中年龄是<=20岁的男生的信息

select *

from xsb

where nl<=20 and xb='男';

#查询除了张三以外的其他学生的信息

select *

from xsb

where not xm='张三';

#查询除了张三、李四以外的其他学生的信息

select *

from xsb

where not (xm='张三' or xm='李四');

（————与上面的sql等价————）

select *

from xsb

where not xm='张三' and not xm='李四';

l 模糊查询：——  like

模糊查询的常用的通配符：%，_

%：表示此处有0~n个字符。

_：表示此处有1个字符。

例：

#查询张姓学员的信息

select *

from xsb

where xm like '张%';

l 集合查询：—— in

例：

#查询学生表姓名是张三或李四的信息

select *

from xsb

where xm in('张三','李四','王欣');

#查询学生表中籍贯是北上广的信息

select *

from xsb

where jg in('北京','上海','广州');

#查询学生表中籍贯不是北上广的信息

select *

from xsb

where jg not in('北京','上海','广州');

l 空值查询： —— is null、is not null

例：

#查询成绩表中没有成绩的信息

select *

from cjb

where cj is null;

#查询所有 有成绩表的学生学号和课程号

select xh,kch

from cjb

where cj is not null;

l 范围查询： —— between...and...

注意：小值放前面，大值放后面。

例：

#查询20(含）~24（含）之间的学生信息

select *

from xsb

where nl between 20 and 24;   

（——与上面的sql等价——）

select *

from xsb

where nl>=20 and nl<=24;

2.3排序显示：—— order by ... asc/desc

asc：升序（默认，不写就是默认asc）

desc：降序

#查询学生表内的记录，按年龄 升序排列

select *

from xsb

order by nl;

（——与上面的sql等价）

select *

from xsb

order by nl asc;

#查询学生表内的记录，按年龄 降序排列

select *

from xsb

order by nl desc;

#查询成绩，按成绩升序排序

select *

from cjb

order by cj;

#查询成绩不为空的，按成绩降序排序

select  *   

from cjb     -----1

where cj is not null    -----2

order by cj desc;    -----3

2.4聚合函数 —— count( )、sum( )、avg( )、max( )、min( )

count( )：统计个数

sum( )：求合

avg( )：求平均数

max( )：求最大值

min( )：求最小值

例：

#统计一共有多少学生

select count(*)

from xsb;

#统计学号为001号的学员，其平均成绩、最大成绩、最小成绩、成绩总分

SELECT avg(cj),max(cj),min(cj),sum(cj)

from cjb

where xh='001';

#统计学号为006号学员的平均成绩

SELECT avg(cj)

from cjb

where xh='006';

#统计所有学生的平均年龄

2.5分组查询： —— group by.. 、having

语法：

select 字段 -----5

from 表名       ------1

[where 查询条件]     -----2

[group by 分组字段]     -----3

[having 分组后的过滤条件]    ------4

[order by 字段 asc|desc];  -----6

group by：按某个字段做分组 （某个字段值相同的算作一组）每，各，统计。1、有聚合函数；2、查出多条记录；

having：对分组后的数据做进一步过滤

where 和having的区别？

————where：对分组前的数据做过滤；having：对分组后的数据做过滤。

#查询每个学员的平均分

SELECT xh,avg(cj)

from cjb

group by xh;

#查询有两门及以上不及格的学生的学号

#思考一：查询出所有不及格的学生的学号

select xh

from cjb

where cj<60;

#思考二：如何加上限定查询两门以上（不及格的）？———— 按xh分组，统计个数，个数>=2的记录

group by xh

having count(*)>=2;

#因此sql语句合起来为：

select xh

from cjb

where cj<60

group by xh

having count(*)>=2;

---------------------------------------------

#查每门课的平均成绩

select kch,avg(cj)

from cjb

group by kch;

#查每门课的平均成绩,其中平均成绩不及格的课程及成绩

select kch,avg(cj)

from cjb

group by kch

having avg(cj)<60;