MySQL 正则表达式

查找name字段中以’小明’为开头的所有数据：

  1. SELECT name FROM employee_tbl WHERE name REGEXP "^小明";

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

  1. SELECT `name` FROM employee_tbl WHERE  `name` REGEXP "明$";

查找name字段中包含’小明’字符串的所有数据：

  1. select name from employee_tbl WHERE `name` REGEXP "小明";

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

  1. SELECT `name` FROM employee_tbl WHERE `name` REGEXP "^[aeiou]|丽$";

MySQL 事务

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

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

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


事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • 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 开启自动提交 ```sql CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;

select * from runoob_transaction_test;

begin; # 开始事务

insert into runoob_transaction_test value(5);

insert into runoob_transaction_test value(6);

commit; # 提交事务

select * from runoob_transaction_test;

begin; # 开始事务

insert into runoob_transaction_test values(7);

rollback; # 回滚

select * from runoob_transaction_test; # 因为回滚所以数据没有插入

  1. <a name="KYRIi"></a>
  2. ## MySQL ALTER命令
  3. 当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
  4. ```sql
  5. CREATE TABLE testalter_tbl
  6. (i INT ,c CHAR(1));
  8. #查看表结构
  9. SHOW COLUMNS FROM testalter_tbl;
  11. #如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段： 
  12. ALTER TABLE testalter_tbl DROP i;
  14. #MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
  15. ALTER TABLE testalter_tbl ADD i INT;
  16. #执行以上命令后，i 字段会自动添加到数据表字段的末尾。
  18. #如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
  19. #尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：
  20. ALTER TABLE testalter_tbl DROP i;
  21. ALTER TABLE testalter_tbl ADD i INT FIRST;
  22. ALTER TABLE testalter_tbl DROP i;
  23. ALTER TABLE testalter_tbl ADD i INT AFTER c;
  24. #FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
  26. #如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
  27. #例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
  28. ALTER TABLE testalter_tbl MODIFY c CHAR(10);
  30. #使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例
  31. ALTER TABLE testalter_tbl CHANGE i j BIGINT;
  32. ALTER TABLE testalter_tbl CHANGE j j INT;
  34. #使用 ALTER 命令添加和删除主键
  35. #主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
  36. ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
  37. ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
  39. #也可以使用 ALTER 命令删除主键：
  40. ALTER TABLE testalter_tbl DROP PRIMARY KEY;
  41. #删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
  43. #可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
  44. SHOW INDEX FROM table_name; \G
  46. #当你修改字段时，你可以指定是否包含值或者是否设置默认值。
  47. #以下实例，指定字段 j 为 NOT NULL 且默认值为100 。 
  48.  ALTER TABLE testalter_tbl 
  49.      MODIFY j BIGINT NOT NULL DEFAULT 100;
  51. #你可以使用 ALTER 来修改字段的默认值，尝试以下实例：
  52. ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  54. #你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
  55. ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
  57. #修改表名
  58. #如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
  59. #尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：
  60. ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MySQL 索引

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

  1. #这是最基本的索引，它没有任何限制。它有以下几种创建方式：
  2. create index indexname on mytable(username(16));
  3. #如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
  4. #修改表结构(添加索引)
  5. ALTER table runoob_tbl ADD index indexname(runoob_id);
  6. #创建表的时候直接指定
  7. CREATE TABLE mytable(  
  8. ID INT NOT NULL,   
  9. username VARCHAR(16) NOT NULL,  
  10. INDEX [indexName] (username(length))  
  11. );
  12. #删除索引的语法
  13. show index from runoob_tbl;
  14. drop index indexname on runoob_tbl;
  15. drop index indexname on mytable;

唯一索引

  1. #它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式
  2. #创建索引
  3. CREATE UNIQUE INDEX indexName ON mytable(username(length));
  4. #修改表结构 
  5. ALTER table mytable ADD UNIQUE [indexName] (username(length));
  6. #创建表的时候直接指定
  7. CREATE TABLE mytable(  
  8. ID INT NOT NULL,   
  9. username VARCHAR(16) NOT NULL,  
  10. UNIQUE [indexName] (username(length))  
  11. );
  13. #使用ALTER 命令添加和删除索引

MySQL 临时表

  1. #创建临时表
  2. CREATE TEMPORARY TABLE SalesSummary (
  3.     product_name VARCHAR ( 50 ) NOT NULL,
  4.     total_sales DECIMAL ( 12, 2 ) NOT NULL DEFAULT 0.00,
  5.     avg_unit_price DECIMAL ( 7, 2 ) NOT NULL DEFAULT 0.00,
  6.     total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 
  7. );
  8. #添加数据
  9. INSERT INTO SalesSummary ( product_name, total_sales, avg_unit_price, total_units_sold )
  10. VALUES
  11.     ( 'cucumber', 100.25, 90, 2 );
  12. #当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。
  13. #如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
  15. #默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
  16. #以下是手动删除临时表的实例：
  17. #创建临时表
  18. CREATE TEMPORARY TABLE SalesSummary (
  19.     product_name VARCHAR ( 50 ) NOT NULL,
  20.     total_sales DECIMAL ( 12, 2 ) NOT NULL DEFAULT 0.00,
  21.     avg_unit_price DECIMAL ( 7, 2 ) NOT NULL DEFAULT 0.00,
  22.     total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 
  23. );
  24. #添加数据
  25. INSERT INTO SalesSummary ( product_name, total_sales, avg_unit_price, total_units_sold )
  26. VALUES
  27.     ( 'cucumber', 100.25, 90, 2 );
  28. #删除临时表
  29. DROP TABLE SalesSummary;

MySQL 复制表

  1. #步骤一：
  2. #获取数据表的完整结构。
  3. SHOW CREATE TABLE runoob_tbl \G;
  4. #步骤二：
  5. #复制以上的查询结果，修改SQL语句的数据表名，并执行SQL语句。
  6. #步骤三：
  7. #执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

MySQL 序列使用

  1. #使用 AUTO_INCREMENT
  2. #MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
  3. #实例
  4. #以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。
  5. CREATE TABLE insect
  6. (
  7. id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  8. PRIMARY KEY (id),
  9. name VARCHAR(30) NOT NULL, # type of insect
  10. date DATE NOT NULL, # date collected
  11. origin VARCHAR(30) NOT NULL # where collected
  12. );
  14. INSERT INTO insect (id,name,date,origin) VALUES
  15. (NULL,'housefly','2001-09-10','kitchen'),
  16. (NULL,'millipede','2001-09-10','driveway'),
  17. (NULL,'grasshopper','2001-09-10','front yard');
  19. SELECT * FROM insect ORDER BY id;
  20. +----+-------------+------------+------------+
  21. | id | name        | date       | origin     |
  22. +----+-------------+------------+------------+
  23. |  1 | housefly    | 2001-09-10 | kitchen    |
  24. |  2 | millipede   | 2001-09-10 | driveway   |
  25. |  3 | grasshopper | 2001-09-10 | front yard |
  26. +----+-------------+------------+------------+
  27. 3 rows in set (0.00 sec)
  29. #重置序列
  31. #设置序列的开始值
  32. #般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：
  33. CREATE TABLE insect (
  34.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  35.     PRIMARY KEY ( id ),
  36.     NAME VARCHAR ( 30 ) NOT NULL,
  37.     date DATE NOT NULL,
  38. origin VARCHAR ( 30 ) NOT NULL 
  39. ) ENGINE = INNODB auto_increment = 100 charset = utf8;
  40. #或者也可以在表创建成功后，通过以下语句来实现：
  41. ALTER TABLE insect AUTO_INCREMENT = 100;

MySQL 处理重复数据

  1. #防止表中出现重复数据
  2. #你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。 
  3. #如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
  4. #INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，
  5. #如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
  6. #NSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 
  7. #而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
  1. #统计重复数据
  3. #过滤重复数据
  5. #删除重复数据

MySQL 导出数据

MySQL中你可以使用SELECT…INTO OUTFILE语句来简单的导出数据到文本文件上。

  1. #使用 SELECT ... INTO OUTFILE 语句导出数据
  2. #以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中: 
  3. select * from runoob_tbl into outfile "/tmp/runoob.txt";
  5. #可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
  6. SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
  7. FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  8. LINES TERMINATED BY '\r\n';
  10. #在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。
  11. SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
  12. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  13. LINES TERMINATED BY '\n'
  14. FROM test_table;

MySQL 导入数据

  1. #1、mysql 命令导入
  3. #2、source 命令导入
  5. #3、使用 LOAD DATA 导入数据
  7. #4、使用 mysqlimport 导入数据

MySQL 函数

  1. #MySQL 字符串函数
  3. #MySQL 数字函数
  5. #MySQL 日期函数
  7. #MySQL 高级函数

MySQL 运算符

  1. #算术运算符
  2. #在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。
  4. #比较运算符
  5. #SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。
  6. #比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
  7. #安全等于
  8. #与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。
  10. #逻辑运算符
  11. #逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
  13. #位运算符
  14. #位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。
  15. #然后再将计算结果从二进制数变回十进制数。
  17. #运算符优先级