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

数据库mysql的常规操作

2018-02-04 1458
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 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 版操作
        

        

            数据库操作
        

    



                            
                        

                        

    
相关实践学习

    

        

            
如何在云端创建MySQL数据库

            

                
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。

                
            

        

        

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

            

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

                
            

        

    



                        
                    
                

                


                

                    

                        

                            
                                
                            
                            

                        

                        码农47
                        

                        

                    

                    

                        

                            

                            
目录

                        

                        

                    

                

                

                    

                


                

    
相关文章

    

        

            

                

                    听风de歌
                    
                

                |
                
7天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        go语言数据库中mysql驱动安装
                    
                    
【11月更文挑战第2天】

                    

                        

                            

                                听风de歌
                                
                            

                            20
                            4
                            
                        

                    

                

                
            

        

        

            

                

                    路卿老师
                    
                

                |
                
4天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        12 PHP配置数据库MySQL
                    
                    
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。

                    

                        

                            

                                路卿老师
                                
                            

                            13
                            1
                            
                        

                    

                

                
            

        

        

            

                

                    土木林森
                    
                

                |
                
13天前

                |
                

                    监控
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        数据库优化：MySQL索引策略与查询性能调优实战
                    
                    
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。

                    

                        

                            

                                土木林森
                                
                            

                            70
                            1
                            
                        

                    

                

                
            

        

        

            

                

                    众所周知
                    
                

                |
                
16天前

                |
                

                    关系型数据库
                    MySQL
                    Linux
                

            

            

                

                    
                        在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤，包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
                    
                    
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤，包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时，文章还对比了编译源码安装与使用 RPM 包安装的优缺点，帮助读者根据需求选择最合适的方法。通过具体案例，展示了编译源码安装的灵活性和定制性。

                    

                        

                            

                                众所周知
                                
                            

                            59
                            2
                            
                        

                    

                

                
            

        

        

            

                

                    众所周知
                    
                

                |
                
19天前

                |
                

                    存储
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL vs. PostgreSQL：选择适合你的开源数据库
                    
                    
在众多开源数据库中，MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而，它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较，以帮助您选择更适合您需求的开源数据库。

                    

                        

                            

                                众所周知
                                
                            

                            74
                            4
                            
                        

                    

                

                
            

        

        

            

                

                    小陈运维
                    
                

                |
                
1天前

                |
                

                    运维
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        安装MySQL8数据库
                    
                    
本文介绍了MySQL的不同版本及其特点，并详细描述了如何通过Yum源安装MySQL 8.4社区版，包括配置Yum源、安装MySQL、启动服务、设置开机自启动、修改root用户密码以及设置远程登录等步骤。最后还提供了测试连接的方法。适用于初学者和运维人员。

                    

                        

                            

                                小陈运维
                                
                            

                            16
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    土木林森
                    
                

                |
                
14天前

                |
                

                    监控
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        数据库优化：MySQL索引策略与查询性能调优实战
                    
                    
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。

                    

                        

                            

                                土木林森
                                
                            

                            45
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    SQL
                    Java
                    数据库连接
                

            

            

                

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

                    

                        

                            

                                谙忆
                                
                            

                            188
                            0
                            
                        

                    

                

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

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

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

                    

                        

                            

                                谙忆
                                
                            

                            139
                            0
                            
                        

                    

                

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

        

        

            

                

                    谙忆
                    
                

                
                
                |
                

                    存储
                    SQL
                    关系型数据库
                

            

            

                

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

                    

                        

                            

                                谙忆
                                
                            

                            138
                            0
                            
                        

                    

                

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

        

    



            

            

                
                
                    
                    

    

        

            
热门文章

        

        

            
最新文章

        

    

    

        

            

                
  • 
                    
                        
    
                            
    1
    
                            
                            
    Myeclipse 操作数据库
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    SQLServer数据库日志清理 清除sqlserver2005日志 - sk
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    数据库查询返回空的原因
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    Java开发数据库设计的14个技巧，你知道几个？
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    服务器增加内存后无法重启数据库的问题及解决
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    国产化人大金仓数据库转库工具：oracle12c数据库转kingbase8.6人大金仓数据库实例演示
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    Android Sqlite数据库版本升级管理初探
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    Sqlserver与access数据库sql语法十大差异
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    使用xtrabackup备份innodb引擎的数据库
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    数据库安全之权限与角色
    
                            
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
        
    
            
    
                
  • 
                    
                        
    
                            
    1
    
                            
                            
    中间件MySQL性能瓶颈
    
                            
    55
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    
                            
    267
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    MySQL上亿数据查询优化：实践与技巧
    
                            
    286
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    
                            
    49
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    MySQL：union all与union区别详解
    
                            
    87
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    学习mysql中使用inner join，left join 等
    
                            
    48
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    【最佳实践】MySQL数据库迁移到PXC集群
    
                            
    91
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    MySQL PXC集群配置IPv6
    
                            
    49
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    【实操记录】MySQL主从配置
    
                            
    138
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    【实操记录】MySQL二进制安装包部署
    
                            
    187
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
    
    

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

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

    
                    
    
    
    
        
    相关实验场景
    
        更多
    
    
    
    
        
  • 
            数据库实验室挑战任务-高级任务
        
    • 
        
  • 
            数据库实验室挑战任务-中级任务
        
    • 
        
  • 
            数据库实验室挑战任务-初级任务
        
    • 
        
  • 
            阿里云数据库产品实验室
        
    • 
        
  • 
            MongoDB数据库入门
        
    • 
        
  • 
            使用DAS实现数据库自动扩容和回缩
        
    • 
    
    

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

    
                    
    
                        
    
    
    
        
    下一篇
    
    
    
    阿里云OSS设置跨域访问