MySQL数据类型补充、数据操作和数据查询

1.数据类型补充

上次我们介绍了MySQL的数值型、字符型和日期时间型，这次我们介绍一下枚举型和集合型。

1.1.枚举型

# 枚举是⾃定义类型，可以多选⼀,实际上存的值是1，2，3...
alter table user add sex enum('男','⼥') default '男';  # 增加sex字段，可选男女，默认为男
insert into user(name,password,sex) values('tom','132','男');

1.2.集合型

# 类似复选框，可以存多个值
alter table student add hobby set('看电影','玩游戏','敲代码','烫头');
insert into users(uid,hobby) values(22,1+2+4+8);
insert into users(uid,hobby) values(22,1|2|4|8);
insert into users(uid,hobby) values(22,'看电影,玩游戏,敲代码');

2.数据操作

2.1.insert

# 写法⼀：
insert into 表名(字段1，字段2...) values(值1,值2...);
# 省略了字段列表，则按照建表时的字段顺序进⾏插⼊，每⼀列都要给值
# 写法⼆：
insert into 表名 values(值1,值2...);
# 写法三：插⼊多个记录
insert into 表名(字段1，字段2...)
values(值1,值2...),(值1,值2...),(值1,值2...)....
# 写法四：
insert into表名(name,age,sex)select name,age,sex from stars;
insert into histroy_student select * from student;

2.2.update

update 表名 set 字段1=值1,字段2=值2... where 条件  # 不加where修改的是所有的记录

2.3.delete

# 删除表中的数据，⾃增主键的值不会重新开始
delete from 表名 where 条件； #如果不加条件，会删除表中所有数据,慎重使⽤
alter table 表名 auto_increment = 5  #设置⾃增主键开始值
# 清空表，⾃增主键的值重新开始编号truncate
truncate table 表名  # 清空表中所有记录，等价于delete from表名；
# delete和truncate差别，truncate后，表中⾃增主键值从1开始

3.数据查询

基本结构：select 字段名 from 表名

3.1.基础查询

select username,password from user;
select usernname as ⽤户名, password as 密码 from user;  #可以给字段起别名
select *  from user; #查询所有字段，慎⽤，⼀般不建议使⽤，会导致⽆法优化sql语句
select 2018,username,password  from user; #可以有常量，表达式
select sname,2018-year(sbirthday) from student; #year是mysql的内置函数
select distinct username  from  user; #去除重复记录distinct针对查询结果去除重复记录，不针对字段

3.2.条件查询(where)

3.2.1.关系运算

关系运算符：>、>=、<、<=、=、!=、<>、between … and …

select username,password from user where uid < 10  # 查询uid小于10的数据的用户名和密码
select username,password from user where uid != 10  # 查询uid不等于10的数据的用户名和密码
select username,password from user where uid between 10 and 20  # 查询uid在10到20之间的数据的用户名和密码

3.2.2.逻辑运算

逻辑运算符：and、or、not

select username,password from user where uid < 100 and uid > 20;  # 查询uid小于100且uid大于20的数据的用户名和密码
select username,password from user where uid > 100 or uid < 20;  # 查询uid小于100或uid大于20的数据的用户名和密码

3.2.3.集合运算

集合运算符：in、not in

select username,password form user where uid in (2,3,4);  # 查询uid为2或3或4的数据的用户名和密码
select username,password form user where uid not in (2,3,4);  # 查询uid不为2或3或4的数据的用户名和密码

3.2.4.判空

判空运算：is null、is not null

select username,password from user where username is null;  # 查询用户名为空值的数据的用户名和密码

3.2.5.字符串的模糊查询(like)

通配符_(下划线)代表⼀个字符，%(百分号)代表任意⻓度字符串。

select * from user where username like '王_';  # 查询用户名为“王X”的记录
select * from user where username like '王%';  # 查询用户名开头为“王”的任意长度姓名的记录

3.3.排序(order by)

asc升序(默认)、desc降序。

select * from user order by age asc;select * from user order by age desc;  # 多字段排序
select name,age from php_user_history order by age desc,name;  # 如果在第⼀列上有相同的值，在具有相同的age的记录上再按name升序排列

3.4.限制结果集(limit)

limit n  # 为取前n条记录
limit offset,n  # 为从第offset条开始取，取n条
select * from php_user_history limit 3;
select * from php_user_history limit 4,2;  # 注意结果集中记录从0开始数数，offset相对于0开始实现分⻚必须的技术点
limit (page-1)*num,num

3.5.集合函数

count统计结果集中记录数；
max最⼤值；
min最⼩值；
avg平均值，只针对数值类型统计；
sum求和，只针对数值类型统计；
注意，集合函数不能直接使⽤在where后⾯的条件⾥，但可以在⼦查询中。

select count(*) num from user;
select count(distinct age) num from user;  # 去除重复记录
select * from student where sno = max(sno);  # 错误

3.6.分组(group by)

将结果集分组统计，规则：
出现了groub by的查询语句，select后⾯的字段只能是集合函数和group by后⾯有的字段，不要跟其它字段；
对分组进⾏过滤，可以使⽤having。

select uid, count(*) num from php_forum group by uid;
select uid,title, count(*) num from forum group by uid havingcount(*) >=2;
# having和where的区别：
# where针对原始表进⾏过滤having是针对分组进⾏过滤