MySQL必会

sql语言对大小写不敏感，但一般使用大。
1.创建数据库
create database test;

2.授予权限
crant all on test.* to user(s);

3.使用指定数据库
use test;

4.删除数据库（可删除数据库里所有的表数据，并将其从系统中删除）
drop database test;

5.创建一个字符串列，login，两个整形列，userid、projid
create table user (login varchar(8),userid int,projid int);

6.删除数据库中的一个表，并清空其中的所有数据
drop table users;

7.向数据库插入一个新行，需要指定表名以及其中每列的值。
insert into users values('leanna',211,1);

8.更新行
update users set projid=4 where projid=2;
update users set projid=1 where userid=311;
修改以及存在的行用update语句。使用set来确定需要修改的列，并提供条件
来确定修改的行。在一个例子，所有的 project id为2的改为4.
在第二个例子中，将uid为311的用户移动到编号为#1的项目组中。

9.删除行
delete from users where projid=%d;
delete from users;
例子1删除指定行，例子2没指定，将所有行删除。

******************************************************************************

例：
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | google | https://www.google.cm/ | 1 | usa |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | cn |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | cn |
| 4 | 微博 | http://weibo.com/ | 20 | cn |
| 5 | facebook | https://www.facebook.com/ | 3 | usa |
+----+--------------+---------------------------+-------+---------+

10.从数据库中选取数据
select name,country from websites;
从websites选取name,country
select *from websites;
从websites选取所有列。

11. select distinct country from websites;
从websites选country取出唯一不同的值，也就是说去除重复的。

12.where 用于过滤
select * from websites where country ='cn';
从websites取出，country为cn所有国家的网站。

13.''单引号只用作文本，如果是数字不可以用引号。
select *from websites where id=1;

14.and 和 or 运算符
在使用where可以配上and 和 or以增加过滤条件
select *from websites where contry='cn' and alexa > 50;
select *from websites where alexa >50 and(country='cn' or country='usa' );

15.order by 用于对结果集排序
order by 默认从小到大，若想从大到小，需在后面加上desc
select * from websites order by alexa;
按照alexa从小到大排序
select * from websites order by alexa desc;
按照alexa从大到小排序
select * from websites order by country,alexa;
按照country和select进行排序
16.insert into插入语句
不在指定列插入
insert into websites(name, url, alexa, country) values('百度','https://www.baidu.com/','4','cn');
在指定列中插入行
insert into websites(name, url,country) values('百度','https://www.baidu.com/',cn');

17.update 更新数据
update websites
set alexa='5000',country='usa'
where name='菜鸟教程';
如果不加where会把表里所有所有alexa和country更改

18.delete 删除数据
delete from websites where name='百度' and country='cn';
如果要将所有行删除，则不需加where。这个时候会保留表的结构、属性、索引。
delete from table_name;
delete * from table_name;

************************************************************************
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
sql函数

19.sql count()函数 函数返回指定列值的数目
sql count(*)函数 返回所有行的数目
sql count(distinct) 返回指定列不同行的数目

select count(count) as nums from access_log //括号里的count什么意思。
where site_id=3;
求sit_id=3时总访问量
select count(*) as nums from access_log;
求"access_log" 表中总记录数
select count(distinct site_id) as nums from access_log;

20.sql sum()函数 求 指定数字的那一列(count) 的总数。
select sum(count) as nums from access_log;

21.sql select limit语句
从websites库中选取头两条
select * from websites limit 2;
当指定了位置偏移量时，从websites库第3条起取4条时,默认偏移量为0
select * from websites limit 1,4;

22.sql select top 语句 也表示取前多少数据
top percent:select top 50 percent * from websites;
表示从websites取前百分之50

23.sql group by 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
select site_id,sum(count) as nums from access_log group by sit_id;

24.sql having 语句 与where关键字对比去记，因为where无法与聚合函数一起使用。having 子句可以让我们筛选分组后的各组数据。

25.wherer 与 select连用，在查询数据时做筛选的；在筛选的时候，除了用'=' '>' '<' and or等条件判断之外
还可以使用：'in' 'between 1 and 20' 表示范围。

26.在查询中涉及超过一个表，在查询中使用了函数，列名称很长或者可读性差，需要把两个列或者多个列结合在一起
以上四个条件适合将表或列改为别名。
as +name
具体用法：select name as n, country as c from websites;
就是查询表websites，将name列与country更名为n或c在输出。同理表的名也可如此更改。as一般放from后使用
select w.name, w.url, a.count,
a.date from websites as w, access_log as a
where a.site_id=w.id and w.name="菜鸟教程";

27.sql inner join 连接两个表格
找到两个表格的公共部分，一般都是id。将两个表格连接成一个表格。并将将所需要信息提取出来。
select websites.name,access_log.count, access_log.date
from websites inter join access_log
on websites.id = access_log.site_id
order by access_log.count;
28.sql create database 语句 创建一个库
create datebase my_db； 创建一个名为 "my_db" 的数据库

29.sql create table 语句 创建一个表
现在想要创建一个名为 "persons" 的表，包含五列：personid、lastname、firstname、address 和 city。
create table persons
(
【column_name1 data_type(size),】
personid int,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
);

30.like 与where 用于筛选字符串
'%a' //以a结尾的数据

'a%' //以a开头的数据

'%a%' //含有a的数据

‘_a_’ //三位且中间字母是a的

'_a' //两位且结尾字母是a的

'a_' //两位且开头字母是a的
select * from websites
where name like '%k';
输出name以k结尾的的行。

--------------------------------------------
1.事物---->保证业务逻辑的完整性
四大特性acid
1)原子性：数据库操作是不可分割的，要么全做完，要么不执行
2)一致性：数据库操作的顺序与结果无关
3)隔离性：加锁，不受其他事物干扰
4)持久性：任意提交事物，系统保证改变不被丢失，即使数据库出现故障。

2.数据库引擎

* 引擎:数据库操作的底层代码，内部设计数据结构与算法

3.使用事物的情况：当数据被更改时：insert update delete
* 表锁--------必须等到一个人操作完了解锁以后另一个人才能操作
* 行锁--------允许多个人操作一张表，即只要不操作被锁的那一行即可。
* begin开始事物------>上锁----->sql语句---------->产生内存级临时表
* commit------------->将刚刚临时表里的数据转移到数据库
* rollback-------->撤销刚刚操作

4.索引----索引是一种加快检索速度的数据结构
* 数据默认是按照主键索引存储的。
* 索引的分类：
按照建索引的方式
单列索引，即索引只包含一个类
组合索引，即一个索引包含多个列

聚集索引
非聚集索引
* 使用索引的缺点：
建立太多了索引会导致物理开销太大，即会占用太多的内存空间。
增加了对表的维护成本。在对表更改时，不仅要保存数据，还有保存索引文件。
* 对某个列建索引，尽量让这个有比较间单的数据类型，越小处理越快，避开null
* 没有索引where一行一行查，有了索引一步到位。
* 对于范围查找的字段索引可能会失效，等值往前写，范围往后写，尽量不用or
* 查看索引 show index from 字段名;
* 创建索引 create index 索引名 on 表名(字段名(字段长度)) 数字可以不用指定长度
* 删除索引 drop index [索引名] on 表名

5.最左前缀原则
如果对column1，column2，column3建立了联合索引，那么在使用该索引时只有三种组合，它们分别是：column1 、column1 and column2、column1 and column2 and column3，
概括起来就是想要使用右边的索引，必须用上左边的所有索引。如果只使用column2作为where的查询条件，将不会用到所建好的索引。这是为什么呢？请看表2。这就好比直接用月薪作为查询条件，
有没有发现月薪是呈全局乱序的状态，尽管它是局部有序的。
如果业务上要求只能用月薪来查询，可行的解决办法是在建立联合索引时把月薪放在最左边，或者直接建立单列索引。
建立联合索引时，数据粒比较小的放大左边

6.索引的本质其实就是新建了一张表，而表本质上的数据结构就是树形结构，所以索引也是树形结构

7.聚集索引：聚集索引的顺序就是数据存储的物理顺序，一个表只能包含一个聚集索引。所以查找更快
非聚集索引：非聚集索引存储在一个地方，数据存储在另一个地方，索引带有指针指向数据存储位置。
索引中的项目按引健值的顺序存储，而表中的信息按另一种顺序存储。
innodb的数据文件本身就是索引文件。从上文知道，myisam索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
8.聚合
count\max\min\（id） sum\avg---必须是（int 或 double）
原始数据看到了，只能看结果。
9.子查询，查询里面嵌套查询
10.分组
分组一般与聚合连用。分组的目的为了更好的统计，非聚合字段不能出现结果集。
分组依据一般是一个字段里相同的会被分到一块去
11.分组后筛选用where
where对原始集进行筛选，having 对分组后的结果集进行筛选。

12.关联表查询
连接分为内链接inner join、左连接 left join 、 右right join
三种连接的差异就在于结果集能显示出来的数据不同，使用内链接，结果集只能显示出两个表匹配上的信息。
使用left join 在显示两个表匹配上信息的基础上，额外还会增加的是左边表独有的信息，没匹配上的用null
使用right join 在显示两个表匹配上信息的基础上，额外还会增加的是右边表独有的信息，没匹配上的用null

具体操作方式 select 查询的信息用.表示 from tablename1
inner join/laft join/ right join on tablename1. = tablename2.

13.查询语句顺序
先结果集——然后关联——然后where 筛选——然后group by/order by ——然后hving 对分组过的结果集筛选

14.分页用limit 每页m条数据，当前显示n页
limit n *m ，m n从0开始

15.一索引二级索引
一级索引：
innodb会自动建立聚集索引，即将数据与索引存放在一起。使用b+tree的数据结构，将数据存储在叶子结点，
各个叶子结点通过指针相连。
1) innodb通常根据主键值(primary key)进行聚簇
2) 如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引-----自增id
3) 上面二个条件都不满足，innodb会自己创建一个虚拟的聚集索引，隐藏。
辅助索引：
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据叶，再通过数据叶中的page directory找到数据行。
即二级索引是我们后建的，它只存储了部分的数据。如果根据键值查找到的数据不能包括全部目标数据时
（就是无法使用到覆盖索引），就需要通过二级索引的指针，也就是键值对中的值，来找到聚簇索引的全部数据，
然后根据完整的数据取出所需要的列的过程就称之为回表。

16.行级锁和页级锁会出现死锁，表锁不会。
表锁的好处是上锁解锁快，并且能避免出现死锁。

17.使用锁是为了保持数据的一致性，但要避免死锁，死锁是两个或多个事物在同一资源上相互占用，并请求锁定对方的资源，
从而导致恶性循环的现象。
常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

18.乐观锁与悲观锁
悲观锁：
悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，
防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，
将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，
可能会严重影响系统的并发处理能力。 自带的数据库事务就是典型的悲观锁。
乐观锁：
乐观锁（optimistic lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，
但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
一般是加一个版本号字段 每次更新时候比较版本号

19.innodb的主键是主索引，myisam的主键与辅助没什么区别，存的是数据的地址。
所以innodb要求表必须有主键（myisam可以没有）innodb的所有辅助索引都引用主键作为data域。
聚集索引搜索是很高效的，而辅助索引必须遍历两遍。遍历第一遍获得主键，根据主键检索。

 

如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！