3.MySQL数据库的基本操作-DQL 基本操作

最新推荐文章于 2024-07-23 14:05:29 发布

对自己好点儿i

最新推荐文章于 2024-07-23 14:05:29 发布

阅读量157

点赞数 4

分类专栏： MySQL数据库文章标签：数据库 mysql android

本文链接：https://blog.csdn.net/m0_67457606/article/details/136984445

版权

MySQL数据库专栏收录该内容

8 篇文章 1 订阅

订阅专栏

本文详细介绍了MySQL基础操作，包括创建表格、插入数据、SQL查询（如选择、过滤、排序和聚合函数应用），以及针对员工信息表的实例分析。

摘要由CSDN通过智能技术生成

MySQL基础练习

练习1

CREATE table student(
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
gender VARCHAR(20),
chinese int,
english int, 
math int 
);
INSERT into student VALUES(NULL,'张明','男',88,87,89),(NULL,'张明','男',69,97,85),
(NULL,'张三','男',55,87,66),(NULL,'李四','男',77,99,33),
(NULL,'laly','女',66,100,86),(NULL,'王五','男',99,66,55);
-- 1.查询表中所有学生的信息
SELECT *FROM student;
-- 2.查询表中所有学生姓名对应的英语成绩
SELECT name,ENGlish FROM	student;
-- 3.过滤表中的重复数据
SELECT DISTINCT * from student;
-- 4.统计每个学生总分
SELECT name ,(chinese+math+english) '总分' FROM student;
-- 5.在所有学生总分的基础上加10分的特长分
SELECT name , (chinese+math+english) +10 'new_total' FROM student;
-- 6.使用别名表示学生的分数
SELECT name '姓名',chinese '语文成绩', math '数学成绩' ,english '英语成绩' FROM student;
-- 7.查询英语大于90的同学
SELECT * from student where english > 90;
-- 8.查询总分大于200的
SELECT * ,(chinese+math+english) '总分' FROM student where (chinese+math+english) > 200;
-- 9.查询英语成绩在80-90之间
SELECT *FROM student WHERE english BETWEEN 80 and 90;
-- 10.查询数学分数不在80-90之间的
SELECT *FROM student WHERE  not ( math BETWEEN 80 and 90);
-- 11.查询数学分数为89 90 91 的同学
SELECT * FROM student where math=89 or math=90  or math=91;
SELECT * FROM student where in (89,90,91);
-- 12.查询所有李姓同学
SELECT * FROM student WHERE name  LIKE'李%';
-- 13.查询数学分80，或者总分200的人
SELECT *FROM student where math=80 or chinese + math + english =200;
-- 14.对数学成绩进行降序排序
SELECT * FROM student ORDER BY math DESC;
-- 15.对总分排序后进行输出
SELECT *,chinese + math + english 'total' FROM student ORDER BY chinese + math + english DESC;
-- 16.对姓李的学生总成绩排序输出
SELECT * FROM student where name LIKE '李%' ORDER BY chinese + math + english DESC;
-- 17.查询男生和女生多少人  并排序
SELECT gender,count(1) FROM student GROUP BY gender ORDER BY gender DESC;

练习2

CREATE TABLE emp(
empno int,
ename VARCHAR(20),
job VARCHAR(20),
mgr int ,
hiredate date,
sal int,
comm int,
deptno int
);
INSERT into emp VALUES(9369,'SMTH','CLERK',7902,'1980-12-17',800,null,20);
INSERT into emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-12-17',1800,null,30);
INSERT into emp VALUES(7521,'WARD','SALESMAN',7698,'1982-12-17',2800,null,40);
INSERT into emp VALUES(7566,'JONES','CLERK',7566,'1983-12-17',3800,null,20);
INSERT into emp VALUES(7654,'MARTIN','SALESMAN',7698,'1984-12-17',5800,null,30);
INSERT into emp VALUES(7698,'BLAKE','CLERK',7902,'1990-12-17',7800,null,10);
INSERT into emp VALUES(7782,'CLARK','SALESMAN',7698,'1980-12-17',6800,null,20);
INSERT into emp VALUES(7788,'SCOTT','SALESMAN',7698,'1980-12-17',5800,null,10);
INSERT into emp VALUES(9369,'SMTH','CLERK',7566,'1980-12-17',3800,null,20);

-- 1.按照员工号升序排列 不在10号部门的员工信息
SELECT *FROM emp  WHERE  not  deptno=10  ORDER BY empno ASC;

-- 2.查询姓名第二个字母不是‘A’ 且薪水大于1000的员工信息，按照年薪降序排序
-- ifnull(comm,0)  如果comm的值为NULL，则当做0，不为NULL还是原来的值
SELECT * FROM emp WHERE ename not LIKE '-A%' AND sal>1000 ORDER BY (sal*12+ifnull(comm,0));
-- 3.求每个部门的平均薪水
SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
-- 4.求每个部门最高薪水
SELECT deptno,max(sal) FROM emp GROUP BY deptno;
-- 5.求每个部门 每个岗位的最高薪水
SELECT deptno,job,max(sal) FROM emp GROUP BY deptno,job ORDER BY deptno;
-- 6.求平均薪水大于2000的部门编号
SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING avg(sal);