数据库复习
趁着放假时间，复习一下数据库的使用。目标：
数据库命令列表
连接、查看数据库
命令 用途 示例 备注 mysql -uUSER -hHOST -pPORT -DDATABASE -p(PASSWORD) 终端下连接数据库命令 mysql -u gamer -h localhost -p 3306 -D gamer2048 -p
1.账户密码可以不写，回车后采用非明文方式输入密码登录，避免被盗 2.连接本地数据库HOST可以设为localhost 3.可以不选择指定数据库登录(登录后再选择相应数据库)
USE database 使用指定数据库 USE gamer2048
1.相关命令：SHOW DATABASES 查看有哪些数据库
SHOW DATABSES 显示可用的数据库表 SHOW DATABASES
1.数据库、表、列、用户、权限等的信息信息实际存储在数据库名为mysql&information_schema的数据库中，使用这类命令时，实际是查询这些数据库的信息
SHOW TABLES 显示数据库中可用的表 SHOW TABLES SHOW COLUMNS FROM table 显示指定表结构信息 SHOW COLUMNS FROM user_info
1.MYSQL简写语句：DESCRIBE/DESC table
SHOW GRANTS 显示用户的安全权限 SHOW GRANTS
1.查看该用户具备哪些操作权限
SHOW STATUS 显示服务器状态信息 SHOW STATUS SHOW CREATE DATABASE database 显示创建特定数据库的SQL语句 SHOW CREATE DATABASE gamer2048 SHOW CREATE TABLE table 显示创建特定表的SQL语句 SHOW CREATE TABLE user_info HELP SHOW 显示允许的show语句 HELP SHOW 快速查询show相关语句
补充：
查
检索数据[SELECT]
命令 用途 示例 备注 SELECT column FROM table 检索单个列 SELECT prod_name FROM products
1.数据的顺序是无意义的，应使用ORDER BY子语句 2.可使用完全限定的表名和列名：SELECT products.prod_name FROM test.products
SELECT column1,column2 FROM table 检索多列 SELECT vend_id,prod_name FROM products
1.返回数据列按先后语句中先后顺序返回 2.逗号存在于列名之间，结尾不要有
SELECT * FROM table 检索所有列 SELECT * FROM products
1.实际是使用了通配符(*) 2.列顺序一般与列在表中定义的顺序相同，但表模式变化可能会影响该顺序 3.可以检索出表中未知的列 4.
避免使用该条语句从而降低性能
相同行过滤[DISTINCT]
命令 用途 示例 备注 SELECT DISTINCT column1 FROM table 检索不同行（过滤作用） SELECT DISTINCT vend_id FROM products
1.DISCTINCT配合多个列时，作用于全部列，而不是局部列(CAN NOT)
行数过滤[LIMIT]
命令 用途 示例 备注 SELECT column FROM table LIMIT num 返回不多于num行数据 SELECT prod_name FROM products LIMIT 5
1.num值设置超过数据的最大行数时，显示所有数据
SELECT column FROM table LIMIT offsets, num 返回从offsets开始（含）的num行数据 SELECT prod_name FROM products LIMIT 0,1
1.
offset参数指定行索引，而行索引从0开始，因此显示第n行的数据时offset参数应设置为n-1
SELECT column FROM table LIMIT num OFFSET offsets 返回从offset开始（含）的num行数据 SELECT prod_name FROM products LIMIT 1 OFFSET 0
1.MYSQL5开始支持该语句=”SELECT column FROM table LIMIT offset, num”语句
排序[ORDER BY]
命令 用途 示例 备注 SELECT column1,column2 FROM table ORDER BY column1 排序单列 SELECT prod_price,prod_name FROM products ORDER BY prod_price
1.默认升序2.多个子语句时顺序：SELECT->FROM->WHERE->ORDER BY->LIMIT
SELECT column1,column2 FROM table ORDER BY column1,column2 排序多列 SELECT prod_price,prod_name FROM products ORDER BY prod_price,prod_name
1.按语句列名顺序先排序前面的列，该列值相同时排序后面的列
SELECT column1,column2 FROM table ORDER BY column1 DESC 指定排列方向，降序排列 SELECT prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name DESC
1.关键字位于列名后面，降序关键字DESC(descending order),升序关键字ASC(acending order，默认)2.MYSQL默认字典序排序(a与A相同)，可配置
普通过滤[WHERE]
命令 用途 示例 备注 SELECT column1 FROM table WHERE column1 < high 简单语句 SELECT * FROM products WHERE prod_price <= 10 ORDER BY prod_price DESC LIMIT 5 SELECT column1 FROM table WHERE column1 < high OR column2 > low 复合语句 SELECT * FROM products WHERE prod_price <= 10 AND vend_id = 1001
1.AND和OR语句组合多个WHERE子句 2.AND优先级比OR优先级高，先和AND组合起来在和OR语句组合起来 3.使用
括号 来改变优先级：SELECT * FROM products WHERE (prod_price < 8 OR prod_price > 10) AND vend_id = 1001
WHERE子句操作符
操作符 用途 示例 备注 = 等于 SELECT * FROM products WHERE prod_price = 10 ORDER BY prod_price DESC
1.比较字符串时需要单引号，比较值时不需要。例：prod_name=’addidas’2. 字符串比较时默认不区分大小写。例：’addidas’=’Addidas’ 为真
!= 不等于 SELECT * FROM products WHERE prod_price != 10 ORDER BY prod_price DESC <> 不等于 SELECT * FROM products WHERE prod_price <> 10 ORDER BY prod_price DESC 同!= > 大于 SELECT * FROM products WHERE prod_price > 10 ORDER BY prod_price DESC >= 大于等于 SELECT * FROM products WHERE prod_price >= 10 ORDER BY prod_price DESC < 小于 SELECT * FROM products WHERE prod_price < 10 ORDER BY prod_price DESC <= 小于等于 SELECT * FROM products WHERE prod_price <= 10 ORDER BY prod_price DESC BETWEEN num1 AND num2 范围 SELECT * FROM products WHERE prod_price BETWEEN 5 AND 10 ORDER BY prod_price DESC 和AND配合,边界均包含([low,high]) IS NULL 空值判定 SELECT * FROM vendors WHERE vend_state IS NULL 反义：IS NOT NULL IN 范围 SELECT * FROM products WHERE vend_id IN (1001,1005)
1.反义：NOT IN 2.逗号分隔两个值，集合放在括号内 3.优点：
速度通常比OR快；可以包含其它SELECT语句，使得能够动态的建立WHERE语句
NOT 否定 SELECT * FROM products WHERE prod_price NOT BETWEEN 5 AND 10 ORDER BY prod_price DESC
1.MYSQL支持使用NOT对IN，BETWEEN和EXISTS和LIKE子句取反
通配符过滤[LIKE]
通配符用单引号(”)包含 MYSQL不支持：[abc]、[\^abc]、[!abc]? 操作符 用途 示例 备注 % 替代一个或多个字符 SELECT * FROM vendors WHERE vend_state LIKE ‘%M’ _ 仅替代一个字符 SELECT * FROM vendors WHERE vend_address LIKE ‘5__ %’
正则表达式过滤[REGEXP]
MYSQL仅支持正则表达式实现的一个很小的子集（不全支持） LIKE与REGEXP区别：LIKE匹配整个列值，REGEXP在列值内进行匹配（除非采用^和$定位符）。 REGEXP匹配特殊字符时采用\\为前导 ，如’\.’匹配’.’字符，MYSQL自己解释一个，正则表达式库解释另一个。正则表达式匹配 用途 示例 备注 . 1个字符匹配 ad|bc 多组OR匹配,匹配其中一组字符 SELECT * FROM products WHERE prod_name REGEXP ‘JetPACK (1000|2000)’
1.注意字符串中文本默认不区分大小写（可配置）
[ab] 多个OR匹配,匹配其中一个字符 [a-z] 范围匹配,匹配范围内其中一个字符 [^ab] 否定匹配,不匹配任意其中一个字符
正则表达式重复元字符 用途 示例 备注 * 匹配0个或多个 ? 匹配0个或1个 + 匹配1个或多个 {n} 匹配n个 {n,} 匹配大于等于n个 {n,m} 匹配n个到m个
正则表达式定位符 用途 示例 备注 ^ 文本开头 $ 文本结尾 [[:<:]] 词开头 匹配含有开头为a的词的列:SELECT * FROM products WHERE prod_name REGEXP ‘[[:<:]]a’ [[:>:]] 词结尾 匹配含有结尾为l的词的列:SELECT * FROM products WHERE prod_name REGEXP ‘l[[:>:]]’
正则表达式匹配字符类 用途 示例 备注 [:album:] 字母和数字[a-zA-Z0-9] [:alpha:] 字母[a-zA-Z] [:upper:] 大写字母[A-Z] [:lower:] 小写字母[a-z] [:digit:] 数字[0-9] [:xdigit:] 十六进制数字[a-fA-F0-9] [:space:] 空格在内的任意空白字符 [:print:] 任意可打印字符
计算显示
对从数据库检索出的数据进行转换、计算或格式化后返回给客户端。
字段别名
命令 用途 示例 备注 AS关键字 字段或值的替换名 便于客户端调用
数据算术运算
实现加减乘除四则运算。例:SELECT order_item*item_price AS total_price FROM orderitems
数据拼接
函数 用途 示例 备注 Concat()函数 拼接多个列和字符 SELECT Concat(vend_id,’(‘,Trim(prod_price),’)’) AS list FROM products
文本处理函数
函数 用途 示例 备注 Upper() 字符转换为大写 Lower() 字符转换为小写 LTrim() 去除字符串左边空格 RTrim() 去除字符串右边空格 Trim() 去除字符串两边空格 Length() 返回字符串长度
时间处理函数
函数 用途 示例 备注 Now() 返回当前日期和时间 Date_Format() 返回一个格式化的日期或时间串 SELECT Date_Format(Date(Now()),’%Y%m%d’) AS time Date() 返回一个日期时间的日期部分 Time() 返回一个日期时间的时间部分
数值处理函数
函数 用途 示例 备注 Mod() 余数 Rand() 随机数 Abs() 绝对值
聚集函数
聚集函数:运行在组上，计算和返回单个值的函数 如果要聚集不同值，配合DISTINCT关键字使用 函数 用途 示例 备注 SUM() 和
1.忽略值为NULL的行
COUNT() 行数
1.使用COUNT(*)对表行数进行计数，不管表中值是否为NULL 2. 使用COUNT(column1)对特定列行数进行计数，忽略值为NULL的行
AVG() 平均值
1.忽略值为NULL的行
MAX() 最大值
1.忽略值为NULL的行
MIN() 最小值
1.忽略值为NULL的行
数据分组[GROUP BY]
GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。 GROUP BY子句可以包含任意数目的列（分组嵌套）。 如果GROUP BY子句嵌套分组，数据将在最后规定的分组上进行汇总。 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。 WITH ROLLUP关键字，可以得到每个分组以及每个分组的汇总级别的值。 分组过滤[HAVING]
WHERE过滤行，HAVING过滤分组。 WHERE在数据分组前进行过滤，HAVING的数据分组后进行过滤。 HAVING支持所有WHERE操作符。 查询嵌套（子查询）
方法一：在WHERE子句中使用子查询，一般与IN操作符一起使用。注意：SELECT语句具有与WHERE子句中相同数目的列（通常单列匹配）。 方法二：在SELECT语句中使用，创建计算字段。 联结
主键：表中标识每行。
自联结
与自身表进行联结，使用表别名。
内部联结/等值联结[[INNER] JOIN]
平等的联结关系。
SELECT vendors.vend_name,products.prod_name,products.prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vendors.vend_name;
SELECT vendors.vend_name,products.prod_name,products.prod_price FROM products JOIN vendors ON vendors.vend_id = products.vend_id ORDER BY vendors.vend_name;
自然联结
等值联结中消除多次出现的列（被联结的列）。
外部联结[OUTER JOIN]
联结包含了那些在相关表中没有关联行的行。
类型 用途 示例 备注 LEFT OUTER JOIN 左外联结 选择左边表中所有行，右边表中没内容的行使用NULL替代 RIGHT OUTER JOIN 右外联结 选择右边表中所有行，左边表中没内容的行使用NULL替代 FULL OUTER JOIN 全外联结 选择两边表中所有行，表中没内容的行使用NULL替代
交叉联结[CROSS JOIN]
交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
组合查询[UNION]
组合多条SELECT语句查询结果。
应用场景
类型 用途 示例 备注 UNION 去除重复行 SELECT * FROM products WHERE vend_id > 1003 UNION SELECT * FROM products WHERE prod_price > 10 UNION ALL 包含重复行 SELECT * FROM products WHERE vend_id > 1003 UNION ALL SELECT * FROM products WHERE prod_price > 10
SELECT子句顺序
SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT
改[UPDATE&SET]
谨慎使用，防止批量更新表中数据（多行），用WHERE语句限制。 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。使用IGNORE关键字可以屏蔽该规则（该关键字可以应用于DELETE等）：UPDATE IGNORE table SET… 用于删除某个列的值：SET c1=NULL。 设置多个列SET c1=v1,c2=v2。 增[INSERT&VALUES]
总是使用列名。 使用LOW_PRIORITY关键字降低MYSQL处理该语句优先级：INSERT LOW_PRIORITY INTO… 类型 用途 示例 备注 INSERT INTO table VALUES(…) 插入完整行 INSERT INTO customers VALUES(NULL, ‘Chen Xi Lin’, ‘Futian Distinct’, ‘Shenzhen’, ‘SZ’, 518034, ‘CHINA’, ‘BAIDU’, ‘chenxilinsidney@gmail.com’)
1.避免使用，可维护性差，表结构更新时会失效
INSERT INTO table(c1,c2) VALUES(…) 插入行的一部分 INSERT INTO customers(cust_name) VALUES(‘Chen ADD’)
1.提倡使用
INSERT INTO table(c1,c2) VALUES(…),(…) 插入多行 INSERT INTO customers(cust_name) VALUES(‘Chen ADD’),(‘XI’)
1.之间用逗号分隔，一个圆括号内为一组数据2.相比使用多条INSERT语句，性能更优
INSERT INTO table1(c1,c2) SELECT c3,c4 FROM table2 插入来自其它表的数据。
1.不要求列名匹配，采用SELECT结果的列中的位置先后顺序作为插入规则插入数据
删[DELETE]
谨慎使用，防止批量删除表中数据（多行），用WHERE语句限制。 DELETE删除整行数据，删除某列的数据使用UPDATE语句。 DELETE不需要列名或通配符。 删除所有数据：DELETE FROM table，使用TRUNCATE table 性能更优（原因：删除原来表并重新建一个一样的表，而不是逐行删除） 表操作
IF NOT EXISTS语句修饰DROP/CREATE TABLE等。 主键可以由多个列组成，PRIMARY KEY(c1,c2) 3.
DROP TABLE IF EXISTS news_list;CREATE TABLE IF NOT EXISTS news_list(
news_id BIGINT(20 ) NOT NULL AUTO_INCREMENT COMMENT '新闻id' ,
ts timestamp NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '最后一次操作时间戳' ,
url VARCHAR (1024 ) NOT NULL COMMENT '新闻地址' ,
redirect_url VARCHAR (1024 ) NOT NULL DEFAULT '' COMMENT '新闻重定向后地址' ,
source_id BIGINT(20 ) NOT NULL DEFAULT 0 COMMENT '来自新闻源id' ,
spider_time timestamp NOT NULL DEFAULT current_timestamp COMMENT '新闻抓取时间' ,
title VARCHAR (1024 ) NOT NULL DEFAULT '' COMMENT '新闻标题' ,
time VARCHAR (256 ) NOT NULL DEFAULT '' COMMENT '新闻发布时间' ,
editor VARCHAR (256 ) NOT NULL DEFAULT '' COMMENT '新闻作者' ,
description TEXT NOT NULL COMMENT '新闻描述' ,
page LONGTEXT NOT NULL COMMENT '新闻url内容' ,
property TEXT NOT NULL COMMENT '新闻自定义属性' ,
PRIMARY KEY (news_id),
FOREIGN KEY fk_source_id(source_id) REFERENCES news_source(source_id),
UNIQUE KEY idx_url(url) using btree
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8 COMMENT '新闻数据表' ;类型 用途 示例 备注 SHOW COLUMNS FROM table 查看表描述 DESC table CREATE TABLE table 创建表 ALTER TABLE table1 ADD c1 INT… 更新表结构，增加列 ALTER TABLE table1 DROP COLUMN c1 更新表结构，删除列 ALTER TABLE table1 ADD CONSTRAINT fk FOREIGN KEY c1 REFERENCES table2(c2) 更新表结构，增加外键 DROP TABLE table 删除表 RENAME table1 TO table2 重命名表
引擎类型 用途 示例 备注 InnoDB 支持事务处理引擎，不支持全文本搜索 MyISAM 不支持事务处理引擎，支持全文本搜索，性能高 MEMORY 功能等同于MyISAM，数据放于内存中，速度快
视图
视图指虚拟表，用于检索。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
优点
类型 用途 示例 备注 SHOW CREATE VIEW view 查看视图创建描述 查看所有：SELECT * from information_schema.VIEWS CREATE VIEW view 创建视图 CREATE OR REPLACE VIEW view 更新视图 另一种方法：先DROP再CREATE DROP VIEW view 删除视图
存储过程
储存过程是一条或多条MySQL语句的集合。
优点
类型 用途 示例 备注 SHOW CREATE PROCEDURE procedure 查看存储过程 查看所有:SHOW PROCEDURE STATUS CALL procedure(@v1,@v2) 调用存储过程 CREATE PROCEDURE procedure(IN/OUT/INOUT v1) BEGIN … END 创建存储过程 DROP PROCEDURE procedure 删除存储过程
游标
在检索出来的行中前进或后退一行或多行。
类型 用途 示例 备注 DECLARE curser1 CURSER FOR … 为某查询结果创建游标 OPEN curser1 打开游标 FETCH curser1 移动游标 OPEN curser1 关闭游标
触发器[TRIGGER]
某条语句（或某些语句）在事件发生时自动执行。
应用场景：
响应的活动：DELETE/INSERT/UPDATE语句。 执行时间：BEFORE or AFTER关键字。 事务处理
可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
应用场景：语句执行过程中发生故障。
用户管理[USER&GRANT]
用户USER创建、密码更新、删除； 用户GRANT权限查看、更新、删除； TIPS
DELIMITER 命令修改MYSQL命令行结束符，用于输入内部含有;符号的语句集合。 所有MySQL变量都必须以@开始。 约定
SQL语句分号(;)结尾； 所有SQL关键字使用大写，所有列和表名使用小写；（虽然MYSQL对关键字不区分大小写） SQL语句分成多行排行，便于阅读；（虽然MYSQL处理语句时忽略会所有空格）
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
