MYSQL 总结——2

1、mysql限制显示条目数：Limit, Offset

图片网址：https://sqlbolt.com/lesson/filtering_sorting_query_results

实例：
SELECT * FROM movies order by id limit 1 offset 2 ;
SELECT * FROM movies order by id limit 2,1 ;
上面两个语句的效果一样，区别：
如果省略offset关键字，那么limit后面的2个参数中，第一个参数起到offset的作用，为开始位置，第二个参数限制查询显示的条目数。

2、字符串匹配时，最好使用Like。
like模糊匹配，不区分大小写，比较通用。
等于号（=），精准匹配，区分大小写。
区别：如果能保证需要匹配的字符串、大小写等格式完全无误，那么Like和 = 的效果一样，但如果不确定大小写之类的，使用like比较保险。

3、SQL约束

约束可以在创建表时规定（create table语句），或者在表创建之后规定（ALTER TABLE语句）。

CREATE TABLE +CONSTRAINT语法

约束的种类：

NOT NULL：某列不能有空值。

UNIQUE：某列的每行必须有唯一值。可以有空值。

PRIMARY KEY：NOT NULL和UNIQUE的结合。不能为空，唯一值。

FOREIGN KEY

CHECK：保证列中的值符合指定的条件。

DEFAULT：设置默认值。

4、索引

优点：可以大大提高MySQL的检索、查询速度。

缺点：降低更新表的速度，如对表进行Insert、Update和Delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

索引类型：
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引：即一个索引包含多个列。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

说明：在RDBMS中索引一般采用B+树、HASH索引来实现。B+树索引具有动态平衡的优点，HASH索引具有查找速度快的特点。索引是关系数据库的内部实现技术，属于内模式的范畴。

用户使用CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引。至于某一个索引是采用B+树，还是HASH索引则由具体的RDBMS来决定。

普通索引

创建索引的方式
a. 创建索引：Create INDEX indexName on Table(列名1，列名2，列名3……)
b. 修改表结构：Alter table tablename Add Index indexName(列名1，列名2，列名3……)
c. 创建表时指定索引：
Create table tablename(
Id int not null,
Username varchar(16) not null,
INDEX [indexname] (列名1，列名2，列名3……) 注释：indexname用中括号，说明创建索引时可以命名，也可以不命名

);

唯一索引

与普通索引类似，不同的是：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
a. 创建索引：Create UNIQUE INDEX indexName ON TABLE(列名1，列名2，列名3……)
b. 修改表结构：ALTER TABLE tablename ADD UNIQUE [indexname] (列名1，列名2，列名3……)
c. 创建表时指定索引：
Create TABLE tablename(
Id int not null,
Username varchar(16) not null,
[Constraint 索引名] UNIQUE [indexname] (列名1，列名2，列名3……)
);

删除索引（普通索引和唯一索引都适用）：
ALTER TABLE tablename DROP INDEX indexname;

显示数据表中相关的索引信息：SHOW INDEX from tablename;

5、主键PRIMARY KEY
使用Alter命令添加和删除主键
a. 添加主键
需要确保添加主键的列不能为空。
ALTER TABLE tablename Add Primary key (field_name);
b. 删除主键
Alter table tablename Drop Primary key；

6、外键 Foreign Key

包含外键的表是从表，做主键的表是主表。

foreign key约束的作用：

foreign key能防止非法数据插入外键列，因为它必须指向目标关系中的值。

创建外键的方式：

a. 创建表时（这个表是指从表，因为外键是在从表中存在）

语法格式：

CREATE TABLE 表名(

column1 datatype null/ not null,

column2 datatype null/not null,

……

CONSTRAINT 外键约束名 foreign key (column1, column2, column3, ……)

references 外键依赖的表(column1, column2, column3, ……)

ON DELETE CASCADE, ——级联删除

);

示例：

create table orders(

O_id int not null,

name string,

C_id int,

foreign key (C_id) references customer(C_id)

);

b. 修改表时

语法格式：

ALTER TABLE 表名 ADD FOREIGN KEY (column1, column2, column3, ……) references 外键依赖的表(column1, column2, column3, ……)

ON DELETE CASCADE ; ——级联删除

示例： ALTER TABLE tablename Add foreign key (C_id) references customer(C_id);

7、CHECK

CHECK语句在声明中没有起作用的原因：

不同于SQL，MYSQL中CHECK只是一段可调用但无意义的子句，CHECK子句会被分析，但MYSQL会直接忽略。

参见“CREATE TABLE”语法：接受这些子句但又忽略子句的原因是为了提高兼容性，以便更容易地从其他SQL服务器中导入代码，并运行程序，创建带参考数据的表。

参考网址：https://blog.csdn.net/qq_39521554/article/details/78681681

8、DEFAULT 默认值

撤销Default：

ALTER TABLE tablename ALTER columnname DROP DEFAULT;

9、视图 VIEW

视图是从一个或几个基本表（视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

a. 创建视图

CREATE VIEW 视图名（列名1，列名2，列名3……）

AS 子查询

[WITH CHECK OPTION]

说明：（1）子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。

（2）WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

（3）组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则默认该视图的各字段由子查询中SELECT子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名：

（3.1）某个目标列不是单纯的属性名，而是SELECT语句中使用聚集函数和列表达式生成的；

（3.2） SELECT语句中多表连接时使用了几个同名列作为视图的字段；

（3.3）需要在视图中为某个列启用新的更合适的名字。

示例：

（3.1）CREATE VIEW S_G(sno, Gavg)

SELECT sno, avg( grade)

FROM SC

GROUP BY sno;

（3.2）CREATE VIEW IS_S1( sno, sname, grade)

SELECT student, sno, sname, grade

FROM student, sc

WHERE sdept ='IS' and student.sno= sc.sno and sc.sno='1';

(3.3) CREATE VIEW BT_S(sno, sname, sbirth)

SELECT sno, sname, 2004- sage

FROM student;

（4）RDBMS执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中景数据查出。

b. 删除视图 DROP

格式： DROP VIEW 视图名 [CASCADE];

视图删除后视图的定义将从数据字典中删除。如果视图上还导出了其他视图，则使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除。

基本表删除后，即使由该表导出的所有视图（定义）没有被删除，但均已无法使用了。

示例：

删除视图： IS_S1：DROP VIEW IS_S1；

执行此语句时由于IS_S1视图上还导出了IS_S2视图，所以该语句被拒绝执行。如果确实要删除，则使用级联删除语句：

DROP VIEW IS_S1 CASCADE; /* 删除了视图IS_S1和由它导出的所有视图 */

c. 查询视图

RDBMS执行对视图的查询时，首先进行有效性检查。检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解。

d. 视图最好的作用：视图能够简化用户的操作

视图机制使用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作。例如，那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐蔽起来了。换句话说，用户所做的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。

6、全文索引 FULLTEXT

使用Alter命令添加全文索引
Alter table tablename Add fulltext indexname(field_name);
该语句指定了索引为Fulltext，用于全文索引。

MySQL临时表
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

使用show tables命令不会显示临时表。

创建临时表
CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL，
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00，
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00，
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
删除临时表
默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。也可以在当前MySQL会话使用drop table命令来手动删除临时表。
Drop table salessummary;

Mysql复制表

MySQL序列

Auto_Increment自增
问题：如何获取最后插入表中的自增列的值

重置序列
如果删除数据表中的记录，需要重新排列。
方式：先删除自增的列，再重新添加auto_increment和主键primary key

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, 这里的first是指添加到第一列吗
-> ADD PRIMARY KEY (id);

设置序列的开始值
一般情况下序列开始值为1， auto_increment=100
格式：
Alter table tablename Auto_increment=100;

Mysql重复数据

主键和索引
Insert ignore into
Replace into

过滤重复数据：distinct, group by

删除重复数据2种方式：
1、mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

注意：这里不是创建临时表，前面没有ｔｅｍｐｏｒａｒｙ字段

２、当然你也可以在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);

　Alter　ignore　table中的ｉｇｎｏｒｅ是什么

菜鸟教程，SQL，不是MYSQL
http://www.runoob.com/sql/sql-foreignkey.html
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

Mysql中定义主键，必须在表中字段都定义完成后，另外说明主键

晚上：练习外键、主键、UNIQUE
这三者之间的关系 http://www.runoob.com/sql/sql-constraints.html
Default:
创建表时设置default，不需要加set关键字， default getdate()
修改表时设置default，需要添加set关键字
Set default ‘100’
删除default：alter table persons alter city drop default

Check：
对一列进行check约束：check(id>0)
对多列进行check约束：check(id>0 and city=’beijing’)

主键、外键、unique、check、default中可以使用constraint关键字，但在mysql中删除时，不会使用drop constraint这样的语句

UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
总结：UNIQUE可以为空，而PRIMARY key不可以为空。

删除主键：drop primary key，后面不加任何东西
删除外键：如果外键有命名，则drop primary key 外键名

创建、更新和删除视图 view

优化/存储过程/授权grant

外键：
只要看到一个外键，就总是能看到与之相关的参照完整性约束。
外键的值必须在主键表中存在对应项，这个规则称为完整性约束。

添加权限

posted @ 2018-12-29 14:30 limalove 阅读(236) 评论(0) 编辑收藏举报

limalove

MYSQL 总结——2

公告