MySQL_复习

一、三大数据类型

1、数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

CREATE TABLE IF NOT EXISTS `user`
(
    a TINYINT NOT NULL, 
    b SMALLINT NOT NULL,
    c MEDIUMINT NOT NULL,
    d INT NOT NULL,
    e BIGINT NOT NULL,
    f FLOAT NOT NULL,
    g DOUBLE NOT NULL
);

2、时间和日期类型

数值类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	1901~2155	0000
DATE	3	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838：59：59~ 838：59：59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

CREATE TABLE IF NOT EXISTS `user`
(
    a DATE NOT NULL,
    b TIME NOT NULL,
    c YEAR NOT NULL,
    d INT NOT NULL,
    e DATETIME NOT NULL,
    f TIMESTAMP NOT NULL
);

3、字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串（variable变量）
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串（101010101）
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

CREATE TABLE IF NOT EXISTS `user`
(
    a CHAR NOT NULL,
    b VARCHAR NOT NULL,
    c TINYBLOB NOT NULL,
    d TINYTEXT NOT NULL,
    e BLOB NOT NULL,
    f TEXT NOT NULL
);

二、增删改查

　　2.1、插入：在MySQL通过INSERT语句向数据表中插入数据。

#插入数据 
# 语法1 
-- INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
INSERT INTO student(sname,sgender,sage)VALUES("张三","男","18")；
# 语法2 在该方式中：(字段名1,字段名2,…)是可选的，它用于指定插人的字段名；(值 1,值 2,…),(值 1,值 2,…)表示要插人的记录，该记录可有多条并且每条记录之间用逗号隔开。
-- INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...),(值 1,值 2,...);
INSERT INTO student(sname,sgender,sage)VALUES("张三","男","18"),("李四","女","20");
#添加表字段
ALTER TABLE student ADD tel VARCHAR(20);

　　2.2、删除：在MySQL通过DELETE语句删除数据表中的数据。

#删除库
-- DROP DATABASE 数据库名;
#删除表
-- DROP TABLE 表名;
#删除数据
-- DELETE FROM 表名 [WHERE 条件表达式];
# 如删除学生表中名字为李四的学生
DELETE FROM student WHERE sname="李四";

　　2.3、修改：在MySQL通过UPDATE语句更新数据表中的数据。

# update语法
--UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
# 修改学生表中id等于2的学生姓名为王五（修改单条数据）
UPDATE student SET sname='王五' WHERE id=2;
# 修改学生表中所有学生年龄修改为18（修改全部数据）
UPDATE student SET age=18;

2.4、基本查询：在MySQL通过SELECT语句更新数据表中的数据。

　　2.4.1、查询所有字段

-- 全表查询
SELECT * FROM student;

　　2.4.2、查询指定字段

-- 字段查询
SELECT sname FROM student;

　　2.4.3、指定条件查询（where）

-- 条件查询
SELECT * FROM student WHERE sage=18;

　　2.4.4、查询后进行分组（group by）

-- 查询语句 （group by分组）按照性别进行分组
SELECT * FROM cs_user GROUP BY gender;

　　2.4.5、查询后进行去重（distinct）

-- 查询语句 （distinct去重）
SELECT DISTINCT age FROM cs_user;

　　2.4.6、查询后进行排序（order by）排序默认正序 DESC倒叙

-- order by排序默认正序  DESC倒叙
SELECT * FROM cs_user ORDER BY age DESC;

　　2.4.7、对查询数量进行条数限制（limit限制条数）

-- limit限制条数
SELECT * FROM  cs_user LIMIT 2,2;

　　2.4.8、补充其他常用函数（max,min,count，avg）

-- 函数max,min,count,avg
SELECT COUNT(*) AS 总数,MAX(age) AS 最大年龄,MIN(age) AS 最小年龄,AVG(age) AS 平均年龄 FROM cs_user;

三、关联查询

　　3.1、内链接查询

　　　　内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

-- 语法：SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段
-- 内连接 ....inner join...on 这里的w表示别名 相当于 websites as w
 SELECT * FROM websites w INNER JOIN access_log a ON w.id=a.site_id;

　　3.2、左链接查询

　　　　左连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

-- 左连接 left join...on
SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id;

　　3.3、右链接查询

　　　　右连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

-- 右连接 right join...on
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;

　　3.4、联合查询union

　　　　UNION 操作符用于合并两个或多个 SELECT 语句的结果集。（自动去重），如果不想去重的话就在union后面加上all

　　　　请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

-- union
SELECT id,NAME FROM websites
UNION ALL
SELECT aid,DATE FROM access_log;

　　3.5、全链接查询=左连接 union 右连接

-- 全连接=左连接 union 右连接
SELECT * FROM w12ebsites w LEFT JOIN access_log a ON w.id=a.site_id
UNION
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;

四、子查询　

　　子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。

　　在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

　　4.1、带比较运算符的子查询

--查询工资大于王强工资的员工信息。思路：第一步，先查询王强的工资，第二步，通过 where子句，进行筛选
SELECT *FROM emp wHERE salary >= (
　　SELECT salary FROM emp wHERE emp_name = ‘王强’
);
--查询部门与张建国的部门相同，工资大于孙岩工资的员工信息。
SELECT*FROM emp
wHERE dept_id = (
    SELECT dept_id FROM emp WHERE emp_name =‘张建国"
AND salary >(
    SELECT salary FROM emp wHERE emp_name =‘孙岩’
);

　　4.2、IN关键字，用于判断某个记录的值，是否在指定的集合中如果字段的值在集合中，则满足条件，该字段所在的记录将会被查询出来。

--查询孙姓员工所在部门的全部员工信息。
SELECT *FROM emp
WHERE dept_id IN(
    SELECT dept_id FROM emp wHERE emp_name LIKE‘孙%
);

五、直接创建视图

#创建视图:逻辑上的虚拟表
CREATE VIEW v_author(编号,姓名)
AS
SELECT * FROM author WITH CHECK OPTION;

六、索引

1.1、什么是索引？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

1.2、为什么要有索引呢？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。

索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

2.1、创建索引：

# 1.1、创建索引：单独创建索引
CREATE INDEX index_name ON emp(emp_name);

2.2、修改时添加索引：

# 1.2、创建索引：修改表结构时创建索引
ALTER TABLE emp ADD INDEX index_salary

2.3、创建表时指定索引：

# 1.3、创建索引：创建表时的时候创建索引
CREATE TABLE IF NOT EXISTS article
(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(30),
    content VARCHAR(50),
    dt DATE,
    PRIMARY KEY(id),
    INDEX index_title (title)
);

2.4、删除索引

# 删除索引
DROP INDEX index_name ON emp;

3.1、创建唯一索引：

# 3.1、单独创建唯一索引：列的值必须唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name);

3.2、修改时添加唯一索引：

# 3.2、修改表时创建唯一索引
ALTER TABLE emp ADD UNIQUE INDEX index_name (emp_name);

3.3、创建表时指定唯一索引：

# 3.3、创建表时创建唯一索引
CREATE TABLE IF NOT EXISTS emp2
(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(30),
    content VARCHAR(50),
    dt DATE,
    PRIMARY KEY(id),
    UNIQUE INDEX index_title (title)
);

3.4、删除唯一索引

# 删除索引
DROP INDEX index_name ON emp;

4.1、创建组合索引：

# 4.1、组合索引
CREATE INDEX index_ct ON article(content,dt);

4.2、修改时添加组合索引：

# 4.2、修改表时创建唯一索引
ALTER TABLE emp2 ADD INDEX index_tc (title,content);

4.3、创建表时指定组合索引：

# 4.3、创建表时创建唯一索引
CREATE TABLE IF NOT EXISTS emp3
(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(30),
    content VARCHAR(50),
    dt DATE,
    PRIMARY KEY(id),
    INDEX index_tc (title,content)
);

4.4、删除唯组合索引

# 删除索引
DROP INDEX index_name ON emp;

七、事务

1.1、什么是事务

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行，它是一个不可分割的工作执行单元。
事务的使用场景:
在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假
如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，
要想解决这个问题就需要通过事务来完成。

1.2、四大特点

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.3、事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。

InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

1.4、MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认

CREATE TABLE runoob_transaction_test(id INT(5));

# 开启事务
BEGIN;
# 插入数据
INSERT INTO runoob_transaction_test VALUES(1);
INSERT INTO runoob_transaction_test VALUES(2);
# 提交事务
COMMIT;

# 开启事务
BEGIN;
# 插入数据
INSERT INTO runoob_transaction_test VALUES("aaa");
INSERT INTO runoob_transaction_test VALUES(4);
# 事务回滚
ROLLBACK;

SELECT * FROM runoob_transaction_test;

2、直接用 SET 来改变 MySQL 的自动提交模式

-- 禁止自动提交
SET AUTOCOMMIT=0
--开启自动提交
SET AUTOCOMMIT=1

八、触发器

1.1、什么是触发器

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，

它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

1.2、创建触发器

USE info;
# 学生表
CREATE TABLE IF NOT EXISTS student(
    username VARCHAR(50),
    PASSWORD VARCHAR(50),
    stuid INT PRIMARY KEY AUTO_INCREMENT,
    birthday DATE
);
INSERT INTO student(username,PASSWORD,birthday)VALUES
('王二','111111','2016-08-23'),
('李四','123456','2016-07-23'),
('杨三','123456','2016-08-17'),
('刘五','000000','2016-08-18'),
('黄六','666666','2016-08-23');
SELECT * FROM student;
# 成绩表
CREATE TABLE IF NOT EXISTS cj(
    num INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT,
    stu_name VARCHAR(50),
    math FLOAT,
    chinese FLOAT,
    english FLOAT
);
SELECT * FROM cj;
# 触发器
DELIMITER $
CREATE TRIGGER ins_stu
AFTER INSERT ON student FOR EACH ROW
BEGIN
    INSERT INTO cj(stu_id,stu_name,math,chinese,english)
    VALUES(new.stuid,new.username,88,88,88);
END $
DELIMITER ;
# 执行触发过程
INSERT INTO student(username,PASSWORD,birthday)VALUES
('张三','222222','2016-08-23');

九、存储过程

1.1、什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1.2、存储过程的优点

存储过程可封装，并隐藏复杂的商业逻辑。存储过程可以回传值，并可以接受参数。存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。存储过程可以用在数据检验，强制实行商业逻辑等。

1.3、创建存储过程

# 创建存储过程
DELIMITER $
CREATE PROCEDURE testa()
BEGIN
    SELECT * FROM student;
    SELECT * FROM cj;
END $
DELIMITER ;

调用

# 调用
CALL testa();

1.4、存储过程的变量以及作用范围

# 创建存储过程
DELIMITER $
CREATE PROCEDURE test2()
BEGIN
    # 声明变量类型
    DECLARE un VARCHAR(32) DEFAULT '';
    # 给username赋值
    SET un='xiaoxiao';
    # 将查询结果赋值给un变量
    SELECT username INTO un FROM student WHERE stuid=3;
    # 查询un变量，返回
    SELECT un;
END $
DELIMITER ;
# 调用存储过程
CALL test2();

# 变量的作用范围
DELIMITER $
CREATE PROCEDURE test3()
BEGIN
    BEGIN
        # 声明变量类型
        DECLARE un VARCHAR(32) DEFAULT '';
        # 给un赋值
        SET un='xiaoxiao';
        # 将查询结果赋值给un变量
        SELECT username INTO un FROM student WHERE stuid=3;
        # 查询un变量，返回
        SELECT un;
    END;
    BEGIN
        # 声明变量类型
        DECLARE un VARCHAR(32) DEFAULT '';
        # 给un赋值
        SET un='xiaoxiao';
        # 将查询结果赋值给un变量
        SELECT username INTO un FROM student WHERE stuid=4;
        # 查询un变量，返回
        SELECT un;
    END;
    
END $
DELIMITER ;
# 调用存储过程
CALL test3();

十、查询优化

1、 count优化

#创建表
CREATE TABLE IF NOT EXISTS cnt
(
    id INT,
    NAME VARCHAR(10),
    age INT,
    tel VARCHAR(10)
);
#创建存储过程
DELIMITER $
CREATE PROCEDURE cnt()
BEGIN
#定义一个循坏变量
DECLARE i INT DEFAULT 0;
WHILE(i<1000) DO
    BEGIN
        SELECT i;
        SET i=i+1;
        INSERT INTO cnt(id,NAME)VALUES(i,"zhang");
    END;
END WHILE;
END $
DELIMITER ;

#调用存储过程
CALL cnt();

#查询语句a
SELECT COUNT(*) FROM cnt WHERE id > 5;
#查询语句b
SELECT (SELECT COUNT(*) FROM cnt) - COUNT(*) FROM cnt WHERE id <= 5;

语句当行数超过11行的时候需要扫描的行数比b语句要多， b语句扫描了6行，此种情况下，b语句比a语句更有效率。当没有where语句的时候直接select count(*) from world.city这样会更快，因为mysql总是知道表的行数。

2、避免使用不兼容的数据类型。

例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。在程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如 SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销；按照特定顺序提取数据的查找

#数据不兼容会拉低效率
INSERT INTO cnt(id)VALUES(12.3);

3、索引字段上进行运算会使索引失效

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描

#索引列进行运算会让索引失效
CREATE INDEX index_age ON cnt(age);
SELECT * FROM cnt WHERE age > 18;
SELECT * FROM cnt WHERE age * 2 > 36;

CREATE INDEX index_age ON cnt(id);
SELECT * FROM cnt WHERE id > 10000;
SELECT * FROM cnt WHERE id * 2 > 20000;

4、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符.

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%”

优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

#创建表
CREATE TABLE IF NOT EXISTS emp
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    age INT,
    tel VARCHAR(10)
);
#创建存储过程
DELIMITER $
CREATE PROCEDURE emp()
BEGIN
#定义一个循坏变量
DECLARE i INT DEFAULT 0;
WHILE(i<1000) DO
    BEGIN
        SELECT i;
        SET i=i+1;
        INSERT INTO emp(NAME,age)VALUES("zhang",i);
    END;
END WHILE;
END $
DELIMITER ;
# 调用存储过程
CALL emp();
SELECT * FROM emp;
# 避免的情况 !=,  <>,  is null,  is not null,  in,  not in
SELECT COUNT(*) FROM emp WHERE age IN (SELECT age FROM emp WHERE id > 100);
SELECT COUNT(*) FROM emp WHERE EXISTS (SELECT age FROM emp WHERE id > 100);

5、尽量使用数字型字段.

一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

# 尽量使用数字型字段
CREATE TABLE IF NOT EXISTS t1 (c1 INT,c2 INT);
CREATE TABLE IF NOT EXISTS t2 (c1 INT,c2 INT);

6、合理使用EXISTS,NOT EXISTS子句。

# 使用where > 0;
SELECT SUM(t1.c1) FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2 > 0);
# 使用exists
SELECT SUM(t1.c1) FROM t1 WHERE EXISTS (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2);

7、能够用BETWEEN的就不要用IN ，能够用DISTINCT的就不用GROUP BY

# between 是连续的范围，使用索引
SELECT * FROM emp WHERE age BETWEEN 100 AND 200;
# in不连续的范围，不能使用索引
SELECT * FROM emp WHERE age IN (100,150,200);

# distinct去重：重复的数据取一个
SELECT DISTINCT NAME FROM emp;
# group by分组
SELECT NAME FROM emp GROUP BY NAME;

8、尽量少用in,not in 改用between

SELECT * FROM t WHERE num IN (1,2,3);
SELECT * FROM t WHERE num BETWEEN 1 AND 3;

9、模糊查询，字母打头

SELECT * FROM t WHERE NAME LIKE '李%';

10、避免索引字段进行函数操作，要使用like

SELECT * FROM t WHERE SUBSTRING(NAME,1,3)='abc';
# 要使用like
SELECT * FROM t WHERE NAME LIKE 'abc%';

posted @ 2022-05-08 22:49 文明老人阅读(51) 评论(0) 收藏举报

刷新页面返回顶部

文明老人

MySQL_复习

一、三大数据类型

1、数值类型

2、时间和日期类型

3、字符串类型

二、增删改查

2.1、插入：在MySQL通过INSERT语句向数据表中插入数据。

2.2、删除：在MySQL通过DELETE语句删除数据表中的数据。

2.3、修改：在MySQL通过UPDATE语句更新数据表中的数据。

2.4、基本查询：在MySQL通过SELECT语句更新数据表中的数据。

2.4.1、查询所有字段

2.4.2、查询指定字段

2.4.3、指定条件查询（where）

2.4.4、查询后进行分组（group by）

2.4.5、查询后进行去重（distinct）

2.4.6、查询后进行排序（order by）排序默认正序 DESC倒叙

2.4.7、对查询数量进行条数限制（limit限制条数）

2.4.8、补充其他常用函数（max,min,count，avg）

三、关联查询

3.1、内链接查询

3.2、左链接查询

3.3、右链接查询

3.4、联合查询union

3.5、全链接查询=左连接 union 右连接

四、子查询

4.1、带比较运算符的子查询

4.2、IN关键字，用于判断某个记录的值，是否在指定的集合中如果字段的值在集合中，则满足条件，该字段所在的记录将会被查询出来。

五、直接创建视图

六、索引

1.1、什么是索引？

1.2、为什么要有索引呢？

2.1、创建索引：

2.2、修改时添加索引：

2.3、创建表时指定索引：

2.4、删除索引

3.1、创建唯一索引：

3.2、修改时添加唯一索引：

3.3、创建表时指定唯一索引：

3.4、删除唯一索引

4.1、创建组合索引：

4.2、修改时添加组合索引：

4.3、创建表时指定组合索引：

4.4、删除唯组合索引

七、事务

1.1、什么是事务

1.2、四大特点

1.3、事务控制语句

1.4、MYSQL 事务处理主要有两种方法：

八、触发器

1.1、什么是触发器

1.2、创建触发器

九、存储过程

1.1、什么是存储过程

1.2、存储过程的优点

1.3、创建存储过程

1.4、存储过程的变量以及作用范围

十、查询优化

1、 count优化

2、 避免使用不兼容的数据类型。

3、索引字段上进行运算会使索引失效

4、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符.

5、尽量使用数字型字段.

6、合理使用EXISTS,NOT EXISTS子句。

7、能够用BETWEEN的就不要用IN ，能够用DISTINCT的就不用GROUP BY

8、尽量少用in,not in 改用between

9、模糊查询，字母打头

10、避免索引字段进行函数操作，要使用like

公告

　　2.1、插入：在MySQL通过INSERT语句向数据表中插入数据。

　　2.2、删除：在MySQL通过DELETE语句删除数据表中的数据。

　　2.3、修改：在MySQL通过UPDATE语句更新数据表中的数据。

　　2.4.1、查询所有字段

　　2.4.2、查询指定字段

　　2.4.3、指定条件查询（where）

　　2.4.4、查询后进行分组（group by）

　　2.4.5、查询后进行去重（distinct）

　　2.4.6、查询后进行排序（order by）排序默认正序 DESC倒叙

　　2.4.7、对查询数量进行条数限制（limit限制条数）

　　2.4.8、补充其他常用函数（max,min,count，avg）

　　3.1、内链接查询

　　3.2、左链接查询

　　3.3、右链接查询

　　3.4、联合查询union

　　3.5、全链接查询=左连接 union 右连接

四、子查询　

　　4.1、带比较运算符的子查询

　　4.2、IN关键字，用于判断某个记录的值，是否在指定的集合中如果字段的值在集合中，则满足条件，该字段所在的记录将会被查询出来。

2、避免使用不兼容的数据类型。