Mysql
三大范式
-
第一范式(1NF):要求数据库表中的每一列都是不可再分的原子数据项,即每一列只能包含一个数据。
-
第二范式(2NF):要求数据库表中的每个非主键列都必须完全依赖于主键,即非主键列必须与主键形成一对多关系。
-
第三范式(3NF):要求数据库表中的每个非主键列都不能依赖于其他非主键列,即非主键列必须直接依赖于主键。
sql语句语法
管理MySQL的命令
- mysql -u 用户名 -p 密码;登录数据库
[root@host]# mysql -u root -p
Enter password:******
- USE 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库
mysql> use 数据库名称;
SHOW DATABASES:列出 MySQL 数据库管理系统的数据库列表。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| RUNOOB |
| cdcol |
| mysql |
| onethink |
| performance_schema |
| phpmyadmin |
| test |
| wecenter |
| wordpress |
+--------------------+
10 rows in set (0.02 sec)
- SHOW TABLES:显示指定数据库的所有表,使用该命令前需要使用 use 命令来选择要操作的数据库。
mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl |
| runoob_tbl |
| tcount_tbl |
+------------------+
3 rows in set (0.00 sec)
- SHOW COLUMNS FROM 数据表:显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id | int(11) | NO | PRI | NULL | |
| runoob_title | varchar(255) | YES | | NULL | |
| runoob_author | varchar(255) | YES | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
- SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+------------+------------+----------+--------------+-------------+-----------+-------------
| runoob_tbl | 0 | PRIMARY | 1 | runoob_id | A | 2 |
+------------+------------+----------+--------------+-------------+-----------+------------
1 row in set (0.00 sec)
- CREATE DATABASE 数据库名;
[root@host]# mysql -u root -p
Enter password:****** # 登录后进入终端
mysql> create DATABASE RUNOOB;
- drop 命令删除数据库
drop database <数据库名>;
数据类型
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值 | |||
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) 极大整数值 | |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度浮点数值 |
日期和时间类型
类型 | 大小( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值 | ||
TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4294967295 bytes | |
LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
表操作语句
- 创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
ENGINE 设置存储引擎,CHARSET 设置编码。
- 删除MySQL数据表的通用语法:
DROP TABLE table_name ;
- 插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
如果数据是字符型,必须使用单引号或者双引号,如:“value”。
- 数据库使用SQL SELECT语句来查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
SELECT * FROM table_name LIMIT N;//查询前N条记录
SELECT * FROM table_name LIMIT offset, size;//table_name是表名,offset是要查询的记录偏移量(从0开始计数),size是每页的记录数。
查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
查询语句中你可以使用一个或者多个表,表之间使用逗号, 分割,并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
- UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
- SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法:
DELETE FROM table_name [WHERE Clause]
如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
- SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
Alter命令
- 删除,添加或修改表字段
mysql> ALTER TABLE testalter_tbl DROP i;
- 修改字段类型及名称
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
- 修改字段默认值
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
- 修改表名
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
多表查询/条件控制
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
- MySQL UNION 操作符语法格式:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
<expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选, 检索条件。
DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选,返回所有结果集,包含重复数据。
- 排序
SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
可以设定多个字段来排序。
可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
可以添加 WHERE…LIKE 子句来设置条件。
- GROUP BY 语句根据一个或多个列对结果集进行分组:
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 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)
- 右连接
与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT 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 |
| NULL | NULL | 22 |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)
- 内连接
获取两个表中字段匹配关系的记录。
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER 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 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)
MySQL的事务
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
四大特性
-
原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
-
一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
-
持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
事务控制语句
-
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。
事务处理方法
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,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;
###唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
- 创建索引
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 TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
###全文索引
全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
###组合索引
建立索引的时候使用多个字段,例如同时使用身份证和手机号建立索引,同样的可以建立为普通索引或者是唯一索引。复合索引的使用复合最左原则。
CREATE INDEX idx_name_age ON users (name, age);
不推荐使用索引的情况
- 经常增删改的列
- 大量的重复的列
- 表记录太少时
使用索引的注意点
- 避免使用函数或表达式进行查询。
- 避免使用模糊查询或通配符查询。
- 避免使用OR操作符连接多个条件。
- 避免使用ORDER BY或GROUP BY操作。
- 避免使用多表连接查询。
- 避免使用子查询或临时表。
MySQL 函数
###字符串函数
ASCII(s) 返回字符串 s 的第一个字符的 ASCII 码。
CHAR_LENGTH(s) 返回字符串 s 的字符数
CHARACTER_LENGTH(s) 返回字符串 s 的字符数,等同于 CHAR_LENGTH(s)
CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x, s1,s2…sn) 同 CONCAT(s1,s2,…) 函数,但是每个字符串之间要加上 x,x 可以是分隔符
FIELD(s,s1,s2…) 返回第一个字符串 s 在字符串列表(s1,s2…)中的位置
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置
FORMAT(x,n) 函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位,最后一位四舍五入。
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置
LCASE(s) 将字符串 s 的所有字母变成小写字母
LEFT(s,n) 返回字符串 s 的前 n 个字符
LOWER(s) 将字符串 s 的所有字母变成小写字母
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len
LTRIM(s) 去掉字符串 s 开始处的空格
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len)
POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置
REPEAT(s,n) 将字符串 s 重复 n 次
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
RIGHT(s,n) 返回字符串 s 的后 n 个字符
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2,使字符串的长度达到 len
RTRIM(s) 去掉字符串 s 结尾处的空格
SPACE(n) 返回 n 个空格
STRCMP(s1,s2) 比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串,等同于 SUBSTR(s, start, length)
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数,返回第 number 个字符左边的字符串。
TRIM(s) 去掉字符串 s 开始和结尾处的空格
UCASE(s) 将字符串转换为大写 如果 number 是负数,返回第(number 的绝对值(从右边数))个字符右边的字符串。
UPPER(s) 将字符串转换为大写
数值函数
ABS(x) 返回 x 的绝对值
ACOS(x) 求 x 的反余弦值(单位为弧度),x 为一个数值
ASIN(x) 求反正弦值(单位为弧度),x 为一个数值
ATAN(x) 求反正切值(单位为弧度),x 为一个数值
ATAN2(n, m) 求反正切值(单位为弧度)
AVG(expression) 返回一个表达式的平均值,expression 是一个字段
CEIL(x) 返回大于或等于 x 的最小整数
CEILING(x) 返回大于或等于 x 的最小整数
COS(x) 求余弦值(参数是弧度)
COT(x) 求余切值(参数是弧度)
COUNT(expression) 返回查询的记录总数,expression 参数是一个字段或者 * 号
DEGREES(x) 将弧度转换为角度
n DIV m 整除,n 为被除数,m 为除数
EXP(x) 返回 e 的 x 次方
FLOOR(x) 返回小于或等于 x 的最大整数
GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
LEAST(expr1, expr2, expr3, …) 返回列表中的最小值
LN 返回数字的自然对数,以 e 为底。
LOG(x) 或 LOG(base, x) 返回自然对数(以 e 为底的对数),如果带有 base 参数,则 base 为指定带底数。
LOG10(x) 返回以 10 为底的对数
LOG2(x) 返回以 2 为底的对数
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
MOD(x,y) 返回 x 除以 y 以后的余数
PI() 返回圆周率(3.141593)
POW(x,y) 返回 x 的 y 次方
POWER(x,y) 返回 x 的 y 次方
RADIANS(x) 将角度转换为弧度
RAND() 返回 0 到 1 的随机数
ROUND(x [,y]) 返回离 x 最近的整数,可选参数 y 表示要四舍五入的小数位数,如果省略,则返回整数。
SIGN(x) 返回 x 的符号,x 是负数、0、正数分别返回 -1、0 和 1
SIN(x) 求正弦值(参数是弧度)
SQRT(x) 返回x的平方根
SUM(expression) 返回指定字段的总和
TAN(x) 求正切值(参数是弧度)
TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入)
日期函数
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
ADDTIME(t,n) n 是一个时间表达式,时间 t 加上时间表达式 n
CURDATE() 返回当前日期
CURRENT_DATE() 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP() 返回当前日期和时间
CURTIME() 返回当前时间
DATE() 从日期或日期时间表达式中提取日期值
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数、
DATE_ADD(d,INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d
DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。
DAY(d) 返回日期值 d 的日期部分
DAYNAME(d) 返回日期 d 是星期几
DAYOFMONTH(d) 计算日期 d 是本月的第几天
DAYOFWEEK(d) 日期 d 今天是星期几,1 星期日,2 星期一,以此类推
DAYOFYEAR(d) 计算日期 d 是本年的第几天
EXTRACT(type FROM d) 从日期 d 中获取指定的值
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期
HOUR(t) 返回 t 中的小时值
LAST_DAY(d) 返回给给定日期的那一月份的最后一天
LOCALTIME() 返回当前日期和时间
LOCALTIMESTAMP() 返回当前日期和时间
MAKEDATE(year, day-of-year) 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
MAKETIME(hour, minute, second) 组合时间,参数分别为小时、分钟、秒
MICROSECOND(date) 返回日期参数所对应的微秒数
MINUTE(t) 返回 t 中的分钟值
MONTHNAME(d) 返回日期当中的月份名称,如 November
MONTH(d) 返回日期d中的月份值,1 到 12
NOW() 返回当前日期和时间
PERIOD_ADD(period, number) 为 年-月 组合日期添加一个时段
PERIOD_DIFF(period1, period2) 返回两个时段之间的月份差值
QUARTER(d) 返回日期d是第几季节,返回 1 到 4
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式
SECOND(t) 返回 t 中的秒钟值
STR_TO_DATE(string, format_mask) 将字符串转变为日期
SUBDATE(d,n) 日期 d 减去 n 天后的日期
SUBTIME(t,n) 时间 t 减去 n 秒的时间
SYSDATE() 返回当前日期和时间
TIME(expression) 提取传入表达式的时间部分
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t
TIME_TO_SEC(t) 将时间 t 转换为秒
TIMEDIFF(time1, time2) 计算时间差值
TIMESTAMP(expression, interval) 单个参数时,函数返回日期或日期时间表达式;有2个参数时,将参数加和
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差,返回
WEEK(d) 计算日期 d 是本年的第几个星期,范围是 0 到 53
WEEKDAY(d) 日期 d 是星期几,0 表示星期一,1 表示星期二
WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期,范围是 0 到 53
YEAR(d) 返回年份
YEARWEEK(date, mode) 返回年份及第几周(0到53)
高级函数
BIN(x) 返回 x 的二进制编码
BINARY(s) 将字符串 s 转换为二进制字符串
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
WHEN conditionN THEN resultN
ELSE result
END CASE 表示函数开始,END 表示函数结束。如果 condition1 成立,则返回 result1, 如果 condition2 成立,则返回 result2,当全部不成立则返回 result,而当有一个成立之后,后面的就不执行了。
CAST(x AS type) 转换数据类型
COALESCE(expr1, expr2, …, expr_n) 返回参数中的第一个非空表达式(从左向右)
CONNECTION_ID() 返回唯一的连接 ID
CONV(x,f1,f2) 返回 f1 进制数变成 f2 进制数
CONVERT(s USING cs) 函数将字符串 s 的字符集变成 cs
CURRENT_USER() 返回当前用户
DATABASE() 返回当前数据库名
IF(expr,v1,v2) 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
ISNULL(expression) 判断表达式是否为 NULL
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值
NULLIF(expr1, expr2) 比较两个字符串,如果字符串 expr1 与 expr2 相等 返回 NULL,否则返回 expr1
SESSION_USER() 返回当前用户
引擎
innodb和myisam的区别
- myisam不支持行级锁,对整张表进行加锁。同时不支持事务和外键,但是可以被压缩,存储空间小,筛选数据快。
- innodb是事务型引擎,事务异常提交时,会被回滚,支持行锁。会在内存中建立其专用的缓冲池用于告诉缓冲数据和索引。
MVCC(mul-version concurrency control)
MVCC(Multi-Version Concurrency Control)是一种并发控制机制,用于处理多个事务并发执行时可能产生的读写冲突和数据不一致问题。MVCC的基本思想是为每个事务提供一个独立的数据版本,从而实现对数据的隔离和保护。
MVCC的核心机制是在每个版本中存储旧数据的快照,同时保留新数据的原始版本。这样,当一个事务执行查询时,MySQL会根据事务的隔离级别和版本号,确定哪些数据版本是可见的,哪些是不可见的。如果一个事务修改了某个数据行,那么其他事务只能看到该数据行的旧版本,直到该事务提交为止。