mysql数据库（常见知识总结）

mysql

MySQL/Orcle/SQL Server
数据库，对数据的存储，持久化存储（磁盘）/效率 内存会丢失

如果把数据库比作一个文件柜的话，创建数据库是指在数据库系统这个房间中申请一个空间并放置一个文件柜。创建数据库是进行表操作的基础，也是进行数据库管理的基础。

创建数据库

CREATE DATABASE 数据库名;

显示数据库

SHOW DATABASES;

删除数据库

DROP DATABASE 数据库名；

创建表

CREATE TABLE 表名(属性名 数据类型 [完整性约束条件],  

索引

是帮助MySQL高效获取数据的数据结构。
优点
提高数据检索效率，降低数据库的IO成本
降低数据排序的成本，降低了CPU的消耗
缺点
索引也是一张表，保存了主键和索引字段，指向实体表的记录，索引列也要占空间
索引虽然提高了查询速度，降低了更新表的速度。
更新时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段。
都会调整因为更新所带来的键值变化后的信息。
建立索引会占用磁盘空间的索引文件。

索引的分类
普通索引（INDEX）
最基本索引，没有任何限制

ALTER TABLE 'table_name'ADD INDEX index_name('col')

唯一索引（UNIQUE）
索引列的值必须唯一，允许有空值

ALTER TABLE 'table_name'ADD UNIQUE('col')

主键索引（PRIMARY KEY）
特殊的唯一索引，不允许有空值

ALTER TABLE 'table_name'ADD PRIMARY KEY('col')

全文索引（FULLTEXT）
仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间。

ALTER TABLE 'table_name'ADD FULLTEXT('col')

组合索引
更多的提高mysql效率

ALTER TABLE 'table_name'ADD INDEX
index_name('col1','col2','col3')

哪些情况需要创建索引？
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引。
4.频繁更新的字段不适合建立索引，因为每次记录不单单是更新了记录还会更新索引
5.WHERE条件里用不到的字段不创建索引。
6.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7.查询中排序的字段
8. 查询中统计或者分组字段

索引使用注意事项
1.不要滥用索引
2.索引中不会包含含有NULL值的列
3.MySQL查询只是用一个索引
4.like
like’%aaa%'不会使用索引like"aaa%"可以使用索引
SQL最基本的数据类型是：
字符串类型；
定长字符串和变长字符串
在定长数据类型里，通常使用空格来填充数量不足的字符
变长字符串也就是长度不固定的字符串。
用VARCHAR(n)来保存不定长度数据
数值类型
一般包括NUMBER、INTEGER、REAL、DECIMAL
日期和时间类型；
用于保存日期和时间信息的
DATE、TIME、DATETIME、TIMESTAMP。
DATETIME数据类型的元素包括：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。

完整性约束条件
对字段进行限制。要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不执行用户的操作。

PRIMARY KEY 标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY 标识改属性为该表的外键，是与之联系的某表的主键
NOT NULL 标识该属性不能为空
UNIQUE 标识该属性的值是唯一的
AUTO_INCREMENT 标识该属性的值自动增加，这是MySQL的SQL语句的特色
DEFAULT 为该属性设置默认值

设置主键

属性名 数据类型 PRIMARY KEY    设置主键

设置外键的原则就是必须依赖于数据库中已存在的父表的主键；外键可以为空值。外键的作用是建立该表与其父表的关联关系。父表中删除某条信息时，子表中与之对应的信息也必须有相应的改变。
设置外键

CONSTRAINT 外键别名 FOREIGN KEY(属性 1.1,属性1.2,…,属性 1.n)   REFERENCES 表名(属性 2.1, 属性 2.2,…,属性 2.n)

设置表的属性值自动增加

属性名 数据类型 AUTO_INCREMENT

一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。
AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLINT、INT和BIGINT等)。默认情况下，该字段的值是从1开始自增。
如果插入一条新的记录时没有为这个字段赋值，那么数据库系统会自动为这个字段插入默认值。

属性名 数据类型 DEFAULT 默认值

查看表结构的语句包括DESCRIBE语句和SHOW CREATE TABLE语句。通过这两个语句，可以查看表的字段名、字段的数据类型和完整性约束条件等。
DESCRIBE语句可以查看表的基本定义。其中包括，字段名、字段数据类型、是否为主键和默认值等，（DESCIBE可以缩写成DESC）

DESCRIBE 表名;

查看表详细结构语句

SHOW CREATE TABLE  表名;

通过sql语句ALTER TABLE来实现表名的修改

ALTER TABLE 旧表名 RENAME [TO] 新表名;

ALTER TABLE语句也可以修改字段的数据类型

ALTER TABLE 表名 MODIFY 属性名 数据类型;

ALTER TABLE语句也可以修改表的字段名

ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

如果要增加新的字段，可以通过ALTER TABLE语句进行增加

ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

ALTER TABLE语句也可以删除表中的字段

ALTER TABLE 表名 DROP 属性名

删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名：

删除没有被关联的普通表

DROP TABLE 表名

删除被其他表关联的父表
1，最简单最直接的办法是，先删除子表，然后在删除父表。但是这样子可能会影响到子表的其他数据
2，另一种办法就是先删除子表的外键约束，然后再删除父表。这种办法，不会影响到子表的其他数据，可以保证数据库的安全。

INSERT INTO 表名 VALUES(值1,值2,…,值n);

INSERT INTO 表名(属性1,属性2,…,属性n) VALUES (值1,值2,… ,值n);

同时插入多条记录

INSERT INTO 表名 [(属性列表)] VALUES (取值列表1), (取值列表2),…, (取值列表n);

将查询结果插入到表中

INSERT INTO 表名1 (属性列表1) SELECT 属性列表2 FROM 表名2 WHERE 条件表达式;

更新数据

UPDATE 表名 SET 属性名1=取值1,属性名2=取值2,…,属性名n=取值n WHERE 条件表达式;

删除数据

DELETE FROM 表名 [WHERE 条件表达式];

SELECT 属性列表
       FROM 表名和视图列表
       [WHERE 条件表达式1]
       [GROUP BY属性名1[HAVING 条件表达式2]]
       [ORDER BY 属性名2[ASC|DESC]]

IN关键字可以判断某个字段的值是否在指定的集合中，如果字段的值在集合中，则满足查询条件，该记录将被查询出来；

[NOT] IN(元素1,元素2,…,元素n)

排序方式由ASC和DESC两个参数指出；ASC参数表示按升序的顺序进行排序，这是默认参数；DESC参数表示按降序进行排序。

使用“*”查询所有字段

SELECT * FROM 表名

[NOT] IN(元素1,元素2,…,元素n)

[NOT] BETWEEN 取值1 AND 取值2

LIKE关键字可以匹配字符串是否相等

[NOT] LIKE ‘字符串’

IS NULL关键字可以用来判断字段的值是否为空值。
当查询语句需要对多个条件进行过滤时，可以使用AND、OR将多个条件进行连接。
可以使用ORDER BY关键字对记录进行排序。

SELECT * FROM TABLE_NAME
[WHERE 条件]
ORDER BY 字段1  [ASC/DESC] [,字段2  [ASC/DESC]…] 

LIMIT关键字就是用来指定查询结果从哪条记录开始显示，还可以指定一共显示多少条记录。
其中初始位置用来设置从查询结果的第几条开始显示，初始位置由0开始，默认情况下为0。记录数表示要显示的记录的条数。

SELECT * FROM TABLE_NAME
LIMIT [初始位置], 记录数

聚合函数
SUM 求和

SELECT SUM(COLUMN) FROM TABLE_NAME [WHERE …]

AVG 求平均值

SELECT AVG(COLUMN) FROM TABLE_NAME [WHERE …]

MAX和MIN 求最大值/最小值

SELECT MAX(COLUMN) FROM TABLE_NAME [WHERE …] 
SELECT MIN(COLUMN) FROM TABLE_NAME [WHERE …]

COUNT 计数

SELECT COUNT(COLUMN/*) FROM TABLE_NAME [WHERE …]

分组统计是使用GROUP BY关键字将查询结果按照某个字段或多个字段进行分组。

SELECT   <字段列表>   [聚合函数] 
FROM  表
[WHERE]  条件
GROUP BY  <字段列表>

使用HAVING对分组过滤

SELECT   <字段列表>   [聚合函数] 
FROM  表
[WHERE]  条件
GROUP BY  <字段列表>
HAVING 条件

交叉连接会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

(1)
SELECT * FROM TABLE_A CROSS JOIN TABLE_B 
[WHERE 条件]
(2)
SELECT * FROM TABLE_A JOIN TABLE_B
[WHERE 条件]
(3)
SELECT * FROM TABLE_A, TABLE_B
[WHERE 条件]

内连接

SELECT * FROM TABLE_A INNER JOIN TABLE_B 
ON 连接条件
[WHERE 条件]

通常情况下使用交叉连接都要配合WHERE对连接后的迪卡尔积进行过滤，而内连接可以使用ON设置连接条件，在对数据进行拼接时，根据连接条件决定如何将两个表或多个表的数据进行关联，省去了无用记录的关联拼接。因此内连接的效率要比交叉连接高。
但是实际上在MySQL中除了使用“,”外，CROSS JOIN和JION进行交叉连接时也可以使用ON设置连接条件，INNER JOIN也可以不写ON连接条件。因此CROSS JOIN、JOIN和INNER JOIN并没有明显的区别。

外连接与内连接不同，内连接时，根据连接条件只保留两个表中有对应数据的记录；而外连接时，当一个表中记录在另一个表中没有对应记录时，会生成一条与NULL值对应的记录。

外连接根据数据保留表的不同，又分为左外连接和右外连接。左外连接时，保留左表中的所有数据，右外连接时，保留右表的所有数据。

左外连接使用LEFT JOIN连接两表，连接时左表为主表，左表中的每条记录必定出现在结果集中，而在右表中没有对应的记录，将以NULL值进行填充。
可以使用IFNULL语句对NULL值记录进行默认值填充

SELECT * FROM TABLE_A LEFT [OUTER] JOIN TABLE_B 
ON 连接条件
[WHERE 条件]

右外连接

SELECT * FROM TABLE_A RIGHT [OUTER] JOIN TABLE_B 
ON 连接条件
[WHERE 条件]

全连接
完全连接左表和右表中所有行，当某行数据在另一个表中没有匹配时，则另一个表的选择列值为NULL。MySQL不支持全外连接。

SELECT * FROM TABLE_A FULL [OUTER] JOIN TABLE_B 
[ON 条件]
[WHERE 条件]

AS-表和字段的别名

SELECT COLUMN AS ALIAS_COLNAME 
FROM TABLE_A AS ALIAS_A, TABLE_B AS ALIAS_B
WHERE ALIAS _A.COLUMN = ALIAS _B.COLUMN

多表查询步骤总结：
1)列出需要查询的所有字段
2)根据字段找出查询所涉及的所有表
3)确定表关联时的连接条件
4)确定过滤条件

当一个查询需要用到另一个查询的结果时，在查询语句中可以出现多层嵌套查询。这种查询语句叫做子查询。

使用子查询时，可以利用比较运算符>、>=、<、<=、=、!=、<>、!>、!<进行条件过滤。带有比较运算符的子查询中，子查询的结果必须是一个单值，否则报错。父查询中的查询表达式通过比较运算符与这个单值进行比较。若比较的结果为TRUE,则列出符合条件的记录，若比较的结果为FALSE,查询结果将是空信息。

“%”可以代表任意长度的字符串，长度可以为0。
“_”只能表示单个字符。

查询条件 符合或关键字
比较 =、<、<=、>、>=、!=、<>、!>、!<
指定范围 BETWEEN AND、NOT BETWEEN AND
指定集合 IN、NOT IN
匹配字符 LIKE、NOT LIKE

是否为空值 IS NULL、IS NOT NULL
多个查询条件 AND、OR

1，CHANGE可以在改变字段数据类型的同时，改变字段名。MODIFY只能用来改变字段的数据类型，不能修改字段名。
2，CHANGE如果不改变字段名，只修改字段类型，CHAGE后面必须跟两个同样的字段名。
3，养成良好的习惯，如果是指修改字段的数据结构就使用MODIFY，如果要修改字段名+数据结构就使用CHANGE。
4，在修改表的字段过程中，如果表中已经有记录，修改数据类型时应该特别小心。因为，修改数据类型时可能会影响表中的数据。
5，特别注意的是，字符类型的字段最好不要改成整数类型，浮点数类型。

SQL语言分类？
DDL,数据定义语言（结构）
DML,数据操作语言（数据）
DCL，数据控制语言（权限）

char，vachar？
char长度固定，varchar长度可变
char存储速度快。
char所占用的长度和定义长度一致，vachar类型和真实长度一致
char英文字符占一个字节，汉字字符占两个字节，vachar，英文和汉字都占两个字节

常见约束？
1.主键约束
2. 默认值约束
3.唯一约束
4.外键约束
5.非空约束
属性名 数据类型 NOT NULL
非空性指字段的值不能为空值（NULL）。非空约束将保证所有记录中该字段都有值。如果用户新插入的记录中，该字段为空值，则数据库系统会报错。

三大范式？
1NF：保证每列的原子性
2NF:保证一张表只描述一件事情
3NF（BDCF）：保证每列都和主键直接相关

常见存储引擎？
MyISAM
innoDB
MEMORY
ARCHIVE

事务的特性？
1.原子性，同时成功或失败，不可分割
2.一致性，前后数据一致
3.持久性，一旦提交，永久保存
4.隔离性，操作过程不可见

事务的隔离级别？
1.未授权读取
2.授权读取
3.可重复读取（默认隔离方式）
4.序列化

事务传播
require（默认传播方式）
supports

多态类型?
编译静态 方法重载overload同一个类中，多个同类不同名的不同函数，增加可读性，返回值类型不作为区分标准
运行动态

访问修饰符？
forname
.class
类的对象调用
类的加载器
默认级别，可重复读

什么是脏读？
1.脏读：读取了未完成更新运行错误的数据
2.不可重复读：多次读取数据结果不一致
3.幻读：数据被改变，不了解，以为出现幻觉

mysql的端口号？
默认3306

什么是触发器？
嵌入到MySQL的一段程序，可以对表进行增删改操作同时响应另一张表做出相同的操作。

索引的优点？
1.加快查询速度
2.减少了服务器需要扫描的数据量
3.帮助服务器避免排队和临时表
4.将随机IO变成顺序IO

索引的分类?
fulltext全文索引
hash
btree
rtree
普通索引
唯一索引
主键索引
全文索引

内连接和外连接的区别?
内连接只输出匹配的数据
外连接：左向外联接（左表为主表），右向外连接（右表）

视图的好处？
简单，安全，逻辑数据独立性

Delete和drop区别？
drop一般用于删除整体数据
delete删除部分数据

子查询?
一个查询是另一个查询的条件

使用什么关键字实现分组？
SQL数据库中的聚合函数，常与分组函数连用于分组查询
group by 分组
where，having过滤

事务隔离级别（五个）
1.default（缺省的）默认值
2.read uncommitted（一个事务可以读取另一个修改了还没提交的事务）
3.read committed（一个事务可以读取另一个提交的事务）
4.repeatable read（一个事务可以多次重复执行某个查询，每次返回记录都相同）
5.serializable（所有的事务依次逐个执行）

事务传播
默认required
supports（如果当前有事务，则加入事务，没有事务，则以非事务的方式运行）

左外连接和右外连接的区别?
左外连接是以左表为基准，右表若没有数据则显示为空
左连接的结果包括left子句中指定的所有行
右连接是左向外连接的反向链接

交叉连接？
不带where子句，返回被连接的两个表所有数据行的笛卡尔积
第一个表中符合条件的数据行数成第二个表中符合查询条件的数据行中

说一下为什么尽量使用select*查询？
不应该，亢余信息消耗网络和数据库负载

LeftJion和RightJion查询的异同点？（左查询和右查询的异同点）
左连接where只影向右表，右连接where只影响左表。
Left Join select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID 左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。 简言之 Left Join影响到的是右边的表
Right Join select * from tbl1 Right Jointbl2 where tbl1.ID = tbl2.ID 检索结果是tbl2的所有数据和tbl1中满足where 条件的数据。简言之 Right Join影响到的是左边的表。

其他查询
inner join select * FROM tbl1 INNER JOINtbl2 ON tbl1.ID = tbl2.ID 功能和 select * fromtbl1,tbl2 where tbl1.id=tbl2.id相同。
1 .WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

2 .无论怎么连接，都可以用join子句，但是连接同一个表的时候，注意要定义别名，否则产生错误！
a> inner join：理解为“有效连接”，两张表中都有的数据才会显示left join：理解为“有左显示”，比如on a.field=b.field，则显示a表中存在的全部数据及a//b中都有的数据，A中有、B没有的数据以null显示 b> right join：理解为“有右显示”，比如on a.field=b.field，则显示B表中存在的全部数据及a//b中都有的数据，B中有、A没有的数据以null显示c> full join：理解为“全连接”，两张表中所有数据都显示，实际就是inner +(left-inner)+(right-inner)

3 .join可以分主次表 外联接有三种类型：完全外联，左联，右联． 完全外联包含两张表的所有记录． 左联是以左边的表为主，右边的为辅，右联则相反

4.一般要使得数据库查询语句性能好点遵循一下原则: 在做表与表的连接查询时，大表在前，小表在后，不使用表别名，通过字段前缀区分不同表中的字段，查询条件中的限制条件要写在表连接条件前，尽量使用索引的字段做为查询条件

UNOIN
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

快捷键
sf select * from
w where
sc select count(*) from
df delete from
port c_port_code
sec c_sec_code