Mysql基本使用详解

mysql数据类型

数值类型

日期和时间类型

字符串类型

注意： char(n) 和 varchar(n) 括号中** n 代表字符的个数**，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

WHERE 子句

• MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以在条件前字段使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
• 用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。
• 可以使用 AND 或者 OR 指定一个或多个条件

where 与 group by的区别

• group by:对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。
• having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。
• 执行顺序：where > group by > having > select > order by

UPDATE 更新

基本使用语法

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

update 替换某个字段中的某个字符

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')

DELETE 语句

• delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除。
• delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚。
• 执行的速度上，drop>truncate>delete。

LIKE 子句

• %：表示任意 0 个或多个字符。
• _：表示任意单个字符。
• []：表示括号内所列字符中的一个（类似正则表达式）。
• [^] ：表示不在括号所列之内的单个字符。
• 查询内容包含通配符时,把特殊字符用 “[ ]” 括起便可正常查询。

UNION 操作符

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

• UNION：用于将不同表中相同列中查询的数据展示出来（不包括重复数据）。
• UNION ALL ：用于将不同表中相同列中查询的数据展示出来（包括重复数据）。

连接的使用

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 获取右表所有记录，即使左表没有对应匹配的记录。

NULL 值处理

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符，当比较的的两个值相等或者都为 NULL 时返回 true。

注意：关于NULL的条件比较运算是比较特殊的。你不能使用 =NULL 或 !=NULL 在列中查找NULL值 。
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL。

正则表达式

MySQL中使用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 次。

事务

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

特性（ACID）

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

事务处理主要有两种方法

• 用 BEGIN, ROLLBACK, COMMIT来实现
  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认
• 直接用 SET 来改变 MySQL 的自动提交模式:
  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

ALTER命令

1. 删除表字段（DROP）

ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

2. 添加表字段（ADD）

ALTER TABLE testalter_tbl ADD i INT;

指定新增字段的位置，可以使用关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

3. 修改表字段（MODIFY 或 CHANGE）

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

ALTER TABLE testalter_tbl CHANGE i j INT;

4. 修改字段默认值

修改字段的默认值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

删除字段的默认值

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

5. 修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

6. 修改存储引擎

alter table tableName engine=myisam;

7. 删除外键约束

alter table tableName drop foreign key keyName;

索引

普通索引

创建索引

CREATE INDEX indexName ON table_name (column_name)

添加索引

ALTER table tableName ADD INDEX indexName(columnName)

删除索引

DROP INDEX [indexName] ON mytable;