字段可以带有数学表达式，例如：select ENAME, SAL*12 'YEAR SALE' from emp;
条件查询
格式：
select
[字段名1], [字段名2], [字段名3]...
from
[表名]
where
[条件];
例子：
select empno, ename from emp where sal = 800;
select ename, sal, deptno from emp where sal > 2000 and (deptno = 10 or deptno = 20);
select ename, job from emp where job in ('manager', 'salesman');
select ename from emp where ename like '%o%';
条件：
大小关系：=, !=, <, >, <>
两个值之间（包含）：between [值1] and [值2]
判断为空：is null和is not null
并且：and
或：or （and 优先级比 or 高，加括号改变优先级）
是否包含：in ([值1], [值2]...)和not in ([值1], [值2]...)
模糊查询：like [值]（% 代表 0 到任意多个字符， _ 代表任意一个字符，注意 \ 转义）
多情况讨论
格式：
select
[字段名1], [字段名2], [字段名3]...
case [目标字段名]
when
[值1]
then
[对应处理]
when
[值2]
then
[对应处理]
else
[对应处理]
end
from
[表名];
例子：
select ename, job, sal oldsal, (case job when 'manager' then sal * 1.1 when 'salesman' then sal * 1.3 else sal end) as newsal from emp;
子查询
语法：
在 select, from, where 子句中嵌套查询
例子：
select ename, sal
from emp
where sal > (
select min(sal)
from emp
);
select t.job, t.avgsal, s.grade
from (select job, avg(sal) avgsal from emp group by job) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) dname from emp e;
正则表达式
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]';
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
此处 ^ 代表匹配串的开始
union
语法：
[查询语句]
union
[查询语句]
注：合理运用 union, 查询次数可以优于 join （例如多表连接）
注：要求结果的列数和数据类型相同
例子：
select ename , job from emp where job = 'manager'
union
select 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(字段名, 被当做的值)
条件表达式：if(条件，为真的值，为假的值)
ABS(): 返回一个数的绝对值。
CEILING(): 返回大于或等于给定数值的最小整数。
FLOOR(): 返回小于或等于给定数值的最大整数。
ROUND(): 四舍五入到指定的小数位数。
MOD(): 返回除法操作的余数。
FORMAT(): 格式化数字为格式化的字符串，通常用于货币显示。
例子：
select lower(ename) as ename from emp;
select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) as name from emp;
select ename from emp where ename = trim(' KING ');
select round(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 by 2;
select ename, sal from emp where sal between 1000 and 2000 order 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 having max(sal) > 2000 order by max(sal);
select deptno, count(distinct job) from emp group by deptno;
select
[字段名1], [字段名2]...
from
[表名A]
inner join
[表名B]
on
[表连接条件];
注：A B 两表没有主次关系，只看表连接条件，inner 可省略
例子：
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
[字段名1], [字段名2]...
from
[表名A]
right outer join -- left outer join
[表名B]
on
[表连接条件];
注：right 意思是表 B 为主表，left 意思是表 A 为主表，outer 可省略
例子：
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;
自联结
例：寻找与 prod_id = 'DTNTR' 产品相同供应商的产品
SELECT p1.prod_id, p1.prod_name
FROM products p1
join products p2
on p2.prod_id = 'DTNTR' AND p1.vend_id = p2.vend_id;
自然连接
不重要的概念，指联结后不出现重复的列
SELECT c.cust_id, c.cust_name, o.order_num, oi.prod_id
FROM customers AS c
JOIN orders AS o
ON c.cust_id = o.cust_id
JOIN orderitems AS oi
ON oi.order_num = o.order_num AND oi.prod_id = 'FB';
select
[分组字段名1], [分组字段名2] ... [分组函数1], [分组函数2]...
from
[表名]
where
[不带分组函数的条件]
group by
[分组字段名1], [分组字段名2]...
having
[带分组函数的条件]
order by
[规则]
limit
[起始下标], [长度];
执行顺序：
from
where
group by
having
select
order by
limit
例子：每个部门工资最多的人
select e.ename, t.*
from emp e
join (select max(sal) maxsal, deptno from emp group by deptno) t
on e.sal = t.maxsal and e.deptno = t.deptno;
create table t_student(
nob int,
name varchar(32),
sex char(1) default 'm',
age int(4),
email varchar(255)
);
create table emp2 as select 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`@`%` |
+-------------------------------------+