MySQL基础-day02

1.约束

约束类型	主键	外键	唯一	非空	自增	默认值	注释
关键字	primary key	foreign key	unique	not null	auto_increment	default	comment

        主键设置:

--在创建语句时添加主键(双主键时)
create table if not exists person(
    id int,
    name varchar(20),

    --双主键中间用,隔开
    primary key(id,name)
)

--创建完表后,通过alter添加主键
create table if not exists person(
    id int,
    name varchar(20)
)
--alter table 表名 add primary key(列名,列名...);
alter table person add primary key(id);

        主键自增:

--创建表时,添加自增
create table person(
    id int primary key auto_increment,
    name varchar(20)
)

--创建之后,通过alter修改
create table person(
    id int primary key,
    name varchar(20)
)
alter table person modify id int auto_increment;

--设置自增的起始值
create table person(
    id int primary key auto_increment,
    name varchar(20)
)
alter table person auto_increment=200;

        关联外键:

create table student(
    id int primary key auto_increment,
    name varchar(20)
)
--创建表时添加外键约束
create table score(
    id int primary key auto_increment,
    name varchar(20),
    student_id int,

    foreign key (student_id) references student(id)
)

--创建完成之后,通过alter后期添加
create table score(
    id int primary key auto_increment,
    name varchar(20),
    student_id int
)
--    alter table 表名 add foreign key(外键列列名) references 关联表名(主键列列名);
alter table score add foreign key(student_id) references student(id);

        unique唯一:

--创建表时,添加unique约束
create table person(
    id int,
    `name` varchar(20),
    unique(id)
);

--创建表之后,添加unique约束
create table person(
    id int,
    `name` varchar(20)
);
alter table person add unique(id);

        非空not null 与 默认值 default:

--创建表时,添加约束
create table person(
    id int not null,
    `name` varchar(30) default 'admin'
);

--创建表之后,添加约束
create table person(
    id int,
    `name` varchar(30)
);
alter table person modify id int not null;--非空
alter table person modify name varchar(30) default 'admin';--默认值

        注释comment:

create table person(
    id int primary key auto_increment comment'主键',
    `name` varchar(30) comment'姓名'
);

2.DQL:        select 列限定 from 表限定 where 行限定;

create table person(
	id int,
	`name` varchar(30)
);
insert into person(id,name) values (1,'张三');
insert into person(id,name) values (2,'李四');
insert into person(id,name) values (3,'王五');
insert into person(id,name) values (4,'赵六');
insert into person(id,name) values (5,'小明');

--and条件
select * from person where id=1 and name='张三';

--or
select * from person where name='张三' or id=1;

--between and 在...之间
select * from person where id between 1 and 3;--等价于    select * from person where id>=1 and id<=3;

--in 在指定数据中
select * from person where id in(1,3,5);-- 等价于 select * from person where id=1 or id=3 or id=5;

--like 模糊查询
-- % 匹配任意个数的任意字符    _ 匹配单个任意字符
select * from person where name like '%六';

--order by 排序
--asc 升序    desc 降序
select * from person order by id asc;

--limit 限制查询条数(永远放在句尾)
--    select 列限定 from 表限定 limit 条数;
--    select 列限定 from 表限定 limit 开始值(不包含),条数;
select * from person limit 3;

3.常用函数&分组(单表)       

         常用函数: 

--count(*):总数    max(字段名):最大值    min(字段名):最小值    sum(字段名):总和
--avg(字段名):平均值    concat('',''):拼接    substring('abcd',1,3):截取子串,返回abc
--curdate():返回当前日期    curtime():当前时间    now()当前日期时间    
--datediff('',''):返回两个参数之间相差天数,用第一个减去第二个

        单表查询:

create table student (
    id int primary key,
    name varchar(20),
    teacher_id int,
    score decimal(18,2)
);
create table teacher(
    id int primary key,
    name varchar(20)
);
insert into teacher (id,name) values(1,'张老师');
insert into teacher (id,name) values(2,'王老师');

insert into student (id,name,teacher_id,score) values(1,'张三',1,90)
,(2,'李四',2,88.9)
,(3,'王五',1,45.7)
,(4,'赵六',1,84)
,(5,'小明',2,92.5)
,(6,'小红',2,47);

--group by     select count(*),max(字段名),min(字段名)... from 表名 group by 字段名;
--查询每个老师带的学生中的最高分数
select *,count(*) as s_num,max(s.score) as score_max from student s group by s.teacher_id;

--having 只能在group by中使用
--查询每个老师所带学生平均分且大于75
select s.teacher_id,AVG(s.score) as avg_score from student s group by s.teacher_id having avg_score>75;

4.嵌套(子)查询

        a.关键字select之后

--select 字段名,(子查询) from 表名;

--查询所有学生的信息并显示老师的名字
select *,(select teacher.`name` from teacher where student.teacher_id=techer.id) as t_name from student;

        注:必须找到两个表之间的对应关系,并且子查询有且仅有一个字段

        b.from之后:作为一张表

--对成绩进行评等级,score<60 为c级,并且是差...
select *,
case 
when s.`等级`='C' then '差'
when s.`等级`='B' then '良'
when s.`等级`='A' then '优'
end as rank
from  (
	select *,
	case
	when score<60 then 'C'
	WHEN score>=60 AND score<80 THEN 'B'
	when score>=80 then 'A'
	end as '等级'
	from student
)as s;

        注:from之后的子查询被当做一张表使用,由于子查询先执行,所以必须该子查询要有别名

        c.where之后:当做条件使用

-- 想查询出张老师下面的所有学生信息
select * from student where teacher_id in (select id from teacher where `name`='张老师');

        注:where后面的子查询如果是多条数据,则必须用in连接,子查询中select后的字段只能有一个

5.合并查询:把两张表或者更多表合并成一张表

--union:去重    union all:不去重
-- 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(不去重)
select * from student where score>60 union all select * from student where teacher_id=1;