约束
概念:
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的正确性、完整性、有效性、唯一性。
分类:
-
primary key
:主键约束,指定某列的数据不能重复、唯一、非空。 -
not null
:非空约束,指定某列不为空。 -
unique
:唯一约束,指定某列和几列组合的数据不能重复。 -
foreign key
:外键,指定该列记录属于主表中的一条记录,参照另一条数据。
非空约束:not null
听名字就能理解,被非空约束的列,在插入值时必须非空。
-
创建表时添加约束
-
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name不能为空 ); INSERT INTO stu (id, name) VALUES (1,"zs") INSERT INTO stu (id, name) VALUES (2,NULL) ALTER TABLE stu MODIFY NAME VARCHAR(20); ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL UPDATE stu SET name = "lisi" WHERE id = 2
创建表完后,添加非空约束
-
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;-- -- 如果表中含有null 则添加失败
删除name的非空约束
-
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束:unique
当为字段指定唯一约束后,那么字段的值必须是唯一的。
唯一约束可以有NULL值,但是只能有一条记录为null. 有待研究!
-
在创建表时,添加唯一约束
-
CREATE TABLE stu1( id INT, phone_number VARCHAR(20) UNIQUE -- 手机号 ); INSERT INTO stu1 (id,phone_number) VALUES (1,"123") INSERT INTO stu1 (id,phone_number) VALUES (2,"123") ALTER TABLE stu1 DROP INDEX phone_number; ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE; UPDATE stu1 set phone_number = "456" WHERE id = 2
删除唯一约束
-
ALTER TABLE stu1 DROP INDEX phone_number;
-
在表创建完后,添加唯一约束
-
ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE; ---- 如果表中含有相同数据 则添加失败
主键约束:primary key
主键(PRIMARY KEY)是用于约束表中的一行,作为这一行的标识符,在一张表中通过主键就能准确定位到一行,因此主键十分重要。
主键要求这一行的数据不能有重复且不能为空。
-
注意:
-
含义:非空且唯一
-
一张表只能有一个字段为主键
-
主键就是表中记录的唯一标识
-
-
在创建表时,添加主键约束
-
create table stu2( id int primary key,-- 给id添加主键约束 name varchar(20) ); INSERT INTO stu2 (id,name) VALUES (1,"zs") INSERT INTO stu2 (id,name) VALUES (NULL,"lisi") ALTER TABLE stu2 DROP PRIMARY KEY; ALTER TABLE stu2 MODIFY id INT PRIMARY KEY; INSERT INTO stu2 (id,name) VALUES (2,"lisi")
删除主键
-
ALTER TABLE stu DROP PRIMARY KEY; --注意:如果id有null值 则删除失败
-
创建完表后,添加主键
-
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长:
MySQL提供了主键自动增长的功能,这样用户就不用再为是否有主键是否重复而烦恼了。
当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值+1,也就不会出现重复主键的可能了。
使用 auto_increment 可以来完成值得自动增长,自动增长的列必须是主键。
-
创建表时,添加主键约束,并且完成主键自增长
-
create table stu3( id int primary key auto_increment,-- 给id添加主键约束和自动增长 name varchar(20) ); INSERT INTO stu3 (id,name) VALUES (1,"zs") INSERT INTO stu3 (id,name) VALUES (2,"lisi") INSERT INTO stu3 (id,name) VALUES (NULL ,"wangwu") DELETE FROM stu3 WHERE id = 3 ALTER TABLE stu3 MODIFY id INT; ALTER TABLE stu3 MODIFY id INT AUTO_INCREMENT;
删除自动增长
-
ALTER TABLE stu MODIFY id INT;
-
添加自动增长
-
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束:foreign key
一个表的一列(a列)指向另一个表的b列(主键),那么这一列(a列)就是外键约束。
比如人和车,把两者保存在一张表里显然是不合理的;
而保存成两张表,则需要想办法表示出二者的关系,即人拥有车,而车属于人。
create table person (
id int primary key,
pname varchar(30)
);
create table car(
id int primary key,
cname varchar(30),
pid int ,
foreign key(pid) references person(id)
);
-- 如果person表中没有人,我们是无法直接添加car表的
INSERT INTO car VALUES (1,"奔驰",1),(2,"宝马",2),(3,"奥迪",2)
INSERT INTO person VALUES (1,"zs"),(2,"lisi"),(3,"ww")
-- 如果在car表中添加一个persion表中不存在的人,也是不能添加的,这就是外键约束的作用
INSERT INTO car VALUES (4,"五菱",4)
SELECT * from person
SELECT * from car
DROP TABLE person
ALTER TABLE car DROP FOREIGN KEY car_ibfk_1;
就是让表与表产生关系,从而保证数据的正确性。
定义外键所在的表叫从表,从表所依赖的数据表是主表
外键可以为null
-
从表依赖于主表,我是否可以直接删除主表?
-
SELECT * from person SELECT * from car DROP TABLE person
-
解决方法:先删除从表,在删除主表,让主表不在被引用即可
-
删除外键
-
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; ALTER TABLE car DROP FOREIGN KEY car_ibfk_1;
创建表时,可以添加外键
-
create table 表名( 外键列, -- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ); -- 定义外健名称是为了 后期方便删除外健约束
创建表之后,添加外键
-
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
级联操作. 外键的建立是多的一方指向一的一方。
-
添加级联操作语法:
-
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
-
-
分类:
-
-
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
3. 级联操作慎用
数据库的设计
多表之间的关系
-
一对一(了解):
-
如:人和身份证
-
分析:一个人只有一个身份证,一个身份证只能对应一个人。
-
-
一对多(多对一):
-
如:部门和员工
-
分析:一个部门有多个员工,一个员工只能对应一个部门
-
-
多对多:
-
如:学生和课程
-
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
-
实现关系
-
一对多(多对一):
如:部门和员工
一个员工只能选一个部门,一个部门可以被多个员工选择。
实现方式:在多的一方建立外键,指向一的一方的主键。
-
多对多:
如:学生和课程
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,
这两个字段作为第三张表的外键,分别指向两张表的主键
3. 一对一(了解):
* 如:人和身份证
-
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。也可以两张表拥有共同主键,主键表示相同含义。
数据库设计的范式
概念:
设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,
这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:
-
第一范式(1NF)、
-
第二范式(2NF)、
-
第三范式(3NF)、
-
巴斯-科德范式(BCNF)、
-
第四范式(4NF)
-
第五范式(5NF,又称完美范式)。
一般都遵循前三个,我们只学习前三个。
分类:
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码
(在1NF基础上消除非主属性对主码的部分函数依赖)
几个概念:
1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。 属性组(学号,课程名称) --> 分数
-
完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
缺了A属性组中哪一个属性,B都没法确定分数值了。
例如:(学号,课程名称) --> 分数
3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) -- > 姓名
-
传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,
在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号-->系名,系名-->系主任
-
码:如果在一张表中,一个属性或属性组,被其它所有属性所完全依赖,称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)二者可以确定表中任何数据
* 主属性:码属性组中的所有属性 (学号,课程名称)
* 非主属性:除码属性组的属性 (姓名,系名,系主任,分数)
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
目的:规范化目的是使结构更合理,消除存储异常,使数据冗余尽量小。便于插入、删除和更新
多表查询:
定义:
多张表的查询,语法和单表查询差不太多。
查询语法:
select
列名列表
from
表名列表
where....
多表查询概述:
-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
-- 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小白龙','男',3000,NULL,NULL);
-- 多表查询
SELECT * FROM emp,dept;
/*如果按照上述方式查询,其实会把两个表的所有可能性都列举出来,查询出两个表的结果集,
这个结果集我们称之为笛卡尔积 */
笛卡尔积:
笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尔积(Cartesian product),又称直积,表示为X × Y,
第一个对象是X的成员 而第二个对象是Y的所有可能有序对的其中一个成员 。
比如: 集合A={a,b} , 集合B={0,1,2}
两个集合的笛卡尔积为:
{ (a,0),(a,1),(a,2),(b,0),(b,1),(b,2) }
说白了,就是有A集合,有B集合,我们取AB集合的所有组合情况。那么AB组合情况就是2*3共6种情况。
如下图是笛卡尔积的结果:
会发现有好多无用的数据,比如红线框起来的部分。
多表查询分类:
-
内连接查询 --- A表和B表相交的部分
-
隐式内连接
-
显式内连接
-
-
外连接查询
-
左外连接
-
右外连接
-
-
子查询
隐式内连接查询:
使用where条件清除无用数据即可。
sql语句;
-- 内连接查询
-- 隐式内连接 显式内连接
-- 隐式内连接:使用where条件清除无用数据。
-- 1.查询所有员工信息和对应部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;-- 提示出来的引号 可加可不加
-- 2.1 查询员工表名称,性别,部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
-- 2.2 简化写法 给表名起别名 t1 是别名
SELECT
t1.`name`,
t1.`gender`,
t2.`name`
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;
显式内连接查询:
-
语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件
-
[inner] 可选关键字
-
显式内连接sql语句:
-
-- 1 查询所有员工信息和对应的部门信息 SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; -- 1.1 INNER 可以省略 SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.id; -- 1.2 也可以起别名 SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id` = t2.`id`;
-
内连接查询注意事项:
-
1.从哪些表中查询数据,
2.条件是什么,判断什么是有效数据,什么是无效数据
3.查询哪些字段,我们会选择我们需要的 字段来查询
外连接查询:
-
概念:
若干个表中,查询某个表的全部信息的同时在查询另一张表的交集信息。
左外连接:
-
语法:
-
select 字段列表 from 表1 left [outer] join 表2 on 条件;-- 表1是左表,查询的是左表所有数据以及和表2其交集部分。
-
[OUTER]关键字可选
-
SELECT * FROM emp LEFT JOIN dept ON emp.`dept_id` = dept.`id`;
右外连接:
-
语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;-- 表2是右表 查询的是右表所有数据以及和表1其交集部分。
子查询:
-
概念:
-
查询中嵌套查询,称嵌套查询为子查询
-
一个select中嵌套另一个select,也属于子查询
-
子查询基本使用
-
查询最高工资的员工信息
-
第一步:先查询最高工资是多少 9000
-
SELECT MAX(salary) FROM emp;-- 先查询最高工资是多少 9000
-
第二步:再查询员工信息 并且薪资=9000的
-
SELECT * FROM emp WHERE emp.`salary` = 9000;--
-
-
子查询完成上述操作
-
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
事务
事务基本介绍:
-
概念:
-
事务(transaction)指逻辑上的一组操作,组成这组操作的各个单元,要么全部成功,要么全部不成功
-
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。 没有事务前 会遇到的问题: 张三和李四各有1000元,张三给李四转500,那么张三应该剩余500,李四剩余1500。 但是如果在张三给李四刚转完钱之后,程序出问题了,会导致李四收不到这笔500元的款项 造成张三损失了500,不翼而飞了,而李四余额还是1000,不变
-
这次操作涉及2条sql语句操作
-
给张三账户减少500
-
给李四账户增加500
-
-- 举例: CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (10), balance DOUBLE ) ; -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); SELECT * FROM account; -- 张三给李四转500 -- 1.张三账户-500 UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan'; 出错啦... -- 2. 李四账户+500 UPDATE account SET balance = balance + 500 WHERE NAME ='lisi'; UPDATE account SET balance = 1000; -- 数据还原到1000
-
在默认情况下,MySQL每执行一条SQL语句,都是一个单独的事务。
-
如果需要在一个事务中包含多条SQL语句,那么需要开启事务和结束事务。
-
开启事务:start transaction
-
结束事务:commit(提交)或 rollback(回滚)
-
-
在执行SQL语句之前,先执行strat transaction,这就开启了一个事务(事务的起点),然后可以去执行多条SQL语句,最后要结束事务。
commit表示提交,即:事务中的多条SQL语句所做出的影响会持久化到数据库中;或者rollback,表示回滚,即回滚到事务的起点,之前做的所有操作都被撤消了。
-
操作:
-
-- 开启事务 START TRANSACTION; -- 张三给李四转500 -- 1. 张三账户-500 UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan'; -- 出错啦... -- 2. 李四账户+500 UPDATE account SET balance = balance + 500 WHERE NAME ='lisi'; -- 如果没有问题 那么就提交 COMMIT; -- 发现问题了 就回滚事务 ROLLBACK; UPDATE account SET balance = 1000;-- 数据还原到1000 SELECT * FROM account;
-
事务提交的方式:
-
事务提交的两种方式:
-
自动提交:
-
mysql就是自动提交的,你用mysql写完sql语句 执行就自动被提交了
-
一条DML(增删改)语句会自动提交一次事务。
-
-
-
手动提交:(oracle)
-
修改事务的默认提交方式:
-
查看事务的默认提交方式:SELECT @@autocommit; 1代表自动提交 0代表手动提交
-
修改默认提交方式: set@@autocommit = 0; 如果你设置了手动提交,那么每次执行完sql语句都需要commit手动提交一次,否则不会持久化存储 如果你设置了自动提交,那么每次执行一些sql语句,就不需要手动特意去提交一次。自动会持久化存储
-
-
-
事务的四大特性(ACID)
-
原子性(Atomicity)
事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功,要么全部执行失败。
-
一致性(Consistency)
事务执行后,数据库状态与其它业务规则保持一致。如转账业务,无论事务执行成功与否,参与转账的两个账号余额之和应该是不变的。(其他特性都是为一致性这个特性服务的)
-
隔离性(Isolation)
隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并发中的事务不会相互干扰。
-
持久性(Durability)
一旦事务提交成功,事务中所有的数据操作都必须被持久化到数据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须能保证通过某种机制恢复数据。
事务的隔离级别 (隔离性)
-
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
数据库事务的隔离级别有4种,由低到高分别为Read uncommitted、Read committed、Repeatable read、Serializable。 序列化
-
存在问题:
-
脏读:一个事务,读取到另一个事务中没有提交的数据
-
虚读:(不可重复读),在同一个事务中,两次读取到的数据不一样。
-
幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
更为具体一些:select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,
· 无法插入,此时就发生了幻读。 (幽灵问题)
-
-
隔离级别:
-
read uncommitted:读未提交(未提交的 可以读到)
-
产生的问题:脏读、虚读、幻读
-
-
read committed:读已提交 (Oracle)
-
产生的问题: 虚读、幻读
-
-
repeatable read:可重复读 (MySQL默认)
-
产生的问题: 幻读
-
-
serializable:串行(xing)化,序列化
-
可以解决所有的问题
当前事务可以将表锁上,其它事务访问不到该表,只有当前事务提交或回滚了,其它事务才可以访问该表。效率极低。
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
-
-
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 可能发生 | 可能发生 | 可能发生 |
读已提交 | × | 可能发生 | 可能发生 |
可重复读 | × | × | 可能发生 |
串行化 | × | × | × |
-
数据库查询隔离级别:
-
select @@tx_isolation; -- mysql5.7之前使用这种方式查询
-
select @@transaction_isolation; -- mysql5.7以后使用这种方式查询
-
数据库设置隔离级别:
-
set global transaction isolation level 级别字符串;
-
set global transaction isolation level repeatable read;
-
事务级别演示:
-
set global transaction isolation level read uncommitted;
转账操作
start transaction; 两个事务窗口都开启事务
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
一般不会修改隔离级别,了解即可,我们使用mysql默认的隔离级别即可。
DCL
概念:
-
SQL分类:
-
DDL:数据定义语言,操作数据库和表,列
-
DML:数据操作语言,增删改表中数据
-
DQL:数据查询语言,查询表中数据
-
DCL:数据控制语言,管理用户,授权
DCL了解即可,因为公司有数据库管理员,他们专门管理数据库的,一般你刚进入公司他们会给你创建一个数据库用户他会给你授权,你到时候用找个用户登入数据库即可。
-
-
DBA:(DataBase Administrator) 数据库管理员
管理用户,授权
DCL:管理用户,授权
一. 管理用户
1. 查询用户:
-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;
通配符: % 表示可以在任意主机远程使用用户登录数据库
2. 添加用户:
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'zs'@'localhost' IDENTIFIED BY '123';
3. 删除用户:
语法:DROP USER '用户名'@'主机名';
DROP USER 'zs'@'localhost'
4. 修改用户密码:
#SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
#UPDATE user SET PASSWORD = PASSWORD('新密码') WHERE USER='用户名'
update user set password = password('1234') where user='zs'
数据库的备份和还原
-
命令行:
-
语法:
-
-- 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
mysqldump -uroot -proot t3 > C://t3.sql -
-
还原:
-
-
图形化工具:
-
转储t3.sql文件
-
执行sql文件,前提是事先创建好数据库 才可以导入成功
-
-
ER图表
概述:
ER图也被称为实体-联系图,提供了表示实体类型、属性和联系的方法。
Entity-relation
三个概念
-
实体 定义:现实世界中任何可以被认知、区分的事物 示例:
1、学校 :学生、教师、课程、班主任 … 2、企业 :职工、产品
-
属性 定义 :实体所具有的特性 示例 1、学生属性:学号、姓名、年龄、性别、专业 … 2、产品属性:产品编号、产品名称、规格 …
-
关系 定义 :实体之间的关系 分类 一对一关系(1:1) 班级和班长 一对多关系(1:n) 公司和职工、班级和学生 多对多关系(m:n) 学生和课程、商店和顾客
-
ER图的绘制 矩形框代表实体
菱形框代表关系
椭圆形代表属性
-
示例 学生选课系统
车队-司机-车辆
sql语句优化技巧
-
对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
-
应尽量避免在 where 子句中使用!=或< >操作符,否则引擎将放弃使用索引而进行全表扫描。
(索引扫描效率会优于全表扫描)
-
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,
如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
-
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
解释说明:UNION 操作符 用于合并两个或多个 SELECT 语句的结果集。默认地,UNION 操作符选取不同的值。
如果允许重复的值,请使用 UNION ALL。
SELECT * FROM s1 UNION SELECT * FROM s2; SELECT * FROM s1 UNION ALL SELECT * FROM s2;
-
in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3,4)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 4;
-
应尽量避免在 where 子句中对字段进行表达式运算操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
-
应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3) = 'abc' -- name以abc开头的id
应改为:
select id from t where name like 'abc%'
-
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
-
任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
-
避免频繁创建和删除临时表,以减少系统表资源的消耗。
临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。
创建临时表的语法与创建表语法类似,不同之处是增加关键字TEMPORARY,如:
CREATE TEMPORARY TABLE 表名 (…. )
-
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
-
尽量避免大事务操作,提高系统并发能力。
-
尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。
如下:
SELECT * FROM t WHERE username LIKE '%li%'
优化方式:尽量在字段后面使用模糊查询。如下:
SELECT * FROM t WHERE username LIKE 'li%'
-
-
1. 登录数据库
2. 创建新数据库 create database m2;
3. 使用新数据库 use m2;
4. 执行文件 source 文件路径 (source c://t3.sql;)
-