数据库基本语句和关键字详解_数据库语句

一、数据库的基本语句

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

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

oracle:ALTER TABLE table_name ADD column_name data_type constraint

pgsql:ALTER TABLE 表名 ADD COLUMN 字段名 字段类型(长度)

删除字段常用sql

alter table 表名 drop column 列名

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