关于mysql

分三个部分，第一部分是常用关键字，第二部分为一些典型的查询，第三部分为一些其他概念

一、关键字

1）建表语句，这个只写出个例子吧，这个表是oracle数据库里面的例子。
create table emp(
EMPNO int(4) not null primary key,
ENAME varchar(20), //mysql里面没有varchar2类型，oracle里面有，这个varchar2貌似是更通用些
JOB varchar(9),    //而且，mysql中varchar比oracle中varchar2占的空间大，可能是mysql预分配多些吧
MGR int(4),    //oracle中是number不是int
HIREDATE DATE,     //也有时间类型的，这个mysql和oracle一样
SAL float(7,2),    //最多显示七位，小数点后最多2位，第三位会自动四舍五入，而oracle为number(7,2)
COMM float(7,2),
DEPTNO int(2)
 );

2）取别名，这个在表连接的时候会常用到
select ename , sal*12 "anuula sal" from emp; //双引号最好加上，如果不是一个字符串的话会产生歧义的，像空格

3）对于空值的处理
select ename, sal from emp where comm is(not) null;//这里注意不是=

4）distinct
select distinct deptno from emp;
select distinct deptno, job from emp; //distinct的为deptno和job的组合

5）<>号这个不等号，时间一长就忘了
select * from emp where deptno <> 10;

6）between and
select * from emp where sal between 800 and 3000; //需要注意的是这里的between包括800和3000，是大于等于and小于等于的简化

7）in
select * from emp where sal in(800, 3000); //其实就是或(or)的简化

8）like
select ename from emp where ename like '%ALL%';//包含ALL的名字%代表0个或多个，比较特殊
select ename from emp where ename like '_A\%%';//第二个字母是A第三个字符是%的，_代表一个字符。默认的转移字符为\这个倒是没什么差别

9)order by
select * from dept order by deptno desc;//指定了desc才是降序，否则默认是升序的asc，可以不写
select * from emp order by deptno asc, ename desc;//先按照deptno升序序，如果deptno相同，按照名字降序

10）常用函数
以下为单行函数，输入为单条记录，产生一个输出
select lower(ename) from emp;//uper
select ename from emp where lower(ename) like '_a%';//取出来第二个字母是a的，不管大小写
select substr(ename, 2, 3) from emp; //从第二个字符开始取三个字符
select round(23.666) from emp; //结果24,默认四舍五入到各位
select round(23.666，2) from emp;//结果24.67，四舍五入小数点后2位

以下为组函数，输入为一个记录组（多条），产生一个输出
select max(sal) from emp; //同样有avg，min，sum等组函数
select count(*) from emp where deptno = 10; //deptno为10的有几个人
select count(comm) from emp;//计算的为comm is not null的总个数
select count(distinct deptno）;//计算一共有多少个不重复的部门

11）group by
select avg(sal), deptno from emp group by deptno;//英文语法，group by在最后面
select max(sal) from emp group by deptno, job; //按照两个字段分组，组为两个的组合
select ename from emp where sal = (select max(sal) from emp); //求出工资最高的人的名字
//select ename, max(sal) from emp; //mysql运行没错，但是结果是错的，max的输出为单行函数，但ename的值可能不唯一
select deptno, max(sal) from emp group by deptno; //对的，因为是按照deptno分组的，得出来的max(sal)与deptno是一一对应的
//select ename, max(sal) from emp group by deptno; //结果是错误的，按照deptno分组，一个组中ename与max(sal)不是一一对应的

12）having
where是对单条记录进行过滤的，而having是对组进行过滤的
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;

关键字总结：
select ename, sal*12 anuual_sal from emp where ename not like '_A%' and sal > 100 order by sal desc;//一个简单的语句
select avg(sal), deptno from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;
注意顺序，select .. from where .. group by .. having .. order by..，先选择，在用where语句过滤，再分组，过滤，排序，单条语句一定注意顺序就OK。

二、典型查询语句

1)每个部门里面的最高工资的人和最高工资

//select ename, sal from emp where sal in (select max(sal) from emp group by deptno);
//错误的，A里面的有人工资可能是B里面的最高工资
select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
//子查询和表链接的组合使用，子查询看成一张表即可

子查询典型用法
select ename from emp where sal = (select max(sal) from emp);
select ename, sal from emp where sal > (select avg(sal) from emp); //求平均工资大于平均薪水的人

b.求每个部门的平均薪水等级
select avg(sal), deptno from emp group by deptno

2)求每个人的经理人

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;//自联接，要有不同的别名，当成两张表来用没有king
//上面的为sql1992的语法，连接条件在where中，一般where只当过滤条件
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno); //99语法，没有king，king的mgr为空，不连接
select e1.ename, e2.ename from emp e1 left （outer） join emp e2 on (e1.mgr = e2.empno);//king显示，左外连接显示左面表全部
select ename, dname from emp right join dept on (emp.deptno = dept.deptno); //右外连接同理显示出所有的dname
select ename, dname from emp full join dept on (emp.deptno = dept.deptno); //全外连接，左面ename和右面dname全部都显示

select ename, dname from emp, dept;//为92年的语法，其实为笛卡尔乘积
select ename, dname from emp cross join dept; //99语法，明确指出
select ename, dname from emp, dept where emp.deptno = dept.deptno;//92
select ename, dname from emp join dept on (emp.deptno = dept.deptno);//99
select ename, dname from emp join dept using (deptno);//不推荐，要求多，必须字段名类型一样

3)求每个人的薪水等级，公寓名称，并且第二个字母不是A(不等值连接)

select ename, dname, grade from
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where e.ename not like '_A%';

4)部门的平均薪水的等级（部门所有人薪水平均值的等级）
select deptno, grade from 
(select avg(sal) avg_sal, deptno from emp group by deptno) a
join salgrade s on (a.avg_sal between s.losal and s.hisal);

5)每个部门平均的薪水等级（部门里面所有人的薪水等级的平均值）

select deptno, avg(grade) avg_grade from
(select deptno, grade from emp e join salgrade s on(e.sal between s.losal and s.hisal)) t
group by deptno;

6)雇员之中哪些是经理人

select ename from emp where empno in
(select distinct mgr from emp); //用distinct的就会很有效率

7）不用组函数求薪水最高值

select max(sal) from emp; //组函数
select sal from emp where sal not in  //这里注意，不是 sal is not in 没有is，经常写错
(select distinct e1.sal from          //利用<号找出不能够连接成功的，同理利用>可以求出最小值
emp e1 join emp e2 on (e1.sal > e2.sal) );

8)求平均薪水最高的部门编号
select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno ) t //这里myql说找不到t这个表，可能别名的作用范围是有限制的，不能
where avg_sal =       //在from后面定义别名然后在where里使用
(select max(avg_sal) from t);

select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno ) t1  //mysql好奇怪，必须有一个别名，否则报错
where avg_sal =
(select max(avg_sal) from
 (select avg(sal) avg_sal, deptno from emp group by deptno ) t2
);

9）求最高薪水部门的名称
select dname from dept
where deptno =
(
select deptno from
 (select avg(sal) avg_sal, deptno from emp group by deptno ) t1 
 where avg_sal =
 (
 select max(avg_sal) from
  (select avg(sal) avg_sal, deptno from emp group by deptno ) t2
 )
);
还可以这样写，用组函数嵌套来实现//mysql貌似不行，oracle可以
select dname from dept
where deptno =
(
select deptno from
 (select avg(sal) avg_sal, deptno from emp group by deptno ) t1 
 where avg_sal =
 (
 select max(avg(sal)) from emp group by deptno t2
 )
);

10)平均薪水等级最低的部门的部门名称
select dname from dept
where deptno =
(
 select deptno from
 (
  select deptno, grade from
   (select avg(sal) avg_sal,deptno from emp group by deptno) t1
  join salgrade s on
  (t1.avg_sal between s.losal and s.hisal)
 ) t2
 where grade =
 (
 select min(grade) from
  (
   select deptno, grade from
    (select avg(sal) avg_sal,deptno from emp group by deptno) t1
   join salgrade s on
   (t1.avg_sal between s.losal and s.hisal)
  ) t3
 )
);

可以用视图view来简化语句，视图是一个虚表可以用来简化子查询语句,还要注意mysql的view不可以包含子查询，而oracal的可以
create view v$_dept_avg_sal_info as
select avg(sal) avg_sal,deptno from emp group by deptno;

create view v$_dept_avg_sal_grade_info as
select deptno, grade from v$_dept_avg_sal_info t
join salgrade s on
(t.avg_sal between s.losal and s.hisal);

然后上面的可以简化成
select dname from dept
where deptno =
(
 select deptno from v$_dept_avg_sal_grade_info
 where grade =
 (
 select min(grade) from v$_dept_avg_sal_grade_info
 )
);

 

11)比普通员工最高薪水还高的经理人的名称
注意mgr有的为空的,select 中必须有is not null。
select ename from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
 (select max(sal) from emp where empno not in       
  (select distinct mgr from emp where mgr is not null)
 );

12）创建表的备份
create table emp2 as select * from emp; //注意有as
insert into emp3 select * from emp;

三、一些概念

约束条件constraint（5个）：

create table emp3(
EMPNO int(4) not null,
ENAME varchar(20) not null,
DEPTNO int(2),
constraint emp3_empno_deptno_unique unique(EMPNO, DEPTNO),
constraint emp3_primary_key primary key(EMPNO, DEPTNO),
constraint emp3_detp_fk foreign key (deptno) references dept(deptno)
);

约束条件写在字段后面 EMPNO int(4) not null primary key,但是这种用法有局限，当要求两个字段的组合不为空的时候不能表示，这个时候加在最后面constraint unique(empno, deptno),可以自己取名字，否则系统会自动取名字。
nut null 不一定是主键，例如人的名字一般不能为空
unique 唯一性约束
primary key 非空唯一的主键，当有组合的时候，也要写在表后面
foreign key 注意被参考字段，必须为主键。一个表的数据受到另外一张表的约束(插入的emp中的deptno必须在表dept中)，而且不能删除被其他表参考的字段

约束条件的删除
alter table emp3 drop constraint emp3_empno_deptno_unique;//oracle上可以

外键约束
create table my_empno(
 deptno int (2) default NULL,
 constraint my_constraint foreign key(deptno) references dept(deptno)
);
被参考的字段必须是主键。insert into my_empno(deptno) valuse(66);如果dept中没有66这个字段是不能插入的，同样在删除dept的时候，如果有别的表在参考它，是不能被删除的。

可以不是外键同样约束
create table my_empno(
 deptno int (2) default NULL references dept(deptno)
);

索引（优化的时候主要手段）
注意字段为主键，或者有唯一性约束的时候，数据库会自动建立索引。索引是读得快，修改慢
create index idx_emp3_deptno on emp3(deptno);
create index idx_emp3_deptno_empno on emp3(deptno,empno); //注意当为组合索引的时候，是查找这个字段的组合效率高，并不是建立了两个索引
drop index idx_emp3_deptno;

视图
当想给别人看表的一部分信息，隐藏一部分关键信息的时候，可以建立视图，然后给别人查看视图的权限，例如只想给别人看公司员工的名单可以建立视图。
create view v$_emp3 as select ename from emp3;

数据库三范式
目的：不存在冗余数据，效率高

第一范式：要有主键，列不可分
第二范式：不能有部分依赖（多对多关系，一般分成三张）
例如一张记录学生选课信息的表，是一个多对多关系，主键为学好和课程号的组合，那么学生的姓名部分依赖于学号与课程号没有一对一的关系，（同一个学号对应多个课程号）。这种就是部分依赖，应该设计成三张表，学生表，课程表，和一张学生选课信息表。
第三范式：不能有传递依赖
例如emp中关于dept的所有信息全部写在emp中，这个时候dept中的信息deptname并不是直接依赖于empno（empno与deptname虽然是唯一的对应，empno对应一个deptname，这里与部分依赖区分开，一般部分依赖都是两个字段组合作为主键）

游标(cursor)
可以理解成一个指向结果集的一个指针，一般用来读取记录，也可以用来更新，声明的时候不一样。

存储过程（produre）
一个PL_SQL（不同数据库的不一样）的程序块，为了以后方便的使用不用在重新编译PL_SQL语句，对一个程序块取名并保存编译以后的结果。可以用这个名字代替这段程序块。另外存储过程和游标都可以有参数。

触发器(trigger)
有before和after触发，可以用来记录用户的操作，也可以来触发一些其他的事件。一些特殊应用，当两张表有外键约束条件的时候，可以利用触发器，来实现同时对两张表的操作。

参考：
http://www.w3school.com.cn/sql/index.asp