MySQL

理论知识

修改数据表

1.修改表名

格式：Alter table 旧表名 rename [to] 新表名；

如下：Alter table tb_grade rename to grade;

2.修改字段名

格式：Alter table 表名 change 旧字段名 新字段名 新数据类型；

如下：Alter table grade change name username varchar(20);

3.修改字段的数据类型

格式： Alter table 表名 modify 字段名 数据类型;

如下：Alter table grade modify id int(20);

4.添加字段

  格式：Alter table 表名 add 新字段名 数据类型

如下：Alter table grade add age int(10);

5.删除字段

格式：Alter table 表名 drop 字段名；

如下：Alter table grade drop age;

6.修改字段的排列位置

格式：Alter table 表名 modify 字段名 1 数据类型 first|after 字段名2

如下：Alter table grade modify username varchar(20) first;  //将username，该为表的第一个字             段

Alter table grade modify id int(20) after grade;//将id字段插入到grade字段后面

7.添加数据

格式：Insert into 表名（字段名1，字段名2，….）

values（值1，值2，……）；

Create database school;

Use school;

Create table student(

id int(4),

name varchar(20) not null,

grade float

);

Insert into student(id,name,grade)values(1,’zhangsan’,98.5);

Insert into student(name,grade,id)values(’lisi’,95.5,2);

（2）为表的指定字段添加数据

Insert into 表名(字段1，字段2，…)values（值1，值2，…）

Insert into student(id,name)values(4,’zhaoliu’);

Insert into student(id,grade)values(5,97);//name字段没有指定默认值，并且添加了非null约束。

（3）同时添加多条记录

Insert into 表名[（字段名1，字段名2，…）]

values（值1，值2，…），（值1，值2，…），…

Insert into student values

(6,’lilei’,99),

(7,’hanmeimei’,100),

(8,’lihua’,50);

//如果指定字段，则可以只为指定字段添加值

8.更新数据

Update 表名

set 字段名1=值1[，字段名2=值2，…]

[where 条件表达式]

Update student set name=‘laozhang’,grade=50 where id = 1;

更新全部数据

如：Update student set grade = 80;

9.删除数据

1.Delete删除部分数据

删除部分数据是指根据指定条件删除表中的某一条或者某几条记录，需要使用where子句来指定删除记录的条件。

Insert into student values(11,’xiaowang’,80);

Select * from student where id = 11;

Delete from student where id = 11;

Delete from student where id > 5;

2.Delete删除全部数据

在delete语句中如果没有使用where子句，则会将表中的所有记录都删除

Select * from student;

Delete from student;

二 单表查询

1.在select语句中指定所有字段

Select 字段1，字段2，…from 表名

Create table student2(

id int(3) primary key auto_increment,

name varchar(20) not null,

grade float,

gender char(2)

); //插入一些数据

Select id,name,grade,gender from student2;

2.在select语句中使用星号（“*”）通配符代表所有字段

Select * from 表名；

Select * from student2

2.按条件查询

查询student2表中id为4的学生姓名：

Select  id,name from student2 where id = 4;

（1.）带in关键字

查询student表中id值为1、2、3的记录：

Select * from student2 where id in (1,2,3);

查询student表中id值不为1、2、3的记录：

Select * from student2 where id not in (1,2,3);

（2）带between and 关键字的查询

查询student表中id值在2~5之间的学生姓名

select * from student where id between 2 and 5;

（3）空值查询

查询student2表中gender为空值的记录

Select * from student where gender is null；

查询student2表中gender不为空值的记录

Select * from student where gender is not null；

（4）带distinct关键字的查询

查询student2表中gender字段的值，要求不能出现重复数据：

Select distinct gender from student2；

（5）带like关键字的查询

1.百分号（%）通配符

匹配任意长度的字符串，包括空字符串。

查找student表中name字段以字符“z”开头的学生id：

Select id,name from student2 where name like ‘z%’;

查找student表中name字段以字符“z”开头，以“n”结尾的学生id：

Select id,name from student2 where name like ‘z%n’;

查找student表中name字段包含字符“y”的学生id：

Select id,name from student2 where name like ‘%y%’;

2.下划线（_）通配符

查询student2表中name字段值以字符串“zhan”开始，以字符串“san”结束，并且两个字符串之间只有一个字符的记录：

Select * from student2 where name like ‘zhan_san’;

查询student2表中name字段值包含8个字符，并且以字符串“san”结束的记录：

Select * from student2 where name like ‘_____san’;

注意：查询student表中name字段值包括“%”的记录：（\%）

select * from student where like ‘%\%%’

（6）带and关键字的多条件查询

查询student2表中id字段值在1,2,3,4之中，name字段值以字符串“san”结束，并且grade字段值小于80的记录：

Select id,name,grade,gender from student2

where id in(1,2,3,4) and name like ‘%san’ and grade <80;

（7）带or关键字的多条件查询

查询student2表中id字段值小于3，或者gender字段值为“女”的学生姓名：

Select id,name,gender from student2 where id<3 or gender=‘女’;

Or和and一起使用的情况

注意：and的优先级高于or，因此当两者在一起使用时，应该先运算and两边的条件，再运算or两边的条件

查询student2表中gender字段值为“女”或者gender字段值为“男”，并且grade字段值为100的学生姓名：

Select name,grade,gender from student2 where gender = ‘女’ or gender = ‘男’ and grade=100;

三 聚合函数

实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值，最小值，平均值等，为此，MySql中提供了一些函数来实现这些功能。

1.count()函数

Count()函数用来统计记录的条数

Select count(*) from 表名

2.sum()函数

Sum()是求和函数，用于求出表中某个字段所有值的总和

Select sum(字段名) from 表名；

3.avg()函数

Avg()函数用于求出某个字段所有值的平均值

Select avg(字段名) from 表名

4.max（）函数

Max（）函数是求最大值的函数，用于求出某个字段的最大值

Select max(grade) from 表名；

5.min（）函数

Min（）函数是求最小值的函数，用于求出某个字段的最小值

Select min(grade) from 表名；

四 对查询结果排序

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户期望的。为了使查询结果满足用户的需求，可以使用order by对查询结果进行排序

Select 字段名1，字段名2，… from 表名 order by 字段名1, [asc|desc]，字段名2 [asc|desc]…

1.查出student2表中的所有记录，并按照grade字段进行排序

2.查出student2表中的所有记录，使用参数asc按照grade字段升序排列

3.查出student2表中的所有记录，使用参数desc按照grade字段降序排列

4.查出student2表中的所有记录，按照gender字段的升序和grade字段的降序进行排列

五 分组查询

在对表中数据进行统计时，也可能需要按照一定的类别进行统计。在mysql中可以使用group by按某个字段或者多个字段中的值进行分组，字段中值相同的为一组：

Select 字段名1，字段名2，…from 表名

group by字段名1，字段名2，..[having 条件表达式]

1.单独使用group by分组

Select * from student2 group by gender；

每个分组只返回一条结果

2.group by和聚合函数一起使用

Group by和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中最大值、最小值、平均值等

将student2表按照gender字段值进行分组查询，计算出每个分组中各有多少名学生：

Select count(*) ,gender from student2 group by gender；

3.group by和having关键字一起使用

Having关键字和where关键字的作用相同，都用于设置条件表达式对查询结果进行过滤，两者的区别在于，having关键字后可以跟聚合函数，而where不能，通常情况下having关键字都和group by 一起使用，用于对分组后的结果进行过滤。

将student2表按照gender字段进行分组查询，查询出grade字段值之和小于300的分组：

Select sum(grade),gender from student2 group by gender having sum(grade)<300;i

六 使用limit限制查询结果的数量

查询student2表的前4条记录：

Select * from student2 limit 4;

七 为表和字段取别名

1.为表取别名

为student2表起一个别名，并查询student2表中gender字段值为“女”的记录

Select * from student2 as s2 where s2.gender=‘女’;

2.为字段取别名

查询student2表中的所有记录的name和gender字段值，并为这两个字段起别名为stu_name和stu_gender：

Select name as stu_name,gender as stu_gender from student2;

具体实践

2、创建如下表

a )插入数据，使表内容如下所示

b）修改李四的SERVLET为65、JSP为73

c）查询并显示ID、USERNAME这两个字段的值

d）将c的结果用下图所示别名显示

e）查询并以如下结果显示

f）查询并显示每个学生的SERVLET和JSP的总成绩

g）查询并显示不重复的ADDRESS字段的值

h）查询ID为2且姓名为李四的学生

i）查询ID为2，或者姓名为张三的学生

J） 查询SERVLET成绩大于70分的学生，并显示所有字段信息

K）查询SERVLET 成绩大于60分小于80分的学生，并显示所有字段信息

L）查询姓名不等于张三的记录，并显示所有字段信息

M）查询ADDRESS为空的记录，并显示所有字段信息

N）查询ADDRESS不为空的记录，并显示所有字段信息

O)查询李姓学生的所有信息

P）查询所有学生SERVLET总成绩

Q)查询所有学生SERVLET的平均分

R)查询SERVLET的最高分和最低分

S)查询前两条记录

T）将表按SERVLET由大到小显示

U）将表按JSP小到大显示

V)查询每个地区的人数

W)查询分组后区域人数超过1个人的区域

x)查询id是1,2,3的记录

X���3x�t�ݬ�