select empno, ename from emp where sal =800;select ename, sal, deptno from emp where sal >2000and (deptno =10or deptno =20);select ename, job from emp where job in ('manager', 'salesman');select ename from emp where ename like'%o%';
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
此处 ^ 代表匹配串的开始
union
语法:
[查询语句]union[查询语句]
注:合理运用 union, 查询次数可以优于 join (例如多表连接)
注:要求结果的列数和数据类型相同
例子:
select ename , job from emp where job ='manager'unionselect ename , job from emp where job ='salesman';
UNION 自动去除重复的行,使用 UNION ALL 包含重复的行
UNION 中若要排序,只允许出现一次,放在最后的 SELECT 语句后
单行处理函数
转大小写:lower(字段名)和upper(字段名)
提取子串:substr(字段名, 起始下标, 截取长度)注:起始下标从 1 开始
拼接成字符串:concat(字段名1, 字段名2)
长度:length(字段名)
去空格:trim(字段名)
四舍五入:round(字段名, 小数位)
生成随机数:rand()
处理 NULL:ifnull(字段名, 被当做的值)
ABS(): 返回一个数的绝对值。
CEILING(): 返回大于或等于给定数值的最小整数。
FLOOR(): 返回小于或等于给定数值的最大整数。
ROUND(): 四舍五入到指定的小数位数。
MOD(): 返回除法操作的余数。
FORMAT(): 格式化数字为格式化的字符串,通常用于货币显示。
例子:
selectlower(ename) as ename from emp;selectconcat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) -1))) asnamefrom emp;select ename from emp where ename =trim(' KING ');selectround(rand() *1000, -1) as num from emp;select ename, (sal +ifnull(comm, 0)) *12 yearsal from emp;
select [字段名1], [字段名2], [字段名3]...from [表名]order by [规则];
例子:
select ename, sal from emp order by sal asc, ename asc;select ename, sal from emp order by2;select ename, sal from emp where sal between1000and2000order by sal;
规则:
默认升序:[字段名]
指定降序:[字段名] desc
指定升序:[字段名] asc
多个字段排序:[字段名1] [排序方式], [字段名2] [排序方式]...(靠前的规则优先)
根据查询结果的字段位置排序:[数字]
分组
格式:
select [分组字段名1], [分组字段名2] ... [分组函数1], [分组函数2]...from [表名]where [不带分组函数的条件]group by [分组字段名1], [分组字段名2]...having [带分组函数的条件]order by [规则];
例子:
select job, sum(sal) from emp group by job;select job, deptno, max(sal) from emp group by job, deptno;select deptno, max(sal) from emp group by deptno havingmax(sal) >2000order bymax(sal);select deptno, count(distinct job) from emp group by deptno;
分组函数(多行处理函数):
最大最小:max(字段名)和min(字段名)
求和:sum(字段名)
平均:avg(字段名)
数量:count(字段名)
关键字补充
去重:distinct(放在所有字段前,后面字段联合去重)
WITH ROLLUP 在分组中使用,获得汇总值
SELECT vend_id, COUNT(*) AS num FROM products GROUP BY vend_id WITHROLLUP;
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno;select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;select a.ename employee, b.ename employer from emp a join emp b on a.mgr = b.empno;
select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;select a.ename employee, b.ename employer from emp a left join emp b on a.mgr = b.empno;
createtablet_student( nob int,namevarchar(32), sex char(1) default'm', age int(4), email varchar(255));createtableemp2asselect ename, job from emp where job ='manager';
create table t_user(
id int(11),
name varchar(32),
birth date,
create_time datetime
);
insert into t_user(id, name, birth)
values (1, 'jk',str_to_date('2000-12-03', '%Y-%m-%d'),now());
insert into t_user values (2, 'jjkk','2002-09-12','2022-07-28 13:13:13');
select name, date_format(birth, '%d\%m\%Y') birth from t_user;
CurDate()
CurTime()
Now()
返回当前日期
返回当前时间
返回当前日期和时间
Date()
Time()
Year()
Month()
Day()
Hour()
Minute()
Second()
返回日期部分
返回时间部分
返回年部分
类比
类比
类比
类比
类比
例子:
SELECT order_num, order_date
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT order_num, order_date
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
update 修改数据
格式:
update
[表名]
set
[字段名1] = [数据],
[字段名2] = [数据],
[字段名3] = [数据],
......
where
[条件];
例子:
update t_user set name = 'Abe' where id = 2;
删除表中数据
格式:
delete from [表名] where [条件];
truncate table [表名];
注:delete支持回滚,速度慢。truncate 物理删除,速度快
例子:
delete from t_user where id = 2;
truncate table t_user;
create view emp_dept_view as
select e.ename, e.sal, d.dname
from emp e
join dept d
on e.deptno = d.deptno;
全文本搜索
开启全文本搜索支持
# 来自 create.sql
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
全文本搜索为每一条数据给出一个匹配值,最匹配d的记录优先
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
使用查询扩展,即找出与搜索结果再相关的其他行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
布尔文本搜索不需要建立 FULLTEXT 索引,更慢
例子:
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
-代表排除后面这个词
*代表词尾通配符
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
""将双引号内部的词视为一整个短语
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
+代表一定有这个词
()包含一个子表达式
<降低这个词的等级
存储过程
存储过程可以封装一系列复杂的操作
有输入输出
形式类似于函数
创建存储过程
例子1:
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;
DELIMITER重新定义语句分隔符
BEGIN, END 包含存储过程体
存储过程名为 productpricing(),这个例子没有参数
例子2:
DELIMITER //
CREATE PROCEDURE productpricing2 (
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 //
DELIMITER ;
这个存储过程有三个函数
OUT代表可以从存储过程传出
pl等等为形参名
DECIMAL是一种数据类型,精度可变的浮点值
例子3:
DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8, 2)
)
BEGIN
SELECT Sum(item_price * quantity)
FROM orderitems
WHERE onumber = order_num
INTO ototal;
END //
DELIMITER ;
IN 代表传入参数
例子4:
DELIMITER //
-- Name : ordertotal2
-- Parameters : onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total if variable
CREATE PROCEDURE ordertotal2(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally add tax'
BEGIN
DECLARE total DECIMAL(8, 2);
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price * quantity)
FROM orderitems
WHERE onumber = order_num
INTO total;
IF taxable THEN
SELECT total + (total / 100 * taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END //
DELIMITER ;
# 书上例子
# 执行报错:ERROR 1415 (0A000): Not allowed to return a result set from a trigger
# 新版本不支持这种写法。触发器不允许出现SELECT *的形式
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
# 修改版
# 结果放进变量
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @ordernumber;
创建了名为neworder的触发器
在 INSERT orders之后触发
FOR EACH ROW代表每一行都触发
NEW是一个虚拟表,可以获取新插入的内容
INSERT INTO orders(order_date, cust_id)
VALUES (Now(), 10001);
SELECT @ordernumber;
CREATE TABLE archive_orders # 存档表
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
DELIMITER //
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 //
DELIMITER ;
OLD是一个虚拟表,可以访问要删除的信息
OLD只读
DELETE FROM orders WHERE order_num = 20010;
SELECT * FROM archive_orders;
+---------------------------------+
| Grants for ben@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%` |
+---------------------------------+
USAGE代表没有权限
授予权限
GRANT [权限] ON [数据库名].[表名] TO [用户名];
具体到权限表见书
例子:
GRANT SELECT ON cc.* TO ben; # cc 就是 crashcourse
mysql> SHOW GRANTS FOR ben;
+-------------------------------------+
| Grants for ben@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%` |
| GRANT SELECT ON `cc`.* TO `ben`@`%` |
+-------------------------------------+