MySQL数据库

1.) 数据库基本概念

  1. 数据库
    数据库会按照一定的数据结构来组织，存储和管理数据。
  2. 数据
    描述现实世界事物的符号的记录。

2.) 数据库管理技术发展的3个阶段

人工管理阶段

  • 记录数据，纸上，墙壁，竹签；数据主要存储在纸带，磁带。通过手工来记录数据。
  • 特点：
  • 数据不能够长期保存
  • 不便于查询
  • 数据是不能共享，独立性

文件管理阶段

  • 数据存储的方式 在磁盘和磁鼓，通过文件的形式进程存储。
  • 特点：
  • 数据可以长期保存
  • 数据由文件系统来管理
  • 数据仍然不能共享
  • 无法应对突发事件（文件误删，磁盘出现故障）

数据库系统阶段

  • 使用数据库管理系统来进行管理数据，将数据进行结构化。
  • 特点：
  • 数据是由数据库管理系统统一管理和维护及控制
  • 数据可以进行共享
  • 数据独立性很强

3) 数据库管理系统

  • DataBase Management System = DBMS，数据系统核心软件之一，是位于用户与操作系统之间的数据管理软件。用于建立，使用和维护数据库。
  • 常用：MySQL、Oracle、SQLserver

4) 数据库设计& 关系型数据库

一、数据设计的步骤

  • 需求分析：了解业务处理方式，用户需求。充分调查
  • 概念结构设计：现实世界的真实反映，包括实体与实体的关系。
  • 逻辑结构设计：通过概念设计结果，来匹配DBMS所支持的数据模型。
  • 物理结构设计：结合DBMS，进行物理模型设计。
  • 编码测试：
  • 运行维护

二、数据模型

概念数据模型
  • 相关概念：
  • 实体（Entity）：客观存在并且相互区别事物与事物之间的联系。例如：一个学生，一门课程。
  • 属性（Attribute）：实体所具有的某一特性。例如：学生：姓名，性别，身高，出生日期，身份号。
  • 码（Key）：唯一标识实体的属性集。例如：学生，学号是学生实体的唯一标识。
  • 域（Domain）：属性的取值范围。例如：学生年龄（7岁~22岁）
  • 实体型（Entity Type）: 用实体名及其属性名称集合来抽象刻画同类的实体。例如：学生(学号，姓名，性别，年龄……)
  • 实体集（Entity Set）: 同型实体的集合称为实体集。
  • 联系/关系（Relationship）：实体与实体之间及实体与组成它的属性之间的关系。
  • 一对一关系（1:1）
    如果存在一个实体集A，每一个实体，在实体集B中至多有一个实体与之联系，反之亦然。则称为实体A与实体B之间是一对一的关系。
  • 一对多的关系（1：N）
    如果对于实体A中每一个实体，在实体集B中有n个实体与值联系，反之，对于实体集B中每一个实体，在实体集A中至多只有一个实体与之联系。则称为1对多的关系。
  • 多对多的关系（M:N）
    如果实体集A中每一个实体，在实体集B中有N个实体与之联系，反之，在实体集B中每一个实体，对于实体集A中有M个与之对应。则称为：多对多的关系。
  • ER图（关系）
  • 实体：通过矩形来表示实体。
  • 关系：是菱形来表示，
  • 属性：椭圆形来表示。

MySQL建唯一约束索引可以几个字段_数据库

逻辑数据模型
  • 层次模型：
  • 层次模型就是一个倒立的树，满足以下两个条件：
  • 有且仅有一个节点无父节点，整个节点称为根节点
  • 其他节点有且仅有一个父节点。

MySQL建唯一约束索引可以几个字段_ci_02

  • 网状模型：
  • 事物与事物之间不满足层次模型，可以网状模型，满足以下条件：
  • 允许一个以上的节点无父节点
  • 一个节点可以有多于一个的父节点
  • 关系模型：
  • 用二维表结构来表示实体与实体之间的联系的模型
  • 关系模型
  • 学生（学号，姓名，性别，班级，生日）
  • 课程（编号，课程名称，任课老师）
  • 选修（学号，编号，成绩）
物理结构模型

MySQL建唯一约束索引可以几个字段_ci_03

三、数据库的类型

1. 关系型数据库
  • 关系型数据库：采用行和列组成二维表来管理数据。

MySQL建唯一约束索引可以几个字段_数据库_04

列名：属性，也称为：字段名称

行：表示一行记录，也成一行数据。

SQL语句

  • 优点：使用表格的形式来存储数据，格式统一，易于维护。存储数据比较安全。
  • 缺点：读写性能比较差；不节省空间；固定表结构，导致数据使用不灵活。
2. 非关系型数据库
  • 非关系型数据库：以对象的形式存储数据。key-value形式来存储。
  • 优点：存储数据上非常灵活；在读取海量数据时，速度比较快。
  • 缺点：不支持SQL语句。

MySQL建唯一约束索引可以几个字段_数据库_05

5) 数据库的范式

范式：（Normal Format）

  • 第一范式（1NF）: 属性是不可分割的；属性要具备原子性。

MySQL建唯一约束索引可以几个字段_数据库_06

  • 第二范式（2NF）：必须要有主键（码，唯一的），具有唯一性的，其他的属性要依赖于主键

MySQL建唯一约束索引可以几个字段_ci_07

  • 第三范式（3NF）：确保每列都与主键列直接相关，而不间接相关。

MySQL建唯一约束索引可以几个字段_数据库_08

6) 结构化语言（重要）

  • 结构化语言（Structure Query Language）：SQL
  • 数据查询语言：DQL（Data Query Language） select (查询)
  • 数据操纵语言：DML（Data Manipulation Language）
  • insert ： 插入数据
  • update： 更新数据
  • delete ：删除数据
  • 数据定义语言：DDL（Data Definition Language）
  • create : 创建数据库，创建表，创建视图，创建存储过程等等
  • alter：修改数据库的字符，修改表结构等一些针对数据对象的修改
  • drop ：删除数据库，删除表，删除视图，删除数据库对象。
  • rename ：重命名
  • truncate ：删除，删除后不能恢复数据
  • 数据控制语言：DCL（Data Control Language）
  • grant ： 赋予某些权限
  • revoke ： 移除权限

7) MySQL的使用

  • 首先：确认Mysql的服务是否启动。
  • mysql服务的启动方式：
  • 第一种：

没有启动mysql服务后，出现的错误提示：

MySQL建唯一约束索引可以几个字段_mysql_09

  • 第二种：通过cmd窗口，输入命令：
1. 停止服务的命令：net stop <服务名称>

例：net stop mysql57

2. 启动服务命令：net start <服务名称>

例：net start mysql57

MySQL建唯一约束索引可以几个字段_ci_10

  • 更改root密码：
更改密码命令：mysqladmin -u <用户名> -p<原密码> password <新密码>

例：mysqladmin -uroot -p123456 password 111111

基础命令

1. 显示数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
/*
information_schema：主要存储数据库对象信息，比如：用户表信息，列信息，权限信息，字符集等
mysql：主要存储用户和用户访问权限的信息。
performance_schema：收集数据库服务器性能参数
sys：提供视图使用，以及性能数据。
*/
2. 切换数据库

use <数据库名称>;

mysql> use mysql;
Database changed
3. 查看表结构

desc <表名称>;

mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(11)     | YES  |     | NULL    |       |
| uname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4. 模糊查询

like：% _

模糊查询： % 代表匹配多个字符。_ 带表匹配一个字符

mysql> show databases like 'my%';
+----------------+
| Database (my%) |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> show tables like 'u%s';
+-----------------------+
| Tables_in_mysql (u%s) |
+-----------------------+
| users                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> show databases like 'mysq_';
+------------------+
| Database (mysq_) |
+------------------+
| mysql            |
+------------------+
1 row in set (0.00 sec)

mysql> show tables like 'us___';
+-------------------------+
| Tables_in_mysql (us___) |
+-------------------------+
| users                   |
+-------------------------+
1 row in set (0.00 sec)

数据库命令

1. 创建数据库

create database <数据库名称>;

  • 数据库名称要求：
  • 数据库名称必须要唯一，和其他数据库名是不能重复
  • 数据库名称，包含是字母，数字，或者_，不能出现汉字。
  • 要求数据库名称，最好是小写。数据库中不区分大小写。
  • 不允许使用mysql 关键字，select，create，alter，database等等
    完整：(所有带[] , 代表是可以省略的内容)
  • create database [if not exists] <数据库名称>
  • [ [default ] character set < 字符集名称>]
  • [[default] collate <校对规则>]；
mysql> create database sin_db
    -> default character set utf8
    -> default collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
2. 查看字符集

show character set;

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
  • 常用的字符集：
  • latin1 : 支持西欧的字符，希腊字符等
  • gbk : 支持简体中文字符
  • big5：支持繁体中文祖父
  • utf8 : 几乎支持所有国家的字符
  • 查看数据库字符集：
    show create database <数据库名称>;
mysql> show create database sin_db;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| sin_db   | CREATE DATABASE `sin_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
  • 修改数据库字符集：
    alter database <数据库名称> character set <字符集的名称> [collate <校验规则名称>]；
mysql> alter database sin_db character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
  • 查看表字符集：
    show create table <表名>;
mysql> show create table users;
+-------+-------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+-------------------------------------------+
| users | CREATE TABLE `users` (
  `uid` int(11) NOT NULL,
  `uname` varchar(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------+
1 row in set (0.00 sec)
  • 更改表字符集：
    alter table <表名称> character set <字符集名称>；
mysql> alter table users character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 查看字段字符集：
    show full colunns from <表名>;
  • 修改字段字符集：
    alter table <表名称> convert to character set <字符集名称>;
mysql> alter table users convert to character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
3. 删除数据库
**drop database <数据库名称>；**
mysql> drop database sin_db;
Query OK, 0 rows affected (0.01 sec)
4. 创建表

create table <表名称>(

列名 数据类型 [约束],

列名2 数据类型 [约束],

……,

列名n 数据类型

);

mysql> create table users(
    -> uid int primary key,
    -> uname varchar(20) not null
    -> );
Query OK, 0 rows affected (0.04 sec)
5. 数据类型

数值类型

MySQL建唯一约束索引可以几个字段_ci_11

日期和时间类型

MySQL建唯一约束索引可以几个字段_mysql_12

字符串类型

MySQL建唯一约束索引可以几个字段_mysql_13

6. 表插入数据

insert into <表名称> values(值1，值2，值3，……)；

insert into <表名称> (字段1，字段2) values（值1，值2）；

插入多行数据

insert into <表名称> values (值1，值2，值3，……),(值1，值2，值3，……),(值1，值2，值3，……)

mysql> insert into users values(1,'一');
Query OK, 1 row affected (0.01 sec)

mysql> insert into users(uname,uid) values('二',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into users values(3,'三'),(4,'四');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | 一    |
|   2 | 二    |
|   3 | 三    |
|   4 | 四    |
+-----+-------+
4 rows in set (0.00 sec)
7. 复制表，备份表

复制表结构：

create table <新表名称> like <被复制的表名称>

复制表结构和表数据(备份)：

create table <新表名称> as (select * from <被复制的表名称>)

mysql> create table users_bk1 like users;
Query OK, 0 rows affected (0.04 sec)

mysql> create table users_bk2 as (select * from users);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
8. 表重命名

alter table <原表名称> rename to <新表名称>；

(rename to = rename as)

rename <新表名称> <原表名称> to table;

mysql> alter table users rename as user;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table users_bk1 to user_bk1;
Query OK, 0 rows affected (0.01 sec)
9. 更改表结构

change ：可以更改字段名，也可以更改数据类型。

alter table <表名称> change <原字段名称> <新字段名称> <数据类型>;

modify : 只能修改字段的数据类型，或者约束，不能更改字段名称。

alter table <表名称> modify <字段名称> <数据类型> ;

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(11)     | NO   | PRI | NULL    |       |
| uname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 更改uid为userid
mysql> alter table user change uid userid int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#更改uname字段长度为21，且取消not null
mysql> alter table user modify uname varchar(21);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| userid | int(11)     | NO   | PRI | NULL    |       |
| uname  | varchar(21) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
10. 增加表字段
**add** : 给表增加一列，或者增加一个字段

alter table <表名称> add <字段名称> <数据类型>;

mysql> alter table user add usex enum('男','女');
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
11. 删除表字段

drop : 删除字段项

alter table <表名称> drop <字段名称>;

mysql> alter table user drop usex;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
12. 更改表数据：

update ：更改表数据关键词

update <表名称> set <字段名=更新字段值> where [条件];

mysql> select * from user;
+--------+-------+
| userid | uname |
+--------+-------+
|      1 | 一    |
|      2 | 二    |
|      3 | 三    |
|      4 | 四    |
+--------+-------+
4 rows in set (0.00 sec)

mysql> update user set uname='两' where userid = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+--------+-------+
| userid | uname |
+--------+-------+
|      1 | 一    |
|      2 | 两    |
|      3 | 三    |
|      4 | 四    |
+--------+-------+
4 rows in set (0.00 sec)
13. 删除表中数据：
**delete from <表名称> where <条件>；**
mysql> delete from user where userid = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+--------+-------+
| userid | uname |
+--------+-------+
|      1 | 一    |
|      2 | 两    |
|      3 | 三    |
+--------+-------+
3 rows in set (0.00 sec)

8) 完整性约束

  • 非空，唯一，主键约束，外键约束，自增长，检查（枚举类型代替），默认值，7大约束条件
  • 可以在创建表的时候，去创建约束条件。

1. 非空约束

  • 关键词：not null
  • 作用：让某个列，不允许空
mysql> create table user(
    -> uid int not null,
    -> uname varchar(20) not null
    -> );
Query OK, 0 rows affected (0.04 sec)

/*
null 与 空值'' 的区别：
1、空值'' :不占用空间，但是有长度，长度是0。代表一个字符串
2、null: 占用空间，但是没有长度。代表数据值为空。 
*/

2. 唯一约束

  • 关键词：unique
  • 作用：去重某一列的数据，让数据保持唯一性。
mysql> create table user(
    -> uid int unique,
    -> uname varchar(20) unique
    -> );
Query OK, 0 rows affected (0.04 sec)

3. 主键约束

  • 作用：具有非空，和唯一约束限制。
  • 关键词：primary key
  • 设置主键：可以设置1个字段为主键，也可以设置多个字段联合作为主键。
    主键的规则 ：
  • 每个表只能有一个主键。
  • 尽量选择一列来作为主键。选择列最好数值的型的。
  • 尽量选择数据值更新少的列来作为主键。
# 一个字段设置主键
mysql> create table user(
    -> uid int primary key,
    -> uname varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)
# 两个字段联合为主键
mysql> create table user(
    -> uid int,
    -> uname varchar(20),
    -> primary key(uid,uname)
    -> );
Query OK, 0 rows affected (0.04 sec)
# 表外添加主键
mysql> alter table user modify uid int primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 表外添加联合主键
mysql> create table user(
    -> uid int,
    -> uname varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)
# 修改表结构的来添加主键,alter table <表名称> add constraint <约束名称> primary key(字段名1,字段名2);
mysql> alter table user add constraint PK_ID primary key(uid,uname);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除主键

4. 默认值

  • 关键词：default
  • 作用：性别：男，女；
mysql> create table user(
    -> uid int,
    -> uname varchar(20),
    -> usex varchar(4) default '男'
    -> );
Query OK, 0 rows affected (0.03 sec)

5. 自增长

  • 关键字：auto_increment
  • 作用：表示这个字段值可以自动增加，每一个默认+1
  • 注意：自增长字段必须具有唯一性(primary key | unique)
mysql> create table user(
    -> uid int primary key auto_increment,
    -> uname varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

6. 检查约束

  • 关键字：check （mysql不生效）
  • 作用：为了减少无效数据的输入，定义列中可接受的值。
  • MySQL中使用枚举代替check：enum
mysql> create table user(
    -> uid int,
    -> uname varchar(20),
    -> usex varchar(4) check(usex in ('男','女'))
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table user(
    -> uid int,
    -> uname varchar(20),
    -> usex enum('男','女')
    -> );
Query OK, 0 rows affected (0.04 sec)

7. 外键约束

  • 关键字：foreign key
  • 什么是外键：从表中对应于主表的列（在主表中作为主键），在从表称为外键或者引用键。
  • 创建外键：
  • 创建表时，创建外键
    references : 引用，参考
    foreign key(从表字段名称) references 主表名称(主键id) : 从表的字段引用主表的主键，从表字段设置为外键
    删除外键：alter table <表名> drop foreign key <外键名称>;
mysql> create table sc(
    -> sid int,
    -> uid int,
    -> foreign key(uid) references user(uid)
    -> );
Query OK, 0 rows affected (0.04 sec)
#创建表后添加外键
mysql> create table sc(
    -> sid int,
    -> uid int
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> alter table sc add constraint fk_uid foreign key(uid) references user(uid);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除外键
mysql> alter table sc drop foreign key fk_uid;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

9) 单表查询

1. 基础查询

查询所有的数据：select * from <表名称>;

mysql> select * from user;
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | 一    |
|   2 | 二    |
|   3 | 三    |
+-----+-------+
3 rows in set (0.00 sec)
# 按字段名称查询
mysql> select uname,uid from user;
+-------+-----+
| uname | uid |
+-------+-----+
| 一    |   1 |
| 二    |   2 |
| 三    |   3 |
+-------+-----+
3 rows in set (0.00 sec)

2. 条件查询

  • 关键字：where
  • 语法：select * from <表名> where + 条件；
mysql> select * from user where uid=1;
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | 一    |
+-----+-------+
1 row in set (0.00 sec)

3. 模糊查询

like : % 代表匹配多个字符，_ 代表匹配一个字符

mysql> select * from user where uname like '一%';
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | 一个人    |
+-----+-------+
1 row in set (0.00 sec)

4. 分页查询

  • 关键字：limit
  • 用法：limit m,n ; m,n 必须是正整数。limit 至少有一个参数
mysql> select * from user limit 1;
+-----+-------+
| uid | uname |
+-----+-------+
|   1 | 一    |
+-----+-------+
1 row in set (0.00 sec)

mysql> select * from user limit 1,2;
+-----+-------+
| uid | uname |
+-----+-------+
|   2 | 二    |
|   3 | 三    |
+-----+-------+
2 rows in set (0.00 sec)

5. 分组查询

  • 关键词：group by <字段名称> having <条件>
  • 语法：select * from 表名称 group by <字段名> having <条件>；
mysql> select * from user;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   1 | 一     |   100 |
|   2 | 二     |   100 |
|   3 | 三     |   100 |
|   1 | 一一   |  NULL |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
|   3 | 三三   |  NULL |
+-----+--------+-------+
7 rows in set (0.00 sec)

mysql> select uid,count(*)
    -> from user
    -> group by uid;
+-----+----------+
| uid | count(*) |
+-----+----------+
|   1 |        2 |
|   2 |        3 |
|   3 |        2 |
+-----+----------+
3 rows in set (0.00 sec)

6. 排序

  • 关键词：order by
  • 语法：select * from 表名称 order by asc（正序） |desc（倒序）
mysql> select * from user;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   1 | 一     |   100 |
|   2 | 二     |   100 |
|   3 | 三     |   100 |
|   1 | 一一   |  NULL |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
|   3 | 三三   |  NULL |
+-----+--------+-------+
7 rows in set (0.00 sec)

mysql> select * from user order by uid;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   1 | 一     |   100 |
|   1 | 一一   |  NULL |
|   2 | 二     |   100 |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
|   3 | 三     |   100 |
|   3 | 三三   |  NULL |
+-----+--------+-------+
7 rows in set (0.00 sec)

mysql> select * from user order by uid desc;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   3 | 三     |   100 |
|   3 | 三三   |  NULL |
|   2 | 二     |   100 |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
|   1 | 一     |   100 |
|   1 | 一一   |  NULL |
+-----+--------+-------+
7 rows in set (0.00 sec)

关系运算符：> < = >= <= != <>不等于

逻辑运算符： and（与），or(或者)，not（非）

mysql> select * from user where uid>2;
+-----+-------+-------+
| uid | uname | score |
+-----+-------+-------+
|   3 | 三    |   100 |
|   3 | 三三  |  NULL |
+-----+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from user where uid=2;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   2 | 二     |   100 |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
+-----+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from user where uid!=2;
+-----+-------+-------+
| uid | uname | score |
+-----+-------+-------+
|   1 | 一    |   100 |
|   3 | 三    |   100 |
|   1 | 一一  |  NULL |
|   3 | 三三  |  NULL |
+-----+-------+-------+
4 rows in set (0.00 sec)

mysql> select * from user where uid=1 or uid=3;
+-----+-------+-------+
| uid | uname | score |
+-----+-------+-------+
|   1 | 一    |   100 |
|   3 | 三    |   100 |
|   1 | 一一  |  NULL |
|   3 | 三三  |  NULL |
+-----+-------+-------+
4 rows in set (0.00 sec)

mysql> select * from user where uid=1 and uname='一';
+-----+-------+-------+
| uid | uname | score |
+-----+-------+-------+
|   1 | 一    |   100 |
+-----+-------+-------+
1 row in set (0.00 sec)

mysql> select * from user where uid=1 and uname not in ('一');
+-----+-------+-------+
| uid | uname | score |
+-----+-------+-------+
|   1 | 一一  |  NULL |
+-----+-------+-------+
1 row in set (0.00 sec)

10) 聚合函数

1. count()

  • 含义：统计数量，统计非空数据的个数( 不包含null的数据)
mysql> select * from user;
+-----+--------+-------+
| uid | uname  | score |
+-----+--------+-------+
|   1 | 一     |   100 |
|   2 | 二     |   100 |
|   3 | 三     |   100 |
|   1 | 一一   |  NULL |
|   2 | 二二   |  NULL |
|   2 | 二二二 |  NULL |
|   3 | 三三   |  NULL |
+-----+--------+-------+
7 rows in set (0.00 sec)

mysql> select count(score) from user;
+--------------+
| count(score) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

2. max()

  • 含义：返回记录中一个最大的值
mysql> select max(score) from user;
+------------+
| max(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

3. 数值函数

  • round(n) : 对数据取整，进行四舍五入的取整。
  • ceil(n) : 向上取整。 81.1 81.9，都是82
  • floor(n) : 向下取整。81.1,81.9 ，都是取8
  • rand() : 默认生成 0~1之间的随机。 如果round(1) ，那么返回结果是一个固定值（返回一个重复序列）。
mysql> select round(3.1415);
+---------------+
| round(3.1415) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> select ceil(3.1415);
+--------------+
| ceil(3.1415) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(3.1415);
+---------------+
| floor(3.1415) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.36050330415414955 |
+---------------------+
1 row in set (0.00 sec)

4. 日期函数

  • now()
  • 返回系统当前日期，返回日期的格式：YYYY-MM-DD HH:MI:SS
  • curdate()
  • 返回系统当前的日期，返回日期格式：YYYY-MM-DD
  • timestampdiff()
  • 返回单独年，月，日
  • year(日期) ： 返回年的值
  • month(日期) ： 返回月的值
  • day(日期) ： 返回天的值
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-04-26 16:33:42 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-04-26 |
+------------+
1 row in set (0.00 sec)

mysql> select timestampdiff(year,'2000-01-01',now());
+----------------------------------------+
| timestampdiff(year,'2000-01-01',now()) |
+----------------------------------------+
|                                     21 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select year(now()),month(now()),day(now());
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
|        2021 |            4 |         26 |
+-------------+--------------+------------+
1 row in set (0.00 sec)

5. 字符串函数

  • length() ： 返回一个字符串的长度
mysql> select length(now());
+---------------+
| length(now()) |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

mysql> select length('12321');
+-----------------+
| length('12321') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

11) 多表查询

1. 交叉连接

  • select * from <表1名称> [cross join] <表2名称> where 子句
  • 什么是交叉连接？
  • 交叉连接一般返回的是一个笛卡尔积
  • 笛卡尔积：两个集合的乘积。
  • 关联查询语法
  • select * from <表1> ，<表2> where <表1.id>=<表2.id>;
mysql> select * from sin1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from sin2;
+------+
| b    |
+------+
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from sin1,sin2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    1 |    3 |
|    2 |    3 |
|    1 |    4 |
|    2 |    4 |
+------+------+
6 rows in set (0.00 sec)
#关联查询
mysql> select * from sin1,sin2 where sin1.a=sin2.b;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

2. 内连接

  • 内连接语法：
  • select * from <表1> [inner] join <表2> on <表1.id>=<表2.id> ；
  • 内连接中：inner 关键字可以省略，只是用join 关键字。
mysql> select * from sin1 join sin2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    1 |    3 |
|    2 |    3 |
|    1 |    4 |
|    2 |    4 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from sin1 join sin2 where sin1.a=sin2.b;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

3. 外连接

  • left join | right join 语法：
  • select * from <表1> left join <表2> on <表1.id> = <表2.id>;
  • select * from <表1> right join <表2> on <表1.id> = <表2.id>;
  • 左外连接——返回的数据：基于左表，返回所有满足条件的数据。
  • 右外连接——返回的数据：基于右表，返回所有满足条件的数据。
mysql> select *
    -> from sin1
    -> left join sin2 on sin1.a=sin2.b;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select *
    -> from sin2
    -> left join sin1 on sin1.a=sin2.b;
+------+------+
| b    | a    |
+------+------+
|    2 |    2 |
|    3 | NULL |
|    4 | NULL |
+------+------+
3 rows in set (0.00 sec)

12) 子查询

  • 将一个查询语句嵌套在另一个查询语句当中
  • 执行顺序：先执行子查询，再执行父查询。
  • select * from 表1 where 字段名 in (select 字段名 from 表2)

1、单行子查询：

  • 使用关系运算符：> < = >= <= != <>
  • 语法：select * from 表1 where 字段1 = （select 字段1 from 表2 ）;
# =：只能等于一个值
mysql> select * from sin1 where a = (select b from sin2 where b = 2);
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

2. in| not in

  • 语法：select * from 表1 where 字段1 in （select 字段1 from 表2 ）；
# in：可以取多个值
mysql> select * from sin1 where a in (select b from sin2);
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

3.exists | not exists

  • exists : 所选的字段存在于后面子句的结果集当中
  • 如果存在数据返回true；如果不存在数据则返回false。
  • 语法：
  • select from 表1 where exists(select from 表b)
#如果存在
mysql> select * from sin1 where exists (select b from sin2 where b = 2 and sin1.a=sin2.b);
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

4. any|all|some

  • any，all ，some 的关键字与子查询语句结合使用。
  • all : 代表查询出来所有的结果。
  • any|some : 表示查询的任意一个结果
# 所有小于2,3,4的值
mysql> select * from sin1 where a < any(select b from sin2);
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
# 必须全部小于2,3,4
mysql> select * from sin1 where a < all(select b from sin2);
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
# any = some
mysql> select * from sin1 where a < some(select b from sin2);
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

5. 纵向组合查询

  • 关键字：union | union all
  • 语法：（select from 表A） union （select from 表B）
  • 注意：
  • 通过union 连接两个表，返回列数必须相同
  • 如果列名称不相同，则以第一个SQL列名为准
  • union all 会保留重复数据
  • 使用union 进行连接SQL语句，单个子句不用order by，最后写order by
mysql> select * from sin1
    -> union
    -> select * from sin2;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from sin1
    -> union all
    -> select * from sin2;
+------+
| a    |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

13) MySQL 高级特性

1. 视图

create view <视图名称> as select * from <表名称> where <条件>;

  • 视图存在的一个虚拟表，也由行和列组成。视图是不存在于数据中的。根据视图来动态获取数据表中数据。
  • 作用：
  • 增加查询效率，减少性能的开销
  • 特点：
  • 本身是不保存数据
  • 视图是可以被当成一张表使用。

修改：alter view <视图名称> as select * from 表名称 where 条件;

删除：drop view <视图的名称>;

mysql> create view view_a as select * from sin1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from view_a;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> alter view view_a as select * from sin2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_a;
+------+
| b    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

mysql> drop view view_a;
Query OK, 0 rows affected (0.00 sec)

2. 存储过程

  • 存储过程是一种数据库对象，为了实现某个特定的任务，编译的一组SQL语句以一个存储单元的形式存储在服务器上，供用户调用。
# 创建结构
create procedure <存储过程名称>()
begin
     -- 代码，来进行数据的处理
end
# 调用存储过程
call  <存储过程名称>()
# 删除存储过程
drop procedure <存储过程名称>
⑴ 变量
用户变量
  • 定义：以@ 开头，形式 【@变量名称】
  • 作用范围：本次连接生效，如果用户断开连接，则用户变量不生效。
create procedure a()
begin
	set @x=1;
end;

call a();
Query OK, 0 rows affected (0.01 sec)

mysql> select @x;
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
系统变量
  • 全局变量
  • 在mysql 启动的时候，服务器会将全局变量初始化为默认值，全局变量的默认值通过mysql的配置更改（my.ini）.
  • 作用范围：在服务重启后生效。
show global variables;
# 修改全局变量
mysql> set @@global.auto_increment_offset = 1;
Query OK, 0 rows affected (0.00 sec)
#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 会话变量
  • 每次新建一个连接，会将全局变量复制一份，作为会话变量。
  • 作用范围：当前会话生效（连接）
show session variables;

# 修改会话变量
mysql> set @@session.auto_increment_offset = 1;
Query OK, 0 rows affected (0.00 sec)
#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 局部变量
  • 定义在SQL语句块中。如：存储过程，或函数中
  • 作用范围：代码开始和结束之间生效。
# 定义变量
declare <变量名称>  数据类型
# 给变量赋值
set  <变量名称>= 值
select 字段名称 into <变量名称>  from 表名 where 条件;

# 例：
create procedure a()
begin
	declare x varchar(20);
	select sname into x from student where sno = 101; 
	select x;
end;

mysql> call a();
+------+
| x    |
+------+
| 李军 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
(2) 控制流程
if

语法：

if 条件1 then 内容1; – 如果满足条件1 ，则执行内容1
elseif 条件2 then 内容2; – 如果不满足条件1，但是满足条件2，则执行内容2
else 内容3; – 如果不满足条件1，也不满足条件2，则 执行内容3
end if;

# 如果年龄小于18，输出"小学生"，如果小于60，输出"成年人"，否则就是老人

create procedure a()
begin
    -- 定义变量
    declare local_age int;
    -- 给变量赋值
    select timestampdiff(year,sbirthday,now()) into local_age from student where sno=101;
    -- 判断年龄
    if local_age < 18 then  select "小学生";
    elseif local_age < 60 then select "成年人";
    else select "老年人";
    end if;
end;

mysql> call a();
+--------+
| 成年人 |
+--------+
| 成年人 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select * from student where sno = 101;
+-----+-------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday           | Class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
+-----+-------+------+---------------------+-------+
1 row in set (0.00 sec)
case

语法
case
when 条件1 then 执行的代码1; – 当满足条件1时，则执行代码1
when 条件2 then 执行的代码2; – 当满足条件2时，则执行代码2
else 执行的代码3; – 否则执行代码3;

end case;

create procedure a()
begin
    -- 定义变量
    declare c_sage int;
    -- 给变量赋值
    select timestampdiff(year,sbirthday,now()) into c_sage from student where sno=101;
    -- 判断年龄
    case 
            when c_sage <18 then select "小学生";
            when c_sage >= 18 and c_sage <60 then select "成年人";
            else select "老人";
    end case;
end;
call a();
while

语法
while 条件1 do – 当满足条件1的时候则，循环执行代码1,直到不满足条件1.
代码1
end while;

# 循环输出1到10之间的数

create procedure a()
begin
    -- 定义变量
    declare i int default 1;
    -- 循环输出i 的值
    while i <=10 do
        select i;
        set i=i+1;
    end while;        
end;

mysql> call a();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    5 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    6 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    7 |
+------+
1 row in set (0.05 sec)

+------+
| i    |
+------+
|    8 |
+------+
1 row in set (0.06 sec)

+------+
| i    |
+------+
|    9 |
+------+
1 row in set (0.07 sec)

+------+
| i    |
+------+
|   10 |
+------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.08 sec)
repeat

语法

repeat
代码1;
until 条件1 – 直到满足条件1，则停止循环
end repeat [循环名称];

create procedure a()
begin
	declare i int default 1;
	repeat
					select i;
					set i=i+1;
	 until i > 10 
	end repeat;    
end;

mysql> call a();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.02 sec)

+------+
| i    |
+------+
|    5 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    6 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    7 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    8 |
+------+
1 row in set (0.05 sec)

+------+
| i    |
+------+
|    9 |
+------+
1 row in set (0.06 sec)

+------+
| i    |
+------+
|   10 |
+------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)
跳出循环
  • 跳出本次循环：iterate
  • 跳出整个循环：leave
# 当i = 5时，跳出本次循环
drop procedure a;
create procedure a()
begin
    -- 定义变量
    declare i int default 0;
    -- 循环输出i 的值
    w:while i < 10 do    
    set i=i+1;
        # 先判断 i 是否等于5,如果等于5 则，跳出本次循环，继续下一次循环
        if i=5 then iterate w;
        end if;
        # 如果 不等于5 则输出i，并且给i+1
    select i;    
  end while w;        
end;

mysql> call a();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    6 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    7 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    8 |
+------+
1 row in set (0.05 sec)

+------+
| i    |
+------+
|    9 |
+------+
1 row in set (0.05 sec)

+------+
| i    |
+------+
|   10 |
+------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.07 sec)
# 当i = 5时，跳出整个循环
create procedure b()
begin
	-- 定义变量
	declare i int default 0;
	-- 循环输出i 的值
	w:while i < 10 do
		set i=i+1;
		select i;
		if i=5 then leave w;
      	end if;
      end while w;        
end;

mysql> call b();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    5 |
+------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

3. 触发器

trigger
  • 触发器：保证数据完整性的一种方法。触发器不是手工启动，与表的事件相关联的。（insert，delete, update）
  • 关键词：
  • new ： 代表将要插入的或者更新的数据。行
  • old ： 代表 更新之前的一行数据，或者删除的一行数据。
  • for each row : 对每行起作用
  • before 和 after ： 在操作之前，在操作之后
  • 操作种类： insert ，update，delete
# 语法：

# 创建一个触发器
create trigger  <触发器名称>  before | after  <操作种类> on 表名称 for each row 
begin
  -- 当发生上面的操作时，需要执行的代码
end
# 查看触发器
show trigger
# 删除触发器
drop trigger <触发器名称>

# 例：
# 给学生表插入数据，检查性别数据是否合理，如果不为男或不为女，则默认男
create trigger tri_in_sex  before insert on student for each row 
begin
    if new.ssex !='男' and new.ssex !='女' then set new.ssex='男';
    end if;
end;
insert into student(sno,sname,ssex) values(2,'lily','未知'),(3,'lily','未知'),(4,'lily','未知');

mysql> select * from student;
+-----+-------+------+-----------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+-----------+-------+
| 2   | lily  | 男   | NULL      | NULL  |
| 3   | lily  | 男   | NULL      | NULL  |
| 4   | lily  | 男   | NULL      | NULL  |
+-----+-------+------+-----------+-------+
3 rows in set (0.00 sec)

# 删除某条数据时，想备份表中插入一条数据
create table student_copy like student;
select * from student_copy;
create trigger student_copy_str before delete on student for each row 
begin
    insert into student_copy
    values(old.sno,old.sname,old.ssex);
end;
select * from student;
delete from student where sno = 101;
mysql> select * from student_copy;
+-----+-------+------+
| Sno | Sname | Ssex |
+-----+-------+------+
| 101 | 李军  | 男   |
+-----+-------+------+
1 row in set (0.00 sec)

4. 游标

cursor
  • 提供了一种从表中检索数据的灵活手段。可以逐行提取数据。
  • 游标：必须要写函数或者存储过程中。解决了返回多个值的问题。
# 声明游标
declare  <游标名称> cursor for select 字段名称 from 表名称;
# 打开游标
open  <游标名称>
# 取值 ： 
fetch  <游标名称> into  变量1,变量2……;
# 关闭游标
close <游标>;

# 异常处理： 如果找不到，设置真，用于退出循环使用。
declare continue handler for not found set flg = true;
# 创建游标存储所有男生的姓名和年龄

create procedure pro_cursor()
begin
declare vname varchar(20);
declare vsno varchar(20);
declare flg int default false;
# 定义游标，保存姓名
declare name_cur cursor  for select sname from student where ssex='男';
# 定义游标，保存学号
declare sno_cur cursor  for select sno from student where ssex="男";
# 异常处理
declare continue handler for not found set flg=true;
# 开启游标
open name_cur;
open sno_cur;
-- 取值
outt:while true do
	fetch name_cur into vname;
	fetch sno_cur into vsno;
	if flg then leave outt;
	else 
					select vname,vsno;
	end if;
end while outt;
-- 关闭游标
close name_cur;
close sno_cur;
end;

mysql> call pro_cursor();
+-------+------+
| vname | vsno |
+-------+------+
| lily  | 2    |
+-------+------+
1 row in set (0.04 sec)

+-------+------+
| vname | vsno |
+-------+------+
| lily  | 3    |
+-------+------+
1 row in set (0.04 sec)

+-------+------+
| vname | vsno |
+-------+------+
| lily  | 4    |
+-------+------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

5. 索引

index
  • 索引：帮助mysql高效获取数据的数据结构（有序）。比如：按拼音的字母，按部首，按笔画。
  • 特点：
  • 索引也是一张表，保存了主键与索引的字段，并指向实体表的记录。
  • 索引占用磁盘空间。
  • 创建索引时，需要确保该索引时应用SQL查询语句条件，where 条件（sname=’张三’）
  • 虽然索引提高了查询速度，但是同时也会降低更新表的速度。
  • 分类：
  • 单列索引：一个索引只包含一列。一个表可以有多个单列索引。
  • 组合索引：一个索引里包含多个列。
# 创建表时添加索引
create table <表名称>(
字段名称 int primary key;
字段名称 varchar(20),
index <索引名称>(字段名称)
);

# 创建索引
create index <索引名称> on <表名称>(字段名称);

# 修改表索引
alter table <表名称> add index <索引名称>(字段名称);

# 删除索引
drop index 索引名称 on <表名称>;
  • 例：
# 创建表时添加索引
create table student(
sno int primary key,
sname varchar(20),
ssex varchar(10),
index index_sno(sno)
);

# 创建索引
mysql> create index index_sno on student(sno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改表索引
mysql> alter table student add index index_sno(sno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除索引
mysql> drop index index_sno on student;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0