学习数据库需要掌握的几个关键概念：
约束即管理，对于数据库管理而言，就是要管理数据的完整性。
可靠性+准确性=数据完整性
实体完整性：任何一个实体，都必须要一个唯一可标识的字段来区分两个不同的实体（指主键）
参照完整性：指两张不同的表，主键和外键之间的一个参照，主键和外键之间必须满足外键的参照完整性（主要指外键）
自定义完整性：指系统自己定义的一些约束条件。
概念：
表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列叫做表的主键。
如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号、课程号）作为成绩表的主键（组合键，也叫联合组建，单使用一个不能有效区分是否重复时使用组合键）
说明：
主键选择的原则：
即“子表”中对应于“主表”的列，在子表中称为外键或者引用键。
它的值要求于主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为。一个表可以有多个外键。
自定义完整性：指根据具体的业务要求所设置的约束，跟之前介绍的主键和外键不同，自定义完整性需要我们单独进行设置之后，数据库才会进行检查。
约束方法：存储过程、触发器
比如：成绩，是0~100分值区间，但是103就不符合要求，就需要设置约束。
语法是SQL
非关系型数据库的数据不是以表的形式保存的，而是以键值对的形式保存的。
什么是SQL：
| 语言分类 | 名称 | 详细描述 |
| 1、数据库查询语句（DQL） | select | 数据选择查询 |
| 2、数据操纵语句（DML） | insert update delete | 插入 更新 删除 |
| 3、数据定义语句（DDL） | create alter drop rename truncate | 新建 修改结构 删除 重命名 删除 |
| 4数据控制语句（DCL） | grante revoke | 授权 接触授权 |
学习重点：
在大多数EDNMS中，使用如下简单的 SQL语句，创建整个数据库：
CREATE DATABASE [IF NOT EXISTS] 数据库名：
[IF NOT EXISTS] 指，若是不存在则创建SHOW DATABASES;DROP DATABASE 数据库名;新的 数据库创建完毕后，为了在该数据库上执行SQL语句，需先生们使用的数据库。声明当前使用哪个数据库：
USE 数据库名;ALTER DATABASE ab_name CHARACTER SET = charset_name
eg:
ALTER DATABASE test CHARACTER SET =gb2312注：如果在navicat中需要关掉数据库连接后重新打开查看数据库属性才能看到更改
CREATE TABLE 表名(
列名 数据类型 约束，
列名 数据类型 约束
)[ENGINE = engine_name|[DEFAUT]CHARACTER SET = charset_name];其中，表名在一个数据库中必须是唯一的，并且符合标识符规则。列名在一个表内必须是唯一的，并且符合标识符规则。列的数据类型决定了什么数据可以存储在列中，而列的约束定义了创建列时的条件。MySQL引擎类型有InnoDB和MyISQM两种，目前一般5.4以上的版本都是InnoDB。
eg：
CREATE TABLE Friend(Name VARCHAR(50),PhoneNo VERCHAR(15));
CREATE TABLE demo(id INT,name VAARCHAR(10))ENGINE=INNODB DEFAULT CHARACTER SET=utf8；| 类型 | 精度范围 | 内容格式 | 用途 |
| INT(n) | (-21474838,21474837) | (0,4294967295) | 整数值 |
| FLOAT | (-3.40E-38,3.40E+38) | 7位小数 | 单精度浮点数 |
| CHAR(n) | 0-255 | 字符型 | 存储定长字符 |
| VARCHAR(n) | 0-65535 | 字符型 | 存储不定长字符 |
| TEXT | 0-65535 | 文本型 | 大容量文本 |
| BLOB | 65k | 二进制文件 | 二进制大对象 |
BLOB可保存音乐、图片等
定长字符串和不定长字符串
日期和时间类型
| 类型 | 格式 | 范围 |
| YEAR | YYYY | 1901-2155 |
| DATE | YYYY-MM-DD | 1000-01-01 - 9999-12-31 |
| TIME | HH:MM:SS | -835:59:59 - 835:59:59 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01-2038 |
| DATEIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 - 9999-12-31 23:59:59 |
日期和时间数据类型插入示例：
CREATE TABLE demo(
id INTauto_increment PRIMARY KEY,
reg_datetime DATETIME,
reg_time TIME,
reg_daya DATE,
reg_year YEAR
)
INSERT INTO table1 VALUES(1,'2018-03-29 23:23:34','2018-03029','2018')思考：在创建表时下列一般定义为何种数据类型？
CREATE TABLE demo(
id INT PRIMARY KEY,
...
)不能两次设置PRIMARY KEY，可以用第三种方法设置第二种方法
CREATE TABLE demo(
id INT,
...
(主键命名规范：pk_表名,)
CONSTRAINT pk_name PRIMARY KEY(id)
)
也可以简写成
CREATE TABLE demo(
id INT,
NAME VARCHAR(10)
...
PRIMARY KEY(id,NAME) --复合主键，单主键也可
)ALTER TABLE stu ADD CONSTRAINT pk_stu PRIMARY KEY(id); --也可以补充符合主键ALTER TABLE stu DROP PRIMARY KEY;“子表”中对应于“主表”的列，在子表中称为外键或者引用键。
它的值要求于主表的主键或者唯一键相对应，外键用来强制引用完整性。一个表可以有多个外键。
语法：
FOREIGN KEY(sid) REFERENCES 主表(sid)
eg：
CREATE TABLE IF NOT EXISTS demo_new(
cid INT PRIMARY KEY,
sid INT,
FOREIGN KEY(sid) REFERENCES demo(sid)
)如何制定外键在update或delete主表时的行为？
即指定外键删除或更新的行为：
eg：从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。
CREATE TABLE IF NOT EXISTS demo_new(
cid INT PRIMARY KEY.
sid INT,
FOREIGN KEY(sid) REFERENCES demo(sid) ON UPDATE CASCADE
)更新和删除随着主键。
CREATE TABLE IF NOT EXISTS demo_new(
cid INT PRIMARY KEY.
sid INT,
FOREIGN KEY(sid) REFERENCES demo(sid) ON DELETE CASCADE ON UPDATE CASCADE
)更新随着主键，拒绝删除父表,此时想要删除主键,可以先删除子键所引用到的数据删除.
CREATE TABLE IF NOT EXISTS demo_new(
cid INT PRIMARY KEY.
sid INT,
FOREIGN KEY(sid) REFERENCES demo(sid) ON UPDATE CASCADE ON DELETE RESTRICT
)添加外键
fk即FOREIGN KEY
ALTER TABLE demo_new ADD CONSTRAINT fk_demo_new FOREIGN KEY(sid)
REFERENCES demo(sid)
ON DELETE CASCADE ON UPDATE CASCADED删除外键约束
ALTER TABLE 表名 DROP FOREIGN KRY 外键名称eg:
DROP TABLE demo; --删除表
CREATE TABLE demo(
--设定为自增的列必须设置为主键,并且一张表只能由一个自增列.字段和主键同时定义并设定自增长.
ID INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10)
)
INSERT INTO demo VALUES(3,'tester1');
INSERT INTO demo(name) VALUES ('tester2'); --没有指定主键的值则会自增长
INSERT INTO demo VALUES (DEFAULT,'tester2'); --若是没有指定行,则可defatltCREATE TABLE demo(
...
vip VARCHAR(50) UNIQUE,
...
)CREATE TABLE demo(
...
vip VARCHAR(50) NOT NULL,
...
)Mysql原生并不支持检查约束，对于离散值可使用set或enum方式解决。对于连续纸只能通过触发器解决。
CREATE TABLE demo(
id INT auto_increment PRIMARY KEY,
name VARCHAR(10),
sex SET('男','女'), --如果其他值则不插入值
payment ENUM('货到付款','在线支付')
)默认值和唯一约束不能同时设置。
CREATE TABLE demo(
...
tel VARCHAR(11) DEFAULT '15888888888' --默认值
...
) SHOW TABLES;SHOW CREATE TABLE 表名;DROP TABLE <表名>;通过外键约束连接在一起的表不能被删除。在删除之前，必须线删除约束。在删除表时，必须是表的所有者或者对该表有管理员权限。
在DB中，下面的语句创建一个名为new_aimtable的新表，该表是赋值了table表的数据和结构（列、数据类型）
CREATE TABLE 新表名 AS (SELECT * FROM 被赋值表名);
如果是只复制表的结构，不复制数据：
CREATE TABLE 新表名 LIKE 被赋值表名;alter table 表名 RENAME [TO|AS] 新的表名;
eg: alter table stu RENAME TO student;ALTER TABLE <表名> ADD COLUMN <列的定义>;
eg：ALTER TABLE product ADD COLUMN product_name VARCHAR(100);更新列名及类型
ALTER TABLE 表名 CHANGE [COLUMN] 原列名 新列名 列定义;
EG: ALTER TABLE stu CHANGE COLUMN stu_name sname varchar(20);只修改列数据：
ALTER TABLE 表名 MODIFY [COLUMN] col_name column_definition;
EG: ALTER TABLE stu MODIFY COLUMN sname varchar(10);alter table 表名 DROP [COLUMN] col_name;
eg: alter table stu DROP COLUMN sname;之前的版本的形式，反引号可不加。
总结
4）练习题
CREATE TABLE Addressbook(
regist_no INT NOT NULL PRIMARY KEY,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address CHAR(20)
)DEFAULT CHARACTER SET=utf82. 假设在创建练习1.1 中的Addressbook 表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook 表中。
列名 ：postal_code
数据类型 ：定长字符串类型（长度为8）
约束 ：不能为NULL
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;3. 编写SQL语句来删除Addressbook表。
DROP TABLE Addressbook;INSERT INTO <表名> ([列名]) VALUES (<值列表>)
eg:
INSERT INTO Student(SName,SAddress,SGrade,SEmail,SSEX)
VALUES('张三','上海松江',6,'zs@SoHu.com',0);--如果没有指定列名，则代表每个列都加入内容，需要都作插入，自增列，可以定义为DEFAULT
--若是插入的类型和定义的不一致，不会报错，会设置为默认值。
--如果在设计表时指定了某列不允许为空，单输入数据的时候没有提供这个值，MYSQL会给该列插入一个默认值，eg，若是上诉的sname没有赋值设置插入，则为" "一个空值字符串
注意：
一次性插入多行记录：
INSERT INTO <表名> ([列名]) VALUES (<值列表>),(<值列表>),(<值列表>)...UPDATE <表名> SET <列名=更新值>
[WHERE <更新条件>]
eg:
UPDATE Student SET SSEX=0
UPDATE Scoures
SET Soures = Soures+5
WHERE Soures <= 95DELETE FROM <表名> [WHERE <删除条件>]DELETE FROM <表名>; --若是不指定删除条件，则将删除所有表中数据与之类似功能的：
TRUNCATE TABLE <表名>;--二者区别：
关于TRUNCATE：DDL语句，会自动提交事务，无法回滚。
1、表结构、列、约束等不被改动
2、不能用于有外键约束的表
3、标识重新开始编号
关于DELETE FROM：手动提交事务，可以在事务中回滚。
1、数据会一行一行的删，效率比较低
eg：
START TRANSACTION; --开始事务
SELECT* FROM stu;
DELETE FROM stu;
SELECT* FROM stu;
ROLLBACK; --事务回滚，只能用作于事务未提交时进行回滚
SELECT* FROM stu;
START TRANSACTION;
SELECT* FROM stu;
DELETE FROM stu;
SELECT* FROM stu;
ROLLBACK;
SELECT* FROM stu;SELECT <列名> --如是为*则表示查找所有列
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC|DESC]] <根据排序的列>
| 运算 | 描述 |
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
eg：
SELECT ename,sal,sal+300 --表示薪资加了300后，显示成一列
FROM emp;注意：
eg
1、
2、
SELECT ename as "姓 名",sal as 薪资
from emp;使用DISTINCT关键字可删除重行
SELECT DISTINCT deptno FROM emp;使用DISTINCT关键字多列排除重复
SELECT DISTINCT 列A,列B FROM 表;--注意，并不意味着任一列没有重复，只有再组合时没有重复，一般没有意义
eg:
select DISTINCT deptno,ename from emp;在SELECT语句中使用LIMIT子句来约束查询结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。
SELECT 列1,列2...列n FROM LIMIT offset,countoffset：参数指定要返回的第一行的偏移量。第一行的偏移量为0，二不是1
count：指定要返回的最大行数
eg：
SELECT* FROM emp LIMIT 5,6 --从偏移量为5的行开始取，取第6行
当使用带一个参数的LIMIT子句时，此参数将用于确定从结果集的开头返回的最大行数。
SELECT* FROM emp LIMIT 5
--这个语句等于 select* from emp limit 0，51、使用limit来获取结果的前几行
eg：求工资最高的前三个员工的信息
SELECT* FROM emp ORDER BY sal DESC LIMIT 3; --DESC降序2、使用limit来获取第n个数据
eg：求工资第4高的员工的信息
SELECT* FROM emp ORDER BY sal DESC LIMIT 3,1;使用WHERE子句限定返回的记录
SELECT [DISTINCT] {*,COLUMN[alias],...}
FROM table
[WHERE condition(s)]WHERE子句必须跟在FROM字句后
注意：
在where子句中字符串和日期要用单引号括起来
运算符：
| 运算符 | 含义 |
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
| BETWEEN...END... | 在两值之间（包含） |
| IN(list) | 匹配列出的值 |
| LIKE | 匹配一个字符模式，模糊匹配 |
| IS NULL | 是空值 |
| AND | 如果组合的条件都为真则返回真值 |
| OR | 如果组合的条件之一是真值，返回真值 |
| NOT | 如果条件为假则返回真值 |
优先级：
所有的比较运算>NOT>AND>OR，可用括号改变
eg：
SELECT empo,ename,sal,mgr
FROM emp
WHERE mgr IN(7902,7566,7788);
也可以：
WHERE mgr=7902 or mgr=7566 or mgr=7788);LIKE：模糊匹配
eg:
SELECT ename
FROM emp
WHERE ename LIKE 'S%'
另外：
WHERE ename LIKE '_A%'
使用ORDER BY 子句将记录排序
ORDER BY 子句在SELECT语句的最后
SELECT empno,ename,sal*12 annsal
FROM emp
ORDER BY annsal; --使用别名排序1. 根据要求创建一个product表，并向其插入以下数据。
Table：product
字段1：product_id（商品id），4位字符，非空，主键
字段2：product_name（商品名称）, 可变字符100位，非空
字段3：product_type（商品类型），可变长32位，非空
字段4：sale_price（销售价格），整型
字段5：purchase_price（进货价格），整型
字段6：regist_date（注册日期），日期型
插入数据如下：
'0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20'
'0002', '打孔器', '办公用品', 500, 320, '2009-09-11'
'0003', '运动T恤', '衣服', 4000, 2800, NULL
'0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'
'0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'
'0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'
'0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'
'0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11'如下：
CREATE TABLE product(
product_id CHAR(4) NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INT,
purchase_price INT,
regist_date DATE
)DEFAULT CHARACTER SET=utf8;
INSERT INTO product VALUES('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20')
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL)
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15')
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20')
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28')
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100,NULL, '2009-11-11')2. 编写一条SQL 语句，从Product（商品）表中选取出“登记日期（regist_date）在2009 年4 月28 日之后”的商品。查询结果要包含product_name 和regist_date 两列。
SELECT product_name ,regist_date FROM Product
WHERE regist_date>'2009-04-28';3. 请写出一条SELECT 语句，从Product 表中选取出满足“销售单价打九折之后利润高于100 元的办公用品和厨房用具”条件的记录。查询结果要包括product_name 列、product_type 列以及销售单价打九折之后的利润（别名设定为profit）
SELECT product_name,product_type, sale_price*0.9 - purchase_price AS profit FROM Product
WHERE (sale_price*0.9 - purchase_price)>100 AND (product_type='办公用品' OR product_type='厨房用具');
4. 筛选出sale_price比purchase_price高出500元及以上的商品的product_name, sale_price，purchase_price。
SELECT product_name,sale_price,purchase_price FROM Product
WHERE sale_price-purchase_price >= 500;5. 将所有销售价格在1000到4000之间的所有商品的信息打印出来
SELECT* FROM product
WHERE sale_price BETWEEN 1000 AND 4000;6. 找出所有销售价格大于等于4000的商品并按进货价格降序排列
SELECT* FROM product
WHERE sale_price>=4000 ORDER BY sale_price DESC;聚合函数会针对进行运算的所有记录返回唯一一个结果
分组函数GROUP BY可对记录进行分组
| COUNT | 返回结果集中行的数目 |
| SUM | 返回结果集中所有值的总和 |
| AVG | 返回结果集中所有值的平均值 |
| MAX | 返回结果集中所有值的最大值 |
| MIN | 返回结果集中所有值的最小值 |
MAX和MIN也可以作用于时间，最早时间和最晚时间
eg:
SELECT COUNT(<计数规范>) FROM <表名>;为什么叫(分组)聚合函数?
普通的select语句会针对表中的每行记录进行处理并返回结果，而聚合函数使根据表中分组的情况进行处理，每组只返回一个结果。
SELECT AVG(IFNULL(comm,0)) FROM emp;分组函数忽略空值，可以使用IFNULL等函数处理空值
SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno;如果有普通列
SELECT job，deptno, AVG(sal) FROM emp
GROUP BY deptno;eg2：
GROUP BY deptno,job; --多列分组，先按照部门分组，再按照职位分组
--job：普通列(没有意义) AVG(sal)：聚合函数 deptno：分组依据列
结论：
MYSQL中，SELECT后出现了聚合函数，则该位置只能出现其他聚合函数和分组依据列，普通列只显示第一行值，其他数据库有些不支持该行为。
Having子句的作用使对行分组进行过滤
SELECT column，group_function
FROM <表>
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[GROUP BY colunm];查询工资大于￥2900的部门的最高工资：
SELECT deptno,max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900;WHERE -> GROUP BY -> HAVING
显示最高的平均工资（嵌套组函数）
SELECT max(avg(sal))
FROM emp
GROUP BY deptno;关系型数据库管理系统在执行一条SQL时，按照如下顺序执行各子句：
SELECT AVG(列名) FROM tablename
WHERE **
GROUP BY<列名>
HAVING
ORDER BY<列名>ASC/DESC
LIMIT1. 请编写一条SELECT 语句，求出销售单价（sale_price 列）总和大于进货单价（purchase_price 列）总和1.5 倍的商品种类以及销售总价和进货总价。
SELECT product_type 商品类型, sum(sale_price) 销售总价,SUM(purchase_price) 进货总价 FROM product
GROUP BY product_type
HAVING sum(sale_price)>SUM(purchase_price)*1.52. 查找商品表中商品销售价格的平均价、最高价、最低价。
SELECT AVG(sale_price) 销售平均价, MAX(sale_price) 销售最高价, MIN(sale_price) 销售最低价
FROM product3. 查找商品表中每种商品的平均售价，并降序显示。
SELECT AVG(sale_price) 销售平均价
FROM product
GROUP BY product_type DESC4. 查找商品表中按类型统计商品数大于2的商品类型及商品数
SELECT product_type,COUNT(product_type) 商品数
FROM product
GROUP BY product_type
HAVING COUNT(product_type)>25. 查询销售价格比最低进货价还低的商品的id、商品名及销售价格。
SELECT product_id,product_name,sale_price
FROM product
WHERE sale_price<purchase_price6. 按照下列语句创建几个表：
DROP TABLE IF EXISTS stu;
create table stu (
sid int primary key, -- 学生id
sname varchar(50) -- 学生姓名
);
DROP TABLE IF EXISTS course;
create table course(
cid int primary key, -- 课程id
cname varchar(50) -- 课程名称
);
DROP TABLE IF EXISTS sc;
create table sc(
sid int, -- 学生id
cid int, -- 课程id
score int -- 成绩
);
ALTER TABLE sc ADD CONSTRAINT fk_sc_course_cid FOREIGN KEY (cid) REFERENCES course(cid);
ALTER TABLE sc ADD CONSTRAINT fk_sc_stu_sid FOREIGN KEY (sid) REFERENCES stu(sid);
insert into stu values(1,"张三");
insert into stu values(2,"李四");
insert into stu values(3,"王五");
insert into course values(1,"语文");
insert into course values(2,"数学");
insert into course values(3,"英语");
insert into sc values(1,1,78);
insert into sc values(2,2,45);
insert into sc values(3,1,67);
insert into sc values(1,3,83);
insert into sc values(3,2,42);
insert into sc values(2,3,54);
insert into sc values(1,2,69);
insert into sc values(2,1,72);
insert into sc values(3,3,34);然后完成以下题目：
（1）获取平均成绩大于60的每个学生名字
（2）获取到语文及格的每个学员的名字
（3）获取及格科目大于1个的每个学员的名字（要点：先按照分数过滤出高于60分的，然后分组，再在每组里面统计分数值大于1个的）
SELECT select_list
FROM table
WHERE expr operator(
SELECT select_list
FROM table
);eg:
SELECT ename
FROM emp
WHERE sal>(
SELECT sal
FROM emp
WHERE empno='7560'
);操作符：=、>、>=、<、<=、<>
eg：
SELECT ename,job
FROM emp
WHERE job = (
SELECT job
FROM emp
WHERE empno = '7369'
)
AND sal>(
SELECT sal
FROM emp
WHERE empno = '7876'
);子查询中使用聚合函数：
SELECT ename,job,sal
FROM emp
WHERE sal=
(
SELECT MIN(sal)
FROM emp
);HAVING子句中使用子查询结果
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal)>
(
SELECT MIN(sal)
FROM emp
WHERE deptno=20
);错误：在多行子查询中使用单行运算符，比如子查询中使用了group by
| 运算符 | 含义 |
| IN | 等于列表中的任何值 |
| ANY | 比较子查询返回的每一个值，只要其中一个值满足条件就返回true |
| ALL | 比较子查询返回的每一个值，要其中每一个值都满足条件才返回true |
IN:
SELECT deptno FR
SELECT empno,ename,job,sal FROM emp
WHERE deptno in
(
OM emp
WHERE ename = 'SEITH' or ename='MILLER'
);ANY:
SELECT empno,ename,job FROM emp
WHERE sal < ANY
(
SELECT sal FROM emp
WHERE job= 'CLERK'
)
AND job <> 'CLERK';即：
where sal>1300 or sal>1100 or sal>800 or sal>950--只需要满足小于1300
ALL:
SELECT empno,ename,job,sal FROM emp
WHERE sal > ALL
(
SELECT avg(sal)FROM emp
GROUP BY deptno
);即：
where sal >1566 and sal >2175 and sal >2916--最后只要sal >1566，所有取值都小于子查询中的返回的所有值了。
外键取值于主键。
如果没有主外键关系，那么就没有多表查询的需求。
语法：
使用连接从多个表中查询数据（使用where来指定连接条件）
1、
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.colum1 = table2.colum2;2、
SELECT table1.column,table2.column
FROM table1 INNER JOIN table2
ON table1.colum1 = table2.colum2;连接查询必须指定连接条件，如果不指定连接条件会造成笛卡尔积的结果。
笛卡尔积形成于：
为了避免笛卡尔结果我们总是在WHERE子句中指定连接条件。
设A，B为集合，用A中元素为第一元素，B中元素为第二元素构成的有序对，所有这样的有序对组合成的集合，叫做A与B的笛卡尔积，记AxB.
eg：
SELECT * FROM emp,dept;则记录数=emp记录数 x dept记录数
可使用别名简化查询
SELECT a.column,b.column
FROM table1 a,table2 b
WHERE a.colum1 = b.colum2;可以使用and过滤条件。
表的连接数=表的数量-1
在LEFT JOIN 中，会返回左表中的所有行，即使左表中又不符合条件的记录，也会在查询结果中显示。同理，RIGHT JOIN即返回右表中所有记录。
eg：
如题：查询所有员工姓名和部门编号，包括没有员工的部门。
SELECT emp.ename,dept.deptno
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno;即返回dept中所有在emp的deptno以及在emp没有出现，但dept存在的数据。
某图书馆系统有以下几个表，建表及数据如下:
-- 创建BOOK:（图书表）
CREATE TABLE BOOK (
BOOK_ID int,
SORT VARCHAR(10),
BOOK_NAME VARCHAR(50),
WRITER VARCHAR(10),
OUTPUT VARCHAR(50),
PRICE int);
-- 创建READER:（读者表）
CREATE TABLE READER (
READER_ID int,
COMPANY VARCHAR(10),
NAME VARCHAR(10),
SEX VARCHAR(2),
GRADE VARCHAR(10),
ADDR VARCHAR(50));
-- 创建BORROW:（借阅表）
CREATE TABLE BORROW (
READER_ID int,
BOOK_ID int,
BORROW_DATE datetime)
-- 插入数据:
-- BOOK表:
insert into BOOK values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90);
insert into BOOK values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90);
insert into BOOK values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90);
insert into BOOK values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);
insert into BOOK values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60);
insert into BOOK values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00);
insert into BOOK values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80);
insert into BOOK values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);
insert into BOOK values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);
insert into BOOK values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);
insert into BOOK values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);
insert into BOOK values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);
insert into BOOK values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);
insert into BOOK values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);
insert into BOOK values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);
-- READER表:
insert into reader values(111,'信息系','王维利','女','教授','1号楼424');
insert into reader values(112,'财会系','李立','男','副教授','2号楼316');
insert into reader values(113,'经济系','张三','男','讲师','3号楼105');
insert into reader values(114,'信息系','周华发','男','讲师','1号楼316');
insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224');
insert into reader values(116,'信息系','李明','男','副教授','1号楼318');
insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214');
insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216');
insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318');
insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506');
insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510');
insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512');
insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202');
insert into reader values(124,'财会系','朱海','男','讲师','2号楼210');
insert into reader values(125,'财会系','马英明','男','副教授','2号楼212');
-- BORROW表:
insert into borrow values(112,445501,'2006-3-19');
insert into borrow values(125,332211,'2006-2-12');
insert into borrow values(111,445503,'2006-8-21');
insert into borrow values(112,112266,'2006-3-14');
insert into borrow values(114,665544,'2006-10-21');
insert into borrow values(120,114455,'2006-11-2');
insert into borrow values(120,118801,'2006-10-18');
insert into borrow values(119,446603,'2006-11-12');
insert into borrow values(112,449901,'2006-10-23');
insert into borrow values(115,449902,'2006-8-21');
insert into borrow values(118,118801,'2006-9-10');-- 1) 找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
SELECT NAME,COMPANY
FROM reader
WHERE NAME LIKE '李%';-- 2) 列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。
SELECT DISTINCT BOOK_NAME,OUTPUT FROM book;-- 3) 查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。
SELECT BOOK_NAME,PRICE FROM book
WHERE OUTPUT = '高等教育出版社' ORDER BY PRICE DESC;-- 4) 查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
SELECT SORT FROM book
WHERE PRICE BETWEEN 10 AND 20 ORDER BY OUTPUT,PRICE;
-- 5) 查找书名以”计算机”开头的所有图书和作者(WRITER)。
SELECT DISTINCT BOOK_NAME,WRITER FROM book
WHERE BOOK_NAME LIKE '计算机%';-- 6)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。
SELECT DISTINCT R.NAME,R.COMPANY
FROM reader R, borrow W
WHERE R.READER_ID = W.READER_ID;
或者:
FROM reader R INNER JOIN borrow W
ON R.READER_ID = W.READER_ID;-- 7) 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。
SELECT B.BOOK_NAME,W.BORROW_DATE
FROM READER R,BOOK B,BORROW W
WHERE R.NAME LIKE '李%' AND R.READER_ID = W.READER_ID AND B.BOOK_ID = W.BOOK_ID;
或：
SELECT B.BOOK_NAME,W.BORROW_DATE
FROM book B INNER JOIN borrow W
ON B.BOOK_ID = W.BOOK_ID INNER JOIN reader R
ON R.READER_ID = W.READER_ID
WHERE R.NAME LIKE '李%';-- 8) 查询2006年7月以后没有借书的读者借书证号、姓名及单位。
SELECT DISTINCT READER_ID,NAME,COMPANY
FROM reader
WHERE READER_ID NOT IN
(
SELECT DISTINCT W.READER_ID
FROM borrow W
WHERE W.BOR
ROW_DATE > '2006-07'
);-- 9) 求出各个出版社图书的最高价格、最低价格和总册数。
SELECT MAX(PRICE),MIN(PRICE),COUNT(OUTPUT)
FROM book
GROUP BY OUTPUT;
注意COUNT(*)
SELECT OUTPUT,MAX(PRICE),MIN(PRICE),COUNT(*)
FROM book
GROUP BY OUTPUT;-- 10) 找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期 。
SELECT R.NAME,R.COMPANY,W.BORROW_DATE
FROM reader R,borrow W
WHERE R.READER_ID = W.READER_ID AND r.NAME<> '赵正义' AND W.BORROW_DATE =
(
SELECT borrow.BORROW_DATE FROM reader, borrow
WHERE reader.NAME = '赵正义' and reader.READER_ID = borrow.READER_ID
);第一种方式：以“@”开始，形式为“@变量名”
SET @nametest = 666
变量可以为数值、字符串等类型，要使用该变量时，直接利用@变量名
的形式即可，如：
SET @nametest； --打印出值666，类似print
第二种方式：通过SELECT 语句定义变量
写法1： SELECT @variable_name := value; 注意这里:=是，不是=
写法2： SELECT XXX into @variable_name; 注意，这里的xxx必须是返回一行数据的值，比如一个聚合函数一般通过select语句定义时，可以通过sql语句来给变量赋值，方便后面使用，如：
SELECT @maxsal := MAX(sal) from emp; --通过select给变量@maxsal赋值
SELECT MAX(sal) into @maxsal from emp;
SELECT @maxsal; --查询@maxsal的值
select ename,sal from emp where sal=@maxsal; --使用变量@maxsal的值秋最高工资员工的姓名，注意，变量名均不区分大小写。
概念：
优点：
DELIMITER // --DELIMITER两个跟存储过程没有关系，有没有都没有，作用是改变sql分隔符的定义，而sql语句的分隔符默认使用分号，所以会修改默认分隔符，此处修改成了//
CREATE PROCEDURE 存储过程名字()
BEGIN
--存储过程代码
END //
DELIMITER; --恢复成默认的设置为分号eg：
DROP PROCEDURE IF EXISTS get_maxsal;
DELIMITER //
CREATE PROCEDURE get_maxsal()
BEGIN
SELECT max(sal) FROM emp;
END //
CALL get_maxsal(); --调用存储过程DECLARE variable_name datatype(size) DEFAULT default_value;
SET 变量名 = 变量值 --注意这里变量前面没有@符号
SELECT XXX into 变量名 FROM XXXX
EG:
DROP PROCEDURE IF EXISTS get_Sal;
DELIMITER //
CREATE PROCEDURE get_sal()
BEGIN
DECLARE avgsal INT; --声明变量
SELECT avg(sal) into avgsal from emp; --赋值变量
SELECT ename,sal from emp where sal > avgsal;
END //
CALL get_sal();03带参数的存储过程
在mysql中，参数有三种模式：IN，OUT或INOUT。
语法：
IN/OUT/INOUT param_name param_type(param_size)
eg:
DROP PROCEDURE IF EXISTS get_sal;
DELIMITER //
CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT empsal INT)
BEGIN
SELECT sal INTO empsal from emp WHERE ename=empname;
END //
--存储过程中定义的参数，会在mysal中自动生成以下划线命名的参数
CALL get_sal('BLAKE',@_empsal); --调用的时候给参数赋值
SELECT @_empsal;可以在存储过程中加入流程控制语句
分支：IF、CASE
循环：REPEAT、WHILE
1、
IF expression THEN
statements;
ENDIF;2、
IF expression THEN
statements;
ELSE
else-statements;
ENDIF;3、
IF expression THEN
statements;
ELSEIF elseif-expression THEN
else-statements;
...
ELSE
else-statements;
ENDIF; --不能少ENDIFeg:
DROP PROCEDURE IF EXISTS get_sal;
DELIMITER //
CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT sallevel VARCHAR(10))
BEGIN
DECLARE empsal INT;
SELECT sal INTO empsal from emp WHERE ename=empname;
IF empsal>3000 THEN
SET sallevel = 'high';
ELSEIF empsal<=3000 AND empsal>2000 THEN
SET sallevel = 'middle';
ELSE
SET sallevel = 'LOW';
END IF;
END //
--存储过程中定义的参数，会在mysal中自动生成以下划线命名的参数
CALL get_sal('BLAKE',@sallevel); --调用的时候给参数赋值
SELECT @sallevel;02 CASE
CASE case_expression
WHEN when_statements_1 THEN commands;
WHEN when_statements_2 THEN commands;
...
ELSE commands --else语句是可选的
ENDIF; --不能少ENDIFeg:
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
SELECT 'hello';
END; --这里的 BEGIN..END代表是一个语句块，可以没有
END CASE;
END
CALL p();REPEAT
statement_list
UNTIL search_condition
END eg：打印指定开始和结束数字的和
DROP PROCEDURE IF EXISTS dorepeat;
CREATE PROCEDURE dorepeat(in b INT,in e INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE TEMP INT DEFAULT B;
REPEAT
set total = total + temp;
set temp = temp + 1;
UNTIL temp>e
END REPEAT;
SELECT total;
END
CALL dorepeat(1,10);WHILE search_condition DO
statement_list
END WHILEeg：
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SELECT 'hello';
SET v1 = v1-1;
END WHILE;
END
CALL dowhile();ITERATE lable
LEAVE lable
eg:
CREATE PROCEDURE testITERATE(p int)
BEGIN
outw:while(true) DO
set p = p+1;
if p=5 THEN
ITERATE outw; --不执行下面的语句，又从 outw:while(true) DO继续执行
ELSEIF p=10 THEN
LEAVE outw; --退出循环outw
END IF;
END WHILE outw;
SELECT p;
END
--待用testITERATE
SET @a=2;
CALL testITERATE(@a);eg2：打印5，3
CREATE PROCEDURE pro_repeat()
BEGIN
DECLARE P INT DEFAULT 6;
outw:REPEAT
set p = p-1;
if p=4 THEN
ITERATE outw; --不执行下面的语句，又从 outw:while(true) DO继续执行
ELSEIF p=2 THEN
LEAVE outw; --退出循环outw
END IF;
SELECT p;
UNTIL p<1
U
END REPEAT outw;
END
CALL pro_repeat();3. 编写一个存储过程，该存储过程接受一个部门编号作为参数，可以求出该部门的员工总数。
CREATE PROCEDURE get_total(IN _deptno INT, OUT _total INT)
BEGIN
SELECT COUNT(*) INTO _total FROM emp WHERE deptno=_deptno;
END
CALL get_total(20,@emptotal);
SELECT @emptotal;概述：
特点：
语法：
CREATE TRIGGER trigger_name
trigger_timer trigger_event ON tbl_nmae FOR ENCH ROW --表示每操作一行就会触发一次
BEGIN
routine_body
END
#trigger_timer:{BEFORE|AFTER}
#trigger_event:{INSERT|UPDATE|DELETE}注：
触发器有两个特殊的变量：old和new
语法：
DROP TRIGGER [IF EXISTS] trigger_name;
SHOW TRIGGERS;
执行触发器监听操作，不需要显示调用。
eg：
CREATE TRIGGER tri_comm BEFORE INSERT ON emp FOR EACH ROW
--每次向emp表插入数据事件之前，就会触发触发器
BEGIN
if new.comm>new.sal THEN --新插入的数据comm>sal时
SET new.comm = new.sal;
END IF;
END即使小于sal也会触发触发器。
CREATE TRIGGER tri_deptno BEFORE DELETE ON depat FOR EACH ROW
BEGIN
IF old.deptno<>40 THEN
--SIGNAL SQLSTATE 'HY000'：抛出异常信息 MESSAGE_TEXT：固定写法
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '不能删除还有员工的部门信息'; --必须写在一句上
END IF;
END
DELETE FROM dept WHERE deotpo=30;1.利用sql复制一份emp表，生成一个名叫emp1的表。
CREATE TABLE 新表名 AS (SELECT * FROM 被赋值表名);2.在emp1上建立一个触发器，要求在更新emp1的数据时，如果该记录的comm为null，则更新后自动变成0，如果该记录的comm小于sal，则更新后comm等于sal。
CREATE TRIGGER tri_emp BEFORE UPDATE ON emp1 FOR EACH ROW
BEGIN
IF old.comm is NULL THEN
SET new.comm = 0;
ELSEIF old.comm < old.sal THEN
SET new.comm = new.sal;
END IF;
END
select * from emp1;
UPDATE emp1 SET deptno = 30 WHERE empno=7369 --old.comm is NULL
UPDATE emp1 SET deptno = 40 WHERE empno=7499 --old.comm < old.sal7）事务
概述：
注意：
（Atom,Constant,Isoation,Duration）
BEGIN;
INSERT INTO dept VALUES(50,'TEST','CHINA');
INSERT INTO dept VALUES(60,'JAVA','CHENGDU');
COMMIT; --提交事务
或：
ROLLBACK; --回滚事务安装：pip install PyMySQL
格式：
import pymysql
#打开数据库
db = pymysql.connect(host="ip地址，本机是localhost",user="root",password="xxxx",database="xxx")
#适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#使用execute()方法执行SQL查询
cursor.execute('SELECT VERSION()') #要执行的sql语句
#使用fetchone()方法获取单条数据
data = cursor.fetchone()
print("Database version:%s"%data)
#关闭数据库连接
db.close()使用with语法：
#打开数据库
with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:
# 适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
cursor.execute('SELECT VERSION()') # 要执行的sql语句
# 使用fetchone()方法获取单条数据
data = cursor.fetchone()
print("Database version:%s" % data)
# 关闭数据库连接
#db.close() 不需要close参考：
eg:
with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:
# 适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
cursor.execute('DROP TABLE IF EXISTS mytable') # 要执行的sql语句
sql = """
CREATE TABLE mytable(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT
)
"""
cursor.execute(sql)eg2:
with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:
# 适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
sql = """
INSERT INTO mytable(FIRST_NAME,LAST_NAME,AGE)
VALUES('%s','%s','%s')
"""%('Mac','Mohan',20)
try:
cursor.execute(sql)
db.commit() #事务不会自动提交，故需要commit一下，但mysql5.0以上的版本事务是自动提交的，没有定义十事务的时候也是自动提交的，可以省略
print('插入成功！')
except:
db.rollback()
print('插入失败，回滚')eg3:
with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:
# 适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
sql = """
SELECT* FROM emp WHERE sal>%s
"""%(1000)
try:
cursor.execute(sql)
results = cursor.fetchall() #还有一个cursor.fetchone()方法
print('结果数据共{}条'.format(cursor.rowcount))
for row in results:
print(row)
except:
print('发生错误，无法查询数据')结果以元组形式显示出来了：
import pymysql
#打开数据库
db = pymysql.connect(host="localhost",user="root",password="123456",database="study")
#适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#使用execute()方法执行SQL查询
cursor.callproc('get_maxsal') #要执行的sql语句
#使用fetchone()方法获取单条数据
result = cursor.fetchall()
print(cursor.rowcount) #显示结果的行数
print(result)
#关闭数据库连接
db.close()没有参数：
有参数的get_sal:
报错：
pymysql.err.OperationalError: (1318, 'Incorrect number of arguments for PROCEDURE study.get_sal; expected 2, got 0')
import pymysql
#打开数据库
db = pymysql.connect(host="localhost",user="root",password="123456",database="study")
#适用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#对于out和inout参数python不支持，随便定义一个值即可
cursor.callproc('get_sal',('BLAKE',0)) #要执行的sql语句
#对于out和inout型的参数，是保存在服务器的变量中的，可以通过select语句查询
#对应的参数访问格式为@_存储过程名_0,@_存储过程名_1，以此类推
cursor.execute('SELECT @_get_sal_0,@_get_sal_1')
print(cursor.fetchall()) #显示结果的行数
#关闭数据库连接
db.close()有参数：
因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- obuygou.com 版权所有 赣ICP备2024042798号-5
违法及侵权请联系：TEL:199 18 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务