一、数据库的基本语句

Create database 数据库名; 创建新数据库

show databases; 查看所有库

drop database 数据库名; 删除数据库

use 库名;  使用数据库

二、数据表的基本语句

1.Create table book(

id int not null auto_increment(设置主键自增),

字段名 varchar(50) not null(不为空) default " " (默认值为""),

字段名 varchar(50) not null default ‘’,

字段名 varchar(50) not null default ‘’,

字段名double not null default 0.00,

字段名 text,

字段名date,

primary key (id),

index 索引名称(数据表想设置索引列的名称),(为某个字段创建索引)

)   新增数据表

2.rename table 原表名 TO  新表名     重命名数据表

或者是alter table  原表名  rename 新表名   重命名数据表

3.show tables；查看所有数据表

4.drop table 表名; 删除某张表

5.desc 表名；  查看表结构

6.alter table 数据表名 add 列名 数据类型 约束 alter 列名;   添加列放在某列的后面

例如:alter table employee add height int(4) default 170 alter name;    添加一列int类型的 身高字段，默认值为170，放在name字段后面（如果想放在第一列把alter 列名改为first）

7.alter table 表名 change 原列名 新列名 数据类型 约束；更改某列列名

三、数据库关键字总结

1.where

where常在数据库中用来进行条件筛选

如 select xx from xx where id=xx;

这就是筛选出id字段=xx的所有记录

2.select

用于查询数据表里的数据

select 查询的字段名(*代表全部数据) from 表名 where [条件表达式]

3.insert

用于新增数据库里新的字段和数据

insert into 表名 (字段名1，字段名2，字段名3) values("字段名1的值","字段名2的值","字段名3的值")

可以利用insert关键字将已有的表中数据复制到另一张新表中

举例：将student表中name,qq_mail两字段数据复制到test_user的name和email字段中

insert into test_user(name,email) select name,qq_mail from student;

4.update

用来修改数据库里的数据

update 表名 set 值1=xx，值2=xx where [条件表达式]

5.delete

用来删除数据库中的某个字段

delete from 表名 where [条件表达式]

6.distinct

用来去除数据库中重复的数据

select distinct 字段名(*) from 表名

7.order by

用于查询的时候可以对数据进行升序/降序排列

select 字段名 from 表名 order by desc(降序)/asc(升序) 默认升序

8.group by

用于数据库查询的时候对数据进行分组

select 字段名 from 表名 group by 字段名1  按照字段名1进行分组

9.having

和where一样是进行条件筛选的，但是区别是where是先把所有符合条件的数据进行筛选，筛选之后再进行分组操作，而having是先进行分组操作，分组之后再对分组后的数据进行筛选

关于数据库中having和where的区别
1.首先用having条件来查询的语句是先分组再判断的
2.having可以使用统计函数，where不可以
3.having可以指定查询的字段别名，where不可以
4.关于关键字执行顺序 where -> group by -> having -> order by -> limit

10.limit

limit是常用于分类查询的一个关键字

select 字段名 from 表名 limit 1 , 5;这是从第二条数据开始查，查询五条数据

第一个参数是从第几行开始查，第二个关键字是查询几条数据

select 字段名 from 表名 limit  5;   这是默认从第一行开始查，查询5条数据，当默认第一行开始的时候是limit 0 , 5 所以可以省略第一个参数

11.offset

这个关键字常用于limit中 表示跳过几条数据

select 字段名 from 表名 limit 1 , 5 offset 3;这是从第二条数据开始查，查询五条数据

跳过3条数据，也就是说查询第5，6条数据

与select 字段名 from 表名 limit 5,2; (从第五条数据开始查询，查询两条数据)查询结果是一样的

12.and

常用于条件筛选中，表示同时满足and前后的条件

select 字段名 from 表名 where 字段名1>xx and 字段名2=yy;

筛选出既满足字段名1>xx的记录和字段名2=yy的记录取两者的交集

13.or

常用于条件筛选中，表示or前后的条件满足其一即可

select 字段名 from 表名 字段名1>xx or字段名2=yy;

筛选出满足字段名1>xx的记录或者是字段名2=yy的记录

14.like

常用于数据库中模糊查询

select 字段名 from 表名 where name="刘%";

查询name中刘是第一个字的所有数据

select 字段名 from 表名 where name="刘_"

查询name中刘是第一个字，一共2个字的所有数据;

_代表匹配一个任意字符，%表示匹配多个任意字符

15.union

常用于数据库中的组合查询

SELECT 字段名 FROM 表名1
UNION
SELECT 字段名 FROM 表名2;

如果表1有a,b,c,d四条数据，表二有a,e,f,g四条数据，那么查询结果就是a,b,c,d,e,f,g，是会去掉相同的数据的

16.union all

常用于数据库中的组合查询

SELECT 字段名 FROM 表名1
UNION ALL
SELECT 字段名 FROM 表名2;

如果表1有a,b,c,d四条数据，表二有a,e,f,g四条数据，那么查询结果就是a,b,c,d,a,e,f,g，是不会去掉相同的数据的

17.left join

左连接查询，常用于多表联查，将两个表联合查询，查询出来的结果会包括左表的全部记录和右表表中符合左表字段的记录，如果左表中对应的数据在右表查不到的话在右表中对应字段的值会显示为NULL

select 字段名 from 表名1 left join 表名2 on 表名1.字段名=表名2.字段名;

18.right join

右连接查询，常用于多表联查，将两个表联合查询，查询出来的结果会包括右表的全部记录和左表中符合右表字段的记录，如果右表中对应的数据在左表查不到的话在左表中对应字段的值会显示为NULL

select 字段名 from 表名1 right join 表名2 on 表名1.字段名=表名2.字段名;

19.inner join

内连接查询，常用于多表联查，会把两个表中共有的字段名及数据显示出来

select * from 表名1 inner join 表名2 on 表名1.字段名=表名2.字段名;

20.Alter

修改字段长度常用语句

alter table 表名 modify 字段名 字段类型;

例如:

alter table qtline modify qtl_bidernote VARCHAR2(4000);

21.rename

用于修改字段名称

ALTER TABLE  表名 RENAME COLUMN 原字段名 TO 改后字段名;

22.add

添加字段常用sql

alter table 表名 add  字段名  字段类型(字段长度)

23.处理主键自增不连续的情况

方法一

delete from test;

alter table `test` auto_increment=1

(好处，可以设置AUTO_INCREMENT 为任意值开始)

提示：如果表列和数据很多，速度会很慢，如90多万条，会在10分钟以上。

方法二

truncate table test

(好处，简单，AUTO_INCREMENT 值重新开始计数)

一般情况下我们使用第二个就可以了，记住以上情况都是彻底删除所有记录。

24.index

index索引:便于更加快速高效的查询数据

创建普通索引

3.ALTER TABLE 想添加索引的表名 ADD INDEX 索引名 (想添加索引的字段名): 添加普通索引，索引值可出现多次。

创建主键索引

1.ALTER TABLE 表名 ADD PRIMARY KEY (字段名): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

创建唯一索引
2.ALTER TABLE 表名 ADD UNIQUE 索引名 (字段名): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

创建全文索引(大多数数据库一般不会使用全文索引，取决于DBMS，一般用于模糊查询大量文本数据时)
4.ALTER TABLE 表名 ADD FULLTEXT 索引名 (字段名):该语句指定了索引为 FULLTEXT ，用于全文索引。

删除索引

ALTER TABLE 表名 DROP INDEX (索引名);

查看索引

SHOW INDEX FROM表名\G

25.视图

视图可以这么理解,就相当于数据库中的一张表,只不过这张表是依赖于其他表存在的

语法CREATE OR REPLACE VIEW 视图名(视图字段1,视图字段2,视图字段3,视图字段4) as 查询语句(将查询出来的内容依次赋给视图)

举例:CREATE OR REPLACE VIEW find (ID,RequestAmountMain,RequestAmountSubSum,multiple) as SELECT A.ID,A.RequestAmount,SUM(B.RequestAmount),SUM(B.RequestAmount)/A.RequestAmount as multiple FROM bpbizpaymentrequest A,BPBIZPAYMENTREQRECEIVER B WHERE A.ID=B.PARENTID GROUP BY A.ID,A.RequestAmount

四、数据库中的函数

    curdate() -- 获取当前日期: 年月日
    curtime() -- 获取当前时间: 时分秒
    sysdate()/now() -- 获取当前日期+时间, 年月日 时分秒
    year('2020-8-10'): 返回日期中的年份, 2020
    month('2020-8-10'): 返回日期中的月份, 8
    day('2020-8-10'): 返回日期中的天数, 10
    hour('2020-8-10 12:34:56'): 返回时间中的小时, 12
    minute('2020-8-10 12:34:56'): 返回时间中的分钟数, 34
    second('2020-8-10 12:34:56'): 返回时间中的秒值, 56

可以使用到sql语句中

如 select * from user where user_time=curdate();

数据库中的查询常用函数

count(列名 | *): 
		count(列名): 表示统计当前列的值有多少个(不统计null值)
		count(*): 以行为单位,统计查询结果中有多少行记录
max(列名): 表示统计当前这一列中所有值中的最大值
min(列名): 表示统计当前这一列中所有值中的最小值
sum(列名): 表示统计当前这一列中所有值的和(也就是说会将这一列中所有的值加在一起返回)
avg(列名): 表示统计当前这一列中所有值的平均值(这一列中所有值的和 / 不是null值的个数 )

五、关于数据库的备份

1、备份单个数据库
在cmd窗口（未登录、未连接到mysql服务器的界面）中，可以通过如下命令对指定的数据库进行备份：
mysqldump -u用户名 -p密码 库名 > 备份文件的位置
示例1: 对db40库中的数据(表,表记录)进行备份,备份到 d:/db40.sql 文件中
mysqldump -uroot -proot db40 > d:/db40.sql

2、备份多个数据库（比如备份两个数据库）
在cmd窗口（未登录、未连接到mysql服务器的界面）中
mysqldump -u用户名 -p密码 --databases 库名1 库名2 ... > 备份文件的位置
示例2: 对db20 和 db40 库中的数据进行备份,备份到 d:/db2040.sql 文件中
mysqldump -uroot -proot --databases db20 db40 > d:/db2040.sql
注意: 1)如果输入命令回车之后没有提示错误,就说明备份成功了!
2)备份多个数据库,不仅会备份这个库中的表和表记录,同时会备份库本身!
或者如果想备份mysql服务器中的所有的库以及库中的表和表记录,可以通过如下命令:
mysqldump -uroot -proot -A > d:/dball.sql
输入完后回车如果没有提示错误(error是错误,警告不是错误可以忽略),即表示备份成功!

1、恢复数据库方式一(单个数据库)：
在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行恢复
mysql -u用户名 -p密码 库名 < 备份文件的位置
示例1: 将 d:/db40.sql 文件中的数据恢复到 db60 库中
1) 先在cmd窗口中(已登录的状态下), 先创建db60库
	create database db60 charset utf8;
2) 再回到cmd窗口中(未登录的状态下), 执行下面恢复的命令
	mysql -uroot -proot db60 < d:/db40.sql
2、恢复数据库方式二(多个数据库)
在cmd窗口中(已登录的状态下)，可以通过source命令来执行指定位置的sql文件中的sql语句：
	source sql文件的位置
示例2: 将 d:/db40.sql 文件中的数据恢复到 db80 库中
1) 先创建db80库, 并进入到db80库
	create database db80 charset utf8;
	use db80;
2) 再通过source命令执行 d:/db40.sql 文件中的sql语句
	source d:/db40.sql
示例3: 将 d:/db2040.sql 文件中的数据恢复回来
1) 将db20,db40库删除(模拟数据丢失)
	drop database db20;
	drop database db40;
2) 再通过source命令执行 d:/db2040.sql 文件中的sql语句
	source d:/db2040.sql

关于数据库中行数统计，无论是MySQL还是Oracle，都有一个函数可以使用，那就是COUNT

尝尝问道以下问题

1、COUNT有几种用法？

2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？

3、COUNT(1)和COUNT(*)之间有什么不同？

4、COUNT(1)和COUNT(*)之间的效率哪个更高？

5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*)

6、MySQL的MyISAM引擎对COUNT(*)做了哪些优化？

7、MySQL的InnoDB引擎对COUNT(*)做了哪些优化？

8、上面提到的MySQL对COUNT(*)做的优化，有一个关键的前提是什么？

9、SELECT COUNT(*) 的时候，加不加where条件有差别吗？

10、COUNT(*)、COUNT(1)和COUNT(字段名)的执行过程是怎样的？

认识COUNT

关于COUNT函数，在MySQL官网中有详细介绍：

简单翻译一下：

1、COUNT(expr) ，返回SELECT语句检索的行中expr的值不为NULL的数量。结果是一个BIGINT值。

2、如果查询结果没有命中任何记录，则返回0

3、但是，值得注意的是，COUNT(*) 的统计结果中，会包含值为NULL的行数。

 总结一点: 就是COUNT(*)会统计包含NULL行的数据, 而COUNT(列名)不会统计包含NULL行的数据

COUNT(*)和COUNT(1)

介绍完了COUNT(*)，接下来看看COUNT(1)，对于，这二者到底有没有区别，网上的说法众说纷纭。

有的说COUNT(*)执行时会转换成COUNT(1)，所以COUNT(1)少了转换步骤，所以更快。

还有的说，因为MySQL针对COUNT(*)做了特殊优化，所以COUNT(*)更快。

那么，到底哪种说法是对的呢？看下MySQL官方文档是怎么说的：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

画重点：same way , no performance difference。意思说 同样的方法，没有性能差异。所以，对于COUNT(1)和COUNT(*)，MySQL的优化是完全一样的，根本不存在谁比谁快！ 

那既然COUNT(*)和COUNT(1)一样，建议用哪个呢？

建议使用COUNT(*)！因为这个是SQL92定义的标准统计行数的语法，而且本文只是基于MySQL做了分析，关于Oracle中的这个问题，也是众说纷纭的呢。
 

COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别

COUNT(常量) 和 COUNT(*)表示的是直接查询符合条件的数据库表的行数。而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数

除了查询得到结果集有区别之外，COUNT(*)相比COUNT(常量) 和 COUNT(列名)来讲，COUNT(*)是SQL92定义的标准统计行数的语法，因为他是标准语法，所以MySQL数据库对他进行过很多优化。

COUNT(*)的优化

COUNT(*)是SQL92定义的标准统计行数的语法, MySQL数据库对他进行过很多优化. 

MyISAM和InnoDB有很多区别，其中有一个关键的区别和我们接下来要介绍的COUNT(*)有关，那就是MyISAM不支持事务，MyISAM中的锁是表级锁；而InnoDB支持事务，并且支持行级锁。

因为MyISAM的锁是表级锁，所以同一张表上面的操作需要串行进行，所以，MyISAM做了一个简单的优化，那就是它可以把表的总行数单独记录下来，如果从一张表中使用COUNT(*)进行查询的时候，可以直接返回这个记录下来的数值就可以了，当然，前提是不能有where条件。

MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用，那是因为MyISAM数据库是表级锁，不会有并发的数据库行数修改，所以查询得到的行数是准确的。

对于InnoDB来说，就不能做这种缓存操作了，因为InnoDB支持事务，其中大部分操作都是行级锁，所以可能表的行数可能会被并发修改，那么缓存记录下来的总行数就不准确了。

COUNT(*)的目的只是为了统计总行数，所以，他根本不关心自己查到的具体值，所以，他如果能够在扫表的过程中，选择一个成本较低的索引进行的话，那就可以大大节省时间。

MySQL数据库对于COUNT(*)的优化，这些优化的前提都是查询语句中不包含WHERE以及GROUP BY条件。

 

COUNT(字段)

查询就比较简单粗暴了，就是进行全表扫描，然后判断指定字段的值是不是为NULL，不为NULL则累加。

相比COUNT(*)，COUNT(字段)多了一个步骤就是判断所查询的字段是否为NULL，所以他的性能要比COUNT(*)慢。

总结

本文介绍了COUNT函数的用法，主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。

因为COUNT(*)是SQL92定义的标准统计行数的语法，所以MySQL对他进行了很多优化，MyISAM中会直接把表的总行数单独记录下来供COUNT(*)查询，而InnoDB则会在扫表的时候选择最小的索引来降低成本。当然，这些优化的前提都是没有进行where和group的条件查询。

在InnoDB中COUNT(*)和COUNT(1)实现上没有区别，而且效率一样，但是COUNT(字段)需要进行字段的非NULL判断，所以效率会低一些。

因为COUNT(*)是SQL92定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT(*)查询表的行数！

参考资料：

不就是SELECT COUNT语句吗，竟然能被面试官虐的体无完肤
《极客时间——MySQL实战45讲》