SQL自学笔记一

最新推荐文章于 2022-11-21 23:15:00 发布

zsxdg

最新推荐文章于 2022-11-21 23:15:00 发布

阅读量999

点赞数 2

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/zsxdg/article/details/108255658

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

什么是sql

1.结构化查询语言

2.访问数据库

3.ANSI标准计算机语言

SQL可以做什么

在数据库中，

查询

更新记录

插入

删除

创建新数据库

创建新表

创建存储过程

创建视图

设置表，存储过程，视图的权限

SQL分类

DDL:数据定义语言

DML：数据操纵语言

DCL：数据控制语言

DDL

对数据库内部的对象进行创建，删除，修改等操作的语言

与DML区别：DML只操作表内部的数据，不涉及表的定义，结构的修改，更不会涉及其他对象

DDL由数据管理员使用（DBA），开发人员一般很少使用

创建数据库：

create database dbname;

查看系统中有哪些数据库

show databases;

选择数据库

use dbname;

查看数据库下所有的表

show tables；

删除数据库

drop database dbname;

创建表

CREATE TABLE tablename(
column_name_1 column_type_1 constraints,
     column_name_2 column_type_2 constraints,
     ... ... 
     column_name_n column_type_n constraints);

查看表

desc tablename;

如果觉得上面的信息还不够全面，为了得到更全面的信息，可以使用：

show create table tablename \G;

删除表

drop table tablename；

修改表类型

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];

增加表字段

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];

删除表字段

ALTER TABLE tablename DROP [COLUMN] col_name;

字段改名

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];

修改字段排列顺序

ALTER TABLE tablename  [ADD|MODIFY] col_name column_definition [FIRST | AFTER col_name];

更改表名

ALTER TABLE tablename RENAME [TO] new_tablename

DML

插入记录

注意：1.可以不用指定列名，但value后面的顺序和字段的排列顺序应该一致

2.可含空的字段，非空但含有默认值的字段以及自增字段，可以不再insert后的字段出现，value后面对应各字段名称的值

INSERT INTO tablename(field1,field2,...fieldn) VALUES(value1,value2,...,valuen);

更新记录

UPDATE tablename SET field1=value1,field2=value2,...fieldn=valuen [WHERE CONDITION];

同时更新多个表中的数据

注意：多表更新的语法更多地用于根据一个遍的字段来动态地更新另一个表的字段

UPDATE t1,t2,...tn set t1.field1=expr1,tn.fieldn=exprn  [WHERE CONDITION]

查询记录

查询不重复的记录：distinct

条件查询

①where关键字

②=、>、<、>=、<=、！=

③and , or

SELECT * FROM tablename [WHERE CONDITION];

排序和限制

desc 降序排序

asc 升序排序

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] , field2 [DESC|ASC] , ... fieldn [DESC|ASC]];

对于排序后的记录，如希望只显示一部分，而不是全部，则可以使用limit关键字来实现

注意：1.limit经常和order by 一起配合使用来进行记录的分页显示

2.limit在其他数据库上不能使用

SELECT ... ... [LIMIT offset_start,row_count];


offset_start：表示记录的起始偏移量
row_count：表示显示的行数

聚合

注意：

HAVING和WHERE的区别在于，HAVING是对聚合后的结果进行条件的过滤，而WHERE是在聚合前就对记录进行过滤。如果逻辑允许，我们尽可能用WHERE先过滤记录，因为这样的结果集减小，聚合的效率将大大提高，最后再根据逻辑看是否用HAVING进行再过滤。

SELECT [field1,field2,...,fieldn] fun_name from tablename
	[WHERE where_contition]
	[GROUP BY field1,field2,...,fieldn [WITH ROLLUP]]
	[HAVING where_contition]



fun_name：表示要做的聚合操作，也就是聚合函数，常用的有sum（求和）、count(*)（记录数）、avg（平均值）、max（最大值）、min（最小值）
GROUP BY：关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数据，部门就应该写在GROUP BY后面
WITH ROLLUP：是可选参数，表明是否对分类聚合后的结果进行再汇总
HAVING：关键字表示对分类后的结果再进行条件的过滤

表连接

外连接：选出其他不匹配的内容

左连接：包含所有左边表中的记录甚至是右边表中没有和它匹配的项目 left join

右连接：包含所有右边表中的记录甚至是左边表中没有和它匹配的项目 right join

内连接：选出两张表中相互匹配的内容

子查询

关键字：in,not in,=,!=,exists,not exists

记忆联合

将两个表的数据按照一定的条件查询出来后，将结果合并在一起显示出来，union和union all

union和union all的区别：

union all是把结果集直接合并在一起，而union是将union all后的结果进行一次distinct，去除重复记录的结果

SELECT * FROM t1 UNION|UNION ALL SELECT * FROM t2 ......

可以用 ? contents的命令来查看帮助可以提供的分类

？ contents

查看sql支持的数据类型

? data types

了解int类型的具体介绍

? int

了解show命令能查看什么

? show

查看create table的语法

? create table

查看元数据信息

元数据值得是数据的数据，比如表名，列名，类型，索引名等表的各种属性名称

删除数据库下所有前缀为tmp 的表

select concat('drop table test1.',table_name,';') from tables where
table_schema='test1' and table_name like 'tmp%';

将数据库test1下所有存储引擎为myisam的表改成innodb

select concat('alter table test1.',table_name,' engine=innodb;') from tables
where table_schema='test1' and engine='MyISAM';

注意：tables表属于information_schema数据库内，所以use information_schemat选择数据库

完整性约束

primary key主键约束

foreign key 外键约束

union 唯一性约束

not null非空值约束

default 默认值约束

auto _increment 用于整数列默认自增1

1.主键约束

1.一个表只能有一个主键

2.唯一性原则，主键的值（键值），必须能够唯一表示表中的每一条记录，且不能为null

3.最小化原则，复合主键不能包括不必要的多余列，也就是说，当从一个复合主键中删除一列后，如果剩下的列构成的主键任然满足唯一性原则，那么这个符合主键是不正确的

4.一个列名在复合主键的列表中只能出现一次

将学号设置为主键

CREATE TABLE tb_student
(
stu_id INT AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30)
);

将学号和班级号设置为符合主键‘

CREATE TABLE tb_student
(
stu_id INT AUTO_INCREMENT,
stu_name VARCHAR(30),
class_id INT NOT NULL,
PRIMARY KEY (stu_id,class_id)
);

添加主键约束

ALTER TABLE tb_student ADD CONSTRAINT PRIMARY KEY(stu_id);

外键约束

只有InnoDB引擎类型支持外键约束

创建班级信息表和学生信息表，设置学生信息表中的班级编号为外键约束

-- 创建班级信息表
CREATE TABLE tb_class
(
class_id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(30) NOT NULL
);
-- 创建学生信息表，并设置班级ID的外键约束
CREATE TABLE tb_student
(
stu_id INT AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30),
class_id INT NOT NULL,
FOREIGN KEY fk_class_id (class_id)
REFERENCES tb_class(class_id)
);

添加外键约束

ALTER TABLE tb_student ADD CONSTRAINT FOREIGN KEY fk_class_id (class_id)
REFERENCES tb_class(class_id);

唯一约束

1.unique的本质是一种索引，一种数据结构，用于提高查询效率

2.可以为空

3.一张表中可以存储多个唯一约束

将学号和姓名设置为唯一约束

CREATE TABLE tb_student
(
stu_id INT UNIQUE,
stu_name VARCHAR(30) UNIQUE
);
INSERT INTO tb_student VALUES(NULL,NULL);
INSERT INTO tb_student VALUES(2,'zhangsan');
SELECT * FROM tb_student;

将学号和姓名设置为复合唯一约束

CREATE TABLE tb_student
(
stu_id INT,
stu_name VARCHAR(30),
UNIQUE uniq_id_name (stu_id,stu_name)
);

添加唯一约束

ALTER TABLE tb_student ADD CONSTRAINT uniq_id_name UNIQUE(stu_id,name);

非空约束

将姓名字段添加为非空约束

CREATE TABLE tb_student
(
stu_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
);
INSERT INTO tb_student VALUES (NULL,NULL);##错误信息:[Err] 1048 - Column
'stu_name' cannot be null

将name设置为非空

ALTER TABLE tb_student MODIFY COLUMN stu_name VARCHAR(30) NOT NULL;

default

可以指定字段的默认值

create table tb_student (
stu_id INT auto_increment PRIMARY KEY,
stu_name VARCHAR(30),
stu_sex CHAR(1) DEFAULT "F"
);
INSERT INTO tb_student(stu_name) VALUES ('zhangsan');
SELECT * FROM tb_student;

auto_increment

自动增长，通常配主关键字段使用，只能用于整型

对于自增的字段，在用delete删除后，再插入值，该字段仍然按照删除前的位置继续增长

CREATE TABLE tb_student(
stu_id INT auto_increment PRIMARY KEY,
stu_name VARCHAR(30),
stu_sex CHAR(1) DEFAULT "F"
);
INSERT INTO tb_student(stu_name) VALUES ('zhangsan');
INSERT INTO tb_student(stu_name) VALUES ('lisi');
INSERT INTO tb_student(stu_name) VALUES ('wangwu');
DELETE FROM tb_student;
INSERT INTO tb_student(stu_name) VALUES ('liqian');

约束的删除

ALTER TABLE 表名 DROP [FOREIGN KEY| INDEX 约束名称]|[PRIMARY KEY]

示例

CREATE TABLE tb_class
(
class_id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(30) NOT NULL
);
CREATE TABLE tb_student
(
stu_id INT,
stu_name VARCHAR(30) ,
class_id INT NOT NULL,
-- 主键约束
PRIMARY KEY(stu_id),
-- 外键约束
FOREIGN KEY fk_class_id (class_id)
REFERENCES tb_class(class_id),
-- 唯一性约束
UNIQUE uniq_name(stu_name)
);
-- 删除主键约束
ALTER TABLE tb_student DROP PRIMARY KEY;
-- 删除外键约束
ALTER TABLE tb_student DROP FOREIGN KEY fk_class_id;
-- 删除唯一性约束
ALTER TABLE tb_student DROP INDEX uniq_name;

数据类型

数值类型

字符串类型

日期和时间类型

数值类型

严格数值类型:integer(int),smallint,decimal(dec),numeric

近似数值类型：float,feal,double,precicio

扩展后增加的类型：tinyint,mediumint,bigint,bit

创建表t1，设置id1 和id2两个字段的宽度分别为int和int（5）

create table t1(id1 int,id2 int(5));

插入数据，id1和id2 均插入值1

insert into t1 values(1,1);
select * from t1;

日期和时间类型

1.表是年月日，用date

2.表示年月日时分秒，用datetime或者timedate

3.只表示时分秒，用time

4.只表示年份，用year

注意：每种日期类型都有一个有效值范围，如果超出了这个范围，系统将进入错误提示，并将以零值来进行存储

创建表t1，字段分别为date,time,datetime 的日期类型

create table t1(d date,t time,dt datetime);

插入测试数据，使用now函数，表示当前日期

insert into t1 values(now(),now(),now());
select * from t1

timestamp

mysql 中有这样的一个默认行为，如果一行数据中某些列被更新了，如果这一行中有timestamp类型的列，那么么这个timestamp列的数据，也会被自动更新到更新操作所发生的那个时间点；这个操作是由 explicit_defaults_for_timestamp这个变更控制的，explicit_defaults_for_timestamp值为no时， timestamp列的值都要显示指定，否则为默认值null。

1）取值为off时

查看explicit_defaults_for_timestamp参数的默认值；

show variables like 'explicit%';

修改explicit_defaults_for_timestamp的值

set explicit_defaults_for_timestamp = off;

创建表t1

create table t1(ts timestamp);

插入null值测试

insert into t1 values(null);
select * from t1;

修改表结构

alter table t1 add column ts2 timestamp default current_timestamp;

插入null值测试

insert into t1 values(null,null);
select * from t1;

创建一个带有timestamp字段y的表t后，修改表结构，再增加一个timestamp的字段z，插入测试数据时只插入x字段，插入成功后查询表数据。

create table t(x int ,y timestamp);
alter table t add column z timestamp default current_timestamp;
insert into t(x) values(1);

修改字段x的值，update 时timestamp列x会自动更新，而z不会。

update t set x=2 where x=1;

2）取值为on时

修改explicit_defaults_for_timestamp的值

set explicit_defaults_for_timestamp = on;

创建表t1并查看表结构：

create table t1(ts timestamp);

插入测试数据

insert into t1 values(null);
insert into t1 values('2020-06-24 00:00:00');
insert into t1 values(default);

关于时区的特性

创建表t1

create table t1(ts1 timestamp not null default current_timestamp,ts2 datetime
default null);

查看当前时区

show variables like 'time_zone';

插入测试数据

insert into t1 values(now(),now());
select * from t1;

设置时区，东九区

set time_zone = "+9:00";

查看测试数据

select * from t1;

字符串类型

1.char和varchar的区别

①char 列的长度固定为创建表时声明的长度，而varchar 列中值为可变长字符串

②在检查的时候，char删除尾部的空格，而varchar 则保留这些空格

创建表t1

create table t1(v varchar(4),c char(4));

插入测试数据

insert into t1 values('ab ','ab ');
select * from t1;

查看长度

select length(v),length(c) from t1;

追加字符

select concat(v,'+'),concat(c,'+') from t1;

2.binary和varbinary

类似与char和varchar，不同的是他们包含二进制字符串和不包括二进制字符串

创建表t1

create table t1(c binary(3));

插入测试数据

insert into t1 values('a');
select * from t1;
select *,hex(c),c='a',c='a\0',c='a\0\0' from t1;

3.enum:枚举类型

①忽略大小写

②插入不在enum范围内的值是，插入的是第一个值

③只允许从值机和中选取单个值，不能一次取多个值

创建临时表t1

create table t1(gender enum('M','F'));

插入测试数据

insert into t1 values('M'),('1'),('f'),(null),('2');
select * from t1;

json类型

1.是一种数据交换格式

2.优点

①自动校验数据是否为json类型。若不是，则报错

②提供了一组操作JSON数据的内置函数，可以方便地提取各类数据，可以修改特定的键值；

③优化的存储格式，存储在JSON列中的JSON数据被转换成内部的存储格式，允许快速读取

3.支持的数据类型

BOOLEAN：true/false

NULL：null

字符串和日期类型：用双引号引起来

ARRAY：括号引起来

OBJECT：KV，要用大括号引起来

4.注意：JSON，数据类型对于大小写是敏感的，null,false 必须小写才合适

创建临时表t1

create table t1(tempcol json);

插入测试数据

insert into t1 values('{"age":20,"time":"2020-2-1"}');
select * from t1;

通过json_type函数可以看到插入的JSON数据是哪种类型

select json_type('"abc"') json1,json_type('[1,2,"abc"]')
json2,json_type('{"k1":"value1"}') json3;

通过json_valid函数判断json数据是否合法

select json_valid('null') json1,json_valid('NULL') json2;

算术运算符

+：加

-：减

* ：乘

/、DIV：除法，返回商

%，MOD：除法，返回余数

注意：在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为null

select 0.1+0.3333,0.1-0.3333,0.1*0.3333,1/2,1%2;
select 1/0,1%0;
select 1/0.0,1%0.0
select mod(3,2),3%2;

比较运算符

=：比较运算符两侧的数值是否相等，如果相等返回1，否则返回0，null不可以用=比较

<>:两侧操作数不等，返回1，否则返回0，null不可以用<>比较

<=>：操作数亮灯返回1，即使操作数为null也可以正确比较

<:当左侧操作数小于右侧操作数时，其返回值为1，否则其值为0。

>:当左侧操作数大于右侧操作数时，其返回值为1，否则返回值为0。

<=:当左侧操作数小于等于右侧操作数时，其返回值为1，否则返回值为0

>=:当左侧操作大于等于右侧操作数时，其返回值为1，否则返回值为0。

”BETWEEN“运算符的使用格式为“a BETWEEN min AND max”，当a大于等于min并且小于等于max，则返回值为1，否则返回0

”IN“运算符使用的格式为”a IN(value1,value2...)“，当a的值存在于列表中时，则整个比较表达式返回的值为1，否则返回0。

”IS NULL“运算符的使用格式为”a IS NULL“，当a的值为NULL，则返回值为1，否则返回值为0

”IS NOT NULL“运算符的使用格式为”a IS NOT NULL“，当a的值为不为NULL，则返回值为1，否则返回值为0

”LIKE“运算符的使用格式为”a LIKE %123%“，当a中含有字符串”123“时，则返回值为1，否则返回值为0

”REGEXP“运算符的使用格式为”str REGEXP str_pat“，当字符串中含有str_pat相匹配的字符串，则返回值为1，否则返回0。

select 1=1,1=0,null=null;
select 1<>1,1<>0,null<>null;
select 1<=>1,1<=>0,null<=>null;

逻辑运算符

“NOT” 或 “!” 表示逻辑非

“AND” 或 “&&” 表示逻辑与运算

“OR” 或 “||” 表示逻辑或运算。

“XOR” 表示逻辑异或。

注意：0表示真，非0表示假，not null的取值依然为null

select not 0,not 1,not null;
select (1 and 1),(0 and 1),(3 and 1),(1 and null);

select (1 or 1),(0 or 1),(0 or 0),(0 or null);
select 1 xor 1,0 xor 0,1 xor 0,null xor 1;

位运算符

“&” 位与：对多个操作数的二进制位做逻辑与操作。

“!” 位或：对多个操作数的二进制位做逻辑或操作。

“^” 位异或：对操作数的二进制位做异或操作。

“~” 位取反：对操作数的二进制位做NOT操作。

“>>” 位右移：对左操作数向右移动右操作数指定的位数。

“<<” 位左移：对左操作数向左移动右操作烽指定的位数。

select 2&3;
select 2|3;
select 2^3;
select ~1;##常量数字以8字节存储，即64位，前63个0加1个1
select 100>>3;##1100100 -> 1100 -> 12
select 100<<3;##1100100 -> 1100100000 -> 800

运算符的优先级

优先级由低到高排列，同一行中的运算符具有相同的优先级：

 select 1*3 - 0 or 1;

select 1*3 -(0 or 1);

字符串函数

MySQL支持的字符串函数

测试：

select concat('I',' Like',' Chinasofti!');
select insert('welcome to beijing',12,7,'guangzhou');
 select lower('WELCOME TO BEIJING'),upper('welcome to guangzhou');
select left('welcome to beijing',7),right('welcome to beijing',7);
select lpad('2020',20,'beijing'),rpad('beijing',20,'2020');
select ltrim(' beijing'),rtrim('beijing ');

select trim(' bei jing ');
select ltrim(' beijing'),rtrim('beijing ');

 select trim(' bei jing ');
 select repeat('mysql',3);
select replace('welcome to beijing','beijing','guangzhou');
select strcmp('a','b'),strcmp('a','a'),strcmp('c','b');
select substring('welcome to beijing',12,3);

数值函数

MySQL支持的数值函数

测试

select abs(-8),abs(8);
 select ceil(-0.8),ceil(0.8);
select floor(-0.8),floor(0.8);
select mod(5,2);
select rand();
select ceil(rand()*100);
select round(5.1234,2);
select round(5.12456,3);
select truncate(5.12456,3);

日期和时间函数

MySQL支持的日期和时间函数：

测试

select curdate();
select curtime();
select now();
select unix_timestamp(now());
select from_unixtime(1581887362);
select week(now());
select year(now());
select hour(now());
 select minute(now());
select monthname(now());

MySQL支持的日期和时间格式：

测试

select date_format(now(),'%M,%D,%Y');
select date_format(now(),'%Y-%m-%d');
select date_format(now(),'%Y年%m月%d日');

MySQL支持的日期间隔类型：

select date_add(now(),interval 31 day);
select date_add(now(),interval '1_2' year_month);
select date_add(now(),interval -3 day);
select datediff(now(),'2019-12-12');

流程函数

MySQL支持的流程函数：

创建临时表

create table salary(userid int,salary decimal(9,2));

插入测试数据

insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000)，(6,null);

查询

select * from salary;

测试

select userid,salary,if(salary>2000,'高','低') as salary_level from  salary;
select userid,salary,ifnull(salary,0) from salary;

select userid,salary, case ifnull(salary,0)
-> when 0 then '零薪酬'
-> when 1000 then '很低'
-> when 2000 then '有点低'
-> else '高' end
-> as salary_level from salary;

 select userid,salary,case when
-> ifnull(salary,0)<=2000 then '低'
-> else '高' end as salary_level from salary;

其他常用函数

MySQL中的其他常用函数：

测试

select database();
select version();
 select user();
select inet_aton('192.168.1.1');
 select inet_ntoa(3232235777);
select md5('123456');

如何选择合适的引擎

• MyISAM ：

• MySQL5.5 之前版本默认的存储引擎。如果应用是以读操作和插入操作为主，只有极少的更新和删除操作，并且对事务的完整性没有要求、没有并发写操作，那么选择这个存储引擎是适合的。 OLTP 环境一般建议不要再使用 MyISAM 。

• InnoDB ：

• MySQL5.5 之后版本默认的存储引擎，用于事务处理应用程序，支持外键，对于大多数的应用系统， InnoDB 都是合适的选择。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么应该优先选择 InnoDB 存储引擎。 InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（ Commit ）和回滚（ Rollback）

• MEMORY ：

• 将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问速度。 MEMORY 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。 MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

• MERGE ：

• 用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。 MERGE 表的优点在于可能突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。这对于诸如数据仓储等 VLDB 环境十分适合。

各种存储引擎的特性

各种存储引擎的对比

• MyISAM ：

• MyISAM既不支持事务，也不支持外键，对事务完整性没有要求或者以 SELECT 、 INSERT 为主的应用可以使用这个引擎来创建表。

• InnoDB ：

• InnoDB 提供了具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小粒度和更强的并发能力，拥有自己独立的缓存和日志。

• 对比MyISAM存储引擎， InnoDB 会占用更多的磁盘空间以保留数据和索引。

• 不同于使用其他存储引擎的表的特点：

• 自动增长列

• 外键约束

• 主键和索引

• 满足唯一和非空约束；

• 优先考虑使用最经常被当作查询条件的字段或者自增字段

• 字段值基本不会被修改

• 使用尽可能短的字段

• 存储方式

• 共享表空间存储

• 多有空间存储

• MEMORY ：

• MEMORY存储引擎使用存在于内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是 .frm 。 MEMORY 类型的表访问非常地快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

• ME RGE ：

• ME RGE 存储引擎也被称为 MRG_MyISAM ，是一组 MyISAM 表的组合。这些 MyISAM 表必须结构完全相同， MERGE 表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行的。

• 对于 MERGE 类型表的插入操作，是通过 INSERT_METHOD 子句定义插入的表

• 对于 MERGE 类型表进行 DROP 操作，只是删除 MERGE 的定义，对内部的表没有任何影响。

• TokuDB ：

• TokuDB 是一个高性能、支持事务处理的存储引擎，具有高扩展性、高压缩率、高效的写入性能，支持大多数在线 DDL 操作。

• 使用 Fractal 树索引保证高效的插入性能；

• 优秀的压缩特性，比 InnoDB 高近 10 倍；

• Hot Schema Changes 特性支持在线创建索引和添加、删除属性列等 DDL 操作；

• 使用 Bulk Loader 达到快速加载大量数据；

• 提供了主从延迟消除技术；

• 支持 ACID 和 MVCC 。

MySQL存储引擎概述

MySQL5.7支持的存储引擎：

InnoDB

MyISAM

MEMORY

CSV

BLACKHOLE

char和varchar

固定长度与可变长度的字符串类型，差异为：

创建临时表

create table temptable(vcol varchar(4),ccol char(4));

插入测试数据

insert into temptable values('ab ','ab ');

select * from temptable;

结论：

char：长度变化不大并对查询速度有较高要求的数据

varchar：可变长度

在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，概括如下：

MyISAM存储引擎：建议使用固定数据列代替可变长度的数据列。

MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理。

InnoDB存储引擎：建议使用VARCHAR类型。

对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的

text与BLOB

用于保存较大文本，差别在于：

BLOB能用来保存二进制数据（照片）；

TEXT只能保存字符数据（文章或日记）；

BLOB和TEXT的常见问题

BLOB和TEXT值会引起一些性能问题，特别是在执行大量的删除操作时；

可以使用合成的索引来提高大文本字段的查询性能；

在不必要的时候避免检索大型的BLOB或TEXT值；

把BLOB或TEXT列分离到单独的表中；

注意：：尽可能在OLTP(On-Line Transaction Processing联机事务处理过程)环境避免使用BLOB或TEXT 类型，优先使用VARCHAR。VARCHAR类型最长可以支持65533字节的长度，已经可以满足绝大多数的需求。

浮点数和定点数

浮点数：一般用于表示含有小数部分的数值；如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。

定点数：以字符串形式存放，可以更精确地保存数据；如果实际插入的数值精度大于实际定义的精度，则MySQL会进行警告（默认的SQLMode下），但是数据按照实际精度四舍五入插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会报错，导致数据无法插入。

创建临时表

create table temptable(fcol float(8,1));

插入测试数据

insert into temptable values(1.23456);

insert into temptable values(1.25456);

select * from temptable;

关于浮点数和定点数的应用中，用户要考虑到以下几个原则：

浮点数存在误差问题；对货币等对精度敏感的数据，应该用定点数表示或存储；

在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

要注意浮点数中一些特殊值的处理。

日期类型选择

MySQL提供的常用日期类型有DATE、TIME、DATETIME和TIMESTAMP，选择日期类型的原则：

根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型，这样不仅仅能节约存储，更能够提高表的操作效率。

如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不要使用 TIMESTAMP，因为TIMESTAMP表示的日期范围比DATETIME要短得多。

如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

字符集的概述

20世纪60年代初：ASCII

ISO-8859系列

GB2312-80

GBK

BIG 5等

Unicode简述

国际标准化组织（International Organization for Standardization，ISO）

1984 UCS 标准编号：ISO-10646 采用4字节32位编码，简称为UCS-4

1991 Unicode1.0

16位编码 UTF（UCS/Unicode Transformation Format）

汉字及一些常见的字符集

汉字字符集编码标准 GB 2312-80：全称《信息交换用汉字编码字符集基本集》，收录了6763个常用汉字和682个非汉字图形符号；

GB 13000：全称《信息技术通用多八位编码字符集（UCS）第一部分：体系结构与基本多文种平面》，收录27484个汉字以及一些偏旁部首等；

GBK：全称《汉字内码扩展规范》1.0版

GB 18030：全称《信息技术信息交换用汉字编码字符集、基本集的扩充》27484

怎样选合适的字符集

对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响，综上所述，选择合适字符集，主要考虑以下几个因素：

满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集；如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性；

如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK；

如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集处理速度快；

如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

MySQL支持的字符集简介

查看所有可用字符集

show character set

查看information_schema.character_set，可以显示所有的字符集和该字符集默认的排序规则

MySQL的字符集包括字符集和排序规则两个概念，字符集和排序规则是一对多的关系

字符集：存储字符串的方式；

排序规则：定义比较字符串的方式每个字符集至少对应一个排序规则。可以用”show collation like '***';”

查看字符集对应的规则

show collation like 'utf8%';

排序规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以ci（大小写不敏感）、cs（大小写敏感）或_bin（二元，即比较是基于字符编码的值而以language无关）结束。

MySQL字符集的设置

1）服务器字符集和排序规则

MySQL的字符集和排序规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

服务器字符集和排序规则，可以在MySQL服务启动的时候确定：

可以在my.ini中设置 character-set-server=utf8

在启动项中指定 mysqld --character-set-server=utf8

在编译时指定 shell > cmake . -DDEFAULT_CHARSET=utf8

查看当前服务器上午字符集和排序规则

 show variables like 'character_set_server';

 show variables like 'collation_server';

2）数据库字符集和排序规则

数据库的字符集和排序规则既可以在创建数据库的时候指定，也可以在创建完数据库后通过”alter database“命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。

设置数据库字符集的规则如下：

如果指定了字符集和排序规则，则使用指定的字符集和排序规则；

如果指定了字符集没有指定排序规则，则使用指定字符集默认排序规则；

如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；

如果没有指定字符集和排序规则，则使用服务器字符集和排序规则作为数据库的字符集和排序规则。

查看当前数据库字符集和排序规则

show variables like 'character_set_database';

 show variables like 'collation_database';

3）表字符集和排序规则

表的字符集和排序规则在创建表的时候指定，可以通过alter table命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。

设置表字符集的规则如下：

如果指定了字符集和排序规则，则使用指定的字符集和排序规则；

如果指定了字符集没有指定排序规则，则使用指定字符集默认排序规则；

如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；

如果没有指定字符集和排序规则，则使用数据库字符集和排序规则作为表的字符集和排序规则

显示当前字符集的排列顺序

show create table temptable;

4）列字符集和排序规则

MySQL可以定义列级别的字符集和排序规则，主要是针对相同的表不同字段需要使用不同的字符集的情况。

列字符集和排序规则的定义可以创建表时指定，或者在修改表时调整。如果在创建表的时候没有特别指定字符集和排序规则，则默认使用表的字符集和排序规则。

5）连接字符集和排序规则

对于客户端和服务器的交互操作，MySQL提供了3个不同的参数

character_set_client（客户端）

character_set_connection（连接）

character_set_results（返回结果的字符集）

索引概述

1）创建索引

创建表的时候创建索引

create table student(id int primary key,name varchar(20),telephone
varchar(20),index(name));

使用CREATE INDEX创建索引

create table student(id int,name varchar(20),telephone varchar(20))；
insert into student values(1,'zhang','15251398693'),
(1,'li','15251398694');
delete from student;
select * from student;
create index ix_student_name on student(name);
show index from student \G
create unique index ix_student_id on student(id);
show index from student \G;
insert into student values(1,'zhang','15251398693'),(1,'li','15251398694');
select * from student;

使用ALTER TABLE创建索引

drop index ix_student_id on student;
show index from student \G;
alter table student add constraint pk_student_id primary key(id);
alter table student add index ix_student_name(name);
show index from student \G;

2）查看索引

show keys from student \G;
show index from student \G;

3）删除索引

drop index ix_student_name on student;
show index from student \G;

设计索引的原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效地使用索引。

要在条件列上创建索引，而不是查询列；

尽量使用唯一索引；

使用短索引；

利用最左前缀；

对于InnoDB存储引擎的表，尽量手工指定主键。

索引优势

1. 索引最大的一个优势就是提高查询效率，它可以通过创建唯一索引或者主键索引来标识行的唯一性，在查询的时候可以快速定位到要查询的行数据；

2. 可以加快表与表之间的连接查询；

3. 在分组和排序的时候可以极大的节省时间，排序的时候利用索引可以进行快速排序，分组查询时虽然不能直接利用索引，但是分组查询先进行排序，在排序这个阶段会减少时间，所有分组查询的时候会进行快速排序。

4. 使用索引来进行查询、排序、分组，使用优化隐藏器，提高系统的性能。

索引劣势

1. 创建索引和维护索引需要耗费时间，随着数据量的增大时间也会逐渐增大；

2. 索引文件的大小也会逐渐增大，索引文件需要占用物理空间，如果建立聚簇索引文件大小会更大，会使数据库的存储逐渐变大；

3. 对表的增加、修改、

删除的时候，都需要对索引的维护，会影响对表和数据的操作速度，加大对表的操作时间

索引设计的误区

不是所有的表都需要创建索引；

不要过度索引；

谨慎创建低选择度索引

设计索引的步骤

1. 整理表上的所有SQL，重点包括select、update、delete操作的where条件所用到的列的组合、关联查询的关联条件等

2. 整理所有查询SQL的预期执行频率；

3. 整理所有涉及的列的选择度，列的不同值相比总非空行数的比例较大，选择度越好，比如全部都是唯一值的主键列选择度最高；

4. 遵照之前提到的设计原则，给表选择合适的主键；

5. 优先给那些执行频率最高的SQL创建索引，执行频率很高的SQL，使用到的索引的效率对整体性能影响也会很大，选择其中选择度最高的列创建索引，如果选择度都不够好，那么应该考虑是否可以使用其他选择度更好的条件，或者选择创建联合索引；

6. 按执行频率排序，依次检查是否需要为每个SQL创建索引；

7. 索引合并，利用复合索引来降低索引的总数，充分利用最左前缀的原则；

8. 上线之后，通过慢查询分析、执行计划分析、索引使用统计，来确定索引的实际使用情况。

BTREE与HASH索引

MEMORY存储引擎的表可以选择使用BTREE索引或HASH索引，两种不同类型的索引各有其不同的适用范围。

Hash索引只能用于对等比较，例如=,（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

HASH索引有一些重要的特征在使用时需特别注意：

只用于使用=或操作符的等式比较；

优化器不能使用HASH索引来加速ORDER BY操作；

MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY 表，会影响一些查询的执行效率；

只能使用整个关键字来搜索一行

而对于BTREE索引，当使用>、=、，或者LIKE 'pattern'（其中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。

下列范围查询适应于BTREE索引和HASH索引：

select * from t1 where key_col = 1 or key_col in(15,18,20);

下列范围只适用于BTREE索引：

select * from t1 where key_col > 1 and key_col < 10;

select * from t1 where key_col like 'ab%' or key_col between 'lisa' and 'simon';

当对索引字段进行范围查询的时候，只有BTREE索引可以通过索引访问，而HASH索引实际上是全表扫描的。

 CREATE TABLE `district_memory` (
 `id` int(11) NOT NULL DEFAULT '0' COMMENT '主键自增',
`pid` int(11) DEFAULT NULL COMMENT '父类id',
`district_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '城市
的名字',
 `type` int(11) DEFAULT NULL COMMENT '城市的类型，0是国，1是省，2是市，3是区',
 `hierarchy` int(11) DEFAULT NULL COMMENT '地区所处的层级',
 `district_sqe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT
'层级序列',
 PRIMARY KEY (`id`)
 ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
insert into district_memory select * from district;

查看执行计划

explain select * from district where id > 10 and id < 100 \G;

explain select * from district_memory where id > 10 and id < 100 \G;

 explain select * from district where id = 10 \G;

explain select * from district_memory where id = 10 \G;

索引在MySQL8.0中的改进

1）不可见索引

所谓不可见，指的对于查询优化器不可见，SQL在执行时自然也就不会选择，但在查看表结构时候索引仍然能看到，也可以通过information_schema.statistics或者show index来查看索引是否可见的状态。

索引默认是可见的，可以通过以下方式来创建不可见索引：

在创建索引时指定invisble关键字来创建不可见索引

通过命令单独添加不可见索引

通过alter table命令来修改索引是否可见

引用不可见索引的目的：

主要是为了减小对于表上的索引进行调整时潜在风险

创建不可见索引：

drop table t;
create table t(i int,j int,k int,index ix_t_i(i) invisible);
 create index ix_t_j on t(j) invisible;
alter table t add index ix_t_k(k) invisible;

通过alter index来修改索引的可见性：

alter table t alter index ix_t_k visible;

查看索引

show index from t \G;

2）倒序索引

由于倒序索引的引入，MySQL8.0取消了对于group by操作的隐式排序，如果业务中有依赖于此特性的，在升级数据库版本的时候要谨慎。

创建倒序索引：

 drop table t;
 create table t(a int,b int,index ix_t_a_desc_b_asc(a desc,b asc));

什么是视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。

视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图的优势主要包括：

简单

安全

数据独立

视图操作

创建视图

修改视图

删除视图

查看视图定义

zsxdg

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
SQL自学笔记一

什么是sql1.结构化查询语言2.访问数据库3.ANSI标准计算机语言SQL可以做什么在数据库中，查询更新记录插入删除创建新数据库创建新表创建存储过程创建视图设置表，存储过程，视图的权限SQL分类DDL:数据定义语言DML：数据操纵语言DCL：数据控制语言DDL对数据库内部的对象进行创建，删除，修改等操作的语言与...
复制链接

扫一扫