MySQL基础命令总结

一、常用基础命令

1、MySQL功能型语句

show databases 查询所有数据库；

use database 切换数据库；

create database [if not exits] 库名 创建数据库；

drop database [if exits] 库名 删除数据库；

show 建库语句 查询数据库创建；

character set 设置数据库的字符集；

alter database 数据库名 character set 编码集 修改数据库的编码集；

2、建表语句

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、字段属性

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;

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

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

create table 新表名 like 旧表名；

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

5、查看表

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

6、删除表

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

7、 修改表

1）需求： 将category表 改为 category1

rename table category to category1;

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

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

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

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

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

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

5）删除列 ，关键字 DROP

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

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

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

1）插入表数据：

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

2）update 更新数据

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

3）delete和truncate 删除表数据

需求：删除表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

#创建员工表

　　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）条件查询

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

录进行过滤

select * from emp where ename="孙悟空";

 需求1:

# 查询员工姓名为黄蓉的员工信息
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：

模糊查询、通配符

# 查询员工姓名含有黄字的员工信息
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 表数据进行排序 (升序/降序)

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

②组合排序

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

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

2）聚合函数

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

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

 

 需求1:

#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 语句,对查询的信息进行分组,相同数据作为一组

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

　

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

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

需求2:

#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指定查询多少行数据

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