/*
存储引擎
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');
------------------------------------------------------------------------
mysql 学习笔记 day04
最新推荐文章于 2024-08-07 14:57:30 发布