mysql 学习笔记 day04

/*
存储引擎
myisam
一个表，三个文件 不支持外键
结构，数据，索引


innondb(默认)
一个表，一个文件 支持外键
bdb
memory
archive


-- 选择依据
-- 性能
-- 功能
*/


alter table class engine myisam;


create table room(
room_id int primary key auto_increment,
room_name char(3) not null default '001'
)engine myisam character set utf8;




----------------------------------order by-------------------------------------------------------


-- order by 字段 升序|降序(asc|desc) 默认asc
-- 允许多字段排序
insert into teacher(t_name, class_name, days) values('dd','12',3);
insert into teacher values(null, null,'d',1);
alter table teacher modify t_id  int auto_increment;
alter table teacher modify t_name varchar(5) default '00b';
select * from teacher order by days asc;
order by




----------------------------------limit--------------------------------------------------------


-- 分页查询
    -- limit offset(偏移量 默认0), row_count(记录数) 


select * from teacher limit 0,4;
select * from teacher limit 4,4;


----------------------------------distinct--------------------------------------------------------


-- 去除重复数据 distinct 

select distinct days from teacher;




----------------------------------union--------------------------------------------------------


-- 联合查询
-- 将多条select 合并到一起 ,一条语句难以实现
-- 使用union关键字
-- 如果union 结果有重复的记录，会消除重复 可以通过all 达到目的 union all
--ERROR 1222 (21000): The used SELECT statements have a different number of columns
select t_name, days from teacher where t_id = 1 
union
select t_name, days, class_name from teacher where t_id = 2;


select max(days) from teacher
union all
select min(days) from teacher;


-- 子语句获得结果排序：
-- 将子语句包裹到括号内，order by limit 时才生效
-- 对所有结果进行统一排序


-- 规定：
-- 多个




----------------------------------子查询------------------------------------------------
select t_name from teacher where days = (select max(days) from teacher);


-- 不加括号报错 
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that correspond
-- s to your MySQL server version for the right syntax to use near 'select max(days) from tea
-- cher' at line 1 


-- 子查询分类 
-- 1、子查询出现的位置 

-- 2、子查询的返回值形式 
-- 一个值 
-- 一列 
-- 多列 
-- 多行多列 


-- in | not in 集合操作符 
-- any | all 比 in not in 功能强大 不常用 not any = not some


-- 参与比较时可以构建一行 用括号
select t_name, gender, c_name, from teacher_class where (gender, c_name)= 
select distinct gender, c_name, from teacher_class where t_name='李白' 
and c_name='php0115' limit 1;


-- 返回一张表
select * from (select t_name, c_name, days from teacher_class where days > 14) as temp
where t_name like '李%';

-- exists 子查询
-- 返回布尔值
-- 如果子查询可以返回数据，返回true
-- 如果子查询没有返回数据 ，返回false
select * from class where exists (select * from student where class_id = 4);
insert into teacher values();
+------+------------+------------+------+
| t_id | t_name     | class_name | days |
+------+------------+------------+------+
|    1 | 汉子       | 0990       |    2 |
|    2 | 汉子汉子汉 | 099044     |    2 |
|    3 | dd         | 12         |    3 |
|    4 | d          | d          |    4 |
|    5 | NULL       | d          |    1 |
+------+------------+------------+------+


-- 下面的sql完成同样的事情
select * from teacher where exists(select * from teacher where teacher.id = t_id);


select * from teacher where id in(select * from teacher);


---------------------------链接查询-----------------------------------------
-- join 将多个表链接起来
create table join_teacher(
id int primary key auto_increment ,
t_name varchar(10),
gender enum('male', 'female','secret')
)engine innodb character set utf8;
insert into join_teacher values
(1, '韩信', 'male'),
(2, '李白','female'),
(3, '韩非子', 'secret');


-- ---------------------------------------------------------------
create table join_class(
id int primary key auto_increment,
c_name char(7),
room char(3)
)engine innodb character set utf8;


insert into join_class values
(1, 'php0115', '107'),
(2, 'php0228', '104'),
(3, 'php0331', '102');
--------------------------------------------------------------------
create table join_teacher_class(
id int primary key auto_increment,
t_id int,
c_id int,
days tinyint,
begin_date date,
end_date date
)engine innodb character set utf8;


insert into join_teacher_class values
(1, 1, 1, 15, '2013-01-15', '2013-2-20'),
(2, 1, 2, 18, '2013-2-28', '2013-3-30'),
(3, 1, 3, 22, '2013-3-31', '2013-5-5'),
(4, 2, 1, 20, '2013-2-22', '2013-3-25'),
(5, 2, 2, 22, '2013-3-31', '2013-4-29'),
(6, 3, 1, 15, '2013-3-27', '2013-4-18'),
(7, 1, 1, 15, '2013-4-19', '2013-5-1'),
(8, 3, 3, 15, '2013-5-28', '2013-6-15'),
(9, 2, 1, 5, '2013-5-4', '2013-5-15');
------------------------------------------------------------------------