一.Limit的另外用法
关键字: OFFSET
SELECT
*
FROM
productnotes
LIMIT 2 OFFSET 2;
二.正则表达式
关键字: REGEXP
SELECT
*
FROM
products
WHERE
-- 匹配1000或者2000
prod_name REGEXP '1000|2000'
ORDER BY
prod_name;
SELECT
*
FROM
products
WHERE
-- 1 Ton 或 2 Ton 或 3 Ton 都将被匹配
prod_name REGEXP '[123] Ton'
ORDER BY
prod_name;
SELECT
*
FROM
products
WHERE
-- 1 或 2 或 3 Ton 都将被匹配,除非把字符 | 括在一个集合中,否则将应用于整个字符串
prod_name REGEXP '1|2|3 Ton'
ORDER BY
prod_name;
正则表达式中的转义字符
SELECT
vend_name
FROM
vendors
WHERE
-- 通过\\ 对.进行转义,如果要想对\进行转义,需要 \\\ 进行转义
vend_name REGEXP '\\.'
ORDER BY
vend_name;
三.函数的使用
3.1CONCAT( )
Mysql用CONCAT( )拼接数据,ORACLE用 || 来拼接数据
SELECT
CONCAT( vend_name, ' (', vend_country, ')' ) 拼接字段
FROM
vendors
ORDER BY
vend_name;
拼接字段
ACME (USA)
Anvils R Us (USA)
Furball Inc. (USA)
Jet Set (England)
Jouets Et Ours (France)
LT Supplies (USA)
3.2 RTRIM( )
去除空格
SELECT
CONCAT( vend_name, ' (', RTRIM( vend_country ), ')' ) 拼接字段
FROM
vendors
ORDER BY
vend_name;
3.3 UPPER( )
字符转换为大写
SELECT
vend_name,
UPPER( vend_name ) AS vend_name_upcase
FROM
vendors
ORDER BY
vend_name;
3.4 Date( )
日期处理函数
SELECT
cust_id,
order_num
FROM
orders
WHERE
-- 通过Date()函数可以从包含年月日时分秒的日期中只提取出年月日
Date( order_date ) = '2005-09-01'
查询2005年9月份的所有订单
SELECT
cust_id,
order_num
FROM
orders
WHERE
Date( order_date ) BETWEEN '2005-09-01'
AND '2005-09-30'
-- 更加优雅的写法,不需要关心是否有闰月或者一个月是30天还是31天
SELECT
cust_id,
order_num
FROM
orders
WHERE
YEAR ( order_date ) = 2005
AND MONTH ( order_date ) = 9
3.5 CASE WHEN
列出所有学生的成绩 格式:学号 姓名 语文 数学 英语 物理
SELECT
student.sid 学号,
student.sname 姓名,
-- 把sum()函数替换为max()函数也是可以的,聚合函数主要就是为了组合才添加的
sum( CASE course.cname WHEN '语文' THEN sc.score ELSE 0 END ) '语文',
sum( CASE course.cname WHEN '数学' THEN sc.score ELSE 0 END ) '数学',
sum( CASE course.cname WHEN '英语' THEN sc.score ELSE 0 END ) '英语',
sum( CASE course.cname WHEN '物理' THEN sc.score ELSE 0 END ) '物理'
FROM
student,
sc,
course
WHERE
student.sid = sc.scid
AND sc.cid = course.cid
GROUP BY
sc.scid;
SELECT
st.stuid,
st.stunm,
MAX( CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',
MAX( CASE c.coursenm WHEN '新视野英语' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '新视野英语',
MAX( CASE c.coursenm WHEN '离散数学' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '离散数学',
MAX( CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '概率论与数理统计',
MAX( CASE c.coursenm WHEN '线性代数' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '线性代数',
MAX( CASE c.coursenm WHEN '高等数学(一)' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '高等数学(一)',
MAX( CASE c.coursenm WHEN '高等数学(二)' THEN ifnull( s.scores, 0 ) ELSE 0 END ) '高等数学(二)'
FROM
Student st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY
st.stuid
四.分组汇总
-- 分组汇总,计算分组之后的总的数量
SELECT
vend_id,
COUNT( * ) AS num_prods
FROM
products
GROUP BY
-- WITH ROLLUP 计算分组汇总的总的值
vend_id WITH ROLLUP;
vend_id num_prods
1001 3
1002 2
1003 7
1005 2
14 -- 14就是分组之后的汇总
五.子查询
SELECT
cust_id
FROM
orders
WHERE
order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' )
-- -------------------------------------------------------------
SELECT
cust_id,
order_num
FROM
orders
WHERE
-- 用 EXISTS 来替代 in,这两张表通过 order_num 字段产生了关联
EXISTS ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
AND orderitems.order_num = orders.order_num )
六.联结查询
6.1内部连接
JOIN ON 是 INNER JOIN ON的简写
SELECT
vend_name,
prod_name,
prod_price
FROM
vendors
-- 内部连接如果on后面不带条件的话,会报错.从而尽可能的避免了笛卡尔积的出现
INNER JOIN products ON vendors.vend_id = products.vend_id;
6.2等值联结
SELECT
vend_name,
prod_name,
prod_price
FROM
vendors,
products
-- 等值连接,如果不添加where条件的话,虽然不会报错,但是会产生笛卡尔乘积
WHERE
vendors.vend_id = products.vend_id;
6.3 外部联结
LEFT JOIN ON 是 LEFT OUTER JOIN ON的简写
SELECT
customers.cust_id,
orders.order_num
FROM
customers
-- LEFT OUTER JOIN 和 LEFT JOIN 的效果相同,LEFT JOIN 是其简写
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
七.其他知识
-- 发生错误也继续执行操作(例如插入的一条数据和字段定义的数据类型不符,如果不加ignore关键字,会全部停止执行,加上之后会跳过出错的地方,继续执行)
update ingore customers ....
-- 清除表的全部的内容(表结构还在,表还在,表的数据不在了)
TRUNCATE customers -- 本质上是删除这个表,然后根据表结构重建一遍表,因此速度快
-- InnoDB不会重新建立表，而是一行一行的删除。因此通过TRUNCATE来清除表的数据(不删除表结构),更快
DELETE FROM customers
-- 获取最后一个AUTO_INCREMENT的值,因为主键都是自动增长,通过这个函数获取自动增长的主键的值
SELECT last_insert_id()
-- 重命名一个表
RENAME TABLE customers2 TO customers
-- mysql的数据库引擎
InnoDB 是一个可靠的事务处理引擎,但是不支持全文检索
MyISAM 是一个性能极高的引擎,支持全文检索,但是不支持事务处理
MEMORY 在功能上等同于MyISAM,但是数据储存在内存(不是磁盘),速度特别快,(适合于临时表的创建和使用)
八.存储过程
8.1税率的计算
创建一个计算税率的储存过程
-- 声明存储过程和这个存储过程用到的变量
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2) -- 储存过程对外输入的变量
) COMMENT '获取加税之后的订单的总额' -- 起到注释说明的作用
BEGIN
-- 声明局部变量
DECLARE total DECIMAL(8,2);
-- 指定局部变量的默认值,假定税率为6%
DECLARE taxrate INT DEFAULT 6;
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
-- 把查询的结果存入声明的局部变量中
INTO total;
-- 如果含税的话,就需要计算税
IF taxable
THEN
SELECT total + (total/100*taxrate) INTO total;
END IF;
-- 把计算的总的结果放入对外的输出的变量中
SELECT total INTO ototal;
END;
调用存储过程
-- 调用存储过程进行税率的计算,0代表false,代表不含税
CALL ordertotal(20005,0,@total)
查询存储过程返回的结果 @total
-- 查询不含税的结果
SELECT @total;
九.游标
存储函数和游标相结合,存储函数中包含了游标
CREATE PROCEDURE processorders()
BEGIN
-- 游标的使用
-- 声明本地的变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o 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 EXISTS ordertotals (order_num INT ,total DECIMAL(8,2));
-- 打开游标
OPEN ordernumbers;
-- 准备循环所有的行
REPEAT
-- 游标一行的数据放到变量o当中
FETCH ordernumbers INTO o;
-- 调用提前准备好的存储函数
-- o是存放游标数据的变量,1表示要计算税,t表示储存函数的输出值
CALL ordertotal(o,1,t);
-- 向创建的表中插入数据
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
UNTIL done END REPEAT;
-- 关闭游标
CLOSE ordernumbers;
END;
调用存储函数
-- 调用存储函数(这个函数中有游标,并且会生成一个新的表),存储函数只有被调用了之后才会生效
CALL processorders();
从存储函数创建的新的表中查询数据
-- 从新的表中查询数据
SELECT * FROM ordertotals;
十.触发器
达到某个条件之后自动触发的SQL语句
10.1 INSERT触发器
定义一个INSERT触发器,在向表中插入数据后,会自动调用触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
-- 现在新版的mysql要求把查询到的结果保存到一个变量中
FOR EACH ROW SELECT NEW.order_num INTO @args;
向表中插入一条数据
-- 插入一条数据,由于我们使用了触发器,因此当产生新的自增长主键的时候
-- 会把自动增长的主键保存到我们定义的变量@args中
INSERT INTO orders (order_date,cust_id) VALUES(NOW(),10001);
查询我们自定义的变量,就可以获得自动增长的主键
select @args;
10.2 DELETE触发器
当我们从orders表删除数据的时候,就会把被删除的数据保存到archive_orders中(该表和orders的表结构相同)
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW -- 表示该触发器作用于每一行
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id); -- 注意这个分号,没有这个分号会报错
END;
10.3 UPDATE触发器
保证输入的所有的vend_state字段的数据都是大写的
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
10.4触发器的删除
DROP TRIGGER deleteorder;
10.5触发器的查看
-- 查看所有的触发器
show TRIGGERS
-- 根据名称查看指定的触发器
show create trigger updatevendor
十一.视图
使用视图的好处
- 重用SQL语句
- 简化复杂的SQL操作.编写成功后,可以方便的对其重用而不必知道视图的内部细节
- 使用表的组成部分,而不是整个表
- 保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示,视图可以返回与底层表的表示和格式不同的数据
视图本质上是一组SQL语句的集合,视图本身不包含数据
11.1使用视图格式化检索出数据
创建视图
-- 创建视图
CREATE VIEW vendorlocations AS -- 此处AS不能省略,否则会报错
SELECT CONCAT( RTrim( vend_name ), '(', Rtrim( vend_country ), ')' ) AS vend_title
FROM
vendors
ORDER BY
vend_name;
使用视图
-- 把视图当成普通的table表查询就可以了
SELECT * from vendorlocations;
11.2 使用视图过滤掉不需要数据
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM
customers
WHERE
-- 把email为空的数据过滤掉
cust_email IS NOT NULL;
使用视图
SELECT * FROM customeremaillist;
11.3 使用视图与计算字段
CREATE VIEW orderitemsexpanded AS
SELECT
order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM
orderitems;
使用视图
SELECT
*
FROM
orderitemsexpanded
WHERE
order_num = 20005;
十二.动态行转列
需求:查询学生的学号,姓名,课程名,分数
SELECT
student.stuid,
student.stunm,
courses.coursenm,
score.scores
FROM
student,
courses,
score
WHERE
student.stuid = score.stuid
AND score.courseno = courses.courseno;
但是我们的需求是下面这样的
12.1 静态行转列
SELECT
st.stuid,
st.stunm,
-- MAX()起到函数聚合的作用
MAX( CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',
......
FROM
student st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY
st.stuid
12.2 动态的行转列
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = '' ',
c.coursenm,
' '', s.scores, 0)) AS '' ',
c.coursenm, ' '' '
)
)
FROM courses c;
查询的结果
-- 下面是查询的结果,也就是文本,我们需要把这些文本拼接到SQL语句中
MAX(IF(c.coursenm = '大学语文', s.scores, 0)) AS '大学语文',
MAX(IF(c.coursenm = '新视野英语', s.scores, 0)) AS '新视野英语',
MAX(IF(c.coursenm = '离散数学', s.scores, 0)) AS '离散数学',
MAX(IF(c.coursenm = '概率论与数理统计', s.scores, 0)) AS '概率论与数理统计',
MAX(IF(c.coursenm = '线性代数', s.scores, 0)) AS '线性代数',
MAX(IF(c.coursenm = '高等数学(一)', s.scores, 0)) AS '高等数学(一)',
MAX(IF(c.coursenm = '高等数学(二)', s.scores, 0)) AS '高等数学(二)'
12.2.1直接拼接SQL的结果
如果我们直接拼接SQL语句的话,就会出现这种情况
Select st.stuid, st.stunm,
(
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, NULL)) AS ',
c.coursenm
)
)
FROM courses c
)
From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid;
12.2.2声明变量并拼接
应该像普通的那些语句那样，进行声明，将语句拼接完整之后，再执行，也就是下面这样
-- 声明一个变量,mysql声明变量需要用@来声明
SET @sql = NULL;
-- 把我们查询的结果放到声明的变量@sql中
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, ''''
)
) INTO @sql -- 放到我们声明的变量@sql中
FROM courses c;
-- 使用CONCAT( )函数拼接SQL语句
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,
' From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid');
-- 预定义SQL语句
PREPARE stmt FROM @sql;
-- 执行SQL
EXECUTE stmt;
-- 释放预定义语句的资源
DEALLOCATE PREPARE stmt;
执行后得到下面的结果
12.2.3拼接语句中加条件
我们还可以在拼接语句中加入条件,例如查询ID为1003学生的信息
SET @sql = NULL;
-- 定义一个条件
SET @stuid = '1003';
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, ''''
)
) INTO @sql
FROM courses c;
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,
' From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Where st.stuid = ''', @stuid, '''
Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
结果
12.2.4存储过程
我们要判断学号是否为空或者等于0再加上条件进行查询的话,就只能使用 存储过程 了
-- 如果存在这个存储过程的话,就删除
drop procedure if exists SP_QueryData;
-- 创建存储过程,指定输入的条件变量
Create Procedure SP_QueryData(IN stuid varchar(16))
-- 整个过程中所有的SQL语句仅需要读记录，不对记录进行写的操作。
READS SQL DATA
BEGIN
-- 待拼接的sql语句
SET @sql = NULL;
-- 学生的id
SET @stuid = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, '\''
)
) INTO @sql
FROM courses c;
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,
' From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno');
-- 如果学生id不为null或者''空字符串的话就进行拼接
IF stuid is not null and stuid <> '' then
SET @stuid = stuid;
-- 拼接SQL语句
SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');
END IF;
SET @sql = CONCAT(@sql, ' Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
调用,传入学生学号进行查询
CALL `SP_QueryData`('1001');
如果想查询全部的话就传入空串
CALL `SP_QueryData`('');
结果
十三. PREPARE语句
-- 预定义两个参数
SET @a = 3;
SET @b = 4;
-- 预定义一个SQL语句,将该语句赋值给stmt1
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
-- 将参数填充到预定义的SQL语句当中,并执行
EXECUTE stmt1 USING @a, @b;
-- 清除预定义的SQL语句
DEALLOCATE PREPARE stmt1;
SET @skip=1;
SET @numrows=3;
-- 预定义一个SQL语句,将该语句赋值给STMT
PREPARE STMT FROM "SELECT * FROM student LIMIT ?,?";
EXECUTE STMT USING @skip, @numrows;
-- 清除预定义的SQL语句
DEALLOCATE PREPARE STMT;
174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
