SQL 语法学习笔记

dml语句：（数据操作语句）查询语句select1)use mydata; desc emp;desc dept;select * from salgrade;select * from dept;2)select now();select sysdate();select ename, sal*12 anuual_sal from emp;     改显示的列名select ename, sal*12 "anuual sal" from emp; select ename, comm from emp;                   展示空值和0select ename, sal*12 + comm from emp;          汇总（错误例子），任何含空值的表达式都不能计算select ename||sal from emp;                    //oracleselect CONCAT(ename,sal) from emp;             //mysqlselect CONCAT(ename,'sal') from emp;select CONCAT(ename,'s''al') from emp;3)select distinct deptno from emp;               //不重复的显示deptno字段的内容select distinct deptno , job from emp;         //不重复的显示字段对的内容4）select * from emp where deptno = 10;           //where 是过滤条件select * from emp where ename = 'test';select * from emp where ename != 'test';select * from emp where sal >= 2000;select * from emp where sal <> 2000;select * from emp where ename > 'ee';select * from emp where sal between 1000 and 2000;select * from emp where sal >= 1000 and sal <= 2000; //等价于上条select ename, sal, comm from emp where comm is null; //选空值 select ename, sal, comm from emp where comm is not null;select ename, sal, comm from emp where sal in (1000, 2000);//找到符合规则的select ename, sal, comm from emp where ename in ('ee','qq');5)*日期处理select ename, sal, hiredate from emp where hiredate > 1981-07-29; //日期需要特殊的字符串，select sysdate();可以查看select ename, sal, hiredate from emp where deptno = 10 and sal > 1000;//两个过滤条件select ename, sal, hiredate from emp where deptno = 10 or sal > 1000;select ename, sal, hiredate from emp where sal not in (1000)；6)select ename from emp where ename like '%e%';//"%"（通配符）表示0个或多个，"_"代表一个字母 （这句话表示找字符串含有e的值）select ename from emp where ename like '_e%';//（这句话表示找字符串第二位为e的值）select ename from emp where ename like '%\%%';  //\为转义字符表示字符串中有一个%select ename from emp where ename like '%$%%' escape '$'; //表示$符号位转义字符（也就是说转义字符可以自定义,默认"\"）7)排序select * from dept order by deptno desc;        //降序排列select empno, ename from emp order by empno asc ;//升序，asc可不写select * from dept where deptno <> 10 order by deptno desc;//混合使用select ename, sal, deptno from emp order by deptno asc, ename desc; //混合排序，先deptno排，后ename排，跟excel一样select ename, sal*12 annual_sal from emp where ename not like '_e%' and sal > 1000 order by sal desc;、//一次总结8)常用sql函数单行:select lower(ename) from emp; //转换成小写select ename from emp where lower(ename) like '_e%'; //用法多种多样select ename from emp where ename like '_e%'or ename like '_E%'; //等价于上面的写法select upper(ename) from emp; //相对应的转成大写select substr(ename, 1, 2) from emp; //从第一个字符串开始截两个字符select char(65) from dual;//把ASCII码转成字符select ascii('A') from dual;select round(23.652) from dual;//四舍五入select round(23.452) from dual;select round(23.652, 1) from dual; //精确到小数点后面多少位select round(23.652, 2) from dual;select round(23.652, -1) from dual;//精确到十位数//oracle才可以用： select to_char(sal, '$99,999.9999') from emp; select ename, sal*12 + IFNULL(comm, 0) from emp; //出现null值也能计算的方法9)*多行函数：//对多个数值进行操作select max(sal) from emp;select min(sal) from emp;select avg(sal) from emp;select round(avg(sal),2) from emp;select count(*) from emp; //有多少条记录select count(*) from emp where sal = 3000 or sal = 5000; //脑残式混合使用select sum(sal) from emp; //算总和10)select deptno, avg(sal) from emp group by deptno; //分组求平均select deptno, max(sal) from emp group by deptno, job;    //两个字段组合分组11）select avg(sal), deptno from emp group by deptno having avg(sal) > 2000; //在前面的基础下求大于6500的，注意，这里不能用where,用where会错误总结：->select * from ? //取数据->where ? <> ?//过滤->group by ?//分组->having ? <> ?;//分组限制select avg(sal), deptno from emp where sal > 2000 group by deptno having avg(sal) > 2000 order by avg(sal) desc; 12)子查询，多表联接select ename , dname, grade from emp e, dept d,salgrade s where e.deptno = d.deptno and e.sal between s.local and s.hisal and job <> 'CLERK';//1992的语法写法select ename,dname from emp ,dept;//1992select ename,dname from emp cross join dept;//1999//交叉连接select ename, dname from emp,dept where emp.deptno = dept.deptno;//1992select ename, dname from emp join dept on(emp.deptno = dept.deptno);//1999select ename, dname from emp join dept using(deptno);//1999简便写法，马士兵不推荐用//等值连接select ename,grade from emp e join salgrade s on(e.sal between s.local and s.hisal); //比要写在where要清楚一点select ename,dname,grade from emp e join dept d on(e.deptno = d.deptno)join salgrade s on (e.sal between s.local and s.hisal)where ename not like '_A%';//多表连接select empno, ename,mgr,mgrname from emp join (select empno mgrempno,ename mgrname from emp) t on(emp.mgr = t.mgrempno) ;select e1.empno, e1.ename, e1.mgr, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;//自连接//两句话是同一个意思，下面的更高效，但上面更常用。这句话意思是求每个人的经理人select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);//把额外的取出来，左外连接select empno, ename,mgr,mgrname from emp left join (select empno mgrempno,ename mgrname from emp) t on(emp.mgr = t.mgrempno);select ename,dname from emp e join dept d on(e.deptno = d.deptno);select ename,dname from emp e right outer join dept d on(e.deptno = d.deptno);//右外连接，把额外的拿出来select ename ,dname from emp e full join dept d on(e.deptno = d.deptno);//全外连接,很可惜，mysql不支持13)子查询，多表联接例子select ename, sal from emp where sal = (select max(sal) from emp); //选sal最多的一组人//避免了select ename, max(sal) from emp;这个语句不能对一堆人的查询的错误select ename, sal from emp where sal > (select avg(sal) from emp); //选sal在平均之上的select deptno,ename, avg_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 deptno, avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.local and s.hisal);//求部门平均薪水的等级select deptno,ename, grade from emp join salgrade s on (emp.sal between s.local and s.hisal);//每个人的薪水等级select deptno,avg(grade) from(select deptno,ename, grade from emp join salgrade s on (emp.sal between s.local and s.hisal))t group by deptno;//求部门平均的薪水等级select ename from emp where empno in(select distinct mgr from emp);//雇员中有哪些人是管理者,包括king。select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));//不用组函数求最高薪水select max(avg_Sal),deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) e1;select deptno , avg_sal from (select avg(sal) avg_sal ,deptno from emp group by deptno)e3 where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno)e1);//求部门平均薪水的最高的部门编号//两个方法select dname from dept where deptno =(  select deptno from     (select avg(sal) avg_sal ,deptno from emp group by deptno)e3       where avg_sal =         (select max(avg_sal) from     (select avg(sal) avg_sal, deptno from emp group by deptno)e1        ));//求平均薪水最高的部门的部门名称select dname, t1.deptno , grade ,avg_sal from  (    select deptno, grade, avg_sal from       (select deptno, avg(sal) avg_sal from emp group by deptno) t    join salgrade s on (t.avg_sal between s.local and s.hisal)  ) t1  join dept on (t1.deptno = dept.deptno)  where t1.grade =   (    select min(grade) from    (      select deptno, grade , avg_sal from        (select deptno, avg(sal) avg_sal from emp group by deptno)t      join salgrade s on (t.avg_sal between s.local and s.hisal)    )t2  );//求平均薪水等级最低的部门的部门名称create view t as select deptno, avg(sal) avg_sal from emp group by deptno;create view v$_dept_avg_sal_info asselect deptno, grade , avg_sal from tjoin salgrade s on (t.avg_sal between s.local and s.hisal); //mysql的bug,不能使用子查询select dname, t1.deptno , grade ,avg_sal from  v$_dept_avg_sal_info t1  join dept on (t1.deptno = dept.deptno)  where t1.grade = (select min(grade)from v$_dept_avg_sal_info);//求平均薪水等级最低的部门的部门名称(使用视图)视图的小科普~select ename from empwhere empno in (select distinct mgr from emp where mgr is not null) andsal >(  select max(sal) from emp   where empno not in (select distinct mgr from emp where mgr is not null));//比普通员工的最高薪水还要高的经理人名称select ename,empno,sal from emp order by sal desc limit 0,5; //从第0行起取5个//取薪水最高的前5名员工select ename, sal from (select ename ,sal fron emp order by sal desc) where rownum <= 5;//不知道行不行的方法select ename,empno,sal from emp order by sal desc where rownum <= 5;//oracle写法，有一定限制（如where rownum > 10;>不能用，只能用<或者<=）马士兵说(oracle)脑子进水了....（比较隔的设定）,解决方法：select ename from(select rownum r,ename from emp) where r > 10;面试题：有3个表S,C,SCS(SNO,SNAME)(学号,姓名)C(CNO,CNAME.CTEACHER)(课号，课名，教师)SC(SNO,CNO,SCGRADE)(学号，课号，成绩)select sname from s join sc on(s.sno = sc.sno) join c(c.cno = sc.cno) where c.cteacher <> 'liming';//找出没选过"liming"老师的所有学生姓名select sname where sno in(select sno from sc where scgrade < 60 group by sno having count(*) >= 2);//列出2门以上（含2门）不及格学生姓名及平均成绩select sname from s where sno in(select sno from sc where cno = 1 and sno in(select sno from sc where cno = 2));//学过1号课程又学过2号课程的所有学生的姓名常见用户语句(oracle):drop user xxx ;create user guanhong identified by guanhong defualt tablespace users quota 10M on users//创建新用户insert into mysql.user(host,user,password) values ("localhost","guanhong",password("guanhong"));插入语句：insert desc dept;create table emp2 as select * from emp;create table dept2 as select * from dept;create table salgrade2 as select * from salgrade;create table avc2 as select * from avc;create table emp3 as select * from emp;insert into dept2 values(50,'game','bj');insert into dept2(deptno,dname) values(60,'game2');insert into dept2 select * from dept;修改语句：updateupdate emp2 set sal=sal*2, ename=concat(ename,'-') where deptno = 10;删除语句：deletedelete from emp2;delete from dept2 where deptno <= 25;ddl数据定义语句(使用后事务自动提交)：create table tb (a varchar(10));drop table tb;create总汇：create table xx（表名）(xx xxx,xx xxx);(字段名 类型)create table class(id tinyint primary key,name varchar(20) not null);create table student (id int,name varchar(255) not null,  /* mySQL直接not null就是约束,不用constraint  */sex tinyint,age tinyint,sdate bigint, grade tinyint default 1 , class tinyint , email varchar(50),/*email varchar(50) unique */constraint stu_class_fk foreign key(class) references class(id),/*外键约束*/constraint stu_id_pk primary key(id),/*主键约束两种写法1）如上2）id int primary key;*/constraint stu_name_email_uni unique(email,name)/*对表约束,email和name的连接是唯一的*/);以下是测试：insert into class(id,name) values(100,'1班');insert into student(id, name,class,email) values(10,'a',100,'a');/*存在100,所以插入成功*/insert into student(id, name,class,email) values(1,'b',10,'b');/*不存在10,所以插入失败*/delete from class where id = 100;/*违反约束条件，所以不能删除*/修改表结构：alter table student add(addr varchar(100));//添加字段alter table student drop addr;//删除字段alter table student modify addr varchar(150);desc student;alter table student modify addr varchar(50);desc student;//修改字段alter table student drop foreign key stu_class_fk;//删除约束条件alter table student add constraint stu_class_fk foreign key(class) references class(id);//添加约束条件drop table student;//删除表数据字典表(view,约束，表):show tables;//查当前数据库的表名含有视图SELECT table_name FROM information_schema.`VIEWS` where table_schema= 'mydata';//查看视图SELECT * FROM information_schema.`TABLE_CONSTRAINTS` where table_name = 'student';//查看约束 SELECT * FROM information_schema.`TRIGGERS`;//查看触发器 SELECT table_name FROM information_schema.`tables`;//查看mysql总数据字典表index(索引)：create index idx_std_email on student (email);//对字段的建立索引//访问一个字段访问量大，访问效率低的时候可以用索引，但不要轻易建立（确实有用的时候才建）因为索引占用大量空间create index idx_std_email on student (email,class);//对两个字段的组合建立索引drop index idx_std_email;//删除索引show index from student;show keys from student;//查看索引view（视图）://视图就是一个字查询//试图建立要付出代价，就是视图维护比较麻烦，比如主表改了，视图也要跟着改create view v$_stu as select id,name,age from student;//视图的保护作用：可以只公开表中的某些信息，不完全公开desc v$_stu;//描述一下视图*视图可以用来更新数据，但是一般不用oracle序列（建立唯一的不间断地序列，作主键）：create table article(id number,title varchar2(1024),cont long)create sequence seq;/*创建序列*/select seq.nextval from dual;insert into article values(seq.nextval,'a','b');insert into article values(seq.nextval,'a','b');insert into article values(seq.nextval,'a','b');insert into article values(seq.nextval,'a','b');drop sequence seq/*删除序列*/mysql自动递增:create table article(id int NOT NULL auto_increment,/*自动递增*/title varchar(1024),cont longtext,primary key(id));三范式：范式：数据库设计的一些应当遵循的规则,姓范的兄弟制定出来的.............很有用，应该具体问题具体分析，必要时应该打破第一范式：不存在冗余数据1)要有主键2)列不可分（例：就是不能把学号，姓名什么的合成一个字段写info 2012_guanhong）范式二：当一张表有多个字段作为主键的时候，非主键的字段不能够依赖与部分主键(不能存在部分依赖)。范式三：每个非关键字列都独立于其他非关键字列，并依赖于关键字。触发器(oracle,mysql待定):create table emp2_log(uname varchar(20),action varchar(10),atime date);create or replace trigger trig    after insert or delete or update on emp2 /*for each row*///每次更新一条就做一次操作begin    if inserting then        insert into emp2_log values (user,'insert',sysdate);    elsif updating then        insert into emp2_log values (user,'update',sysdate);    elsif deleting then        insert into emp2_log values (user,'delete',sysdate);    end if;end;树状结构展示：drop table article;create table article(id int primary key,cont varchar(4000),pid int,idleaf int(1),alevel int(2));insert into article values (1,'蚂蚁大战大象',0,0,0);insert into article values (2,'大象被打趴下了',1,0,1);insert into article values (3,'蚂蚁也不好过',2,1,2);insert into article values (4,'瞎说',2,0,2);insert into article values (5,'没有瞎说',4,1,3);insert into article values (6,'怎么可能',1,0,1);insert into article values (7,'怎么没有可能',6,1,2);insert into article values (8,'可能性很大的',6,1,2);insert into article values (9,'大象进医院了',2,0,2);insert into article values (10,'护士是蚂蚁',9,1,3);蚂蚁大战大象    大象被打趴下了蚂蚁也不好过瞎说    没有瞎说大象进医院了    护士是蚂蚁    怎么可能怎么没有可能可能性很大的oracle的函数(展示递归)：create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is  cursor c is select * from article where pid = v_pid;  v_preStr varchar(1024) := '';begin   for i in 1..v_level loop    v_preStr := v_preStr || '    ';  end loop  for v_article in c loop    dbms_output.put_line(v_preStr || v_article.cont);    if(v_article.isleaf = 0) then      p(v_article.id , v_level + 1);    end if;  end loop;end;set serveroutput on;exec p(0);Transaction(事务)语句：dcl语句: