mysql 学习笔记 day04

/*存储引擎myisam一个表，三个文件 不支持外键结构，数据，索引innondb(默认)一个表，一个文件 支持外键bdbmemoryarchive-- 选择依据-- 性能-- 功能*/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 columnsselect t_name, days from teacher where t_id = 1 unionselect t_name, days, class_name from teacher where t_id = 2;select max(days) from teacherunion allselect 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 tempwhere t_name like '李%';-- exists 子查询-- 返回布尔值-- 如果子查询可以返回数据，返回true-- 如果子查询没有返回数据 ，返回falseselect * 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');------------------------------------------------------------------------