MySQL基础命令总结

一、常用基础命令

1、MySQL功能型语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13

show databases 查询所有数据库；   use database 切换数据库；   create database [if not exits] 库名 创建数据库；   drop database [if exits] 库名 删除数据库；   show 建库语句 查询数据库创建；   character set 设置数据库的字符集；   alter database 数据库名 character set 编码集 修改数据库的编码集；

2、建表语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

drop table if exists test_a; CREATE TABLE `test_a` (  `id` varchar(10) NOT NULL,  `username` varchar(10) NOT NULL,  `password` varchar(10) NOT NULL,  PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   drop table if exists test_a_description; CREATE TABLE `test_a_description` (  `id` varchar(10) NOT NULL,  `age` varchar(10) ,  `address` varchar(50) ,  `parent_id` varchar(10) NOT NULL,  PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、字段属性

?

1 2 3 4 5 6 7 8 9 10 11 12 13

not null：没有给值数据的时候为默认值，而varchar的默认值不设置的话为空；   auto_increment：定义列为自增的属性，一般是用于主键自增，数值会在上一行基础上加1；   primary key：关键字用于定义列为主键，也可以多列定义组合主键，列间以逗号分隔；   engine：设置存储引擎，charset设置编码；   default null：设置默认值null；   default 值：设置默认值。   我们在建表的时候可以根据需要选择字段设置。

USE cw_test01;
-- 创建表

CREATE TABLE category(
cid INT auto_increment,
cname VARCHAR(20), PRIMARY key(cid)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

?

1 2 3 4 5

-- 创建测试表 CREATE TABLE test1( tid INT, tdate DATE );

4、快速复制相同的表结构

create table 新表名 like 旧表名；

?

1 2 3 4

-- 创建一个表结构与 test1 相同的 test2表 create table test2 like test1; -- 查看表结构 DESC test2;

5、查看表

?

1 2 3 4 5 6

-- 查看当前数据库中的所有表名 SHOW TABLES; -- 显示当前数据表的结构 DESC category; -- 查看创建表的SQL语句 SHOW CREATE TABLE category;

6、删除表

?

1 2 3 4

-- 直接删除 test1 表 drop table test1; -- 先判断 再删除test2表 drop table if EXISTS test2;

7、 修改表

1）需求： 将category表 改为 category1

?

1	rename table category to category1;

2）向表中添加列， 关键字add

?

1 2	需求： 为分类表添加一个新的字段为 分类描述 cdesc varchar(20) alter table category1 add cdesc varchar(20) DEFAULT "分类描述";

3）修改表中列的 数据类型或长度 ， 关键字 MODIFY

?

1 2	需求：对分类表的描述字段进行修改，类型varchar(50) alter table category1 MODIFY cdesc varchar(50);

4）修改列名称 , 关键字 CHANGE

?

1 2	需求: 对分类表中的 desc字段进行更换, 更换为 description varchar(30) alter table category1 change cdesc description VARCHAR(30);

5）删除列 ，关键字 DROP

?

1 2	需求： 删除分类表中description这列 alter table category1 drop description;

8、DML 操作表数据进行增删改查

?

1 2 3 4 5 6 7 8

# 创建学生表 CREATE TABLE student( sid INT, sname VARCHAR(20), age INT, sex CHAR(1), address VARCHAR(40) );

1）插入表数据：

?

1 2 3 4

INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟 空',20,'男','花果山'); INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球'); INSERT INTO category1 (cname) VALUES('白骨精');

2）update 更新数据

?

1 2	需求：带条件的修改，将sid 为1的学生，性别改为女 UPDATE student SET sex = '女' WHERE sid = 1;

3）delete和truncate 删除表数据

?

1 2 3 4 5 6

需求：删除表student中sid=2的记录 delete from student where sid=2;   需求：删除所有表所有数据 delete from student； truncate table student；      #truncate效率比较高

9、DQL 查询表中数据（重点）

1）准备数据

#创建员工表
表名 emp
表中字段：
eid 员工id，int
ename 姓名，varchar
sex 性别，char
salary 薪资，double
hire_date 入职时间，date
dept_name 部门名称，varchar

?

1 2 3 4 5 6 7 8 9

#创建员工表　　drop table if EXISTS emp; 　　CREATE TABLE emp( 　　eid INT, 　　ename VARCHAR(20), 　　sex CHAR(1), 　　salary DOUBLE, 　　hire_date DATE, 　　dept_name VARCHAR(20) 　　);

#添加数据
INSERT INTO emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部');
INSERT INTO emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
INSERT INTO emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部');
INSERT INTO emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部');
INSERT INTO emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部');
INSERT INTO emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部');
INSERT INTO emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
INSERT INTO emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部');
INSERT INTO emp VALUES(9,'吴承恩','男',20000,'2000-03-14',NULL);
INSERT INTO emp VALUES(10,'孙悟饭','男', 10,'2020-03-14',财务部);
INSERT INTO emp VALUES(11,'兔八哥','女', 300,'2010-03-14',财务部);

2） 简单查询

执行顺序

FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

需求1： 查询emp中的 所有数据

SELECT * FROM emp; -- 使用 * 表示所有列

需求2： 查询emp表中的所有记录，仅显示id和name字段

SELECT eid,ename FROM emp;

需求3： 将所有的员工信息查询出来，并将列名改为中文

别名查询，使用关键字 as

# 使用 AS关键字,为列起别名
SELECT
eid AS '编号',
ename AS '姓名' ,
sex AS '性别',
salary AS '薪资',
hire_date '入职时间', -- AS 可以省略
dept_name '部门名称'
FROM emp;

 

需求4：查询一共有几个部门

使用去重关键字 distinct

-- 使用distinct 关键字,去掉重复部门信息
select DISTINCT dept_name as '部门名称' from emp;

需求5: 将所有员工的工资 +1000 元进行显示

运算查询 (查询结果参与运算)

SELECT ename , salary + 1000 FROM emp;

3）条件查询

如果查询语句中没有设置条件,就会查询所有的行信息,在实际应用中,一定要指定查询条件,对记

录进行过滤

?

1	select * from emp where ename="孙悟空";<br>

 需求1:

?

1 2 3 4 5 6 7 8

# 查询员工姓名为黄蓉的员工信息 select * from emp where ename="黄蓉"; # 查询薪水价格为5000的员工信息 select * from emp where salary=5000; # 查询薪水价格在5000到10000之间所有员工信息 select * from emp where salary between 5000 and 10000; # 查询薪水价格是3600或7200或者20000的所有员工信息 select * from emp where salary in (3600,7200,20000);

 需求2：

模糊查询、通配符

?

1 2 3 4 5 6 7 8 9 10

# 查询员工姓名含有黄字的员工信息 select * from emp where ename like "%黄%"; # 查询以'孙'开头的所有员工信息 select * from emp where ename like '孙%'; # 查询第三个字为'精'的所有员工信息 select * from emp where ename like '__精%'; # 查询没有部门的员工信息 select * from emp where dept_name  is null; # 查询有部门的员工信息 select * from emp where dept_name  is not null;

10、mysql核心查询（重点）

 单表查询

1）排序

通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)

①单列排序

只按照某一个字段进行排序, 就是单列排序

需求1:

使用 salary 字段,对emp 表数据进行排序 (升序/降序)

?

1 2 3 4

-- 默认升序排序 ASC select * from emp order by salary; -- 降序排序 select * from emp order by salary desc;

②组合排序

在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序　　

?

1 2	#在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做升序排序 SELECT * FROM emp ORDER BY salary DESC, eid ASC;

2）聚合函数

函数：方法，它封装了一些逻辑，比如给他一堆数据，特定函数可以返回最大值max()，avg()平均值

聚合，也称为聚合统计或者聚合查询，就需要使用select关键字，有select 就得有from xxx

 

 需求1:

?

1 2 3 4 5 6 7 8 9 10 11 12 13

#1 查询员工的总数 select count(DISTINCT eid) from emp; #2查看员工总薪水、最高薪水、最小薪水、薪水的平均值 select count(salary),MAX(salary),MIN(salary),AVG(salary) from emp;   #3 查询薪水大于4000员工的个数 select count(*) from emp where salary>4000;   #4 查询部门为'教学部'的所有员工的个数 select count(DISTINCT ename) from emp where dept_name="教学部"   #5 查询部门为'市场部'所有员工的平均薪水 select  avg(salary) from emp where dept_name="市场部";

3）分组

分组往往和聚合函数一起时候，对数据进行分组，分完组之后在各个组内进行聚合统计分析

比如：求各个部门的员工数~

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

?

1 2	#各个部门的员工数 select dept_name,count(ename) from emp GROUP BY dept_name;

　

需求1: 通过性别字段 进行分组,求各组的平均薪资

?

1	SELECT sex, AVG(salary) FROM emp GROUP BY sex;

需求2:

?

1 2 3 4 5 6

#1.查询每个部门的平均薪资 select dept_name,avg(salary) from emp GROUP BY dept_name ORDER BY avg(salary) desc; #2.查询每个部门的平均薪资, 部门名称不能为null,降序排列 select dept_name,avg(salary) from emp  where dept_name is not null GROUP BY dept_name ORDER BY avg(salary) desc; #3. 查询平均薪资大于6000的部门.且部门不为空 select dept_name,avg(salary) from emp GROUP BY dept_name having avg(salary)>6000 and dept_name is not  NULL ;

where 与 having的区别

分析:

1) 需要在分组后,对数据进行过滤,使用 关键字 having

2) 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条

件。

4）limit关键字

limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据

?

1 2 3 4

# 查询emp表中的前 5条数据 select * from emp limit 5; # 查询emp表中 从第4条开始,查询6条 select * from emp limit 3,6;