MySQL数据库基本命令

基本数据命令

– 01.查看mysql服务器中所有数据库

show databases;

– 02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）

– 语法：USE 库名;

use db10;

– 03.查看当前数据库中的所有表

– 先进入某一个库,再查看当前库中的所有表

show tables;

– 04.删除mydb1库

– 语法：DROP DATABASE 库名;

drop database test2

如果存在删除test2，如果不存在则不执行也不报错

drop database if exists test2;

– 05.重新创建mydb1库，指定编码为utf8

– 语法：CREATE DATABASE 库名 CHARSET 编码;

– 需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；

– 如果不存在则创建mydb1;

create database test3 charset=utf8;

– 06.查看建库时的语句（并验证数据库库使用的编码）

– 语法：SHOW CREATE DATABASE 库名;

 show create database test3;

---

1.2.创建、删除、查看表

– 07.进入mydb1库，删除stu学生表(如果存在)

use mydb1;

– 语法：DROP TABLE 表名;

drop table stu;

删除表，如果存在删除，不存在不报错

drop table if exists stu;

– 08.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：

​ CREATE TABLE 表名(
​ 列名 数据类型,
​ 列名 数据类型,
​ …
​ 列名 数据类型
​ );
​ 创建stu表的SQL语句如下:

create table student(
	id int(11),
	name VARCHAR(32),
	sex char(2),
	birthday datetime,
  	score float
)

– 09.查看stu学生表结构
– 语法：desc 表名

desc stu;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QAqHWuXs-1617954028383)(C:\Users\Administrator.USER-20201125AL\AppData\Roaming\Typora\typora-user-images\image-20210408153333594.png)]

---

2.新增、更新、删除表记录

– 10.往学生表(stu)中插入记录(数据)

– 语法：INSERT INTO 表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);
– 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!

INSERT into stu(id,name,gender,birthday,score) values(1,"张三","男","2021-4-8",99.9);

– 11.查询stu表所有学生的信息

– 语法：SELECT 列名 | * FROM 表名

SELECT * from stu;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LN4p1hrf-1617954028387)(C:\Users\Administrator.USER-20201125AL\AppData\Roaming\Typora\typora-user-images\image-20210408153927313.png)]

– 12.修改stu表中所有学生的成绩，加10分特长分

– 修改语法: UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句];\

update stu set score=score+10;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ww2EcMT9-1617954028405)(C:\Users\Administrator.USER-20201125AL\AppData\Roaming\Typora\typora-user-images\image-20210408154756728.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KRQbt4Yn-1617954028408)(C:\Users\Administrator.USER-20201125AL\AppData\Roaming\Typora\typora-user-images\image-20210408154828368.png)]

– 13.修改stu表中编号为1的学生成绩，将成绩改为83分。

提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。

UPDATE stu set score=83 where id=1;

– 14.删除stu表中所有的记录

– 删除记录语法: DELETE FROM 表名 [where子句]

全部删除

delete from stu;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-guUh6Gu2-1617954028410)(C:\Users\Administrator.USER-20201125AL\AppData\Roaming\Typora\typora-user-images\image-20210408155819337.png)]

– 仅删除符合条件的

条件删除

delete from stu where id=2;

3.查询表记录

3.1.基础查询

– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!

-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;

-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
	id int primary key auto_increment,	-- 员工编号
	name varchar(50),					-- 员工姓名
	gender char(1),						-- 员工性别
	birthday date,						-- 员工生日
	dept varchar(50),					-- 所属部门
	job varchar(50),					-- 所任职位
	sal double,							-- 薪资
	bonus double						-- 奖金
);

-- 往员工表中插入记录
INSERT INTO `emp` VALUES (null, '王海涛', '男', '1995-03-25', '培优部', '讲师', 1800, 400);
INSERT INTO `emp` VALUES (null, '齐雷', '男', '1994-04-06', '培优部', '讲师', 2500, 700);
INSERT INTO `emp` VALUES (null, '刘沛霞', '女', '1996-06-14', '培优部', '讲师', 1400, 400);
INSERT INTO `emp` VALUES (null, '陈子枢', '男', '1991-05-18', '培优部', '总监', 4500, 600);
INSERT INTO `emp` VALUES (null, '刘昱江', '男', '1993-11-18', '培优部', '讲师', 2600, 600);
INSERT INTO `emp` VALUES (null, '王克晶', '女', '1998-07-18', '就业部', '讲师', 3700, 700);
INSERT INTO `emp` VALUES (null, '苍老师', '男', '1995-08-18', '就业部', '总监', 4850, 500);
INSERT INTO `emp` VALUES (null, '范传奇', '男', '1999-09-18', '就业部', '讲师', 3200, 700);
INSERT INTO `emp` VALUES (null, '刘涛', '男', '1990-10-18', '就业部', '讲师', 2700, 500);
INSERT INTO `emp` VALUES (null, '韩少云', '男', '1980-12-18', NULL, 'CEO', 5000, null);
INSERT INTO `emp` VALUES (null, '董长春', '男', '1988-02-05', '培优部', '讲师', 3200, 300);
INSERT INTO `emp` VALUES (null, '张久军', '男', '1989-01-11', '培优部', '讲师', 4200, 500);
-- -----------------------------------
-- 创建db20库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db20库(如果存在)
drop database if exists db20;
-- 重新创建db20库
create database db20 charset utf8;
-- 选择db20库
use db20;

-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
	id int primary key auto_increment,	-- 部门编号
	name varchar(20)					-- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');

-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表, 要求id, name, dept_id
create table emp(
	id int primary key auto_increment,	-- 员工编号
	name varchar(20),					-- 员工姓名
	dept_id int							-- 部门编号
	-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);

-- -----------------------------------
-- 创建db30库、dept表、emp表并插入记录
-- -----------------------------------

-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;

-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
	id int primary key auto_increment,	-- 部门编号
	name varchar(20)					-- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');

-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表（员工编号、员工姓名、所在部门编号）
create table emp(
	id int primary key auto_increment,	-- 员工编号
	name varchar(20),					-- 员工姓名
	dept_id int							-- 部门编号
);
-- 往员工表中插入记录
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 5);



-- -----------------------------------
-- 创建db40库、dept表、emp表并插入记录
-- -----------------------------------

-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;

-- 创建部门表
create table dept(				-- 创建部门表
	id int primary key,			-- 部门编号
	name varchar(50),			-- 部门名称
	loc varchar(50)				-- 部门位置
);

-- 创建员工表
create table emp(				-- 创建员工表
	id int primary key,			-- 员工编号
	name varchar(50),			-- 员工姓名
	job varchar(50),			-- 职位
	topid int,					-- 直属上级
	hdate date,					-- 受雇日期
	sal int,					-- 薪资
	bonus int,					-- 奖金
	dept_id int,				-- 所在部门编号
	foreign key(dept_id) references dept(id)
);

-- 往部门表中插入记录
insert into dept values ('10', '培优部', '北京');
insert into dept values ('20', '就业部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');

-- 往员工表中插入记录
insert into emp values ('1001', '王克晶', '办事员', '1007', '1990-12-17', '800', 500, '20');
insert into emp values ('1003', '齐雷', '分析员', '1011', '1991-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海涛', '推销员', '1011', '1991-02-22', '2450', '600', '10');
insert into emp values ('1007', '刘苍松', '经理', '1017', '1991-04-02', '3675', 700, '20');
insert into emp values ('1009', '张慎政', '推销员', '1011', '1991-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陈子枢', '经理', '1017', '1991-05-01', '3450', 400, '10');
insert into emp values ('1013', '张久军', '办事员', '1011', '1991-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖红', '分析员', '1007', '1997-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩少云', '董事长', null, '1991-11-17', '5000', null, null);
insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1991-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1997-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵栋', '经理', '1017', '1991-12-03', '950', null, '30');
insert into emp values ('1025', '朴乾', '分析员', '1023', '1991-12-03', '3000', 600, '30');
insert into emp values ('1027', '叶尚青', '办事员', '1023', '1992-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------

– 15.查询emp表中的所有员工，显示姓名，薪资，奖金

select NAME,sal,bonus from emp; 

– 16.查询emp表中的所有部门和职位

select dept,job from emp;

去除重复的记录，如何提出重复记录，只保留一条

select DISTINCT dept,job from emp;

3.2.WHERE子句查询

– 17.查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

– 18.查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资

select name,sal+bonus from emp where (sal+bonus)>3500;

– 19.查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资

select name,sal from emp where sal BETWEEN 3000 and 4500;

– 20.查询emp表中【薪资为 1400、1600、1800】的员工，显示员工姓名和薪资

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;

– 21.查询薪资不为1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where sal!=1400 and sal!=1600 and sal!=1800;

– 22.(自己完成) 查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

select name,sal from emp where sal>4000 or sal<2000;

– 23.(自己完成) 查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 AND bonus<600;

– 24.查询没有部门的员工（即部门列为null值）

SELECT * from emp WHERE dept is NULL;

– 思考：如何查询有部门的员工（即部门列不为null值）

select * from emp where dept is not NULL;

3.3.模糊查询

– 25.查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。

select name from emp where name like '刘%';

– 26.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

select name from emp where name like '%涛%';

– 27.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

select name from emp where name like '刘_';

3.4.多行函数查询

– 28.统计emp表中薪资大于3000的员工个数

select count(*) from emp where sal>3000;

– 29.求emp表中的最高薪资

select MAX(sal) from emp;

– 30.统计emp表中所有员工的薪资总和(不包含奖金)

SELECT sum((sal+bonus)) from emp;

– 31.统计emp表员工的平均薪资(不包含奖金)

select avg(sal) from emp;

3.5.分组查询
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
– 32.对emp表，按照部门对员工进行分组，查看分组后效果。

SELECT dept from emp GROUP BY dept;

– 33.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

select job,count(*) from emp GROUP BY job;

– 34.对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

select dept,MAX(sal) from emp GROUP BY dept;

3.6.排序查询
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
– 35.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

select name,sal from emp ORDER BY sal ASC;

– 36.对emp表中所有员工的奖金进行降序(从高到低)排序，显示员工姓名、奖金。

select name,sal from emp ORDER BY sal DESC;

3.7.分页查询
在mysql中，通过limit进行分页查询，查询公式为：
limit (页码-1)*每页显示记录数, 每页显示记录数0
– 37.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据

select * from emp LIMIT 0,3;

– 38.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

select name,sal from emp ORDER BY sal DESC LIMIT 0,3;

3.8.其他函数
– 39.查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。

select name,birthday from emp where birthday BETWEEN '1993-1-1' and '1995-12-31';

– 40.查询emp表中本月过生日的所有员工

select * from emp where WEEKOFYEAR(birthday)=WEEKOFYEAR(CURDATE());

SELECT * from emp where MONTH(birthday)=MONTH(CURDATE());

– 41.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

SELECT name,CONCAT(sal,"/元") from emp;

王振华 1800/元

– 补充练习：查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）

---

4.多表查询
4.1.连接查询

– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!

– 42.查询部门和部门对应的员工信息

---

SELECT dept.name,emp.name from dept,emp where dept.id=emp.dept_id;

4.2.连接查询

---

– 43.查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null

【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

select dept.name,emp.name from dept LEFT JOIN emp ON dept.id=emp.dept_id;

– 44.查询【所有员工】及员工所属部门，如果某个员工没有所属部门，部门显示为null

【右外连接查询】：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。

select dept.name,emp.name from dept RIGHT  JOIN emp ON dept.id=emp.dept_id;

---

4.3.子查询练习

– 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!

– 45.列出薪资比’王海涛’的薪资高的所有员工，显示姓名、薪资

SELECT name,sal from emp where sal > (
	select sal from emp where name='王海涛'
);

– 46.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。

SELECT emp.name,emp.job from emp LEFT JOIN dept ON emp.dept_id=dept.id where emp.job=(
	select job from emp where name='刘沛霞'
);

---

4.4.多表查询练习

– 47.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号，显示部门名称，员工名称。

SELECT dept.name,emp.name from emp LEFT JOIN dept ON emp.dept_id=dept.id where dept.name='培优部'

– 48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

select e1.name,e2.id,e2.name from emp e1,emp e2 where e1.topid=e2.id;

– 49.列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资

SELECT dept.name,emp.sal from emp LEFT JOIN dept ON emp.dept_id=dept.id  
GROUP BY emp.dept_id HAVING emp.sal>1500;

– 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。

SELECT  dept.id,dept.name,count(*),avg(sal) from emp INNER JOIN dept on emp.dept_id=dept.id group by dept_id

– 51.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、部门名称。

select e1.hdate,e1.name,e2.id,e2.name,dept.name,e2.hdate from emp e1,emp e2,dept where e1.topid=e2.id and e1.hdate < e2.hdate and e1.dept_id=dept.id;