常见的数据库单表练习题
第一题：
表结构
CREATE DATABASE zy;
USE zy;
CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) DEFAULT NULL,
score INT(11) DEFAULT NULL,
address VARCHAR(255) DEFAULT NULL,
useremail VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
表数据
INSERT INTO student VALUES
(1,'张三',98,'北京','111111111@qq.com'),
(2,'李四',88,'上海','111111112@qq.com'),
(3,'王五',78,'广州','111111113@qq.com'),
(4,'赵六',68,'深圳','111111114@qq.com'),
(5,'孙七',58,'杭州','111111115@qq.com'),
(6,'小红',48,'北京','111111116@qq.com'),
(7,'小黑',99,'上海','111111117@qq.com'),
(8,'小绿',100,'杭州','111111118@qq.com'),
(9,'小粉',60,'杭州','111111119@qq.com'),
(10,'小紫',70,'黑龙江','111111110@qq.com');
查询语句
SELECT * FROM student;
SELECT id,NAME,score FROM student WHERE score>70;
ALTER TABLE student MODIFY COLUMN NAME VARCHAR(50);
DESC student;
ALTER TABLE student ADD pingjia VARCHAR(20);
UPDATE student SET score=88 WHERE NAME='张三';
SELECT * FROM student WHERE score>80;
SELECT * FROM student WHERE id IN(1,5,7);
SELECT * FROM student WHERE id BETWEEN 5 AND 8;
SELECT * FROM student WHERE NAME='小红' AND score>60;
SELECT * FROM student WHERE NAME='小红' OR score>90;
第二题：
表结构
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);
查询语句
SELECT * FROM emp;
SELECT * FROM emp WHERE ename='张三';
SELECT ename,job,sal FROM emp WHERE ename LIKE '___';
SELECT * FROM emp WHERE empno BETWEEN 1004 AND 1008;
SELECT * FROM emp WHERE job ='文员' AND ename='黄盖';
SELECT * FROM emp WHERE hiredate >'2001-01-01';
SELECT * FROM emp WHERE COMM IS NULL;
第三题：
表结构
CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) DEFAULT NULL,
score INT(11) DEFAULT NULL,
address VARCHAR(255) DEFAULT NULL,
useremail VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
表数据
INSERT INTO student VALUES
(1,'张三',98,'北京','111111111@qq.com'),
(2,'李四',88,'上海','111111112@qq.com'),
(3,'王五',78,'广州','111111113@qq.com'),
(4,'赵六',68,'深圳','111111114@qq.com'),
(5,'孙七',58,'杭州','111111115@qq.com'),
(6,'小红',48,'北京','111111116@qq.com'),
(7,'小黑',99,'上海','111111117@qq.com'),
(8,'小绿',100,'杭州','111111118@qq.com'),
(9,'小粉',60,'杭州','111111119@qq.com'),
(10,'小紫',70,'黑龙江','111111110@qq.com');
查询语句
SELECT id,NAME,address FROM student;
SELECT id,NAME,score FROM student;
ALTER TABLE student MODIFY COLUMN useremail VARCHAR(50);
ALTER TABLE student ADD pingjia VARCHAR(20);
UPDATE student SET score=92 WHERE NAME='张三'
SELECT * FROM student;
SELECT * FROM student WHERE score<80;
UPDATE student SET score=score+20 WHERE NAME='小红';
SELECT * FROM student WHERE id IN (1,5,7);
SELECT * FROM student WHERE id BETWEEN 4 AND 9;
SELECT * FROM student WHERE NAME='小红' AND score>60;
SELECT * FROM student WHERE NAME='小红' OR score>90;
SELECT * FROM student WHERE score IS NULL;
SELECT id,NAME,score FROM student WHERE NAME !='张三';
SELECT * FROM student ORDER BY score;
SELECT DISTINCT(address) FROM student;
第四题：
表结构
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);
查询语句
SELECT * FROM yuangong;
ALTER TABLE yuangong RENAME TO emp;
ALTER TABLE emp MODIFY COLUMN ename VARCHAR(40);
DELETE FROM emp WHERE empno=1014 AND ename='黄盖';
SELECT * FROM emp WHERE empno IN(1007,1009,1011);
SELECT * FROM emp WHERE job='文员' OR ename='张三';
SELECT * FROM emp WHERE hiredate>'2000-12-31';
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！