MySQL 高级操作

1.常用的数据类型

1.1.数字型

1.1.1整数型

tinyint	1字节
int		4字节
在计算机中,数据都是以二进制的形式进行存储,1个字节(byte)占用8位(bit)
0=>0000 0000
1=>0000 0001
2^8	可以表示256个数字
tinyint可以表示的范围: 0~255

在计算机中,数字又分正负,0表示正,1表示负
0 000 0001	+1  2^7 表示128个数 0~127
1 000 0001	-1  2^7 表示128个数 -128 ~ -1

有符号的情况下:
tinyint表示范围: -128 ~ 127
无符号的情况下:
tinyint表示范围: 0 ~ 255

int 的取值范围: -21亿 ~ 21亿  /  0 ~ 42亿
4个字节
2^32

unsigned 无符号
例:
create table `student1`(
`id` int unsigned,
`name` varchar(20),
`socre` tinyint unsigned   
)engine=innodb charset=utf8 comment='学生表1';

insert into `student1` values(0,'张飞',0);		# 成功
insert into `student1` values(4294967295,'张飞',0);# 成功
insert into `student1` values(4294967295,'张飞',255);# 成功

insert into `student1` values(4294967296,'张飞',255);# 失败
insert into `student1` values(-1,'张飞',255);# 失败
insert into `student1` values(1,'张飞',256); # 失败
insert into `student1` values(1,'张飞',-1);  # 失败

1.1.2小数型

浮点数: float,double  会有精度丢失
定点数: decimal	   不会有精度丢失,通常用来表示货币
decimal(M,D)#稍微大范围,如成绩100分用decimal(3,1)就有问题,decimal(4,1)范围就大了,但是后期可以限制
M:总长度		D:小数位长度
decimal(5,2)

例:
create table `student2`(
`id` int unsigned,
`name` varchar(20),
`socre` tinyint unsigned,
`hight` decimal(3,2)
)engine=innodb charset=utf8 comment='学生表2';

insert into `student2` values(1,'关羽',100,1.85); # 成功
insert into `student2` values(1,'关羽',100,10.1); # 失败
insert into `student2` values(1,'关羽',100,2.999); # 成功,3.00
insert into `student2` values(1,'关羽',100,2.125); # 成功 2.13

1.2.字符型

char(M)		定长字符串
varchar(M)	变长字符串

注意:
如果是char类型,长度确定,占用的空间就固定
如果是varchar类型,随着存储的数据而改变占用的空间

如何选择?
如果长度小于255,且长度固定,则使用char()
如果长度小于255,且长度不固定,则使用varchar(),如果超过255,则使用"文本"类型

create table `student3`(
`id` int unsigned,
`name` varchar(20),
`socre` tinyint unsigned,
`sex` char(1)
)engine=innodb charset=utf8 comment='学生表3';


insert into `student3` values(1,'abcabcabcabcabcabcabc',100,'男'); # 失败
insert into `student3` values(1,'abcabcabcabcabcabcab',100,'男'); # 成功
insert into `student3` values(1,'abcabcabcabcabcabcab',100,'保密'); # 失败

1.3.文本型

常用的:
text		2个字节 表示65535个
longtext	4个字节 表示42亿个

1.4.日期时间型

常用:
datetime	日期与时间 范围:0001~9999年
timestamp	时间戳		范围: 1970~2038年

例:
create table `student4`(
`id` int unsigned,
`name` varchar(20),
`birthday` datetime,
`end_time` timestamp
)engine=innodb charset=utf8 comment='学生表4';

insert into `student4` values(1,'张飞','200-1-2 12:30:20','2000-12-31 12:20:30');
insert into `student4`(id,name,birthday) values(2,'关羽','199-12-20 10:20:30');
update `student4` set name='刘备' where id=1;

2. 字段属性

2.1主键( primary key)

a.用于唯一标识一条记录
b.一个表只能有一个主键
c.主键不能为空
d.主键的值不能重复

2.2 自动增长(auto_increment)

例:
create table `stu1`(
`name` varchar(20),
`sex` char(1),
`age` tinyint unsigned 
) engine=innodb charset=utf8 comment='学生表1';

insert into `stu1` values
('李三','男',28),
('李三','男',28),
('李四','男',28),
('张三','男',28);

==================================================
create table `stu2`(
`id` int unsigned primary key auto_increment,
`name` varchar(20),
`sex` char(1),
`age` tinyint unsigned 
) engine=innodb charset=utf8 comment='学生表2';

insert into `stu2` values(1,'张三','男',28);
insert into `stu2`(name,sex,age) values('张三','男',28);

insert into `stu2`(id,name,sex,age) values(5,'张三','男',28);
insert into `stu2`(name,sex,age) values('李三','男',28);
insert into `stu2`(id,name,sex,age) values(null,'小三','男',28); 

注:自动增长是基于当前最大值+1

2.3 null 是否为空

null		允许为空
not null	不能为空

2.4 default 默认值

default '默认值'

2.5 unique key 唯一键

一个表可以有多个主键,但可以有多个唯一键

2.6 comment 备注

给字段添加备注信息
`name` varchar(20) comment '姓名'

例:
create table `stu3`(
`id` int unsigned primary key auto_increment,
`name` varchar(20) not null comment '姓名',
`nick_name` varchar(20) not null unique key comment '昵称',
`sex` char(1) default '男' comment '性别'    
)engine=innodb charset=utf8 comment='学生表3';


insert into `stu3`(name,nick_name,sex) values('张三','小三','男'); # 成功
insert into `stu3`(nick_name,sex) values('小三','男'); # 失败
insert into `stu3`(name,nick_name) values('张五','小五'); # 成功
insert into `stu3`(name,nick_name) values('王五','小五'); # 失败

3.查询 select

3.1 查询 选项

select [选项] 字段列表 from `表名` [where 条件表达式];

all 选项(默认的)
select all name from `stu3`;
select name from `stu3`;  # 结果一样

distinct 选项---去重
针对查询出的结果进行去重
注意:只有所有字段的值全部一样,才算重复

select name,sex,age from `stu2`;
select distinct name,sex,age from `stu2`;
select distinct * from `stu2`;

3.2 字段表达式

通配符 *
* 匹配所有字段
select * from `表名`;


字段列表
select name,age from `表名`;


列 别名 as
select name from `stu2`;
select name as '姓名' from `stu2`;

select name '姓名' from `stu2`;
select name '姓名',sex '性别' from `stu2`;

select name '姓名',sex '性别',age+10 from `stu2`;
select name '姓名',sex '性别',age+10 '十年后年龄' from `stu2`;

聚合函数
count()	统计,计数
sum()	求和
avg()	求平均值
max()	求最大值
min()	求最小值

例:
求`stu2`表中有多少条记录
select count(*) from `stu2`;

求`stu2`表中所有人的年龄的和
select sum(age) from `stu2`;

求`stu2`表中所有人的平均年龄
select avg(age) '平均年龄' from `stu2`;

求`stu2`表中年龄最大的记录
select max(age) from `stu2`;

求`stu2`表中年龄最小的记录
select min(age) from `stu2`;

4.排序&查询不含关键字的记录

#升序 order by score desc
#降序 order by score ascx
select * from score order by score desc;

查询student表中姓“张”的同学记录
select * from student where sname like '张%';

查询student表中不姓“张”的同学记录
select * from student where sname  not like '关%';

5.连接查询

创建两个表,一个学生表,一个班级表
学生表: id,姓名,性别,班级id
create table `student0`(
`id` int unsigned primary key auto_increment,
`name` varchar(20),
`sex` char(1),
`class_id` varchar(20)
)engine=innodb charset=utf8 comment='学生表';

insert into `student0`(id,name,sex,class_id) values
(1,'张飞','男','0927'),
(2,'关羽','男','1114'),
(3,'刘备','男','0831'),
(4,'大乔','女','1114'),
(5,'小乔','女','1031'),
(6,'貂蝉','女','0927');


班级表: id,班级id,班级名称
create table `class`(
`id` int unsigned primary key auto_increment,
`class_id` varchar(20),
`class_name` varchar(20)
)engine=innodb charset=utf8 comment='班级表';

insert into `class` values
(null,'0927','软件测试班'),
(null,'1114','软件测试班'),
(null,'0831','UI妹子班'),
(null,'1031','JAVA和尚班'),
(null,'1201','H5前端班');

需求:
查询出每个学生以及其对应的班级
select * from `student0`,`class`; ===>  查询结果为笛卡尔乘积

查询出正确的结果:
select * from `student0`,`class` where `student0`.`class_id`=`class`.`class_id`;

使用连接查询查出正确的结果:
select * from `student0` join `class` on `student0`.`class_id`=`class`.`class_id`;


连接查询分类:
内连接:  inner join 简写为 join
将左右两张表中能匹配上的记录查询出来.

外连接:
左外连接 left join
select * from `class` left join `student0` on `student0`.`class_id`=`class`.`class_id`;
将左表中与右表匹配不上的记录也查询出来.

右外连接 right join
select * from  `student0` right join `class` on `student0`.`class_id`=`class`.`class_id`;
将右表中与左表匹配不上的记录也查询出来.

using
使用一个字段来作为连接条件,两张表的同名字段
select * from  `student0` right join `class` using(class_id);

select student0.name,student0.sex,class.class_id,class.class_name from  `student0` right join `class` using(class_id);