创建数据库
插入数据存储过程代码

创建表

创建work1.sql文件

-- 创建表
CREATE TABLE `innodb`(
	`id` int(11) NULL DEFAULT 0,
	`data` int(11) NULL DEFAULT 0)
	ENGINE=InnoDB;

CREATE TABLE `myisam`(
	`id` int(11) NULL DEFAULT 0,
	`data` int(11) NULL DEFAULT 0)
	ENGINE=myisam;

create procedure myinsert(a int)
	begin
		declare i int default 1;
		repeat
			insert into `innodb`  VALUES(i,i);
			set i=i+1;
		until i>a end repeat;
	end;

-- 执行存储过程
call myinsert(10000);
-- 删除myinsert函数
drop procedure if exists myinsert;

可以编写sql文件，也可在命令列界面中分步运行

运行之后需要刷新

针对数据库的操作

-- 查看当前数据库
show databases;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS Management_System
DEFAULT CHARACTER SET utf8mb4	
DEFAULT COLLATE utf8mb4_unicode_ci;

Use Flight_Management_System;

-- 删除数据库
DROP DATABASE [ IF EXISTS ] <数据库名>;

针对表的操作

-- 向表中添加数据
insert into `flight` values('CA123','TSN','TAO','CA');

-- 清空表
delete from [表名];

视图

create view search_tickets
    as
    select *
    from timetable natural join flight natural join airline

drop view xxxx

查找

当输出的属性不为主键时,可能出现重复

LIKE/NOT LIKE匹配

% 代表任意字符串

_ 代表一个字符

SELECT	name
FROM	drinkers
WHERE	phone LIKE '555%';

-- 查找所有李姓员工
SELECT	*
FROM	employee 
WHERE	empname LIKE '李%';

NULL空值

任意字符串(%)与空值比较的结果是unknown,不会返回unknown结果值

true=1,false=0,unknown=1/2

-- 查找所有job非空的元组
SELECT	*
FROM	workson
WHERE	job LIKE '%';	-- 或者为 job IS NOT NULL

ORDER BY 输出排序

ORDER BY <list of attributes>
ASC(升序,默认),DESC(降序)

SELECT	enpname
FROM	employee
WHERE	deptno='d2'
ORDER BY empno DESC;	-- 可以按照不输出的属性排序

多表查询

笛卡尔积

-- 使用笛卡尔积连接两个表
-- 两个表中有同名属性,select其中一个时需要标明,不然会出现两列
SELECT	beer
FROM	frequents,likes 
WHERE	bar='Joe"s Bar' AND	
		frequents.drinker = likes.drinker;
		-- 待查找的内容为'->"

natural join

只使用于两个表有一列相同属性

SELECT	*
FROM	employee natrual join department
WHERE	location='天津';
-- 相同的两列不重复出现

-- 等价
SELECT	*
FROM	employee,department
WHERE	department.deptno=employee.deptno and
		location='天津';
-- 使用and/or连接多个查询条件

inner join

作用与natural相同，使用与有多个相同属性，使用on进行约束

left/right outer join 左/右外连接

(显示count=0的部分->单元内容为null)

-- 给出每个专业借阅的“c2”类的图书总数，没有借阅的次数显示为0（
select majorid,majorname,count(catid) as borrowcount
from student left outer join borrow on student.stuid=borrow.stuid
left outer join book on book.bookid=borrow.bookid
and book.catid='c2'
natural join major
group by majorid;

元组变量

引入一个关系的两个或更多部分

-- 返回制造商一致的啤酒对
SELECT	b1.name,b2.name
FROM	beers b1,beers b2
WHERE	b1.manf=b2.manf and
		b1.name<b2.name; -- 避免冗余

子查询

嵌套

-- find bars that serve Miller at the same price Joe charges for Bud
SELECT	bar
FROM	sells
WHERE	beer='Miller' and price=
			(SELECT	price
			FROM	sells
			WHERE	bar='Joe"s Bar' and beer='Bud');

IN/NOT IN操作

只能用于连接查询结果,而不能是 in+<表>

SELECT	*
FROM	Beers
WHERE	name IN 
			(SELECT beer
			FROM likes
			WHERE drinker='Fred'); 
			-- 当子查询只有一个查询结果,in可改为=
-- 或写成
SELECT	name,manf
FROM	Beers,Likes
WHERE	drinker='Fred' and beer=name;

EXISTS

当且仅当关系非空时为真

-- 不存在制造商一致的不同啤酒-> 一个manf只有一个beer的beer
SELECT	name
FROM	beers b1
WHERE	NOT EXISTS
			(SELECT *
             FROM	beers
             WHERE	manf = b1.manf and
             		name <> b1.name);

-- 查找部门中只有一名员工的员工姓名
SELECT	empname
FROM	empoyee e1
WHERE	not exists
		(SELECT	*
         FROM	employee
         WHERE	deptno=e1.deptno and
        		empname<>e1.empname);

ANY/ALL

x=ANY (relation) 等于其中至少一个
x>ANY (relation) 大于至少一个
relation中只有一个属性列
X<>ALL (relation) x不在relation内
x>ALL (relation) x大于等于关系中的所有元组

-- 找到售价最高的，注意是>=
SELECT	beer
FROM	sells
WHERE	price>=ALL(SELECT price FROM sells);

-- 不是最低价格
SELECT	beer
FROM	sells
WHERE	price>ANY(SELECT price FROM sells);

子表命名

-- 给出借阅了所有图书类别的本科生
select stuid,stuname
from
(select stuid,count(distinct catid) as times
from borrow natural join student natural join book
where student.degree='本科生'
group by stuid) as temp1 
natural join student
where times=(select count(*) from category);
-- -----------------
select stuid, stuname 
from student 
where degree = '本科生' and not exists(
	select catid 
    from category 
    where catid not in(
		select catid from book, borrow
		where book.bookid = borrow.bookid and borrow.stuid = student.stuid
	);

Union, Intersecion, Difference

intersect：交集
union：并集
except：A-B

-- 两个集合的交集,要求两个表具有相同的属性列
(SELECT	*	FROM	likes) -- likes(drinker,beer)
	INTERSECT
(SELECT	drinker,beer
FROM	sells,frequents
WHERE	frequents.bar=sells.bar);

Set(集合)/Bag(包)

普通的select-from-where是包->可重复
union\intersection\difference是集合->不可重复

SELECT	DISTINCT price	-- 强制转化为不可重复
FROM	sells;

(...) UNION ALL (...);	-- 可重复

-- eg. empname有七条
SELECT	e1.empname
FROM	empname e1,empname e2; -- 返回49条

SELECT	DISTINCT e1.empname
FROM	empname e1,empname e2; -- 返回7条

Aggregations聚合操作

sum、avg、min、max、count、count(*)

聚合不可以嵌套使用

AVG

SELECT	AVG(price)
FROM	sells;

COUNT

count忽略null，计算重复
count(*)不忽略null

SELECT	COUNT(DISTINCT price) -- 有多少种不同的售价
FROM	sells
WHERE	beer='Bud';

GROUP BY

分组运算在积运算和投影运算之后进行

SELECT	beer,AVG(price)
FROM	sells
GROUP BY	beer; -- 获得每种啤酒的平均价格

-- 每个员工做几个项目
SELECT	empno,count(*)
FROM	workson
GROUP BY	empno;

-- 每个项目由几名员工完成
SELECT	projectno,count(*)
FROM	workson
GROUP BY	projectno;

NULL单独作为一个分组

-- 1998.04.15后入职员工所参与项目的平均预算
SELECT	empno,avg(budget)
FROM	workson,project
WHERE	workson.projectno=project.projectno
		and date>'1998-04-15'
GROUP BY	empno;

MIN

SELECT	min(budget)
FROM	project;

-- 查找bud的最低价格,不能得到正确结果
SELECT	bar,min(price)
FROM	sells
WHERE	beer='Bud';
-- 应该写为
SELECT	bar
FROM	sells
WHERE	beer='Bud' and 
		price<=ALL(SELECT	price
					FROM	sells
					WHERE	beer='Bud');

Having

SELECT	beer,AVG(price)
FROM	sells
GROUP BY	beer -- having 添加group条件，至少在三家酒吧售卖或由B公司制造
HAVING	COUNT(*)>=3 OR --针对group by中的属性
		beer	IN	(SELECT name	
		-- 只有group by中的属性，才能以非聚合形式出现在having中
					FROM	beers
					WHERE	manf='Busch');