本文共 10360 字，大约阅读时间需要 34 分钟。

 

相关的sql语句也并不太难，英文有点基础就能过驾驭了，主要还是熟能生巧。

无法上传文件，就将emp.sql的文本放在最后面了，拉出去改后缀就好了

 

数据库相关SQL

- 查询所有 show databases; - 创建  create database db1 character set utf8/gbk; - 查看详情 show create database db1; - 删除数据库 drop database db1; - 使用 use db1;

 

 表相关SQL

- 创建 create table t1(name varchar(10),age int)charset=utf8/gbk; - 查询所有 show tables; - 查询详情 show create table t1; - 查询表字段 desc t1; - 添加表字段 alter table t1 add age int first/after xxx; - 删除表字段 alter table t1 drop age; - 修改表字段 alter table t1 change 原名 新名 新类型 - 修改表名  rename table t1 to t2;

 

数据相关SQL

- 插入数据  insert into t1(字段名1,字段名2) values(值1,值2),(值1,值2),(值1,值2); - 查询数据  select 字段信息 from t1 where 条件; - 修改数据  update t1 set xxx=xxx where 条件; - 删除数据  delete from t1 where 条件;

lg：

1. 创建数据库mydb2 字符集utf8  并使用	create database mydb2 character set utf8;	use mydb2;2. 在数据库中创建员工表emp 字段:id,name,sal,deptId(部门id) 字符集utf8	create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8	create table dept(id int,name varchar(10),loc varchar(10))charset=utf8;4. 部门表插入以下数据: 1 神仙部 天庭       2 妖怪部  盘丝洞 	insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');5. 员工表插入一下数据:  1 悟空 5000 1       2 八戒  2000  1 	insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',6000,2),(4,'白骨精',8000,2);6. 查询工资6000以下的员工姓名和工资	select name,sal from emp where sal<6000;7. 修改神仙部的名字为取经部	update dept set name='取经部' where id=1;8. 给员工添加奖金comm字段 	alter table emp add comm int;9. 修改部门id为1的部门奖金为500	update emp set comm=500 where deptId=1;10. 把取经部的地址改成五台山	update dept set loc='五台山' where id=1;11. 删除两个表 	drop table emp;	drop table dept;

 

 

###主键约束 primary key

- 什么是约束: 创建表时给表字段添加的限制条件 - 什么是主键: 表示数据唯一性的字段称为主键 - 主键约束: 唯一且非空     create table t1(id int primary key,name varchar(10));     insert into t1 values(1,'aaa');   //成功     insert into t1 values(1,'bbb');   //失败 不能重复     insert into t1 values(null,'ccc'); //失败 不能为null

###主键约束+自增 - 自增规则:从历史最大值+1     create table t2(id int primary key auto_increment,name varchar(10));     insert into t2 values(null,'a');    1     insert into t2 values(null,'b');    2     insert into t2 values(10,'c');      10     insert into t2 values(null,'d');    11     delete from t2 where id>=10;     insert into t2 values(null,'e');    12

###导入 *.sql文件 - 下载emp.zip  解压得到emp.sql文件 复制到 d:/根目录 - 在命令行中执行    source d:/emp.sql;   - 执行select * from emp; 如果出现中文乱码 执行 set names gbk; 再次查询即可

###去重 distinct  1. 查询员工表有哪几种不同的工作     select distinct job from emp;

###is null 和 is not null 1. 查询没有上级领导的员工信息     select * from emp where mgr is null; 2. 查询有上级领导的员工姓名和mgr     select ename,mgr from emp where mgr is not null;

###比较运算符 > < >= <= = !=和<> 1. 查询员工工资小于等于3000的姓名和工资     select ename,sal from emp where sal<=3000; 2. 查询工作不是程序员的员工姓名和工作(两种写法)     select ename,job from emp where job!='程序员';     select ename,job from emp where job<>'程序员';

###and 和 or - and类似java中的&&   or类型java中的|| - 多个条件同时满足使用and    满足其中一个用or 1. 查询1号部门工资高于2000的员工信息     select * from emp where deptno=1 and sal>2000; 2. 查询工作是人事或者工资大于3000的员工姓名 工作和工资.     select ename,job,sal from emp where job='人事' or sal>3000;

###in(x,y,z)（是某值） 1. 查询工资为3000,1500,5000的员工信息     select * from emp where sal=3000 or sal=1500 or sal=5000;      select * from emp where sal in(3000,1500,5000);

###between x and y（两者之间） 1. 查询工资在2000到3000之间的员工信息     select * from emp where sal>=2000 and sal<=3000;     select * from emp where sal between 2000 and 3000; 2. 查询工资在2000到3000之外的员工信息     select * from emp where sal not between 2000 and 3000;

 

###综合练习1. 查询有上级领导并且是3号部门的员工信息    select * from emp where mgr is not null and deptno=3;2. 查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号    select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;3. 查询1号部门工资为800和1600的员工信息     select * from emp where deptno=1 and sal in(800,1600);4. 查询1号和2号部门工资高于2000的员工信息    select * from emp where deptno in(1,2) and sal>2000;5. 查询员工表中出现的部门编号有哪几个    select distinct deptno from emp;

 

###模糊查询 like - %: 代表0或多个未知字符 - _: 代表1个未知字符 - 举例:     1. 以x开头    x%     2. 以x结尾    %x     3. 包含x      %x%     4. 第二个字符x    _x%     5. 倒数第三个是x    %x__     6. 以x开头倒数第二个是y  x%y_ 1. 查询员工表姓孙的员工     select * from emp where ename like '孙%'; 2. 查询工作中包含销售的员工姓名和工作     select ename,job from emp where job like '%销售%'; 3. 查询名字中以精结尾的员工姓名     select ename from emp where ename like '%精'; ###排序 order by - order by 字段 asc/desc; 1. 查询每个员工的姓名和工资,然后按照工资升序排序     select ename,sal from emp order by sal; 2. 查询每个员工的姓名,工资和部门编号,按照部门编号降序排序     select ename,sal,deptno from emp order by deptno desc; - 多字段排序:只需要在order by后面写多个排序的字段即可 3. 查询每个员工的姓名,工资和部门编号,按照部门编号降序排序,如果部门编号一致则按照工资升序排序     select ename,sal,deptno from emp order by deptno desc,sal;    

 

###分页查询limit - 格式: limit 跳过的条数,请求条数或每页条数 1. 查询员工表第一页的3条数据     select * from emp limit 0,3; 2. 查询员工表第2页的3条数据     select * from emp limit 3,3; 3. 查询员工表第3页的2条数据     select * from emp limit 4,2; 4. 查询员工表工资最高的前三名     select * from emp order by sal desc limit 0,3; 5. 查询员工表中第4页的2条数据     select * from emp limit 6,2;

 

###数值计算+-*/ 1. 查询每个员工的姓名,工资及年终奖(5个月的工资)     select ename 名字,sal 工资,5*sal 年终奖 from emp; 2. 查询每个员工的姓名 工资及涨薪5块钱之后的工资     select ename 姓名 ,sal 工资,sal+5 涨薪 from emp;

 

###聚合函数 - 对查询的多条数据进行统计查询 - 平均值avg(字段)     1. 查询1号部门的平均工资     select avg(sal) from emp where deptno=1; - 最大值max(字段)     1. 查询2号部门的最高工资     select max(sal) from emp where deptno=2; - 最小值min(字段)     1. 查询2号部门的最低工资     select min(sal) from emp where deptno=2; - 求和sum(字段)     1. 查询1号部门和2号部门的工资总和     select sum(sal) from emp where deptno in(1,2); - 计数count(字段)     1. 查询员工表中的人数     select count(*) from emp;     2. 查询1号部门工资大于2000块钱的人数     select count(*) from emp where deptno=1 and sal>2000;

###练习题1. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页的2条数据	select ename,sal from emp where sal>2000 order by sal limit 2,2;2. 查询和销售相关的工作的工资总和	select sum(sal) from emp where job like '%销售%';3. 查询程序员人数	select count(*) from emp where job='程序员';4. 查询1号部门中有领导的员工中的最高工资	select max(sal) from emp where deptno=1 and mgr is not null;5. 查询2号部门的最高工资和最低工资 起别名	select max(sal) 最高,min(sal) 最低 from emp where deptno=2;6.  查询1号部门里面名字中包含空字的员工姓名	select ename from emp where deptno=1 and ename like '%空%';

 

 

###分组查询 group by 

1. 查询每个部门的平均工资     select deptno,avg(sal) from emp group by deptno; 2. 查询每个部门的最高工资      select deptno,max(sal) from emp group by deptno; 3. 查询每种工作的人数     select job,count(*) from emp group by job; 4. 查询每个部门工资高于1500的员工数量     select deptno,count(*) from emp where sal>1500 group by deptno; 5. 查询1号部门和2号部门的最高工资     select deptno,max(sal) from emp where deptno in(1,2) group by deptno; 6. 查询1号部门每种工作的人数     select job,count(*) from emp where deptno=1 group by job; 7. 查询1号和2号部门中每种工作的工资总和     select job,sum(sal) from emp where deptno in(1,2) group by job; 8. 查询每种工作的最低工资     select job,min(sal) from emp group by job;

 

###having - where后面只能写普通字段的条件,不能写聚合函数的条件 - having后面写聚合函数条件,结合group by使用. - 各个关键字的顺序:     select .... from 表名 where 普通字段条件 group by....having 聚合函数条件 order by....limit ....; 1. 查询每个部门的平均工资,只查询平均工资大于2000的部门     select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; 2. 查询每种工作人数只查询人数为1的     select job,count(*) from emp group by job having count(*)=1;     select job,count(*) c from emp group by job having c=1; 3. 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门      select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;

 

 

###综合练习1. 查询没有上级领导的员工编号empno，姓名，工资	select empno,ename,sal from emp where mgr is null;2. 查询有奖金的员工姓名和奖金	select ename,comm from emp where comm>0;3. 查询名字中包含精的员工姓名和工资	select ename,sal from emp where ename like '%精%';4. 查询名字中第二个字是八的员工信息	select * from emp where ename like '_八%';5. 查询1号部门工资大于2000的员工信息	select * from emp where deptno=1 and sal>2000;6. 查询2号部门或者工资低于1500的员工信息	select * from emp where deptno=2 or sal<1500;7. 查询工资为3000，1500，5000的员工信息按照工资升序排序	select * from emp where sal in(3000,1500,5000) order by sal;8. 查询3号部门的工资总和	select sum(sal) from emp where deptno=3;9. 查询每个部门工资大于1000的员工人数，按照人数升序排序	select deptno,count(*) c from emp 	where sal>1000 group by deptno order by c;10. 查询每种工作中有领导的员工人数按照人数降序排序	select job,count(*) c from emp 	where mgr is not null group by job order by c desc;11. 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序	select * from emp order by deptno,sal desc;12. 查询有领导的员工，每个部门的编号和最高工资	select deptno,max(sal) from emp where mgr is not null group by deptno;13. 查询有领导的员工，按照工资升序排序，第3页的2条数据	select * from emp where mgr is not null order by sal limit 4,2;14. 查询每个部门的工资总和，只查询有上级领导的员工并且要求工资总和大于5400，最后按照工资总和降序排序,只查询结果中的第一条数据	select deptno,sum(sal) s from emp 	where mgr is not null group by deptno 	having s>5400 order by s desc limit 0,1;

 

 

emp.sql文件，复制下来改就好了

/*SQLyog Ultimate - MySQL GUI v8.2 MySQL - 5.5.59-MariaDB : Database - newdb3**********************************************************************//*!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*/`newdb3` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `newdb3`;/*Table structure for table `dept` */DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` (  `DEPTNO` int(4) NOT NULL,  `DNAME` varchar(14) NOT NULL,  `LOC` varchar(13) DEFAULT NULL,  PRIMARY KEY (`DEPTNO`),  UNIQUE KEY `DNAME` (`DNAME`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `dept` */insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values (1,'神仙','天庭'),(2,'妖怪','盘丝洞'),(3,'普通人','北京'),(4,'赛亚人','外星球');/*Table structure for table `emp` */DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` (  `EMPNO` int(4) NOT NULL,  `ENAME` varchar(10) NOT NULL,  `JOB` varchar(9) DEFAULT NULL,  `MGR` int(4) DEFAULT NULL,  `HIREdate` date DEFAULT NULL,  `SAL` double(7,2) DEFAULT NULL,  `COMM` double(7,2) DEFAULT NULL,  `DEPTNO` int(4) DEFAULT NULL,  PRIMARY KEY (`EMPNO`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `emp` */insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREdate`,`SAL`,`COMM`,`DEPTNO`) values (1,'孙悟空','销售',4,'1980-12-17',800.00,NULL,1),(2,'猪八戒','销售',4,'1981-02-20',1600.00,300.00,1),(3,'沙僧','销售',4,'1981-02-22',1250.00,500.00,1),(4,'唐僧','销售经理',8,'1981-04-02',2975.00,NULL,1),(5,'刘备','项目经理',NULL,'1981-09-28',1250.00,1400.00,3),(6,'关羽','程序员',5,'1981-05-01',2850.00,NULL,3),(7,'张飞','程序员',5,'1981-06-09',2450.00,NULL,3),(8,'观音','CEO',NULL,'1981-11-17',5000.00,NULL,1),(9,'白骨精','人事',8,'1981-09-08',1500.00,0.00,2),(10,'蜘蛛精','人事',8,'1981-12-03',950.00,NULL,2),(11,'黑熊怪','市场',8,'1981-12-03',3000.00,NULL,2);/*!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 */;