2022.3 7MySQL 1-4

本文 属于 学习笔记，对原文代码进行过 测试 并修改，确保可行。

主要参考资料：MySQL详细学习教程_Baret-H的博客

---

MySQL 是最流行，在 WEB应用 方面 最好的 R-DBMS(Relational Database Management System关系数据库管理系统)应用软件之一。

---

1.1数据库

数据库DB（Database）是按照 某种数据结构 来 组织、存储和管理 数据的仓库。每种数据库都有一个或多个不同的 API（即函数 或叫 方法） 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在其他类型的文件中（而不是数据库文件），但是在其他类型文件中读写数据速度相对较慢。

数据库也分为 关系型数据库 和 非关系型数据库。

关系型数据库SQL（Structured Query Language），即 建立在关系模型基础上的数据库：

• MySQL、Oracle、Sql Server、DB2、SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储
• 通过 外键 来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

• Redis、MongoDB
• 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

SQL与NoSQL区别-读写性能_牛牛码特的博客-CSDN博客

关系型数据库sql 十分强调数据的一致性，却因此降低了读写性能。虽然Sql存储数据和处理数据的可靠性很不错，但一旦面对海量数据的处理的时候效率就会变得很差，特别是遇到高并发读写的时候性能就会下降的非常厉害。

而NoSQL数据库相对关系型数据库优势最大的恰恰是应对大数据方面，也就是对于大量的每天都产生非结构化的数据能够高性能的读写，这是因为NoSQL数据库是按key-value类型进行存储的，以数据集的方式存储的，因此无论是扩展还是读写都非常容易，并且NoSQL数据库不需要关系型数据库繁琐的解析，所以NoSQL数据库大数据管理、检索、读写、分析以及可视化方面具有关系型数据库不可比拟的优势。

---

1.2 DBMS

现在我们使用 关系型 数据库管理系统（RDBMS）来存储和管理大数据量。

MySQL是数据库管理系统！

RDBMS 的特点：

• 1.数据以表格的形式出现
• 2.每行hang 为 各种记录名称 (实体个例)
• 3.每列lie 为 记录名称所对应的数据域 (实体的属性)
• 4.许多的行和列组成一张表单
• 5.若干的表单组成database

RDBMS的一些术语：

• 数据库: 一些关联表的集合。        关联表 即  关联起来的数据表。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 冗余：存储 不必要的数据（如 列3 可以由 列2-列1得到，那列3的存在就是不必要的）。优点：可以快速查询；提高了数据的安全性。 缺点：维护成本高，易出现数据不同步；浪费空间。
• 主键：主键 在每个表里 是唯一的。可使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：（或称 组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：索引就是 提前对表中某一列或多列的值进行排序的一种结构，可快速访问数据库表中的特定信息。对那些 经常被查询的列 而言，索引很有必要。（在构建的时候 需要额外声明 才能对 某列 生成对应的 索引）
• 参照完整性: 要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

---

1.3 MySQL简介

MySQL 由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种R-DBMS。

• MySQL 是开源的。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 使用标准的 SQL 数据语言。
• MySQL 可运行于多个系统上，支持多种语言。包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。 在php的web开发中应用最广。
• MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
• MySQL 采用了 GPL 协议，可以修改源码来开发自己的 MySQL 系统（即 可以定制）。
• 一点很棒的特性是，可以对它进行缩减，来支持嵌入的数据库应用程序。这使得mysql可以处理 小、中、大 型的系统。

 PHP MySQL 函数格式：

mysqli_function(value,value,...);

<?php
$retval = mysqli_function(value, [value,...]);
if( !$retval )
{
   die ( "相关错误信息" );
}
// 其他 MySQL 或 PHP 语句
?>

mysqli_connect($connect);    #成功链接到 MySQL 后返回连接标识，失败返回 FALSE
#mysqli_connect(host, username, password, dbname,port, socket);
mysqli_query($connect,"SQL 语句");
mysqli_fetch_array()
mysqli_close($link)        #断开与 MySQL 数据库的链接。 $link即 链接标识
#如果没有指定 link_identifier，则关闭上一个打开的连接。
#通常不需要使用 mysqli_close()，因为已打开的非持久连接会在脚本执行完毕后自动关闭。

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo '数据库连接成功！';
mysqli_close($conn);
?>

---

1.4 安装

MySQL :: Download MySQL Community Server

有两个安装包(mysql-installer-web-community-8.0.28.0.msi) 和(mysql-installer-community-8.0.28.0.msi)，二选一，带web的是联网安装包，没web是离线安装包。

菜鸟的安装教程太老了（是用于zip安装包的），以这个为主（用于msi安装包）。

安装按流程走即可，配置环境变量也简单。在启动服务的时候，如果选择用cmd启动，记得要 用管理员身份运行cmd

启动mysql服务

>net start MySQL80

第五步，修改MySQL数据存储路径 的时候，若直接修改ini文件 会提示没有权限，解决方法见：把ini复制到外面，改完再复制进来替换掉。（不放心就先复制一个原件副本）

my.ini里还可以修改

# 设置mysql客户端默认字符集    #适合5.1及之前版本?
default-character-set=utf8

# latin1字符集 太旧了，改用utf8 或者 utf8mb4    #适合5.5及之后版本?
character-set-server=utf8

1.5 登录 MySQL

C:\WINDOWS\system32>net start MySQL80
MySQL80 服务正在启动 ..
MySQL80 服务已经启动成功。

C:\WINDOWS\system32>mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is *
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

登录格式：mysql -h主机 -u用户名 -p密码        --主机 用户名 密码 紧接-h-u-p，中间最好不需要空格

例：>mysql -hlocalhost -uroot -p123456        --这种是直接在-p后面加上 明文密码，可行，但sql会警告这样做不安全

--下面是不在-p后面接 明文密码
--登录本机mysql 可以不加-hlocalhost，若要登录其他主机，需加上 -h主机名
>mysql -uroot -p                
Enter password: *************

输入 exit 或 quit 退出登录

mysql> exit
Bye

 关闭mysql服务

>net stop MySQL80

 1.6 其他设置

--查看mysql的基本设置情况
mysql> status;    --或者
mysql> \s

--------------
mysql  Ver 8.0.28 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          22
...
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               ....
Binary data as:         Hexadecimal
Uptime:                 52 min 22 sec

Threads: 2  Questions: 17  Slow queries: 0  Opens: 141  Flush tables: 3  Open tables: 60  Queries per second avg: 0.005
--------------

--查看character相关属性
> show variables like '%char%';

+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\... |
+--------------------------+---------------------------------------------------------+

--上述character set变量的解释见：https://blog.csdn.net/JineD/article/details/115767489

utf8 与 utf8mb4:mysql使用utf8mb4经验吐血总结_胡庚申的博客 

不过这篇文章感觉有点过时了，现在mysql默认的utf8都是带mb的

---

以下属于进阶内容，建议先跳过，​见mysql字符集和排序字符集的区别_戴国进的博客

版本与字符集：

Mysql5.5版本在my.ini上上修改character-set-server为utf8

​Mysql8.0以上安装完默认character_set_server 为utf8mb4

为什么要用utf8mb4：

随着互联网的发展，产生了许多新类型的字符，例如emoji，也就是我们通常在聊天时发的小黄脸表情，这种字符的出现不在基本多文种平面的Unicode字符之中，导致emoji无法在MySQL中使用utf8存储。MySQL于是对utf8字符进行了扩展，增加了utf8mb4这个编码。

按照数据库大小的储存最优思想：

使用utf8能够节省一定空间， 但是utf8mb4拥有目前来说比较好的兼容性，因情况取舍

MySQL 中字符集相关变量：

​ character_set_client：客户端请求数据的字符集

​ character_set_connection：从客户端接收到数据，然后传输的字符集

​ character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。

character_set_filesystem：把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的

​character_set_results：结果集的字符集

​character_set_server：数据库服务器的默认字符集

character_set_system：存储系统元数据的字符集，总是 utf8，不需要设置

创建指定字符集的数据库：

 方法不唯一，用下面的set names 或者 其他方案 都是可以的。

---

排序字符集 collation

Mysql8.0以上默认使用utf8mb4_0900_ai_ci，以前版本都默认为utf8_general_ci

>SHOW VARIABLES LIKE 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | gbk_chinese_ci     |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果。

常用的utf8mb4排序字符集：

2.3.1 utf8mb4_general_ci：
​ci即case insensitive，不区分大小写。没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致，但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。另外，在比较和排序的时候速度更快。

2.3.2 utf8mb4_bin：
​ 将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

2.3.3 utf8mb4_unicode_ci：
不区分大小写，基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法，所以兼容度比较高，但是性能不高。

排序规则概念：

​定义：是指对 指定字符集 下不同字符的比较规则。排序规则有以下特征：

​它和字符集（CHARSET）相关。

每种字符集都有多种它支持的排序规则。

每种字符集都会默认指定一种排序规则为默认值。

排序规则作用：

排序规则指定后，它会影响我们使用 ORDER BY语句查询的结果顺序，会影响到 WHERE条件中大于小于号的筛选结果，会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外，mysql 建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都和排序规则有关。

---

按照MySQL实例级别、库级别、表级别、列级别以及SQL指定 设置排序优先级：

4.1mysql实例级别：

修改my.ini来修改相应的排序规则，改好后 重启mysql服务。

--查看确认
show variables like '%character%';
show variables like '%collation%';

4.2 库级别设置：

创建库时 指定数据集和排序规则

CREATE DATABASE TESTDB
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

--查看
show create database testdb;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
--如果查看之前建立的库，都是默认的utf8mb4_0900_ai_ci 而非utf8mb4_general_ci

4.3 表级别：

创建表时 指定表的数据集和排序规则：

use testdb;
            
CREATE TABLE user(
    `id` int(11) NOT NULL,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--查看
show table status from testdb like 'user';    
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| user | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2022-05-16 14:07:41 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
--我用了自己的表websites;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| websites | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |            0 |         0 |              6 | 2022-05-03 16:55:42 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

4.4 列级别设置：

创建表时 指定列的数据集和排序规则：

drop table user;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--查看
show full columns from user;
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int          | NULL               | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name  | varchar(255) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

4.5 SQL指定设置：

SQL语句中指定

SELECT id, name FROM `user` 
ORDER BY name COLLATE utf8mb4_unicode_ci;

优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置

mysql字符集latin1_mysql的latin1字符集支持所有字符集

Latin1是ISO-8859-1的别名。Latin1是单字节编码，向下兼容ASCII，其编码范围是0x00-0xFF。欧元符号出现的比较晚，没有被收录在latin1中。

因为latin1使用了单字节内的所有空间，在支持latin1的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之，把其他任何编码的字节流当作ISO-8859-1编码看待都没有问题。这是个很重要的特性，MySQL数据库默认编码是Latin1就是利用了这个特性。而ASCII编码是一个7位的容器。
这使得 如果数据库内表的字符集是latin1，那么默认情况下中文也可被支持！

把一个gbk编码的串写入latin1的表，不会有任何问题，保存的是原封不动的字节流。从表中读取已写入的串也不会有任何问题，且读出的字节流就和当初写入的完全一致。（只要再用合适的编码集去解码就行了，如gbk）。读取出来以后，如果在终端下，就会理解成locale类型(如果locale系gbk，当时写入的gbk中文串可正常回显)

综上，建DB和访问DB时如果都采用默认的latin1，那就不仅仅支持中文，而是支持任意的编码方式！

---

中文编码的经验教训：

1. 基于可维护的角度，虽然latin1没什么问题，但是还是尽量换成utf8或者gb系列

2. 出现乱码时：

SHOW VARIABLES LIKE 'character%'

SHOW VARIABLES LIKE 'collation_%';

a、要保证数据库中存的数据与数据库编码一致，即数据编码与character_set_database一致；

b、要保证通讯的字符集与数据库的字符集一致，即character_set_client, character_set_connection与character_set_database一致；

c、要保证SELECT的返回与程序的编码一致，即character_set_results与程序编码一致；

d、要保证程序编码与浏览器、终端编码一致

3. 要想简单一点的话，就将各个字符集都设为一致的，写入mysql的配置文件，每次用客户端都设置一下字符集(set names 'xxx')，写入和读取时要记得确保字节流的编码是ok的

mysql> set names utf8;    --用于设定数据库编码，让中文可以正常显示

> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb3                                                 |
| character_set_connection | utf8mb3                                                 |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb3                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3

不过set names 只是在会话阶段 暂时地改变了编码集，打开新窗口 依然会恢复原来的character set。        其他改变编码集的方案见下

 三种 作用域(有效期)不同的 改变mysql编码集的 方案: 设置MySQL的字符编码_Turp的博客/mysql字符集和排序字符集的区别_戴国进的博客

---

2.1 基本命令

参考：MySQL详细学习教程（建议收藏）_Baret-H的博客-CSDN博客_mysql教程

库 相关命令 

mysql>
show databases;	                            --查看当前所有的数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;    --创建数据库
SHOW CREATE DATABASE 数据库名;                --查看创建数据库的语句
DROP DATABASE [if EXISTS] 数据库名;        --删除数据库,整个文件夹被删去
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
show tables from 数据库名;    --查看对应数据库的表
describe/desc 表名;	--显示表的部分信息
exit	--退出连接

--		--单行注释
#		--单行注释
/*...*/		--多行注释

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.13 sec)

mysql> use mysql;    #进入库mysql
Database changed

mysql> show tables;    #查看库mysql里所有的表
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
    ...
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+

--常用
mysql> show table status from mysql;    --显示库mysql中所有表的详细信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     --显示库RUNOOB里名字以runoob开头的表的信息

mysql> desc user;    --显示表user的部分信息；或者 show columns from user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
...
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
...
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
...
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
...
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

mysql> show full columns from student;    --显示表的详细信息
mysql> show index from user;    --显示表的详细索引信息

表中的列类型(type)：MySQL详细学习教程（建议收藏）_Baret-H的博客-CSDN博客_mysql教程

        MySQL 数据类型 | 菜鸟教程

2.2 创建表

创建库见上面的 2.1基本命令

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--公式
CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
--即， 字段名+列类型+是否为空+默认值 +注释comment
/*
表名和字段尽量使用``括起来; 字符串使用单引号括起来; 主键的声明一般放在最后，便于查看；
不设置字符集编码的话，会使用MySQL默认的字符集编码，有多种修改方式，见1.6进阶内容；
ENGINE指定 要使用的引擎
*/

mysql> desc student;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)  | NO   |     | 匿名    |                |
| pwd      | varchar(20)  | NO   |     | 123456  |                |
| sex      | varchar(2)   | NO   |     | 女      |                |
| birthday | datetime     | YES  |     | NULL    |                |
| address  | varchar(100) | YES  |     | NULL    |                |
| email    | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

mysql> show create table student;    #查看表的定义语句
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
| student | CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

表中的 其他字段属性(type之外的)：

Auto_InCrement

通常理解为自增，自动在上一条记录的基础上默认+1

通常用来设计唯一的主键，必须是整数类型

可定义 起始值 和 步长：

AUTO_INCREMENT=100 设置当前表步长， 只影响当前表
SET @@auto_increment_increment=5，影响所有使用自增的表(全局)

NULL 和 NOT NULL

• 默认为NULL , 即 该列可以没有数值
• NOT NULL , 则 该列必须有值

 DEFAULT

• 用于设置默认值
• 若 写入数据时 没有写该列的值 , 则值 默认为"男"的

 COMMENT 注释

拓展：每一个表，都必须存在以下五个字段：

名称	描述
id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

2.5、数据库存储引擎

INNODB（默认）

• 默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

• 早些年使用，节约空间，速度较快

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	支持（MySQL5.5前不支持）
表空间大小	较小	较大，约为2倍

数据库文件存在的物理空间位置：

MySQL数据表以文件方式存放在磁盘中

• 包括表文件 , 数据文件 , 以及 数据库的选项文件
• 位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

MySQL在文件引擎上区别：

• INNODB数据库文件类型就包括**.frm**、.ibd以及在上一级目录的ibdata1文件
• MYISAM存储引擎，数据库文件类型就包括
  • .frm：表结构定义文件
  • .MYD：数据文件
  • .MYI：索引文件

---

2.6、修改数据库

修改表

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);
alter table students add age int(11) default null comment '年龄';

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束, 这里改的是type
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段名 和 type 都改了

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

删除表

-- 删除表(如果存在再删除)    DROP TABLE [IF EXISTS] 表名
DROP TABLE IF EXISTS teachers;    --会删掉 表 文件

• IF EXISTS为可选 , 判断是否存在该数据表
• 如删除不存在的数据表会抛出错误。所有的创建和删除尽量加上判断，以免报错

---

3、MySQL数据管理

3.1、外键

users是 从表，roles作为被引用的那个表 是主表， (容易混淆）。有两种添加外键的方式：

1、在创建表的时候增加 外键约束

先有主表，再有从表       

-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 字段名 列类型 是否空 默认值 别称
-- 指定主键 指定外键 建立外键约束
-- 引擎 编码集

2、创建表 后，再 添加外键约束

这里就没要求 表创建的先后顺序，但 建立联系前，两个表必须存在。

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

---

mysql> drop table if exists grade;    --试图删主表
ERROR 3730 (HY000): Cannot drop table 'grade' referenced by a foreign key constraint 'FK_gradeid' on table 'student'.

mysql> drop table if exists student;
Query OK, 0 rows affected (0.04 sec)

mysql> drop table if exists grade;
Query OK, 0 rows affected (0.05 sec)

删除有 外键关系 的表的时候，必须要先删除 从表 ，再删除被引用的表（主表）

以上的操作都是物理外键、数据库级别的外键，不建议使用！避免数据库过多造成困扰！

• 数据库就应该是单纯的表，只用来存数据，只有行（数据）和列（属性）
• 我们想使用多张表的数据，使用外键，用程序去实现

---

3.2、DML语言 insert update delete TRUNCATE

Data Manipulation Language：数据操作语言

添加数据 insert

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');    --报错
INSERT INTO `student`(`name`,`gradeid`) VALUES ('zsr',1);

--下面也要加上`gradeid`字段，因为 建表的时候要求 非空
-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');   --要加上gradeid

-- 省略字段，这是当所有列都要添加数据的时候
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 

修改数据 update

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;

-- 不指定条件的情况,会改动 表中所有行
UPDATE `student` SET `name`='zsr204';    --改表中所有行的name

-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

关于WHERE条件语句：

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN…AND…	闭合区间
AND	和
OR	或

删除数据 delete 和 TRUNCATE

DELETE FROM 表名 [WHERE 条件]

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;    --只是清空表中数据，表文件还在

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

CREATE TABLE `test`(
    `id` INT(4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

-- 不会影响自增
DELETE FROM `test`;    --即，清空表后，再添加数据，id会从原来的位置开始，而非从1开始

-- 会影响自增
TRUNCATE TABLE `test`;    --truncate清空后，再添加数据，id从1开始

关于DELETE删除的问题，重启数据库后（关闭窗口不算，必须是stop start)：

• INNODB引擎 自增列会从1开始（存在内存当中，断电即失） 旧版本！
• MYISAM引擎 继续从上一个子增量开始（记录到数据文件里，不会丢失）

---

 mysql重启后自增id是从几开始增加

• 「innodb 引擎(8.0 之前版本)：Innodb 表中把自增列作为主键 ID 时，自增列通过 auto-increment 计数器实现，计数器的最大值是记录在内存中。重启数据库，会导致 auto-increment 计数器重置，从而导致下一次insert 的主键 ID 重置。」
• 「MyISam 引擎：MyISAM 表会把自增列(auto-increment 计数器)最大值是记录到数据文件里，重启 MySQL 自增列(计数器)最大值不会丢失，从而使用自增列作为主键 ID 时也不会丢失。」

show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coll` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3 |    --可以看到auto_increment的计数
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQL 8.0 auto-increment 计数器逻辑

在 MySQL 8.0 中，这个计数器的逻辑变了：每当计数器的值有变，InnoDB 会将其写入 redo log，保存到引擎专用的系统表中。

MySQL 正常关闭后重启：从系统表中获取计数器的数值。

MySQL 故障后重启：从系统表中获取计数器的值；从最后一个检查点开始扫描 redo log 中记录的计数器值；取这两者的最大值作为新值。

innodb 表在启动后，AUTO_INCREMENT 会自动检测出、并重置为当前表中自增列的最大值 +1

---

假如表格里 AUTO_INCREMENT 计数器此时值是 10。执行update table set id = 15 where id = 9后，这时继续插入数据，到了自增 ID=15 重现重复会报错。但是后面继续插入，就不会报错，因为即使报错了，AUTO_INCREMENT 的值依旧会增加。

---

现在使用的一般都是 innodb 引擎，如果将 myisam 引擎转换过来的时候，一定要小心这个引擎在自增 id 上的不同表现。在主从使用不同引擎的时候,也会出现问题，最好将引擎改完一致性的。

TRUNCATE 完全清空一个数据库表，表的结构和索引约束不会变！区别：

DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表。
TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增。

---

DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

---

4、DQL查询数据

Data QueryLanguage 数据查询语言：

• 查询数据库数据，如SELECT语句
• 简单的单表查询或多表的复杂查询和嵌套查询
• 是数据库语言中最核心，最重要，使用频率最高的语句

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

前提配置：

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;    --会创建一个叫school的文件夹

-- 用school数据库
USE `school`;

-- 创建年级表grade表
CREATE TABLE `grade`(
	`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
	`GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY	(`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- 给grade表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

-- 创建成绩result表
CREATE TABLE `result`(
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`SubjectNo` INT(4) NOT NULL COMMENT '考试编号',
	`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
	`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
	KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 给result表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);

-- 创建学生表student
CREATE TABLE `student`(	
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`LoginPwd` VARCHAR(20) DEFAULT NULL,
	`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
	`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
	`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
	`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
	`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',
	`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
	`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`StudentNo`),
	UNIQUE KEY `IdentityCard` (`IdentityCard`),
	KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 给学生表插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');

-- 创建科目表
CREATE TABLE `subject`(
	`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
	`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
	`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
	`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

-- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);

SELECT 字段 FROM 表;    --记得加``

4.1、基础查询 from, as

SELECT 查询列表 FROM 表名;

--查询列表 可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
--查询结果 是一个虚拟的表格

-- 查询全部
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 去重 用DISTINCT 
SELECT DISTINCT `StudentNo` FROM result;

4.2、条件查询 where, like, between...and..., not, in

select 查询列表 from 表名 where 筛选条件;

-- 查询考试成绩在95~100之间的
-- AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;

-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;

-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';        --%代表n个字符，可为0

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';        --_代表1个字符

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);    --这不是范围，而是一个数组

4.3、分组查询 group by, having

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段    --会自动去重，只保留第一个。 且所选的字段，决定了函数的作用对象
【having 分组后的筛选】
【order by 排序列表】

SELECT *,AVG(StudentResult)
FROM result r                --把result 称为r表，换个别的字也行，比如rr或者q
INNER JOIN `subject` s        --需要连接的表，并 别称为 s表， 同上，叫ss也行
on r.SubjectNo=s.SubjectNo    --中间这三行的作用 就是 连接两个表
GROUP BY r.SubjectNo;    --按照subjectno分为3组，然后AVG()分别把1，2，3组作为对象

SELECT *,AVG(StudentResult)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.studentno;    --按照studentno分为 2组，然后AVG()分别把1000,1001组作为对象

SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
where r.studentresult>96    --加上where，就能在分组前 先把低于96的数据 剔除
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

where 和 having区别：

	使用关键字	筛选的表	位置
分组前筛选	where	原始表	group by的前面
分组后筛选	having	分组后的结果	group by 的后面

分组后 用where 会报错

4.4、连接查询

与其他表连接

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;

-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;

-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）

SELECT *
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo;

SELECT *
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo;

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
--当同一个字段名，如studentno，出现在多个表中时，select需指明用哪一个表的studentno
--如上，用s.studentno和r.studentno都可以
--后面三个字段名 都只出现在一个表中，故 不需要 特别指定。
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='程序设计';

自连接。（可理解为 复制表，然后链接）

-- 创建一个表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`pid` INT(10) NOT NULL COMMENT '父课程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'    --AS可省略
FROM course AS a,course AS b    --AS可省略
WHERE a.`courseid`=b.`pid`;

SELECT a.`courseid` AS '父课程', a.coursename as '名', b.`courseid` AS '子课程', b.coursename as '名'
FROM course a,course b
WHERE a.`courseid`=b.`pid`;

 

4.5、排序和分页 order by,  limit

排序 order by

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc
--order by一般放在查询语句的最后    asc升序 desc降序

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

分页

select 查询列表
from 表
limit offset,pagesize;
--limit 比 order by 位置还要靠后
--offset:起始的条目索引，默认从0开始。    注意不是主键值，而是行数
--size:本页显示的条目数

--加点数据
INSERT INTO `grade`(`GradeName`) 
VALUES ('大5'),('大6'),('大7'),('大8');
INSERT INTO `grade`(`GradeName`) 
VALUES ('大9'),('大10'),('大11'),('大12');

select * from `grade`
limit 0,5;    --从第一行开始，显示前5行

4.6、子查询

即，在 where子句中嵌套一个子查询语句

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
WHERE SubjectName='程序设计'
ORDER BY StudentResult DESC;

-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
    SELECT SubjectNo FROM `subject`
    WHERE SubjectName='程序设计'
)

--再来一个
select * from subject where gradeid=(select gradeid from `grade` where gradename='大二');

4.7、MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
select floor(5.1)；  -- 向下取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢 我',1,3,'超级'); -- INSERT(str,pos,len,newstr) 
--pos从1开始，位置是第一个字的前面，往后就是每个字的间隙
--len是从pos开始 想要替换掉的 原str的长度
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串

-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前 日期+时间
SELECT LOCALTIME(); -- 本地 日期+时间
SELECT SYSDATE(); -- 系统 日期+时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());    --无前缀0
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

聚合函数

函数	描述
max	最大值
min	最小值
sum	和
avg	平均值
count	计算个数

SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;    --都相当于数有多少行
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

---