数据库的一些基本sql语句

create database Test创建数据库
use Tset是进入这个数据库
drop table表名 删除一张表
delete *from 表名 删除一条记录
insert into 表名 values（）增加一条数据
update 表名 set 列名=新数据 where 列名 = ‘条件’ 更新数据
select top 3 *from emp order by sal
distinct是去重
Union是去重 union all不去重联表
left join和join区别 left是左边即使没有右边对应 也会显示
select e.* from emp e where not exists(
select 1 from hist h where h.eid=e.eid
)
exists返回真假
identity是自动增长 创建主键需要
check是约束
desc是降序
asc是升序
top n是取前n名
like是模糊查询
是先执行order by 再取前三

select * from emp;
    -- * 表示所有的
    -- from emp 表示从emp表查询

select empno, ename from emp;

select ename, sal from emp;

select ename, sal*12 as "年薪" from emp;
        --as 可以省略 记住: "年薪" 不要写成'年薪' 也不要写成 年薪

select ename, sal*12 as "年薪", sal "月薪", job from emp;

select 888 from emp;
    --ok
    --输出的行数是emp表的行数  每行只有一个字段，值是888

select 5;  --ok 
           --不推荐    

select deptno from emp; --14行记录 不是3行记录


去重个查询
select distinct deptno from emp;  --distince deptno 会过滤掉重复的deptno
select distinct comm from emp;  --distinct也可以过滤掉重复的null  或者说如果有多个null 只输出一个
select distinct comm, deptno from emp;--把comm和deptno的组合进行过滤 
select deptno, distinct comm from emp;--error 逻辑上有冲突

select 10000 from emp; --14行记录


between查询
--查找工资在1500到3000之间(包括1500和3000)的所有的员工的信息
select * from emp 
    where sal>=1500 and sal<=3000
等价于
select * from emp 
    where sal between 1500 and 3000

--查找工资在小于1500或大于3000之间的所有的员工的信息
select * from emp 
    where sal<1500 or sal>3000
等价于
select * from emp 
    where sal not between 1500 and 3000

    in查询
    select * from emp where sal in (1500, 3000, 5000)
等价于
select * from emp 
    where sal=1500 or sal=3000 or sal=5000

select * from emp where sal not in (1500, 3000, 5000) --把sal既不是1500 也不是3000也不是5000的记录输出
等价于
select * from emp 
    where sal<>1500 and sal<>3000 and sal<>5000
        --数据库中不等于有两种表示：  !=    <>   推荐使用第二种
        --对或取反是并且  对并且取反是或

null查询

    select * from emp;
--输出奖金非空的员工的信息
select * from emp where comm <> null; --输出为空 error
select * from emp where comm != null; --输出为空 error


select * from emp where comm = null;--输出为空 error
    --总结：null不能参与<>  !=  =运算

--null可以参与is  not is
select * from emp where comm is null; --输出奖金为空的员工的信息
select * from emp where comm is not null; --输出奖金不为空的员工的信息


--任何类型的数据都允许为null
create table t1 (name nvarchar(20), cnt int, riqi datetime);  
insert into t1 values (null, null, null)
select * from t1;


select * from emp;
--输出每个员工的姓名 年薪(包含了奖金)  comm假设是一年的奖金
select empno, ename, sal*12+comm "年薪" from emp;
    --本程序证明了：null不能参与任何数据运算 否则结果永远为空


 top 查询
 select * from emp;

select top 5 * from emp;
select top 15 percent * from emp; --输出的是3个，不是2个

--把工资在1500到3000之间(包括1500和3000)的员工中工资最高的前4个人的信息输出
select top 4 * 
    from emp
    where sal between 1500 and 3000
    order by sal desc  --desc降序 不写则默认是升序


    order by
    --asc是升序的意思 默认可以不写  desc是降序

select * from emp order by sal; --默认是按照升序排序
select * from emp order by deptno, sal; --先按照deptno升序排序，如果deptno相同，再按照sal升序排序

select * from emp order by deptno desc, sal;
    --先按deptno降序排序 如果deptno相同 再按照sal升序排序
    --记住sal是升序不是降序
    --order by a desc, b, c, d    desc只对a产生影响 不会对后面的b  c d 产生影响

select * from emp order by deptno, sal desc
    --问题：desc是否会对deptno产生影响?
    --答案：不会
    --先按deptno升序，如果deptno相同，再按sal降序



    isnull函数
    select ename, sal*12+isnull(comm, 0) "年薪" from emp;
    --isnull(comm, 0) 如果comm是null 就返回零 否则返回comm的值


    聚合函数

    select lower(ename) from emp; -- 最终返回的是14行 lower()是单行函数          转换成小写数值
select max(sal) from emp; --返回1行 max()是多行函数

select * from emp;

select count(*) from emp;  --返回emp表所有记录的个数
select count(deptno) from emp;  --返回值是14 这说明deptno重复的记录也被当做有效的记录
select count(distinct deptno) from emp; --返回值是3  统计deptno不重复的记录的个数
select count(comm) from emp; --返回值是4 这说明comm为null的记录不会被当做有效的记录


select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp; --ok 


select max(sal), lower(ename) from emp;     //这个聚合函数的组合就有问题 因为前面那个返回一行  后面那个返回了多行   所以要用group by  来组合



模糊查询法
select * from emp   where ename like '%A%'  --ename只要含有字母A就输出
select * from emp where ename like 'A%'  --ename只要首字母是A的就输出
select * from emp where ename like '%A'  --ename只要尾字母是A的就输出


select * from emp where ename like '_A%' --ename只要第二个字母是A的就输出


select * from emp where ename like '_[A-F]%' --把ename中第二个字符是A或B或C或D或E或F的记录输出 


select * from emp where ename like '_[^A-F]%' --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出 

create table student
(
    name varchar(20) null
    ,age int
);


insert into student values ('张三', 88);
insert into student values ('Tom', 66);
insert into student values ('a_b', 22);
insert into student values ('c%d', 44);
insert into student values ('abc_fe', 56);
insert into student values ('haobin', 25);
insert into student values ('HaoBin', 88);
insert into student values ('c%', 66)
insert into student values ('long''s', 100)
select * from student;

select * from student where name like '%\%%' escape '\'  --把name中包含有%的输出 
select * from student where name like '%\_%' escape '\' --把name中包含有_的输出

简单的创建和查询的例子

drop table emp
go
create table emp
(
    职员编号 int primary key identity,
    员工姓名 nvarchar(20) not null,
    员工年纪 int not null,
    毕业学校 nvarchar(20) ,
    员工工资 decimal(20,3)
)
go
insert into emp values('张三',25,'河南农业职业学院',5000.00)
Insert into emp values('李四',27, '郑州大学',4000.00);
Insert into emp values('王五',28, '郑州大学',6000.00);
Insert into emp values('赵六',30, '河南农业职业学院',7000.00);
Insert into emp values('王刚刚',23, '河南大学',5000.00);
Insert into emp values('刘随随',31, '河南农业职业学院',5000.00);
go
update emp set 员工工资=员工工资+3000 where 毕业学校='河南农业职业学院'
select *from emp where 员工年纪 between 27 and 30
select top 3*from emp
select *from emp order by 员工工资 asc
select *from emp where 员工姓名 like '王%'
select COUNT(1)from emp
select 毕业学校,AVG(员工工资) avg_sal from emp 
group by 毕业学校
having AVG(员工工资)>6000

周考测试题
1.查询有两份工作(经历)的员工信息
Select * from emp where eid in (
Select eid from hist group by eid having count(1)>1
)
2.查询所有的员工信息，要求结果集展示所有的员工姓名，姓名共几个字，所在部门的前两个字，字符格式的生日这四列信息。
Select e.*,left(d.dname,2) as 部门 from emp e left join dept d on e.did=d.did
3.查询行政部部和财务部的员工信息
Select * from emp e where did in(
Select did from dept where dname in(‘行政部’,’财务部’)
)
4.查询和”郑鸟”在同一个城市工作过的员工信息
Select * from emp e where eid in(
Select DISTINCT eid from hist where ress in(
Select ress from hist where eid=(
Select eid from emp where ename=’郑鸟’
)
)
)
5.查询平均工资大于7000的部门名称及其平均工资，并按平均工资逆序排列

Select d.dname,avg(sar) “avg_sar” from dept d
left join emp e on d.did=e.did
group by d.dname having avg(sar)>7000 order by avg_sar desc
6.查询没有工作经验的员工信息
Select * from emp e where eid not in(
Select distinct eid from hist
)
7.查询hist表的job字段，并去除重复
Select distinct job from hist
8.查询曾经在北京工作过的员工信息
Select * from emp where eid in(
Select distinct eid from hist where ress=’北京’
)
9.查询曾经在上海工作过的员工信息
Select * from emp where eid in(
Select distinct eid from hist where ress=’上海’
)
10.将第9题和第10题两个结果集合并，要求不去除重复
Select * from emp where eid in(
Select distinct eid from hist where ress=’北京’
)Union all
Select * from emp where eid in(
Select distinct eid from hist where ress=’上海’
)
11.根据部门号从高到低，工资从低到高列出每个员工的信息
Select * from emp order by did desc,sar asc
查询工资不在1000-2000之间的员工的编号、姓名、工资和部门号（not between … and… ）
select “E”.eid,”E”.ename,”E”.sar,”D”.did
from emp “E”
left join dept “D”
on “E”.did=”D”.did
where “E”.sar not between 1000 and 2000
12.查询emp表中员工姓名、工资、部门号，并按部门号升序排序，同一部门按工资降序排序
Select * from emp order by did asc,sar desc
13.查询工资经验为空的员工的编号、姓名、工资和部门号
Select * from emp where eid not in(
Select distinct eid from hist
)
14.一张成绩表score，有字段：班级classid，学号id，姓名name，成绩grade
a)求各个班的平均成绩
Select classid,avg(grade) from score group by classid
b)查询出比该班平均成绩高的学生的所有信息
Select * from score s, (
Select classid,avg(grade) avg_grade from score group by classid
)b where s.classid=b.classid and s.grade>b.avg_grade
15.神马是索引？什么时候适合建索引？什么时候不适合建索引？
一张表，该表保存了主键与索引字段，并指向实体表的记录，索引可以大大提高数据库的检索速度，查询频率高于修改频率，反之不合适。
16.神马是视图？视图的优点和缺点？
一次语句的查询结果是视图，这是临时结果，每次使用都要查询。
17.union和union all有什么不同？
Union 是合并去重，union all 合并但是不进行去重
18.in和exists的区别？
In 执行时 会 翻译成 多个‘=‘ 用 or 连接起来，适用于少量数据。
Exists 是存在性测试，速度更快。适用于大量数据。

数据库考试题
1.查询部门名称为”研发部”的所有员工
Select * from emp e where did=(
Select did from dept where dname=’研发部’
)

2.查询市场部和研发部的员工信息
Select e.* from emp e where did in(
Select did from dept d where d.dname in(‘市场部’,’研发部’)
)

3.查询和”郑鸟”在同一个城市工作过的员工信息
Select * from emp e where eid in(
Select eid from hist where ress in(
Select ress from hist where eid=(
Select eid from emp where ename=’东京子贱’
)
)
)

4.查询员工姓名,账号及其所在部门
Select e.*,a.aid,d.dname from emp e
left join acc a on e.eid=a.eid
inner join dept d on e.eid=d.eid

5.查询所有性别为“男”的员工姓名和工作经历
Select e.ename,h.* from emp e
inner join hist h on e.eid=h.eid where sex=’m’

6.查询每一个部门的部门名称及平均工资
Select d.dname,avg(sar) avg_sar from dept d
left join emp e on d.did=e.did group by d.dname

7.查询所有部门的部门经理的详细信息
Select e.* from emp e where exists(
Select 1 from dept d where d.eid=e.eid
)

8.查询比部门平均工工资高的员工信息
Select * from emp e,(Select did,avg(sar) avg_sar from emp e
group by did)b where e.did=b.did and e.sar>b.avg_sar
9.查询员工姓名,工资及工作地点
Select e.ename,e.sar,h.ress from emp e left join hist h on e.eid=h.eid order by ename

10.查询hist表的ress，要求去除重复
Select distinct ress from hist

11.查询没有工作经历的员工信息，要求用not exists
select e.* from emp e where not exists(
select 1 from hist h where h.eid=e.eid
)

12.查询没有账号的员工信息，要求用not in
Select * from emp e where eid not in(
Select distinct eid from acc
)

13.查询在北京和南京工作过的员工信息
Select * from emp e where eid in(
Select eid from hist where ress in(‘北京’,’南京’
)
)

14.修改研发部门所有员工的工资 工资+1
UPDATE emp set sar=sar+1 where eid in(
Select eid from dept where dname=’研发部门’
)

15.把“苏察哈尔灿”反转
Select reverse(‘苏察哈尔灿’);

16.把“I LOVE CODING”转小写
Select LOWER(‘I LOVE CODING’)

17.把“do you love coding?”转大写
select UPPER(‘do you love coding?’)

18.截取“尼古拉斯芳慧”的姓氏
Select left(‘尼古拉斯芳慧’,4)

19.把“世界舞王尼古拉斯芳慧”中的“芳慧”替换成“赵四”
Select replace(‘世界舞王尼古拉斯芳慧’,’芳慧’,’赵四’)

20.求-5的绝对值
Select abs(-5)

21.取3.1415926的整数
select CAST(3.1415926 as DECIMAL)
select CONVERT (3.1415926 ,DECIMAL)

22.求0到250之间的随机整数
Select RAND()*250

23.把当前日期转换成中国人的日期格式
select cast(now() as date)

24.Union和union all的区别？
前者去重，后者不去重

25.left join、right join与join的区别？（join等同于inner join）
left join 用左边的表连接右边的，右边找不到与左边匹配的记录时，把左边显示出来，右边为null
right join 用右边的表连接左边的，左边找不到与右边匹配的记录时，把右边显示出来，左边为null
left join 用左边的表连接右边的，右边找不到与左边匹配的记录时，不显示该条记录。