Mysql的总结

Mysql复习

·       数据库: 

• 数据库是一些关联表的集合。.

·       数据表:

•  表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

·       列:

•  一列(数据元素)包含了相同的数据,例如邮政编码的数据。

·       行：

• 一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

·       冗余：

• 存储两倍数据，冗余降低了性能，但提高了数据的安全性。

·       主键：

• 主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

·       外键：

• 外键用于关联两个表。

·       复合键：

• 复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

·       索引：

• 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

·       参照完整性: 

• 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性

 

 

创建数据库

                  CREATE DATABASE RUNOOB

删除数据库

    DROP DATABASE RUNOOB

Mysql数据类型

    为三类：数值、日期/时间和字符串(字符)类型。

表结构创建

·       如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。

·       AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

·       PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

·       ENGINE设置存储引擎，CHARSET设置编码。

创建MySQL数据表需要以下信息：

·       表名

·       表字段名

·       定义每个表字段

 

删除一个表

          DROP TABLE table_name

向表中插入一个数据

    如果数据是字符型，必须使用单引号或者双引号，如："value"。

例增加语句

INSERT INTO runoob_tbl 

    -> (runoob_title, runoob_author, submission_date)

    -> VALUES

    -> ("学习 PHP", "菜鸟教程", NOW());

查询语句

注意

·            查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
• 你可以使用 LIMIT 属性来设定返回的记录数。

查询所有

         select *fromrunoob_tbl;

where的用法

例：

SELECT *fromrunoob_tblWHERErunoob_author='菜鸟教程';

修改语句：

                   UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

·       可以同时更新一个或多个字段。

o  你可以在 WHERE子句中指定任何条件。

o  你可以在一个单独表中同时更新数据。

 

 

例子

 update 语句可用来修改表中的数据, 简单来说基本的使用形式为:

update 表名称 set列名称=新值 where更新条件;

以下是在表 students 中的实例:

将 id 为 5的手机号改为默认的 - :update students settel=default where id=5;

将所有人的年龄增加 1: update students set age=age+1;

将手机号为 13288097888的姓名改为 "小明",年龄改为 19:update students setname="小明", age=19wheretel="13288097888";

删除语句

                   DELETE FROM table_name [WHERE Clause]

·       如果没有指定 WHERE子句，MySQL 表中的所有记录将被删除。

·       你可以在 WHERE子句中指定任何条件

·       您可以在单个表中一次性删除记录。

例子：

 

    delete 语句用于删除表中的数据, 基本用法为:

deletefrom 表名称 where删除条件;

以下是在表 students 中的实例:

删除 id 为 3的行: delete from studentswhere id=3;

删除所有年龄小于 21 岁的数据: delete from students where age<20;

删除表中的所有数据: delete from students;

Union

MySQL UNION 操作符用于连接两个以上的 SELECT语句的结果组合到一个结果集合中。多个 SELECT语句会删除重复的数据。

·       expression1, expression2,... expression_n:要检索的列。

·       tables: 要检索的数据表。

·       WHERE conditions: 可选，检索条件。

·       DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION操作符已经删除了重复数据，所以 DISTINCT修饰符对结果没啥影响。

·       ALL: 可选，返回所有结果集，包含重复数据。

 

排序

ORDER BY 

·       你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。

·       你可以设定多个字段来排序。

·       你可以使用 ASC或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。

·       你可以添加 WHERE...LIKE子句来设置条件

Mysql连接

·       INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

·       LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

·       RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

MysqlNULl的处理

·       IS NULL: 当列的值是 NULL,此运算符返回 true。

·       IS NOT NULL: 当列的值不为 NULL,运算符返回 true。

·       <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

关于 NULL的条件比较运算是比较特殊的。你不能使用 = NULL或 != NULL 在列中查找 NULL 值。

在 MySQL中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL返回false 。

MySQL 中处理 NULL使用 IS NULL 和 IS NOT NULL 运算符。

正则表达式

下表中的正则模式可应用于 REGEXP操作符中。

模式

描述

^

匹配输入字符串的开始位置。如果设置了 RegExp对象的 Multiline属性，^ 也匹配 '\n' 或 '\r'之后的位置。

$

匹配输入字符串的结束位置。如果设置了RegExp对象的 Multiline属性，$ 也匹配 '\n' 或 '\r'之前的位置。

.

匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n'在内的任何字符，请使用象 '[.\n]'的模式。

[...]

字符集合。匹配所包含的任意一个字符。例如， '[abc]'可以匹配 "plain"中的 'a'。

[^...]

负值字符集合。匹配未包含的任意字符。例如， '[^abc]'可以匹配 "plain"中的'p'。

p1|p2|p3

匹配 p1 或 p2或 p3。例如，'z|food'能匹配 "z" 或 "food"。'(z|f)ood'则匹配 "zood" 或 "food"。

*

匹配前面的子表达式零次或多次。例如，zo*能匹配 "z" 以及 "zoo"。*等价于{0,}。

+

匹配前面的子表达式一次或多次。例如，'zo+'能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+等价于 {1,}。

{n}

n 是一个非负整数。匹配确定的 n次。例如，'o{2}'不能匹配 "Bob" 中的 'o'，但是能匹配 "food"中的两个 o。

{n,m}

m 和 n均为非负整数，其中n <= m。最少匹配 n次且最多匹配 m 次。

实例

了解以上的正则需求后，我们就可以更加自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：

查找name字段中以'st'为开头的所有数据：

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

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

·       在 MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。

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

·       事务用来管理 insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

·       1、事务的原子性：一组事务，要么成功；要么撤回。

·       2、稳定性：有非法数据（外键约束之类），事务撤回。

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

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

在 MySQL命令行的默认设置下，事务都是自动提交的，即执行 SQL语句后就会马上执行 COMMIT操作。因此要显式地开启一个事务务须使用命令BEGIN或 START TRANSACTION，或者执行命令 SETAUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事物控制语句：

·       BEGIN或START TRANSACTION；显式地开启一个事务；

·       COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

·       ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

·       SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

·       RELEASE SAVEPOINTidentifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

·       ROLLBACK TO identifier；把事务回滚到标记点；

·       SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

·       BEGIN 开始一个事务

·       ROLLBACK 事务回滚

·       COMMIT 事务确认

2、直接用 SET来改变 MySQL 的自动提交模式:

·       SET AUTOCOMMIT=0 禁止自动提交

·       SET AUTOCOMMIT=1 开启自动提交

 

 

alert用途

 

alter其他用途：

修改存储引擎：修改为myisam

alter table tableName engine=myisam;

删除外键约束：keyName是外键别名

alter table tableName drop foreign key keyName;

修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面

alter table tableName modify name1 type1 first|after name2;

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL查询语句的条件(一般作为 WHERE子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER mytable ADD INDEX [indexName] ON (username(length)) 

创建表的时候直接指定

CREATE TABLE mytable(  

ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  

INDEX [indexName] (username(length))  

);  

删除索引的语法

DROP INDEX [indexName] ON mytable; 

---

唯一索引

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

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  

ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  

UNIQUE [indexName] (username(length))  

);  

---

使用ALTER命令添加和删除索引

有四种方式来添加数据表的索引：

·       ALTER TABLE tbl_name ADDPRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

·       ALTER TABLE tbl_name ADDUNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

·       ALTER TABLE tbl_name ADDINDEX index_name (column_list): 添加普通索引，索引值可出现多次。

·       ALTER TABLE tbl_name ADDFULLTEXT index_name (column_list):该语句指定了索引为FULLTEXT，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

使用ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

---

显示索引信息

你可以使用 SHOW INDEX命令来列出表中的相关的索引信息。可以通过添加 \G来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G

........

MySQL 处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

---

防止表中出现重复数据

你可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tbl

(

    first_name CHAR(20),

    last_name CHAR(20),

    sex CHAR(10)

);

如果你想设置表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键的默认值不能为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)

);

如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而REPLACE INTO into如果存在primary或unique相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个UNIQUE索引，如下所示：

CREATE TABLE person_tbl

(

   first_name CHAR(20) NOT NULL,

   last_name CHAR(20) NOT NULL,

   sex CHAR(10)

   UNIQUE (last_name, first_name)

);

---

统计重复数据

以下我们将统计表中 first_name和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name

    -> FROM person_tbl

    -> GROUP BY last_name, first_name

    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：

·       确定哪一列包含的值可能会重复。

·       在列选择列表使用COUNT(*)列出的那些列。

·       在GROUP BY子句中列出的列。

·       HAVING子句设置重复数大于1。

---

过滤重复数据

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

mysql> SELECT DISTINCT last_name, first_name

    -> FROM person_tbl;

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

mysql> SELECT last_name, first_name

    -> FROM person_tbl

    -> GROUP BY (last_name, first_name);

---

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex

    ->                  FROM person_tbl;

    ->                  GROUP BY (last_name, first_name, sex);

mysql> DROP TABLE person_tbl;

mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl

    -> ADD PRIMARY KEY (last_name, first_name);