数据库mysql的常规操作-低调大师

数据库mysql的常规操作

2018-02-03 536

　　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)默认就是索引。

微信关注我们

原文链接：https://yq.aliyun.com/articles/620435

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

ubuntu安装gooderp终极指南

概述我承认以前的centos安装上有错误，这次，我重新记录一下安装过程注意，我使用pyenv来管理python的版本安装开始环境是ubuntu16.04阿里云的ecs首先更新下系统sudo apt update && sudo apt upgrade接着移除旧内核sudo apt autoremove因为ubuntu是不用重启就可以安装好内核的，所以你可以不重启新建gooderp用户首先新建一个用户来管理gooderpgroupadd gooderpuseradd -g gooderp gooderppasswd gooderp接着让gooderp用户可以使用sudo命令visudo在这个文件中添加gooderp ALL=(ALL:ALL) ALL之后切换成gooderp用户su gooderp 下载源码包首先安装gitsudo apt install git在/home下新建gooderp目录sudo mkdir /home/gooderp修改目录所有者sudo chown -Rf gooderp:gooderp /home/gooderp接着切换到goo...

2018-02-04

667

python 第三方库升级方法

网上查到的批量升级方法，我的电脑上无法实现，只能根据自己实际情况来：查找可以升级的库：pip3 list --outdated 升级对应的库：pip3 install --upgrade tensorflow 只能一个一个升级

2018-02-04

638

发表评论

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。