MYSQL基本语法题目练习

-- 查询student表
SELECT * FROM student

-- 查询 math 分数大于 80 分的学生
SELECT NAME,math FROM student WHERE math>80;

-- 查询 english 分数小于或等于 80 分的学生
SELECT NAME,english FROM student WHERE IFNULL(english,0)<=80;

-- 查询 age 等于 20 岁的学生
SELECT NAME,age FROM student WHERE age=20;

-- 查询 age 不等于 20 岁的学生
SELECT NAME,age FROM student WHERE age!=20;

-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
SELECT NAME,sex,age FROM student WHERE age>35 AND sex='男';

-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
SELECT NAME,sex,age FROM student WHERE age>35 OR sex='男';

-- 查询 id 是 1 或 3 或 5 的学生
SELECT id,NAME FROM student WHERE id=1 OR id=3 OR id=5;

-- 查询 english 成绩大于等于 75，且小于等于 90 的学生
SELECT NAME,english FROM student WHERE english>=75 AND english<=90;

-- 查询姓马的学生
SELECT NAME FROM student WHERE NAME LIKE '马%';

-- 查询姓名中包含'德'字的学生
SELECT NAME FROM student WHERE NAME LIKE '_德%';

-- 查询姓马，且姓名有两个字的学生
SELECT NAME FROM student WHERE NAME LIKE '马__';

-- 查看表详细结构
DESC employee;

-- 查询员工的所有信息
SELECT * FROM employee;

-- 查询所有员工的姓名和职位
SELECT ename,job FROM employee;

-- 查询所有员工的职位(去掉重复的)
SELECT DISTINCT job FROM employee;

-- 查询所有员工的姓名和总金额(薪资+佣金)
SELECT ename,(sal+IFNULL(comm,0)) FROM employee;

-- 查询部门编号等于20的所有员工信息
SELECT ename,deptno FROM employee WHERE deptno=20;

-- 查询职位是clear并且薪资大于1000的员工信息
SELECT ename AS 员工姓名,job AS 职位,sal AS 薪资 FROM employee WHERE job LIKE 'clear' AND sal>1000;

-- 查询薪资在2000到3000之间的所有员工信息
SELECT ename,sal FROM employee WHERE sal>=2000 AND sal<=3000;

-- 查询佣金是null 的所有员工信息
SELECT ename,comm FROM employee WHERE comm IS NULL;

-- 查询部门编号在10和20中的所有员工信息
SELECT ename,deptno FROM employee WHERE deptno>=10 AND deptno<=20;

-- 查询员工姓名包含M的所有员工信息
SELECT * FROM employee WHERE ename LIKE '%M%';

-- 查询所有员工信息按照薪资降序排列
SELECT ename,sal FROM employee ORDER BY sal DESC;

-- 查询所有员工信息先按照薪资升序排列在按照雇佣时间降序排列
SELECT ename,Hiredate,sal FROM employee ORDER BY sal ASC,Hiredate DESC;

-- 统计所有员工的个数
select count(*) as 员工总数 from employee;

-- 查询部门编号等于10的所有员工的平均薪资
select ename,avg(sal),deptno from employee where deptno=10;

-- 查询所有员工中薪资最高的员工信息
SELECT ename,sal FROM employee  ORDER BY sal desc limit 0,1;

-- 查询每个部门的最高薪资 
select deptno,max(sal) from employee group by deptno;

-- 查询每个部门的员工个数


-- 查询每个部门平均薪资在2000以上的部门编号和平均薪资

创建数据库和表

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.5.40 : Database - db1
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db1` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db1`;

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `empno` int(11) DEFAULT NULL,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(40) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `Hiredate` date DEFAULT NULL,
  `sal` double(10,2) DEFAULT NULL,
  `comm` double(10,2) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employee` */

insert  into `employee`(`empno`,`ename`,`job`,`MGR`,`Hiredate`,`sal`,`comm`,`deptno`) values (7369,'SMITH','CLEAR',7902,'1981-03-12',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1982-03-12',1600.00,300.00,30),(7521,'MARD','SALESMAN',7698,'1983-03-12',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-03-12',2975.00,NULL,20),(7654,'MARRTIN','SALESMAN',7698,'1981-03-12',2850.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-03-12',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1985-03-12',2450.00,NULL,20),(7788,'SCOTT','ANALYST',7566,'1981-03-12',3000.00,NULL,10),(7839,'KING','PRESIDENT',NULL,'1981-03-12',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1989-03-12',1500.00,0.00,30),(7876,'ADAMS','CLEAR',7788,'1998-03-12',1100.00,NULL,20),(7900,'JAMES','CLEAR',7698,'1987-03-12',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'2021-07-01',3000.00,NULL,20),(7934,'MILLER','CLEAR',7782,'1981-03-12',1300.00,NULL,10);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  `english` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`NAME`,`age`,`sex`,`address`,`math`,`english`) values (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;