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 : 对每行起作用
  • beforeafter : 在操作之前,在操作之后
  • 操作种类: 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