mysql语句相关学习

sql

数据库

发布日期: 2020-05-20

更新日期: 2020-08-18

文章字数: 7,796

阅读时长: 31 分

阅读次数:

mysql语句训练：mysql官网

1. mysql数据库创建表语句

--商品表
CREATE TABLE product_info (
    product_id VARCHAR(32) NOT NULL, 
    product_name VARCHAR(64) NOT NULL,
    product_price DECIMAL(8,2) NOT NULL , 
    product_stock INT NOT NULL,
    product_description VARCHAR(64),
    product_icon VARCHAR(512),
    category_type INT NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    update_tine VARCHAR(64) ,
    PRIMARY KEY (product_id)
 );
 --类目表
 CREATE TABLE product_category (
    category_id INT NOT NULL AUTO_INCREMENT,
    category_name VARCHAR(64) NOT NULL,
    category_type INT NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    update_tine VARCHAR(64) ,
    PRIMARY KEY(category_id),
     --类型唯一性
    UNIQUE KEY uqe_category_type (category_type)    
 );
 --订单表
 CREATE TABLE order_master (
    order_id VARCHAR(32) NOT NULL,
    buyer_name VARCHAR(32) NOT NULL,
    buyer_phone VARCHAR(32) NOT NULL,
    buyer_address VARCHAR(128) NOT NULL,
    buyer_openid VARCHAR(64) NOT NULL,
    order_amount DECIMAL(8,2) NOT NULL,
    order_status TINYINT(3) NOT NULL DEFAULT 0,
    pay_status TINYINT(3) NOT NULL DEFAULT 0,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    update_tine VARCHAR(64) ,
     --订单主键
    PRIMARY KEY(order_id),
     --订单索引
    KEY idx_buyer_openid (buyer_openid)
 );
  --订单明细表
CREATE TABLE order_detial(
    detial_id VARCHAR(32) NOT NULL,
    order_id VARCHAR(32) NOT NULL,
    product_id VARCHAR(32) NOT NULL,
    product_name VARCHAR(64) NOT NULL,
    product_price DECIMAL(8,2) NOT NULL,
    product_quantity   INT NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    update_tine VARCHAR(64) ,
    PRIMARY KEY(detial_id),
    KEY idx_order_id (order_id)
 );

需求：创建一个薪资表，表中有员工的信息，查询某个员工薪资变化t以及员工编号emp_no

--创建薪资表
CREATE TABLE `salaries` (
`emp_no` INT(11) NOT NULL,
`salary` INT(11) NOT NULL,
`from_date` DATE NOT NULL,
`to_date` DATE NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`))
--1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
--2、输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
--3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件
select a.* from
(select emp_no,count(salary) as t from 
salaries GROUP BY emp_no)
 as a 
where t>15

--对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by
select salary from salaries  where to_date='9999-01-01'
group by salary  order by salary DESC

having和where的使用场景

WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。

HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

自己学习mybatis时创建相关的表，以后可以复习复习mysql语句

mybatis学习

USE test
DROP TABLE `user`;

CREATE TABLE `user`(
    `id` INT(20) NOT NULL PRIMARY KEY,
    `name` VARCHAR(30) DEFAULT NULL,
    `pws` VARCHAR(30) DEFAULT NULL

)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `user`(`id`,`name`,`pws`) VALUES(1,'xing','123456')

##生成一张product表，单表
CREATE TABLE `product`(
    `pid` INT(20) NOT NULL PRIMARY KEY,
    `pname` VARCHAR(30) DEFAULT NULL,
    `pprice` VARCHAR(30) DEFAULT NULL,
    `pcategary` VARCHAR(30) DEFAULT NULL

)ENGINE=INNODB DEFAULT CHARSET=utf8;

##生成一张教师表
CREATE TABLE `teacher`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `teacher`(`id`,`name`) VALUES(1,'xing');

#生成一张学生表，关联老师
CREATE TABLE `student`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY(`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student`(`id`,`name`,`tid`) VALUES (1,'小一',1);
INSERT INTO `student`(`id`,`name`,`tid`) VALUES (2,'小二',1);

INSERT INTO `product`(`pid`,`pname`,`pprice`,`pcategary`) VALUES(1,'玩具','25','1001');
INSERT INTO `product`(`pid`,`pname`,`pprice`,`pcategary`) VALUES(2,'娃娃','225','1001');
SELECT * FROM test.product
##hsujuku 

SELECT COUNT(NAME) FROM test.user 
##创建一张博客表
CREATE TABLE `blog`(
    `id` VARCHAR(50) NOT NULL ,
    `title` VARCHAR(100)NOT NULL,
    `author` VARCHAR(30) NOT NULL,
    `create_time` DATETIME NOT NULL ,
    `views` INT(30) NOT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO blog(id,title,author,create_time,views) VALUES('1','nihao','itxing','2020-05-20',10);
INSERT INTO blog(id,title,author,create_time,views) VALUES('2','ll','itxing','2020-05-21',11);
SELECT  * FROM blog

mysql中的一些函数的使用

测试常用函数使用的表：

CREATE TABLE `成绩`(
    `编号` INT(20) NOT NULL PRIMARY KEY,
    `语文` INT(20) DEFAULT 0,
    `数学` INT(20) DEFAULT 0,
    `英语` INT(20) DEFAULT 0,
    `化学` INT(20) DEFAULT 0
)ENGINE = INNODB DEFAULT CHARSET =utf8;

2. 常用聚合函数

distinct函数： 去重复，将查询的某一列中重复的值去掉

SELECT DISTINCT(英语) FROM 成绩

max函数：获取某一列中的最大值

SELECT MAX(DISTINCT(英语)) AS 英语最大值 FROM 成绩

limit函数： 与offset相结合的使用

—显示第n条数据

SELECT 英语 FROM 成绩 LIMIT n

—-显示从第n条记录开始然后跳过第m条记录查询后一条记录

SELECT 英语 FROM 成绩 LIMIT n OFFSET m

—–显示从第n条记录开始显示m条

SELECT 英语 FROM 成绩 LIMIT n ,m

注意：mysql的数据是从0行开始算，limit1，1表示第二行的数据，在行数不够时，返回的是最多的数据，limit3,4表示从行3开始取四条记录，limit 4 offset 3也是从行3取4条记录

ifnull函数：ifnull(a,b),如果表达式a为null结果为表b，否则结果为a

SELECT IFNULL((SELECT 英语 FROM 成绩 WHERE 英语 > 90),NULL) AS 学霸
SELECT IFNULL((SELECT 英语 FROM 成绩 WHERE 英语 < 59),NULL) AS 学渣

需求查询表中英语第二大的元素：

SELECT IFNULL((SELECT  DISTINCT 英语 FROM 成绩 ORDER BY 英语 DESC LIMIT 1,1),NULL) AS 第二大

Order By字句 字句(clause)，sql语句通常由字句构成，一个字句通常由一个关键字和提供的数组组成，order字句能够进行对列进行字母顺序排序，能够使用desc关键字对其进行降序排序操作。

多个列排序，使用,号隔开

select column1,column2 from table1 order by column1,column2

应用场景：使用order by和limit语句可以找出最高或最低的数据

从数据库中找出最贵的商品价格

select price from products order by price desc limit 1;

where字句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	两者之间

##等值查询
select * from pprice where pprice = 225;
##不等查询
 select * from product where pprice >= 225;
 ##一下两条语句语义相同，都是找出值不为225的数据
 select * from product where pprice <> 225;
 select * from product where pprice != 225;
 ##区间查询
 select * from product where pprice between 245 and 260;
 ##空值查询
 select * from product where pprice is null;

操作符： （operator）用来连结或改变where字句总的句子的关键字，也成为逻辑操作。

操作符包括：AND操作符(优先级比or高)、or操作符、In操作符、NOT操作符

##表示价格不是225,248的产品
select * from product where pprice not in(225,248) order by pprice;

通配符： wildcard用来匹配值的一部分特殊字符，使用时需要有like操作符，like其实是一种谓词。

搜索模式： search pattern由字面值、通配符或者两者组合成的搜索条件

1.百分号通配符（%）：在搜索串中，%表示任何字符出现任意次数

##like与%通配符，匹配实体列中以o开头的字符
select pname from product where pname like 'o%';
##匹配中间是@xing的所有员工，不论其前后出现何种字符
 select * from employee where Email like '%@xing%';
 ##匹配中间的字符
 select * from employee where email like 'i%m';

注意：使用%匹配字符串时，存储的数据中若是有空格字符，则可能导致匹配失败，例如匹配like ‘%ss’若数据库中的字符为“miss ”后面有空格存在，则匹配失败。

null值的匹配，‘%’不能匹配到数据位NULL的数据

2.下划线（_）通配符，与%用法类似，但下划线只能匹配单个字符而不是多个字符。

注：不能过度使用通配符；在确实需要使用通配符时，除非绝对有必要，否则不要将他们用来搜索模式的开始处；需要检查通配符的位置，不能放置错误

正则表达式：mysql的正则表达式作用是匹配文本，将一个模式与一个文本串进行比较

1.基本的字符匹配，使用“.”匹配任意一个字符

##使用REGEXP关键字和正则表达式匹配字符，匹配所有25开头的数据，258、256、253等等
select * from product where pprice REGEXP '25.';

LIKE匹配整个列。如果被匹配的文本在列值中出现， LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现， REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

2.使用“|”进行匹配，表示or

##使用|表达式匹配列中是225或245的数据
select * from product where pprice REGEXP '225|245';

3.使用“[]”表示一组字符

##使用[]进行匹配，下面表示匹配1001或2001或3001的数据都返回，表示找每个部门中的第一个
select * from product where pcategary REGEXP '[123]001';

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、 2或3，但[^123]却匹配除这些字符外的任何东西。

4.匹配范围使用短横线进行匹配-，例如[0-9]、[a-z]、[A-Z]

##范围匹配正则[1-3]
select * from product WHERE pcategary REGEXP '[1-3]001';

5.匹配特殊字符，转义字符\\

例如需要查找数据中有.的字符：select * from 表 where 列 Regexp ‘\\.’

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配({1,})
？	0个或1个匹配({0,1})
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围(m不超过255)

举个栗子：

匹配连在一起的4位数字 ,[[:digit:]]{4}匹配连在一起的任意4位数字 ,四个数字相连，中间不能有其他的字符

select * from product WHERE pcategary REGEXP ‘[[:digit:]]{4}’;

定位符：

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

##匹配以1-4开头的数据信息
select * from product where pcategary regexp '^[1-4]';
##匹配以2-4结尾的任意数据
select * from product where pcategary regexp '[2-4]$';

拼接字段：

1.Concat()函数拼接两个列

select Concat(pid,’(‘,pcategary,’)’) from product;

结果：

1(1001)
2(1001)
3(1002)
4(1003)

2.Rtrim()函数去掉值右边的所有空格，对数据进行整理

select Concat(RTrim(pid),’(‘,RTrim(pcategary),’)’) from product;

3.别名

select 列 as 别名 from 表 as 别名

4.执行计算

###查询pid ，pprice 以及计算两者的和pid+pprice称为一个新的列newcolumn
select pid,pprice,pid+pprice as newcolume  from product;

数据处理函数：

大多数函数支持以下类型的函数：

1.用于处理文本串的文本函数

2.用于在数值数据上进行算术操作的数值函数

3.用于处理日期和时间值并从这些值汇提取特定成分的日期函数和时间函数

4.返回DBMS正使用的特殊信息的系统函数

1.文本处理函数

函数	说明
left()	返回左边的字符
length()	返回串的长度
locate()	找出串的一个子串
lower()	转换成小写
Ltrim()	去掉串的左边空格
right()	返回串右边的字符
Rtrim()	去掉串右边的空格
soundex()	返回串的soundex的值
substring()	返回子串字符
upper()	转换成大写

soundex是一个将任何文本转换成描述其语音表示的字母数字模式的算法

使用upper例子

转换成大写字符

日期函数：

日期函数

数值处理函数：

数值处理函数

聚集函数(经常会使用)：

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列之和

##查询产品表中价格的均值
select avg(pprice) from product;
##计算有多少条数据
select count(pprice) from product;
##计算出价格最大值
select max(pprice) from product;
##计算出价格最小值
select min(pprice) from product;
##去掉重复的数据再进行平均
select avg(destinct pprice) as avgprice from product

数据分组：

创建分组Group by字句,需要知道的重要的规定

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。

GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

过滤分组：过滤分组规定了包括哪些分组，排除哪些分组。所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

select * from product group by pcategary having pcategary>1002;

分组和排序：Group by 和Order by

Order by	Group by
排序产生的输出	分组行，但输出可能不是分组的顺序
任意列都可以使用，甚至非选择列也可以使用	只可能使用选择列或表达式列，且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列，必须使用

3. 各种查询

子查询 另一条查询结果作为新一条查询的条件

##查询员工表中是管理人员的员工
select emp_no from dept_emp where emp_no in(select emp_no from dept_manager);
##查询员工表中不是管理人员的员工
select emp_no from dept_emp where emp_no not in(select emp_no from dept_manager);

联结表：

主键：一张表中数据唯一的表示；外键是某一个表中的一列，它包含另一个表中的主键值，定义了表间关系。

两个或两个以上的表中的字段有相同时，使用完全限定名。

1.内部联结(inner join…on)：又称为等值联结，基于两个表之间相等的测试。

2.自联结：在同一张表中查询出不同的数据，下面使用一个例子说明自联结。

自联结

每一个员工都有自己的编号，每一个员工记录着自己的领导信息，为null者说明自己就是领导，现在查询salary大于自己领导的员工姓名。

##查询员工工资大于自己领导的姓名，也可以使用子查询进行操纵，自连接处理更快
select e1.name from employee2 e1,employee2 e2 where e2.id = e1.mangerid and e1.salary>e2.salary;

3.自然联结

##自然连接查询出员工的名字，部门名和员工邮件
select e.LAST_NAME,e.EMAIL,d.DEPT_NAME from employee e,department d where e.ID = d.ID;

自然联结

4.外部联结 left/right join…on：与内连接类似，用于将主外键关系的数据联结

总结： 使用联结和联结条件

需要注意联结类类型，一般使用内连接，使用外连接也是有效地

保证使用正确的联结条件，都则返回不正确的数据

应该总是提供联结条件，都则得到的是笛卡尔积

一个联结包含多个表时，最好先将每个联结测试一下

组合查询 通常也称作并或符合查询。通常使用组合查询的情况有：

1.在单个查询中从不同的表返回类似结构的数据

2.对单个表执行多个查询，按单个查询返回数据

使用union将同一张表的不同条件的数据查询出来

##查询数据库表中价格小于200的以及属于1002或1004类别的商品数据
##union自动删除了重复的行，对于既满足第一个条件的数据又满足第二个条件的数据只会查询出一条记录
##如果需要查询出所有满足第一个条件有需要满足第二个条件的数据且不去重复数据可以使用union ALL子句
SELECT * FROM product WHERE pprice <200
UNION 
SELECT * FROM product WHERE pcategary IN ('1002','1004') ;

使用union的规则：

1.Union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔

2.Union中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)

3.列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型(不同的数值类型)

全文本索引 Mysql中最常用的引擎MyIsam和InnoDB，前者支持全文本搜索，后者不支持。

创建数据库表启动全文本索引，使用FULLTEXT子句：

##使用FULLTEXT子句，使用MYISAM引擎
##再导入数据时，不应该使用FULLTEXT，更新索引要花费时间，先导入数据再进行数据修改
CREATE TABLE `productnotes`(
    note_id INT NOT NULL AUTO_INCREMENT,
    prod_id CHAR(10) NOT NULL,
    note_date DATETIME NOT NULL,
    note_text TEXT NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE = MYISAM;

进行全文索引时使用Match()和Against()执行全文本搜索，其中Match()会指定被搜索的列，Against()指定要使用的搜索表达式。

布尔文本搜索：全文本搜索的另一种形式(后续理解清楚再做详细记录)

布尔方式需有以下细节：

要匹配的词；要排斥的词；排列提示；表达式分组；另外一些内容

4. 表中记录的相关操作

插入数据 使用insert values关键字

##插入数据操作，使用;号可以一次提交两条语句
INSERT INTO blog(id,title,author,create_time,views) VALUES('1','nihao','itxing','2020-05-20',10);

INSERT INTO blog(id,title,author,create_time,views) VALUES('2','ll','itxing','2020-05-21',11);

更新数据与删除数据 update set where 语句和delete from where 句

##更新数据库表中的某一条数据，使用update关键字更新删除时需要有where，否则整个表中的数据都将被修改
UPDATE test.user SET NAME = 'hanhan',pws = 'like' WHERE id = 6;
##删除一条数据操作，使用delete from操作
DELETE FROM test.user WHERE id = 8;

5.相关表的操作

创建表和操作表 创建时，需要有表名、列名、列定义。例如：

##创建一张表，使用的是MyIsam引擎
CREATE TABLE `productnotes`(
    note_id INT NOT NULL AUTO_INCREMENT,
    prod_id CHAR(10) NOT NULL,
    note_date DATETIME NOT NULL,
    note_text TEXT NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE = MYISAM;

建表时需要指定主键，主键必须唯一，且主键不为null值 使用AUTO_INCREMENT表示自增，使用default表示默认值。

使用AUTO_INCREMENT缺点是：新增时不指定，查询时不能将主键与其他列对应起来

引擎类型：Engine= Myisam、InnoDB、MEMORY

InnoDB：是一个可靠的事务处理引擎，它不支持全文搜索

Myisam性能极高的引擎，支持全文本搜索，不支持事务和外键

MEMORY在功能等同于Myisam，由于数据存储在内存中速度快

更新表

表中的结构做改动时需要使用更新操作，ALTER TABLE

##给表中新增一列
ALTER TABLE test.user ADD nick_name VARCHAR(20);
##alert table的一种常见用途是定义外键
ALTER table orderitems add constraint fk_ordertimes_orders foreign key (order_num) references orders (order_num)

删除表

##删除成绩表
DROP TABLE 成绩;

重命名表

##将数据库中表名为employee2改为employee02
RENAME TABLE employee2 TO employee02;

6.视图

使用视图

视图的应用：重用sql、简化复杂的sql操作、使用表的组成部分而不是整个表、保护数据、更改数据格式表示

视图的规则和限制：

视图命名唯一、视图数目创建没限制、创建视图需有一定的访问权限

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图

视图检索数据select总有order by ，视图中的order by 被覆盖

视图不能索引，也不能有关联的触发器或者默认值

视图可以和表一起使用

创建视图：

使用Create VIEW语句创建，使用Show Create VIEW viewname；

来查看创建视图的语句；

用drop删除视图；

更新视图时，可以先用drop再用create

##创建视图，是两张表的联合查询，查询出人员的姓名、人员的邮件、人员所在的部门名称
CREATE VIEW dept_employ AS
SELECT LAST_NAME,EMAIL,dept_name FROM employee ,department
WHERE employee.DEPT_ID=department.ID
##从视图总获取元素信息
SELECT * FROM dept_employ

##创建一个过滤空数据的视图
CREATE VIEW employIsNotNull AS
SELECT * FROM employee WHERE EMAIL IS NOT NULL;
SELECT * FROM employIsNotNull;

##更新视图时，如果存在分组、联结、子查询、并、聚集函数、DISTINCT、计算列等，不可以对视图进行更新

*应该将视图用于检索（ SELECT语句），而不用于更新（ INSERT、 UPDATE和DELETE）。 *

7.存储过程

存储过程

通过把处理封装在容易使用的单元中，简化复杂的操作；

由于不要求反复建立一系列处理步骤，保证了数据的完整性；

简化对变动的管理；

提高性能；

存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

执行存储过程的操作

创建存储过程：

create procedure product()

begin

select * from products;

end

调用存储过程： call 存储过程名(参数集);

删除存储过程：drop procedure 存储过程名 ;

检查存储过程：Show Create Procedure 存储过程名

##创建并调用简单的存储过程
DELIMITER $$
CREATE PROCEDURE getemploy()
BEGIN 
    SELECT * FROM employee;
END$$
DELIMITER;
##存储过程实际上是一种函数，所以存储过程名后需要有()符号
CALL getemploy();

##删除存储过程
DROP PROCEDURE  getemploy;

DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END//而不是END;

使用游标：mysql游标只能用于存储过程

小结：本篇文章用于记录一些基本的sql语句或者基本的数据库概念，对于一些mysql深入的知识点在另外一篇文章中。mysql知识点

8.操作数据库引擎

数据库引擎的介绍

1.ISAM(Index Sequential Access Method)

ISAM主要针对于查询次数远远大于更新次数的场景，不支持事务，不占用内存和存储资源。目前不适用，一般使用Myisam

2.MyISAM

mysql5.5之前默认的存储引擎，支持表级锁的机制，用于优化并发的读写操作。Optimize Table命令，来恢复更新机制所浪费的空间，但是会使用锁对表进行锁定。

注意：MyISAM引擎使用，必须经常使用Optimize Table命令清理空间；必须经常备份所有的试试数据，工具：用来修复数据库文件的MyISAMCHK工具和用来恢复空间的MyISAMPACK工具。

该引擎会有三个文件产生：

.frm表结构、.MYD数据文件、.MYI表的索引信息

3.Innodb

由于MyIsam不支持事务，innodb提供了具有提交、回滚和崩溃恢复能力的事务安全的存储引擎。innodb锁定在行级锁、外键支持。

特点：

支持事务、数据多版本读取、锁定机制的改进(行级)、实现外键

数据库表更换数据库引擎时，innodb中的表中涉及到外键时不能转换成MyISAM引擎。

MyISAM与Innodb的区别

1.Innodb支持事务，MyISAN不支持，对于Innodb将sql语句默认封装成事务，自动提交，最好是将多条的sql语句放在begin和commit之间，组成一个事务。

2.Innodb支持外键，MyISAM不支持。对一个包含外键的InnoDB转换成MyISAM会失败

3.InnoDB是聚集索引，数据库文件和索引文件在一起，不需要有主键，通过主键索引效率很高。但是辅助索引会有两次查询(回表)，先查询到主键，再找数据，因此主键不应该过大，主键过大，其他索引会变大。MyISAM是非聚集索引，数据文件与索引文件分离，索引保存的是数据文件的指针。

4.Innodb不保存具体的行数，执行select count(*) from table时是一个全表扫描的过程。MyISAM用一个变量保存了整个表的行数，速度快。

5.Innodb不支持全文索引，而MyIsam支持全文索引，查询效率MyISAM高

数据库引擎的选择

1.要有事务，使用Innode

2.一个表大多数只是读取数据，使用MyIsam，读写频繁的数据表使用Innodb

3.系统奔溃后，MyISaM数据恢复更困难

4.最好使用Innodb(mysql5.5默认的)

查看数据库支持的引擎

SHOW ENGINES;

查看数据库当前的默认引擎

SHOW VARIABLES LIKE ‘%storage_engine%’;

默认的数据库引擎

查看数据库表使用的引擎

SHOW CREATE TABLE tableName

查看自己的表使用的是哪一种数据库引擎

修改表的存储引擎

alert table table_name engine = engine_name

事物的隔离级别

读未提交(read uncommitted) 事务产生的问题都不能解决

读已提交(read committed) 解决脏读

可重复读(repeatable read) 解决不可重复读

串行化(serializable) 都能够解决(脏读、幻读、不可重复读)

9.数据库的索引优化条件

优点

1.通过创建唯一性索引，保证数据库表中的每一行数据的唯一性

2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因

3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

4.在使用分组和排序字句进行数据检索时，同样可以显著减少查询中分组和排序的时间

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2.索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定物理空间，如果是聚簇索引，那么空间更大

3.当对表中数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

创建索引

1.普通索引 (INDEX)

1.1 直接创建

创建索引需要索引名、表名、以及列和需要匹配的字符长度

CREATE INDEX [index_name] ON [table_name] (column(length))

1.2 修改表结构的方式

Alter table 表名 add index 索引名(列名[(length)])

1.3 创建表时指定索引

Create Table 表名 ([…],Index 索引名 (列名1，列名2，…))

1.4 查看索引

Show Index From 表名

1.5 删除索引

DROP INDEX 索引名 ON 表名

Alert table 表名 DROP INDEX 索引名

2.唯一索引 (UNIQUE)

ALTER TABLE 表名 ADD UNIQUE(列名)

3.全文索引 fulltext

创建一个带有全文索引的表，全文索引使用FULLTEXT

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

在已经创建的表中添加全文索引

create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

修改表创建全文索引

alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

全文索引的使用

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

4.组合索引

如果对col1、col2、col3进行一个组合索引创建，会产生col1、（col1,col2）、(col1、col2、col3)三个索引

索引优化的前提：

什么样的数据能够建立索引？

1.在经常需要搜索的列上，可以加快搜索的速度

2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度

4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

5.在经常需要排序的列上创建索引，因为索引已经排序，这样利用索引的排序加快查询

6.在经常使用在Where字句中的列上面创建索引，加快条件的判断速度

建立索引，一般根据where的条件来建立，select的条件是where f1 和f2 只有在f1和f2上同时建立索引才有用。

不适合建立索引的列

1.查询很少的列

2.又很少数据值的列

3.对于那些定义为text、image和bit数据类型的列不应该建立索引

4.当修改性能远远大于检索性能时，不应该创建索引

sql的优化

1.避免全表扫描，例如select * from 表等等

2.尽量避免where字句中对字段进行null值判断，否则将导致引擎放弃使用索引进行全表扫描例如(select * from t where num is null)，可以将null设置一个默认的值0来避免

3.尽量避免where字句中使用！= 或者<>操作符，会放弃索引，全表扫描

4.尽量避免在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

5.in和not in也需要慎用

例如：select id from t where num in (1,2,3)

优化使用:select id from t were num between 1 and 3

6.下面的查询也将导致全表扫描

select id from t where name like ‘%abc%’

7.尽量避免在where字句中对字段进行表达式操作，这样导致引擎全表扫描

例如：select id form t where num/2 = 100(对索引列进行表达式，将不走索引)

优化：select id form t where num = 100*2(对条件进行表达式运算，走索引)

8.尽量避免在where字句中对字段进行函数操作，这将引擎全表扫描

select id form t where sunstring(name,1,3) = ‘abc’ 查询name以abc开头的id

优化：select id from t where name like ‘abc%’

9.不要在where字句中”=”左边进行函数、算数、表达式等运算

10.在使用索引字段作为条件时，如果索引是复合索引，那么必须使用到该索引第一个字段作为条件才能保证系统使用该索引

11.使用exist代替in 例如select num from t where num in（select num from b）优化select num from a where exit (select 1from b where a.num = b.num)

12.索引并不是越多越好，索引加快了搜索的效率，降低了插入与更新的效率

13.尽量使用数字类型的字段，只含数字信息的字段使用数值类型的字段

14.尽可能使用varchar代替char，因为是可变长的字段，空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率较高

15.任何地方都不要使用select * from，用具体的字段列代替*

it星

https://github.com/fengyanxing/2020/05/20/mysql-yu-ju-xue-xi/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 it星 !

sql

java工具使用笔记

it星

hutool工具使用笔记： hutool-aop JDK动态代理封装，提供非IOC下的切面支持 hutool-bloomFilter 布隆过滤，提供一些Hash算法的布隆过滤 hutool-cache 简单缓存实现 hut

2020-05-21 java工具

java工具

Mybatis学习(二)

接上次的mybatis学习日志 Mybatis提供的日志支持可选的值有：SLF4J、LOG4J、LOG4J2、JDK_LOGGING、COMMONS_LOGGING、STDOUT_LOGGING、NO_LOGGING，或者是实现了 o

2020-05-19 java深入学习

Mybatis

1. mysql数据库创建表语句

2. 常用聚合函数

3. 各种查询

4. 表中记录的相关操作

5.相关表的操作

6.视图

7.存储过程

8.操作数据库引擎

数据库引擎的介绍

MyISAM与Innodb的区别

数据库引擎的选择

查看数据库支持的引擎

查看数据库当前的默认引擎

查看数据库表使用的引擎

修改表的存储引擎

事物的隔离级别

9.数据库的索引优化条件

优点

缺点

创建索引

1.普通索引 (INDEX)

2.唯一索引 (UNIQUE)

3.全文索引 fulltext

4.组合索引

索引优化的前提：

sql的优化

你的赏识是我前进的动力