SQL基础语法

关系型数据和非关系型数据库

关系型数据库是指采用了关系模型来组织数据的数据库。简单来说，关系模式就是二维表格模型。
主要代表：SQL Server，Oracle,Mysql,PostgreSQL。

关系型数据库为了维护一致性所付出的巨大代价就是读写性能比较差。而像微博、facebook这类应用，对于并发读写能力要求极高，关系型数据库已经无法应付。所以必须用一种新的数据结构存储来替代关系型数据库。所以非关系型数据库应用而生。

NoSQL非关系型数据库，主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统，主要代表MongoDB，Redis、CouchDB。

NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

语法格式：

select 字段列表|* from 表名

[where 搜索条件]

[group by 分组字段 [having 分组条件]]

[order by 排序字段 排序规则]

[limit 分页参数]

Where 条件查询

可以在where子句中指定任何条件

可以使用 and 或者 or 指定一个或多个条件

where条件也可以运用在update和delete语句的后面

where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

-- 查询 users 表中 年龄在22到25之间的数据

select * from users where age >= 22 and age <= 25;

select * from users where age between 22 and 25;

-- 查询 users 表中 年龄不在22到25之间的数据

select * from users where age < 22 or age > 25;

select * from users where age not between 22 and 25;

-- 查询 users 表中 年龄在22到25之间的女生信息

select * from users where age >= 22 and age <= 25 and sex = '女';
and和or 使用时注意

sql会优先处理and条件（下面两语句结果不一样）

select * from users where age=22 or age = 25 and sex = '女';

select * from users where (age=22 or age = 25) and sex = '女';

Like 子句

我们可以在where条件中使用=,<,> 等符合进行条件的过滤，但是当想查询某个字段是否包含时如何过滤？

可以使用like语句进行某个字段的模糊搜索，

like 语句 like某个确定的值 和。where name = '王五' 是一样

select * from users where name like '王五';

-- 使用 % 模糊搜索。%代表任意个任意字符

-- 查询name字段中包含五的

select * from users where name like '%五%';

-- 查询name字段中最后一个字符 为 五的

select * from users where name like '%五';

-- 查询name字段中第一个字符 为 王 的

select * from users where name like '王%';

-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似

-- 查询表中 name 字段为两个字符的数据

select * from users where name like '__';

注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意：

尽可能的不去使用%或者_

如果需要使用，也尽可能不要把通配符放在开头处

sql中的统计函数（聚合函数）通常情况下都是配合着分组进行数据的统计和计算

max(),min(),count(),sum(),avg()

select count(*) from users; 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算

select count(id) from users; 如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计

Group BY 分组

group by 语句根据一个或多个列对结果集进行分组

一般情况下，是用与数据的统计或计算，配合聚合函数使用

-- 统计 users 表中 男女生人数

-- 很明显按照上面的需要，可以写出两个语句进行分别统计

select count(*) from users where sex = '女';

select count(*) from users where sex = '男';

-- 可以使用分组进行统计，更方便

select sex,count(*) from users group by sex;

-- 分别统计每个班级的男女生人数

select classid,sex,count(*) as num from users group by classid,sex;

注意，在使用。group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后面。

Having 子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where，

having后面常跟聚合函数

where过滤的是行数据，having过滤的是分组数据

-- 要统计班级人数

select classid,count(*) from users group by classid;

-- 统计班级人数，并且要人数达到5人及以上

select classid,count(*) as num from users group by classid having num >=5;

SELECT A FROM table GROUP BY A HAVING COUNT(DISTINCT B) = 2

Order by 排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，

首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序

Asc 升序，默认。  desc降序

-- 按照年龄对结果进行排序，从大到小

select * from users order by age desc;

-- 从小到大排序 asc 默认就是。可以不写

select * from users order by age;

-- 也可以按照多个字段进行排序

select * from users order by age,id; # 先按照age进行排序，age相同情况下，按照id进行排序

select * from users order by age,id desc;

Limit 数据分页

limit n 提取n条数据，

limit m,n 跳过m跳数据，提取n条数据

-- 查询users表中的数据，只要3条

select * from users limit 3;

-- 跳过前4条数据，再取3条数据

select * from users limit 4,3;

-- limit一般应用在数据分页上面

-- 例如每页显示10条数据，第三页的 limit应该怎么写？ 思考

第一页 limit 0,10

第二页 limit 10,10

第三页 limit 20,10

第四页 limit 30,10

-- 提取 user表中 年龄最大的三个用户数据 

select * from users order by age desc limit 3;

listagg( )比较重要

后面要跟group by一起使用，一般可以查询完毕之后最后再使用这个函数，然后group by所有查询的字段再带上listagg的字段即可，最后对listagg字段去重，sqlachemy也有这个函数

 

连接查询

1.where 是在两个表join完成后，再附上where条件
2.而 and 则是在表连接前过滤A表或B表里面哪些记录符合连接条件，同时会兼顾是left join还是right join。即
假如是左连接的话，如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NULL）。on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

添加数据

格式： insert into 表名(字段列表) values(值列表...)

-- 不指定字段添加值     insert into 表名 values(值列表...)

修改数据

格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

UPDATE delivery_detail SET shipping_status='Partial Shipped' WHERE id=47265

删除数据

delete from 表名 [where 条件]

-- 删除stu表中id值为100的数据

 delete from stu where id=100

-- 删除dms_express_company表中所有数据

DELETE FROM dms_express_company

修改表结构

语法格式:alter table 表名 action (更改的选项)

添加字段

语法:alter table 表名 add 添加的字段信息 --在users表中 追加 一个num字段

alter table users add num int not null;

-- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段 alter table users add email varchar(50) after age;

-- 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone alter table users add phone char(11) not null after age;

-- 在表的最前面添加一个字段

alter table users add aa int first;

删除字段

# 删除字段 alter table 表名 drop 被删除的字段名 alter table users drop aa;

修改字段

语法格式: alter table 表名 change|modify 被修改的字段信息 change: 可以修改字段名，

modify: 不能修改字段名。

# 修改表中的 num 字段 类型，使用 modify 不修改表名

alter table users modify num tinyint not null default 12;

# 修改表中的 num 字段 为 int并且字段名为 nn alter table users change num mm int;

# 注意:一般情况下，无特殊要求，不要轻易修改表结构

删除表

drop table 表名

MyISAM和InnoDB表引擎的区别

1) 事务支持

MyISAM不支持事务（回滚），而InnoDB支持。

事务：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行

MyISAM：只支持表级锁（锁整个表），用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

 InnoDB：支持事务和行级锁（锁单独的行），是innodb的最大特色。

行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。在 update 语句的 where 条件使用了唯一索引（比如id字段加了索引，where条件带上了id）就会单独锁定这一行，如果没有加索引，就会全表扫描，相当于把整个表锁住了。因此，当在数据量非常大的数据库表执行 update 语句时，如果没有使用索引，其他语句都会被锁住不能执行，业务会因此停滞，接下来等着你的，就是老板的挨骂。

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

InnoDB的主键范围更大，最大是MyISAM的2倍。

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。

InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

外键： MyISAM：不支持      InnoDB：支持

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。

另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。现在默认使用InnoDB。

事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位  

 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据 

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。简为：一个事务读取到另一事务已提交的insert数据。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
解决并发问题的途径是什么?答案是：采取有效的隔离机制。怎样实现事务的隔离呢？隔离机制的实现必须使用锁，下面是锁的基本原理：

1.共享锁（读锁）

共享锁用于读取数据操作，它允许其他事务同时读取锁定的资源，但不允许其他事务更新它。

2.排他锁（写锁）

排他锁用于修改数据的场合，他锁定的资源，其他事务部能读取也不能修改。

3.更新锁

更新锁在更新操作初始化截断用来锁定可能要被修改的资源，从而避免使用共享锁造成的死锁现象。
锁机制能有效地解决并发事务时的各种问题，但是也会影响到并发的性能。数据库系统提供了4种可选的事务隔离级别，它们是：

a.Read Uncommited:读未提交的数据

b.Read commited：读已提交的数据

c.Repeateble Read:可重复读

d.Serialable:串行化

Read Uncommited: 该隔离级别读取数据时不使用任何锁。可能会出现脏读，不可重复读，和幻读的问题。

Read commited:返回的是读取时间点之前已提交的数据，因此可以避免脏读。但重复读数据时，返回的数据和读取时间点有关，因此会重现不可重复读，另外还会出现幻读现象。

Repeatable Read:该隔离级别能够保证重复读，可以避免脏读和不可重复读问题。

Serializable:该隔离级别能够避免脏读，不可重复读和虚读现象，是最严格的隔离级别。

上面四种隔离级别，从a-d隔离级别越来越严格，数据安全和真实性越来越高，但并发性能越来越低。所以选择什么样的隔离级别应根据应用的具体要求而定。

数据库备份与还原

1、有时候正式系统和本地，需要把数据备份到本地数据库，方便测试。直接Navicat，转储sql，然后运行sql。数据和表都搞定（适用于数据量不大的情况），只能转储两种(结构，结构和数据)。可以单表和全表，适用于全表，单表适用于第二种方法。

2、导出向导和导入向导，单表操作，导出向导可以一次性导出多个表，导入向导一次执行导入一个，但是可以并行，也相当于一次多个。

3、数据传输：不推荐

4、数据同步：适用于小数量数据，几万行，超过10万速度就很慢，同步时注意外键关系，外键会制约同步成功与否。3和4都在nzvicat 工具栏目录下

SQL优化建议

1、如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

假设现在有employee员工表，要找出一个名字叫jay的人.

反例：

select id, name from employee where name = 'jay'

正例：

select id, name from employee where name = 'jay' limit 1

理由：

• 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。

• 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

2、应尽量避免在where子句中使用or来连接条件

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下SQL

反例:

select * from user where userid=1 or age = 18

正例：

//使用union all
select * from user where userid=1
union all
select * from user where age = 18

//或者分开两条sql写：
select * from user where userid=1;
select * from user where age = 18

理由：

• 使用or可能会使索引失效，从而全表扫描。

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。 

3、优化like语句 

日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。

反例：select userId，name from user where userId like '%123'

正例：select userId，name from user where userId like '123%'

4、应尽量避免在where子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

反例：select * from user where age-1 = 10；

正例：select * from user where age = 11；

理由：

• 虽然age加了索引，但是因为对它进行运算，索引直接迷路了。

5、Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小。

应尽量避免在where子句中使用!=或<>两个都是不等于操作符，否则将引擎放弃使用索引而进行全表扫描。

对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。

6、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

反例：

select job, avg(salary) from employee group by job having job='president' or job= 'managent'

正例：

select job, avg(salary) from employee where job='president' or job='managent' group by job；