MySQL 基本介绍
最流行的关系型数据库管理系统(RDBMS,Relational Database Management System)。
MySQL 常用命令
-
连接数据库。指定主机、用户、密码进行连接。
mysql -h localhost -u root -p
-
新建数据库 create 、删除数据库 drop 、选择数据库 use 。
create database 数据库名; # 新建 drop database 数据库名; # 删除 use 数据库名; # 选择
-
新建数据表 create 、删除数据表 drop 。
CREATE TABLE table_name (column_name column_type); # 新建 DROP TABLE table_name ; # 删除
-
插入数据 insert 。
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
-
删除数据 delete 。
DELETE FROM table_name [WHERE Clause]
-
更新数据 update 。
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
-
查询数据 select 。
# limit n,m : 分页,取n条数据,m表偏移量[0,1,2...] SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
-
查询的 where 子句。
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....
-
查询的 like 子句。具体标识.
# '-':可以匹配任意单个字符。 # '%':可以匹配任意字符串。 SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
-
两个 select 查询的并集 union 。
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
-
将 select 查询出的数据进行排序 order by 。
SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
-
group by 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | singin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.01 sec)
-
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
**LEFT JOIN(左连接):**获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
与 on 联用。inner join 等同于 where 语句 ,且 inner 可省略。
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
-
NULL 值处理。mysql 中 null 与其他任何值比较(即使是null),结果都是 null。
# is null : 当前列的值为null,返回true。 # is not null : 当前列的值不为null,返回true。 ifnull(columnName,0); # 当columnName值为null时,会把null值转为0。
-
alter 修改数据表名,或修改数据表字段。
- 修改数据表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
- 增、删、改数据表字段
ALTER TABLE testalter_tbl ADD i INT; # 增 ALTER TABLE testalter_tbl DROP i; # 删 ALTER TABLE testalter_tbl MODIFY c CHAR(10);# 改 ALTER TABLE testalter_tbl CHANGE i j BIGINT;# 改
MySQL 其他命令
命令 | 描述 |
---|---|
SELECT VERSION( ) | 服务器版本信息 |
SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
SELECT USER( ) | 当前用户名 |
SHOW STATUS | 服务器状态 |
SHOW VARIABLES | 服务器配置变量 |
MySQL其他
1.正则表达式
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 次。 |
示例:
-
查询name字段中以’st’开头的数据:
SELECT name FROM person_tbl WHERE name REGEXP '^st';
-
查找name字段中以’ok’为结尾的所有数据:
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
-
查找name字段中包含’mar’字符串的所有数据:
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
-
查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据:
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
2.事务
事务可以维护数据库的完整性,保证成批SQL语句要么全执行,要么全不执行。事务的ACID属性。
- 原子性(Atomicity):一个事务包含的所有操作,要么全完成,要么全都不完成。
- 一致性(Consistency):事务执行的前后,数据库的完整性(实体完整性、参照完整性…)没有被破坏。
- 隔离性(Isolation):防止多个事务并发执行时由于交叉执行而导致数据的不一致。
- 持久性(Durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
无事务隔离可能出现的情况
- 脏读 :读到未提交的数据,事务1已经读取了事务2修改的内容,随后事务2由于错误回滚。事务1读的就是脏数据。
- 不可重复读:前后读取,数据内容不一致。事务1第一次读取,然后事务2修改了内容,事务1再次读取时发现数据发生改变了。也就是无法读到重复的数据,称为不可重复读。update引起。
- 幻读:前后读取,数据的总量不一致了。事务1第一次查询某表总量,随后事务2增加或删除了一些记录,当事务1再次查询总量时,发现数据量改变了。insert或delete引起。
事务隔离级别
- “读未提(Read Uncommitted)” :可读取未提交内容。一致性最差,可能产生“脏读”、“不可重复读”、“幻读”。
- “读提交(Read Committed)” :只能读取提交的内容。可避免“脏读”。使用“快照”读。
- “可重复读(Repeated Red)” :一次事务中读相同数据保证是一样的。使用“快照”读,且事务启动不可修改。可避免“脏读”,“不可重复读”。MySQL的默认隔离级别。
- “串行化(Serializable)” :事务按顺序串行,一个一个执行。可避免“脏读”、“不可重复读”、“幻读”。但是效率低下,性能也很差。
3.索引
索引可以大大提高MySQL的检索速度,如字典的目录页(拼音、笔画、偏旁部首)就是索引。
-
创建索引
CREATE INDEX indexName ON table_name (column_name)
-
修改表结构(增加索引)
ALTER table tableName ADD INDEX indexName(columnName)
-
创建表的时候直接指定索引
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
-
删除索引
DROP INDEX [indexName] ON mytable;