MYsql3306

SQL server1433

Limit分页

pageSize条数pageCode页数

select * from student limit （pageCode-1）*pageSize（0，5，10），pageSize（5）

CREATE DATABASE staff
use staff

CREATE TABLE emp(
empno int PRIMARY KEY,-- 员工号
ename VARCHAR(20),-- 员工姓名
job VARCHAR(20),-- 工作
mgr VARCHAR(20),-- 上级编号
hiredate VARCHAR(20),-- 受雇日期
sal VARCHAR(20),-- 薪金
comm VARCHAR(20),-- 佣金
deptno VARCHAR(20)-- 所属部门编号
);

CREATE TABLE dept(
deptno int PRIMARY KEY, -- 部门 编号
dname VARCHAR(20), -- 部门名称
loc VARCHAR(20) -- 地点
);

insert into emp values(1101,'zs','java开发','d01','2001-11-1','','12k','10');
insert into emp values(1102,'dd','Android','d02','2001-11-1','1500','12k','20');
insert into emp values(1103,'zz','java开发','d01','2001-11-1','1600','12k','10');
insert into emp values(1104,'zmj','SQL server','d03','2001-11-1','1700','12k','30');
insert into emp values(1105,'cc','java开发','d01','2001-11-1','1000','12k','10');
insert into emp values(1106,'cp','java开发','d01','2001-11-1','1000',null,'10');
insert into emp values(1107,'zAj','SQL server','d03','2001-11-1','1700','12k','30');
delete from emp
insert into dept values(10,'Java','J工作室');
insert into dept values(20,'Android','A工作室');
insert into dept values(30,'Sql','S工作室');

select * from emp
-- 在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。
select ename as 员工姓名,deptno as 部门编号,sal as 薪水 from emp 
-- 在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。
select sal from emp where sal>1500 ORDER BY sal DESC
-- 在emp表中查询出comm字段为空值的记录。
select * from emp where comm=null
-- 查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）
select count(deptno) from emp distinct deptno
-- 在emp表中查询出部门编号为10或20的记录（要求使用IN关键字）
select * from emp where deptno in(select deptno from emp where deptno between 10 and 20)
-- 在emp表中查询出姓名询出姓名的第二个字母为A的记录。
select * from emp where ename like '_A%'
-- 查询出emp表中总共有多少条记录。
select count(empno) from emp
-- 查询emp表中出每个部门的部门代码、薪水之和、平均薪水。
select sum(sal),avg(sal),deptno from emp group by deptno

******************************************************************************************
接上
CREATE DATABASE staff
use staff

CREATE TABLE emp(
empno int PRIMARY KEY,-- 员工号
ename VARCHAR(20),-- 员工姓名
job VARCHAR(20),-- 工作
mgr VARCHAR(20),-- 上级编号
hiredate VARCHAR(20),-- 受雇日期
sal VARCHAR(20),-- 薪金
comm VARCHAR(20),-- 佣金
deptno VARCHAR(20)-- 所属部门编号
);

CREATE TABLE dept(
deptno int PRIMARY KEY, -- 部门 编号
dname VARCHAR(20), -- 部门名称
loc VARCHAR(20) -- 地点
);

insert into emp values(1101,'zs','java开发','d01','80','','12k','10');
insert into emp values(1102,'dd','Android','d02','90','1500','12k','20');
insert into emp values(1103,'zz','java开发','d01','100','1600','12k','10');
insert into emp values(1104,'zmj','SQL server','d03','60','1700','12k','30');
insert into emp values(1105,'cc','java开发','d01','66','1000','12k','10');
insert into emp values(1106,'cp','java开发','d01','56','1000',null,'10');
insert into emp values(1107,'zAj','SQL server','d03','88','1700','12k','30');
insert into emp values(1108,'SMITH','SQL server','d03','65','1000','12k','30');
insert into emp values(1109,'coco','CLERK','d02','77','1800','12k','20');
insert into emp values(1110,'mm','SALES','d03','88','1000','12k','30');
insert into emp values(1111,'SCOTT','SALES','d03','69','1000','12k','30');
insert into emp values(1112,'厝措','SALES','d03','200','2000','12k','20');
insert into emp values(1113,'MANAGER','java开发','d03','200','500','12k','20');
delete from emp
insert into dept values(10,'Java','J工作室');
insert into dept values(20,'Android','A工作室');
insert into dept values(30,'Sql','S工作室');

select * from emp
-- 在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。
select ename as 员工姓名,deptno as 部门编号,sal as 薪水 from emp 
-- 在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。
select sal from emp where sal>1500 ORDER BY sal DESC
-- 在emp表中查询出comm字段为空值的记录。
select * from emp where comm=null
-- 查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）
select count(deptno) from emp distinct deptno
-- 在emp表中查询出部门编号为10或20的记录（要求使用IN关键字）
select * from emp where deptno in(select deptno from emp where deptno between 10 and 20)
-- 在emp表中查询出姓名询出姓名的第二个字母为A的记录。
select * from emp where ename like '_A%'
-- 查询出emp表中总共有多少条记录。
select count(empno) from emp
-- 查询emp表中出每个部门的部门代码、薪水之和、平均薪水。
select sum(sal),avg(sal),deptno from emp group by deptno

-- 1．列出至少有一个员工的所有部门。 
select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);
-- 2．列出薪金比“SMITH”多的所有员工。 
select * from emp where sal>(select sal from emp where ename = 'SMITH')
-- 3．列出所有员工的姓名及其直接上级的姓名。 
select ename,mgr from emp
-- 4．列出受雇日期早于其直接上级的所有员工。 
select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); 
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
select d.dname as 部门名称,e.* from emp e right outer join dept d on e.deptno=d.deptno
-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT e.ename,d.dname, t.totalemp FROM dept d JOIN emp e ON e.deptno = d.deptno JOIN ( SELECT DEPTNO, count(*) AS totalEmp FROM emp GROUP BY deptno ) t ON d.deptno = t.deptno 
WHERE job = 'CLERK';
-- 7．列出最低薪金大于1500的各种工作。 
select job from emp where sal>1500
-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
select ename from emp where job='SALES'
-- 9．列出薪金高于公司平均薪金的所有员工。 
select empno,ename from emp where sal>(select avg(sal) from emp)
-- 10．列出与“SCOTT”从事相同工作的所有员工。  
select ename from emp where job in (select job from emp where ename='SCOTT')
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
select ename,sal from emp where sal>(select max(sal) from emp where deptno='30')
-- 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
select count(empno),avg(sal),avg(hiredate) from emp group by deptno
-- 14．列出所有员工的姓名、部门名称和工资。 
select e.ename,d.dname,e.sal from emp e, dept d where e.deptno=d.deptno
-- 15．列出所有部门的详细信息和部门人数。 
select * from dept a left join (select deptno,count(*) from emp group by deptno) b on a.deptno=b.deptno ;
-- 16．列出各种工作的最低工资。 
select min(sal),job from emp GROUP BY job
-- 17．列出薪金最低的MANAGER的基本信息。 
select * from emp where sal in (select min(sal) from emp where ename='MANAGER')
-- 18．列出所有员工的年工资,按年薪从低到高排序。
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal ASC;

******************************************************************************************
create database demo
use demo

create table classinfo(
classid int primary key auto_increment,
className varchar(20)
);

create table student(
stuid varchar(20) primary key,
stuname varchar(20),
stusex char(2),
stuage int,
stuaddress varchar(50),
stuclassid int references classinfo(classid)

);
-- 添加数据
insert into student values ('1101','zz','男',20,'湖南长沙',1);
insert into student values ('1102','cc','男',22,'南阳信阳',1);
insert into student values ('1103','xx','男',20,'武汉重庆',2);
insert into student values ('1104','bb','男',23,'湖南长沙',1);
insert into student values ('1105','dd','男',20,'南阳新乡',3);
insert into student values ('1106','咪咪','女',33,'湖南长沙',1);
insert into student values ('1107','娜娜','女',25,'武汉重庆',2);
insert into student values ('1108','婷婷','女',30,'南阳信阳',3);
insert into student values ('1109','乐乐','女',20,'湖南长沙',3);
insert into student values ('1110','aca','女',20,'湖南长沙',2);
delete from student
delete from classinfo
insert into classinfo values (null,'J211201');
insert into classinfo values (null,'J211202');
insert into classinfo values (null,'J211203');
-- 查询班级信息
select * from student 
select * from classinfo
-- 修改学生地址
update student set stuage='25' where stuid = '1102'
-- 删除某个学生信息
delete from student where stuid = '1104'
-- 查询某个学生年龄大于20的学生信息
select * from student where stuage>24
-- 查询学生年龄在20到30之间的学生信息
select * from student where stuage between 20 and 30
-- 查询性别实女生并且年龄大于30的学生信息
select * from student where stusex='女' and stuage>30
-- 查询性别为女的学生信息
select * from student where stusex='女'
-- 查询地址是湖北武汉的学生信息
select * from student where stuaddress='湖北武汉'
-- 查询班级编号为3的学生信息
select * from student where stuclassid=3
-- 查询学生姓C的学生信息  模糊查询
select * from student where stuname like'c%'
-- 查询学生姓C的学生信息并且年龄大于24岁的学生信息
select * from student where stuname like'c%' and stuage>24
-- 查询学生姓名中有c的学生信息
select * from student where stuname like'%c%'
-- 内连接查询   inner join on 查询学生信息，显示班级名称
select s.*,c.className from student s inner join classinfo c on s.stuclassid=c.classid
-- 查询每个班级的学生人数
select count(*) as 人数,stuclassid from student group by stuclassid
-- 查询每个班级的学生人数,学生性别是男
select count(*) as 人数,stuclassid from student where stusex='男' group by stuclassid 
-- 查询每个班级平均年龄大于24的信息  分组再筛选
select avg(stuage) as 平均年龄 from student group by stuclassid having stuage>24 
-- 对年龄做一个升序的排序  降序desc
select * from student order by stuage desc
-- 分页-每页显示5行数据  pageSize=5,PageCode=3
select * from student limit 0,5
-- 外连接  左外（以左边的表为基准），右外(以右边的表为基准)
select s.* , c.className from student s left outer join classinfo c on s.stuclassid = c.classid

select s.* , c.className from student s right outer join classinfo c on s.stuclassid = c.classid
-- 查询前10条数据
select * from student limit 0,10

-- 查询班级编号为1的学生信息（子查询）
select * from student where stuclassid in(select classid from classinfo where className='J211201')

-- 查询年龄大于平均年龄的所有学生信息
select * from student where stuage>(select avg(stuage) from student)