/ 数据库

MySQL基本操作

选择数据库: USE database;


显示所有数据库: SHOW DATABASES;

显示当前选择的数据库里的所有表: SHOW TABLES;

显示某张表的所有列: SHOW COLUMNS FROM table; (等效于DESCRIBE table;)

显示服务器状态信息: SHOW STATUS;

显示用户权限: SHOW GRANTS;

显示服务器的警告或错误: SHOW WARNING;/SHOW ERRORS;


检索表中一列: SELECT column FROM table;

检索表中多列: SELECT column1, column2 FROM table;

检索表中所有列: SELECT * FROM table;

检索不同行: SELECT DISTINCT column FROM table;(这个DISTINCT作用于所有列,意思即指定的每一列相同才判定是相同,有一列不同都是不相同的行,就会显示检索出来)

限制检索行数: SELECT column FROM table LIMIT m,n;(m表示开始检索的位置,从0开始,n表示检索的行数,如果只有一个参数则从第0行开始选择n行)


排序检索结果: SELECT column1 FROM table ORDER BY column2;

依据多个列排序检索结果: SELECT column1 FROM table ORDER BY column2, column3;(先依据column2,再依据column3进行排序)

指定升/降序排序: SELECT column1 FROM table ORDER BY column2 DESC, column3;(先依据column2降序排序,再依据column3进行排序)


过滤检索: SELECT column1 FROM table WHERE column2 = ''; (WHERE子句的操作符有: =, <>, !=, <, <=, >, >=, BETWEEN(BETWEEN和AND结合使用))
空值检索: SELECT column1 FROM table WHERE column2 IS NULL;


AND/OR过滤检索: SELECT column1 FROM table WHERE column1 = '' AND column2 != '';(除AND之外还有OR操作符,且AND的优先级高于OR,为了不出错,推荐使用圆括号组合过滤条件)

IN过滤检索: SELECT column1 FROM table WHERE column2 IN (value1, value2); (column2为value1或者value2)

NOT过滤检索: SELECT column1 FROM table WHERE column2 NOT IN (min, max); (NOT操作符用于否定WHERE中的子句)


通配符过滤检索: SELECT column1 FROM table WHERE column2 LIKE 'str%'; (为了使用通配符,必须使用LIKE操作符匹配整个列,之后%表示出现任意字符任意次数;还有_通配符,_表示一个任意字符)

正则表达式检索: SELECT column1 FROM table WHERE column2 REGEXP 'regexp'; (特殊正则字符用\引导,例如-用\-表示)


拼接字段: SELECT Concat(column1, '-', column2, '-') FROM table; (Concat是一个函数,这条语句输出的字段为column1-column2-)

别名: SELECT Concat(column1, '-', column2, '-') AS field1 FROM table; (field1作为别名输出,别名有时候也称作导出列)(FROM里的表也可以有别名,用于WHERE里等,这个别名不输出到客户机)

计算字段: SELECT column1*column2 AS field1 FROM table;


数据处理函数

文本处理函数

Left: 返回串左边的字符
Length: 返回串的长度
Locate: 找出串的一个子串
Lower: 将串转换为小写
LTrim: 去掉串左边的空格
Right: 返回串右边的字符
RTrim: 去掉串右边的字符
Soundex: 返回串的SOUNDEX值(SOUNDEX是将字符车转换为语音描述的模式算法,可以提供按发音搜索,而不是字符搜索功能)
Substring: 返回子串的字符
Upper: 将串转换为大写

日期和时间处理函数u

AddDate: 增加一个日期(天、周等)
AddTime: 增加一个时间(时、分等)
CurDate: 返回当前日期
CurTime: 返回当前时间
Date: 返回日期时间的日期部分
DateDiff: 计算两个日期之差
Date_Add: 高度灵活的日期运算函数
Date_Format: 返回一个格式化的日期或时间串
Day: 返回一个日期的天数部分
DayOfWeek: 返回一个日期对应的星期数
Hour: 返回一个时间的小时部分
Minute: 返回一个时间的分钟部分
Month: 返回一个日期的月份部分
Now: 返回当前日期和时间
Second: 返回一个时间的秒部分
Time: 返回一个日期时间的时间部分
Year: 返回一个日期的年份部分

数值处理函数

Abs: 返回一个数的绝对值
Cos: 返回一个角度的余弦值
Exp: 返回一个数的指数值
Mod: 返回除操作的余数
Pi: 返回圆周率
Rand: 返回一个随机数(应该是[0, 1)之间)
Sin: 返回一个角度的正弦
Sqrt: 返回一个数的平方根
Tan: 返回一个角度的正切


聚集函数-运行在组上,计算和返回单个值的函数

AVG: 返回某列的平均值(一个AVG只能求一列的平均值,另外AVG会忽略NULL的行)。 SELECT AVG(column) AS field FROM table;
COUNT: 返回某列的行数。SELECT COUNT() AS field FROM table; (如果不是而是指定列,则会计算出对应列不为空的总数)
MAX: 返回某列的最大值
MIN: 返回某列的最小值
SUM: 返回某列值之和


分组检索: SELECT column1, COUNT() AS field FROM table GROUP BY column1; (注意GROUP BY只能对选择列或者表达式列进行)
过滤分组检索: SELECT column1, COUNT(
) AS field FROM table GROUP BY column1 HAVING COUNT(*) >= 2; (WHERE用于单行的过滤,HAVING用于分组的过滤)


WHERE里的子查询: SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2 WHERE column4 = ''); (子查询由内而外进行,先查内部的,得到结果后再向外进行)
作为计算字段的子查询: SELECT column1, (SELECT column2 FROM table2 WHERE table2.column2 = table1.column2) AS field FROM table1;


联结表查询: SELECT column1, column2, column3 FROM table1, table2 WHERE table1.column1 = table2.column1; (联结的创建就是规定要联结的所有表以及它们之间如何关联(外键))
内部联结: SELECT column1, column2, column3 FROM table2 INNER JOIN table1 ON table1.column1 = table2.column1; (更加明确地指定联结关系)
联结多个表: SELECT column1, column2, column3 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table2. column2 = table3.column2;
自联结: SELECT column1, column2 FROM table AS t1, table AS t2 WHERE t1.column1 = t2.column1 AND t2.column2 = '';
外部联结: SELECT table1.column1, table2.column2 FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column1; (OUTER JOIN表示外部联结,LEFT/RIGHT表示包括所有行的是左边还是右边的表)


组合查询: SELECT column1, column2 FROM table WHERE column3 IN (a, b) UNION SELECT column1, column2 FROM table WHERE column4 < n; (UNION只要求选择的列和列的类型相同)
取消重复行的组合查询: 将上面的UNION换成UNION ALL


全文本搜索:

FULLTEXT: 要进行全文本搜索就要建立索引,且每次数据改动候要重新索引。可以在建表时设置FULLTEXT创建自动索引:

CREATE TABLE productnotes
(
    note_id int NOT NULL AUTO_INCREMENT,
    prod_id char(10) NOT NULL,
    note_data datetime NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text),
) ENGINE=MyISAM;

之后可以使用Match()和Against()执行全文本搜索:

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

查询扩展: SELECT note_text FROM productnotes WHERE Match(note_text) AGAINST('rabbit' WITH QUERY EXPANSION); (查询扩展不止进行文本搜索,还会将与文本搜索结果相关联的行搜索出来)


插入数据: INSERT INTO table VALUES(NULL, 'Pep E. LaPaw', '90046', NULL); (这种必须严格按照表列的顺序指定值插入,不是很实用)
指定列插入数据: INSERT INTO table(column1, column2, column3, column4) VALUES(NULL, 'Pep E. LaPaw', '90046', NULL); (明确给出列名,就不用严格按照列的顺序输入值)
插入多组值: INSERT INTO table(column1, column2) VALUES(NULL, 'Pep E. LaPaw'), (NULL, 'M. Martin'); (用,分隔每一组)


更新数据: UPDATE table SET column1 = '' WHERE column2 = ''; (指定三个东西: 要更新的表,列名和新值,要更新行的过滤条件)
IGNORE: UPDATE IGNORE table SET column1 = '' WHERE column2 > n; (即使发生错误,也继续更新,如果不加IGNORE,出错了的话会恢复到更新前的状态)
删除某列的数据: 即将某列的数据设置为NULL(如果允许的话)
删除行: DELETE FROM table WHERE column1 = ''; (如果不带WHERE将删除所有行,但删除所有行更快的方法见下一条语句)
删除所有行: TRUNCATE table; (实质上是删除原来的表并建立一个新的表)


创建表,就是指定表名以及指定表列的详情:

CREATE TABLE table
(
column1 int NOT NULL AUTO_INCREMENT,
column2 char(50) NOT NULL,
PRIMARY KEY(column1)
) ENGINE = InnoDB;

注意: 每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(通过使它成为主键)。last_insert_id()函数将返回最后一个AUTO_INCREMENT的值。


几个数据库引擎:

InnoDB: 可靠的事务处理引擎,但不支持全文本搜索。
MEMORY: 在功能上等同于MyISAM,但由于数据存储在内存(而不是磁盘中),速度很快(适合于临时表)。
MyISAM: 性能极高的引擎,支持全文本搜索,但不支持事务。