MySql学习总结

由于mysql内容较多，本文只是把mysql的一些基本内容记录到一块，方便随时需要随时查阅，后续有时间也会不断增加

mysql语句整体来说可以分为四类：

• 数据定义语言(DDL)：用于创建修改数据库内部的数据结构，如：create，rename，drop，alter。
• 数据查询语言(DQL)：从数据库的一个或者多个表中查询数据，如：select。
• 数据操作语言(DML)：修改数据库表中的数据，如：insert，delete，update。
• 数据控制语言(DCL)：用于数据库访问权限的控制，如：grant，remoke。

---

数据定义语言

1.create可以用于创建数据库，表，视图等等。

-- 创建数据库
create database csdnnote;
-- 使用该数据库，下面的操作都将在这个数据库里面
use csdnnote;

-- 创建一个csdn的文章列表
-- id   :文章的编号
-- name :文章标题
-- whenw:文章编写时间
create table csdn_article ( id int not null, name varchar(255) not null, whenw datetime, remark varchar(255) null) engine=InnoDB;

-- 基于csdn_article创建一个view
-- 检索文章id以及title(name)
create view articlelist as select id,name as title from csdn_article;

2.rename主要用于修改表名等等。

 -- 把表名修改为csdn_articles，基于原来的表名创建的视图便不能用了
 rename table csdn_article to csdn_articles;

3.drop主要用来删除数据库，表，视图，以及索引等等

    --删除视图
    --由于上次我们创建的视图在修改表名后失效了，这里把其删除。
    drop view articlelist;
    -- 通过show tables；查看视图已经不存在了
    /* 由于删除table和删除视图的语法格式一样，这里不在写出来
       drop table tablename;
     */

4.alter主要用于表级操作，如增加列，删除列，修改列的类型信息以及名字，重命名表，删除主键，添加主键，添加索引，删除索引等等

-- 添加列
-- 这里我们添加一无用的列，然后在把其删除。
-- 列名    :nouse
-- 内容类型 :varchar
-- 默认值   :null
-- 验证方式 :desc csdn_articles;或者show create table csdn_articles；
alter table show create csdn_articles； add column nouse varchar(20) null;
-- 删除列,验证方式同添加列
alter table csdn_articles drop column nouse;

-- 使用alter修改列
-- 使用alter修改列时有两种组合：
-- （1）alter + modify 不能修改表名，可以修改列类型以及属性
-- （2）alter + change 能修改表名已经列类型属性
-- 我们把remark从255个字符修改成500个字符
alter table csdn_articles modify remark varchar(500) null;
-- 设置id的自动增加
alter table csdn_articles modify id int(11) not null auto_increment;
-- 我们把name修改为title，如果原表中存在用name建立的各种间，那么mysql会在更改列名时自动完成响应修改
alter table csdn_articles change name title varchar(255) NOT NULL;
-- 把title改回name
alter table csdn_articles change title name varchar(255) NOT NULL;

-- 添加主键，这里我们把id列添加为主键
-- 注意：一个主键能够包含该多列，这里我们演示了一列
alter table csdn_articles add primary key(id);
-- 通过desc tablename;或show create table tablename;来查看是否添加成功

-- 删除主键，使用alter删除id上的主键，由于一个表只有一个主键，所以这里我们并不需要制定那个列上的主键。
alter table csdn_articles drop primary key;

补充：主键，唯一索引，普通索引，复合索引的区别
普通索引：的唯一任务是加快对数据的访问速度，普通索引允许被索引的数据列包含重复的值。因此，应该只为那些最经常出现在查询条件(where column=)或排序条件（order by column）中的数据列创建索引。处于插入效率考虑，建立唯一索引的column并不是越多越好。
唯一索引：如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
主键索引：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE，主键索引只能有一个，二唯一索引与普通索引可以有多个。
复合索引：索引可以覆盖多个数据列，如像INDEX（columnA，columnB）索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX（columnA,columnB）。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX（A，B，C）可以当做A或（A,B）的索引来使用，但不能当做B、C或（B，C）的索引来使用。
全文索引：全文索引主要用于为大段文字建立索引。主键索引、普通索引、唯一索引以及复合索引在所有索引列的字符串长度之和大于一定值时会建立索引失败，以复合索引为例，name_remark_index(name, remark)如果len(name)+len(remark)大于1000(与数据库版本有关，还有存储引擎有关)就会报错，其中len(name)创建表时指定的长度，而不是数据实际长度，myisam表，单列索引，最大长度不能超过 1000 bytes，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）， myisam表，组合索引，索引长度和不能超过 1000 bytes，否则会报错，创建失败； innodb表，单列索引，超过767 bytes的，给出warning，最终索引创建成功，取前缀索引（取前255 字符）。innodb表，组合索引，各列长度不超过767 bytes ，如果有超过767 bytes 的，则给出报警，索引最后创建成功，但是对于超过767字节的列取前缀索引，与索引列顺序无关，总和不得超过3072，否则失败，无法创建。

-- 添加普通索引,建立在id上
-- 索引名:id_index
alter table csdn_articles add index id_index(id);
-- 添加第二个普通索引，建立在name上的索引
-- 索引名:name_index
alter table csdn_articles add index name_index(name);

-- 删除索引
alter table csdn_articles drop index id_index;
alter table csdn_articles drop index name_index;
-- 普通索引也是可以基于多列进行创建的
alter table csdn_articles add index name_id_index(id, name);

-- 创建唯一索引，唯一索引既可以基于多列也可以基于单列
alter table csdn_articles add unique index remark_index(name, remark);
-- 删除唯一索引不用指定unique
alter table csdn_articles drop  index remark_index;
-- 可以在同一列上添加多个相同类型的索引，不同名的索引具有互斥性，我们可以再主键上添加其他索引。
-- 在删除remark_index之前，可以在name上添加一个unique index
alter table csdn_articles add unique index dup_name_unique_index(name);
-- 如果我们在已经添加unique的索引上添加普通索引则不行

-- 添加全文索引，基于多列建立索引
alter table  csdn_articles add fulltext name_remark_fulltext(name, remark);
-- 删除全文索引
alter table  csdn_articles drop index name_remark_fulltext;

---

数据操作语言(DML)

---

1.insert主要用于往数据库表中插入数据，可以单条插入也可以批量插入，也可以把其他select语句的检索结果作为数据库的插入数据。

-- 使用insert插入单条数据
insert into csdn_articles(title,whenw,remark) values("man epoll", NOW(),"The  epoll API performs a similar task to poll");
-- 如果表名后面不指定列，在values里面就要为表中所有的列，按照列出现的顺序为列提供值。
-- 多列删除时values后接多个","分割开的value域。
insert into csdn_articles(title,whenw,remark) values("man epoll_wait", NOW(), "The  epoll_wait()  system  call  waits for events on the epoll"),("man epoll_ctl", NOW(), "This  system  call  performs  control  operations  on the epoll");

-- 基于其他select语句的检索结果插入
insert into csdn_articles(title,whenw,remark) values(select title,whenw,remark from csdn_articles);
-- 上面的语句会导致csdn_articles表中除了id之外的内容重复，但也是可以演示基于其他select检索结果记性插入的语法格式的。

2.delete主要是用于删除表中满足一定条件的列，有时候也能用于view的删除，但是view的删除有很多限制，在这里本文不做展开了。

-- 删除上面重复的行，在上面的插入操作执行完毕后，我们的csdn_articles总共有六行，id=[1,6],其中4-6行是1-3行的重复插入，这里我们把4-6行删除。
-- 语法：delete from tablename 条件
delete from csdn_articles where id >= 4;

补充:delete和drop的区别？
对于结构删除使用drop，如数据库删除、表删除、索引删除等；而对于数据的删除则是delete。

3.update主要是用于更新表中满足条件的行的内容。

-- 语法：update tablename set columnname=new_value 条件;（如果不跟条件会导致当前表当前列的内容全部改变）
-- 修改下id=3的更新时间
update csdn_articles set whenw=NOW() where id=3;

-- 修改多行或者所有行，下面两条语句对于csdn_articles表是等价的。
update csdn_articles set whenw=NOW() where id>=1;
update csdn_articles set whenw=NOW();

---

数据查询语言(DQL)

数据查询语言中的关键语句是select，select 可以说是mysql中使用最多，用法最为丰富的语句了。

select语法:select 列名1，列名2，列名3…列名n from tablename 条件;
【条件】在select是可带可不带的，它的主要作用是使检索结果更加精确，更加复合我们的预期。

---

1.使用select检索单个列

-- 检索title列【无条件】
select title from csdn_articles;
-- 检索title列【带条件】
select title from csdn_articles where id = 2;

2.使用select检索多列

-- 检索title及其对应的remark
select title, remark from csdn_articles;
-- 带条件检索
select title, remark from csdn_articles where id != 1;
-- 条件中带逻辑关系
select title, remark from csdn_articles where id = 1 or id = 3;

3.select与limit结合限制返回的行数

limit后面跟一个参数

-- 在没有limit是，下面的select语句能够检索出来三行内容
select * from csdn_articles;
-- 加上limit 2之后，下面的语句返回结果会限制在2行以内
-- 如果未加limit num之前就返回了<=num行，加上limit之后也会返回<=num行，这里num=2。
-- 如果加上limit num，且返回结果集A，且len(A)>num，那么该语句将返回结果集A的子集A[1,num]，A集合的元素下标k (1<= k <= n)
select * from csdn_articles limit 2;

如果limit后面跟两个数字，如limit 3 4，它的意思是从结果集A中元素下标3的位置向后查找最多4个元素返回（不包括第三行），如果len(a) <= 3，返回结果为空。

-- 在没有limit是，下面的select语句能够检索出来三行内容
select * from csdn_articles;
-- 下面的语句将返回1行，因为原结果集中元素个数最多是3个，也就是A[1],A[2],A[3]，由于又从第2行后一个位置开始查找最终结果集，，所以最多返回A[3]（A(2,-]）。
select * from csdn_articles limit 2，3;

3.select与order by连用，对结果集进行排序，有一下三种情况需要考虑：
（1）order by的默认行为是增序排列，行为类似与语句（order by 列名1,…列名n asc;）如果把asc修改为desc，就能把order by的行为从升序变为降序。
（2）order by 后面跟多列时，只有在通过第一列无法确定当前正在比较的两行的顺序时（也就是当前比较的两行对应列是相等的），才会继续向后比较剩余的列。另外比较列的顺序是按照列名在order by后面出现的顺序，而不是表中列名顺序。

-- 按id大小降序排列
select * from csdn_articles order by id desc;
-- 按照title和id降序排列， 先比较title后比较id
select * from csdn_articles order by title,id desc;

4.select与where子句连用，where子句后大多跟的是具体的检索条件，相关select语句会把满足检索条件的行返回。
（1）where子句的常用操作符如下：

---

符号	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between A and B	在值A与值B之间

---

由于操作符过多只举一个between A and B的例子，如下：

-- 返回id介于2-3之间的行，包含id=2，以及id=3
select * from csdn_articles where id  between 2 and 3;
-- 如返回id介于2-4之间的行，由于我们没有第四行，饭后结果将和上面一条语句一样
select * from csdn_articles where id  between 2 and 4;

（2）order by与where连用时，order by应该放在where子句的后面。

-- 逆序输出操作符例子中的结果
select * from csdn_articles where id  between 2 and 3 order by id desc;

（3）如果where子句中向查找一个某一列为空的行，null值的比较不用=，而是用is。

-- 我们先插入一个remark为null的行
insert into csdn_articles(title,whenw) values("man getcwd", NOW());
-- 插入上面的语句时id自增并没承接id=3变为id=4，而是id=7了，这个主要我进行过多次插入删除，mysql内部记录的下一个自增的id随着插入次数一直在增加，删除时mysql并不会检查是否需要减小这个自增id，主要是因为我们的删除可能是不连续，如果mysql在用户每次删除都检查这个id是否应该重新设置，一方面效率低，一方面会引入其他准确性问题。
/*
+----+----------------+---------------------+-----------------------------------------------------------------+
| id | title          | whenw               | remark                                                          |
+----+----------------+---------------------+-----------------------------------------------------------------+
|  1 | man epoll      | 2018-07-10 09:33:17 | The  epoll API performs a similar task to poll                  |
|  2 | man epoll_wait | 2018-07-10 09:33:17 | The  epoll_wait()  system  call  waits for events on the epoll  |
|  3 | man epoll_ctl  | 2018-07-10 09:33:17 | This  system  call  performs  control  operations  on the epoll |
|  7 | man getcwd     | 2018-07-10 11:15:48 | NULL                                                            |
+----+----------------+---------------------+-----------------------------------------------------------------+
*/
-- 下面的语句可以检索到id=7的结果
select * from csdn_articles where remark is null;

5.通过and和or来组合where子句，and和or是有优先级之分的，and优先级 > or优先级，同一个作用域之内的，并不是那个在前就先计算那个的，而是先计算优先级较大的。

-- 下面两个sql语句是等价的
select * from csdn_articles where id  between 2 and 3 order by id desc;
select * from csdn_articles where id >= 2 and id <= 3 order by id desc;

看一个优先级的sql语句：

-- 数据库表的内容如下：
/*
+----+----------------+---------------------+-----------------------------------------------------------------+
| id | title          | whenw               | remark                                                          |
+----+----------------+---------------------+-----------------------------------------------------------------+
|  1 | man epoll      | 2018-07-10 09:33:17 | The  epoll API performs a similar task to poll                  |
|  2 | man epoll_wait | 2018-07-10 09:33:17 | The  epoll_wait()  system  call  waits for events on the epoll  |
|  3 | man epoll_ctl  | 2018-07-10 09:33:17 | This  system  call  performs  control  operations  on the epoll |
|  7 | man getcwd     | 2018-07-10 11:15:48 | NULL                                                            |
|  8 | test null      | 2018-07-10 11:26:09 | null                                                            |
+----+----------------+---------------------+-----------------------------------------------------------------+
*/
-- 下面两个等价的sql语句，为了更明确的表达sql语句的意思，我建议根据自己要表达的意思加上响应的括号
select * from csdn_articles where id = 7 or id >= 2 and remark is not null;
select * from csdn_articles where id = 7 or (id >= 2 and remark is not null);
-- 上面的sql执行以后检索结果如下：
/*
+----+----------------+---------------------+-----------------------------------------------------------------+
| id | title          | whenw               | remark                                                          |
+----+----------------+---------------------+-----------------------------------------------------------------+
|  2 | man epoll_wait | 2018-07-10 09:33:17 | The  epoll_wait()  system  call  waits for events on the epoll  |
|  3 | man epoll_ctl  | 2018-07-10 09:33:17 | This  system  call  performs  control  operations  on the epoll |
|  7 | man getcwd     | 2018-07-10 11:15:48 | NULL                                                            |
|  8 | test null      | 2018-07-10 11:26:09 | null                                                            |
+----+----------------+---------------------+-----------------------------------------------------------------+
*/

6.where子句中的IN操作符、exists以及not操作符。
（1）IN操作符后面跟一个集合或则其他select返回的集合，使用形式如下：
A IN 集合B
如果操作数A在集合B中存在，那个这个语句就会返回true。

-- 检索id=2 或id=3的行
select * from csdn_articles where id in(1,3);
-- 等价的语句
select * from csdn_articles where id = 1 or id = 3;

（2）not操作符主要用于IN操作符前面，主要用于否定IN操作符的返回结果，not 也能对between的区间取反，这里不演示了，自己测试吧。

---

数据控制语言(DCL)

---

非数据库管理员，不太常用，略过了。

---