MySQL常见操作

这里就不讲安装了后面闲了有机会的话会出各种软件的安装教程，MySQL安装简易哔哩哔哩跟着视频来，建议8.0版本后

数据库基础

MAC OS终端操作：

1、MySQL安装完成后打开设置
2、点击MySQL

3、 保证服务开启

4、打开终端输入如下指令

/usr/local/MySQL/bin/mysql -u root -p

root：指的是你自己设置的数据库

用户用户用户

名，不是库名。
5、输入密码（一般不显示密码输进去回车就好）

当看到绿框框的欢迎内容就证明好了。
当然用自带的软件Workbench、navicat或者其他的数据库软件操作更简单，尤其navicat甚至可以很好的同步本地与服务器数据，但是鉴于我们处于总结目的，回顾或学习MySQL基本语法建议一定要会命令行操作，和Linux是一样的道理后续会介绍，当然后面也会介绍这些软件的部分使用

查看当前已有数据库

 show databases;

🍊个🌰：

showdatabases;

注意加‘；’

使用数据库

当然这个操作是鉴于我们已经有数据库的基础上

use ---

🍊个🌰：

use blog;

新建数据库

create database ; ( 新建名为“”的数据库;)
create database if not exists character set utf8mb4; （如果 “”库不存在就新建并且字符集设为

utf8mb4

;
🍊个🌰：
创建test数据库：

createdatabase test;createdatabaseifnotexists test characterset utf8mb4;

删除库

DROP DATABASE [IF EXISTS] db_name;（[if EXISTS]可不写）

所以看出来了吧，MySQL不分大小写

🍊个🌰：

DROPDATABASE test;

表操作

经历过保姆级教学的库操作，相信大家已经基本回顾的差不多了，众所周知表操作与库操作基本一致我们快速上手一下

1. 使用某表：

use db_name;

1. 新建表：

create table db_name;

创建一个数据库的表时需要在表里添加至少一个的属性

3、 查看表：

查看库中所有表：show tables;
查看某一张表： desc table_name;

4、删除表：

删除某表 ：drop table table_name;
如果存在某表，则删除： drop table if exists table_name;

Mysql数据类型

字符串类型

我们在这里不再阐述基本数据类型

日期类型

数据类型大小说明对应java类型DATETIME8字节范围从1000到9999年，不会进行时区的检索及转换。java.util.Date、java.sql.TimestampTIMESTAMP4字节范围从1970到2038年，自动检索当前时区并进行转换。java.util.Date、java.sql.Timestamp

增删查改

我们将以举🌰的形式呈现
首先随便在一个库里面构建一个学生表：

DROPTABLEIFEXISTS student;CREATETABLE student ( 
id INT,
sn INTcomment'学号',
name VARCHAR(20)comment' 姓 名 ',
qq_mail VARCHAR(20)comment'QQ邮箱');

1. 增（insert）

增加两个学生：
id学号姓名QQ1001芋头2002白糖

INSERTINTO student (id, sn, name)VALUES(1,001,'芋头'),(2,002,'白糖');

添加一个：
id学号姓名QQ3003豆腐3592526819

INSERTINTO student VALUES(3,003,'豆腐','3592526819');

1. 查（Retrieve）

我们依然🍊个🌰：
先建一个成绩表并插入数据用来查询：

DROPTABLEIFEXISTS exam_result;CREATETABLE exam_result (
 id INT,
 name VARCHAR(20),
 chinese DECIMAL(3,1),
 math DECIMAL(3,1),
 english DECIMAL(3,1));INSERTINTO exam_result (id,name, chinese, math, english)VALUES(1,'唐三藏',67,98,56),(2,'孙悟空',87.5,78,77),(3,'猪悟能',88,98.5,90),(4,'曹孟德',82,84,67),(5,'刘玄德',55.5,85,45),(6,'孙权',70,73,78.5),(7,'白骨精',98.0,98.0,98.0);

• 全列查询

SELECT*FROM exam_result;

• 指定列查询

SELECT id, name, english FROM exam_result;

• 查询字段为表达式

-- 表达式不包含字段SELECT id, name,10FROM exam_result;-- 表达式包含一个字段SELECT id, name, english +10FROM exam_result;-- 表达式包含多个字段SELECT id, name, chinese + math + english FROM exam_result;

• 别名

SELECT id, name, chinese + math + english 总分 FROM exam_result;

• 去重：DISTINCT

SELECTDISTINCT math FROM exam_result;

• 排序：ORDER BYASC 为升序（从小到大）; DESC 为降序（从大到小）; 默认为 ASC

-- 查询同学及总分，由高到低SELECT name, chinese + english + math FROM exam_result 
ORDERBY chinese + english + math DESC;-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示SELECT name, math, english, chinese FROM exam_result 
ORDERBY math DESC, english, chinese;

• 条件查询：WHERE

比较运算符：

辑运算符：

1.WHERE条件可以使用表达式，但不能使用别名。

2.AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

-- 查询英语不及格的同学及英语成绩 ( < 60 )SELECT name, english FROM exam_result WHERE english <60;-- 查询总分在 200 分以下的同学SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english <200;-- 查询语文成绩大于80分，且英语成绩大于80分的同学SELECT*FROM exam_result WHERE chinese >80and english >80;-- 查询语文成绩在 [80, 90] 分的同学及语文成绩SELECT name, chinese FROM exam_result WHERE chinese BETWEEN80AND90;-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩SELECT name, math FROM exam_result WHERE math IN(58,59,98,99);-- 使用 OR 也可以实现SELECT name, math FROM exam_result WHERE math =58OR math =59OR math
-- % 匹配任意多个（包括 0 个）字符SELECT name FROM exam_result WHERE name LIKE'孙%';-- 匹配到孙悟空、孙权-- _ 匹配严格的一个任意字符SELECT name FROM exam_result WHERE name LIKE'孙_';-- 匹配到孙权

• 分页查询：LIMIT

按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

-- 第 1 页SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3OFFSET0;-- 第 2 页SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3OFFSET3;-- 第 3 页，如果结果不足 3 个，不会有影响SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3OFFSET6;

3.修改（Update）

大家看一下应该就会语法了

-- 将孙悟空同学的数学成绩变更为 80 分UPDATE exam_result SET math =80WHERE name ='孙悟空';-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分UPDATE exam_result SET math =60, chinese =70WHERE name ='曹孟德';-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分UPDATE exam_result SET math = math +30ORDERBY chinese + math + english LIMIT3;-- 将所有同学的语文成绩更新为原来的 2 倍UPDATE exam_result SET chinese = chinese *2;

4.删除（Delete）

DELETEFROM exam_result WHERE name ="孙悟空";

截止目前数据库MySQL基础我们已经掌握的7788了，增删改查也基本了解下面来看一些进阶操作。

数据库约束

约束类型

• NOT NULL : 指示某列不能存储 NULL 值。
• UNIQUE : 保证某列的每行必须有唯一的值。
• DEFAULT : 规定没有给列赋值时的默认值。
• PRIMARY KEY : NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY : 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK : 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

🍊个🌰：

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识DROPTABLEIFEXISTS classes;CREATETABLE classes (
id INTPRIMARYKEYauto_increment, 
name VARCHAR(20),`desc`VARCHAR(100));DROPTABLEIFEXISTS student;CREATETABLE student (
id INTPRIMARYKEYauto_increment,
sn INTUNIQUE,
name VARCHAR(20)DEFAULT'unkown',
qq_mail VARCHAR(20),
classes_id int,FOREIGNKEY(classes_id)REFERENCES classes(id));

外键用于关联其他表的主键或唯一键

表的关系

• 一对多：最常用的关系，如部门和员工
• 多对多：学生选课表和学生表，一门课程可以有多个学生选择，一个学生选择多门课程
• 一对一：相对使用比较少，员工表，公民表，护照表

高级查询

聚合查询

聚合函数:
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
🍊个🌰：

-- 统计数学成绩总分SELECTSUM(math)FROM exam_result;-- 统计平均总分SELECTAVG(chinese + math + english) 平均总分 FROM exam_result;-- 返回 > 70 分以上的数学最低分SELECTMIN(math)FROM exam_result WHERE math >70;

GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

🍊个🌰：

准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

createtable emp(
id intprimarykeyauto_increment,
name varchar(20)notnull,
role varchar(20)notnull, 
salary numeric(11,2));insertinto emp(name, role, salary)values('马云','服务员',1000.20),('马化腾','游戏陪玩',2000.99),('孙悟空','游戏角色',999.11),('猪无能','游戏角色',333.5),('沙和尚','游戏角色',700.33),('隔壁老王','董事长',12000.66);

 NUMERIC数据类型是一种精确数字数据类型，其精度在算术运算后保留到最小有效位，numeric是标准sql的数据类型，格式是numeric(m，n）。

numeric(a，b)函数有两个参数，前面一个为总的位数，后面一个参数是小数点后的位数，例如numeric（5，2）是总位数为5，小数点后为2位的数，也就是说这个字段的整数位最大是3位。

🌰：查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary)from emp groupby role;

HAVING字句

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING
🌰：显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary)from emp groupby role havingavg(salary)<1500;

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

二话不多说来看一张图：

关联查询可以对关联表使用别名

🍊个🌰：

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识DROPTABLEIFEXISTS classes;CREATETABLE classes (
id INTPRIMARYKEYauto_increment, name VARCHAR(20),`desc`VARCHAR(100));-- 重新设置学生表结构DROPTABLEIFEXISTS student;CREATETABLE student (
id INTPRIMARYKEYauto_increment, sn INTUNIQUE,
name VARCHAR(20)DEFAULT'unkown', qq_mail VARCHAR(20),
classes_id int,FOREIGNKEY(classes_id)REFERENCES classes(id));-- 创建课程表DROPTABLEIFEXISTS course;CREATETABLE course (
    id INTPRIMARYKEYauto_increment, 
    name VARCHAR(20));-- 创建课程学生中间表：考试成绩表DROPTABLEIFEXISTS score;CREATETABLE score (
     id INTPRIMARYKEYauto_increment,
     score DECIMAL(3,1),
     student_id int, 
     course_id int,FOREIGNKEY(student_id)REFERENCES student(id),FOREIGNKEY(course_id)REFERENCES course(id));insertinto classes(name,`desc`)values('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),('自动化2019级5班','学习了机械自动化');insertinto student(sn, name, qq_mail, classes_id)values('09982','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','tellme',null,2),('09527','老外学中文','[email protected]',2);insertinto course(name)values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');insertinto score(score, student_id, course_id)values-- 黑旋风李逵(70.5,1,1),(98.5,1,3),(33,1,5),(98,1,6),-- 菩提老祖(60,2,1),(59.5,2,5),-- 白素贞(33,3,1),(68,3,3),(99,3,5),-- 许仙(67,4,1),(23,4,3),(56,4,5),(72,4,6),-- 不想毕业(81,5,1),(37,5,5),-- 好好说话(56,6,2),(43,6,4),(79,6,6),-- tellme(80,7,2),(92,7,6);

先把上面这些个表构建好然后一一🍊🌰：

内连接

语法：

select 字段 from 表1 别名1[inner]join 表2 别名2on 连接条件 and 其他条件;select 字段 from 表1 别名1,表2 别名2where 连接条件 and 其他条件;

-- 学生表、成绩表、课程表3张表关联查询SELECT
stu.id, stu.sn, stu.NAME, stu.qq_mail, sco.score, sco.course_id, cou.NAME
FROM student stu
JOIN score sco ON stu.id = sco.student_id 
JOIN course cou ON sco.course_id = cou.id
ORDERBY stu.id;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完 全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示select 字段名 from 表名1leftjoin 表名2on 连接条件;-- 右外连接，表2完全显示select 字段 from 表名1rightjoin 表名2on 连接条件;

🌰：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

-- “老外学中文”同学 没有考试成绩，也显示出来了select*from student stu leftjoin score sco on stu.id=sco.student_id;-- 对应的右外连接为：select*from score sco rightjoin student stu on stu.id=sco.student_id;-- 学生表、成绩表、课程表3张表关联查询SELECT
stu.id, stu.sn, stu.NAME, stu.qq_mail, sco.score, sco.course_id, cou.NAME FROM student stu 
LEFTJOIN score sco ON stu.id = sco.student_id 
LEFTJOIN course cou ON sco.course_id = cou.id
ORDERBY stu.id;

自连接

自连接是指在同一张表连接自身进行查询。

🌰：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

-- 先查询“计算机原理”和“Java”课程的idselect id,name from course where name='Java'or name='计算机原理';-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息SELECT s1.*FROM score s1 , score s2 WHERE 
s1.student_id = s2.student_id
AND s1.score < s2.score AND s1.course_id =1AND s2.course_id =3;-- 也可以使用join on 语句来进行自连接查询SELECT s1.*FROM score s1
JOIN score s2 ON s1.student_id = s2.student_id 
AND s1.course_id =1AND s2.course_id =3;

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

union:该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

🍊个🌰：查询id小于3，或者名字为“英文”的课程：

select*from course where id<3unionselect*from course where name='英文';-- 或者使用or来实现select*from course where id<3or name='英文';

union all: 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。 

🍊个🌰：查询id小于3，或者名字为“Java”的课程

-- 结果集中出现重复数据select*from course where id<3unionallselect*from course where name='英文';

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 单行子查询：返回一行记录的子查询 🍊个🌰：查询与“不想毕业” 同学的同班同学

select*from student where classes_id=(select classes_id from student where name='不想毕业');

• 多行子查询：返回多行记录的子查询

🍊个🌰：查询“语文”或“英文”课程的成绩信息

1. [NOT] IN关键字：

-- 使用INselect*from score where course_id in(select id from course where name='语文'or name='英文');-- 使用 NOT INselect*from score where course_id notin(select id from course where name!='语文'and name!='英文');

1. [NOT] EXISTS关键字：

-- 使用 EXISTSselect*from score sco whereexists(select sco.id from course cou where(name='语文'or name='英文')and cou.id = sco.course_id);-- 使用 NOT EXISTSselect*from score sco wherenotexists(select sco.id from course cou where(name!='语文'and name!='英文')and cou.id = sco.course_id);

截止目前MySQL数据库我们基本操作及语法我们已经看完了，后面将持续更新其他内容敬请期待