//文章表
文章标题(title)、文章内容(content)、文章点击量(hits)、文章评论量(coms)
create table article(
aid int(11) not null auto_increment primary key,
title varchar(1000) not null,
content text not null,
hits int(11) not null,
coms int(11) not null
)engine=innodb charset=utf8;
//评论表
评论内容(com_content)、评论时间(com_time)
create table comment(
cid int(11) not null auto_increment primary key,
aid int(11) not null,//关联字段不能设为主键
com_content text not null,
com_time datetime null,
foreign key(aid) references article(aid)
)engine=innodb charset=utf8;
//学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(1000) NOT NULL,
`age` tinyint(4) NOT NULL,
`sex` enum('男','女','其他') NOT NULL,
`tel` varchar(1000) NOT NULL,
`joinTime` date NOT NULL,
`local` varchar(255) DEFAULT NULL,
PRIMARY KEY (`stid`)
) ENGINE=InnoDB CHARSET=utf8;
//科目表
create table course(
coid int(11) not null auto_increment primary key,
co_name varchar(255) not null
);
//成绩表(哪个学生的哪科成绩)
//联合主键：两个字段联合起来唯一标识一条记录
//stid和coid联合起来作为一个主键
create table score(
stid int(11) not null,//关联学生表
coid int(11) not null,//关联科目表
score decimal(5,2) not null
);
alter table score add foreign key(stid) references student(stid);
alter table score add foreign key(coid) references course(coid);
学生ID 学生姓名 学生成绩
(1)左链接(left join)：会读取左边表的全部数据,即使右表无对应数据
A left join B：A在左边作为主表,A表全部数据都会显示出来，而B表只会显示符合条件的数据，B表记录不足的地方会以Null补足
//left join：最常用
//select 字段名 from 表名1 left join 表名2 on 表1.关联字段=表2.关联字段；
select st.stid,st.name,sc.score from student st
left join score sc on st.stid=sc.stid;
//score作为主表
select st.stid,st.name,sc.score from score sc
left join student st on st.stid=sc.stid;
//学生ID 学生姓名 科目 学生成绩
select st.stid,st.name,co.co_name,sc.score from student st
left join score sc on st.stid = sc.stid
left join course co on sc.coid = co.coid;
图书ID 图书名 图书类型
select bk.id,bk.b_name,bt.type_name from books bk
left join book_type bt on bk.type_id = bt.type_id;
(3)查询小雪所有科目的成绩，要求显示结果为：学生ID 学生姓名 科目名称 科目成绩
select st.stid,st.name,co.co_name,sc.score from student st left join score sc on st.stid =sc.stid
left join course co on co.coid = sc.coid
where st.name='小雪';
右连接(right join)：会读取右边表的全部数据，若左边表无对应的数据，会以Null来补足
A right join B：B表在右边，作为主表
//右连接：
//score在右边，作为主表
select st.stid,st.name,sc.score from student st
right join score sc on st.stid = sc.stid;
//子查询
//什么情况下使用
//(1)一般涉及到两张表及两张以上的表
//(2)通过一个已知表的条件去查找另一个表的数据(必须保证两张表有一个共同的字段)
//查询图书类型是'黑客'的所有的图书信息、
select type_id from book_type where type_name = '黑客';
select * from books where type_id=6;
select * from books where type_id = (select type_id from book_type where type_name = '黑客');
//查询小明的所有科目成绩
select stid from student where name = '小明';
select * from score where stid = (select stid from student where name = '小明');
//查询小雪的英语成绩
select stid from student where name = '小雪';
select coid from course where co_name = '英语';
select * from score where stid =(select stid from student where name = '小雪') and coid = (select coid from course where co_name = '英语');
//查询语文成绩最高的学生信息
select coid from course where co_name = '语文';
select stid from score where coid =(select coid from course where co_name = '语文') order by score desc limit 0,1;
select * from student where stid = (select stid from score where coid =(select coid from course where co_name = '语文') order by score desc limit 0,1);
1、子查询
(1)查询小明的所有科目的成绩
(2)查询小明的数学成绩
(3)查询河北、山东这两地用户下的订单信息
select uid from users where address in ('河北','山东');
//limit in 不能一起使用
select * from orders where uid in(select uid from users where address in ('河北','山东'));
(4)查询近一个月内(2017-02-02~2017-03-02)订单金额最少的用户信 息
select uid from orders where orderTime between '2017-02-02' and '2017-03-02' order by money limit 0,1;
select * from users where uid = (select uid from orders where orderTime between '2017-02-02' and '2017-03-02' order by money limit 0,1);
//无限极分类(递归)表
理财
京东下金库
票据理财
基金理财
众筹
智能硬件
流行文化
公益
保险
车险
健康险
意外险
ID name pid(父级ID)
1 理财 0
2 众筹 0
3 保险 0
4 京东下金库 1
5 票据理财 1
6 基金理财 1
7 智能硬件 2
8 流行文化 2
9 车险 3
10 健康险 3
11 意外险 3
select * from 表 where pid=0;
select * from biao where pid = (select id from biao where name = '理财');
ID name pid
1 山东 0
2 济南 1
3 青岛 1
4 长清区 2
5 历下区 2
6 某某镇 4
//聚合函数，通常与group by一起使用
//常用于统计
//count();统计记录数
select count(*) from department;
select count(id) from books;
select count(id) as shuliang from books;
//max()求最大值,min()求最小值
select max(price) expensive from books;
select min(price) from books;
//sum() 求和
select sum(price) from books;
//avg() 求平均值
select avg(price) from books;
select avg(score) from score where stid = (select stid from student where name = '小明');
//字符串函数,会影响mysql执行效率
//截取字符串
substr(string,start,length)=substring(string,start,length)
string:要截取的字符串
start:从哪个位置开始截,从1开始
length:截取多少长度
select substr(b_name,1,2) from books;
//拼接字符串
//select concat(1,2,3);//结果123
//任意一个参数为Null,结果就为null
select concat(1,2,null);//结果null
select concat(b_name,price) from books;
//日期时间函数
select now();
select curdate();
select curtime();
select unix_timestamp();//默认返回当前时间戳
select unix_timestamp('2017-03-04');//将日期转换为时间戳
//返回某个日期是周几(1=星期天 2=星期1,...)
select dayofweek('2017-03-04');
//日期相加:date_add();或adddate();
select date_add('2017-03-03',interval 1 day);
select date_add(now(),interval 1 hour);
select date_add(now(),interval -1 month);
//日期相减:date_sub();或subdate();
select date_sub(now(),interval 1 minute);
//格式化日期：DATE_FORMAT(date,format);
http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html
//分组查询：group by
//查询结果中有重复的数据，只取每组中的第一条记录
员工ID 员工姓名 部门名称
select st.id,st.name st_name,de.name de_name from staff st left join department de
on st.dep_id = de.id
group by de_name;
员工ID 员工姓名 部门名称 工资
//求每个小组的最高工资
select de.name de_name,max(st.salary) from staff st left join department de
on st.dep_id = de.id
group by de_name;
having:分组中的条件(在聚合之后对记录进行筛选)
//查询每小组平均年龄大于25岁的员工的平均工资
select de.name de_name,avg(st.age),avg(st.salary) from staff st left join department de
on st.dep_id = de.id
group by de_name
having avg(st.age) > 25;
//查询每一类的图书的平均价格，并且筛选出平均价格大于50的记录
select avg(bk.price),bt.type_name from books bk left join book_type bt on bk.type_id=bt.type_id
group by bt.type_name
having avg(bk.price) > 50;
