创建一个数据库db_test,设置字符集为utf8
create database db_test character set utf8;
查看全部数据库
show databases;
选择数据库db_test
use db_test;
查看db_tests数据库中的所有表
show tables;
创建表book
| 字段 | 类型 | 说明 |
|---|---|---|
| bid | int(4) primary key | 书编号 |
| bname | varchar(20) | 书名称 |
| bnum | int(3) | 购买量 |
| author | varchar(20) | 作者 |
| price | double(4,1) | 价钱 |
| edition | date | 版次 |
create table book(
bid int(4),
bname varchar(20),
bnum int(3),
author varchar(20)
price double(4,1),
edition date
);
查看表结构
desc book;
向表中插入数据
| bid | bname | bnum | author | price | Edition |
|---|---|---|---|---|---|
| 1001 | 数据库技术 | 200 | 张三 | 79 | 2012-4-1 |
| 1002 | 程序设计教程 | 300 | 李四 | 50.5 | 2013-12-1 |
| 1003 | 软件测试艺术 | 150 | 王五 | 100.5 | 2014-4-1 |
| 1004 | 功能自动化技术 | 235 | 赵六 | 89 | 2014-7-1 |
| 1005 | 性能自动化技术 | 400 | 田七 | 180.5 | 2015-6-1 |
| 1006 | 操作系统全解 | 147 | 腊八 | 200 | 2016-6-1 |
| 1007 | 软件成长之路 | 180 | 初九 | 65 | 2017-5-1 |
| 1008 | 计算机基础 | 600 | 正十 | 32 | 2018-4-1 |
| 1009 | 网站搭建 | 300 | 初一 | 55 | 2018-11-1 |
| 1010 | 手机测试技术 | 320 | 初二 | 60 | 2019-10-1 |
insert into book values(1001,'数据库技术',200,'张三',79,'2012-4-1');
更新book表中,书编号是1005的,书姓名修改为JMeter
update book set bname='JMeter' where bid=1005;
更新book表中,版次是2017-5-1的,作者改为张海燕
update book set author='张海燕' where edition='2017-5-1';
更新book表中,书名是网站搭建的,将购买量修改为500,价钱改为100
update book set bnum=500,price=100 where bname='网站搭建';
更新book表中,将购买量全部修改450
update book set bnum=450;
删除book表中,书编号是1009的记录
delete from book where bid=1009;
删除book表中,书名是操作系统全解记录
delete from book where bname='操作系统全解';
删除book表中,全部数据
delete from book;
删除表
drop table book;
#员工表
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP(
EMPNO INT(4) PRIMARY KEY,
ENAME VARCHAR(20) NOT NULL,
JOB VARCHAR(20),
MGR INT(4),
HIREDATE DATE,
SAL DOUBLE(7,2),
COMM INT(4),
DEPTNO INT(4)
);
#部门表
DROP TABLE IF EXISTS DEPT;
CREATE TABLE DEPT(
DEPTNO INT(4) PRIMARY KEY,
DNAME VARCHAR(20) NOT NULL UNIQUE,
LOC VARCHAR(20)
);
#学生表
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT(
SID INT(4) PRIMARY KEY,
SNAME VARCHAR(20),
SEX CHAR(3),
SCORE DOUBLE(4,1),
ADDRESS VARCHAR(50),
TYPE VARCHAR(20)
);
#工资等级表
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #工资等级
LOSAL double, #最低工资
HISAL double #最高工资
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,5000);
INSERT INTO SALGRADE VALUES (6,5001,7000);
SELECT * FROM SALGRADE;
INSERT INTO STUDENT VALUES (1001,'唐家三少','男',65,'杭州','羽毛球');
INSERT INTO STUDENT VALUES (1002,'TOMS','男',90,'北京','篮球');
INSERT INTO STUDENT VALUES (1003,'天蚕土豆丝','男',77,'成都','足球');
INSERT INTO STUDENT VALUES (1004,'我吃西红柿','男',88.5,'重庆','网球');
INSERT INTO STUDENT VALUES (1005,'JACK','男',75,'杭州','羽毛球');
INSERT INTO STUDENT VALUES (1006,'烽火戏诸侯','男',82.5,'广州','羽毛球');
INSERT INTO STUDENT VALUES (1007,'ROSE','女',100,'浙江','乒乓球');
INSERT INTO STUDENT VALUES (1008,'LUCY','女',99,'江西','乒乓球');
INSERT INTO STUDENT VALUES (1009,'孙悟空','男',65,'天津','足球');
INSERT INTO STUDENT VALUES (1010,'沙僧','男',70.5,'天津','足球');
INSERT INTO STUDENT VALUES (1011,'猪八里','女',90.5,'*','网球');
INSERT INTO STUDENT VALUES (1012,'JAMES','男',67,'湖北','网球');
INSERT INTO STUDENT VALUES (1013,'JACK','女',78,'内蒙古','网球');
INSERT INTO STUDENT VALUES (1014,'孙多多','女',90,'上海','乒乓球');
INSERT INTO STUDENT VALUES (1015,'孙权','男',80,'河北','羽毛球');
SELECT * FROM STUDENT;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','HEBEI');
INSERT INTO DEPT VALUES (20,'RESEARCH','HANGZHOU');
INSERT INTO DEPT VALUES (30,'OPERATION','CHENGDU');
INSERT INTO DEPT VALUES (40,'UI','CHONGQING');
INSERT INTO DEPT VALUES (50,'TESTING','BEIJING');
INSERT INTO DEPT VALUES (60,'DEVELOP','SHANGHAI');
INSERT INTO DEPT VALUES (70,'ROBOT','GUANGZHOU');
SELECT * FROM DEPT;
INSERT INTO EMP VALUES(7210,'TIANNI','CLERK',7902,'1980-4-20',500,100,60);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,1000,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1982-2-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1980-4-2',3500,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1983-5-1',2850,800,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-9-6',4450,200,10);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1983-5-20',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-6-10',1500,0,30);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',950,500,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,2000,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1982-11-23',1300,NULL,10);
INSERT INTO EMP VALUES(7945,'MITTER','CLERK',7782,'1983-6-23',1500,NULL,40);
INSERT INTO EMP VALUES(7950,'CONTER','MANAGER',7839,'1983-2-1',3500,300,90);
SELECT * FROM EMP;
drop table if exists stu01;
create table stu01(
sno int(8) primary key, ##学员编号
sname varchar(20), ##学员姓名
sex char(3), ##性别
age int(3), ##年龄
etime date, ##入学时间
address varchar(50) ##地址
);
insert into stu01 values(2019001,'Tom','男',20,'2019-10-12','北京');
insert into stu01 values(2019002,'Rose','男',22,'2014-11-2','上海');
insert into stu01 values(2019003,'Jack','男',25,'2016-10-3','广州');
insert into stu01 values(2019004,'Smith','男',30,'2018-11-5','深圳');
insert into stu01 values(2019005,'Jones','女','18','2018-10-12','成都');
insert into stu01(sno,sname,sex,age,etime) values(2019006,'Jims','男',20,'2014-12-9');
insert into stu01(sno,sname,sex,etime,address) values(2019007,'Polla','女','2015-10-8','重庆');
insert into stu01(sno,sname,age,etime) values
(2019008,'Tyta',28,'2016-12-5');
insert into stu01 values(2019009,'Koal','男',40,'2018-10-6','石家庄');
insert into stu01 values(2019010,'Hoyu','女',23,'2017-07-3','西安');
insert into stu01 values(2019011,'Finel','女',34,'2018-07-10','长沙');
insert into stu01(sno,sname,age,address) values(2019012,'Bill',23,'杭州');
drop table if exists cou01;
create table cou01(
cno int(4) primary key,##课程编号
cname varchar(30) ##课程名称
);
insert into cou01 values(1001,'数据库技术');
insert into cou01 values(1002,'软件测试');
insert into cou01 values(1003,'软件研发');
insert into cou01 values(1004,'影视编辑');
insert into cou01 values(1005,'市场营销');
drop table if exists sco01;
create table sco01(
sno int(8), ##学员编号
cno int(8), ##课程编号
grade double(4,1) ##学员成绩
);
insert into sco01 values(2019001,1001,80);
insert into sco01 values(2019002,1001,60);
insert into sco01 values(2019002,1002,90);
insert into sco01 values(2019003,1003,66.5);
insert into sco01 values(2019004,1005,78);
insert into sco01 values(2019001,1003,55.5);
insert into sco01 values(2019004,1003,66);
insert into sco01 values(2019006,1002,120);
insert into sco01 values(2019005,1002,78);
insert into sco01 values(2019007,1005,85);
insert into sco01 values(2019002,1003,92);
insert into sco01 values(2019003,1004,70.5);
insert into sco01 values(2019006,1003,75);
insert into sco01 values(2019008,1004,72);
insert into sco01 values(2019006,1004,70);
insert into sco01 values(2019005,1001,56);
insert into sco01 values(2019003,1005,80);
insert into sco01 values(2019004,1001,60);
insert into sco01 values(2019006,1005,66);
select * from stu01;
select * from cou01;
select * from sco01;
查询stu01表中,全部列的数据
select * from stu01;
查询cou01表中,课程编号、课程名称
select cno,cname from cou01;
查询sco01表中,学员编号、学员成绩并给每列起别名
select sno 编号,score 成绩 from sco01;
查询sco01表中,学员编号、学员成绩,根据学员成绩降序排列
select sno,score from sco01 order by score desc;
查询sco01表中,学员编号、学员成绩,根据学员成绩降序排列,在根据学员编号升序排列
select sno,score from sco01 order by score desc,sno asc;
查询sco01表中,课程编号并去重显示
select distinct cno from sco01;
查询stu01表中,第3条到第6条记录全部信息
select * from stu01 limit 2,4;
查询stu01表中,倒数后5条记录 ---?
select * from stu01 limit 7,5;
select * from stu01 order by sno desc limit 5;
查询stu01表中,学员编号是2020005的,学员的编号、学员姓名、学员年龄
select sno,sname,age from stu01 where sno=2020005;
查询stu01表中,学员姓名是Jones的,学员的编号、学员姓名、学员地址
select sno,sname,address from stu01 where sname='Jones';
查询stu01表中,学员姓名含有o的,学员的编号、学员姓名、学员性别
select sno,sname,sex from stu01 where sname like '%o%';
查询stu01表中,学员姓名首字母以J开头的,学员编号、学员姓名
select sno,sname from stu01 where sname like 'J%';
查询sco01表中,学员成绩在70~90之间的,学员的编号、学员成绩
select sno,score from sco01 where score between 70 and 90;
查询sco01表中,学员成绩不在60~80之间的,学员的编号、学员成绩
select sno,score from sco01 where not score between 60 and 80;
查询stu01表中,学员编号是2020001、2020003、2020009的学员信息
select * from stu01 where sno in(2020001,2020003,2020009);
查询stu01表中,除了学员编号是2020001、2020003、2020009的其他学员信息
select * from stu01 where not sno in(2020001,2020003,2020009);
查询stu01表中,学员姓名不含有o的或者地址是北京的学员信息
select * from stu01 where not sname like '%o%' or address='北京';
查询stu01表中,学员的总数量
select count(*) from stu01;
查询sco01表中,每个学员的编号和平均成绩
select sno,avg(score)
from sco01
group by sno;
查询stu01表中,男生和女生的人数
select sex,count(sex)
from stu01
where sex is not null
group by sex;
查询sco01表中,选修两门课程的学员学号
select sno
from sco01
group by sno
having count(*)=2;
删除stu01表中,学员编号是2020012,2020008,2020006的学员信息
delete from stu01 where sno in(2020012,2020008,2020006);
查询课程编号(cno)是1002的,学员的编号(sno),姓名(sname),年龄(age),地址(address)
select sno,sname,age,address
from stu01
where sno in(select sno from sco01 where cno=1002);
查询stu01表中,2018年入学的学生名单,根据学员编号降序排列
select * from stu01 where etime like '2018%' order by sno desc;
select * from stu01 where year(etime) ='2018' order by sno desc;
查询stu01表中,10月入学的学生名单,根据入学时间升序排列
select * from stu01 where month(etime) ='10' order by etime asc;
查询部门编号是30号部门的，员工的详细信息。
select * from emp where deptno=30;
查询职位是CLERK的,员工的编号、姓名、部门编号。
select empno,ename,deptno from emp where job='CLERK';
查询奖金多于基本工资的员工信息。
select * from emp where comm>sal;
查询奖金多于基本工资60%的员工信息。
select * from emp where comm>sal*0.6;
查询10号部门的经理(MANAGER)、20号部门的职员(CLERK)的员工信息。
select * from emp where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK');
查询10号部门的经理、20号部门的职员或者既不是经理也不是职员但是工资高于2000元的员工信息。
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (job<>'MANAGER' and job<>'CLERK' and sal>2000);
查询获得奖金的员工的信息。
select * from emp where comm is not null and comm<>0;
查询奖金少于100或者没有获得奖金的员工的信息。
select * from emp where comm<100 or comm is null;
查询姓名以A、B、S开始的员工信息。
select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%';
查询名字长度为6个字符的员工信息。
select * from emp where length(ename)=6;
查询名字中不包含R字符的员工信息。
select * from emp where not ename like '%R%';
查询员工的详细信息并按姓名升序排序。
select * from emp order by ename asc;
查询员工的信息并按职位降序工资升序排列。
select * from emp order by job desc,sal asc;
计算员工的日薪(按30天)。
select ename,round(sal/30,2) as dsal from emp;
查询姓名中包含A的员工信息。
select * from emp where ename like '%A%';
查询工资水平多于SMITH的员工信息。
select sal from emp where ename='SMITH';
select * from emp where sal>(select sal from emp where ename='SMITH');
查询部门编号及本部门的最低工资。
select deptno,min(sal) from emp
group by deptno;
查询工资水平多于平均工资的员工。
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
查询与30部门员工工资水平相同的员工姓名与工资。
select ename,sal from emp where sal in(select sal from emp where deptno=30);
查询员工职位及其从事此工作的最低工资。
select job,min(sal) from emp
group by job;
计算出员工的年薪(sal*12)，并且以年薪降序排序。
select sal,sal*12 as ysal from emp order by ysal desc;
查询拥有员工的部门名称、部门编号
select dname,deptno from dept where deptno in(select deptno from emp);
查询工资多于smith的员工信息。
select * from emp where sal>(select sal from emp where ename='smith');
查询员工和所属经理的姓名。
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno;
查询员工的入职日期早于其经理入职日期的,员工及其经理姓名。
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate;
查询员工姓名及其所在的部门名称。
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
查询职位是CLERK的,员工姓名和所在部门名称。
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.job='CLERK';
查询销售部(SALES)所有员工的姓名。
select e.ename
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES';
查询与SCOTT从事相同工作的员工。
select * from emp where job=(select job from emp where ename='SCOTT');
查询与30部门员工,工资水平相同的员工姓名与工资。
select ename,sal from emp where sal in(select sal from emp where deptno=30);
查询工资高于30部门所有员工工资水平的员工信息。
select * from emp where sal>(select max(sal) from emp where deptno=30);
查询部门编号、部门名称、部门地址及其每个部门的员工总数。
select e.deptno,d.dname,d.loc,count(e.deptno)
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno;
查询员工的姓名、所在部门名称及其工资。
select e.ename,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno;
查询员工的详细信息。(包括部门名称、地址)
select e.*,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
查询工资处于第四级别的员工的姓名,工资。
select e.ename,e.sal
from emp e,salgrade s
where e.sal between s.losal and s.hisal and s.grade=4;
查询部门名称为ACCOUNTING 的部门下,所有员工信息？
select * from emp where deptno=(select deptno from dept where dname='ACCOUNTING');
查询部门名称为SALES 的部门下,每月需要发出的工资总额？
select sum(sal) from emp where deptno =(select deptno from dept where dname='SALES');
查询部门名称为SALES 的部门的,部门经理(MANAGER)？
select * from emp where deptno =(select deptno from dept where dname='SALES') and job='MANAGER';
查询部门名称为RESEARCH的部门下,入职时间为 1980-12-17 的员工信息
select * from emp where deptno =(select deptno from dept where dname='RESEARCH') and hiredate='1980-12-17';
根据题目要求,写出SQL语句
name course score
张三 语文 85
张三 数学 82
李四 语文 85
李四 数学 81
李四 英语 88
王五 语文 75
王五 数学 85
王五 化学 99
查询student表中,学员每门课程,都大于80分的学生姓名
select name from student group by name having min(score)>80;
根据题目要求,写出SQL语句
name course score
张三 语文 85
刘建飞 数学 66
孙程 语文 85
刘城 数学 81
刘云 英语 70
王五 语文 75
刘建飞 英语 85
刘云 化学 80
查询student表中,“刘”姓学生中,平均成绩大于75分学员信息
select name,avg(score) from student
where name like '刘%'
group by name
having avg(score)>75;
根据题目要求,写出SQL语句
员工信息表A
员工编号 员工姓名 员工性别 联系电话
id name sex tel
1001 张三 男 13012345678
1002 李四 男 15510234567
1003 李丽 女 15778945612
员工亲属信息表B
员工编号id 亲属编号rid 亲属姓名rname 联系电话rtel
id rid rname rtel
1001 21001 张三四 13015345667
1002 21002 李四五 13512345678
1002 21003 李四五 13012345623
1003 21004 李丽英 13712345611
1001 21005 张三四 13012345678
1001 21006 张三二 13912345645
1002 21007 李思琪 13333345671
使用SQL向员工信息表A中,插入一条数据：(1004,张三丰,男，010-62570007)
insert into A表 values(1004,'张三丰','男',010-62570007);
用SQL查询语句查询出,亲属数量大于1的员工编码、员工姓名、员工亲属数量
select max(a.id),max(a.name),count(b.rid) from A a,B b where a.id=b.id group by b.id having count(b.rid)>1;
有部分员工的亲属信息录入重复,例如父亲信息录入两次,这类信息的特征是亲属名称相同,但是亲属编码不同,请用SQL语句将这类情况的员工编码、重复的亲属编码、亲属姓名查询出来
select a.id,b.rid,b.rname from A a,B b where a.id=b.id and b.rname in(select name from B group by name having count(rid)>=2);
查询课程名称为'软件测试'的,学员编号和学员姓名
select t.sno,t.sname
from stu01 t,cou01 c,sco01 s
where t.sno=s.sno and s.cno=c.cno and c.cname='软件测试';
查询课程编号为1001的学员姓名和所属单位
select t.sname,t.depart
from stu01 t,sco01 s
where t.sno=s.sno and s.cno=1001;
查询不选修课程编号为1005的学员姓名和所属单位
select t.sname,t.depart,s.cno
from stu01 t,sco01 s
where t.sno=s.sno and s.cno !=1005;
查询选修课程超过3门的学员编号和所属单位
select t.sname,t.depart
from stu01 t,sco01 s
where t.sno=s.sno
group by s.sno
having count(s.sno)>3;
查询所有课程都大于60分的,学员编号和学员姓名
select t.sno,t.sname
from stu01 t,sco01 s
where t.sno=s.sno
group by s.sno
having min(s.score)>60;
查询每门课程平均分小于60分的,课程编号和平均分
select cno,avg(score)
from sco01
group by cno
having avg(score)<60;
查询各门课程的平均成绩和课程编号
select cno,avg(score)
from sco01
group by cno;
查询没有选择课程编号是1002课程的学生信息
select * from stu01 where not sno in
(select sno from sco01 where cno=1002);
查询学习每门课程的课程名称,人数
select c.cname,count(*)
from cou01 c,sco01 s
where c.cno=s.cno
group by s.cno;
查询学习每门课程的课程名称,最高成绩
select c.cname,max(s.score)
from cou01 c,sco01 s
where c.cno=s.cno
group by s.cno;
查询Rose学习的课程名称和成绩
select c.cname,s.score
from stu01 t,cou01 c, sco01 s
where t.sno= s.sno and c.cno = s.cno and t.sname= 'Rose';
查询每个学员的名称和总成绩
select t.sname,sum(s.score)
from stu01 t, sco01 s
where t.sno=s.sno
group by s.sno;
查询将软件测试成绩降序排列
select c.cname,s.score
from cou01 c, sco01 s
where c.cno=s.cno and c.cname= '软件测试'
order by s.score desc;
查询地址是上海的姓名,年龄,地址,课程,成绩
select t.sname,t.age,t.address,c.cname,s.score
from stu01 t,cou01 c, sco01 s
where t.sno=s.sno and c.cno=s.cno and t.address= '上海';
查询至少有2名男生的单位
select depart,count(*)
from stu01
where sex= '男'
group by sex
having count(*)>=2;
查询每个学生的姓名和学龄
select sname,datediff(curdate(),etime)
from stu01; --按天算
select sname,(datediff(curdate(),etime)/365)
from stu01; --按年算
查询姓名是李四的老师,所讲的课程编号,课程名称
select cno,cname from cou01 where ctea= '李四';
查询学习数据库技术课程的,学员姓名,年龄,分数
select t.sname,t.age,s.score
from stu01 t,sco01 s,cou01 c
where t.sno=s.sno and c.cno=s.cno and c.cname= '数据库技术';
查询北京巨人单位所有人的平均分
select t.depart,avg(s.score)
from stu01 t,sco01 s
where t.sno = s.sno and t.depart= '北京巨人'
group by t.depart;
查询选学多门课程的同学中,最高分数
select sno,max(score)
from sco01
group by sno
having count(*)>=2;