当前位置：首页 > article >正文

MySQL基础(初阶+进阶)（详解）

article 2024/11/18 14:22:38

前言：

MySQL基础的操作还有很多，上一次主要是针对MySQL的库的增删查的相关操作，接下来就来探究一下MySQL中表的更多的操作。主要围绕着MySQL中表增删查改的操作进行探究！

表的操作：

在进行对表的操作的时候，由于表是放在数据库中的，但是数据库有好多，在新增表之前就需要我们首先选中我们要对哪一个数据库进行操作。

语法：

use + 数据库名；

在选中我们需要进行操作的数据库之后，就可以在选中的数据库中进行一系列的表操作！

新增表：

语法：

create + table + 表名(变量名变量类型，变量名变量类型...)。

在增添表的同时，使用comment也可以给字段增加说明(备注)：

create table stu_test (

id int ,

name varchar ( 20 ) comment ' 姓名 ' ,

password varchar ( 50 ) comment ' 密码 ' ,

age int ,

sex varchar ( 1 ),

birthday timestamp ,

amout decimal ( 13 , 2 ),

resume text

)；

删除表：

如果此时想要删除一张表，包括表中的所有的信息，可以使用如下的语法：

drop + table + （if exists）表名；

查看表：

在此处的查看表针对的是查看表的组成信息，这里不是指查看某张表中的具体内容！！

语法：

desc + 表名；

就会出现如上的一张图。

当然也可以看看当前的数据库中有哪几张表：

语法：

show + tables;

当然接下来我们就来讨论如何在表中进行增删查改具体的信息。

CRUD:

在表中进行的操作，也就是增删查改简称CRUD(即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写)。

插入：

当一整表创建好之后里面是没有任何内容的，是需要我们自己手动放内容进行去的。

如果我我们想要插入一行数据，语法：

insert + into + 表名 + values(对应的变量).

例如：

-- 创建一张学生表

DROP TABLE IF EXISTS student;

CREATE TABLE student (

id INT ,

sn INT comment ' 学号 ' ,

name VARCHAR ( 20 ) comment ' 姓名 ' ,

qq_mail VARCHAR ( 20 ) comment 'QQ邮箱'

);

单行数据插入+全列插入：

insert into student values(1,1,'张三','123456@qq.com');

insert into student values(2,2,'王五',null);

当我们某一列的数据不直到，或者不想插入可以写为null，意思可以理解为空白的意思。

多行插入+指定列插入：

insert into student (id, sn, name) values

( 102 , 20001 , ' 曹孟德 ' ),

( 103 , 20002 , ' 孙仲谋 ' );

此时插入需要注意插入的数据类型和个数都要一一对应！！

查询：

MySQL中对于查询是重重之重，是MySQL的核心部分，查询的方式也是有很多种，针对不同的环境、要求，查询的方式也就不同。

基础语法：

SELECT

[ DISTINCT ] {* | {column [, column ] ...}

[ FROM table_name ]

[ WHERE ...]

[ ORDER BY column [ ASC | DESC ], ...]

LIMIT ...

此处括号里的都是一些查询的条件，这些条件会在后面一一进行说明。

全列查询：

首先我们来看最普通的查询，也就是全局查询：

select + * + from + 表名；

这个*号可以理解为通配符，也就是指所有的列的数据。

指定列查询：

select + 列名,列名,...+ from + 表名;

查询字段为表达式：

如果此时想要查询的信息是与原来信息相关的进行一些其他操作后的内容：

-- 表达式不包含字段

SELECT id, name, 10 FROM exam_result;

-- 表达式包含一个字段

SELECT id, name, english + 10 FROM exam_result;

-- 表达式包含多个字段

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

别名：

如果我们查询出来的信息想给其重新起别名也是可以的：

语法：

SELECT column [AS] alias_name [...] FROM table_name；

例如：
SELECT id, name, chinese + math + english as 总分 FROM exam_result;

去重：

当然我们也可以将我们查到的信息进行一个去重的操作：

语法：

select + distinct + 列名 + from + 表名;

排序：

当我们希望查询出的结果有一定的顺序时，此时可以利用order by进行排序；

语法：

select + (*/distinct/列名/字段) + from + 表名 + order by + 列名;

当结尾加入order by+列名时，此时默认是升序排序，如果想要排降序时+desc；

select + (*/distinct/列名/字段) + from + 表名 + order by + 列名 + desc;

此时有两个特殊的例子：

根据别名排序：

我们可以根据别名进行排序：

SELECT name, chinese + english + math as total FROM exam_result ORDER BY total DESC ;

多个字段进行排序，根据优先级顺序排序：

如果此时我想要根据多个条件进行排序：

SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC , english, chinese;

这里的意思就是：
先根据math排降序，如果有遇到math相同的，再根据english排升序，如果再遇到englis一样的，最后再根据chinese排升序！！

条件查询：

接下俩就是我们经常遇到的场景，如果一个表中有很多数据，但是此时我们只想要我们需要的那一部分，此时就可以经过增加一些条件进行筛选！

语法：

select + (*/distinct/列名/字段) + from + 表名 + where + 列名 + 条件+( order by + 列名 + desc);

以下是可以使用的一些条件：

注：

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

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

模糊查询：

什么时模糊查询：

例如：

此时先创建出如上的表：

如果此时我要查询姓孙的人，该怎么查？

就可以已使用我们的模糊查询：

这里的%也是一个通配符。

如果想要查除姓之外，名字里面带孙的人可以用'孙%'

如果想要精确的查，可以用到'_' ,一个'_'表示一个字符，如果想要查两个字的人，并且姓是孙，可以写为'孙_'.

这就是模糊查询！！

其他的查询较为简单在这里就不进行一一的说明列举了。

分页查询：

如果此时一张表的数据量非常大，查询出来的结果也非常多，此时想要把这些结果进行分页，我想要1页只显示10条信息，此时可以用到分页查询：

语法：

-- 起始下标为 0

-- 从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [ WHERE ...] [ ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [ WHERE ...] [ ORDER BY ...] LIMIT s, n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT ... FROM table_name [ WHERE ...] [ ORDER BY ...] LIMIT n OFFSET s;

此时就可以控制每一页的信息量！！

修改(update):

当我们需要修改表中的内容时，可以用到update关键字：

语法：

update + 表名 + set + 修改条件 + where + 条件；

例如：

我想将孙权的数学成绩改为90；

改后的结果为：

删除：

语法：

delete + from + 表名 + where + 条件;

此时如果想要删除某一行，假设删除孙权的成绩：

关于表的约束：

在我们创建一个新表的时候，可以对所有的参数进行约束，在我们传参的时候就会多一层检查。

NOT NULL约束：

如果我们希望某一列在表中的值不能为空，此时就可以对这一列，即对应的相关的变量进行约束。

假设创建一个学生表，希望学生表中id这一列不能为空，可以这样创建：

create table student (id int not null,name varcahr(20),qq_mail varchar(20));

此时id这一列就不能插入null，如果插入null会报错：

UNIQUE 唯一约束：

有一些值在一些场景中是唯一存在的，例如身份证，所以此时我们也可以针对某一列进行唯一约束：

重新建表，每个学生只有唯一的邮箱：

create table student(id int not null,name varchar(20),qq_mail varchar(20) unique);

此时如果插入重复的邮箱号就会报错：

DEFAULT 默认值约束：

MySQL中的默认值是NULL，也就是当我们某一列不插入元素时，会为我们自动添加null进去。那么如何修改这个默认值，可以使用default进行修改，当我们不添加任何元素的时候，希望name对应的列自动添加'无名氏'.

create table student(id int not null,name varchar(20) default '无名氏', qq_mail varchar(20) unique);

效果如下：

当然该默认值可以在表的信息中看到：

PRIMARY KEY主键约束：

这个约束是干什么的呢？

第一：

他起到了和唯一约束的效果。

第二：

在查找主键对应的一行数据效率比较高

第三：

它可以搭配auto_increment，使用，可以自动'按顺序'生成整型。

例如：

将id设为主键，并搭配auto_increment.

create table student(id int primary key auto_increment,name varchar(20) default '无名氏', qq_mail varchar(20) unique);

首先看看表中的信息：

如果此时我不手动添加id，效果如下：

效果等同于唯一约束+not null约束.

FOREIGN KEY外键约束：

外键用于关联其他表的主键或唯一键，语法：

foreign key(字段名) + references + 主表(列);

如果主表(主键)，关联了外键，那么主表中关联了外键的列与外键将会相互制约，一般是在两张表中体现。

主表中关联了外键的列插入的值必须在外键中存在。

外键想要进行删除操作，必须确保主表中没有外键对应的值。

例如：

创建学生表和班级表：

create table calsses(id int primary key auto_increment,name varchar(20));

create table student(id int primary key auto_increment,name varchar(20),classes_id int,foreign key (classes_id)references calsses(id));

此时，先插入班级信息：

此时插入学生信息：

如果此时插入一个calsses_id 为3的，就会报错：

当然此时也不能直接删除我外键的相关信息，因为在主表中有用到：

表的设计：

表的设计有三大规范：

一对一：

一对多：

多对多：

例如：

此时创建三个表，这三个表之间有关联：

学生表、课程表、成绩表：

1、 create table student(id int primary key auto_increment ,name varchar(20),english decimal(3,1),chinese decimal(3,1),math decimal(3,1));

2、 create table course(id int primary key,name varchar(20));

3、 create table score(id int primary key auto_increment,score decimal(3,1),student_id int,course_id int ,foreign key(student_id) references student(id),foreign key (course_id) references course(id));

新增：

如果此时想要把一张表的数据选定的几行拷贝到另外一张表中应该怎么做？

语法：

insert + from + 目标表名(变量名，...) + select + 拷贝表的变量名,... + from + 拷贝表名;

假设此时有一张大表，要存放全校所有学生的信息：

CREATE TABLE test_user (

id INT primary key auto_increment,

name VARCHAR ( 20 ) comment ' 姓名 ' ,

age INT comment ' 年龄 ' ,

email VARCHAR ( 20 ) comment ' 邮箱 ' ,

sex varchar ( 1 ) comment ' 性别 ' ,

mobile varchar ( 20 ) comment ' 手机号 '

);

此时只需要将学生表中的信息拷贝过去即可：

insert into test_user(name, email) select name, qq_mail from student;

查询：

聚合查询：

当我们需要筛选出需要查询表的一些信息时，此时MySQL提供了一些有用的函数：

语法：

select + 聚合函数(变量名) + from + 表名;

在这里就不再举例了，可以自己试试！！

GROUP BY 子句：

语法：

select + （聚合函数(变量名)、*、...）+ from + 表名 + group by + 列名;

该子句自带去重效果！！

HAVING:

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING!!

显示平均工资低于 1500 的角色和它的平均工资:

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500 ;

联合查询：

此时还是针对多张表，此时多张表都有一定的关联，但是当我查询的时候，就希望能将这几张表的内容进行筛选，最后放在同一张表中！我应该怎么做？

笛卡尔积：

在解决上述的问题的时候引入一个新概念——笛卡尔积。

有n行，a列的表与有m行，b列的表进行笛卡尔积，结果会得到m*n行，a+b列的表！！

例如：
创建一张班级表和学生表并插入相关信息：

create table classes(id int primary key,name varchar(20));

create table student(id int primary key auto_increment, name varchar(20),classes_id int, foreign key (classes_id) references classes(id));

此时要想将两个表进行合并，就需要用到笛卡尔积：

命令：select * from 表一名,表二名;

但是此时发现，有些地方还需要进行约束，学生的班级应该一一对应:

进行where约束之后，并且控制要打印的内容，最后得到的结果才是我们想要的结果。

当然则会一系列的操作，也可以有一些特定的写法：

内连接：

语法：

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

select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件;

加上此时我只想查看张三所在的班级：

写法一：

select stu.name student_name,cla.name class_name from student as stu join classes cla on stu.classes_id = cla.id and stu.name = '张三';

写法二：

select stu.name student_name,cla.name class_name from student stu join classes cla on stu.classes_id = cla.id and stu.name = '张三';

此时就能结合两张表中的信息，精准定位到我们要查找的信息！！

当然三张表中甚至多张表中都可以这样操作。

外连接：

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

语法：

-- 左外连接，表 1 完全显示

select 字段名 from 表名 1 left join 表名 2 on 连接条件 ;

-- 右外连接，表 2 完全显示

select 字段 from 表名 1 right join 表名 2 on 连接条件 ;

如果此时想要让其中的一个表显示完整，可以尝试使用外连接！！

左外连接：

右外连接：

自链接：

该连接方式，可以有一个很大的用处，就是将一行的值改为一列的值。

有以下的场景：

之前比较两个值的大小只能列于列之间进行比较，但是如果要比较两个学生的英语成绩谁的高，可以这样比较：

首先创建一张表：

此时如果想要比较两两的语文成绩或者英语成绩怎么办？

方式一：

select s1.name,s1.chinese,s2.name,s2.chinese from score s1,score s2 where s1.chinese > s2.chinese;

方式二：

select s1.name,s1.chinese,s2.name,s2.chinese from score s1 join score s2 on s1.chinese>s2.chinese;