常用的sql语句

--创建一个数据库sql1create database sql1--创建一个部门表deptcreate table dept(deptno int primary key,deptname nvarchar(30),loc nvarchar(30))--创建一个员工表empcreate table emp(empno int primary key,ename nvarchar(30),job nvarchar(30),mgr int,hiredate datetime,sal numeric(10,2),comm numeric(10,2),deptno int foreign key references dept(deptno)--根据需要把dept创建为外键)MySQL中主键自增长create table customers(id int auto_increment primary key not null, name varchar(15));insert into customers(name) values("name1"),("name2");select id from customers;SQL server中主键自增长create table customers(id int identity(1,1) primary key not null, name varchar(15));insert into customers(name) values('name1'),('name2');select id from customers;例子：create table employee(id int identity(1,1) primary key not null,ename varchar(20),email varchar(20),hiredate datetime)insert into employee values('zhangsan','[e-mail]','1990-1-1')--针对外键--1、外键只能指向主键--2、外键和主键的数据类型要一致--查询时，能不用*尽量不要用，有时候需要的不是全部的数据--select 字段1，字段2 from 表名 where 条件--查找1982.1.1后入职的员工select * from emp where hiredate>'1982-1-1'--查找工资在2000到2500的员工,两种写法select * from emp where sal>2000 and sal<2500select * from emp where sal between 2000 and 2500--如何使用like操作符(模糊查询)--查询首字母为s的员工姓名和工资select ename from emp where ename like 's%'--百分号代表的是任意的--如何显示第三个字符为大写O的所有员工的姓名和工资？select ename,sal from emp where ename like '__O%'--两个下划线代表前两位，O在第三位，后面的任意的不确定的用百分号代替======================================================================day02select * from empselect empno,ename,sal from emp where sal=(select min(sal)from emp)select avg(sal)'平均工资',sum(sal)'总工资'from empselect  count(*)'总人数'from empselect empno,ename,sal from emp where sal>(select avg(sal) from emp)select empno,ename,sal from emp where sal=(select max(sal) from emp)select avg(sal)'平均工资',deptno'部门' from emp group by deptnoselect empno,ename,sal,deptno from emp order by deptnoselect avg(sal)'平均工资',min(sal)'最低工资',max(sal)'最高工资',   deptno,job from emp group by deptno order by deptnoselect avg(sal)'平均工资',min(sal)'最低工资',max(sal)'最高工资',       deptno from emp group by deptno order by deptnoselect sum(sal)/count(*) from emp;select * from dept--查询平均工资小于6000的部门的平均工资--having往往和group by结合使用，可以对分组查询的结果进行筛选select avg(sal),deptno from emp group by deptno having avg(sal)<6500--查询部门平均工资小于6500的部门号和平均工资,按照降序排列select avg(sal),deptno from emp group by deptno having avg(sal)<6500 order by avg(sal) descselect * from emp,dept where dept.deptname='研发部' and emp.deptno= dept.deptnoselect ename,sal,loc,deptname,emp.deptno from emp,dept where dept.deptname='研发部' and emp.deptno=dept.deptno order by ename desc--如何显示部门号为10的部门名，员工名，工资select deptname,ename,sal from emp e,dept d where e.deptno=10 and e.deptno=d.deptno--查询员工名,工资,所在部门名字,并按照部门排序select ename,sal,deptname from emp e,dept d where e.deptno=d.deptno order by deptname--自连接查询,查询所有员工和其对应的上级的姓名select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empnoselect * from empUPDATE "表格"SET "栏位1" = [值1], "栏位2" = [值2] WHERE "条件";===========================================================================day03--创建一个数据库sql1create database sql1--创建一个部门表deptcreate table dept(deptno int primary key,deptname nvarchar(30),loc nvarchar(30))--创建一个员工表empcreate table emp(empno int primary key,ename nvarchar(30),job nvarchar(30),mgr int,hiredate datetime,sal numeric(10,2),comm numeric(10,2),deptno int foreign key references dept(deptno)--根据需要把dept创建为外键)--针对外键--1、外键只能指向主键--2、外键和主键的数据类型要一致--查询时，能不用*尽量不要用，有时候需要的不是全部的数据--select 字段1，字段2 from 表名 where 条件--查找1982.1.1后入职的员工select * from emp where hiredate>'1982-1-1'--查找工资在2000到2500的员工,两种写法select * from emp where sal>2000 and sal<2500select * from emp where sal between 2000 and 2500--如何使用like操作符(模糊查询)--查询首字母为s的员工姓名和工资select ename from emp where ename like 's%'--百分号代表的是任意的--如何显示第三个字符为大写O的所有员工的姓名和工资？select ename,sal from emp where ename like '__O%'--两个下划线代表前两位，O在第三位，后面的任意的不确定的用百分号代替select * from emp where empno in (1,2,3,11) order by salselect empno,ename,sal,hiredate from emp where hiredate is null--复杂查询select empno,ename,sal from emp where sal=(select min(sal) from emp)select ename,sal,hiredate,job from emp where sal=(select max(sal) from emp)--显示所有员工的平均工资和总工资select avg(sal)'平均工资',sum(sal)'总工资'from emp--计算共有多少员工select count(*)'总人数' from emp--查询高于平均工资的员工select empno,ename,sal,job from emp where sal>(select avg(sal)from emp)--查询工资在5000到6500的人select * from emp where sal between 5000 and 6500--如何显示每个部门的平均工资和最高工资select avg(sal)'平均工资',max(sal)'最高工资',deptno'部门编号' from emp group by deptno order by deptno desc--显示每个部门的，每种岗位的平均工资和最低工资select avg(sal),min(sal),deptno'部门编号'from emp group by deptnoselect distinct job from emp group by deptno--显示平均工资低于4000的部门号，和他的平均工资--having往往对查询的结果进行筛选，如果在select语句中，同时包含了group by，having，order by，】--那么他们的顺序是group by，having，order byselect avg(sal)'部门平均工资',deptno'部门编号' from emp group by deptno having avg(sal)<6500order by deptno desc--复杂查询--多表查询：基于两个和两个以上的表和视图的查询，在实际应用中，查询单个表可能不可能满足你的需求--显示员工名，工资和所在部门的名字,当无法知道字段是属于哪个表的时候需要申明所属的表--也可以申明每个字段所属的表select ename,sal,deptname,d.deptno from emp e,dept d where e.deptno=d.deptno and d.deptname='财务部'--显示部门号为10的部门名，员工名，工资。select d.deptname,ename,sal,d.deptno from emp e,dept d where d.deptno=10 and d.deptno=e.deptno--显示员工名，工资，所在部门名，并按照部门排列select ename,sal,deptname from emp e,dept d where d.deptno=e.deptno order by deptname--自连接查询：是指在同一张表的连接查询--显示所有员工和其对应的上级的名字，分析员工和其上级的名字都在emp这张表中，我们可以--将其看两张emp表，分别命名为emp worker和emp bossselect worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empnoselect * from emp order by deptnoselect distinct job from emp group by deptnoselect *from emp e,dept d where e.deptno=d.deptno and deptname='财务部'select worker.ename'员工',boss.ename'上级' from emp worker,emp boss where worker.mgr=boss.empno --查询10号部门的工作相同的员工的名字、岗位、工资、部门号select * from emp where job in (select distinct job from emp where deptno=10)select *from emp where sal>(select avg(sal),deptno from emp group by deptno)--显示高于部门平均工资的工资的姓名，薪水，和他们部门的平均工资--分析1.首先要知道各个部门的平均工资select avg(sal) from emp group by deptno--2.把上面的查询结果当做一个临时表来对待select emp.ename,emp.sal,tem.myavg from emp,(select avg(sal)myavg,deptno from emp group by deptno) temwhere emp.deptno=tem.deptno and emp.sal>myavg--SqlServer分页查询--查询第5个到第10个入职员工（按照入职时间的先后顺序）--1.显示第1个到第4个入职的员工select top 4 empno,ename,hiredate from emp order by hiredateselect top 6 * from emp where empno not in(select top 4 empno from emp order by hiredate)order by hiredateselect * from emp order by hiredate--？请显示第11个到第13个入职的人员的信息select top 10 *from emp order by hiredateselect top 3 * from emp where empno not in(select top 10 empno from emp order by hiredate)order by hiredate--显示5到9的人的信息（按照薪水的低到高）select top 4 * from emp order by salselect top 5 *from emp where empno not in(select top 4 empno from emp order by sal)order by sal--显示员工编号1到5的人的信息select top 5 *from emp order by empnoselect top 3 * from emp where empno not in(select top 5 empno from emp order by empno)order by empno--之前通过自连接（内连接）查询了有上级的员工的姓名和其对应上级的姓名，--现在通过左连接查询除了有上级的信息，还有就是没有上级的信息，在Oracle中还可以使用(+)来表示左，右连接select w.ename,b.ename from emp w right join emp b on w.mgr=b.empnoselect count(*) from empcreate table test(testid int primary key identity(1,1),testname varchar(30) unique,testpass varchar(30) ,testage int)drop table testinsert into test(testname,testage)values('ee',24)select * from goods--创建一个goods表create table goods(goodsid nvarchar(50) primary key,goodsname nvarchar(80) not null,unitprice numeric(10,2)check (unitprice>0),categroy nvarchar(3)check (categroy in('食物','日用品')),provider nvarchar(50))insert into goods values('sh1002','牙刷',6,'日用品','上海一品')insert into goods values('bj5002','毛巾',15,'食物','北京yiyong6')insert into goods values('sz5002','毛巾',15,'食物','北京yiyong6')insert into goods values('j5002','毛巾',15,'食物','北京yiyong6')insert into emp (empno,empname,job,sal,deptno) values(2,'李四','财务经理',8000.33,2)update goods set goodsname='洗发水',unitprice=21,categroy='日用品',provider='广州日出'where goodsid='bj5002'--备份数据库操作 back（备份）backup database db_gxxlbj to disk='e:/gxxlbj.bak'backup database liangshanhero to disk='e:/liangshanhero.bak'--恢复数据库操作 restore（恢复）restore database liangshanhero from disk='e:/liangshanhero.bak'SqlServer中查询表的结构：sp_help table_name;=====================================================================================CREATE TABLE emp(empno INT AUTO_INCREMENT PRIMARY KEY NOT NULL,ename VARCHAR(20),job VARCHAR(30),mgr INT,sal NUMERIC(10,2),comm NUMERIC(10,2),deptno INT,FOREIGN KEY(deptno) REFERENCES dept(deptno))CREATE TABLE dept(deptno INT  PRIMARY KEY NOT NULL,dname VARCHAR(10),loc VARCHAR(10))INSERT INTO dept(deptno,dname,loc) VALUES(10,'研发部','深圳')INSERT INTO dept(deptno,dname,loc) VALUES(20,'人事部','上海')INSERT INTO emp(ename,job,mgr,sal,comm,deptno) VALUES('张三','项目经理',123,2345,234,10)====================================================================================OracleOracle里支持的对列查询--查询和smith部门号，工作一样的员工select * from emp wher(deptno,job)=(select deptno,job from emp where ename='smith')select emp.ename,emp.sal,tem.myavg from emp,(select avg(sal)myavg,deptno from emp group by deptno) temwhere emp.deptno=tem.deptno and emp.sal>myavg--在from子句中使用子查询也就是将子查询出来的结果当作一张表或视图--如何显示高于自己部门平均工资的员工信息思考：各个部门的平均工资是多少？select avg(sal),deptno from emp group by deptno;把上面查询的结果当作一个临时表对待====================================================================================有时候，为了对某个sql语句进行效率测试，需要海量数据时，可以使用下面的sql创建海量数据insert into tb_name(id,name,sal,job,deptno) select empno,ename,sal,job,deptno from emp====================================================================================Oracle分页查询--Oracle使用的是三层过滤来实现分页查询第一层：select * from emp where 条件 --按照条件查询出所有结果，但是不带分页条件第二层：select t1.* rownum rn from （select * from emp where 条件）t1 where rownum <=需要娶到的介绍条数第三层：select t2.* from(select t1.* rownum rn from （select * from emp where 条件）t1 where rownum <=需要娶到的介绍条数)where rn>=开始的条数=====================================================================================Oracle合并查询：两个查询结果的并集,关键字union(去掉重复的数据，重复数据只取一份)select * from emp where sal > 2500 unionselect * from emp where job ='manager'关键字union all该操作于union相似，但是不会取消重复行，而且不会排序intersect取交集select * from emp where sal > 2500 intersectselect * from emp where job ='manager'minus取差集 a1 minus a2  即a1减去a1和a2的交集Oracle左外连接select t1.id from tb1 left join tb2 on tb1.id=tb2.id另一种写法：select t1.id from tb1 t1,tb2 t2 where t1.id=t2.id(+)右外连接：select t1.id from tb1 right join tb2 on tb1.id=tb2.idselect t1.id from tb1 t1,tb2 t2 where t1.id(+)=t2.id+号在左表示右外连接，在右表示左外连接完全外连接：select t1.id from tb1 outer join tb2 on tb1.id=tb2.id以前的内连接查询：select t1.id,t2.ename from tb1 t1,tb2 t2 where t1.id = t2.id=====================================================================================Mybatis中的模糊查询：select * from user where username like '%${usernames}%'eclipse快捷键Ctrl+2，LAlt+shift+LAlt+shift+R========================================================================================MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)/*日期转字符串*/select date_format(JZKSRQ,'%Y-%c-%d %H:%i:%s') 日期 from healthtest.mb_medicalrecord where JZLSH = '20151221003';select date_format(JZKSRQ,'%Y-%c-%d') 日期 from healthtest.mb_medicalrecord where JZLSH = '20151221003';select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');select date_format('2013-11-11 22:23:01','%Y-%c-%d %H:%i:%s');select date_format('2013-11-11 22:23:01','%Y-%c-%d %h:%i:%s');select date_format('2013-11-11','%Y-%c-%d %H:%i:%s');select date_format('2013-11-11','%Y-%c-%d');MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30 /*字符串日期*/select str_to_date('2012-11-13','%Y-%c-%d')日期;select str_to_date('2012-11-13 22:12:13','%Y-%c-%d %H:%i:%s')日期;select str_to_date('2012/11/14','%Y/%c/%d');Oracle日期转字符串：select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1992-4-7'select  create_date from tb_employee t where to_char(create_date,'yyyy-MM-dd')>'2015-06-01'select  create_date from tb_employee t where to_char(create_date,'yyyy-MM-dd HH:mm:ss')>'2015-06-01 18:55:00'Oracle字符串转日期select to_date('2005-10-02,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dualselect to_date('2005-10-02','yyyy-mm-dd') from dual