AI 助理
备案控制台
开发者社区 数据库 文章 正文

数据库mysql的常规操作

2018-02-04 1464
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 　　1. 什么是数据库？　　　　数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。　　　　简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。

　　1. 什么是数据库？

　　　　数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

　　　　简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。

　　　　严格来说，数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。

　　2.  为什么需要使用数据库？

　　　　 假设这样一个场景，需要存储一所学校所有学生的姓名，性别，出生日期和各科成绩，是用Word文档来存，还是使用Excel表格？相信一个正常人都会选择Excel表格；当然，使用Word文档也能实现这一需求；使用Word文档实现存储这一需求当然没有任何问题，但是如果某个学生来要查询自己的学生，或者需要修改某个学生的成绩，给这些学生按成绩排名等这些需求，如果需要再扩展一下存储内容，比如再存一下每个学生的联系方式，那使用Word就几乎不可能实现了。同样的，在程序中所需要存储的各种信息会比上面的场景更加复杂，使用一种有结构，有规律的数据存储方式就非常的有必要了。

　　3. 如何管理操作数据库？

　　　　我们可以使用一种叫做数据库管理系统(DBMS)的软件来操作数据库。

　　　　数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。

　　　　常见的数据库管理系统有：Oracle数据库、DB2、MySQL、Informix、SQL Server等。

　　4. 如何使用数据库管理系统(DBMS)？

　　　　一般我们使用SQL语言与DBMS交互来操作关系型数据库（一般日常使用的MySQL、SQL Server、Oracle数据库都是关系型数据库）。

　　　　SQL语言在这些数据库管理系统中基本通用。不能说完全通用是因为不同的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。（基本上能在一种数据库管理系统中熟练的使用SQL，迁移到其他系统其实很简单）

　　　　

　　在本篇博客中，就详细介绍一下如何在mysql中使用SQL语言实现常规操作。

　　首先介绍一下mysql中的数据类型（可以先略过，用到时再来查看）：

　　参考自（http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh）

　　MySQL有多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

　　(1) 数值类型：

　　MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

　　BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

　　作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

 　　注：MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

　　(2)日期/时间类型：

　　表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

类型 大小
(字节) 		范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳


　　(3)字符串类型：

　　字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

　　CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

　　CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。

　　CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

　　VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

　　同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

　　注：BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

　　　　BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

　　　　有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

例一（显示一下当前系统中有哪些数据库）：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

　　注1：使用"show databases;"命令可以显示当前系统中有哪些数据库。

　　注2: 在mysql中，sql语句的结束必须使用";"。

　　注3: SQL语言不区分大小写。

例二（进入某个数据库并显示这个数据库中都有哪些表）：

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A              |
| B              |
| cats           |
| users          |
+----------------+
4 rows in set (0.00 sec)

　　注："use test;"命令，进入了test数据库；"show tables;"命令，显示了test数据库中的表。

例三（显示表的结构）：

mysql> desc users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

　　注1：使用"desc users;"命令显示了users这张表的结构；

　　注2: 上面这张user表的结构如下表所示： 有id（存储11为的int类型数据，而且id是自增的），emali（存储255位的varchar类型数据），password（同前）三个字段，相等于Excel表格中的三列（mysql中的数据类型在上面有介绍），id，emial，password即为这张表的字段。

例四（查询某张表中数据）：

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | very-secret |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
|  7 | xiaoli@123.com       | simple      |
|  8 | xiangwang@123.com    | simple      |
|  9 | xiaohong@123.com     | very-secret |
+----+----------------------+-------------+
9 rows in set (0.00 sec)

　　注1：使用"select * form users"查询user表中的所有数据，*代表user表中的所有字段。

 

mysql> select * from users where users.password='simple';
+----+-------------------+----------+
| id | email             | password |
+----+-------------------+----------+
|  4 | xiaoming@123.com  | simple   |
|  5 | xiaoqiang@123.com | simple   |
|  7 | xiaoli@123.com    | simple   |
|  8 | xiangwang@123.com | simple   |
+----+-------------------+----------+
4 rows in set (0.01 sec)

　　注2：使用" select * form users where users.password='simple' "命令就可以查询users表中所有password为simple的数据(在where后面加条件即可)。

 

mysql> select users.email from users;
+----------------------+
| email                |
+----------------------+
| webmaster@python.org |
| webmaster@python.org |
| webmaster@python.org |
| xiaoming@123.com     |
| xiaoqiang@123.com    |
| xiaozhang@123.com    |
| xiaoli@123.com       |
| xiangwang@123.com    |
| xiaohong@123.com     |
+----------------------+
9 rows in set (0.00 sec)

　　注3: 如果只想查看某一字段的数据，只需使用"select tablename.x from tablename"即可。

 

# 查询条件为password='simple'， 只显示users.email字段
mysql> select users.email from users where password='simple';
+-------------------+
| email             |
+-------------------+
| xiaoming@123.com  |
| xiaoqiang@123.com |
| xiaoli@123.com    |
| xiangwang@123.com |
+-------------------+
4 rows in set (0.00 sec)

　　注4: 查询字段与查询条件没有关系。

 

例五（将下面数据插入表中）：

　

mysql> insert into users (email,password) values ('abc@xyz.com','very-simple');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users where password='very-simple';
+----+-------------+-------------+
| id | email       | password    |
+----+-------------+-------------+
| 10 | abc@xyz.com | very-simple |
+----+-------------+-------------+
1 row in set (0.00 sec)

　　注1：在例三中，我们知道id是自增的，所以不用人为指定，只需指定其他两个字段的值。

　　注2: 向表中插入数据的语法如下：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

例六（修改表中的数据）：

　　修改表中数据的语法如下。

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

　　注意例四，我们将id为2的password改为"hello word"。

mysql> update users set password='hello word' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users where id=2;
+----+----------------------+------------+
| id | email                | password   |
+----+----------------------+------------+
|  2 | webmaster@python.org | hello word |
+----+----------------------+------------+
1 row in set (0.00 sec)

例七（删除表中的数据）：

　　删除表中数据的语法。

DELETE FROM table_name [WHERE Clause]

　　删除users表中id>6的数据。

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | hello word  |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
|  7 | xiaoli@123.com       | simple      |
|  8 | xiangwang@123.com    | simple      |
|  9 | xiaohong@123.com     | very-secret |
| 10 | abc@xyz.com          | very-simple |
+----+----------------------+-------------+
10 rows in set (0.00 sec)

mysql> delete from users where id>6;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | hello word  |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
+----+----------------------+-------------+
6 rows in set (0.00 sec)

  　　上面的例子都是介绍对数据库表中数据的操作，接下来就来了解一下对表及数据库本身的操作。

例八（创建数据库）：

#创建数据库语法
CREATE DATABASE DABASENAME;
# 创建数据库
mysql> create database mysql_test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

 例九（在数据库中创建表）：

创建一个如下结构的student表:

　　字段stu_id可存储的类型为11位int（不能为空），字段name可存储的类型为32位char（不能为空），字段age可存储的类型为11位int（不能为空），字段register_data可存储的类型为date。其中stu_id为主键，并且是自增的。

#创建数据表语法
CREATE TABLE table_name (column_name column_type);
#进入刚才建立的数据库，建立student表
mysql> use mysql_test;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(
    -> stu_id int not null auto_increment,    #字段名，类型，属性
    -> name char(32) not null,
    -> age int not null,
    -> register_date date,
    -> primary key(stu_id)    # 设置某个字段为主键
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)

mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| stu_id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name          | char(32) | NO   |     | NULL    |                |
| age           | int(11)  | NO   |     | NULL    |                |
| register_date | date     | YES  |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

　　

　　使用alter修改表结构

# 修改表名称
ALTER TABLE tablename RENAME TO new_tablename;

# 删除表的某个字段
alter table tablename drop fieldname; 

# 向表中添加字段
alter table tablename add fieldname int(11) not null;

# 修改字段类型，把字段 c 的类型从 CHAR(4) 改为 CHAR(8)
ALTER TABLE tablename MODIFY c CHAR(8);

# change语句，CHANGE 关键字之后，紧跟着的是要修改的字段名，然后指定新字段名及类型。
# 修改字段名及类型
ALTER TABLE tablename CHANGE x y BIGINT;
# 修改字段类型
ALTER TABLE testalter_tbl CHANGE x x INT;

 

例十（删除数据库即库中所有的表）：

# 取消数据库及数据库中的所有表语法 DROP DATABASE DATABASENAME;
 

 
 
# 取消数据库中某张表语法 DROP TABLE TABLENAME;
 

 

注： DROP DATABASE用于取消数据库中的所用表格和取消数据库。使用此语句时要非常小心！如果要使用DROP DATABASE，您需要获得数据库DROP权限。
 

 
 
# 删除刚创建的mysql_test数据库及库中的student表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> drop database mysql_test;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 

 

  例十一（在MySql中的外键关联）：
 

　　上面的例子所介绍的对数据和表的操作都是单张数据表内的，而MySql是关系型数据库，这代表它管理的是多张相互之间有关联的数据表。这里就需要介绍两个概念，主键(primary key)和外键(foreing key)。
 

                  
 

　　主键(primary key)：是每条记录的ID，在整张表中的值唯一（例如如上图两张表中每行最外面的行号）。
 

　　外键(foreing key)：用于同其他表中数据做关联，在整张表中的值可以不唯一（如上图，如果某个学生参加了补考，可以再向表中加入一次他的成绩）。例如上图学生成绩表就以stu_id与学生信息表做关联，它们对于同一个人有相同的取值，使用外键便可将这两张表中的数据关联起来。
 

　　注意：一个表的外键在另一个表中的字段值必须唯一（可以是主键）。
 

 
 
mysql> create table student_info(id int not null auto_increment, stu_id int not null unique, name varchar(32) not null, age int not null, gender enum('F', 'M'), primary key(id));
Query OK, 0 rows affected (0.03 sec)

mysql> desc student_info;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| stu_id | int(11)       | NO   | UNI | NULL    |                |
| name   | varchar(32)   | NO   |     | NULL    |                |
| age    | int(11)       | NO   |     | NULL    |                |
| gender | enum('F','M') | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> create table study_level(id int not null auto_increment, stu_id int not null, mathematics int not null, physics int not null, chemistry int not null, primary key(id), key fk_study (stu_id), constraint fk_study foreign key (stu_id) references student_info (stu_id));
Query OK, 0 rows affected (0.02 sec)

mysql> desc study_level;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| stu_id      | int(11) | NO   | MUL | NULL    |                |
| mathematics | int(11) | NO   |     | NULL    |                |
| physics     | int(11) | NO   |     | NULL    |                |
| chemistry   | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 

 

 　　注：在本例中，使用stu_id作为study_level表的外键，而stu_id在student_info中的值是唯一的。
 

 
 
# 向表中插入图中数据，插入数据的sql语法前面有介绍
mysql> select * from student_info;
+----+--------+----------+-----+--------+
| id | stu_id | name     | age | gender |
+----+--------+----------+-----+--------+
|  1 |  10001 | zhangsan |  16 | F      |
|  2 |  10002 | lisi     |  17 | M      |
|  3 |  10003 | wangwu   |  16 | M      |
|  4 |  10004 | zhouliu  |  15 | F      |
+----+--------+----------+-----+--------+
4 rows in set (0.00 sec)

mysql> select * from study_level;
+----+--------+-------------+---------+-----------+
| id | stu_id | mathematics | physics | chemistry |
+----+--------+-------------+---------+-----------+
|  1 |  10001 |          78 |      70 |        83 |
|  2 |  10002 |          87 |      85 |        92 |
|  3 |  10003 |          60 |      54 |        76 |
|  4 |  10004 |          52 |      46 |        44 |
+----+--------+-------------+---------+-----------+
4 rows in set (0.00 sec)
 

 

　　上面的示例还看不出来使用外键的好处，那就在下面来演示一下为什么需要使用外键。
 

 
 
# 外键对插入数据的约束
# 向study_level表再插入一条stu_id=10004的记录，没有任何问题
mysql> insert into study_level (stu_id, mathematics, physics, chemistry) values(10004, 65, 70, 63);
Query OK, 1 row affected (0.01 sec)

mysql> select * from study_level;                                               +----+--------+-------------+---------+-----------+
| id | stu_id | mathematics | physics | chemistry |
+----+--------+-------------+---------+-----------+
|  1 |  10001 |          78 |      70 |        83 |
|  2 |  10002 |          87 |      85 |        92 |
|  3 |  10003 |          60 |      54 |        76 |
|  4 |  10004 |          52 |      46 |        44 |
|  5 |  10004 |          65 |      70 |        63 |
+----+--------+-------------+---------+-----------+
5 rows in set (0.00 sec)

# 向study_level表插入一条stu_id=10010的记录，由于在student_info表中没有关于这个值的记录，所以会出错，这就是外键约束
mysql> insert into study_level (stu_id, mathematics, physics, chemistry) values(10010, 65, 70, 63);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`study_level`, CONSTRAINT `fk_study` FOREIGN KEY (`stu_id`) REFERENCES `student_info` (`stu_id`))
 

 

 
 
# 外键对于删除数据的约束
# 删除study_level表中stu_id=10004的数据，没有任何问题
mysql>  delete from study_level where stu_id=10004;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from study_level;
+----+--------+-------------+---------+-----------+
| id | stu_id | mathematics | physics | chemistry |
+----+--------+-------------+---------+-----------+
|  1 |  10001 |          78 |      70 |        83 |
|  2 |  10002 |          87 |      85 |        92 |
|  3 |  10003 |          60 |      54 |        76 |
+----+--------+-------------+---------+-----------+
3 rows in set (0.01 sec)

# 删除student_info表中stu_id=10002的数据，由于study_level表中有数据关联了这条数据，所以无法删除
mysql> delete from student_info where stu_id=10002;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`study_level`, CONSTRAINT `fk_study` FOREIGN KEY (`stu_id`) REFERENCES `student_info` (`stu_id`))
 

 

 例十二（mysql中多表的连接查询）：
 

　　上面示例介绍的在表中查询数据都是在一张表中，但在实际生产过程中多表的跨表查询才是最常见的。那么在本例中将介绍一下在mysql中使用join来进行连接查询。
 

　　常用的连接有一下几种：
 

　　　　INNER JOIN（内连接,或等值连接）、LEFT JOIN（左外连接）、RIGHT JOIN（右外连接）、FULL JOIN（全外连接）。
 

　　我们先来看一下两表的结构，为了便于理解都是非常简单的表。
 

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

mysql> select * from B;
+---+
| b |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
5 rows in set (0.00 sec)　
 

 

　　
 

　　INNER JOIN（内连接,或等值连接）：指使用等号"="比较两个表的连接列的值，相当于两表执行笛卡尔后，取两表连结列值相等的记录。
 

　　说明：通俗来讲，相当于取两表连接列内容的交集。
 

 
 
mysql> select * from A inner join B on A.a = B.b;
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
2 rows in set (0.00 sec)
 

 

 
 

　　LEFT JOIN（左外连接）：指将左表的所有记录与右表符合条件的记录，返回的结果除内连接的结果，还有左表不符合条件的记录，并在右表相应列中填NULL。
 

　　说明：比如对于表A和B，结果显示A，A∩B。
 

 
 
mysql> select * from A left join B on A.a = B.b;
+---+------+
| a | b    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 1 | NULL |
| 2 | NULL |
+---+------+
 

 

 
 

　　RIGHT JOIN（右外连接）：与左外连接相反，指将右表的所有记录与左表符合条件的记录，返回的结果除内连接的结果，还有右表不符合条件的记录，并在左表相应列中填NULL。
 

　　说明：比如对于表A和B，结果显示A∩B，B。
 

 
 
mysql> select * from A right join B on A.a = B.b;
+------+---+
| a    | b |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+---+
5 rows in set (0.00 sec)
 

 

　　
 

　　FULL JOIN（全外连接）：指将左表所有记录与右表所有记录进行连接，返回的结果除内连接的结果，还有左表与右表不符合条件的记录，并在左表与右表相应列中填NULL。
 

　　说明：通俗来讲，相当于取两表连接列内容的并集。
 

 
 
mysql> select * from A left join B on A.a = B.b UNION select * from A right join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
| NULL |    7 |
+------+------+
7 rows in set (0.00 sec)
 

 

 　　注：由于mysql并不直接支持full join，所以不能使用select * from A full join B on A.a = B.b来进行full join查询。
 

 
 

例十二（mysql中的事务）：
 

　　在数据库管理系统中，事务是一组不可被分割执行的SQL语句集合，如果有必要，可以撤销。一个最简单的例子，银行账号之间的转账，A--->B转账10000元，要在数据库中完成这个操作，必须得实现两个操作，A的账户余额减少10000元，B的账户余额增加10000元（这两个操作缺一不可），但如果完成了第一个操作（即A的账户减少10000元，而B的账户还未增加）之后数据库管理系统出现异常，比如出现服务器宕机，程序异常终止等这些无法预估的意外，这时重启了系统我们就希望能恢复到最开始的状态（即A的账户未减少，B的账户未增加）。这时使用事务就非常的有必要。
 

　　mysql的事务支持与服务器本身无关，只与使用的存储引擎有关，在mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务。
 

　　事务处理用来维护数据库的完整性，保证一组SQL语句要么全部执行，要么全部不执行。
 

　　事务的特点ACID:
 

　　　　事务的原子性(Atomicity)：一组事务，要么成功；要么撤回。
 

　　　　稳定性(Consistency) ： 有非法数据（外键约束之类），事务撤回。
 

　　　　隔离性(Isolation)：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
 

　　　　可靠性(Durability)：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
 

　　
 

　　使用事务的步骤：
 

　　　　1. 开启事务start transaction，可写为 begin。 
　　　　2. 然后记录需要执行的一组sql。 
　　　　3. 提交commit。 
　　　　4. 如果所有的sql都执行成功，则提交(commit)，将sql的执行结果持久化到数据表内，否则回滚(rollback)。
　　注意：　　　
　　　　a. 无论回滚还是提交，都会关闭事务！需要再次开启，才能使用。 提交(commit)后，便不能再回滚(rollback)。
　　　　b. 还有一点需要注意，就是事务只针对当前连接。
 

　　说明：提交与回滚相当与word中的保存与撤销。
 

 
 
# 查寻A表所有数据
mysql> select * from a;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.01 sec)

# 开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 插入数据
mysql> insert into A (a) values(666);
Query OK, 1 row affected (0.01 sec)
# 查询，数据已加入A表，但还未持久化
mysql> select * from a;
+-----+
| a   |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
| 666 |
+-----+
5 rows in set (0.00 sec)
# 回滚
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
# 再次查询，因为回滚，刚才的操作被撤销
mysql> select * from a;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)
# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 插入数据
mysql> insert into A (a) values(777);
Query OK, 1 row affected (0.00 sec)
# 提交，将刚刚的操作持久化
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查询
mysql> select * from a;
+-----+
| a   |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
| 777 |
+-----+
5 rows in set (0.01 sec)
 

 

 
 

例十三（索引）：
 

　　为了在大量数据中快速实现查找功能，使用索引是非常有必要的。那么，
 

　　在mysql中索引是什么？
 

　　　　在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
 

　　使用索引的利与弊：
 

　　　　使用索引能极大提高数据检索的效率。但是，索引也是一张表（该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间），更新数据表时，MySQL不仅要保存数据，还要保存索引文件，索引在大大提高查询速度的同时会降低更新表的速度（对表进行增，改，删操作的速率会降低很多，尤其是在数据量很大的时）。
 

　　索引的类型：　　　　
 

　　　　单值索引（单列索引）：即一个索引只包含单个列，一个表可以有多个单列索引。
 

　　　　唯一索引：索引列的值必须唯一，但允许有空值。　　
 

　　　　复合索引：即一个索引包含多个列。
 

 
 
# 创建索引sql语句
# 创建索引
CREATE INDEX indexName ON tablename(fieldname(length)); 
# 创建唯一索引
CREATE UNIQUE INDEX indexName ON tablename(fieldname(length));
 

 

 
 
# 使用修改表结构的方法添加索引
ALTER tablename ADD INDEX [indexName] ON (fieldname(length))
# 使用修改表结构的方法添加唯一索引
ALTER tablename ADD UNIQUE [indexName] ON (fieldname(length))
 

 

　　注1：本文对mysql索引的只是一个简单的介绍，若要详细了解索引请参阅mysql官方文档。
 

　　注2：一个表的主键(primary key)默认就是索引。
 

 

                        
                    

                    
                        

                            

    
文章标签：

    

        

            云数据库 RDS MySQL 版
        

        

            关系型数据库
        

        

            索引
        

        

            数据库
        

        

            存储
        

        

            SQL
        

        

            数据库管理
        

        

            MySQL
        

        

            Oracle
        

        

            API
        

        

            算法
        

    



                            

    
关键词：

    

        

            云数据库 RDS MySQL 版数据库
        

        

            数据库mysql
        

        

            云数据库 RDS MySQL 版操作
        

        

            数据库操作
        

    



                            
                        

                        

    
相关实践学习

    

        

            
如何快速连接云数据库RDS MySQL

            

                
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。

                
            

        

        

            
全面了解阿里云能为你做什么

            

                
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs

                
            

        

    



                        
                    
                

                


                

                    

                        

                            
                                
                            
                            

                        

                        码农47
                        

                        

                    

                    

                        

                            

                            
目录

                        

                        

                    

                

                

                    

                


                

    
相关文章

    

        

            

                

                    栈江湖
                    
                

                |
                
4天前

                |
                

                    存储
                    Oracle
                    关系型数据库
                

            

            

                

                    
                        数据库传奇：MySQL创世之父的两千金My、Maria
                    
                    
《数据库传奇：MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建，现归Oracle所有，广泛应用于阿里巴巴、腾讯等企业。2009年，Widenius因担心Oracle收购影响MySQL的开源性，创建了MariaDB，提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB，以确保更好的性能和社区支持。掌握MariaDB作为备用方案，对未来发展至关重要。

                    

                        

                            

                                栈江湖
                                
                            

                            17
                            3
                            
                        

                    

                

                
            

        

        

            

                

                    栈江湖
                    
                

                |
                
4天前

                |
                

                    安全
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！
                    
                    
《MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！》介绍了MySQL中的三种关键日志：二进制日志（Binary Log）、重做日志（Redo Log）和撤销日志（Undo Log）。这些日志确保了数据库的ACID特性，即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改，保证事务持久性；Undo Log记录事务的逆操作，支持回滚和多版本并发控制（MVCC）。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异，强调了InnoDB在高并发和事务处理中的优势。通过这些机制，MySQL能够在事务执行、崩溃和恢复过程中保持

                    

                        

                            

                                栈江湖
                                
                            

                            21
                            3
                            
                        

                    

                

                
            

        

        

            

                

                    栈江湖
                    
                

                |
                
4天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
                    
                    
《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》介绍了如何利用MySQL的二进制日志（Binlog）恢复误删除的数据。主要内容包括：

1. **启用二进制日志**：在`my.cnf`中配置`log-bin`并重启MySQL服务。
2. **查看二进制日志文件**：使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。
3. **创建数据备份**：确保在恢复前已有备份，以防意外。
4. **导出二进制日志为SQL语句**：使用`mysqlbinlog`

                    

                        

                            

                                栈江湖
                                
                            

                            26
                            2
                            
                        

                    

                

                
            

        

        

            

                

                    AIweker
                    
                

                |
                
17天前

                |
                

                    关系型数据库
                    MySQL
                    数据库
                

            

            

                

                    
                        Python处理数据库：MySQL与SQLite详解 | python小知识
                    
                    
本文详细介绍了如何使用Python操作MySQL和SQLite数据库，包括安装必要的库、连接数据库、执行增删改查等基本操作，适合初学者快速上手。

                    

                        

                            

                                AIweker
                                
                            

                            124
                            15
                            
                        

                    

                

                
            

        

        

            

                

                    1436047922066202
                    
                

                |
                
11天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
                    
                    
Mysql数据库故障：
Mysql数据库表记录丢失。

Mysql数据库故障表现：
1、Mysql数据库表中无任何数据或只有部分数据。
2、客户端无法查询到完整的信息。

                    

                        

                            

                                1436047922066202
                                
                            

                            38
                            5
                            
                        

                    

                

                
            

        

        

            

                

                    1436047922066202
                    
                

                |
                
18天前

                |
                

                    关系型数据库
                    MySQL
                    数据库
                

            

            

                

                    
                        数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
                    
                    
mysql数据库文件ibdata1、MYI、MYD损坏。 
故障表现：1、数据库无法进行查询等操作；2、使用mysqlcheck和myisamchk无法修复数据库。

                    

                        

                            

                                1436047922066202
                                
                            

                            67
                            6
                            
                        

                    

                

                
            

        

        

            

                

                    Apache_Tomcat_Nignx
                    
                

                |
                
22天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL导入.sql文件后数据库乱码问题
                    
                    
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。

                    

                        

                            

                                Apache_Tomcat_Nignx
                                
                            

                            73
                            3
                            
                        

                    

                

                
            

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    SQL
                    Java
                    数据库连接
                

            

            

                

                    
                        MySQL---数据库从入门走向大神系列(十五)-Apache的DBUtils框架使用
                    
                    
MySQL---数据库从入门走向大神系列(十五)-Apache的DBUtils框架使用

                    

                        

                            

                                谙忆
                                
                            

                            196
                            0
                            
                        

                    

                

                
                    MySQL---数据库从入门走向大神系列(十五)-Apache的DBUtils框架使用
                
            

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔离(锁机制)
                    
                    
MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔离(锁机制)

                    

                        

                            

                                谙忆
                                
                            

                            146
                            0
                            
                        

                    

                

                
                    MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔离(锁机制)
                
            

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    存储
                    SQL
                    关系型数据库
                

            

            

                

                    
                        MySQL---数据库从入门走向大神系列(五)-存储过程
                    
                    
MySQL---数据库从入门走向大神系列(五)-存储过程

                    

                        

                            

                                谙忆
                                
                            

                            144
                            0
                            
                        

                    

                

                
                    MySQL---数据库从入门走向大神系列(五)-存储过程
                
            

        

    



            

            

                
                
                    
                    

    

        

            
热门文章

        

        

            
最新文章

        

    

    

        

            

                
  • 
                    
                        
    
                            
    1
    
                            
                            
    达梦数据库字段类型 varchar 转 text
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    使用 Spring Boot 执行数据库操作：全面指南
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    PolarDB 分布式版 V2.0，安全可靠的集中分布式一体化数据库管理软件
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    在云上轻松部署达梦数据库
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    活动实践 | 自建数据库迁移到云数据库
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    数据库数据恢复—ORACLE常见故障的数据恢复方案
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    数据库的行级锁与表锁？ 
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    自建数据库迁移到云数据库实操
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    数据库分库分表的原因？ 
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    InfluxDB vs TDengine ：2025 年了，谁家用的数据库还不能高效读缓存？
    
                            
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
        
    
            
    
                
  • 
                    
                        
    
                            
    1
    
                            
                            
    【MYSQL】 ——索引（B树B+树）、设计栈
    
                            
    9
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    【MySQL】——数据查询_进阶操作（超详细）！！
    
                            
    9
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    MySQL（条件约束）
    
                            
    9
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    带你认识MySQL（前置介绍）
    
                            
    16
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    【MySQL进阶篇】存储引擎（MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案）
    
                            
    32
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    【MySQL基础篇】事务（事务操作、事务四大特性、并发事务问题、事务隔离级别）
    
                            
    51
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    MySQL/SqlServer跨服务器增删改查（CRUD）的一种方法
    
                            
    25
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    【MySQL基础篇】MySQL约束语法
    
                            
    34
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    【MySQL基础篇】盘点MySQL常用四大类函数
    
                            
    13
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
    
                            
    13
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
    
    

    
                    
                    
    
    
    
        
    相关课程
    
        更多
    
    
    
    
        
  • 
                数据库的前世今生
            
    • 
        
  • 
                数据库核心概念
            
    • 
        
  • 
                从传统数据库到云数据库演进
            
    • 
        
  • 
                数据库常见问题排查
            
    • 
        
  • 
                数据库及SQL/MySQL基础
            
    • 
        
  • 
                高校精品课-西安交通大学 -数据库理论与技术
            
    • 
    
    

    
                    
    
    
    
        
    相关电子书
    
        更多
    
    
    
    
        
  • 
                DTCC 2022大会集锦《云原生一站式数据库技术与实践》
            
    • 
        
  • 
                阿里云瑶池数据库精要2022版
            
    • 
        
  • 
                2022 DTCC-阿里云一站式数据库上云最佳实践
            
    • 
    
    

    
                    
    
    
    
        
    相关实验场景
    
        更多
    
    
    
    
        
  • 
            使用DAS实现数据库SQL优化
        
    • 
        
  • 
            MySQL基础-学生管理系统数据库设计
        
    • 
        
  • 
            如何快速连接云数据库RDS MySQL
        
    • 
        
  • 
            MySQL数据库快速部署实践
        
    • 
        
  • 
            自建数据库迁移到云数据库
        
    • 
        
  • 
            MySQL引擎及架构优化
        
    • 
    
    

    
                    
                    
    
    
    
        
    推荐镜像
    
        更多
    
    
    
    
        
  • 
            mysql
        
    • 
        
  • 
            mariadb
        
    • 
        
  • 
            postgresql
        
    • 
    
    

    
                    
    
                        
    
    
    
        
    下一篇
    
    
    
    手把手教你白嫖阿里云服务器(免费领服务器)