每个人都是从菜鸟变老鸟的,所以分享一波自己以前学习SQL的练习语句,希望对菜鸟们有用;
员工表:
CREATE TABLE `emp` (
`empno` int(11) NOT NULL,
`ename` varchar(50) DEFAULT NULL,
`job` varchar(50) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`COMM` decimal(7,2) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL,
PRIMARY KEY (`empno`),
KEY `fk_emp` (`mgr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `emp` VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO `emp` VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO `emp` VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO `emp` VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-04-19', '30000.00', null, '20');
INSERT INTO `emp` VALUES ('1013', '庞统', '分析师', '1004', '2001-12-03', '30000.00', null, '20');
INSERT INTO `emp` VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO `emp` VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO `emp` VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO `emp` VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO `emp` VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
INSERT INTO `emp` VALUES ('1011', '周泰', '文员', '1008', '2007-05-23', '11000.00', null, '20');
学生表:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`chinese` float DEFAULT NULL,
`english` float DEFAULT NULL,
`math` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
INSERT INTO students
VALUES (‘2’, ‘李进’, ‘67’, ‘98’, ‘89’);
INSERT INTO students
VALUES (‘1’, ‘张小明’, ‘89’, ‘98’, ‘90’);
INSERT INTO students
VALUES (‘3’, ‘王五’, ‘87’, ‘78’, ‘77’);
INSERT INTO students
VALUES (‘4’, ‘李一’, ‘88’, ‘98’, ‘90’);
INSERT INTO students
VALUES (‘5’, ‘李来财’, ‘82’, ‘84’, ‘67’);
INSERT INTO students
VALUES (‘6’, ‘张进宝’, ‘55’, ‘85’, ‘45’);
INSERT INTO students
VALUES (‘7’, ‘黄蓉’, ‘85’, ‘75’, ‘80’);
INSERT INTO students
VALUES (‘8’, ‘张一李’, ‘75’, ‘65’, ‘30’);
INSERT INTO students
VALUES (‘9’, ‘何李’, ‘75’, ‘65’, ‘90’);
INSERT INTO students
VALUES (‘10’, ‘单’, ‘75’, ‘65’, ‘30’);
INSERT INTO students
VALUES (‘12’, ‘jack’, ‘75’, ‘65’, ‘40’);
INSERT INTO students
VALUES (‘13’, ‘marry’, ‘75’, ‘65’, ‘60’);
DQL语句的作用:主要用于查询表中的数据。
- 查询所有的学生:
SELECT * FROM students;
- 只要查询学生姓名,语文
SELECT NAME,chinese FROM students;
- 查询的时候使用别名:
SELECT NAME AS 姓名 , chinese AS 语文 FROM students;(标准语法)
SELECT NAME 姓名 , chinese 语文 FROM students;(方言)
- 查询一个公司里面的工作岗位。–清除重复数据
SELECT DISTINCT(job) FROM emp;
5.在所有学生分数上加10分特长分—查询的结果是可以运算的
SELECT NAME, chinese+10 语文 ,math+10 数学, english+10 英语 FROM students;
6.查询姓名为’张小明’的学生成绩
SELECT * FROM students WHERE NAME = ‘张小明’;
7.查询英语成绩大于等于90分的同学
SELECT * FROM students WHERE english >=90;
- 查询总分大于200分的所有同学
SELECT NAME, (chinese+math+english) 总分 FROM students WHERE (chinese+math+english)>200;
- 查询数学分数为89或者90或者91的同学
方式一(OR): SELECT * FROM students WHERE math=89 OR math=90 OR math=91;
方式二(IN): SELECT * FROM students WHERE math IN (89,90,91);
10.查询英语分数在 80-90之间的同学,包含80和90
方式一: SELECT * FROM students WHERE english>=80 AND english<=90;
方式二: SELECT * FROM students WHERE english BETWEEN 80 AND 90;
11.查询所有姓’李’的学生成绩,%可以匹配0个字符或多个字符。 %代表了任意的字符,而且可以匹配多个字符
SELECT * FROM students WHERE NAME LIKE ‘李%’;
12.查询所有名’李’字的学生成绩. _ 代表任意的 一个字符。
SELECT * FROM students WHERE NAME LIKE ‘_%李%’;
13.查询所有姓李的学生,并且姓名只有两个字。
SELECT * FROM students WHERE NAME LIKE ‘李_’;
14.查询所有姓名中包含’李’的学生成绩
SELECT * FROM students WHERE NAME LIKE '%李%';
15.查询所有姓’李’的学生成绩,但姓名必须是三个字符,_表示1个字符
SELECT * FROM students WHERE NAME LIKE '李__';
16.对数学成绩排序(降序)后输出
ASC(升序,默认是升序)
DESC(降序)
SELECT * FROM students ORDER BY math DESC;
17.组合排序:第一参考数学,如果数学成绩一样根据语文排序
SELECT * FROM students ORDER BY math DESC,chinese DESC;
18.对总分排序(降序)后输出
SELECT NAME ‘姓名’ ,(chinese + math+english) ‘总分’ FROM students ORDER BY (chinese + math+english) DESC;
19.对姓’李’的学生总分排序(降序)输出
SELECT NAME ‘姓名’,(chinese + math+english) ‘总分’ FROM students WHERE NAME LIKE ‘李%’ ORDER BY (chinese + math+english) DESC;
20.统计一个班级共有多少学生 COUNT() 把所有的行数加起来。
SELECT COUNT(*) FROM students;
SELECT COUNT(IFNULL(math,0)) FROM students;
SELECT IFNULL(math,0) FROM students; IFNULL 如果该字段是NULL,那么使用默认值取代。
21。 统计数学成绩大于80的学生有多少个
SELECT COUNT(id) FROM students WHERE math >= 80;
22。 统计总分大于250的人数有多少人。
SELECT COUNT(*) FROM students WHERE (chinese+math+english)>=250;
23 。 统计一个班级数学总成绩(SUM)
SELECT SUM(math) ‘班级数学总成绩’ FROM students;
24.。统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese) 语文总分, SUM(math) 数学总分,SUM(english) 英语总分 FROM students;
25.。统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese+math+english) 总分 FROM students;
26.统计一个班级语文成绩平均分–AVG
SELECT AVG(chinese) FROM students;
27.求一个班级总分平均分
SELECT AVG(chinese+math+english) FROM students;
28.求班级最高分和最低数学分数 MIN MAX
SELECT MIN(math) FROM students;
SELECT MAX(math) FROM students;
29。统计工资大于15000的每个工作岗位人数。— group by
SELECT job,COUNT(job) FROM emp WHERE sal>=15000 GROUP BY job;
sal>15000这个条件数据是分组之前就存在的数据还是分组之后才存在的数据: 之前。
30.统计工资大于15000的每个工作岗位人数,并且该工作岗位的人数大于等于
工作岗位的人数大于等于3,这个是分组前就存在的数据还是分组后存在的数据?分组之后。如果
是分组后才存在的数据,那么需要写在HAVING上。
SELECT job ,COUNT(job) FROM emp WHERE sal>=15000 GROUP BY job HAVING COUNT(job)>=3;
u
31。limit语句
SELECT * FROM students LIMIT 开始索引值,总数;