子查询是指在另一个查询语句中的SELECT子句。
例句:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中,SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。
所以,我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。
行级子查询
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
行级子查询的返回结果最多为一行。
行级子查询的返回结果最多为一行。
优化子查询
-- 创建数据表
CREATE TABLE IF NOT EXISTS tdb_goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL, goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL, goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);
-- 写入记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT)
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT)
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT)
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT)
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT)
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
-- 求所有电脑产品的平均价格,并且保留两位小数,AVG,MAX,MIN、COUNT、SUM为聚合函数
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
-- 查询所有价格大于平均价格的商品,并且按价格降序排序
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC;
-- 使用子查询来实现
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods)
ORDER BY goods_price DESC;
WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods)
ORDER BY goods_price DESC;
-- 查询类型为“超记本”的商品价格
SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
-- 查询价格大于或等于"超级本"价格的商品,并且按价格降序排列
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;
-- = ANY 或 = SOME 等价于 IN
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;
-- 创建“商品分类”表
此处由于数据表中分类信息重复冗余,占用内存过大,故抽象化出来一个商品分类表,用于储存分类信息
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40)
cate_name VARCHAR(40)
);
-- 查询tdb_goods表的所有记录,并且按"类别"分组
抽出数据表中的分类信息,按类别进行分组,主键id自动赋值
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
-- 将分组结果写入到tdb_goods_cates数据表
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; 将分组信息写入商品分类表
-- 通过tdb_goods_cates数据表来更新tdb_goods表
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name 将数据表的分类信息和商品分类表的name信息内设置内连接
SET goods_cate = cate_id ; 将原数据表中cate改为id
-- 通过CREATE...SELECT来创建数据表并且同时写入记录
-- SELECT brand_name FROM tdb_goods GROUP BY brand_name;
CREATE TABLE tdb_goods_brands ( 一步到位创建数据表同时写入数据表
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
-- 通过tdb_goods_brands数据表来更新tdb_goods数据表(错误)
brand_name属性重名了,只能把属性起别名来更新刚创建的数据表
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name
SET brand_name = brand_id;
-- Column 'brand_name' in field list is ambigous
-- 正确
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
SET g.brand_name = b.brand_id;
-- 查看tdb_goods的数据表结构
DESC tdb_goods; DESC可查看数据表结构
-- 通过ALTER TABLE语句修改数据表结构
操作结束后,尽量更改数据表结构中被抽象出来的两个属性的类型,这样才能真正的实现
ALTER TABLE tdb_goods 对数据表的优化操作,节省内存
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
-- 分别在tdb_goods_cates和tdb_goods_brands表插入记录
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
-- 在tdb_goods数据表写入任意记录
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849'); 未通过物理外键,默认通过逻辑外
-- 查询所有商品的详细信息(通过内连接实现)
//连接须将左表/右表的所需属性列在SELECT下
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id 前面已经将goods_cate更改为cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;
-- 查询所有商品的详细信息(通过左外连接实现)
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id //左表中全部和右表中符合条件的,如果没有显示为null,这里打印机的右表应为null,因为他是后加入的
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G; //没有被归类 此处进行了两次左外连接
-- 查询所有商品的详细信息(通过右外连接实现)
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g //右表的全部和走镖符合条件的,路由器交换机网卡在这里显示了,打印机没了
RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;
--多表连接(表的合成)
//与外键相反外键是表的拆分
SELECT goods_id,good_name,brands_name FROM tb_goods AS g
INNER JOIN tb_cate AS c ON g.cate_id=c.cate_id
INNER JOIN tb_brand AS b ON g.cate_id=b.brand_id
-- 无限分类的数据表设计
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 //父类ID
);
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 //父类ID
);
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
-- 查找所有分类及其父类
//符合条件就连接 子写前面,父写后面即查找父类
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id; //表的自身连接,必须起别名,打印出的即为SELECT后的三个属性
-- 查找所有分类及其子类
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; //父写前面,子写后面 即查找子类
-- 查找所有分类及其子类的数目
//count(s.type_name) AS children_count起别名
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;//排序
-- 为tdb_goods_types添加child_count字段
ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;
-- 将刚才查询到的子类数量更新到tdb_goods_types数据表
UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id
GROUP BY p.type_name
GROUP BY p.type_name
ORDER BY p.type_id ) AS t2
ON t1.type_id = t2.type_id
SET t1.child_count = t2.children_count;
-- 复制编号为19,20的两条记录
SELECT * FROM tdb_goods WHERE goods_id IN (19,20);
-- INSERT ... SELECT实现复制
INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);
-- 查找重复记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2; //HAVING分组条件 count数量大于2即为重复
-- 删除重复记录
DELETE t1(记录) FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE(删除id较大的) t1.goods_id > t2.goods_id;
--字符函数
CONCAT() 字符连接 SELECT CONCAT('A','B,','C');
CONCAT_WS() ('|','a','b')使用指定分隔符连接字符 SELECT CONCAT_WS('-','A','S');
FORMAT() 数字格式化 SLECT FORMAT(12560.74); == 12,560,75 千分位分割格式化
LOWER() 转换为小写字母 SELECT LOWER('MYSQL');
UPPER() 转换为大写字母 SELECT LOWER(‘mysql’)
LEFT() RIGHT() 获取左侧/右侧字符数起 SELECT LEFT('MYSQL',2); == MY
LENGTH() 获取当前字符串长度 SELECT LENGTH('MYSQL'); ==5 空格也算长度
LTRIM()删除首空格 SELECT LENGTH(LTRIM(' MYSQL '));
RTRIM() 删除尾空格
TRIM() 删除首尾空格 SELECT TRIM(LEADING/ TRAILING/BOTH'?' FROM '??MYSQL);删除??mysql中的首/尾/全部??字符
SUBSTRING() 进行字符串截取 SELECT SBUSTRING('MYSQL','1','2') 从mysql第一位截取2个mysql中从1数起,若未指定截取数量,全部截取,若截取位置为负,倒着截取
REPLACE() 替换字符 SELECT REPLCAE('??MY??SQL???','??','');将my??sql中??换为空格
[NOT] LIKE 进行模式匹配 SELECT 'MYSQL' LIKE '%M%';返回BOOLEAN型,%代表通配符任意字符,_代表任意一个字符,只要中间有m的会被查找到
--数值运算函数
CEIL() SELECT SEIL(3.01); == 4进一取整
FLOOR() 舍一取整
DIV 整型除法/取整 3 DIV 4
MOD 取余 5 MOD 3
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取 TRUNCATE(125.89,2) =125.8 截取两位小数
--比较运算符
[NOT]BETWEEN...AND..在范围之内 SELECT 15 BETWEEN 1 AND 33;==1
[NOT]IN() 在列出值范围内 SELECT 10 IN(5,10,15,20); == 1
IS [NOT]NULL SELECT NULL IS NULL == 1;SELECT *FROM test WHERE first_name IS NULL
--时间日期函数
NOW() 当前时间
CURDATE() 当前日期
CURTIME() 当前时间
DATR_ADD() 日期变化增减 SELECT DATE_ADD('2014.3.12',INTERVAL +/-365DAY)加365天
DATE_FORMAT 格式化SELECT DATEFORMAT('2014-3-2','%m/%d/%Y') = 03/02/2014
DATEDIFF() 日期差值 SELECT DATRDIFF('2015.2.23','2015.2.12');
--信息函数
CONNECTION_ID() 返回当前连接的ID/线程的ID
DATABASE() 当前打开的数据库
LAST_INSERT_ID() 返回最后插入的记录的ID,若同时写入多条记录,则只返回第一条写入记录的ID
USER() 当前用户
VERSION() 版本号
--聚合函数--只有一个返回值
AVG() 平均值
COUNT() 计数
MAX()
MIN()
SUM()
--加密函数
MD5() 信息摘要算法 SELECT MD5('ADMIN');
PASSWORD() SET PASSWORD = PASSWORD('XXXX')改本地mysql密码
--自定义函数
自定义函数两个必须条件:1.参数(可以无参) 2.返回值
创建自定义函数(应用较少):
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}routing_body(方法体)
函数体由合法的SQL语句构成,可以使简单的SELECT或INSERT语句,可以包含复合结构BEGIN...END,可以包含声明流程控制
无参自定义函数:
mysql> CREATE FUNCTION f1() returns VARCHAR(30)
-> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %h点:%i分:%s秒');
mysql> SELECT f1();
++++++2016年10月16日 12点:21分:29秒+++++++
带参自定义函数:
mysql> create function f2(num1 smallint unsigned,num2 smallint unsigned)
-> RETURNS FLOAT(10,2) UNSIGNED //returns设置返回值类型
-> RETURN (num1+num2)/2;
复合结构自定义函数:
DELIMITER //; //设置命令通过双斜线来结束
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test (username) VALUES (username);
RETURN LAST_INSERT_ID();
END
//
删除自定义函数:DROP FUNCTION [IF EXISTS] function_name;
--存储引擎
SHOW CREATE table table_name;查看数据表的创建命令
MYSQL可以将数据以不同的技术存储在内存中,该技术称为存储引擎。
每种存储引擎存储机制,索引技巧,锁定水平均有不同,提供广泛不同的功能。
主要有:
MyISAM InnoDB Memory CSV Archive /以下为各种存储引擎的特点
并发控制:多个连接同时对记录进行修改时保证数据的一致性和完整性。也是lock系统
锁:共享锁:同一时间段内,多个用户可以读取听一个资源,读取中数据不会发生变化。
排他锁:任何时间只有一个用户能写入资源,操作进行是会阻塞其他读锁写锁操作。加锁会增加系统开销,应权衡线程安全和性能。
锁颗粒:表锁:开销最小的锁策略。锁表。
行锁:开销最大的锁策略。支持最大并发操作,锁行。
事务:事务用于保证数据库的完整性。类似备份。
事务的特征:原子性 一致性 隔离性 持久性。
外键:保证数据一致性的策略。
索引:对数据表的一列或多列的值进行排序的一种结构。目录。
修改存储引擎:1.修改ini配置文件 2.通过创建数据表命令实现
CREATE TABLE tbl_name(
...
)ENGINE=engine_name;
3.创建表后ALTER修改
ALTER TABLE tbl_name ENGINE [=] engine_name;
--存储过程:
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并且作为一个单元处理。
优点:增强了SQL的语句功能和灵活性;实现较快的速度,第一次相同,多次执行不再进行语法分析;减少网络流量;类似封装的函数
1.存储过程的能增强SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算;
2.在运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。
以上说明了一些存储过程的好处,但是这不意味这什么地方都适合用,能用一条select语句查询出的结果写成存储过程只能降低效率,这样是没有好处的。要在批量操作,逻辑复杂的业务中使用存储过程。不能滥用。
--语法结构:
CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic...]routine_body
proc_parament: [IN|OUT|INOUT]para_name type;IN:参数的值必须在调用过程中指定,但无返回
OUT:该参数可以在被储存过程中被改变,可以返回
INOUT:该参数在调用时指定,可以被改变和返回
--创建不带参存储过程
CREATE PROCEDURE sp1() SLEECTION VERSION();
调用 CALL sp_name(paramter[,...]); (参数)
--创建带IN参数的存储过程
DELINITER //
CREATE PROCEDURE remocveUserById(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=id;
END
//
调用: CALL remocveUserById(3);
删除存储过程:DROP PROCEDURE [IF EXISTS] sp_name;存储过程的修改只能修改注释、内容类型,而不能修改过程体,如果要修改过程体,应该先删掉存储过程再重新创建
--创建带OUT参数的存储过程
DELIMITER //
CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName; //SELECT INTO
END0
//
调用:CALL removerUserAndReturnUserName(10,@nums); //@nums为一个变量,通过它接收返回值BEGIN END语句块内为局部变量
SET @I=7; //声明用户变量(成员变量)
--IN OUT的存储过程
DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age; //注意变量不同
SELECT ROW_COUNT() INTO delNums; //返回插入、删除和更新的受影响的记录总数
SELECT COUNT(id) FROM users INTO leftNums;
END
//
DELIMITER ;
SELECT * FROM users;
SELECT COUNT(id) FROM users WHERE age = 20;
CALL rmUserByAgeAndRtInfos(20, @a, @b);
SELECT * FROM users;
SELECT COUNT(id) FROM users WHERE age = 23;
CALL rmUserByAgeAndRtInfos(23, @a, @b);
SELECT @a, @b;
--存储过程与自定义函数区别
存储过程实现功能较为复杂,函数的针对性更强
存储过程可以有多个返回值,函数只能有一个
存储过程一般独立运行,函数可以作为其他SQL语句的额组成部分你来执行
自定义函数较为少用,存储过程较为常用,类似方法