MySQL笔记

一、建表

创建表：

create table person(
    person_id smallint unsigned, 
    teacher_id smallint unsigned, 
    fname varchar (20), 
    lnamre varchar(20), 
    gender enum('M','F'), 
    birth_date date, 
    street varchar (30), 
    city varchar(20), 
    state varchar (20), 
    country varchar(20), 
    postal_code varchar(20),
    /* 对列(person_id)建立主键约束,名为pk_person */
    constraint pk_person primary key (person_id))
    /* 对teacher_id建立外键约束，teacher_id的值只能来自于teacher表的teacher_id */
    constraint person_teacher foreign key (teacher_id) references teacher (teacher_id);

修改表：

/* 在person表中添加新字段price */
alter table person add price integer(10) null
/* 重新定义person表的person_id列 */
alter table person modify person_id smallint unsigned auto_increment

修改表的约束：

/* 在product表中添加新的主键约束 */
alter table product add constraint pk_product primary key (product_cd);
/* 在product表中添加新的外键约束，对teacher_id建立外键约束，teacher_id的值只能来自于teacher表的teacher_id */
alter table product add constraint product_teacher foreign key (teacher_id) references teacher (teacher_id);
/* 在product表中删除主键约束，只需要把add变为drop就好 */
alter table product drop constraint pk_product primary key (product_cd);

二、增删改查

2.1、插入：

INSERT INTO person
(person_id,fname,lname,gender,birth_date)
VALUES(null，'William'，'Turner'，'M'，'1972-05-27');

2.2、删除：

DELETE FROM pezson 
WHERE person_id = 2;

DELETE FROM login_history 
ORDER BY login_date 
/* 删除排序后的前262行数据 */
LIMIT 262;

MySQL 并不允许在 delete 或 update 语句中使用 limit 子句时提供第二个参数。

2.3、更新：

UPDATE person
SET street ='1225 Tremont St.', city = 'Boston', atate ='MA', country = 'USA', postal_code ='02138' 
/* 显式指定日期字符串格式 */
, birth_date = str_to_date("DEC-21-1980", "%b-%d-%Y")
WHERE person_id = 1;

UPDATE account
/* 为排序后的前10名用户更新数据 */
SET avail_balance = avail_balance + 100 
ORDER BY open_date 
LIMIT 10;

2.4、查询：

/* 设定fname的别名为fn */
/* 加上distinct使查询到的数据组合不重复（不是使某个字段数据不重复） */
select distinct person_id, fname fn, lname, birth_date 
from person
/* 条件操作符：and、or、not */
where peraon_id = 1 and not (fname = 1 or lname = 1)
/* 使查询结果先按fname字母顺序排列，再按lname字母顺序排列 */ 
/*排序默认为升序（asc），在后面加desc则改为降序 */
order by fname, lname desc
/* 指定字符校对（核对），ci结尾表示不区分大小写，cs结尾表示区分大小写 */
COLLATE utf8_general_ci
/* 分页查询：偏移量为50，传回的最大量为10 */
LIMIT 50, 10;

分组查询：

/* 查询在以identity_id分组的情况下，各分组的数据行数 */
SELECT identity_id, COUNT(function_id) 
FROM identity_function 
where 1 = 1
/* 以identity_id及sex分组 */
GROUP BY identity_id, sex
/* 返回条件为COUNT(function_id) = 21，count()是分组之后才有的数据，而分组在where之后，所以对count()的判断不能放在where中，而应该放在having中 */
/* having专用于分组结果的筛选 */
HAVING COUNT(function_id) = 21

多列子查询：

SELECT account_id,product_cd,cust_id 
FROM account
/* 过滤条件的两列必须用括号括起来，并且排列顺序与子查询结果的顺序相同 */
WHERE (open_branch_id, name) in (SELECT branch_id, uname 
                       			FROM branch
                       			WHERE fid < 9999;

三、过滤（where）

and、or、not：

select *
from person
where peraon_id = 1 and not (fname = 1 or lname = 1)

between 操作符：

SELECT amp_id,fnama,lname,start_date 
FROM employae
WHERE start_date BETWEEN '2005-01-01' AND '2007-01-01';

当使用 between 操作符时，必须首先指定范围的下限（在 between 后面），然后指定范围的上限（在and 的后面）。

相当于>=和<=。

in、all、any、exists 操作符：

SELECT account_id,product_cd,cust_id,avail_balance 
FROM account
WHERE product_cd IN (SELECT branch_id, uname 
                     FROM branch
                     WHERE fid < all(100, 200, 300) and fid > any(1, 2, 3) and exists(select account_id 
                                                                                      from account));

通配符：

SELECT lname 
FROM amployee
WHERE lname LIKE '_a%e%';

'-‘代表一个字符，’%'代表任意数目的字符，包括0个。

正则表达式：

SELECT emp_id,fname,lname 
FROM employee
whERE lname REGEXP '^[FG]';

regexp操作符用于接受一个正则表达式。

is、NULL值的使用：

SELECT emp_id,fname,iname,superior_amp_id 
FROM amployee
WHERE auperior_amp_id IS NULL;

注意：

• 表达式可以为null，但不能等于 null（即 auperior_amp_id = NULL 是错误的）；
• 两个null 值彼此不能判断为相等。

四、连接

用于在同一查询中获取多个表的数据

4.1、内连接

内连接：

SELECT e.fname,.lname,d.name 
FROM employee e INNER JOIN depaztment d
ON e.dept_id = d.dept_id;

如果在一个表中的dept_id列中存在某个值，但该值在另一张表的 dept_id列中不存在，那么相关行的连接会失败，在结果集中将会排除包含该值的行。这种类型的连接被称为内连接，也是最常用的一种连接类型。如果想要包含其中某个表的所有行，而不考虑每行是否在另一表中存在匹配，那么可以使用外连接。

如果连接两个表的列名是相同的，那么可以使用 using 子句替代 on 子句，如下所示（不推荐）：

SELECT e.fname,e.lname,d.name
FROM employee e INNER JOIN department d
USING (dept_id);

三表连接：

SELECT a.acount_id,c.fad_id,e.fname,e.1name 
FROM employee e INNER JOIN account a ON e.amp_id = a.open_emp_id 
INNER JOIN customer c ON a.cust_id = c.cust_id 
WHERE c.cust_type_cd = 'B';

虽然三个表出现的顺序不同，但是即使交换他们的位置，查询的结果也并不会改变，因为sql是一种非过程化的语言，也就是说只需要描述要获取的数据库对象，而如何以最好的方式执行查询则由数据库服务器负责。

不过，如果需要自己选择一个表作为开始点，需要添加 STRAIGHT_JOIN ，如下所示：

SELECT STRAIGHT_JOIN a.account_id,c.fed_id,e.fname,e.1name 
FROM customer c INNER JOIN account a ON a.cust_id = c.cust_id 
INNER JOIN amployee e ON a.open_emp_id = e.emp_id 
WHERE c.cust_type_cd = 'B';

多表连接一次删除多个表的数据（Innodb引擎不适用）：

DELETE account2,customer2,individual2 
FROM account2 INNER JOIN customer2 
			 ON account2.cust_id = customer2.cust_id INNER JOIN individual2 
			 									  ON customer2.cust_id = individual2.cust_id 
where indiyidual2.cust id = 1;

多表连接一次修改多个表的数据（Innodb引擎不适用）：

UPDATE individual2 INNER JOIN customer2 
				  ON individual2.cust_id = customer2.cust_id INNER JOIN account2
														  ON customer2.cust id = account2.cust id
SET individual2.cuat id = individual2.cust id + 10000, 
	customer2.cust_id = customor2.cust_id + 10000, 
	account2.cust_id = account2.cust_id ＋ 10000 
WHERE individual2.cust id = 3;

4.2、外连接

SELECT a.account_id,a.cust_id,b.name 
FROM account a LEFT OUTER JOIN business b ON a.cust_id = b.cust_id;

左外连接包括第一个表的所有行，但仅仅包含第二个表中那些匹配行的数据，右外连接（right outer join）则相反。

三路外连接：

SELECT a.account_id,a.cust_id,b.name 
FROM (account a LEFT OUTER JOIN business b ON a.cust_id = b.cust_id) ab left outer join cass c on ab.name = c.name;

三路外连接实际上每个查询都只使用了单一外连接。

交叉连接（笛卡尔积） cross join：

SELECT pt.name,p.product_cd,p.name 
FROM product p cross JOIN product_type pt;

五、使用集合（复合查询）

当对两个数据集合执行集合操作时，必须首先应用下面的规范。

• 两个数据集合必须具有同样数目的列;
• 两个数据集中对应列的数据类型必须是一样的（或者服务器能够将其中一种转换
  为另一种）。

union 操作符和 union all操作符（并操作）：

union与union all 操作符可以连接多个数据集，它们的区别在于 union对连接后的集合排序并去除重复项，而 union all保留重复项。使用 union all得到的最终数据集的行数总是等于所要连接的各集合的行数之和。

SELECT'IND' type_cd,cust_id,iname name FROM individual 
UNION ALL
SELECT 'BUS'type_cd,cust_id,name FROM business;

intersect 操作符（交操作）（mysql未实现）：

SELECT emp_id,fname,iname FROM employea 
INTERSECT
SELECT cust_id,fname,1name FROM individual;

except操作符（差操作）（mysql未实现）：

SELECT amp_id FROM amployae
WHERE assigned_branch_id = 2
AND (title ='Teller'OR title='Head Teller') 
EXCEPT
SELECT DISTINCT open_emp_id FROM account
WHERE open_branch_id = 2;

如果需要对复合查询的结果进行排序，那么可以在最后一个查询后面增加 order by 子句。当在 order by 子句中指定要排序的列时，需要从复合查询的第一个查询中选择列名。

SELECT emp_id,assigned_branch_id FROM employee
WHERE title = 'Teller' 
UNION
SELECT open_emp_id,open_branch_id FROM account
WHERE product_cd ='SAV' 
ORDER BY emp_id;

集合操作符优先级：

• 操作符以自顶向下的顺序被解析和执行。
• intersect 操作符比其他操作符具有更高的优先级。
• 可以用圆括号对多个查询进行封装，以明确指定它们的执行次序。

(SELECT cust id FROM account WHERE product_cd IN('SAV','MM') 
 UNION ALL 
 SELECT a.cust_id FROM account a INNER JOIN branch b ON a.open_branch_id = b.branch_id 
 WHERE b.name ='Woburn Branch') 
INTERSECT
(SELECT cust id FROM account WHERE avail balance BETwEEN 500 AND 2500 
 EXCEPT
SELECT cust id FROM account WHERE product_cd = 'CD' AND avail_balance < 1000);

六、数据生成、转换和操作

6.1、字符串操作

向数据库中插入带引号的字符串数据时，可以使用转义字符：

UPDATE string_tbl SET text_fld = 'This string didn\'t work,but it does now'

quote()，在提取字符串时，它用单引号将整个字符串包起来，并且为其中的单引号增加转义字符：

sELECT quote(text_fld) FROM string_tbl;

position()，查找某个字符串在另一个字符串中的位置：

SELECT POSITION('characters' IN vchar_fld) 
FROM string_tbl;

如果找不到该子字符串，那么 position()函数将返回 0。

concat()，用于连接字符串。

在已有字符串后面添加新字符串：

UPDATE string_tbl
SET text_fld = CONCAT(text_fld,',but now it is longer');

生成简介：

SELECT CONCAT(fname,' ',lname,'has been a', title,'since',start_data)amp_narrative 
FROM amployee
WHERE title = 'Tellar' OR title='Head Teller';

6.2、日期操作

str_to_date()，将字符串格式化为日期字符串：

UPDATE individual
SET birth_date = STR_TO_DATE('Septambar 17,2008','%M %d,%Y') 
WHERE cust _id = 9999;

current_date()、current_time()、current_timestamp()，返回当前时间：

SELECT CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP();

date_add()，为日期增加一定的时间：

增加五天：

SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 5 DAY);

增加3小时27分11秒：

UPDATE transaction
SET txn_date = DATE_ADD(txn_date, INTERVAL '3:27;11' HOUR_SECOND) 
WHERE txn_id =9999;

间隔名称	描述
Second	秒数
Minute	分钟数
Hour	小时数
Day	天数
Month	月份
Year	年份
Minute_second	分钟数和秒数，中间用 “:” 隔开
Hour_second	小时数、分钟数和秒数，中间用 “:” 隔开
Year_month	年份和月份，中间用 “-” 隔开

last_day() 求得当月的最后一天：

SELECt LAST_DAY('2008-09-17');

convert_tz() 将本地时间转换为UTC时间：

sELECT CONVERT_Tz(CURRENT_TIMESTAMP(),'US/Eastern','UTC') current_ute;

dayname() 获取当天是星期几：

SELECT DAYNAME('2008-09-18');

extract() 获取某个时间中的某个日期数据：

SELECT EXTRACT(YEAR FROM '2008-09-18 22:19:05');

datediff() 求得两个日期的天数之差：

sELECT DATEDIFF('2009-09-03 23:59:59','2009-06-2400:00:01');

6.3、聚集函数

Max() ，返回集合中的最大值

Min() ，返回集合中的最小值

Avg() ，返回集合的平均值

Sum() ，返回集合的和

Count() ，返回集合中值的个数

SELECT MAX(avail_balance)max_balance, 
MIN(avail_balance) min_balance, 
AvG(avail_balance)avg_balance, 
SUM(avail_balance) tot_balance, 
COUNT (*) num_accounts 
FROM account
WHERE product_cd = 'CHK';

如果在使用聚集函数的同时，需要同时列举某个项，则需要有where（隐式分组）或者group by（显式分组）。

如果希望对分组的不同值计数而不是统计分组的行数，可以使用distinct：

SELECT COUNT(DISTINCT open_emp_id) 
FROM account;

with rollup 用于在多条件分组统计数据的基础上再进行统计汇总，即用来得到group by product_cd的汇总信息：

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance 
FROM account
group by product_cd, open_branch_id with rollup;

6.4、其它

cast() 将一种数据类型转化为另一种数据类型：

sELECT cAST('1456328' As SIGNED INTEGER);

七、条件逻辑

查找型case表达式 ：

SELECT c.cust_id,c.fed_id, 
(CASE
 /* when为条件判断，then为相应的返回表达式,else为没有when符合时的返回 */
	WHEN c.cust_type_cd = 'I' 
		THEN (SELECT CONCAT(i.fname,'',i.1name) FROM individual j WHERE i.cust_id = c.cust_id) 
	WHEN c.cust_type_cd = 'B' 
		THEN(SELECT b.name FROM business b WHERE b.cust_id = e.cust_id) 
	ELSE 'Unknown' 
END) name
FROM customer c;

八、事务

事务的隔离级别：读未提交、读提交、可重复读、串行化

两种锁策略：

• 数据库的写操作必须向服务器申请并获得写锁才能修改数据，而读操作必须申请和获得读锁才能查询数据。多用户可以同时读取数据，而一个表（或其他部分）一次只能分配一个写锁，并且拒绝读请求直至写锁释放。
• 数据库的写操作必须向服务器申请并获得写锁才能修改数据，而读操作不需要任何类型的锁就可以查询数据。另一方面，服务器要保证从查询开始到结束读操作看到一个一致的数据视图（即使其他用户修改，数据看上去也要相同）。这个方法被称为版本控制。

SQL server采取第一种锁策略，Oracle采用第二种锁策略，mysql则都采用（取决于存储引擎的选择）

锁的粒度：表锁、页锁、行锁

事务创建方式：

• 一个活跃事务总是和数据库会话相联系，所以没有必要，也没有什么方法能够显式地启动一个事务。当前事务结束时，服务器自动为会话启动一个新的事务。
• 如果不显式地启动一个会话，单个的 SQL 语句会被独立于其他语句自动提交。启动一个事务之前需先提交一个命令。

oracle数据库采用了第一种策略，mysql和sql server采用了第二种。

MySQL和 SQL Server 都允许读者为单个会话关闭自动提交模式，在这种情况下，对于事务来说服务器就像 Oracle 数据库一样工作。一旦离开了自动提交模式，所有的 SQL 命令都会发生在同一个事务的范围，并且必须显式地对事务进行提交或者回滚。

创建一个事务：

/* 启动事务 */
START TRANSACTION; 

SELECT i.cust_id
FROM individuali
WHERE i.fname = 'Frank' AND i.lname ='Tucker';

INSERT 
INTO transactiona (txn id,txn date,account_id,
txn_type  Cd,amount)
VALUES (NULL,now(),@mm_id,'CDT',50);

/* 设置一个保存点 */
SAVEPOINT my_savepoint;

INSERT INTO transaction (txn id,txn_date,account_id, txn_type_cd,amount)
VALUES (NULL,now(),@chk_id,'DBT',50); 

/* 回滚到my_savepoint保存点，并保存保存点之前的事务 */
rollback to savepoint my_savepoint;

UPDATE account
SET last activity_date = now(), avai1 balance = avail balance - 50 WHERE account id = @mm id;

/* 忽略所有保存点，撤销整个事务 */
rollback;

UPDATE account
SET last activity date = now(),
avail_balance = avail balance + 50 WHERE account_id = @chk_id; 

/* 结束事务 */
COMMIT;

九、索引

如同人们使用索引在出版物中查找单词一样，数据库服务器也使用索引定位表中的行。与普通的数据表不同，索引是一种以特定顺序保存的专用表。不过，索引并不包含实体中的所有数据，而是那些用于定位表中行的列，以及描述这些行的物理位置的信息。因此，索引的作用就是便捷化检索表中行和列的子集，而不需要检查表中的每行。

创建一个索引：

ALTER TABLE department
ADD INDEX dept_name_idx (name);

这个语句为 department.name 列创建了索引（确切地说，这是一个 B 树），此外该索引被命名为dept_name_idx。

创建一个唯一索引：

ALTER TABLE department
ADD UNIQUE dept_name_idx (name);

唯一索引，department.name的值不能出现重复（普通索引则没有该限制）。

创建一个多字段的索引：

ALTER TABLE employee
ADD INDEX emp_names_idx(lname,fname);

该索引适用于对lname的查询和对lname+fname的查询，不适用于对fname的查询。

删除一个索引：

ALTER TABLE department 
DROP INDEX dept_name_idx;

在删除存在的外键和添加新的外键时包含 on update cascade 语句，这种外键约束的变化能够实现传播（级联更新）：

ALTER TABLE product
ADD CONSTRAINT f_product_type_cd FoREIGN KEY (product_type_cd) REFERENCES product_type (product_type_cd) 
ON UPDATE CASCADE;

级联删除则为：on delete cascade

十、视图

创建视图：

CREATE VIEW customer_vw (cust_id, fed_id, cust_type_cd, address, city, state, zipcode)
AS
SELECT cust_id, concat('ends in',substr(fed_id,8,4)) fed_id, cust_type_cd, address, city, state, postal_code 
FROM customer;

一旦视图被创建，用户就能把它当一个表来查询。

视图是存储在数据字典中的查询，它的行为表现得像一个表，但实际上并不拥有任何数据（可以称之为虚拟表）。当发出一个对视图的查询时，该查询会被绑定到视图定义上，以产生最终被执行的查询。

当视图被创建后，并没有产生或存储任何数据，服务器只是简单地保留该查询以供将来使用。

视图在某些情况下，可以被用来修改基础表的数据，但是无法在一个语句中同时修改两个基础表的数据。

十一、存储过程

参考博客：https://blog.csdn.net/ychinata/article/details/52729431

存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合（类似于函数）。

优点：简单、安全、高性能

标准使用：

/* 创建存储过程 */
create procedure productpricing(
    /* in：定义int类型的输入变量onumber */
	in onumber int,
    /* out：定义decimal(8,2)类型的输出变量ototal */
	out ototal decimal(8,2)
)
/* 存储过程体开始 */
begin
	select sum(item_price)
	from orderitems
	/* 使用输入变量onumber */
	where order_num = onumber
	/* 将查询到的值写入输出变量ototal */
	into ototal;
/* 存储过程体结束 */
end;

智能存储过程：

create procedure ordertotal(
	in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)
)
begin
-- 设置定界符为//
	delimiter //
	-- declare定义局部变量
    declare total decimal(8,2);
    declare taxrate int default 6;
    
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into total;
    -- 使用if判断taxable是否为true（另外，if语句还支持elseif和else子句，elseif还使用then子句，else不使用）
    if taxable then
        select total+(total/100*taxrate) into total;
	end if;
    -- 最后，保存局部变量total到ototal
    select total into ototal;
end //
-- 将定界符设回;
delimiter ;

使用存储过程：

/* 使用存储过程 */
call productpricing(100, @ototal);
select @ototal;

删除存储过程：

/* 删除存储过程 */
drop procedure if exists productpricing;

十二、游标

mysql的游标只能用于存储过程（和函数）。

十三、触发器

触发器是mysql响应delete、insert、update而自动执行的一条（组）mysql语句。

只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表的每个事件定义，每个表每次事件只允许一个触发器，因此，每个表最多支持6个触发器（before insert、after insert、before update、after update、before delete、after delete）。

触发器中不能调用存储过程。

创建insert触发器：

-- 创建insert触发器，在orders表的数据行插入前触发，触发器名为neworder
create trigger neworder after insert on orders
for each row 
-- new为新插入行的数据
select new.order_num;

创建update触发器：

-- 创建update触发器
create trigger updatevendor
before update on vendors
for each row 
-- new为更新后的数据，old为更新前的数据，new的数据可以修改，old的数据无法修改
set new.vend_state = upper(new.vend_state);

创建delete触发器：

-- 设置定界符为//
delimiter //
-- 创建delete触发器，在orders表的数据行删除前触发，触发器名为deleteorder
create trigger deleteorder before delete on orders
for each row
-- 触发器执行语句
begin
	insert into archive_orders(order_num, order_date, cust_id)
	-- old为被删除行的数据，old的数据无法修改（只读）
    values(old.order_num, old.order_date, old.cust_id);
end //	-- ?
delimiter ;

删除触发器：

drop trigger newproduct;

触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。