MySQL
数据库的概率
row：行（记录） rank：列 （字段） 是关系数据库（由行和列组成的） 数据库由表、关系以及操作对象组成
补充： 非关系数据库：Redis MongoDB 非关系数据库的数据不是以表的形式保存的，而是以键值对的形式保存的 常见的关系数据库：SQL Server MySQL Oracle DB2 SQLite
注意
如判断是否为空的时候，不要用 ==null 而是用 IS NULL
主键
1.主键必须是非空且唯一的 2.一个表只能有一个主键，主键约束确保了表中的行是唯一的 #主键可以是一列或者多列 比如成绩表可以是(课程id和学生id作为联合主键) 3.表中可以没有主键，但是通常情况下应当为表设置一个主键
选择原则：
-
最少性
尽量选择单个字段作为主键
-
稳定性
尽量选择数值更新少的列作为主键
外键
简单来说，就是 子表 中对应的于 主表 的列，在子表中称为外键或者引用键 外键用来 强制引用完整性
参照完整性：主键和外键
自定义完整性：存储过程、触发器
结构化查询语句SQL
SQL语句的分类
1.数据查询语句(DQL) Select 数据选择查询
2.数据操作语句(DML) Insert 插入
Update 更新
Delete 删除
3.数据定义语句(DDL) Create 新建
Alter 修改结构
Drop 删除
Rename 重命名
Truncate 删除
4、数据控制语句(DCL) Grant 授权
Revoke 解除授权
语句规则
1.SQL语句要以 ; 结尾，一条SQL语句可以描述一个数据库操作，在RDBMS中，SQL语句也是逐条执行的 2.SQL语句不区分大小写，例如SELECT 和 select是一个意思 3.字符串和日期值需要用引号括起来，如'abc' ,'2020-09-20'这样的，但是数值型的值，如1,2,3等这样的不需要用括号括起来
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名
查询数据库
show databases； show create table 表名 #可以查到表的定义语句
删除数据库
drop database 数据库名；
连接到数据库
use 数据库名
创建表
create table 表名(
列名 数据类型 约束；
列名 数据类型 约束；
)
数据类型
char(n) 定长字符串：字符串长度是一定的，如果实际长度不足，将以空格来补齐。比如char(10),那么如果实际存储的字符是hello，将存储hello+5个空格 varchar(n) 不定长字符串，如果长度不足不会用空格补充，如果实际存储的是hello，则存储的字符长度为5 int 整数值 float 单精度浮点型 text 大容量文本 blob 二进制大对象 （存图片、视频等）
删除表
DROP TABLE 表名
通过外键约束链接到一起的表不能被删除，在删除表之前，必须先删除约束，在删除表时，我们是表的所有者或者对该表有管理员权限
复制表
复制表，且复制他的结构和数据
create table 新表名 as （select * form 被复制的表名）；
只复制表的结构，不复制数据
create table 新表名 like 被复制的表名；
修改表名
alter table 表名 rename as 新的表名； 例： alter table stu rename as student；
添加列
alter table 表名 add column 列的定义； 例：alter table Product ADD COLUMN product_name VARCHAR(100);
删除列
alter table 表名 drop column 列名； 例：alter table students drop column sname；
创建主键(PRIMARY KEY)
类型1
create table stu( ID INT PRIMARY KEY, sname VARCHAR(5) NOT NULL, sex int )
类型2
create table stu（
id int，
sname varchar(5) NOT NULL,
sex int,
constraint pk_stu PRIMARY KEY (id)
）
类型3(复合主键)
create table stu(
id int,
sname varchar(5) NOT NULL,
sex int,
PRIMARY KEY(id,sname)
)
补充主键
用ALTER语句补充声明主键 ALTER TABLE stu ADD CONSTRAIN pk_stu PRIMARY KEY(id,name);
删除主键
ALTER TABLE stu DROP PRIMARY KEY; #主键只有一个，删除即可
创建外键
CREATE TABLE IF NOT EXISTS demo_new ( cid INT PRIMARY KEY, sid INT, FOREIGN(sid) REFEREENCES demo(sid) )
更新时外键会跟着一起更新
CREATE TABLE IF NOT EXISTS demo_new ( cid INT PRIMARY KEY, sid INT, FOREIGN(sid) REFEREENCES demo(sid) ON UPDATE CASECADE )
更新时外键会跟着一起更新，删除的时候也会跟着一起删除
CREATE TABLE IF NOT EXISTS demo_new ( cid INT PRIMARY KEY, sid INT, FOREIGN(sid) REFEREENCES demo(sid) ON UPDATE CASECADE ON DELETE CASECADE )
更新时外键会跟着一起更新，删除的时候也会不会跟着一起删除
CREATE TABLE IF NOT EXISTS demo_new ( cid INT PRIMARY KEY, sid INT, FOREIGN(sid) REFEREENCES demo(sid) ON UPDATE CASECADE ON DELETE RESTRICT )
补充外键
ALTER TABLE demo_new ADD CONSTRAINT fk_demo_new FOREIGN KEY(sid) REFERENCES demo(id) ON DELETE CASECADE ON UPDATE CASCADE;
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
自增长(AUTO_INCREMENT)
CREATE TABLE demo( #设置为自增的列必须设置为主键，并且一张表只能有一个自增列 ID int AUTO_INCREMENT PRIMARY KEY, ) insert into demo values (DEFAULT,'tester1');
唯一约束(UNIQUE)
与主键的区别：约束唯一但是可以为空，但是主键不可以为空 CREATE TABLE demo( vip VARCHAR(50) UNIQUE )
检查约束
CREATE TABLE demo(
sex SET('男'，'女')
vip VARCHAR(50) UNIQUE
)
创建默认值(DEFAULT)
CREATE TABLE demo(
sex SET('男'，'女')
vip VARCHAR(50) UNIQUE
tel VARCHAR(11) DEFAULT '13799490088'
)
DML语句（增，删，改）
插入
insert into 表名 （列名） values (值)
例：insert into Students(Sname,Saddress,SGrade) values ('张三'，'福建厦门'，'6')
注意：如果在设计表的的时候就指定某列不为空，但插入数据的时候没有提供这个值，则mysql会给该列插入一个默认的值。
一次插入多个值
insert into students(Sname,SGrade,SSex) VALUES
('张课'，7,1)，
('张是'，7,1)，
('张删除'，7,1)，
('张对'，7,1)，
('张是的'，7,1)，
('张速度'，7,1)；
更新
UPDATE 表名 SET 列名 = 更新值 WHERE 条件 UPDATE Students SET SSEX = 0; UPDATE Students SET Scores = Scores + 5 WHERE Scores <= 95;
删除
DELETE FROM 表名 WHERE 删除条件
DELETE FROM stu； 和 TRUNCATE TABLE stu的区别 DELETE不会提交事务 而TRUNCATE会提交事务 没办法回滚数据。 TRUNCATE不会记录删除的日志，TRUNCATE的效率比较高。
DQL 查询语句
SELECT 列名 FROM 表名 WHERE 查询条件语句 ORDER BY 排序的列名 [DESC 或 ASC] 默认是升序 ASC 降序 DESC 例：SELECT stuid,age+10 FROM students;
as 可以重命名 SELECT stuid AS '学生' FROM students;
去重 DISTINCT select DISTINCT sname from students；
限制行数
select 列1，列2 .... from 表 limit offset，count； offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1 count指定要返回的最大行数 两个参数的时候 select * from emp limit 5,6 --从偏移量为5的行开始取，取6行 一个参数的时候 select * from emp limit 5 -- 取前5个 取工资最高的前三个 select * from emp ORDER BY DESC LIMIT 3; 取工资第四高的 select * from emp ORDER BY DESC LIMIT 3,1
where
注意，在where子句中字符串和日期要用单引号括起来
select * from emp where hiredate >='1981-04'
运算符
BETWEEN AND 在两个值之间(包含) IN(list) 匹配列出的值 LIKE 匹配一个字符模式 IS NULL 是空值 <> 不等于 SELECT reader.`NAME`,reader.COMPANY,borrow.BORROW_DATE FROM reader INNER JOIN borrow ON reader.READER_ID = borrow.READER_ID AND borrow.BORROW_DATE IN (SELECT borrow.BORROW_DATE FROM reader INNER JOIN borrow ON reader.READER_ID = borrow.READER_ID AND reader.`NAME`='赵正义') AND reader.`NAME`<>"赵正义";
优先级 AND 如果组合的条件都为真则返回真值 3 OR 如果组合的条件 之一是真值，返回真值 4 NOT 如果条件为假则返回真值 2 所有比较运算 1
SELECT empno，ename，job，sal FROM emp WHERE sal>=1100 AND job='CLERK';
BETWEEN
SELECT ename，sal FROM emp WHERE sal BETWEEN 1000 AND 1500;
IN 主要和括号里的一个相等就匹配成功
SELECT empno，ename，sal，mgr FROM emp WHERE mgr IN(7902,7566,7788);
LIKE执行通配查询
查询条件可包含文字字符和数字
% 可表示零或者多个字符
_ 可表示一个字符
SELECT ename FROM emp WHERE ename LIKE 'S%';
IS NULL
SELECT ename，mgr FROM emp WHERE mgr is NULL;
ORDER BY
ASC 升序，缺省 默认是ASC DESC 降序
数据分组和聚合函数
常见的聚合函数
COUNT: 返回结果集中行的数目 count(*) 返回表中的记录数 count(列名) 返回非空记录数 SUM: 返回结果集中所有值的总和 AVG： 返回结果集中所有值的平均值 MAX： 返回结果集中所有值的最大值 MIN： 返回结果集中所有值的最小值
这样会把列名中为NULL的值记为0 SELECT AVG(IFNULL(列名，0)) FROM 表名；
分组计算平均值 SELECT deptno，AVG(sal) FROM emp ORDER BY deptno；
Having子句
Having子句的作用是对分组进行过滤 记录被分组 使用组函数 匹配HAVING子句的组被显示 匹配HAVING子句的组被显示 HAVING子句应放在GROUP BY后面，ORDER BY之前
限定分组结果
SELECT deptno，max(sal) FROM emp GROUP BY deptno HAVING max(sal)>2900;
SELECT job,SUM(sal) AS PAYROLL FROM emp WHERE job NOT LIKE 'SALES%' GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal);
显示组最高平均工资
SELECT max(avg(sal)) FROM emp GROUP BY deptno;
分组查询的区别
WHERE子句： - 从数据源中去掉不符合其搜索条件的数据 GROUP BY 子句： - 搜索数据行各个组中，统计函数为各个组计算统计值，每个组输出一行结果 HAVING 子句： - 从分组计算结果中进行过滤，去掉不符合其组搜索条件的各组数据行
因为A1,B1没有参与查询所以只返回第一行，然后max返回最大的
执行顺序
子查询
#
子查询的规则
子查询要用括号括起来 将子查询放在比较运算符的右边 子查询中不要加order by 语句 对单行子查询使用单行运算符 = > < 对多行子查询语句使用多行运算符 in 、 any 、 all <any 小于最大值 >any 大于最小值 <all 小于最小值 >all 大于最小值
select empno,ename,job from emp where sal < ANY (select sal from emp where job = 'CLERK') 只有有一个满足小于就行
多表查询
注意
所有的多表连接都必须指定连接条件，（使用where关键字指定或xxx join on指定）。不指定连接条件的时候，将形成笛卡尔积，这是没有任何意义的。 当要显示连接条件的所有复合数据的时候，使用内连接。如果既要显示复合条件的数据，又要显示不符合连接条件的数据，则需要使用外连接。
内连接
INNER JOIN
外连接
左外连接（LEFT JOIN） 右外连接(RIGHT JOIN)
内连接
两个书写的方法也一样的
select table1.column，table2.column from table1,table2 where table1.column1 = table2.column2 #一般是主外键连接 select table1.column，table2.column from table1 INNER JOIN table2 on table1.column1 = table2.column2 #一般是主外键连接
在多表连接的时候使用过滤条件
SELECT emp.empno，emp.ename，emp.deptno， dept.deptno，dept.loc FROM emp，dept WHERE emp.deptno = dept.deptno and emp.ename = 'KING'
外连接
左连接
在LEFT JOIN 中，返回左表的所有行，即使左表中有不符合连接的条件的记录，也会在查询结果中显示，同理RIGHT JOIN就是返回右表中所有记录。 左右区分的使用，就是看要全部显示记录的那张表是在左边还是右边
SELECT emp.ename,dept.deptno FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno; #就是返回右边的全部记录，包括在左表中没有对应到的记录
右连接
SELECT emp.ename,dept.deptno FROM dept LEFT JOIN emp ON emp.deptno = dept.deptno; #就是返回左边的全部记录，包括在右边中没有对应到的记录
存储过程
概念
存储过程是存储在数据库目录中的一段声明性SQL语句，存储过程写好后，可以被其他编程语言直接调用，非常方便。
变量
-
用户自定义变量
select max(sal) into @maxsal from emp;
找到sal的最大值然后将他赋值给sal
select MAX(price) into @maxsal from book; SELECT @maxsal;
存储过程
定义存储过程
DELIMITER // CREATE PROCEDURE 存储过程名字() BEGIN --存储过程代码 END // DELIMITER 一般写成这样也行 CREATE PROCEDURE 存储过程名字() BEGIN --存储过程代码 END
存储过程例子
DROP PROCEDURE IF EXISTS get_maxsal;
DELIMITER//
CREATE PROCEDURE get_maxsal()
BEGIN
SELECT max(sal) from emp;
END//
一般写这样就行
CREATE PROCEDURE get_maxsal()
BEGIN
SELECT max(sal) from emp;
END
CALL get_maxsal(); --调用存储过程
在存储过程中声明变量
声明变量的语法： DECLARE variable_name datatype(size) DEFAULT default_value; 变量赋值： SET 变量 = 变量值 -- 注意这里变量前面没有@符号 也可以用sql语句赋值： SELECT xxx into 变量名 from xxxx
例子
DROP PROCEDURE IF EXISTS get_maxsal;
CREATE PROCEDURE get_maxsal()
BEGIN
DECLARE avgsal INT;
SELECT avg(sal) into avgsal from emp;
SELECT enmae,sal from emp where sal > avgsal;
END
call get_maxsal();
参数有三种模式
在MySQL中，参数有三种模式：IN OUT 或 INOUT IN 是默认模式，在存储过程中用定义IN参数，调用该存储过程的时候必须将参数传递给存储过程 OUT 可以再存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序，由于OUT参数并没有被赋值，所以不能读取，只能赋值。 INOUT INOUT参数是IN和OUT参数的组合，这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序
例子
DROP PROCEDURE IF EXISIS get_sal;
CREATE PROCEDURE get_sal(IN empname VARCHAR(5)，OUT empsal INT)
BEGIN
SELECT sal INTO emsal from emp WHERE ename = empname；
END
--在存储过程中定义的参数，会在mysql中自动生成以下划线命名的参数
CALL get_sal('BLAKE',@_empsal)；
SELECT @_empsal；
例子
CREATE PROCEDURE get_total(IN _dept INT , OUT _total INT) BEGIN SELECT count(*) INTO _total FROM emp WHERE deptno = _dept; END CALL get_total(40,@emptotal); SELECT @emptotal;
流程控制
可以在存储过程中加入流程控制语句 分支 IF CASE 循环 REPEAT WHILE
IF
IF expression THEN
statements
END IF；
IF expression THEN
statements
ELSE
else-statments
END IF；
CASE
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;
END CASE;
END;
REPEAY
REPEAT statement_list UNITL search_condition END REPEAT;
WHILE
WHILE search_condition DO
statement_list
END WHILE;
CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO SELECT 'hello'; SET v1 = v1 -1; END WHILE; END
事务
概念
- 事务是作为单个逻辑单元执行的一系列操作 - 多个操作作为一个整体向系统提交，要么执行、要么都不执行，事务是一个不可分割的工作逻辑单元 - 这特别适合于多用户同时操作的数据通信系统，例如：订票、银行、保险公司以及证券交易系统等
事务的特性
-
原子性
组成事务处理的语句形成一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单位。比如：银行转账过程中，必须同时从一个账号减去转账金额，并加到另一个账户中，只改变了一个账户是不合理的。
-
一致性
在事务处理执行前后，数据库是一致的，也就是说，事务应该正确的转换系统状态，比如：银行转账过程中，要么转账金额从一个账户转入另一个账户，要么两个账户都不变，没有其他情况
-
隔离性
一个事务处理对另一事务处理没有影响，就是说任何事务都不可能看到一个处在不完整状态下的事务，比如说，银行转账过程中，在转账事务没有提交之前，另一个转账事务只能处于等待状态。
-
持久性
事务处理的效果能够被永久保存下来，反过来说，事务应当能够承担所有的失败，包括服务器、进程、通信以及媒体失败等等，银行转账过程中，转账后账户的状态要能被保存下来。
常见事务控制语句
BEGIN 或 START TRANSACTION;显式地开启一个事务；
COMMIT； 用于提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；用于回滚事务，回滚会结束用户的事务，并撤销正在进行的所有为提交的修改；
例子
BEGIN； INSERT INTO dept VALUES(50,'DDDD')； INSERT INTO dept VALUES(50,'SSSS')； COMMIT; --提交事务
触发器
触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的SQL语句，它们主要用于强制复杂的业务规则或要求。
特点
它与表紧密相连，可以看作表定义的一部分； 它不能通过名称被直接调用，更不允许带参数，而是当用户对表中数据进行修改的时候，自动执行； 它可以用于MySQL约束，默认值和规则的完整性检查，实施更为复杂的数据完整性约束。
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name FOR EACH ROW #每一行
BEGIN
routine_body
END
#trigger_time ：{BEFORE|AFTER}
#trigger_event : {INSERT|UPDATE|DELETE}
例子
向emp表中插入新数据时，如果comm大于sal，则将comm设置为跟sal一样的值
CREATE TRIGGER tri_comm BEFORE INSERT ON emp FOR EACH ROW
BEGIN
if new.comm >new.sal THEN
SET new.comm = new.sal;
END IF;
END
删除dept表中还有员工的部门，提示错误 CREATE TRIGGER tri_deptno BEFORE DELETE ON dept for EACH ROW BEGIN if old.deptno<>40 THEN SIGNAL SQLSTATE'HY000' SET MESSAGE_TEXT = '不能删除还有员工的部门信息'；--必须写在一句上 END IF; END;
通过Python链接mysql
import pymysql
#建立数据库建立
db = pymysql.connect(host="192.168.xx.xx",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()
建表
import pymysql
#建立数据库建立
with pymysql.connect(host="localhost",user="root",password="123456",database="library") 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)
查询多条数据
import pymysql
#建立数据库建立
with pymysql.connect(host="localhost",user="root",password="123456",database="library") as db:
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#使用execute()方法执行SQL查询
sql="SELECT*FROM emp WHERE sal>%s"%(1000) #要执行的sql语句
try:
cursor.execute(sql)
results = cursor.fetchall() #还有一个cursor.fetchone()方法
print('结果数据共{}条'.format(cursor.rowcount))
for row in results:
print(row)
except:
print('发生错误，无法查询到数据')
调用存储过程(没有使用参数)
import pymysql
#建立数据库建立
with pymysql.connect(host="localhost",user="root",password="123456",database="library") as db:
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
cursor.callproc('get_maxsal') #调用callproc方法调用存储过程
results = cursor.fetchall()
print(cursor.rowcount)#显示行数
print(results)
调用存储过程（带参数）
import pymysql
#建立数据库建立
with pymysql.connect(host="localhost",user="root",password="123456",database="library") as db:
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#对于out或inout参数python不支持，随便定义一个值即可
cursor.callproc('get_sal'，('BLAKE'，0))
#对于out和inout型的参数，是保存在服务器的变量中，可以通过select语句查询
#对于的参数访问格式为 @_存储过程名_0,@_存储过程名_1，以此类推
cursor.execute("SELECT @_gal_sal_0，@_get_sal_1")
print(cursor.fetchall())
作业
-- 1.利用sql复制一份emp表，生成一个名叫emp1的表。
CREATE TABLE emp1 AS (SELECT * FROM emp);
-- 2.在emp1上建立一个触发器，要求在更新emp1的数据时，如果该记录的comm为null，则更新后自动变成0，如果该记录的comm小于sal，则更新后comm等于sal
CREATE TRIGGER tri_com 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 comm=110 WHERE empno=7369;
-- 3.编写一个存储过程，该存储过程接受一个部门编号作为参数，可以求出该部门的员工总数。
DROP PROCEDURE IF EXISTS pro_dep;
CREATE PROCEDURE pro_dep(IN deptno1 INT,OUT total INT)
BEGIN
SELECT COUNT(empno) INTO total FROM emp1 WHERE deptno = deptno1;
END
CALL pro_dep(20,@emptotal);
SELECT @emptotal;
8053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
