mysql知识点梳理 mysql重点知识

 

知识点总结：

1.创建数据库：

create database_name;

2.删除数据库：

drop database_name;

3.选择/切换数据库

use database_name;

4.创建数据表：

create table table_name(column_name column_type);

例：

create table if not exists table1(
id int auto_increment primary key,
name varchar(20) not null，
password varchar(40) not null)enging=InnoDB default charset=utf8;

注：MySQL命令终止符为分号（;）。

附：存储引擎（enging）：
　　mysql中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果在研究大量的临时数据，也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库（以确保事务处理不成功时数据的回退能力）。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎（也称作表类型）。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据表和表格的存储引擎，以便在选择如何存储你的信息，如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供的最大的灵活性。 

分类：
　　MyISAM：拥有较高的插入，查询速度，但不支持事务；
　　InnoDB：5.5版本后MySQL的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定；
　　BDB：源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性；
　　Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MySQL重启时丢失；
　　Merge：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用；
　　Archive：非常适合存储大量的独立的，作为历史纪录的数据。因为他们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差；
　　Federated：将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用；
　　Cluster/NDB：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用；
　　CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引；
　　BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继。
5.删除数据表：

drop table table_name;

注：执行删除命令后所有数据都会消失，所以要非常小心。

附：

　　删除表内数据，用delete：delete from 表名 where 删除条件；
　　清除表内数据，保存表结构，用truncate：truncate table 表名；
　　删除表用drop：drop table 表名；
　　1.当你不再需要该表时，用drop；
　　2.当你仍要保留该表，但要删除所有记录时，用truncate；
　　3.当你要删除部分记录时，用delete。
6.插入数据：

insert into table_name(field1,field2,...fieldN)values(value1,value2,...valueN);

注：如果数据是字符型，必须使用单引号或者双引号。

例：
插入单条数据：

insert into table_name(name,password,date)values("kwonjiyong","peaceminusone",now());

插入多条数据：

insert into table_name(name,password,date)values("kwonjiyong","peaceminusone",now()),（"zhangqi","xxxidaizy",now()）...; //now()是一个mysql函数，该函数返回日期和时间。

其中id如果设置了auto_increment（自动增加）可以不提供，也可以根据需要自己设置；

7.查询数据：

select * from table_name;//查询数据表中的所有数据

select column_name,column_name from table_name [where 条件][limit N][offset M];

注：
　　1.查询时可以使用一个或者多个表，表之间使用( ,)分割，并使用where语句来设定查询条件；
　　2.select命令可以读取一条或者多条记录；
　　3.可以使用（*）来代替其他字段，select语句会返回表的所有字段数据；
　　4.可以使用where语句来包含任何条件；
　　5.可以使用limit属性来设定返回的记录数；
　　6.可以通过offset指定select语句开始查询的数据偏移量，默认情况下，偏移量为0。

附：limit和offset用法：

select * from table limit 2,1; //含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

select * from table limit 2 offset 1; //含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

8.where子句：

　　1.可以在 where 子句中指定任何条件；
　　2.可以使用 and 或者 or 指定一个或多个条件；
　　3.where 子句也可以运用于SQL的 delete 或者 update 命令；
　　4.where 子句类似于程序语言中的 if 条件，根据mysql表中的字段值来读取指定的数据；
　　5.操作符 （ =，<>，!=，>，<，>=，<= ）可用于where子句中；
　　6.mysql的 where 子句的字符串比较是不区分大小写的。可以使用binary关键字来设定where子句的字符串比较是区分大小写的，例：

select * from 表名 where name='Aaa'; //不区分大小写
 select * from 表名 where binary name='Aaa'; //区分大小写

9.update查询：
使用update命令修改mysql数据表数据：

update 表名 set field1=new_value1,field2=new_value2 [where 条件];

当需要字段中的特定字符串批量修改为其他字符串时，可使用以下操作：

update table_name set field =replace(field,'old_string','new_string')[where 条件];

注：
　　1.可以同时更新一个或多个字段；
　　2.可以在where子句中指定任何条件；
　　3.可以在一个单独表中同时更新数据。
10.delete语句：

delete from table_name [where 条件];

delete from table_name;//删除表中的所有数据

注：
　　1.如果没有指定where子句，mysql表中的所有记录将被删除；
　　2.可以在where子句中指定任何条件；
　　3.可以在单表中一次性删除记录。
11.like子句：

select * from table_name where field1 like condition1;

注：

　　1.可以在where子句中指定任何条件；
　　2.可以在where子句中使用like子句；
　　3.可以使用like子句代替等号 = ；
　　4.like通常与 % 一起使用；
　　5.可以使用 and 或者 or 指定一个或多个条件；
　　6.可以在delete或update命令中使用where...like 子句来指定条件。

附：
　　1.SQL like子句中使用百分号 % 字符来表示任意字符；
　　2.如果没有使用百分号 % ，like子句与等号 = 的效果是一样的。
12.union：

select expression1,expression2,...expressionN from table1_name [where conditions]union [all|distinct]
select expression1,expression2,...expressionN from table2_name [where conditions];

参数：
　　distinct：可选，删除结果集中重复的数据。默认情况下union操作符已经删除了重复数据，所以distinct修饰符对结果没有影响。
　　all：可选，返回所有结果集，包含重复数据。
注：
　　1.union操作符用于连接两个以上的select语句的结果组合到一个结果集中。多个select语句会删除重复的数据。
13.排序：
如果需要对读取的数据进行排序，可以使用 order by 子句来设定想按哪个字段哪种方式来进行非排序，再返回搜索结果。

select field1,field2,...fieldN table_name1,table_name2...
order by fueld1,[field2...][asc [desc]]

注：
　　1.可以使用任何字段来作为排序的条件，从而返回排序后的查询结果；
　　2.可以设定多个字段来排序；
　　3.可以使用asc或desc关键字来设置查询结果是按照升序或降序排列。默认情况下，它是按升序排列；
　　4.可以添加where...like子句来设置条件。
14.group by分组：

select column_name,function(column_name)
from table_name
where column_name operator value
group by column_name;

　　1.group by 语句根据一个或多个列对结果集进行分组；
　　2.在分组的列上可以使用count，sum，avg等函数。
15.连接的使用：
     可以在select，update和delete语句中使用mysql的join来联合多表查询。join 按照功能大致分为如下三类：
　　1.inner join（内连接，或等值连接）：获取两个表中字段匹配关系的记录；
　　2.left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录；
　　3.right join（右连接）：与left join相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例：

select table1.username,table1.password,table2.id from table1 (inner) join table2 on table1.country=table2.country;
select table1.username,table1.password,table2.id from table1,table2 where table1.country=table2.country;

以上两种方式等价。
16.null值处理：
三大运算符：
　　1.is null：当列的值是null，此运算符返回true；
　　2.is not null：当列的值不为null，运算符返回true；
　　3.<=>：比较运算符（不同于=运算符），当比较的两个值为null时返回true；
注：
　　关于null的条件比较运算是比较特殊的。不能使用 =null 或 !=null 在列中查找null值。在mysql中，null值与任何其他值的比较（即使是null）永远返回false，即null=null返回false。mysql中处理null使用 is null 和 is not null 运算符。
17.正则表达式：
mysql中使用regexp操作符来进行正则表达式匹配：
例：

select name from table1 where name regexp '^st';//查找name字段中以'st'为开头的所有数据
select name from table1 where name regexp 'ok$';//查找name字段中以'ok'为结尾的所有数据
select name from table1 where name regexp 'mar';//查找name字段中包含'mar'字符串的所有数据
select name from table1 where name regexp '^[aeiou]|ok&';//查找name字段中以元音字符开头或以'ok'结尾的所有数据

附：
　　^ ：匹配输入字符串的开始位置；
　　$ ：匹配输入字符串的结束位置；
　　. ：匹配除"\n"之外的任何单个字符；
　　[...] ：字符集合。匹配所包含的任意一个字符。例如，'[abc]'可以匹配 "plain" 中的 'a' ；
　　[^...] ：负值字符集合。匹配未包含的任意字符。例如，‘[^abc]’可以匹配 "plain" 中的 'p' ；
　　p1|p2|p3 ：匹配p1或p2或p3。例如 'z|food' 能匹配"z"或"food"。'(z|f)ood'则匹配"zood"或"food"；
　　* ：匹配前面的子表达式零次或多次。例如，'zo*'能匹配"z"以及"zoo"。* 等价于{0,}；
　　+ ：匹配前面的子表达式一次或多次。例如，'zo+'能匹配"zo"以及"zoo"，但不能匹配"z"。+等价于{1,}；
　　{n} ：n是一个非负整数。匹配确定的n次。例如，'o{2}'不能匹配"Bob"中的'o'，但是能匹配"food"中的两个'o'；
　　{n,m} ：m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。
18.事务：
注：
　　1.在mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务；
　　2.事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行；
　　3.事务用来管理insert，update，delete语句；
　　4.事务必须满足（ACID）：原子性、一致性、隔离性、持久性：

mysql事务处理主要有两种方法：
　　1.用begin，rollback，commit来实现
　　　　begin：开始一个事务
　　　　rollback：事务回滚
　　　　commit：事务确认
　　2.直接用set来改变mysql的自动提交模式：
　　　　set autocommit=0 ：禁止自动提交
　　　　set autocommit=1 ：开启自动提交
19.alter命令：
　　1.删除，添加或修改表字段：

alter table table_name drop i;//删除表的i字段（如果表中只剩一个字段则无法使用drop删除字段）
alter table table_name add i int;//向表中添加i字段并定义数据类型（字段会自动添加到表字段的末尾）
alter table table_name add i int first;//新增字段会设定为第一列
alter table table_name add i int after c;//设定位于c字段之后

　　2.修改字段类型及名称(使用modify或change)：

alter table table_name modify c char(10);//将字段c的类型改为char(10)
alter table table_name change i j bigint;
alter table table_name change j j int;//change关键字之后紧跟的是你要修改的字段名，然后指定新字段名及类型

　　3.alter table 对null值和默认值的影响（如果不设默认值，mysql会自动将该字段设为null）：

alter table table_name modify j bigint not null default 100;//指定字段j为not null 且默认值为100

　　4.修改字段默认值：

alter table table_name alter i set default 1000;//修改i字段默认值为1000
alter table table_name alter i drop default;//删除i字段的默认值
alter table table_name enging=myisam;//修改存储引擎为myisam

　　5.修改表名：

alter table table_name rename to new_table;

　　6.删除外键约束：

alter table table_name drop foreign key keyName;

　　7.修改字段的相对位置：

alter table table_name modify column1 type1 first|after column2;//type1为column1字段原来的类型

20.索引：

　　1.普通索引：
　　　　(1).创建索引：

create index indexName on mytable(username(length));

　　　　(2).修改表结构（添加索引）：

alter table table_name add index indexName(columnName);

　　　　(3).创建表的时候直接指定：

create table mytable(id int not null,username varchar(16) not null,index [indexName](username(length)));

　　　　(4).删除索引：

drop index [indexName] on mytable;

　　2.唯一索引：
　　　　(1).创建索引：

create unique index indexName on mytable(username(length));

　　　　(2).修改表结构：

alter table table_name add unique [indexName](username(length));

　　　　(3).创建表的时候直接指定：

create table mytable (id int nopt null,username varchar(16) not null,unique [indexName](username(length)));

　　3.使用alter命令添加和删除索引：
　　例：

alter table table_name add index (c);//在表中添加索引
　　alter table table_name drop index c;//删除索引

　　4.使用alter命令添加和删除主键：

alter table table_name modify i int not null;//添加主键索引时，需要确保该主键默认不为空(not null)
　　alter table table_name add primary key (i);
　　alter table table_name drop primary key;//删除主键

　　5.显示索引信息(通过添加\G来格式化输出信息)：

show index from table_name;\G

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

create temporary table table_name(name varchar(10),price double not null default 0.00,count int not null default 0);

　　当使用show tables命令显示数据表列表时，将无法看到临时表。如果退出当前mysql会话，再使用select命令来读取原先创建的临时表数据，会发现数据库中没有该表的存在，因为在退出时临时表已经被销毁了。
　　2.删除临时表：

drop table table_name;

22.复制表：
完整的复制mysql数据表：
　　1.使用show create table 命令获取创建数据表（create table）语句，该语句包含了原数据表的结构，索引等；
　　2.复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构；
　　3.如果想复制表的内容，可以使用insert into ...select语句来实现。
例：
　　1.获取数据表的完整结构：

show create table table_self;

　　2.修改SQL语句的数据表名，并执行SQL语句：

create table clone_tbl(...);

　　3.执行完步骤2后，将在数据库中创建新的克隆表clone_tbl。如果想拷贝数据表的数据可以使用insert into...select语句来实现：

insert into clone_tbl(column1,column2,...columnN)
　　select column1,column2,...columnN from table_self;

附：
另一种完整复制表的方法：

create table targetTable like sourceTable;
insert into targetTable select * from sourceTable;

可以拷贝一个表中其中的一些字段：

create table newadmin as(select username,password from admin);

可以将新建的表的字段改名：

create table newadmin as(select id,username as uname,password as pass from admin);

可以拷贝一部分数据：

create table newadmin as(select * from admin where left(username,1)='s');

可以在创建表的同时定义表中的字段信息：

create table newadmin(id int not null auto_increment primary key)as(select * from admin);

mysql复制表的两种方式：
　　1.只复制表结构到新表：

create table 新表 select * from 旧表 where 1=2;    //where 1=2 永远不成立，所以select * from 旧表 where 1=2 返回一个空集合，只返回旧表的字段
　　或者
　　create table 新表 like 旧表;

　　2.复制表结构及数据到新表：

create table 新表 select * from 旧表;

23.元数据：

　　1.获取查询语句影响的记录数：查询结果信息：select,update或delete语句影响的记录数；
　　2.数据库和数据表的信息：包含了数据库及数据表的结构信息；

show tables;
　　show databases;

　　3.mysql服务器信息：包含了数据库服务器的当前状态，版本号等。

select version(); //服务器版本信息
　　select database();//当前数据库名（或者返回空）
　　select user(); //当前用户名
　　shoew status; //服务器状态
　　show varibles; //服务器配置变量

24.序列使用：
mysql序列是一组整数：1,2,3,..,由于一张数据表只能有一个字段自增主键，如果想实现其他字段也自动增加，可以使用序列。
　　1.使用auto_increment
　　2.获取auto_increment值：

last_insert_id();//获取最后的插入表中的自增列的值

　　3.重置序列：如果删除了多行记录，并希望对剩下数据的auto_increment列重新排列，可以删除自增的列然后重新添加：

alter table table_name drop id;
　　alter table table_name add id int not null auto_increment primary key;

　　4.设置序列的开始值：一般序列的开始值为1，如果需要指定，可通过下列语句实现：

create table table_name(id int not null auto_increment primary,...)auto_increment=100;

也可以在表创建成功后，使用：

alter table t auto_increment=100;

25.mysql处理重复数据：
　　1.防止表中 出现重复数据：
　　　　(1).可以通过设置primary key（主键）或unique（唯一）索引来保证数据的唯一性。如果设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功，并抛出错。
　　　　(2).使用insert ignore into执行后不会出错，也不会向数据表中插入重复数据，该语句会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。
　　　　(3).insert ignore into 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而 replace into into 如果存在primary或unique相同的记录，则先删除掉，再插入新记录。
　　2.统计重复数据：

select count(*) as repetitions,last_name,first_name
　　from person_tbl
　　group by last_name,first_name
　　having repetitions > 1;

　　该语句将返回person_tbl表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：
　　　　(1).确定哪一列包含的值可能会重复；
　　　　(2).在列选择列表中使用count(*)列出的那些列；
　　　　(3).在group by子句中列出的列；
　　　　(4).having子句设置重复数大于1。
　　3.过滤重复数据：
　　　  如果需要读取不重复的数据可以在select语句中使用distinct关键字来过滤重复数据：

select distinct last_name,first_name from person_tbl;

也可以使用group by来读取数据表中不重复的数据：

select last_name,first_name from person_tbl group by(last_name,first_name);

　　4.删除重复数据：

create table tmp 
　　select last_name,first_name,sex 
　　from person_tbl 
　　group by(last_name,first_name,sex);
　　drop table person_tbl;
　　alter table tmp rename to person_tbl

　　　也可以在数据表中添加index(索引)和primary key(主键)来删除表中的重复记录：

alter ignore table person_tbl
　　add primary key(last_name,first_name);

26.MySQL及SQL注入：
如果通过网页获取用户输入的数据并将其插入一个mysql数据库，就有可能发生SQL注入安全问题。
SQL注入，就是通过把SQL命令插入到web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
防止SQL注入，需要注意以下几点：
　　1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等；
　　2.永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取；
　　3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接；
　　4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息；
　　5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装；
　　6.SQL注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用SQL注入检测工具jsky，网站平台有亿思网站安全平台检测工具，MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
like语句中的注入：
like查询时，如果用户输入的值有"_"和"%"，则会出现这种情况：用户本来只是想查询"abcd_"，查询结果中却有"abcd_"、"abcde"、"abcdf"等等；用户要查询"30%"（注：百分之三十）时也会出现问题。
27.导出数据：
　　1.使用select...into outfile语句导出数据：

select * from tbl into outfile '/self/xxx.txt';

　　可以通过命令选项来设置数据输出的指定格式，以下为导出CSV格式：

select * from passwd into outfile 'xxx.txt'
　　fields terminated by ',' enclosed by '"'
　　lines terminated by '\r\n';

　　生成一个文件，各值用逗号隔开（这种格式可以被许多程序使用）：

select a,b,a+b into outfile 'xxx.txt'
　　fields terminated by ',' optionally enclosed by '"'
　　lines terminated by '\n'
　　from test_table;

　　附：
　　select ...into outfile 语句有以下属性：
　　　　(1).load data infile是select...into outfile的逆操作，select句法。为了将一个数据库的数据写入一个文件，使用select...into outfile,为了将文件读回数据库，使用load data infile;
　　　　(2).select...into outfile'file_name'形式的select可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有file权限，才能使用此语法；
　　　　(3).输出不能是一个已存在的文件。防止文件数据被篡改；
　　　　(4).你需要有一个登录服务器的账号来检索文件，否则select...into outfile不会起任何作用；
　　　　(5).在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然可以读取该文件，但可能无法将其删除。
　　2.导出表作为原始数据：
　　3.导出SQL格式的数据：
　　　　(1).需要导出整个数据库的数据，可以使用以下命令：

$ mysqldump -u root -p db_name>database_dump.txt
　　　　password ******

　　　　(2).需要备份整个数据库，可以使用以下命令：

$ mysqldump -u root -p --all-database>database_dump.txt
　　　　password ******

　　4.将数据表及数据库拷贝至其他主机：
　　在源主机上执行以下命令，将数据备份至dump.txt中：

$ mysqldump -u root -p database_name table_name>dump.txt
　　password ******

　　如果完整备份数据库，则无需使用特定的表名称。
　　如果需要将备份的数据库导入到mysql服务器中，可以使用以下命令，使用以下命令需要确认数据库已经创建：

$ mysql -u root -p database_name<dump.txt
　　password ******

28.MySQL导入数据：
　　1.使用load data导入数据：

load data local infile 'dump.txt' into table mytbl;

　　以上实例从当前目录中读取文件dump.txt，将该文件中的数据插入到当前数据库的mytbl表中。如果指定local关键字，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。
　　2.使用mysqlimport导入数据：

$mysqlimport -u root -p --local database_name dump.txt
　　password ******