SQL语句基础 数据库 选择数据库
展示数据库
展示表
展示表列 1 2 SHOW COLUMNS FROM customers; DESCRIBE customers;
检索数据 检索列 1 2 SELECT prod_name, prod_name FROM products;
检索不重复的行 1 2 SELECT DISTINCT vend_idFROM products
限制结果(返回前几行或第几行) 1 2 3 SELECT prod_nameFROM productsLIMIT 5 ,5
LIMIT的两种情况
LIMIT 检索的开始行 行数
LIMIT 行数()从第一行开始
排序数据 1 2 3 SELECT prod_nameFROM productsORDER BY prod_name
按多个列排序 1 2 3 SELECT prod_nameFROM productsORDER BY prod_name, prod_price
逆序 1 2 3 SELECT prod_nameFROM productsORDER BY prod_name DESC
顺序关键字为ASC,顺序逆序关键字只应用到直接位于其前面的列名
过滤数据 1 2 3 SELECT prod_nameFROM productsWHERE prod_name = test
WHERE子句操作符 1 =, <>, !=, <, <=, >, >=, BETWEEN, IS NULL
在过滤数据时,无论匹配过滤还是不匹配过滤都不会返回NULL值的行
组合过滤关键字
AND的优先级比OR高
1 WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10
IN表示范围中的每个条件都可以进行匹配
1 WHERE vend_id IN (1002, 1003)
相当于
1 WHERE vned_id = 1002 OR vwnd_id = 1003
IN可以包含其他SELECT语句
用通配符过滤 通配符:匹配值的一部分的特殊字符
LIKE操作符 %通配符 %表示任何字符出现任意次数
1 2 3 4 5 6 7 8 所有以jet开头的产品 SELECT prod_id, prod_nameFROM productsWHERE prod_name LIKE 'jet%' 在任何位置包含文本jet的值 ... WHERE prod_name LIKE '%jet%'
区分大小写
%能匹配0、1或多个字符
尾空格会干扰通配符匹配,解决方法:在搜索模式最后附加一个%
%通配符不能匹配NULL
_通配符 用法类似%,但只匹配单个字符
1 2 3 4 产品名形如 1 ton anvil, e ton_anvil SELECT prod_id, prod_nameFROM productsWHERE prod_name LIKE '_ ton anvil'
通配符搜索的处理较慢,不要过度使用通配符,若其他操作符能做到,就用其他操作符
除非必要,否则不要把通配符用在搜索模式的开始处,搜索起来最慢
用正则表达式进行搜索 REGEXP REGEXP后所跟的东西作为正则表达式
1 2 3 SELECT prod_nameFROM productsWHERE prod_name REGEXP '1000'
. .在正则表达式中表示匹配任意一个字符
LIKE和REGEXP的区别:
LIKE匹配整个列(整个串),REGEXP在列值内进行匹配(子串)
正则表达式不区别大小写
| 用于OR匹配
1 WHERE prod_name REGEXP '1000|2000|3000'
[] 匹配几个字符之一
1 WHERE prod_name REGEXP '[123] Ton'
[-] 匹配范围
1 2 3 WHERE prod_name REGEXP '[1-5]' 等价于 WHERE prod_name REGEXP ‘1|2|3|4|5'
特殊字符 1 2 3 \\.表示查找. \\-表示查找- \\\表示查找\
空白元字符
元字符
说明
\ \ f
换页
\ \ n
换行
\ \ r
回车
\ \ t
制表
\ \ v
纵向制表
匹配字符类
类
说明
[:alnum:]
任意字母和数字(同[a-zA-Z0-9])
[:alpha:]
任意字符(同[a-zA-Z])
[:blank:]
空格和制表(同[\t])
[:cntrl:]
ASCII控制字符(ASCII 0到31和127)
[:digit:]
任意数字(同[0-9])
[:graph:]
与[:print:]相同,但不包括空格
[:lower:]
任意小写字母(同[a-z])
[:print:]
任意可打印字符
[:punct:]
既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]
包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:]
任意大写字母(同[A-Z])
[:xdigit:]
任意十六进制数字(同[a-fA-F0-9])
重复元字符
元字符
说明
*
0个或多个匹配
+
1个或多个匹配(等于{1,})
?
0个或1个匹配(等于{0,1})
{n}
指定数目的匹配
{n,}
不少于指定数目的匹配
{n,m}
匹配数目的范围(m不超过255)
1 2 3 4 匹配形如 TNT (1 stick) TNT (5 sticks) REGEXP '\\([0-9] sticks?\\)'
1 2 匹配连在一起的4位数字 REGEXP '[[:digit:]]{4}'
定位符 匹配特定位置的文本
元字符
说明
^
文本的开始
$
文本的结尾
[[:<:]]
词的开始
[[:>:]]
词的结尾
1 2 匹配以数字或.开头的列值 REGEXP '^[0-9\\.]'
计算字段 拼接字段 Concat()拼接两个列,各个串之间用逗号分隔
1 2 3 SELECT Concat (vend_name, '(' , vend_country, ')' )FROM vendorsORDER BY vend_name
多数DBMS用+或||来实现拼接,MySQL用Concat()实现
RTrim()用于删除数据右侧多余的空格来整理数据,LTrim()删除左侧多余空格
使用别名 AS赋予别名
1 2 3 SELECT Concat (vend_name, '(' , vend_country, ')' ) AS vend_titleFROM vendorsORDER BY vend_name
算术运算 可以在SELECT中使用加减乘除
1 2 3 SELECT quantity*item AS expanded_priceFROM orderitemsWHERE order_num = 20005
函数 文本处理函数
函数
说明
Left()
返回串左边的字符
Length()
返回串的长度
Locate()
找出串的一个子串
Lower()
将串转换为小写
LTrim()
去掉串左边的空格
Right()
返回串右边的字符
RTrim()
去掉串右边的空格
Soundex()
返回串的SOUNDEX值
SubString()
返回子串的字符
Upper()
将串转换为大写
日期和时间处理函数
函数
说明
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()
返回一个随机数
Sin()
返回一个角度的正弦
Sqrt()
返回一个数的平方根
Tan()
返回一个角度的正切
汇总数据 聚集函数
函数
说明
AVG()
返回某列的平均值
COUNT()
返回某列的行数
MAX()
返回某列的最大值
MIN()
返回某列的最小值
SUM()
返回某列值之和
如果指定列名,则指定列的值为空的行被COUNT()函数忽略,但如果COUNT()函数中用的是*,则不忽略
MAX()、MIN()、SUM()函数忽略值为NULL的行
聚集不同的值 指定DISTINCT参数
1 2 3 SELECT AVG (DISTINCT prod_price) AS avg_priceFROM productsWHERE vend_id = 1003
DISTINCT必须使用列名,不能用于计算或表达式或*
DISTINCT一般只用于COUNT(),用于MIN(),MAX()无意义
分组数据 分组将数据分为多个逻辑组,便于对每个组进行聚集计算
创建分组 用GROUP BY创建分组
1 2 3 SELECT vend_id, COUNT (*) AS num_prodsFROM productsGROUP BY vend_id
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前
如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组
过滤分组 HAVING过滤分组(WHERE过滤行)
1 2 3 4 SELECT cust_id, COUNT (*) AS ordersFROM ordersGROUP BY vend_idHAING COUNT (*) >= 2
子查询 1 2 3 4 5 SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' )
联结表 内部联结 1 2 3 SELECT vend_name, prod_name, prod_priceFROM vendors v INNER JOIN products pON v.vend_id = p.vend_id
创建高级联结 自联结 1 2 3 4 SELECT p1.prod_id, p1.prod_nameFROM products AS p1, products AS p2WHERE p1.vend_id = p2.vend_idAND p2.prod_id = 'DTNTR'
外部联结 包含在相关表中没有关联行的行:OUT JOIN
在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表
LEFT JOIN从左边的表中选择所有行
RIGHT JOIN从右边的表中选择所有行
1 2 3 SELECT customers.cust_id, orders.order_numFROM customers RIGHT OUTER JOIN ordersON orders.cust_id = customers.cust_id
组合查询 合并结果集 在各语句之间放上关键字UNION
1 2 3 4 5 6 7 SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id IN (1001 , 1002 )
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)
UNION从查询结果集中自动去除了重复的行,如果想返回所有匹配行,可使用UNION ALL而不是UNION
排序组合查询 在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后
1 2 3 4 5 6 7 8 SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id IN (1001 , 1002 )ORDER BY vend_id, prod_price
全文本搜索 两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本搜索,而后者不支持
正则搜索的限制:
全文本搜索中数据是索引的,所以速度很快
启用全文本搜索支持 在CREATE时FULLTEXT(note_text)
进行全文本搜索 Match()指定被搜索的列,Against()指定要使用的搜索表达式
1 2 3 4 5 SELECT note_textFROM productnotesWHERE Match (note_text) Against('rabbit' )可以用LIKE WHERE note_text LIKE '%rabbit%'
传递给Match()的值必须与FULLTEXT()定义的相同,若指定多个列,就必须列出它们(而且次序正确)
搜索不区分大小写
插入数据 插入完整的行 1 2 3 4 5 6 7 8 9 10 INSERT INTO customersVALUES (NULL , 'Pep E' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' , NULL , NULL )
没有输出
可以省略都写列,但必须允许为NULL或给出默认值
插入多个行 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 INSERT INTO customersVALUES (NULL , 'Pep E' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' , NULL , NULL ), (NULL , 'Pep E' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' , NULL , NULL ),
插入检索的数据 不一定要求列名匹配
更新和删除数据 更新数据 更新时注意不要省略WHERE子句,否则会更新所有行
1 2 3 4 UPDATE customersSET cust_email = 'elmer@qq.com' cust_name = 'Wynton' WHERE cust_id = 10005
可以使用子查询,使得能用检索出来的数据更新列数据
IGNORE可以在更新多行时,即使发生错误也能继续更新,否则整个UPDATE操作将被取消
删除某个列的值可以设置其为NULL(若允许)
删除数据 1 2 DELETE FROM customersWHERE cust_id = 10006
更新和删除时先用SELECT进行测试
创建和操纵表 创建表 1 2 3 4 5 6 7 8 CREATE TABLE customers( cust_id int NOT NULL AUTO_INCREMENT, cust_name char (50 ) NOT NULL , cust_address char (50 ) NULL , cust_city char (50 ) NULL DEFAULT 'Los' , PRIMARY KEY (cust_id) )ENGINE =InnoDB
AUTO_INCREMENT表示,本列每当增加一行时自动增量
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如,通过使它成为主键)
默认值用DEFAULT指定
ENGINE指定引擎类型
更新表定义 1 2 3 ALTER TABLE vendorsADD vend_phone CHAR (20 )DROP COLUMN vend_phone
删除表
重命名表 1 RENAME TABLE customers TO customers2
使用视图 视图常见应用
重用SQL语句
简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
使用视图
视图用CREATE VIEW语句来创建
使用SHOW CREATE VIEW viewname;来查看创建视图的语句
用DROP删除视图,其语法为DROP VIEW viewname;
更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE ORREPLACE VIEW
1 2 3 4 CREATE VIEW productcustomers AS SELECT cust_name,...FROM customers,...WHERE ...
使用视图可以一次性编写基础SQL,根据需要多次使用
利用视图格式化检索出的数据 1 2 3 4 5 CREATE VIEW productcustomers AS SELECT Concat (RTrim (cust_name),...) AS vend_titleFROM customers,...WHERE ...ORDER BY ...
利用视图过滤不想要的数据 1 2 3 4 CREATE VIEW customereamillist AS SELECT cust_id, cust_name, cust_emailFROM customersWHERE cust_email IS NOT NULL
利用视图与计算字段 …
使用存储过程 优点:简单、安全、高性能
执行存储过程 1 2 3 CALL productpricing(@pricelow @pricehigh @priceaverage)
创建存储过程 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE PROCEDURE productpricing( OUT pl DECIMAL (8 , 2 ) OUT ph DECIMAL (8 , 2 ) OUT pa DECIMAL (8 , 2 ) ) BEGIN SELECT MIN (prod_price) INTO pl FROM products; SELECT MAX (prod_price) INTO ph FROM products; SELECT AVG (prod_price) INTO pa FROM products; END ;
使用游标 使用游标的原因 使用简单的SELECT语句,例如,没有办法得到第一行、下一行或前10行,也不存在每次一行 地处理所有行的简单方法(相对于成批地处理它们) 有时,需要在检索出来的行中前进或后退一行或多行,游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集
创建游标 1 2 3 4 5 6 CREATE PROCEDURE processorders()BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END ;
打开和关闭游标 1 2 OPEN ordernumbers CLOSE ordernumbers
使用游标数据 将数据检索到一个o的局部声明的变量中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE PROCEDURE processorders()BEGIN DECLARE o INT ; DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; OPEN ordernumbers; FETCH ordernumbers INTO o; CLOSE ordernumbers; END ;
或
1 2 3 4 5 ... REPEAT FETCH ordernumbers INTO o; UNTIL done END REPEAT ; ...
加入条件语句的复杂版本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE PROCEDURE processorders()BEGIN DECLARE done BOOLEAN DEFAULT 0 ; DECLARE 0 INT ; DECLARE t DECIMAL (8 ,2 ); DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1 ; CREATE TABLE IF NOT EXITS ordertotals OPEN ordernumbers; REPEAT FETCH orders INTO o; CALL ordertotal(o,1 ,t) INSERT INTO ordertotals(order_num, total) VALUES (o,t); UNTIL done END REPEAT ; CLOSE ordernumbers; END ;
使用触发器 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句
创建触发器 1 2 CREATE TRIGGER newproduct AFTER INSERT ON productsFOR EACH ROW SELECT 'Product added'
删除触发器
INSERT触发器
在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行
在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值)
对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值
1 2 CREATE TRIGGER neworder AFTER INSERT ON ordersFOR EACH ROW SELECT NEW.order_num
DELETE触发器
在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行
1 2 3 4 5 CREATE TRIGGER deleteorder BEFORE DELETE ON ordersFOR EACH ROW BEGIN INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES (OLD.order_num, OLD.order_date, OLD.cust_id)
UPDATE触发器
在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值
在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值)
OLD中的值全都是只读的,不能更新
1 2 CREATE TRIGGER updatevendor BEFORE UPDATE ON vendorsFOR EACH ROW SET NEW.vend_state = Upper (NEW.vend_state)
使用事务管理 事务处理可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行
标识事务的开始
ROLLBACK回退SQL语句 1 2 3 4 5 6 SELECT * FROM ordertotals;START TRANSACTION ;DELETE FROM ordertotals;SELECT * FROM ordertotals;ROLLBACK ;SSELECT * FROM ordertotals;
使用COMMIT 1 2 3 4 START TRANSACTION DELETE FROM orderitems WHERE order_num = 20010 ;DELETE FROM orders WHERE order_num = 20010 ;COMMIT ;
使用保留点