MySQL练习
通过windows服务管理器启动MySQL服务
|
1
|
开始-->运行-->输入services.msc命令-->启动MySQL服务
|
通过DOS命令启动MySQL服务
|
1
|
net start mysql
|
停止MySQL服务
通过windows服务管理器启动MySQL服务
|
1
|
开始-->运行-->输入services.msc命令-->停止MySQL服务
|
通过DOS命令停止MySQL服务
|
1
|
net stop mysql
|
数据库">登录MySQL数据库
通过DOS命令来登陆：
|
1
|
mysql -h 主机的地址 -u 用户名 -p 密码
|
使用MySQL Command Line Client 登录，它在你安装MySQL的目录中
数据库的基本操作
创建数据库的语法格式：
|
1
|
create databases 数据库名称 ;
|
例：创建一个名称为xiaoxu的数据库，SQL语句如下：
|
1
|
create databases xiaoxu ;
|
查看数据库的语法格式：
|
1
|
show databases ;
|
查看某个已创建好的数据库的语法格式：
|
1
|
show create databases 数据库名称 ;
|
修改数据库编码的语法格式：
|
1
|
alter database 数据库名称 default character set 编码方式 collate 编码方式_bin ;
|
例：将数据库xiaoxu 的编码修改为 gbk，SQL语句如下所示：
|
1
|
alter database xiaoxu default character set gbk collate gbk_bin ;
|
删除数据库的语法格式：
|
1
|
drop database 数据库名称 ;
|
数据表的基本操作
创建数据表语法格式：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
create table 表名
{
字段名 1,数据类型[完整性约束条件],
字段名 2,数据类型[完整性约束条件],
...
字段名 n,数据类型[完整性约束条件],
}
|
查看数据表的语法格式：
|
1
2
3
4
5
6
7
8
9
|
show create table 表名 ;
或者
describe 表名 ;
简写为
desc 表名 ;
|
修改数据表：
1. 修改表名的语法格式：
|
1
|
alter table 旧表名 rename [to] 新表名 ;
|
2.修改字段名的语法格式
|
1
|
alter table 表名 change 旧字段名 新字段名 新数据类型 ;
|
例：将数据表grade中的name字段改为username，数据类型保持不变
|
1
|
alter table grade change name username varchar(20) ;
|
3. 修改字段的数据类型的语法格式
|
1
|
alter table 表名 modify 字段名 数据类型 ;
|
例：将数据表grade中的id字段的数据类型由int(11)修改为int(20)
|
1
|
alter table grade modify id int(20) ;
|
4. 添加字段的语法格式
|
1
2
3
|
alter table 表名 add 新字段名 数据类型
[约束条件] [FIRST|AFTER已存在的字段名]
|
例：将数据表grade中添加一个没有约束条件的 int 类型的字段 age。
|
1
|
alter table grade add age int(10) ;
|
5. 删除字段的语法格式：
|
1
|
alter table 表名 drop 字段名 ;
|
6.修改字段的排列位置的语法格式：
|
1
|
alter table 表名 modify 字段名1 数据类型 first|after 字段名2
|
例1：将数据表grade的username字段修改为表的第一个字段
|
1
|
alter table grade modify username varchar(20) first ;
|
例2：将数据表grade的id字段插入到grade字段后面
|
1
|
alter table grade modify id int(20) after grade ;
|
7. 删除数据表的语法格式
|
1
|
drop table 表名 ;
|
添加、更新与删除数据的操作
添加数据
1. 在表中，为所有字段添加数据的insert语句有两种，分别如下：
insert 语句中指定所有字段名，其语法语法格式
|
1
2
3
|
insert into 表名(字段名1,字段名2, ...)
value(值1,值2, ...)
|
例：向student表中添加一条新记录，记录中 id 字段的值为 1，name字段的值为’zhangsan’，grade字段的值为98.5
假设已创建如下表：
|
1
2
3
4
5
6
7
8
9
|
create table student(
id int(4),
name varchar(20) not null,
grade float
);
|
向student表中插入一条数据，代码如下所示
|
1
2
3
|
insert into student(id,name,grade)
values(1,'zhangsan',98.5);
|
insert语句中不指定字段名，其语法格式
|
1
|
insert into 表名 values(值1,值2, ...)
|
注意：由于insert语句中没有指定字段名，添加值的顺序，必须和字段在表中已经定义的顺序相同
例:向已添加纪录的student表中添加一条新记录，记录中 id 字段的值为3，name字段的值为’wangwu’，grade字段的值为61.5，可参考第一种方法的例子
|
1
2
3
|
insert into student
values(3,'wangwu',61.5)
|
2. 为表中的指定字段添加数据，其语法格式
|
1
2
3
|
insert into 表名(字段名1,字段2, ...)
values(值1,值2, ...)
|
例：向student表中添加一条新记录，记录中 id 字段的值为 4，name字段的值为“zhaoliu”，grade字段不指定值
|
1
2
3
|
insert into student(id,name)
values(4,'zhaoliu') ;
|
insert语句的其它用法：
为表中指定的字段或者全部字段添加数据，其语法格式
|
1
2
3
|
insert into 表名
set 字段名1 = 值1 [,字段名2 = 值2, ...]
|
例：向student表中添加一条新记录，该条记录中 id 字段的值为5，name字段的值为’boya’，grade字段的值为 99
|
1
2
3
|
insert into student
set id = 5, name = 'boya', grade = 99 ;
|
为表中同时添加多条记录，其语法格式
|
1
2
3
4
5
6
7
|
insert into 表名 [(字段名1,字段名2, ...)]
values(值1,值2, ...),(值1,值2, ...),
...
(值1,值2, ...) ;
|
例：向student表中添加三条新记录。
假设已创建如下表：
|
1
2
3
4
5
6
7
8
9
|
create table student(
id int(4),
name varchar(20) not null,
grade float
);
|
添加新记录如下
|
1
2
3
4
5
6
7
|
insert into student
values (6,'lilei',99),
(7,'hanmeimei',100),
(8,'poly',40.5) ;
|
更新数据
更新表中的记录，其语法格式
|
1
2
3
4
5
|
update 表名
set 字段名1 = 值1 [,字段名2 = 值2, ...]
[where 条件表达式]
|
update更新部分数据
更新student标准那个id字段值为1的记录，将记录中的name字段的值更改为’caocao’，grade字段的值更改为50。
在更新数据前，首先使用查询语句查看id字段值为1的记录，执行结果如下
|
1
2
3
|
select * from student
where id = 1 ;
|
更新结果如下
|
1
2
3
4
5
|
select * from student
set name = 'caocao', grade = 50
where id = 1 ;
|
例2：更新student表中 id 字段值小于4的记录，将这些记录的grade字段值都更新为 100。
在更新数据前，首先使用查询语句查看 id 字段值小于 4 的记录，执行结果如下
|
1
2
3
|
select * from student
where id < 4 ;
|
更新结果如下
|
1
2
3
4
5
|
update student
set grade = 100
where id < 4 ;
|
update 更新全部数据
例：更新student表中全部记录，将grade字段值都更新为80
|
1
2
3
|
update student
set grade = 80 ;
|
删除数据
删除数据有两种方法：
第一种：delete语句
删除表中的记录，其语法格式
|
1
|
delete from 表名 [where 条件表达式]
|
delete删除部分数据
例：在student表中，删除id字段值为11的记录。
在删除之前，首先使用查询语句查看id字段值为11的记录，执行结果如下
select * from student
where id = 11 ;
delete删除全部数据
在delete语句中，若没用使用where子句，则会将表中的所有记录都删除
例：删除student表中的所有记录。
delete from student ;
第二种：truncate语句
truncate [table] 表名
例：删除student表中的所有记录
|
1
|
truncate
table student ; |
delete语句truncate语句的异同点
相同点：都能删除表中的所有数据的功能。
不同点：
delte语句：
delete语句是DML语句；
delete 语句后面可以跟where子句，通过指定where子句中的条件表达式只删除满足条件的记录；
delete语句删除表中所有记录后，再次向表中添加记录时，自动增加字段的值为删除时该字段的最大增加1；
使用delete语句时，每删除一条记录都会在日志中记录。
truncate语句：
truncate语句通常被认为是DDL语句；
truncate语句只能用于删除表中的所有记录；
truncate语句删除表中的数据后，再次向表中添加记录时，自动增加字段的默认初始值重新由1开始；
使用truncate语句时，不会在日志中记录删除的内容，因此truncate语句的执行效率比delete语句高。
查询数据
简单查询
select语句的语法格式如下
|
1
2
3
4
5
6
7
8
9
10
11
|
select
[distinct] *| {字段名1,字段名2,字段名3, ...} from 表名 [where 条件表达式1] [group by 字段名 [having 条件表达式2]] [order by 字段名 [asc|desc]] [limit [offset] 记录数] |
查询所有字段有两种方法
第一种：
在select语句中列出所有字段名来查询表中的数据，其语法格式
|
1
|
select
字段名1,字段名2, ... from 表名 ; |
第二种：
使用星号(“*”)通配符来代替所有的字段名，其语法格式如下
|
1
|
select
* from student ; |
查询指定字段，其语法格式如下
|
1
|
select
字段名1,字段名2, ... from 表名 ; |
按条件查询
带关系运算符的查询
在select语句中，最常见的是使用where子句指定查询条件对数据进行过滤，其语法格式如下
|
1
2
3
4
5
|
select
字段名1,字段名2, ... from 表名 where 条件表达式 ; |
常见的关系运算符如下表所示
|
关系运算符
|
说明
|
关系运算符
|
说明
|
|
=
|
等 于
|
<=
|
小于等于
|
|
< >
|
不等于
|
>
|
大 于
|
|
!=
|
不等于
|
>=
|
大于等于
|
|
<
|
|
|
|
例：查询student表中grade大于80的学生姓名。
|
1
|
select
name, grade from student where grade > 80; |
带 in 关键字的查询
in 关键字用于判断某个字段的值是否在指定集合中，若字段的值在集合中，则满足条件，该字段所在的记录将被查询出来。其语法格式如下
|
1
2
3
4
5
|
select
* | 字段名1, 字段名2, ... from 表名 where 字段名 [not] in (元素1, 元素2, ...) |
说明：在上面的语法格式中，“元素1, 元素2, …”表示集合众的元素，即指定的条件范围。not 是可选参数，使用not表示查询不在in关键字指定集合范围中的记录。
例：查询student表中id值为1、2、3的记录。
|
1
|
select
id, grade, name, gender from student where id in (1,2,3) ; |
带between and 关键字的查询
between and用于判断某个字段的值是否在指定的范围之内，其语法格式如下
|
1
2
3
4
5
|
select
*|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] between 值1 and 值2 ; |
说明：在上面的语法格式中，“值1”表示范围条件的起始值，“值2”表示范围条件的结束值。not是可选参数，使用not表示查询指定范围之外的记录，通常情况下，“值1”小于“值2”，否则查询不到任何结果。
例：查询student表中id值在2~5之间的学生姓名。
|
1
|
select
id, name from student where id between 2 and 5 ; |
空值查询
在数据表中，某些列的值可能为空值（null），空值不同于0，也不同于空字符串。在MySQL语句中，使用 is null 关键字来判断字段的值是否为空值，其语法格式如下
|
1
2
3
4
5
|
select
*| 字段名1, 字段名2, ... from 表名 where 字段名 is [not] null ;de> |
例：查询student表中gender为空值的记录。
|
1
|
select
id, name, grade, gender from student where gender is null ; |
带distinct关键字的查询
很多表中某些字段的数据存在重复的值。有时，需要过滤掉查询记录中重复的值，可以使用distinct关键字来实现这种功能，其语法格式如下
|
1
|
select
distinct 字段名 from 表名 ; |
说明：“字段名”表示要过滤重复记录的字段
例：查询student表中gender字段的值，查询记录不能重复。
|
1
|
select
distinct gender from student ; |
distinct关键字还可以作用于多个字段，其语法格式如下
|
1
2
3
|
select
distinct 字段名1, 字段名2, ... from 表名 ; |
说明：只有distinct关键字指定的多个字段值都相同，才会被认作是重复记录
例：查询student表中的gender和name字段，使用distinct关键字作用于这两个字段。
|
1
|
select
distinct gender, name, from student ; |
带like关键字的查询
like关键字可以判断连个字符串是否相匹配，其语法格式如下
|
1
2
3
4
5
|
select
*|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] like '匹配字符串' ; |
说明：“匹配字符串”指定用来匹配的字符串，其值可以是一个普通字符串，也可以是包含百分号（%）和下划线（_）的通配字符串。百分号和下划线统称为通配符。
百分号（%）通配符
百分号通配符能匹配任意长度的字符串，包括空字符串。例如，字符串“c%”匹配以字符c开始，任意长度的字符串，如“ct”、“cut”、“current”等等。
例1：查找student表中name字段值以字段“s”开头的学生 id。
|
1
|
select
id, name, from student where name like "s%" ; |
说明1：百分号通配符可以出现在通配字符串的任意位置
例2：查询student表中name字段值以字符“w”开始，以字符“g”结束的学生id。
|
1
|
select
id, name, from student where name like 'w%g' ; |
说明2：在通配字符串中可以出现多个百分号通配符
例：查询student表中name字段值包含字符“y”的学生id。
|
1
|
select
id, name from student where name like '%y%' ; |
下划线（_）通配符
下划线通配符与百分号通配符有些不同，下划线通配符只匹配单个字符。若要匹配多个字符，需要使用多个下划线通配符。
注意：若使用多个下划线匹配多个连续的字符，下划线之间不能有空格
例1：查询student表中name字段值以字符串“wu”开始，以字符串“ong”结束，并且两个字符串之间只有一个字符的记录。
|
1
|
select
* from student where name like "wu_ong" ; |
例2：查询student表中name字段值包含7个字符，并且以字符串“ing”结束的记录。
|
1
|
select
* from student where name like '____ing' ; |
百分号和下划线通配符进行的查询操作
注意：若要匹配字符串中的百分号和下划线，则使用右斜线（“\”）对百分号和下划线进行转义
例：查询student表中name字段值包括“%”的记录。
假设student表中以存在如下记录
|
1
2
3
|
insert
into student(name, grade, gender) values('sun%er', 95, '男') ;ode> |
带and关键字的多条件查询
在使用select语句中，有时为了使查询结果更加精确，可以使用多个查询条件，其语法格式如下
|
1
2
3
4
5
|
select
*|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 and 条件表达式2 [... and 条件表达式n] ; |
例：查询student表中id字段值小于5，并且gender字段值为“女”的学生姓名。
|
1
|
select
id, name, gender from student where id < 5 and gender = '女' ; |
带or关键字的多条件查询
在使用or关键字时，只要记录满足任意一个条件就会被查询出来，其语法格式如下
|
1
2
3
4
5
|
select
*|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 or 条件表达式2 [... or 条件表达式n] ;e> |
例：查询student表中id字段值小于3或者gender字段值为“女”的学生姓名。
|
1
|
select
id, name,gender from student where id < 3 or gender = '女' ; |
or和and关键字一起使用的情况
注意：and的优先级高于or
例：查询student表中gender字段值为“女”或者gender字段值为“男”，并且grade字段值为100的学生姓名。
|
1
2
3
4
5
|
select
name, grade, gender from student where gender = '女' or gender = '男' and grade = 100 ;de> |
高级查询
聚合函数
实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值、最小值、平均值等。
|
函数名称
|
作用
|
函数名称
|
作用
|
|
count()
|
返回某列的行数
|
max()
|
返回某列的最大值
|
|
sum()
|
返回某列值的和
|
min()
|
返回某列的最小值
|
|
avg()
|
返回某列的平均值
|
|
|
以上表中的函数对一组值进行统计，并返回唯一值，这些函数被称为聚合函数。
count()函数
count()函数用来统计记录的条数，其语法格式如下
|
1
|
select
count (*) from 表名 ; |
例：查询student表中一共有多少条记录
|
1
|
select
count(*) from student ; |
sum()函数
sum()函数是求和函数，用于求出表中某个字段所有值的总和，其语法格式如下
|
1
|
select
sum(字段名) from 表名 ; |
例：求出student表中grade字段的总和。
|
1
|
select
sum(grade) from student ; |
avg()函数
avg()函数用于求出某个字段所有值的平均值，其语法格式如下
|
1
|
select
avg(字段名) from 表名 ; |
例：求出student表中grade字段的平均值
|
1
|
select
avg(grade) from student ; |
max()函数
max()函数是求最大值的函数，用于求出某个字段的最大值，其语法格式如下
|
1
|
select
max(字段名) from 表名 ; |
例：求出student表中grade字段的最大值
|
1
|
select
max(grade) from student ; |
min()函数
min()函数是求最小值的函数，用于求出某个字段的最小值，其语法格式如下
|
1
|
select
min(字段名) from 表名 ; |
例：求出student表中grade字段的最小值
|
1
|
select
min(grade) from student ; |
对查询结果排序
使用 order by 对查询结果进行排序，其语法格式如下
|
1
2
3
4
5
|
select
字段名1, 字段名2, ... from 表名 order by 字段名1[asc|desc], 字段名2 [asc|desc] ... |
说明：参数 asc 表示按照升序进行排序，desc表示按照降序进行排序。默认情况下，按照 asc 方式进行排序。
例：查出 student 表中的所有记录，并使用参数 asc 按照grade字段升序方式进行排列。
|
1
2
3
|
select
* from student order by grade asc ;e> |
注意：在按照指定字段进行排序时，若某条记录的字段值为null，则这条记录会在第一条显示，因为 null 值可以被认为是最小值
分组查询
使用 group by 按某个字段或者多个字段中的值进行分组，字段中值相同的为一组，其语法格式如下
|
1
2
3
4
5
|
select
字段名1, 字段名2, ... from 表名 group by 字段名1, 字段名2, ... [having 条件表达式]code> |
说明：having关键字指定条件表达式对分组后的内容进行过滤，需要注意的是，group by 一般和聚合函数一起使用。
由于分组查询比较复杂，下面分几种情况对分组查询进行详解。
单独使用 group by 分组
单独使用 group by 关键字，查询的是每个分组中的一条记录。
例：查询student表中的记录，按照gender字段值进行分组。
|
1
|
select
* from student group by gender ; |
group by 和聚合函数一起使用
group by 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等。
例：将student表按照gender字段值进行分组查询，计算出每个分组中各有多少名学生。
|
1
|
select
count(*), gender from student group by gender ; |
结果说明：group by 对student表按照gender字段中的不同值进行了分组，并通过count()函数统计出每个组个数。
group by 和 having关键字一起使用
例：将student表按照gender字段进行分组查询，查询出grade字段值之和小于300的分组。
|
1
|
select
sum(grade), gender from student group by gender having sum(grade) < 300 ; |
having 关键字和where关键字的异同点
相同点：都用于设置条件表达式对查询结果进行过滤
不同点：
having关键字：
having关键字后可以跟聚合函数，通常情况下，having关键字都和group by 一起使用，用于对分组后的结果进行过滤。
where关键字：
where关键字不能后跟聚合函数。
使用 limit 限制查询结果的数量
limit关键字可以指定查询结果从哪一条记录开始以及一共查询多少条信息，其语法格式如下
|
1
2
3
4
5
|
select
字段名1, 字段名2, ... from 表名 limit [offset,]记录数 |
说明：limit 后面可以跟两个参数，第一个参数“offset”表示偏移量，若偏移量为0，则从查询结果的第一条记录开始；偏移量为1，则从查询结果中的第二条记录开始，以此类推。offset为可选值，若不指定，其默认值为0。第二个参数“记录数”表示返回查询记录的条数。
例1：查询student表中的前4条记录。
|
1
|
select
* from student limit 4 ; |
例2：查询student表中grade字段值从第5位到第8位的学生（从高到底）。
|
1
|
select
* from student order by grade desc limit 4, 4 ; |
为表和字段取别名
为表取别名的语法格式如下
|
1
|
select
* from 表名 [as] 别名 ; |
例：为student表起一个别名 s,并查询student表中gender字段值为“女”的记录。
|
1
|
select
* from student as s where s.gender = '女' ; |
为字段取别名的语法格式如下
|
1
|
select
字段名 [as] 别名 [, 字段名 [as] 别名, ...] from 表名 ; |
例：查询student表中的所有记录的name和gender字段值，并为这两个字段起别名 stu_name 和 stu_gender。
|
1
|
select name as stu_name, gender stu_gender from student ; |
