create DATABASE ruirui CHARACTER set utf8;
use ruirui;
create table daorui(
id int(11),
name varchar(20),
score int(11),
address VARCHAR(50),
useremail VARCHAR(20)
)
insert into daorui VALUES (1,'张三',98,'北京','111111111@qq.com','1'),
(2,'李四',88,'上海','111111111@qq.com','1'),
(3,'王五',78,'广州','111111111@qq.com','1'),
(4,'赵六',68,'深圳','111111111@qq.com','1'),
(5,'孙七',58,'杭州','111111111@qq.com','1'),
(6,'小红',48,'北京','111111111@qq.com','1'),
(7,'小黑',99,'上海','111111111@qq.com','1'),
(8,'小绿',100,'杭州','111111111@qq.com','1'),
(9,'小粉',60,'杭州','111111111@qq.com','1'),
(10,'小紫',70,'黑龙江','111111111@qq.com','1');
delete from daorui;
SELECT * from daorui;
-- 5.使用sql语句查询出表中所有score>70的同学的id,name,score
select id,name,score from daorui where score>70;
-- 6.更改name字段的数据类型为varchar(50)
alter table daorui MODIFY COLUMN name VARCHAR(50)
-- 查询字段数据类型
desc daorui;
-- 7.向表中添加一个字段,字段名称为“pingjia”,字段类型为varchar(20)
alter table daorui add pingjia varchar(20);
update daorui set score=88 where name='张三';
9.如果80分为及格线,查询出所有及格的同学的详细信息
SELECT * from daorui where score>80;
-- 10.使用关键字in,查询id值是1或5或7的同学的基本信息
select * from daorui where id in(1,5,7);
-- 11.查询id值在5至8的所有同学的基本信息
select * from daorui where id BETWEEN 5 and 8
-- 12.查询姓名是小红并且分数大于60的同学的基本信息
select * from daorui where score>60 or name='小红';
-- 13.查询姓名是小红或者分数大于90的同学的基本信息
select * from daorui where score>90 or name='小红';
create table daozong(
empno int(11),
ename VARCHAR(50),
job varchar(50),
mgr int(11),
hiredata date,
sal decimal(7,2),
COMM decimal(7,2),
deptno int(11)
)
INSERT INTO daozong VALUES(1001,'甘宁','文员',1013,'2000-12-17',8000.00,NULL,20),
(1002,'黛绮丝','销售员',1006,'2001-2-20',16000.00,3000.00,30),
(1003,'殷天正','销售员',1006,'2001-2-22',12500.00,5000.00,30),
(1004,'刘备','经理',1009,'2001-4-02',29750.00,NULL,20),
(1005,'谢逊','销售员',1006,'2001-9-28',12500.00,14000.00,30),
(1006,'关羽','经理',1009,'2001-5-01',28500.00,null,30),
(1007,'张飞','经理',1009,'2001-9-01',24500.00,null,30),
(1008,'诸葛亮','分析师',1004,'2007-4-19',30000.00,null,10),
(1009,'曾阿牛','董事长',NULL,'2001-11-17',50000.00,null,20),
(1010,'韦一笑','销售员',1006,'2001-9-08',15000.00,0.00,30),
(1011,'周泰','文员',1008,'2007-5-23',11000.00,null,20),
(1012,'程普','文员',1006,'2001-12-03',9500.00,null,30),
(1013,'庞统','分析师',1004,'2001-12-03',30000.00,null,20),
(1014,'黄盖','文员',1007,'2002-1-23',13000.00,null,10),
(1015,'张三','文员',1007,'2002-1-23',53000.00,null,50)
-- 查询表中所有内容(10分)
select * from daozong;
-- 2.查询表中姓名是张三的所有消息记录(10分)
select * from daozong where ename='张三';
-- 3.查询表中姓名是三个字组成的所有员工的ename,job,sal字段的对应信息(10分)
select ename,job,sal from daozong where ename like '___'
-- 4.查询表中empno字段从1004至1008所有员工的记录(10分)
select * from daozong where empno BETWEEN 1004 and 1008;
-- 5.查询表中所有job字段是文员并且姓名是黄盖的员工的所有信息(10分)
select * from daozong where job='文员' and ename='黄盖';
-- 6.查询表中在2001年以后入职的员工信息(10分)
select * from daozong where hiredata>'2001';
-- 7.查询表中奖金(COMM)是NULL的员工信息(10分)
select * from daozong where COMM is NULL;
create table laodao(
id int(11),
name varchar(20),
score int(11),
address varchar(50),
useremail varchar(20)
)
insert into laodao VALUES (1,'张三',98,'北京','118991111@qq.com'),
(2,'李四',88,'上海','111111111@qq.com'),
(3,'王五',78,'广州','444111111@qq.com'),
(4,'赵六',68,'深圳','111111111@qq.com'),
(5,'孙七',58,'杭州','111111111@qq.com'),
(6,'小红',48,'北京','111221111@qq.com'),
(7,'小黑',99,'上海','111190111@qq.com'),
(8,'小绿',100,'杭州','111111111@qq.com'),
(9,'小粉',60,'杭州','788931111@qq.com'),
(10,'小紫',70,'黑龙江','118411111@qq.com')
select * from laodao;
DELETE from laodao;
-- 4.使用sql语句查询出表中id,name,和address字段的所有内容
select id,name,address from laodao;
-- 5.使用sql语句查询出表中所有同学的id,name,score
select id,name,score from laodao;
-- 6.更改useremail字段的数据类型为varchar(50)
alter table laodao MODIFY COLUMN useremail VARCHAR(50)
DESC laodao;
-- 7.向表中添加一个字段,字段名称为“pingjia”,字段类型为varchar(20)
alter table laodao add pingjia varchar(20)
-- 8.更改姓名是张三的同学的分数为92
update laodao set score =92 where name='张三';
select * from laodao;
-- 9.如果80分为及格线,查询出所有不及格的同学的详细信息
select * from laodao where score<80;
-- 10.把姓名是“小红”的同学的分数在原来的基础上+20
select name,sum(score+20) from laodao where name='小红'
-- 11.使用关键字in,查询id值是1或5或7的同学的基本信息
select * from laodao where id in(1,5,7)
-- 12.查询id值在4至9的所有同学的基本信息
select * from laodao where id BETWEEN 4 and 9;
-- 13.查询姓名是小红并且分数大于60的同学的基本信息
select * from laodao where name='小红' or score>60;
-- 14.查询姓名是小红或者分数大于90的同学的基本信息
select * from laodao where name='小红' or score>90
-- 15.查询score字段值是NULL的同学的基本信息
select * from laodao where score is NULL
-- 16.查询name不是张三的同学的id,name,和score
select id,name,score from laodao where name not in('张三')
-- 17.按地址升序,成绩降序
SELECT * from laodao ORDER BY useremail
SELECT * from laodao ORDER BY score desc
-- 18.对城市进行去重显示
SELECT DISTINCT(address) FROM laodao;
CREATE TABLE emp (
empno INT(11) NOT NULL AUTO_INCREMENT,
ename VARCHAR(255) DEFAULT NULL,
job VARCHAR(255) DEFAULT NULL,
mgr INT(11) DEFAULT NULL,
hiredate DATE DEFAULT NULL,
sal VARCHAR(255) DEFAULT NULL,
COMM VARCHAR(255) DEFAULT NULL,
deptno INT(11) DEFAULT NULL,
PRIMARY KEY (`empno`)
)
INSERT INTO emp VALUES
(1001,'甘宁','文员',1013,'2000-12-17','8000.00','',20),
(1002,'黛绮丝','销售员',1006,'2001-12-20','16000.00','3000.00',30),
(1003,'殷天正','销售员',1006,'2001-02-22','12500.00','5000.00',30),
(1004,'刘备','经理',1009,'2001-04-02','29750.00',NULL,20),
(1005,'谢逊','销售员',1006,'2001-09-28','12500.00','14000.00',30),
(1006,'关羽','经理',1009,'2001-05-01','28500.00',NULL,30),
(1007,'张飞','经理',1009,'2001-09-01','24500.00',NULL,10),
(1008,'诸葛亮','分析师',1004,'2007-04-19','30000.00',NULL,20),
(1009,'曾阿牛','董事长',NULL,'2001-11-17','50000.00',NULL,10),
(1010,'韦一笑','销售员',1006,'2001-09-08','15000.00','0.00',30),
(1011,'周泰','文员',1008,'2007-05-23','11000.00',NULL,20),
(1012,'程普','文员',1006,'2001-12-03','9500.00',NULL,30),
(1013,'庞统','分析师',1004,'2001-12-03','30000.00',NULL,20),
(1014,'黄盖','文员',1007,'2002-01-23','13000.00',NULL,10),
(1015,'张三','文员',1007,'2002-01-23','53000.00',NULL,50);
-- 1.查询表中所有内容(10分)
SELECT * FROM yuangong;
-- 2.修改表名为”emp”(10分)
ALTER TABLE yuangong RENAME TO emp;
-- 3.修改ename字段的类型为varchar(40) (10分)
ALTER TABLE emp MODIFY COLUMN ename VARCHAR(40);
-- 4.删除表中empno是1014并且ename是黄盖的员工信息(10分)
DELETE FROM emp WHERE empno=1014 AND ename='黄盖';
-- 查询表中empno字段的值是1007,1009或1011员工的所有记录(10分)
SELECT * FROM emp WHERE empno IN(1007,1009,1011);
-- 6.查询表中所有job字段是文员并且姓名是张三的员工的所有信息(10分)
SELECT * FROM emp WHERE job='文员' OR ename='张三';
-- 7.查询表中在2001年以后入职的员工信息(10分)
SELECT * FROM emp WHERE hiredate>'2000-12-31';