备案控制台
探索云世界
开发者社区 数据库 文章 正文

数据库mysql的常规操作

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

        

            算法
        

    



                            

    
关键词：

    

        

            数据库mysql
        

        

            数据库操作
        

        

            云数据库 RDS MySQL 版操作
        

    



                            
                        

                        

    
相关实践学习

    

        

            
基于CentOS快速搭建LAMP环境

            

                
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。

                
            

        

        

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

            

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

                
            

        

    



                        
                    
                

                


                

                    

                        

                            
                                
                            
                            

                        

                        码农47
                        

                        

                    

                    

                        

                            

                            
目录

                        

                        

                    

                

                

                    

                


                

    
相关文章

    

        

            

                

                    技术蜜糖罐
                    
                

                |
                
15天前

                |
                

                    SQL
                    数据可视化
                    关系型数据库
                

            

            

                

                    
                        轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
                    
                    
轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）

                    

                        

                            

                                技术蜜糖罐
                                
                            

                            92
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    技术蜜糖罐
                    
                

                |
                
15天前

                |
                

                    存储
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
                    
                    
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）

                    

                        

                            

                                技术蜜糖罐
                                
                            

                            42
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    技术蜜糖罐
                    
                

                |
                
15天前

                |
                

                    关系型数据库
                    MySQL
                    数据库
                

            

            

                

                    
                        轻松入门MySQL：精准查询，巧用WHERE与HAVING，数据库查询如虎添翼（7）
                    
                    
轻松入门MySQL：精准查询，巧用WHERE与HAVING，数据库查询如虎添翼（7）

                    

                        

                            

                                技术蜜糖罐
                                
                            

                            27
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    Y2000104
                    
                

                |
                
11天前

                |
                

                    存储
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL基础入门：数据库操作全攻略
                    
                    
MySQL基础入门：数据库操作全攻略

                    

                        

                            

                                Y2000104
                                
                            

                            44
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    Y2000104
                    
                

                |
                
11天前

                |
                

                    关系型数据库
                    MySQL
                    数据库
                

            

            

                

                    
                        卸载云服务器上的 MySQL 数据库
                    
                    
卸载云服务器上的 MySQL 数据库

                    

                        

                            

                                Y2000104
                                
                            

                            30
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    认真学习的小雅兰.
                    
                

                |
                
1天前

                |
                

                    SQL
                    关系型数据库
                    MySQL
                

            

            

                

                    
                        MySQL环境搭建——“MySQL数据库”
                    
                    
MySQL环境搭建——“MySQL数据库”

                    

                        

                            

                                认真学习的小雅兰.
                                
                            

                            10
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    认真学习的小雅兰.
                    
                

                |
                
1天前

                |
                

                    SQL
                    NoSQL
                    关系型数据库
                

            

            

                

                    
                        初识MySQL数据库——“MySQL数据库”
                    
                    
初识MySQL数据库——“MySQL数据库”

                    

                        

                            

                                认真学习的小雅兰.
                                
                            

                            5
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    林太白
                    
                

                |
                
2天前

                |
                

                    SQL
                    JavaScript
                    API
                

            

            

                

                    
                        ❤Nodejs 第四章（操作本地数据库实现删除-源码地址已开放）
                    
                    
【4月更文挑战第4天】❤Nodejs 第四章（操作本地数据库实现删除-源码地址已开放）在Node.js中实现删除本地数据库记录的操作。首先尝试通过SQL删除ID为8的用户，然后编写`app.delete`路由处理程序，从请求体获取ID并执行删除。。最终成功删除用户并展示了数据库的更新结果。下一节将优化增删改查功能。

                    

                        

                            

                                林太白
                                
                            

                            15
                            1
                            
                        

                    

                

                
            

        

        

            

                

                    find_starshine
                    
                

                |
                
4天前

                |
                

                    关系型数据库
                    MySQL
                    数据库
                

            

            

                

                    
                        数据库基础（mysql）
                    
                    
数据库基础（mysql）


                    

                        

                            

                                find_starshine
                                
                            

                            8
                            0
                            
                        

                    

                

                
            

        

        

            

                

                    LightOf
                    
                

                |
                
4天前

                |
                

                    SQL
                    关系型数据库
                    数据库
                

            

            

                

                    
                        【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
                    
                    
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。

                    

                        

                            

                                LightOf
                                
                            

                            29
                            3
                            
                        

                    

                

                
            

        

    



            

            

                
                
                    
                    

    

        

            
热门文章

        

        

            
最新文章

        

    

    

        

            

                
  • 
                    
                        
    
                            
    1
    
                            
                            
    RocketMQ 流数据库解析：如何实现一体化流处理？
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    阿里云数据库Cassandra的产品价格
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言，助力业务平滑迁移
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    一文搞懂数据库中的“锁”（图文详解）
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    【软件设计师备考 专题 】数据仓库和分布式数据库基础知识
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    开发者视角看云原生数据库一体化技术趋势
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    【C/C++ 数据库 sqlite3】SQLite C语言API返回值深入解析
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    阿里云数据库 SelectDB 内核 Apache Doris 2.0.6 版本正式发布
    
                            
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    jdbc实现批量给多个表中更新数据（解析Excel表数据插入到数据库中）
    
                            
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
        
    
            
    
                
  • 
                    
                        
    
                            
    1
    
                            
                            
    [MySQL]事务原理之redo log,undo log
    
                            
    41
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    2
    
                            
                            
    [MySQL]SQL优化之索引的使用规则
    
                            
    35
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
    3
    
                            
                            
    [MySQL] SQL优化之性能分析
    
                            
    28
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    4
    
                            
    【mysql】—— 用户管理
    
                            
    31
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    5
    
                            
    【mysql】—— 视图
    
                            
    26
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    6
    
                            
    【mysql】—— 函数的基本介绍
    
                            
    25
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    7
    
                            
    【mysql】—— 表的增删改查
    
                            
    40
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    8
    
                            
    【mysql】—— 表的约束
    
                            
    29
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    9
    
                            
    【MYSQL】—— MySQL 在 Centos 7环境安装
    
                            
    43
    
                        
    
                        
                
    • 
                
  • 
                    
                        
    
                            
                            
    10
    
                            
    MySQL模糊查询二三事
    
                            
    24
    
                        
    
                        
                
    • 
            
    
            
    
                
            
    
        
    
    
    

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

    
                    
    
    
    
        
    相关电子书
    
        更多
    
    
    
    
        
  • 
                2022 DTCC-阿里云一站式数据库上云最佳实践
            
    • 
        
  • 
                云时代的数据库技术趋势
            
    • 
        
  • 
                超大型金融机构国产数据库全面迁移成功实践
            
    • 
    
    

    
                    
    
    
    
        
    相关实验场景
    
        更多
    
    
    
    
        
  • 
            测试场景（RDS无优惠）
        
    • 
        
  • 
            MySQL引擎及架构优化
        
    • 
        
  • 
            基于EBS部署高性能的MySQL服务
        
    • 
        
  • 
            使用Python完成RDS数据开发及分析
        
    • 
        
  • 
            PolarDB for AI：在数据库中通过SQL实现AI能力
        
    • 
        
  • 
            云原生HTAP数据库，让你的交易和分析一库搞定
        
    • 
    
    

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

    
                    
    
                        
    
    
    
        
    下一篇
    
    
    
    阿里云oss简介和使用流程

    
                        
    
                    
    
                
            
    
            
        
    
    
    


  
  
  
    
    
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4