mysql数据库的基本操作

1、基本常识：

表格组成：
   表头(header): 每一列的名称（第一行），由字段1，字段2，…字段n组成；
   列(col): 具有相同数据类型的数据的集合；
   行(row): 每一行用来描述某条记录的具体信息;值(value)：行的具体信息, 每个值必须与该列的数据类型相同；
   键(key)： 键的值在当前列中具有唯一性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

2、连接和管理mysql数据库

（1）命令行操作连接mysql
   如果我们要登录本机的 MySQL 数据库，只需要输入以下命令即可：

mysql -u root -p

   按回车确认, 如果安装正确且 MySQL 正在运行, 会得到以下响应:
   Enter password:
（2）使用python脚本连接mysql
   使用 mysql-connector，它是 MySQL 官方提供的驱动器。这样便连接了mysql数据库。注意：创建数据库连接的时候，如果直接连接某个数据库（可添加参数database=数据库名）。

import mysql.connector
mydb = mysql.connector.connect( 
	host="localhost", # 数据库主机地址 
	user="yourusername", # 数据库用户名 
	passwd="yourpassword" # 数据库密码 ) 

（3）管理mysql：
   连接mysql成功后，我们可以对其进行以下管理：
   SHOW DATABASES;: 列出 MySQL 数据库管理系统的数据库列表。
   use 数据库名; ：选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
   SHOW TABLES;: 显示某数据库的所有表。
   SHOW COLUMNS FROM 数据表名;: 显示数据表有哪些属性（表头），属性的数据类型，是否为主键 ，是否为 NULL，默认值等。
   SHOW INDEX FROM 数据表名;: 显示数据表详细的索引信息，包括PRIMARY KEY（主键）。
（4）为某数据库添加mysql用户：
   两种方法：

#方法1
mysql> use 数据库名; 
Database changed 
mysql> INSERT INTO user 
			(host, user, password, select_priv, insert_priv, update_priv) VALUES 				 
			('localhost', 'guest', MD5('guest123'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;刷新，这个命令执行后会重新载入授权表
mysql> SELECT host, user, password FROM user WHERE user = 'guest';查看该用户各个信息。

#方法2
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
		-> ON TUTORIALS.* 
		-> TO 'zara'@'localhost' 
		-> IDENTIFIED BY 'zara123';

3、创建/删除数据库和数据表

3.1、数据库

创建数据库两种方法：
   （1）命令行连接 MySQL 服务后，使用 create 命令创建数据库，语法如下:

CREATE DATABASE 数据库名;

   （2）python中连接mysql后，在python中使用SQL语言，首先生成游标对象，然后利用游标对象执行SQL语句。

sql ="CREATE DATABASE 数据库名"
mycursor = mydb.cursor() 
mycursor.execute(sql)

删除数据库：SQL语言：

drop database 数据库名;

3.2、数据表

（1）创建数据表两种方法：
   创建MySQL数据表需要以下信息： 数据表名，表头名（属性名），定义每个属性的数据类型，定义主键等。当然也可以使用命令行和python语句，python语句定义了游标之后，只需简单的执行SQL语句即可，所以只介绍SQL语句和命令行的操作了。
   语法：CREATE TABLE 数据表名 (表头名 对应数据类型);

mysql> use 数据库名; 
Database changed 
mysql> CREATE TABLE 数据表名( 
-> id INT NOT NULL AUTO_INCREMENT, 
-> title VARCHAR(100) NOT NULL, 
-> author VARCHAR(40) NOT NULL, 
-> submission_date DATE, 
-> PRIMARY KEY ( id ) #定义主键。
-> )

（2）主键设置：
   创建表的时候我们一般都会设置一个主键（PRIMARY KEY）。我们可以使用 “INT AUTO_INCREMENT PRIMARY KEY” 语句来创建一个主键，主键起始值为 1，逐步递增。
A,当还没创建表的时候，可以和其他列一起创建：

mycursor.execute("CREATE TABLE 数据表名 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

B,当已经创建表了，可以向表中添加主键：

mycursor.execute("ALTER TABLE 数据表名 ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

（3）删除数据表：语句，

DROP TABLE 数据表名;

（4）创建临时表：临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

CREATE TEMPORARY TABLE 数据表名（...）;

（5）复制表：完全的复制MySQL的数据表，包括表的结构，索引，默认值等。完整的复制MySQL数据表，步骤如下：

• 使用： SHOW CREATE TABLE 数据表名；语句命令获取创建数据表的语句，该语句包含了原数据表的结构，索引等。
• 复制那些语句，修改数据表名，并执行修改后的语句，将完全的复制数据表结构。
• 如果你想复制表的内容，你就可以使用： INSERT INTO 新数据表名（字段）SELECT 字段 FROM 旧数据表名； 语句来实现。

（6）ALTER 修改命令：用来修改数据表名或者修改数据表字段。
修改表名、类型：

ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE 表名 ENGINE = MYISAM;

查看数据表详细信息：

SHOW TABLE STATUS LIKE '数据表名'；

删除，添加或修改表字段：

#删除字段1
	ALTER TABLE 表名  DROP 字段1; 
#添加字段2，并设置数据类型。默认结尾，若需指定，则在结尾加上：关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
	ALTER TABLE 表名 ADD 字段2 INT; 
#修改字段类型及名称
	ALTER TABLE 表名 MODIFY 字段3 CHAR(10);#modify只能修改类型
	ALTER TABLE 表名 CHANGE 字段2 字段3 BIGINT;#change都可以该, 但更改类型时，字段2 字段2两个都要写。
#指定字段是否包含值，和是否设置默认值，不设置默认值或非NULL，则为NULL
	ALTER TABLE 表名  MODIFY 字段5 BIGINT NOT NULL DEFAULT 100;
#修改字段默认值
	ALTER TABLE 表名 ALTER 字段4 SET DEFAULT 1000;
#删除字段默认值
	ALTER TABLE 表名 ALTER 字段4 DROP DEFAULT;	

查看字段详细信息：

SHOW COLUMNS FROM 表名;

4、向数据表中插入数据

MySQL 表中使用 INSERT INTO 语句来插入属性值数据。通用的 INSERT INTO SQL语法：如果属性值是字符型，必须使用单引号或者双引号，如：“value”。

INSERT INTO 数据表名 ( 字段1, 字段2,...字段N ) VALUES ( 字段1的值, 字段2的值,...字段N的值);

举例插入两条数据：

mysql> use 数据库名; 
Database changed 
mysql> INSERT INTO 数据表名 
	-> (title, author, date) 
	-> VALUES 
	-> ("学习 PHP", "菜鸟教程", NOW());  
mysql> INSERT INTO 数据表名 
	-> (title, author, date) 
	-> VALUES 
	-> ("JAVA 教程", "RUNOOB.COM", '2016-05-06'); 

   在以上实例中，我们并没有提供 id 的属性数据，因为该字段的属性值，我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。
批量插入数据：
   批量插入使用 executemany() 方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据：

sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" 
val = [ 
	('Google', 'https://www.google.com'), 
	('Github', 'https://www.github.com'), 
	('Taobao','https://www.taobao.com'), 
	('stackoverflow', 'https://www.stackoverflow.com/') ] 
mycursor.executemany(sql, val)

   如果插入某行数据后，想要获得该数据记录的id，可以使用mycursor.lastrowid得到。

5、查询数据

   MySQL数据库中查询数据通用的 SELECT 语法：[ ]内的不是必须的

SELECT 字段1,字段2 FROM 数据表名1，数据表名2
	   [WHERE Clause] [LIMIT N] [ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录（多少行）。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有属性的数据。
• 你可以使用 WHERE 语句来包含任何条件。 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  限制行：select 字段 from 表名 limit 2， 2表示限制两行，否则该表头下的。
  去重：select distinct 字段 from 表名，对该字段属性对应的数据去重，得到将唯一的标识取出来。

5.1、过滤函数where

    如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段属性值来读取指定的数据。

SELECT 字段1, 字段2,...字段N FROM 表名1, 表名2...
[WHERE 条件1 [AND [OR]] 条件2.....，

（1）where的操作符：=，<>与!=，<和>，between…and…(指定两个值之间,可以是数值，文本，日期）。这些操作符和语句构成条件。如：

SELECT * FROM user WHERE uid BETWEEN 2 AND 5

（2）逻辑操作符：and,or,(not)in，（not）null。
注：MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

5.2、like函数与通配符

l   ike与where连用，一般用于按照某一规则进行字段列的数据查询。通配符%表示代替一个或多个字符，_代替一个字符。一般用%就可以了。
   like：我们知道使用 SELECT 命令来查询数据，其中可使用 WHERE 子句来获取指定条件的记录。当条件模糊，有时需要在 WHERE 子句中使用LIKE 子句。
   通配符： LIKE 子句中可以使用百分号 %字符来代表任意字符，如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的，就又变成普通的where语句。

SELECT 字段1, 字段2,...字段N FROM 表名 
WHERE 字段1 LIKE 条件1 [AND [OR]] 字段2 = 'somevalue'

SELECT * from runoob_tbl WHERE author LIKE '%COM'; #%表示任意字符

6、数据表之间的关联

6.1、join

当需要从多个数据表中读取数据时，可使用JOIN 在两个或多个表中查询数据。
JOIN按功能分为三类：

• INNER JOIN：内连接，求交集，交集条件时“ON 条件”，查询两数据表中匹配该条件的各个记录（行），然后连接到一起作为一个数据表。前面的表1的字段放在左边，后面的表2的字段放在右边，如果表2的记录数少于表1的则进行补齐。

SELECT 表名1.字段2，表名2.字段4 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段1 = 表名2.字段1

SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.aid = user.uid;   
等价于：
SELECT article.aid,article.title,user.username FROM article, user  WHERE article.aid = user.uid;   

• LEFT/RIGHT JOIN：左/右连接，同样是以“ON 条件”查询，但是会取某个表作为主表，主表的所有记录都会显示（不仅是符合条件的），副表则仅显示符合条件的，副表没有匹配主表的则为NULL。
  LEFT JOIN时，主表在前，副表在后。
  RIGHT JOIN时，主表在后，副表在前。

SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.aid = user.uid

6.2、union

   UNION/UNION ALL：联合，合并多个具有相同字段的数据集。UNION是去重联合，后者则是不管重不重复。联合的意思是表头字段相同，记录叠加；连接则是字段的连接。

SELECT expression1, expression2, ... expression_n FROM tables
[WHERE conditions]
UNION [ALL]
SELECT expression1, expression2, ... expression_n FROM tables
[WHERE conditions];

例如：

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

7、排序order by与分组group by

7.1、order by

   如果我们需要对SELECT读取的数据进行排序，可以使用 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。默认为是按升序排asc，如果降序后面接desc 。

SELECT 字段1, 字段2,...字段N from 表名1, 表名2...
ORDER BY 字段1 [ASC [DESC][默认 ASC]], [字段2...] [ASC [DESC][默认 ASC]]

   可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。可以设定多个字段来排序。可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。你可以在之前添加 WHERE…LIKE 子句来设置条件。

7.2、group by

   如果我们需要对SELECT读取的数据进行分组，可使用GROUP BY 语句，根据一个或多个字段对应的列数据，对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法：

SELECT 字段1, function(字段1)
FROM 表名
WHERE 条件
GROUP BY 字段1;

如：

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

使用 WITH ROLLUP：WITH ROLLUP 可以实现在分组统计基础上再进行相同的统计（SUM,AVG,COUNT…）。例如我们将以上的数据表按名字进行分组，每个人统计了登录次数，在此基础上统计了所有人登录的次数：

SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

   这样得出了登录次数之和SUM(singin) ，但其这样对应的是NULL名字。
记录 NULL： 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，
coalesce 语法：select coalesce(a,b,c);
参数说明：如果a == null，则选择b；如果b ==null，则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

8、处理重复数据

   有些 MySQL 数据表中可能存在重复的记录，有时允许存在，有时需要删除。那么如何防止或删除数据表中的重复数据呢？

8.1、防止表中出现重复数据

保证数据唯一性：设置指定字段为主键（PRIMARY KEY） 或唯一索引（UNIQUE）来保证数据的唯一性。
   如果你想设置表中字段 first_name，last_name 数据不能重复，你可以：
（1）设置双主键，那么对应的字段的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

CREATE TABLE person_tbl(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name));

（2）插入数据不重复：INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略那些在数据库中已存在的数据，不插入。达到不重复插入的目的。
（3）唯一索引 UNIQUE：如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。如下所示：

CREATE TABLE person_tbl ( 
first_name CHAR(20) NOT NULL, 
last_name CHAR(20) NOT NULL,
sex CHAR(10), 
UNIQUE (last_name, first_name) );

8.2、统计重复数据

   统计表person_tbl中 字段first_name 和字段last_name的记录的重复数：

SELECT  last_name, first_name，COUNT(*) as repetitions
	FROM person_tbl
	GROUP BY last_name, first_name
	HAVING repetitions > 1;

执行的操作：字段first_name 和字段last_name的列包含的值可能会重复。使用COUNT(*)进行统计重复数，作为新的字段repetitions。用GROUP BY子句进行分组。HAVING子句用于在聚合函数count之后的筛选（where只能在之前选），筛选后分组后只留下重复数大于1那些记录。

8.3、过滤重复数据

DISTINCT：如果需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

SELECT DISTINCT 字段1, 字段2  FROM 表名;

GROUP BY：也可以使用 GROUP BY 来读取数据表中不重复的数据：

SELECT 字段1, 字段2  FROM 表名   GROUP BY (字段1, 字段2);

8.4、删除重复数据

（1）GROUP BY ：如果想删除数据表中的重复数据，可以使用以下的SQL语句：

CREATE TABLE 临时表名 SELECT 字段1, 字段2  FROM 旧表名  GROUP BY (字段1, 字段2);
DROP TABLE 旧表名;
ALTER TABLE 临时表名 RENAME TO 旧表名;

（2）索引和主键：当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）方法如下：

ALTER IGNORE TABLE 表名
	ADD PRIMARY KEY (last_name, first_name);

9、对数据记录的操作

9.1、UPDATE

    UPDATE 命令修改 MySQL 数据表数据,也就是修改字段对应的属性值，where条件可以控制修改哪条记录或其他条件。

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

如：

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

9.2、DELETE语句

    DELETE FROM 命令来删除数据表中的记录：where可以控制哪条记录，或者符合哪些条件的记录，若无where则删除所有记录。

DELETE FROM table_name [WHERE Clause]

10、事务

事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）
注意：在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
MYSQL 事务处理主要有两种方面：
1、用 BEGIN, ROLLBACK, COMMIT来实现
   BEGIN 开始一个事务
   ROLLBACK 事务回滚
   COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
   SET AUTOCOMMIT=0 禁止自动提交
   SET AUTOCOMMIT=1 开启自动提交
举例：

mysql> use 数据库名;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表，只有innodb引擎才能使用事务。
mysql> select * from runoob_transaction_test;
mysql> begin;  # 开始事务
mysql> insert into runoob_transaction_test value(5);
mysql> insert into runoob_transaction_test value(6);
mysql> commit; # 提交事务，永久修改了
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
mysql> begin;    # 开始事务
mysql>  insert into runoob_transaction_test values(7);
mysql> rollback;   # 回滚，撤销正在进行，所有未提交的修改
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+

11、索引

索引可以大大提高MySQL的检索速度，所以要对索引进行合理的设计。
分类：索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
注意：创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。更新表时，MySQL不仅要保存数据，还要保存一下索引文件。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
显示索引信息：

SHOW INDEX FROM 表名;

11.1 创建/删除索引

   有以下几种创建方式：
（1）设置某字段为索引，并设置索引名：

CREATE INDEX 索引名 ON 表名(字段(length)); 

（2）修改表结构(添加某字段为索引)：

ALTER TABLE 表名 ADD INDEX 索引名(字段);

（3）创建表的时候直接指定：

CREATE TABLE 表名( 字段1 INT NOT NULL, 字段2 VARCHAR(16) NOT NULL,INDEX [索引名] (字段2(length)));  

（4）删除索引的语法：

DROP INDEX [索引名] ON 表名; 

11.2、唯一索引UNIQUE

   与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

CREATE UNIQUE INDEX 索引名 ON 表名(字段(length)); 
ALTER TABLE 表名 ADD UNIQUE [索引名](字段(length));
CREATE TABLE 表名( 字段1 INT NOT NULL, 字段2 VARCHAR(16) NOT NULL,UNIQUE [索引名] (字段2(length)));  

11.3、使用ALTER命令添加和删除索引/主键

ALTER TABLE 表名 ADD PRIMARY KEY (字段): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE 表名 ADD UNIQUE 索引名 (字段): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE 表名 ADD INDEX 索引名 (字段): 添加普通索引，索引值可出现多次。
ALTER TABLE 表名 ADD FULLTEXT 索引名 (字段):该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE testalter_tbl DROP PRIMARY KEY;

12、导出、导入数据

12.1、导出数据

 SELECT ... INTO OUTFILE 文件路径

逆操作：LOAD DATA INFILE
例如：我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

导出文件格式：你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM 表名 INTO OUTFILE '/tmp/runoob.txt
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY 'rn';

导出表作为原始数据：mysqldump 产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 。使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
例如：将数据表 runoob_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl
password ******

导出SQL格式的数据，各种格式信息，操作信息：

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

导出整个数据库的数据：

$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

备份所有数据库：

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

12.2、导入数据

1、mysql 命令导入
使用 mysql 命令导入语法格式为：

mysql -u用户名    -p密码    <  数据库名.sql
mysql -uroot -p123456 < runoob.sql

2、source 命令导入

source 命令导入数据库需要先登录到数库终端：
mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

3、使用 LOAD DATA 导入数据
   从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的数据表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

   LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
        -> INTO TABLE mytbl (b, c, a);

13、函数、运算符、正则表达式

链接地址
函数：各种聚合函数，日期函数，高级函数，字符串函数。
运算符：算术运算符，比较运算符，逻辑运算符，位运算符。
正则表达式：MySQL可以通过 LIKE …% 来进行模糊匹配。同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
例如：查找name字段中以’st’为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

14、mysql数据库实例与提升

14.1、经典的50个SQL语句（以学生、教师为背景）

语句链接地址

背景：数据库有四个表：学生表，课程表，教师表，成绩表。

14.2、数据库常见问题

1、存储引擎：MyISAM与InnoDB

InnoDB存储引擎： 主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用。四大特性：插入缓冲，二次写，自适应哈希索引，预读。
MyISAM存储引擎：是MySQL官方提供的存储引擎，主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
InooDB和MyISAM的select count（*）哪个更快，为什么：
答：myisam更快，因为myisam内部维护了一个计算器，可以直接调取。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
两者的索引的实现方式：都是 B+树索引， Innodb 是索引组织表， myisam 是堆表， 索引组织表和堆表的区别要熟悉。
MyISAM与Innodb两者的区别：

1.InnoDB支持事物，而MyISAM不支持事物
2.InnoDB支持行级锁，而MyISAM支持表级锁
3.InnoDB支持MVCC多版本并发控制, 而MyISAM不支持
4.InnoDB支持外键，而MyISAM不支持
5.InnoDB不支持全文索引，而MyISAM支持。
6.InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器， myisam 支持
7.InnoDB表支持多种行格式， myisam 不支持
8.InnoDB是索引组织表， myisam 是堆表

2、索引

（1）什么是索引：索引（Index）是帮助MySQL高效获取数据的数据结构。
（2）索引的种类：:
从逻辑角度
1、主键索引：主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”
2、唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
3、组合索引
从物理存储角度
1、聚簇索引（clustered index）：聚簇索引的叶子节点就是数据节点，就是按照每张表的主键构造的一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
2、非聚簇索引（non-clustered index）：非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
（3）Mysql索引使用的数据结构：主要有B+Tree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

（4）Mysql的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的：
  MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引”。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
   B+树：B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录结点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。 数据库中B+树索引分为聚集索引和辅助索引,两者内部都是B+树,即高度平衡的,叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。
（5）索引的目的是什么？
快速访问数据表中的特定信息，提高检索速度；创建唯一性索引，保证数据库表中每一行数据的唯一性；加速表和表之间的连接；使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
（6）、索引对数据库系统的负面影响是什么？
负面影响：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。
（7）、为数据表建立索引的原则有哪些？
在最频繁使用的、用以缩小查询范围的字段上建立索引。在频繁使用的、需要排序的字段上建立索引。
（8）、 什么情况下不宜建立索引？
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

3、外连接、内连接与自连接的区别

交叉连接：cross join (不带条件where…)
交，叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。3*3=9条记录，即笛卡尔积。
内连接： inner join，则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。
外连接：左右全，其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。

4、数据库范式

1、 第一范式（1NF）
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
2、 第二范式（2NF）
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。第二范式（2NF）要求实体的属性完全依赖于主关键字。
3 第三范式（3NF）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）。
白话区:
   第一范式：每个表应该有唯一标识每一行的主键。
   第二范式：在复合主键的情况下，非主键部分不应该依赖于部分主键。
   第三范式：非主键之间不应该有依赖关系。
   BC范式:排除了任何属性(不光是非主属性，2NF和3NF所限制的都是非主属性)对候选键的传递依赖与部分依赖。

5、数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素：ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性:要么全做,要么不做,不能只做一半。整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:事务的前后,约束都能满足。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:事务之间是独立的,互不影响的。隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
持久性:事务执行之后,事物的结果可以持久保存。在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
事务隔离级别:
（1）read uncommitted:可以读到未提交的事务结果
（2）read committed:只能读已提交事务的结果
（3）repeatable reads:可以读到开启事务时的值
（4）serializable:两个事务同时发生,必定是有先后的
注意：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。
事务隔离机制的实现：基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

6、视图的作用，视图可以更改么？

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

7、drop,delete与truncate的区别

   drop直接删掉表， truncate删除表中数据，再插入时自增长id又从1开始， delete删除表中数据，可以加where字句。

8、存储过程与触发器的区别

   触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。
触发器是在一个修改了指定表中的数据时执行的存储过程。触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句。

9、锁

MyISAM和InnoDB存储引擎使用的锁：
   MyISAM采用表级锁(table-level locking)。
   InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁。
表级锁和行级锁对比：
表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
InnoDB存储引擎的锁的算法有三种：
   （1）Record lock：单个行记录上的锁
   （2）Gap lock：间隙锁，锁定一个范围，不包括记录本身
   （3）Next-key lock：record+gap 锁定一个范围，包含记录本身

10、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别：
   在单字节字符集下， char（ N） 在内部存储的时候总是定长。在多字节字符集下面， char(N)如果存储的字节数超过 N，那么 char（ N）将和 varchar（ N）没有区别。 varchar(N)无论是什么字节字符集，都是变长的，即都存储变长数据和变长字段长度列表。
(2)、varchar(50)中50的涵义：
   最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。
(3)、int（20）中20的涵义：
   是指显示字符的长度，不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示
(4)、mysql为什么这么设计：
   对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

11、数据库分片的两种常见方案

（1）客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
（2）中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

12、缓存

   开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。
   缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存。

13、备份计划

   备份计划：视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。
mysqldump是逻辑备份，xtranbackup是物理备份。

14、字符集及校对规则

   字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。Mysql中每一种字符集都会对应一系列的校对规则。
   Mysql采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

15、慢查询

   为什么查询速度会慢：查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：
（1）确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访间了太多的列。
（2）确认MYSQL服务器层是否在分析大量超过需要的数据行。

16、数据库优化的思路

1、SQL语句优化
   1）应尽量避免在 where 子句中使用!=或<>操作符，否则引擎会放弃使用索引而进行全表扫描。
   2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
   3）很多时候用 exists 代替 in 是一个好的选择
   4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤。
   5) 通过show status命令了解各种SQL的执行效率.
   6) 定位效率较低的SQL语句.
   7) Explain分析低效SQL的执行计划,各项含义如下：

id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行；如果 id 值相同，则顺序从上到下。
select_type：查询中每个 select 子句的类型。
table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。
partitions:匹配的分区信息。
type:join 类型。
possible_keys：列出可能会用到的索引。
key:实际用到的索引。
key_len:用到的索引键的平均长度，单位为字节。
ref:表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的
key 指向的对象，比如说驱动表的连接列。
rows:估计每次需要扫描的行数。
filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
extra:重要的补充信息。

   Explain 结果中，一般来说，要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引。
   被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总的需求。
2、数据库结构优化
   1）范式优化： 比如消除冗余（节省空间。。）
   2）反范式优化：比如适当加冗余等（减少join）
   3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
   4）拆分其实又分垂直拆分和水平拆分：
   a、案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
   b、方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)
4.服务器硬件优化
   多花点钱！
5、大表优化
   当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
（1）、限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
（2）读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
（3）缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
（4）垂直分区：根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。
垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
（5）水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
6、profile的意义以及使用场景
   Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候，需要用profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql的性能瓶颈。

17、一些mysql的简单问题

链接地址一
链接地址二

最后

以上就是沉默戒指为你收集整理的mysql数据库的基本操作的全部内容，希望文章能够帮你解决mysql数据库的基本操作所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。