MySQL数据库基础
分类： mysql 18 0
本文转自 https://blog.csdn.net/zhen_yr19/article/details/143814961，如有侵权，请联系删除。
目录
一、什么是MySQL?
MySQL 是一个开源的、跨平台的 关系型数据库管理系统 (RDBMS)，最初由瑞典公司MySQL AB 开发，现在由 Oracle 维护和支持。它使用 SQL（结构化查询语言） 来管理和操作数据，是目前世界上最流行的数据库之一。 MySQL 的开源性、高性能、多引擎支持和易用性，使其成为个人开发者、中小企业和大型互联网公司首选的数据库管理系统。
其中，SQL可分为：DQL(数据查询语句)、DDL(数据定义语句)、DML(数据操作语句)、TCL（事务控制语句）、DCL（数据控制语言）**。**
二、SQL语句
1、DDL(数据定义语句)
数据定义语句，全称为Data Definition Language，负责数据库的结构定义与管理，涵盖数据库、表、视图、索引等对象的创建、修改和删除操作。
数据库对象包含：数据库(database)或方案(shema)、表、视图、索引、约束、函数、存储过程、触发器、事务、用户、权限等。
1.1 数据库相关的语句
1.1.1 创建数据库
语法：
create database [if not exists] <数据库名> [options];
options:
[DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
示例：
-
普通数据库 wifidb
create database wifidb; AI写代码sql -
创建数据库 wifidb, 默认指定字符集为 utf8
```sql
create database wifidb default character set utf8;AI写代码sql##### 1.1.2 打开或使用数据库 > **语法：** > > **use <数据库名>;** **示例：** ```sql use wifidb; AI写代码sql</code></pre> <h5>1.1.3 删除数据库</h5> <p>> <strong>语法：</strong></p> <blockquote> <p><strong>drop database [if exists] <数据库名>;</strong></p> </blockquote> <p><strong>示例：</strong></p> <p>```sql drop database wifidb;AI写代码sql</p> <pre><code> ##### **1.1.3 显示数据库** > #### show databases; #### 1.2 表相关的语句 mysql表的引擎: InnoDB, MyISAM, Memery， CSV等。 ##### 1.2.1 查看当前数据库支持的引擎 > **语法：** > > **show engines;** #####  ##### 1.2.2 创建表 **mysql5.7支持的数据**： 数值类型（int, integer, smallint, bigint, decimal, float,double, bit）、字符串类型（char, varchar, text）、日期与时间类型（date, time, timestamp, data）。 > **第一种：** > > **用于从头定义一个新的表结构，手动指定表的字段及其属性：** > > **CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name > (create_definition,...) > [table_options] > [partition_options]** > > **第二种：** > > **从查询结果中创建一个新表并填充数据，新表的结构由查询结果推导，或者部分指定。** > > **CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name > [(create_definition,...)] > [table_options] > [partition_options] > [IGNORE | REPLACE] > [AS] query_expression** > > **第三种：** > > **快速复制一个表的结构（不包含数据），适用于需要基于现有表创建一个空表的场景。** > > **CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name > { LIKE old_tbl_name | (LIKE old_tbl_name) }** ##### 1.2.3 修改表 > drop table 表名; > > **-- 修改字段** > alter table 表名 MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]; > > **-- 修改字段名** > alter table 表名 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST |AFTER col_name]; > > **-- 修改表名** > alter table 表名 RENAME [TO | AS] new_tbl_name; ##### 1.2.4 删除表 > drop table 表名; > > **-- 添加字段** > alter table 表名 ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]; > **-- 删除字段** > alter table 表名 DROP [COLUMN] col_name; ### 2、DML**(数据操作语句)** 数据操作语句，全称为Data Manipulation Language，主要用于对**数据库中的数据**进行**增（INSERT ）、删（DELETE）、改（UPDATE）**操作。 #### 2.1 插入语句 **语法：** > **-- 单条记录插入** > insert into 表名[(字段名 [,...] )] values (值 [,...]); > > **-- 批量记录插入** > inert 表名[(字段名 [,...] )] values > (值 [,...]) [, > .... ]; **示例：**</code></pre> </li> <li> <p>向t_users表中插入一条数据</p> <pre><code class="language-sql">insert into t_users values('disen', password('disen123'), '%');AI写代码sql</code></pre> </li> <li> <p>批量向t_users表中插入lucy, jack, rose三个用户，口令默认为123456</p> <p>```sql insert into t_users(auth,user) values(password('123456'), 'lucy'),(password('123456'), 'jack'),(password('123456'), 'rose');AI写代码sql</p> <pre><code> #### 2.2 修改语句 > **语法：** > > **update 表名 set 字段名=值 [...] [where 条件表达式];** #### 2.3 删除语句 **语法：** > **delete from 表名 [where 条件表达式];** **示例：**</code></pre> </li> <li> <p>删除t_users表中的disen用户数据</p> <pre><code class="language-sql">delete from t_users where user='disen';AI写代码sql</code></pre> </li> <li> <p>清空t_users表的数据</p> <p>```sql delete from t_users;AI写代码sql</p> <pre><code> #### 2.4 条件表达式 ##### 2.4.1 关系表达式 > 字段名表达式 >|>=|<|<=|=| != 值或字段表达式; **示例：**</code></pre> </li> <li> <p>查询taiyang库的student表的95031班级的所有学生</p> <pre><code class="language-sql">select * from student where class='95031';AI写代码sql</code></pre> </li> <li> <p>查询taiyang库的student表的不是95031班级的所有学生</p> <p>```sql select * from student where class='95031';AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>查询taiyang库的Score成绩表的成绩高于90分的所有学生的学号、成绩、课程号</p> <pre><code class="language-sql">select * from score where degree > 90;AI写代码sql</code></pre> <h5>2.4.2 逻辑表达式</h5> <p>> and 与或且</p> <blockquote> <p>or 或 not 取反（非）</p> </blockquote> <p><strong>示例：</strong></p> </li> <li> <p>查询taiyang库的student表的95031班级的所有男学生</p> <p>```sql select * from student where class='95031' and ssex='男';AI写代码sql</p> <pre><code> ##### 2.4.3 mysql条件关键字 > 字段名或表达式 between 开始值或表达式 and 结束值或表达式 ; 表示的[开始，结束]区间的。 > 字段名 in (值或表达式,...) 字段值 与 in中的值或表达式的关系为等值关系，且in中的多个值或表达式的关系为或。 > > 字段名 like 匹配表达式; 匹配表达式中的通配符： _ 一个任意字符, % 0或多个任意字符。 **示例：**</code></pre> </li> <li> <p>查询成绩在80到90区间的所有记录</p> <pre><code class="language-sql">select * from score where degree between 80 and 90;AI写代码sql</code></pre> </li> <li> <p>查询3-245或3-105的课程成绩记录</p> <p>```sql select * from score where cno in ('3-245', '3-105');AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>查询姓名中第二个字是"军"的所有学生</p> <pre><code class="language-sql">select * from student having sname like '_军%';AI写代码sql</code></pre> <h3>3、DQL<strong>(数据查询语句)</strong></h3> <p>数据查询语句，全称为Data Query Language，用于<strong>从数据库中检索数据</strong>，核心是<strong>SELECT</strong>语句。查询可以结合过滤、排序、分组、聚合函数等操作，帮助用户提取所需信息。</p> <h4>3.1 普通查询语句</h4> <p><strong>语法：</strong></p> <p>> select [distinct] *|字段表达式 from 表名 [as 别名] [where 语句] [group by 语句][having 语句] [order by 语句] [limit 语句];</p> <blockquote> <p><strong>字段表达式：</strong> { 字段名或表达式 [ [as] 别名] [, ...] }</p> </blockquote> <p><strong>示例：</strong></p> </li> <li> <p>查看student表，输出的字段名包含"姓名"、"性别"</p> <p>```sql select sname "姓名", ssex "性别" from student;AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>统计student表中所有班级名称， 要求只显示班级编号的后2位</p> <pre><code class="language-sql">select distinct substr(class, char_length(class)-1) as class from student;AI写代码sql</code></pre> <h4>3.2 分组语句与聚合函数</h4> <p><strong>分组语句：</strong></p> <p>分组语句用于将查询结果按照一个或多个列的值进行分组，使得相同值的行被归为一组。这样可以对每个组分别进行聚合计算，从而得到更有意义的统计信息。</p> <p>【要求】在group by 语句的字段，必然在select语句中非使用聚合函数， 另外在select语句未使用聚合函数的字段必然在group by 语句中。</p> <p>> <strong>语法：</strong></p> <blockquote> <p><strong>select 字段表达式 from 表名 group by 字段名或表达式 [, ...] [having 条件表达式];</strong></p> </blockquote> <p><strong>聚合函数：</strong></p> <p>聚合函数是对一组数据进行计算并返回一个单一值的函数，它可以对表中的数据进行汇总、统计等操作。</p> <p>> <strong>常见的聚合函数：</strong></p> <blockquote> <ul> <li>count() 统计行数，不会统计null , 常用count(1), 避免遇到NULL。 <ul> <li>sum() 求和</li> <li>min() 求最小</li> <li>max() 求最大</li> <li>avg() 求平均</li> </ul></li> </ul> </blockquote> <p><strong>示例：</strong></p> </li> <li> <p>查看每门课程的平均成绩、最高成绩、最低成绩, 且按平均成绩从高到低排序。</p> <p>```sql select cno, round(avg(degree),1) as avg, round(max(degree),1) as max, round(min(degree),1) as minfrom scoregroup by cnoorder by avg DESC;AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>查看每位学生的平均成绩，且只看平均成绩高于80分的。</p> <p>【注意】having主要用于聚合字段的条件，可替换where</p> <pre><code class="language-sql">select sno, round(avg(degree), 1) as avg_score from scoregroup by snohaving avg_score > 80;AI写代码sql</code></pre> <h4>3.3 分页语句</h4> <p><strong>分页之前一般先排序！！！</strong></p> <p>> <strong>语法：</strong></p> <blockquote> <p><strong>limit n;</strong> 只显示前n行 <strong>limit offset, rowsize;</strong> 从offset 行号开始显示rowsize条记录</p> <p><strong>【重要！！！】</strong></p> <p><strong>行号从0开始的</strong></p> <p>*<em>offset = (page - 1) </em> rowsize;**</p> </blockquote> <p><strong>示例：</strong></p> </li> <li> <p>显示平均成绩最高的学生信息</p> <p>```sql select sno, round(avg(degree), 1) as avg_score from scoregroup by snoorder by avg_score DESClimit 1;AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>分页设计，每页显示2条，要求显示出第3页的数据</p> <pre><code class="language-sql">select * from studentlimit 4,2;AI写代码sql</code></pre> <h4>3.4 多表联接语句</h4> <p>> <strong>多表联接方式：</strong></p> <blockquote> <ul> <li> <p><strong>等值连接(内联接)</strong></p> <ul> <li> <p>where 等值条件</p> </li> <li> <p>join on</p> </li> <li> <p>nature join （自然连接， 要求两个连接表的具有相同意义的字段名）</p> </li> <li> <p><strong>外连接（左外连接、右外连接、全外连接）</strong></p> </li> <li> <p>left join on</p> </li> <li> <p>right join on</p> </li> <li> <p>nature left join</p> </li> <li> <p>nature right join</p> </li> </ul> </li> </ul> </blockquote> <p>两个表连接时，可能会出现笛卡尔（乘）积，如表1的记录数数n， 表2的记录数为m， 连接之后的结果为m*n条件。</p> <p>> <strong>语法：</strong></p> <blockquote> <p>select 表1名.<em>|字段名或表达式 [, 表2名.</em>|字段名 ...] from 表1 , 表2 where 表1.字段名 = 表2.字段名;</p> <p>select 表1名.<em>|字段名或表达式 [, 表2名.</em>|字段名 ...] from 表1 [natural] [left|right] join 表2 [on 表1.字段名 = 表2.字段名] [natural] [left|right] [join ... ] [where ...] [group by...] [...]</p> </blockquote> <p><strong>示例：</strong></p> </li> <li> <p>查询每位学生的各科成绩信息，显示姓名、课程名和成绩</p> <p>```sql select s.sname, c.cname, sc.degreefrom student snatural join score scnatural join course c;AI写代码sql</p> <pre><code></code></pre> </li> <li> <p>统计每门课的学生成绩的名次</p> <pre><code class="language-sql">select cno, sno, degreefrom scoreorder by cno, degree desc;AI写代码sql</code></pre> <p><strong>@变量：</strong></p> <p><strong>只适合有序数据的排序</strong></p> <p>```sql select cno, sno, degree, @i:=@i+1 as rankfrom score, (select @i:=0) aorder by cno, degree desc;AI写代码sql</p> <pre><code> **一个表两次查询：** ```sql select a.cno, a.sno, a.degree,count(b.sno)+1 as rankfrom score aleft join score b on a.cno = b.cno and a.degree < b.degreegroup by a.cno, a.sno, a.degreeorder by a.cno, a.degree desc;AI写代码sql3.5 子查询语句
select语句可以作为条件表达式的右值，也可以作为子表与其它表进行连接，总体来说，子查询即为其它SQL的语句一部分，主要用于select语句。即select语句中又包含select语句，被包含的select语句称之为子查询语句。
> 语法：
select ... from ....
join (select ... from ... ) 别名select ... from ...
where 字段名 =|>|<|...|in (select 字段名 from ... where 条件);
非in 要求子查询必须是单列单行， in 子查询必须是单列（可以包含多行） -
低于平均成绩的所有学生的学号、课程号和成绩
```sql
select * from scorewhere degree < (select avg(degree) from score);AI写代码sql -
查询最高成绩的学生的信息
select a.*,b.degree from student a natural join score bwhere b.degree = (select max(degree) from score );AI写代码sql -
显示每位学生的信息和最高/最低/平均成绩
```sql
select a.*, b.max, b.min, b.avgfrom student anatural join ( select sno, max(degree) max, min(degree) min, avg(degree) avg from score group by sno) b order by b.avg_ desc;AI写代码sql### 4、TCL**（事务控制语句）** 事务控制语句，全称为 Transaction Control Language，用于**管理事务**，保证数据库操作的一致性和完整性，确保一组相关操作要么全部成功执行，要么全部失败回滚。 > **begin;** 开始事务 > **commit;**提交事条， 开启事务之后的所有操作则提交 > **rollback;**回滚事务，取消开启事务之后的所有操作 > **rollback to** 保存点名称; 回滚到保存还原点之前的事务状态 > **savepoint**保存点名称; 【注】开始事务之后，如果执行了DDL语句，则会自动提交事务 ### 5、DCL（数据控制语言） 数据控制语言，全称为，主要用于控制数据库**用户的访问权限和数据的安全性**，确保数据库的访问和操作符合安全策略。 > **GRANT**：**用于向用户或角色授予对数据库对象的访问权限。** > > 例如，GRANT SELECT, INSERT ON employees TO user1语句会授予户user1对employees表的查询和插入权限，使其能够执行相应的操作。 > > **REVOKE：用于撤销用户或角色对数据库对象的访问权限。** > > 例如，REVOKE INSERT ON employees FROM user1语句会撤销用户user1对employees表的插入权限，限制其对该表的操作范围。
共 0 条评论关于 “MySQL数据库基础”