SQL语法二

外键语法 ：
use company;
-- 外键约束
-- 创建一张老师表
create table teacher
(
tid int primary key auto_increment,
tname char(20)
)ENGINE = INNODB  DEFAULT charset utf8;


-- 插入两条记录
insert into teacher(tname) values('周明');
insert into teacher(tname) values('张骁');


-- 创建学生表
create table student
(
sid int primary key auto_increment,
sname char(20),
tid int REFERENCES teacher.tid -- 外键，引用主表teacher的主键
)ENGINE = INNODB default charset utf8 ;


-- 插入两条学生信息
insert into student(sname,tid) VALUES('潘万',1);
insert into student(sname,tid) VALUES('景腾',2);


-- 请查询一下，周明老师教哪些学生
-- 分析一下，好像是两张表里面的信息，让两张表有所关联
-- 从下面一张叫student,teacher(tid,tname,sid,sname,tid)
select sname from student,teacher where tname = '周明' 
and teacher.tid = student.tid;














-- 单表查询


-- 创建一张员工表,给下面的查询做测试用drop table employee delete from employee
create table employee
(
eid int primary key auto_increment,
ename char(20), -- 名字
salary float,  -- 工资
comm float, --  奖资
shebao float,  -- 社保
department char(20) -- 部门名字
)ENGINE = INNODB DEFAULT charset utf8;


insert into employee(ename,salary,comm,shebao,department) values('张在',10000,2000,3000,'销售');
insert into employee(ename,salary,comm,shebao,department) values('张四',12000,2000,3000,'销售');
insert into employee(ename,salary,comm,shebao,department) values('张五',13000,2000,3000,'研发');
insert into employee(ename,salary,comm,shebao,department) values('张三',9000,2000,3000,'研发');


select * from employee;


-- 查询
use company;


-- 单表查询
-- 1.查询所有
select * from users;


-- 2.选取自己想要查询的列
select userid,pwd from users;


-- 单表查询，模糊查询(只是字符串类型)
-- %代表0或者无限字符
-- _代表一个任意字符
select * from emp where ename like '_L%';


select * from emp;
select * from dept;


-- 3.带多条件的查询 比如登录查询
select userid from users where userid='admin' and pwd='567';


-- 4.给查询出来的列取别名(下面的写法没人用）
select userid 帐号 from users;


-- 5.带计算的查询，一般是对字段进行计算(下面的写法没人用）！！！！！！！！！
select 1+2+3;
--   例子1:计算员工的年薪
select (salary+comm-shebao) * 12 as totalsalary from employee;
--   例子2：将所有员工工资加1000块
-- update employee set salary = salary + 1000;


-- 6.带函数的查询
select avg(salary) avg from employee;-- 求最高工资
select min(salary) min from employee;-- 求最大值，求最小值，总和
select max(salary) max from employee;
select sum(salary) sum from employee;
select count(*) sum from employee;


select avg(salary),min(salary),max(salary),sum(salary) from employee;


-- 7.分组查询,一般和avg,sum,max,min函数配合使用
-- 求出每个部门中工资最高的
select max(salary) from employee group by department;


-- 在分组后，再次挑选条件
-- having 与where 效果一样，都是条件
-- 1. where接表字段，having接函数
select max(salary) 最高工资 from employee group by department
having 最高工资>12000;
-- 求出每个部门中工资最高,同时比10000块(函数字段)要高的


select max(salary) 最高工资,avg(salary) 平均工资 from employee group by department
having 最高工资>平均工资;
-- 求出每个部门中工资最高,同时比平均工资(函数字段)要高的


select max(salary) m ,avg(salary) a  from employee group by department
having m>a;


select max(salary) m   from employee group by department
having m>avg(salary);


-- 8.排序，默认升序asc,可不写,降序desc
select * from employee order by salary asc;
select * from employee order by salary desc;


-- 9.分页,从第一条开始取(索引)，取2条（长度）
select * from employee limit 0,2;
-- 取出工资最高的三个人
select * from employee order by salary desc limit 0,3;


-- 说明一下各种关键这的顺序
select 字段1,....,字段N
from 表名1,....,表名N
where  字段1,....,字段N(每个字段后面>,>=,<,<=,!=,=,in,not in, And ,or)
group by  字段1
having 函数(avg,sum,max,min,count)
order by 字段1 asc/desc
limit 0,?