Java数据库篇3——SQL

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库 查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统

1、SQL分类

分类	说明
数据定义语言	简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列 等
数据操作语言	简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新
数据查询语言	简称DQL(Data Query Language)，用来查询数据库中表的记录
数据控制语言	简称DCL(Date Control Language)，用来定义数据库的访问权限和安全级别， 及创建用户

2、Mysql数据类型

3、DDL（数据定义语言）

3.1、操作数据库

查询表

#查看所有表
Show tables;
#查看指定表的内容
Desc 表名;
#查看表的创建语句（字符集）
Show create table 表名;

创建表

Create table 表名(
    列名1 数据类型1，
    列名2 数据类型2，
    列名3 数据类型3
);

复制表1

Create table 表名2 like 表名1;

修改表

#修改表名
Alter table 原表名 rename to 新表名;
#修改表的字符集
Alter table 表名 character set 字符集;
#在表内添加一行
Alter table 表名 add 列名 数据类型;
#修改列名
Alter table 表名 change 原列名 新列名 数据类型（可修改）;
#修改列的数据类型
Alter table 表名 modify 列名 新数据类型;
#删除列
Alter table 表名 drop 列名;

删除表

Drop table 表名;
#存在此表才删除
Drop table if exists 表名;

3.2、DDL操作表

查询表

#查看所有表
Show tables;
#查看指定表的内容
Desc 表名;
#查看表的创建语句（字符集）
Show create table 表名;

创建表

创建表
Create table 表名(
	列名1 数据类型1，
	列名2 数据类型2，
	列名3 数据类型3
);

复制表1

Create table 表名2 like 表名1;

修改表

#修改表名
Alter table 原表名 rename to 新表名;
#修改表的字符集
Alter table 表名 character set 字符集;
#在表内添加一行
Alter table 表名 add 列名 数据类型;
#修改列名
Alter table 表名 change 原列名 新列名 数据类型（可修改）;
#修改列的数据类型
Alter table 表名 modify 列名 新数据类型;
#删除列
Alter table 表名 drop 列名;

删除表

Drop table 表名;
#存在此表才删除
Drop table if exists 表名;

4、DML（数据操作语言）

4.1、添加数据

#添加单个数据
Insert into 表名 (列1，列2……列n) values (值1，值2……值n);
#添加多个数据
Insert into 表名 (列1，列2……列n) values (值1，值2……值n),
(值1，值2……值n),
(值1，值2……值n);

• 添加表中所有列数据的时候，可以省略列这个括号
• 列和值要一一对应
• Null可以作为占位符使用
• 值除了数字类型和null，其他类型都要用引号引起来

4.2、修改数据

Update表名 set 列=值，列=值 where 条件语句;

• 值除了数字类型和null，其他类型都要用引号引起来
• 不加条件就修改所有的记录

4.3、删除数据

Delete from 表名 where 条件语句;

• 不加条件删除所有的记录

• truncate table 表名；删除表，创建一个一摸一样的空表

5、DQL（数据查询语言）

5.1、语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

5.2、基础查询

#多字段查询
Select 列名1，列名2 from 表名；
#去重查询
Select distinct 列名1，列名2 from 表名;(只有结果集完全相同才会被去重)
#计算列的查询
Select 列名1，列名2，列名1+列名2 from 表名;(一般都是数值相加，null+任何数都是为null)
#Ifnull函数
Select 列名1，列名2，ifnull（列名1，0）+ ifnull（列名2，0） from 表名；
#查询所有列
Select * from 表名；

5.3、条件查询

• >

• <

• =

• <>不等于

• !=不等于

• And等同于&&

• Or等同于||

• Between，，，and，，，包含边界，not不包含

• In（值1，值2，，，值n）集合

• Null不能用=判断要用is和is not

• As起别名，数据库，表，都可以起别名，且as可以省略

select 列名 from 表名 where 条件表达式

5.4、模糊查询

• _占位符，单个任意字符
• %占位符，任意多个任意字符

SELECT * FROM address WHERE home LIKE ‘_澈’;

5.5、排序查询

• 升序ASC
• 降序DESC

Select 列1，列2 from 表名 order by 列1 排序方式1，列2，排序方式2;

5.6、聚合函数

5.6.1、count函数

#count(*)：返回表中满足where条件的行的数量
select count(*) from student;
#count(列)：返回列值非空的行的数量
select count(score) from student;
#count(distinct 列)：返回列值非空的、并且列值不重复的行的数量
select count(distinct score) from student;

5.6.2、max和min函数

统计列中的最大最小值

select max(score) from student;
select min(score) from student;

如果统计的列中只有NULL值，那么MAX和MIN就返回NULL

5.6.3、sum和avg函数

求和与求平均

select sum(score) from student;
select avg(score) from student;
select avg(ifnull(score,0)) from student;

表中列值为null的行不参与计算，要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换

5.7、分组查询

分组查询的一定是分组字段或者聚合函数，否则将没有意义

Select 列1，列2 from 表名 group by 列名;
Select 列1，列2，列名 from 表名 group by 列名 having 条件语句;

Where分组前限定，having在分组后限定，where不可以判断聚合函数，having可以

5.8、分页查询

#Limit 开始索引 每页的条数；
Select * from 表名 limit 开始索引(每页条数*当前页码减一) 每页条数;

6、DCL（数据控制语言）

用来管理用户和管理权限

用户的信息都放在mysql数据库下的user表中

6.1、管理用户

• localhost
• %

#添加用户
Create user '用户名'@'主机名' identified by '密码';
#修改用户
Alter user '用户名'@'主机名’ identified by '密码';
#删除用户
Drop user '用户名'@'主机名';

6.2、权限管理

#查询权限
Show grants for '用户名'@'主机名';
#添加权限
Grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
#删除权限
Revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';

均可使用通配符，权限的通配符用all不是*