MySQL最好实行

2019-06-28 作者:数据库   |   浏览(78)

MySQL最佳实践

1、MySQL启动和关闭
(安装及配置请参照百度经验,这里不再记录。MySQL默认端口号:3306;默认数据类型格式:utf8)
法①:我的电脑→右击→管理→服务和应用程序→选中MySQL→右击→启动 (关闭:选择“关闭”)

Mysql数据库:端口号3306,超级用户 root unsigned 正值

 

图片 1

修改提示符:--prompt 提示符

从以下两方面讲MYSQL最佳实践: 

图片 2

参数:D 完整的日期 d当前数据库 h服务器名称 u 当前用户
数据是否可以为空:NULL NOT NULL
编号:与主键组合使用,起始值为1. AUTO_INCREMENT

  1. 表结构最佳实践

法②:cmd→net start mysql (关闭:net stop mysql)

主键:PRIMARY KEY或者KEY。每张表只存在一个主键;主键自动设置为NOT NULL;主键保证记录唯一性。编号必须和主键配合使用,而主键不一定和编号配合使用。

2.QUERY最佳实践

2、MySQL的登录和退出
(在进入MySQL的DOS命令中,cls有清屏功能,登录MySQL以后,该语句无法实现)

外键:FOREIGN KEY。保持数据的一致性,完整性;实现一对一或一对多的关系。外键列和参照列必须有相似的数据类型。若是数字,则其长度和有无符号位必须相同,而字符的长度可以不同。数据表的存储引擎只能是InnoDB。

 

参数
详细
描述

外键约束的参照操作:
1、CASCADE:在父表中进行删除或更新时,子表对应行也进行改变。
2、SET NULL:必须保证子表列没有设置成NOT NULL情况下,才能随父表进行删除或更新,并设置子表中的外键列为NULL。
3、RESTRICT:拒绝对父表的删除或者更新。
4、NO ACTION:与RESTRICT相同。
子表:具有外键列,子表所参照的表叫做父表。

表结构最佳实践: 

-D
--database=name
打开指定的数据库

唯一约束:UNIQUE KEY。允许为空;每张表可以有多个唯一约束;保证记录唯一性(存储过程中保留空值只能有一个)。
默认值:DEFAULT。当插入字段是,如果没有明确赋值,系统自动赋予默认值。

 

-V
--version
输出版本信息并退出

数据类型:整型(TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT)
浮点型(FLOAT[(M、D]、DOUBLE[(M、 D)])M数字总位数 D小数点后总位数
日期时间型:YESR TIME DATE DATATIME TIMESTAMP

1.越小通常越好

-u
--user=name
用户名

修改定界符:delimiter

 

-P
--port=#
端口号

创建数据库:CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/DECIMAL/DOUBLE

-p
--password[=name]
密码

修改数据库编码方式:ALTER {DATABASE|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

在满足需求的前提下,尽量选择占用字节数小的数据类型。如上,能用TINYINT(1 byte)时,绝不用 SMALLINT(2byte)/MEDIUMINT(3byte).

-h
--host=name
服务器

删除数据库:DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

不会有负数存储时,尽量用UNSIGNED的类型。

登录:
a、输入“mysql -uroot -p”,再按回车键。(root是绝对用户)
b、在“Enter password:”后输入密码,再按回车键;若未设定密码,直接按回车键
c、提示成功登录MySQL,成功登陆后有“mysql>”标识

创建数据表:CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,........);

若可行,则尽量用INT替代FLOAT,DECIMAL等形式。如存储价格时,100*price 转成整型。

图片 3

查看数据表:SHOW TABLE [FROM db_name] [LIKE 'pattern'|WHERE expr]

这么做的主要目的,是节省存储空间。数据所占空间越小,查询时需要走得路就越少,从而节省时间。 

退出:
输入“exit;”或“quit;”或“q;”(“;”是MySQL语句中必须包含的结束标识符)
提示Bye,即表示退出MySQL

查看数据表结构:SHOW COLUMNS FROM tbl_name

 

图片 4

插入记录:INSERT [INTO] tbl_name [(col_name)] VALUES (val,......)

2.简单就好

3、快捷输入
按方向键“↑”、“↓”可以快速填充历史输入数据。

外键约束:FOREIGN KEY (f_name) REFERENCES tbl_name (p_name)

 

4、MySQL语句的规范
关键字与函数名称全部大写;
数据库名称、表名称、字段名称全部小写;
SQL语句必须以分号结尾(若未填写分号, 会有一个“->”的提示,表示期待一个结束的命令)。

显示表结构:SHOW CREATR TABLE tbl_name;

DATE/TIMESTAMP/DATETIME (3 byte/ 4byte/8byte)

5、MySQL常用命令
显示当前服务器版本 SELECT VERSION();
显示当前日期 SELECT NOW();
显示当前用户 SELECT USER();
SELECT 查询表数据,SHOW 显示有许多形式,提供信息数据库、表、列,或状态信息服务器。

显示索引:SHOW INDEXES FROM tbl_name (G按列显示)

道理同上。说明一点:

6、数据库的创建、查看、修改、删除
("{}"为必选项,"[]"为可选项,"|"表示从中选择)
创建:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
查看(打开):
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
修改:
ALTER {DATABASES | SCHEMAS} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
删除:
DROP {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
示例:
创建一个数据库t1

添加一列:ALTER TABLE tbl_name ADD 字段名 数据类型 约束条件[FIRST|AFTER col_name]

TIMESTAMP是从1970年到2038就没了的。

图片 5

添加主键约束:ALTER TABLE tbl_name ADD[CONSTERAINT(约束)[symbol(约束名字)]] PRIMAEY KEYindex_type

DATETIME则从1000到9999年为止的。所以,存储空间省还是不省?值不值得省,完全取决于业务需求了。

查看数据库t1

添加或删除默认约束:ALTER TABLE tbl1 ALTER SET age DEFAULT'34';
DROP DEFAULT;
删除主键约束:ALTER TABLE tbl DROP PRIMARY KEY;(只有一个主键不需要写名字)
删除唯一约束:ALTER TABLE tbl DROP {INDEX|KEY} index_name;

 

图片 6

修改列定义:ALTER TABLE tbl MODIFY[COLUMN] id (要修改成的条件) SMALLINT....[FIRSE|AFTER col_name(某个字段)]

3.尽量避免NULL

再次创建一个数据库,命名t1,会有错误提示。这时忽略错误提示加入“IF NOT EXISTS”就不再提示错误,这时虽然没有错误但是会出现警告

数据表更名:ALTER TABLE tbl RENAME [TO|AS] tbl1;
RENAME tbl TO tbl1 [tb TO tb1,.......]

 

图片 7

删除表中数据:DELETE FROM tbl WHERE id=1;

额外BYTE消耗,增加查询复杂度 (vs.空)

查看警告

查找语句:
字段别名:SELECT id AS userid,username AS uname FROM users;(为表中的字段重新定义一个名字)
GROUP BY:分组显示
HAVING:分组条件
ORDER BY:对分组进行排列[ACE|DESC]
LIMIT:限制查询返回数量 SELECTFROM users LIMIT 2;
SELECT
FROM users LIMIT 3,2;

只有需要区分空与NULL时,才可NULL,否则最好都有个默认的空值。

图片 8

子查询(SubQuery):嵌套在查询内部,放在圆括号里面。
将查询后的结果写入表中:INSERT [INTO] tbl [(id,username)]
SELCET...
连接类型:
1、内连接(INNER JOIN):显示两表中符合连接条件的部分。
2、左外连接(LEFT[OUTER] JOIN):显示左表中的全部和右表中符合条件的部分。
3、右外连接(RIGHT[OUTER] JOIN)

对数字型,默认值一般是0,或0.0之类的。这是有别于NULL的,好理解。

创建数据类型为gbk格式的数据库

两张表连接更新:
方法一:UPDATE tbl1(要更新的表) INNER JOIN tbl2(参考表) ON tbl1.id=tbl2.id(条件) SET...
方法二:创建一张新表,创建新的字段用于保存查找后的结果。
CREATE TABLE [IF NOT EXIST] tbl
create_name ....
SELECT...
当两张表连接时,两表中存在相同的字段,因此可以给同名字段赋予不同的别名。数据表在使用过程中可以赋予别名使用。

对于string型,空可表示为两种:'' 和NULL.两者是很有区别的。

图片 9

假设你写col字段为空的查询:

修改数据库t2的类型

1)无NULL的情况: 

图片 10

select * from t where col=''

删除数据库
(查看所有的数据库SHOW DATABASES)

2) 有NULL的情况

图片 11

select * from t where col is NULL or col=''

7、数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。
有符号型,数字的最高位用0和1表示±,无符号位没有正负性,无符号型的大致是有符号型存储的2倍。
1字节=8位(1B=1bit)
整型
数据类型
存储范围
字节

 

TINYINT
有符号型:-128127(-2^72^7 -1),无符号型0255(02^8 -1)
1

 

SMALLINT
有符号型:-215~215 -1,无符号型0~2^16 -1
2

这都算小case。当你OUTER JOIN时,你就会痛苦一点说:col为NULL是因为JOIN不上而NULL还是它没值而NULL?

MEDIUMINT
有符号型:-223~223 -1,无符号型0~2^24 -1
3

当然,这都是大体的方针,guideline, 具体还得看应用场景。

INT
有符号型:-231~231 -1,无符号型0~2^32 -1
4

 

BIGINT
有符号型:-263~263 -1,无符号型0~2^64 -1
8

4.IP用数字存储

浮点型
数据类型
存储范围
字节

 

FLOAT[(M,D)]
-3.402823466E 38~-1.175494351E-38、0和 1.175494351E-38 ~3.402823466E 38
4

15 bytes vs. 4bytes (INET_ATON,INET_NTOA)

DOUBLE[(M,D)]
-1.7976931348623157E 308-2.2250738585072014E-308、0和2.2250738585072014E-308 1.7976931348623157E 308
8

省省省。MYSQL提供了ip转数字,和数字转IP的上述两个函数,所以,还是能省则省吧。

【注】M是数字总位数,D是小数点后面的位数。如果M和D被省略,根据硬件允许的限制来保值。单精度浮点数值精确到大约7位小数位。E 38 表示10^38

 

日期时间型
列类型
字节
存储范围

5.静态表会更快(固定长度的表)

YEAR
1
YEAR[(2|4)] 缺省为4位格式,4位格式取值范围为1901 - 2155,0000,2位格式取值范围为70-69(1970-2069)

 

TIME
3
-838:59:59~838:59:59

无VARCHAR, TEXT, BLOB可变长度的string类型的字段,则称此表为静态表。否则,为动态表。

DATE
3
1000-1-1~9999-12-31

缺点:浪费点空间 (所谓静态,就是类型设多大,它就直接分配多大的地方给你,不管你用得着用不着。)

DATETIME
8
日期时间, 1000-1-1 00:00:00~9999-12-31 23:59:59

现在静态表应该很罕见吧。设计表时,在满足需求的情况下,若能静动分离,是最好的,若不能,也就算了。静态表的好处是,查询快,因为读时,读完这条记录,它知道跳多远,能读到下一条记录。动态表则不然,因为任何一条记录的长度是动态的。

TIMESTAMP
4
时间戳,1970-1-1 00:00~2037年的某个时刻

 

【注】实际开发中“日期时间型”用得比较少,因为考虑到跨时区的问题,一般会用数字类型取代

  1. 垂直分割

MySQL本身Y2K安全的,但是呈交给MySQL的输入值可能不是。一个包含2位年份值的任何输入是由二义性的,因为世纪是未知的。这样的值必须被解释成4位形式,因为MySQL内部使用4位存储年份。
对于DATETIME, DATE, TIMESTAMP和YEAR类型,MySQL使用下列规则的解释二义性的年份值:
-在范围00-69的年值被变换到2000-2069。
-在范围70-99的年值被变换到1970-1999。

 

记得这些规则仅仅提供对于你数据的含义的合理猜测。如果MySQL使用的启发规则不产生正确的值,你应该提供无二义的包含4位年值的输入。

优点:降低表复杂度和字段数目,(如可分离静态和动态表)

字符型
列类型
存储需求

缺点:过度分割导致多JOIN,性能更低

CHAR(M)
(定长,不足用空格自动补全)M个字节,0<=M<=255

看业务,看数据量去平衡取舍。没有绝对的好坏,都得因地制宜。

VARCHAR(M)
(变长,输入多少就是多少)L 1个字节,其中L<=M,0<=M<=65535

 

TINYTEXT
L 1个字节,其中L<2^8 (1个字节表示最大存储范围,下同)

  1. 字符集选择

TEXT
L 2个字节,其中L<2^16

 

MEDIUMTEXT
L 3个字节,其中L<2^24

减少数量,而减少IO

LONGTEXT
L 4个字节,其中L<2^32

我们大部分业务,直接GBK够了。UTF8完全是一种浪费。

ENUM('value1','value2',…)
(最终选择其中的一个value)1或2个字节,取决于枚举值的个数(最多65535个)

你想啊,GBK任何一个字都只用2字节就够了。而UTF8因为支持了太多国家的语言,导致存储中文时,需要2-4byte. 所以除非可能国际化,不然还真没有必要。

SET ('value1','value2',…)
(集合,可在其中做任意的排列组合)1、2、3、4或者8个字节,取决于set成员的数目(最多64个)

 

8、数据表操作
数据表(或称:表)是数据库中最重要的组成部分之一,是其他对象的基础。
关系型数据表,是一张二维表格,所谓二维表格就具有行和列。行称为记录,列称为字段。数据表是数据的载体。

  1. 适度冗余 (空间换时间)

打开数据库
要想进行数据表相关的操作,必须要在一个数据库中进行,那么就要打开一个数据库
USE

 

图片 12

适度冗余最直接的目的,一般是为了减少JOIN。就是通过JOIN才能得到的另一张表内的字段,以冗余的形式,在当前表中再存储一遍。这样不需要JOIN了。当数据量大到一定程度时,这种做法是比较常见的。

显示被打开的数据库

 

图片 13

  1. 主键不要设得太大 (InnoDB)

创建数据表
CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,…)
column_name 列名称 data_type数据类型
数据表有多少列一定是经过项目分析得到的,不是凭空想象而来的。

 

图片 14

这句话只适用于InnoDB. 这涉及到InnoDB数据存储形式。它是以B加树的形式存储数据文件的。即,INNODB把数据文件存成跟索引文件一样了。所以,每次你读记录的时候,都要通过主键去查询。这也是为啥InnoDB中建表时,必须要有个自增长主键的原因。

【注】UNSIGNED 无符号型,最后一个列不需要再用“,”隔开

 

查看数据表列表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
查看当前数据库下的所数据列表

假设表T,字段(id, owner, title, c1,c2,c3,c4).假设你想在owner字段上建个索引,则对应的索引上会存储两个字段,一个是OWNER,另一个则是ID(主键),用于到数据文件中读取对应记录的。若你把ID设成BIGINT,那就意味着,你每建一个索引,对应地都要加上这个8byte长的字段,那你索引体积蹭地一下上去了。

图片 15

 

查看其它数据库下的数据列表(但是实际上并没有离开当前的数据库)

  1. 增长ID的重要性 (InnoDB)

图片 16

 

查看数据表结构
SHOW COLUMNS FROM tbl_name

InnoDB的数据文件本身就是索引文件,且是基于主键的索引文件。所以,这是为啥必须要有个主键的原因。你即使不设,它还是会默默地给你加个主键的。这是其一。

图片 17

其二,为是得是增长呢?需要是增长的原因是,添加新的记录时,你只需要后面append就行了,若不是按顺序增长的,则插入新记录时,它首先要找到合适的位置,然后看有没有空间给它插,若没有,得让后面的往后挪,来给它腾位置。一两条就算了,若千万个都这么干时,你说,这插入得多慢?慢不说,还给你搞得遍地是碎片,多不爽。

【注】 NULL 代表没有值(空)。

 

9、记录(行)操作
插入记录(行)
INSERT [INTO] tbl_name [(col_name,…)] VALUES(val,…)
col_name:列名称 (列名称可以省略)VALUES:值

  1. CHAR vs. VARCHAR:

图片 18

 

省略掉col_name后,就必须为所有的字段都赋值。字段不满时会提示错误“列不匹配第一行数据”;
当指定列名称后,就无需输入所有字段。

不讲编码,因为char还是varchar都会跟着编码走的。

图片 19

其实想说的是,在大分部值的长度明确且 较短时(如存储md5值),适合选择char(或更好的是binary in this case). 只有值的长度长短不一时,即较长的很长,较短的很短时,还是要选择用varchar的。

显示记录(行)
SELECT expr,… FROM tbl_name
expr 表达式

 

图片 20

char是你定义几个char,它就直接分配你几个char. 

“*”此时,表示筛选全字段,即查看所有记录

varchar(n) 的实际空间占用长度是,n char 1-2 byte.讲到空间节省,这个确实会更节省一点。

空值与非空
NULL,字段值可以为空(NULL 和空格是两码事);
NOT NULL ,字段值禁止为空
NUL 字节与 NULL 值不同;NUL 为一个零值字节,而 NULL 代表没有值(空)。

若整个表定义中,只要有一个VARCHAR,那char与varchar基本区别不大了,除非说,你定义的是char(1).

图片 21

 

(系统默认可以为空,所以NULL可以不写)

 

查看数据表结构,提示username不允许为空

QUERY最佳实践:

图片 22

 

若把name插入一个空值,系统会提示错误

  1. 不要用 “SELECT *”,否则,会读多,传输多,且增加可避免的表扫描

图片 23

 

10、约束初识(根据功能)
一般在列类型后添加约束字段
自动编号
AUTO_INCREMENT
必须与主键组合使用,默认情况下,起始值为1,每次的增量为1,从而可以保证数据的唯一性。
每次递增1,说明是数值型,可以为整数,也可以为浮点数。若为浮点数,小数位数一定要为0。

我就不废话了,基本人尽皆知的道规矩。

图片 24

 

(提示自动编号必须与主键一起使用)

  1. 不要 like ‘%item%’ but ‘item%’

主键约束(PRIMARY KEY)
每张数据表只能存在一个主键;
主键保证记录的唯一性;
主键自动为NOT NULL;
自动编号必须与主键一起使用,但是主键不一定非要与自动编号一起使用。

 

创建一个带有主键约束的数据表

前面有%,这索引就没办法利用了。所以,若想用索引加快查询速度,那前面别加%.

图片 25

 

(也可以直接输入KEY,无需写PRIMA)

  1. Cardinaltiy (基数) & Selectivity (选择比)

输入多个记录后,再查看记录,即可发现是自动编号的

 

图片 26

Cardinality: 不同值的个数。如表t中其有100条记录, 字段owner也有100条值,但其中10个不相同的值。这10就是这字段的Cardinality.

唯一约束(UNIQUE KEY)
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值(NULL);
每张数据表可以存在多个唯一约束。
【注】字段可以为空,表示两条或两条以上的记录,这个字段都可以为空。但如果多个字段为空,那么就说明记录是相同的,这与“保证记录的唯一性”是背离的,所以只能有一个空值!

Selectivity: 10/100 = 10%就是这字段的selectivity.

创建一个带有主键约束和唯一约束的数据表

这概念主要用来判断此字段是否适合建索引。Cardinality越大,Selectivity越高的字段,越是理想的建索引的对象。有时数据库会根据这个值来决定,是利用索引还是扫表。所以说,不是你建了索引,人家就会用的。而且,索引不可太多,多了反而会拖慢更新速度。

图片 27

 

“username”插入相同的数据后,提示重复,即表示记录是唯一的

  1. ORDER BY created DESC的优化

图片 28

 

默认约束
DEFAULT(默认值)
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。

时间排序是应用中比较常见的需求。细想,这时间不是自增长的嘛?那跟ID自增长不是一回事儿嘛? 所以说,在ORDER BY 时,用自增长的主键ID,会比用created,省一个FILE SORT操作。快很多的。

创建一个带有默认值的数据表

 

图片 29

  1. Count(1), count(*), count(owner)的区别

可以看到“sex”已经有了默认值

count(1)等同于count(*),等同于count(任何一个NOT NULL的字段)

图片 30

count(owner):若owner是可NULL的,则数出来的数跟上面的三种情况会少的。少的正好是那些owner is null的个数。

输入username,不输入sex的情况下,系统默认sex为3

 

图片 31

  1. Don`t JOIN ON 不同数据类型

11、约束(根据功能)
1.约束保证数据的完整性和一致性;
2.约束分为表级约束和列级约束。根据约束针对的字段多少决定的,如果约束只针对于某一字段来使用,
3.称为列级约束;如果约束针对两个或两个以上的字段来使用,称为表级约束。
约束按照类型(功能)划分:
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)

A表user_id作为B表的外键,这种很常见。此时,需注意user_id字段的类型,在两张表里都要保持一致。这样节省不必要的开支,比如,数据库替你做类型转换等。

外键约束
1.保证数据一致性,完整性;
2.实现一对一或一对多的关系;
3.外键约束的要求:
a.父表(子表参照的表)和子表(具有外键列的表)必须使用相同的存储引擎,而且禁止使用临时表;
b.数据表的存储引擎只能为INNODB;
c.外键列(加过FOREIGN KEY的列)和参照列(外键列参照的列)必须具有相似的数据类型,其中数字的长度、是否有符号位必须相同;而字符的长度可以不同;
d.外键列和参照列必须创建索引,如果外键列不存在索引的话,MySQL将自动创建索引(参照列没有索引,MySQL自动创建;外键列没有索引,MySQL不会创建)。

 

编辑数据表的默认存储引擎
MySQL配置文件
default-storage-engine=INNODB

  1. 不要用全文索引(full-text index)

创建一个数据表,默认存储引擎为InnoDB

 

图片 32

当前只有MyISAM才支持全文索引。而且,不太好用,可自定义性比较差,所以完全无视它即可。若真需要做全文索引,还是考虑用Lucene, Solr, ElasticSearch, Sphinx, Groonga, Xapian等吧。个个都是行家里手,功能齐全,可定义性强,随你搞。

再次创建一个数据表,但外键列(pid)和参照列(id)的数据类型不一致,所以提示“无法添加外键约束”foreign key constraint(外键约束)

 

图片 33

  1. Limit n,m 慢,慎用

外键列(外键:pid)和参照列(主键:id)的数据类型一致时,查看参照列数据表索引,可以查到参照列id已经有索引

 

图片 34

大部分人翻页,可能都是靠这个的。数据量大时,这显然会很慢。网上有人推荐说,第一次查出来后,记住当前页的最后一个ID,然后,在查询下一页时,把这个ID做为限制条件加进去,然后取limit pagesize。

查看外键列数据表的索引,可以看到id和pid均有索引

诸如此类,若细想,应该是能想出点儿可行之策的我觉的。其实,当数据量很大时,你可以换个角度想,如继续在limit n,m上做文章能还是直接换个查询方式,如用搜索引擎等。

图片 35

 

显示users数据表结构,可以看到

  1. 多字段索引

图片 36

 

12、外键约束的参照操作
1.CASCADE:从父表删除或更新记录时,同时自动删除或更新子表中匹配的行
2.SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
3.RESTRICT:拒绝对父表的删除或更新操作
4.NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
进行了外键约束的创建以后,在更新表时,子表是否也进行相应的操作

这个无需多说吧,道理应该是司空见惯了。

CASCADE

CREATE INDEX idx_col123 ON t (col1,col2,col3);

图片 37

用法则:

要更新相应的行,必须要先存在记录,所以要分别在两张表中插入记录。在插入记录时,一定要先插入主表(province)中的记录,在插入次表(user1)中的记录,因为次表是要参照主表的,所以一定要遵循先后顺序。

where col1='' and col2='' and col3=''

给主表插入数据

where col1='' and col2=''

图片 38

where col1=''

给子表插入数据
(为什么“id”不是“123”而是“134”——虽然('jack',7)记录没有写入成功,但是编号已经递增了1,所以编号变成了“134”。)

where col1='' and col3='' (col1时用索引,col3时一行行验证过滤的)

图片 39

你想想B Tree啥样就知道了。(mysql里应该是B Tree, 查询时,逻辑相仿,区别不大)

删除主表中的一条记录,子表相应的记录也会删除

 

图片 40

  1. 一個SELECT能否用多個索引?

只有数据表的存储引擎为INnoDB的类型时,我们才能使用外键约束,如果是其他类型我们也想使用的话,就不能定义物理外键,要定义逻辑外键(那么我们在定义两张表结构的时候,按照存在的某种结构去定义,但是不去使用“FOREIGN KEY”这个关键词)。

 

13、表级约束与列级约束(根据操作数目)
对一个数据列建立的约束,称为列级约束。
对多个数据列建立的约束,称为表级约束。
列级约束既可以在列定义时声明,也可以在列定以后声明;表级约束只能在列定义后声明。
NOT NULL ,DEFAULT就不存在表级约束

可以。Mysql高一点的版本推出了merge optimization,支持的就是这功能。

列级约束

 

图片 41

  1. JOIN vs. EXISTS 哪个更快?

14、修改数据表
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
FIRST:所添加的列放在首列;
AFTER col_name:放所添加的列在某列之后。
不加 [FIRST | AFTER col_name]所添加的列在所有列的最后面。
添加单列,所有的列不需要加入小括号,可以指定位置关系

 

图片 42

1)没有定论,主要看JOIN的表大小,和one/many – to – one/many关联关系.

图片 43

2) 需要明确的是:EXIST相比JOIN的优势在于 first match就返回,JOIN是能match的全部match.

添加多列
ALTERTABLE tbl_name ADD [COLUMN] (col_name column_definition,…)
添加单列,所有的列需要加入小括号,不可以指定位置关系,添加后就在原来列的后方

3) JOIN相对于EXIST的优势在于可以根据实际情况选择执行的顺序(join order),MySQL5.6之前,如果where中有EXISTS 执行顺序总是从外道内,现在好像变得更智能了。

删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name

4)小表JOIN大表时,用EXISTS可能更快。

图片 44

 

也可以让删除、添加列同时进行~

A a JOIN B b ON (a.id=b.aid) WHERE a.owner='aaa' and b.cat='bbb';

添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)

执行顺序如下:

图片 45

(取出A表内所有满足条件owner='aaa' 的记录)

添加唯一约束
创建的时候约束写在列名称、类型之后,添加的时候约束卸载列名称、类型之前
唯一约束可以有多个,主键约束只能有一个
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,…)

JOIN = 两个for内一一匹配

图片 46

(取出B表内所有满足条件cat='bbb'的记录)

图片 47

 

添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_defintion

A a WHERE owner='aaa' and EXISTS

图片 48

(SELECT 1 from B where cat='bbb' and a.id=aid)

添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

执行顺序如下:

图片 49

for (取出A表内所有满足条件owner='aaa' 的记录)

15、修改数据表
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY

for (取出B表内所有满足条件cat='bbb'的记录)

图片 50

check if a.id=b.aid

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name
查看索引 ,确定唯一约束名称

 

图片 51

说明一点:里面的for,若无索引,得一条条全读一遍B表的数据。若有索引,则只需读一条对应记录即可。

删除唯一约束,查看索引,已经删除

根据上面执行逻辑,外加表大小和关联关系,你可以推导出用哪个更好,再测几次,看看执行计划啥的,大体就有定论了。

图片 52

 

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
查看外键约束名称

从以下两方面讲MYSQL最佳实践: 1. 表结构最佳实践 2.QUERY最佳实践 表结构最佳实践: 1.越小通常越好 TINYINT/SMALLINT/MEDIUMINT/IN...

图片 53

删除外键约束后,可以查看已删除

图片 54

图片 55

修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

把“age”放在数据表的第一列

图片 56

修改类型
(有大类型改成小类型,可能会导致数据丢失)

图片 57

修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER] col_name

修改列名称、定义

图片 58

修改数据表名称
法①
ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
法②(可为多张数据表更名)
RENAME TABLE tbl_name TO new_tabl_name [, tbl_name2 TO new_tbl_name2] …

法①

图片 59

图片 60

法②:

图片 61

【注】以后尽量少使用数据列、数据表修改,假如表名、列名被引用的话,修改之后可能会导致某些视图或过程无法正常的工作

16、操作数据表
插入记录1(行)
INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE} ({exper | DEFAULT},…),(…),…
如果列名称(col_name)省略掉的话,所有的字段都需要赋值,每个字段之间要用逗号分隔,否则将出现错误。

为默认的自动编号的字段赋值,可以写入空值(NULL)或者默认值(DDEFAULT),仍然遵守默认递增的形式

图片 62

也可插入表达式
[图片上传中。。。(63)]

如表格中某个字段是默认值(这里是age),当该字段插入“DEFAULT”时,将自动赋值默认值

图片 63

一次性插入多条记录(md5哈希值)
[图片上传中。。。(65)]

插入记录2(set语句)
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…
与上一种的区别在于,此方法可以使用子查询(SubQuery),这个一次性只能插入一条记录

图片 64

插入记(SELECT语句)
INSERT [INTO] tbl_name [(col_name,…)] SELECT …
此方法可以将查询结果插入到指定数据表

单表更新记录(UPDATE)
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]… [WHERE whre_condition]

如果省略where条件,修改列中的所有的记录都将更新

图片 65

可以同时修改多列

图片 66

增加条件语句,只会修改对应的记录(id%2=0:表示偶数,除以2的余数为0)

图片 67

单表删除记录
DELETE FROM tbl_name [WHERE where_condition]

图片 68

删除记录,再次插入,此编号为原来所有记录编号 1

图片 69

查询表达式解析、查找记录
SELECT select_expr [,select_expr …]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC],…]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
每一个表达式表示想要的一列,必须至少有一个;
多个列之间以英文逗号分隔;
星号()表示所有列,tbl_name.可以表示命名表的所有列;
查询表达式可以使用[AS] alias_name为其赋予别名;
别名可用于GROUP BY ,ORDRE BY或HAVING子句。

只查询数据表中的部分记录

图片 70

查询表达式的顺序可以和原数据表中字段的顺序不一致
(SELECT查询表达式的顺序将影响着结果的顺序)

图片 71

在使用多表连接的时候,存在两张不同的表存在相同的字段,如果致谢字段名的话就分不清这个字段到底属于哪张数据表,tbl_name.col_name可以清晰地分辨出这个字段属于哪张数据表

图片 72

查询数据表并赋予别名
(字段的别名也会影响结果集当中的字段的别名)
[图片上传中。。。(75)]

AS可以省略,但最好写下,因为当别名和数据表中真实存在的字段一致的情况下,它指的就是别名,而不是这个字段。

图片 73

条件表达式
对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。
在WHERE表达式中,可以使用MySQL支持的函数或运算符。

17、查询结果
查询结果分组
[GROUP BY {col_name | position} [ASC | DESC],…]
分组的时候既可以指定列的列名,也可以指定列的位置。ASC 升序(默认),DESC降序。多个分组条件之间要用逗号进行分割,

按照性别对所有记录进行分组

图片 74

SELECT语句中第一个出现的字段,建议不要指定位置

图片 75

分组条件
[HAVING where_condition]
分组可以对全部记录进行分组,也可以对指定记录进行分组。在指定HAVING分组条件的时候,一定要保证分组的条件要么为一个聚合函数(只有一个返回结果的函数),要么抱着这个字段必须出现在当前的SELECT语句中,否则会出现错误
[图片上传中。。。(79)]

分组结果进行排序
[ORDER BY {col_name | expr | position} [ASC | DESC],…]
可以按某一个字段排序,也可以按位置进行排序

图片 76

也可以多个字段进行排序,如果第1个字段没有排列出自己想要的结果,那就会一次进行第2个、3个依次类推。
age升序排列(默认),相同的age记录根据id降序进行排列

图片 77

限制分组结果返回的数量
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

查询结果,显示两条记录(从第一条记录开始计数)
[图片上传中。。。(82)]

SELECT语句记录是从0开始编号,若想显示编号“3、4”则输入“2,2”

图片 78

id号和结果集中排列顺序没有任何关系,只要排在结果集中第一个位置,那么就是0(offset偏移值)。不管它ID是多少
[图片上传中。。。(84)]

INSERT SELECT 将查询的结果插入到数据表
[图片上传中。。。(85)]

18、SELECT子查询
记录的写操作:INSERT(增)、DELETE(删)、UPDATE(改)
记录的读取操作:SELECT(查)
出现乱码时要使用gbk的形式,而不是utf8的形式。

在客户端以gbk的形式显示数据,但是它不影响数据表中的数据类型,仅仅改变的显示的类型
SET NAMES gbk;

子查询概述
子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。子查询指嵌套在查询内部,切必须始终出现在圆括号内。子查询可以包含多个关键字或条件,如:
DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等
子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO

示例:
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1,称为Out Query/Out Statement (外层查询/外层声明)
SELECT col2 FROM t2,称为Subquery
在外层查询中可以发现,其中的增、删、改、查都可以带有子查询,这里的查询指的是已知的所有SQL命令的统称

子查询的结果可以是标量、一行、一列或子查询,结果可以被其他SQL语句所使用

使用比较运算符的子查询
使用比较运算符
=、>、<、>=、<=、<>、!=、<=>
语法结构
operand comparison_operator subquery

SELECT AVG (col_name) FROM tbl_name;
查找数据表中某列的平均值
[图片上传中。。。(86)]

对平均值四舍五入,并保留小数点后的2位
[图片上传中。。。(87)]

查询本表中哪些商品的价格超过5636.36
SELECT goods_id,goods_name,goods_price FROM tdb_goods FROM tdb_goods WHERE goods_price >= 5636.36;
通过子查询
SELECT goods_id,goods_name,goods_price FROM tdb_goods FROM tdb_goods WHERE goods_price >= (SELECT ROUND (AVG(goods_price),2) FROM tdb_goods);

查询某个数据的一个属性
查询所有超极本的数量
SELECT goods_price FROM tdb_goods WHERE goods_cate ='超极本'
[图片上传中。。。(88)]

用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY (subquery) 满足其中一个就可以
operand comparison_operator SOME (subquery) 满足其中一个就可以
operand comparison_operator ALL (subquery) 满足所有才可以
使用ANY、SOME、ALL关键字的返回值

ANY
SOME
ALL

、>=
最小值
最小值
最大值

<、<=
最大值
最大值
最小值

=
任意值
任意值

<>、!=

任意值

查询哪些商品的的价格超过超极本的价格
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超极本');

[NOT] IN 子查询
operand comparison_operator [NOT] IN (subquery)
=ANY运算符与IN等效;
!=ALL或<>ALL运符与NOT IN等效

[NOT] EXISTS子查询
如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE。

将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,…)] SELECT …

(查询列结构 DESC tbl_name)
示例~
查询结果:
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
将查询结果写入数据表:
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

19、多表更新
参照另外的表更新本标的记录
UPDATE table_references SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]…[WHEREwhere_condition]

语法结构
table_reference
{[INSERT | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_REFERENCE
ON conditional_expr
连接类型
INNER JOIN,内连接
在MySQL中,JOIN、CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外链接

示例:
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate =cate_name SET goods_cate = cate_id;

CREATE…SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] select_statement

[图片上传中。。。(89)]

当两个数据表格都有相同的列时,更新此列的数据要给表格设置别名
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id
但是原来的数据表结构无法改变

[图片上传中。。。(90)]

20、连接
MySQL在SELECT语句、多表更新、夺标删除语句中支持JOIN操作。

table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} tabl_reference ON conditional_expr
两张表之间的连接,连接类型有:内连接、左外连接、有外链接,ON 后面跟的是连接条件

数据表参照
table_reference tbl_name [ [AS] alias ] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name 赋予别名,table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。

连接条件
使用ON关键字来设定连接条件,页可以使用WHERE来代替,通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。

连接类型
内连接
INNER JOIN,内连接(在MySQL中,JOIN,CROSS JOIN 和 INNER JOIN 是等价的)
仅显示同时符合左、右两表之间连接条件的记录
示例:
SELECT goods_id,goods_name,cate_name FROm tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

左外连接
LEFT [OUTER] JOIN ,左外连接
显示坐标全部的记录及右表符合连接条件的记录
示例:
SELECT goods_id,goods_name,cate_name FROm tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

右外连接
RIGHT [OUTER] JOIN,右外连接
显示坐标全部的记录及左表符合连接条件的记录
示例:
SELECT goods_id,goods_name,cate_name FROm tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

多表连接
示例:
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

连接说明
外连接
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A;
数据表A的结果集根据左链接条件依赖所有数据表

20、无限级分类表设计
无限级分类一般包括:分类的ID、分类的名称、父类的ID
查找无限分类的数据表就需要进行自身连接
示例,一张无限分类的数据表
[图片上传中。。。(91)]

自身连接
同一个数据表对其自身进行连接
【注】若一字表做自身连接一定要给表取一个别名
示例:参照子表 (左边是父表,右边是字表子表)
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

[图片上传中。。。(92)]
示例:参照父表 (左边是子表,右边是字表父表)
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;

[图片上传中。。。(93)]

示例:参照子表并分组按照顺序排列
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
[图片上传中。。。(94)]

显示子类数量
SELECT p.type_id,p.type_name,count (s.type_name)child_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
[图片上传中。。。(95)]
多表删除
DELETE tbl_name[.] [,tbl_name[.]]… FROM table_references [WHERE where_condition]
要先查找
【查找具有两条及两条以上记录的数据:
DELETE tbl_name[.] [,tbl_name[.]]… FROM table_references GROUP BY tbl_name[.*] HAVING count ()】having引用

本文由www.bifa365365.com发布于数据库,转载请注明出处:MySQL最好实行

关键词: www.bifa3653