java SQL常用语句总结大全（超详细）

数据库

数据库定义：

**数据库：**存储数据的仓库. 其本质是一个文件系统，数据库按照特定的格式将数据存储到文件中，使用者可以对数据库中的数据进行增加，修改，删除及查询操作。

存储位置

优点

缺点

内存例如: 集合,实体类对象数据是放在内存中

存取速度很快

不能永久的保存,程序停止时,内存释放数据消失

文件例如: IO流,把数据存在文件中

可以永久操作数据

每种不同类型的文件都有自己存放数据的格式,数据的管理和维护不方便

数据库

数据库按照特定的格式将数据存储起来，通过SQL或命令可以方便的对大量数据进行增、删、改、查操作。

数据库通常是要花钱的

常见的数据库如下：

数据库名称

描述

MYSQL

开源免费的,小型的数据库.原产自瑞典AB公司,后来被SUN公司收购, 再后来,Oracle收购SUN,Oracle收购后. MySQL6.x版本也开始收费。

Oracle

收费的大型数据库,Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。

DB2

IBM公司的数据库产品,收费的。常应用在银行系统中.

SQLServer

MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。

SQLite

嵌入式的小型数据库，应用在手机端。

数据库管理系统（DBMS）

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问和操作数据库中表内的数据。

数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个项目创建一个数据库。为保存项目中实体的数据，一般会在数据库创建与实体对应的表，以保存程序中实体的数据。数据库管理系统、数据库和表的关系如图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KJxBWMDH-1598621923724)(E:/%E5%B0%B1%E4%B8%9A%E7%8F%AD%E8%B5%84%E6%96%99/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%B5%84%E6%96%99/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%92%8CMybatis-AB%E6%A8%A1%E5%BC%8F%E8%AE%B2%E4%B9%89/day01-mysql/%E8%AE%B2%E4%B9%89/img/1590373819343.png)]

先有数据库管理系统 → 再有数据库 → 再有表 → 再有数据

Mysql服务器

启动Mysql服务器的方式：

方式1: windows服务方式启动此电脑右击 --> 管理–>服务–> MySQL56

方式2: DOS命令方式启动

启动服务的命令: net start mysql56

停止服务的命令: net stop mysql

连接Mysql服务器的方式：

方式1: DOS连接Mysql

1将DOS命令窗口当做Mysql的客户端连接并操作Mysql数据库:
2	登录命令:
3		mysql -u用户名 -p密码	
4	mysql -u用户名 -p
5		密码
6	
7	mysql -h主机地址 -u用户名 -p密码
8	
9	mysql -h主机地址 -u用户名 -p
10		密码
11		
12登录的方式有很多种,使用任意一种即可.
13    用户名(根用户): root
14    密码(根密码): root

方式2：SQLyog连接Mysql

SQLyog 是业界著名的 Webyog 公司出品的一款简洁高效、功能强大的图形化 MySQL 数据库管理工具。使用SQLyog 可以快速直观地让您从世界的任何角落通过网络来操作远端的 MySQL 数据库。

SQL

**SQL介绍：**结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。不区分大小写！！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ce8LFCFs-1598621923727)(E:/%E5%B0%B1%E4%B8%9A%E7%8F%AD%E8%B5%84%E6%96%99/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%B5%84%E6%96%99/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%92%8CMybatis-AB%E6%A8%A1%E5%BC%8F%E8%AE%B2%E4%B9%89/day01-mysql/%E8%AE%B2%E4%B9%89/img/1590132052749.png)]

SQL语句分类(4类)

分类

描述

关键字

DDL(Data Definition Language)
数据定义语言

用来定义数据库对象:数据库,表,列

create,drop,alter等

DML(Data Manipulation Language)
数据操作语言

用来对数据库中表的数据进行增删改

insert,delete, update等

DCL(Transaction Control Language)
数据控制语言

用来定义数据库的访问权限和安全级别,及创建用户

GRANT, REVOKE等

TCL(Transaction Control Language)
事务控制语言

用于控制数据库的事务操作

COMMIT,SAVEPOINT,ROLLBACK等

DQL(Data Query Language)
数据查询语言 (掌握)

DQL语言并不是属于MYSQL官方的分类,但是对数据库的操作最多就是查询，所以我们的程序员把查询语句的语句称作为DQL语言

SELECT 等

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写。
3种注释

注释的语法

说明

–空格

单行注释,通用的注释

单行注释,只有mysql中可以使用

/* */

多行注释,

DDL语句（定义数据数据库,表,列）

DDL操作数据库

1创建数据库
2-- 需求: 创建名称为db1的数据库
3	create database db1;
4-- 需求: 当db2数据库不存在时创建名称为db2数据库
5	create database if not exists db2; 
6-- 需求: 创建名称为db3数据库,并指定字符集为gbk
7	create database db3 character set gbk;
8查看数据库
9-- 需求:查询Mysql数据管理系统中所有的数据库
10	show databases;
11-- 需求:查询db3数据库的建库语句
12	show create database db3;
13修改数据库(了解)
14-- 需求:将db3数据库的字符集改成utf8
15	alter database db3 default character set utf8;
16-- 注意:utf8没有中间的横杠
17-- 从数据库管理系统中删除名称为db2的数据库
18	drop database db2;
19-- 查看正在使用的数据库
20	select database();
21-- 使用db1数据库
22	use db1;
23

DDL操作表

1创建表
2-- 需求:创建student表包含
3--		id整数
4--		name变长字符串长20
5--		sex性别定长型1
6--		birthday字段日期类型
7create table student(
8     id int,   -- 编号
9     name varchar(20),  -- 姓名
10     sex char(1),   -- 性别
11     birthday date  -- 生日
12);
13-- 需求:创建一个student2表,结构与student相同
14	create table student2 like student;
15	
16查看表
17-- 需求:查看mysql数据库中的所有表
18	show tables;
19-- 需求:查看student表的结构
20	desc student;
21-- 需求:查看student表的建表语句
22	show create table student;
23	
24修改表结构(了解)
25  添加表列
26-- 需求:为student表添加一个新的字段nianling,类型为varchar(2)
27	alter table student add nianling varchar(2);
28  修改列类型  
29-- 需求:将student表中的nianling字段的类型改为int(11)
30	alter table student modify nianling int(11);
31  修改列名 
32-- 需求:将student表中的nianling字段名改成age,类型int(2)
33	alter table student change nianling age int(2);
34  删除列  
35-- 需求:将student表中的age字段从表中删除
36  	alter table student drop age;
37  修改表名  
38-- 需求:将student表名称改为stu
39	rename table student to stu;
40  修改字符集
41-- 需求:将stu表的字符集修改为gbk, 再将其修改回来
42	alter table stu character set gbk;
43	alter table stu character set utf8;
44	
45删除表
46-- 需求:直接删除student2表
47	drop table student2;
48-- 需求:判断表是否存在,若存在则删除表
49	drop table if exists student2;
50

DML语句（操作表中数据，增删改）

1新增记录
2   新增全部字段数据
3-- 罗列所有的字段
4  INSERT INTO 表名 (字段名1, 字段名2, 字段名3…)  VALUES (值1, 值2, 值3);
5-- 不写字段名
6  INSERT INTO 表名 VALUES (值1, 值2, 值3…);
7   新增部分字段数据
8INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 
9关键字说明及注意事项如下：
101. 关键字说明
11   INSERT INTO 表名 //表示往哪张表中添加数据
12   (字段名1, 字段名2, …)  //要给哪些字段设置值
13   VALUES (值1, 值2, …); //设置具体的值
142. 注意
15   值与字段个数,顺序，类型都必须对应
16   值的数据大小必须在字段的长度范围内
17   除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
18   如果要插入空值，可以不写字段，或者插入null
19-- 添加一条记录(罗列所有字段方式): id为1,姓名为tom,性别为男,生日为2020-05-20
20	insert into stu (id,sex,name,birthday) values (1,'男','tom','2020-05-20');
21	
22蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中 
23-- 1.快速创建一张stu2表,结构与stu一样
24	create table stu2 like stu;
25-- 2.查询stu2表结构和表中的数据
26	desc stu2;
27	select * from stu2;
28-- 3.蠕虫复制stu表中的数据到stu2表中
29	insert into stu2 select * from stu;
30
31更新表记录
32-- 不带条件修改数据  
33 UPDATE 表名 SET 字段名=值,字段名=值;
34-- 带条件修改数据  
35  UPDATE 表名 SET 字段名=值 WHERE 字段名=值 AND字段名称=值;
36-- 需求:修改表中的性别为'女'
37	update stu set sex='女'; 
38-- 需求:将表中id为1的性别改为男
39	update stu set sex='男' where id=1;
40	
41删除表记录
42-- 不带条件删除数据 
43  DELETE FROM 表名;
44-- 根据条件删除数据  
45  DELETE FROM 表名 WHERE 字段名=值;
46-- truncate删除表记录  
47  TRUNCATE TABLE 表名;
48delete和truncate的区别：
49 	delete是将表中的数据一条一条删除
50 	truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样
51-- 需求:删除id为2的学生信息
52	delete from stu where id=2;
53-- 需求:删除整张表中的所有数据
54	delete from stu;
55	truncate table stu;
56

DQL语句（查询表的数据，查）

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

创建商品表：product

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-txGCo9RG-1598621923730)(C:\Users\Ali\AppData\Roaming\Typora\typora-user-images\1598618222305.png)]$

1无条件查询：
2
3查询所有
4-- 需求:查询所有的商品.
5SELECT * FROM product;
6查询指定列
7-- 需求:查询商品名和商品价格.
8SELECT pname,price FROM product;
9别名查询
10-- 需求3.别名查询.使用的关键字是as（as可以省略的）.	
11--   3.1表别名:查询商品名称和价格
12	SELECT pro.pname,pro.price FROM product AS pro;
13--   3.2列别名：查询商品名称和价格
14	SELECT pname AS "商品名称",price "商品价格" FROM product;
15去重复查询
16-- 需求:查看商品表中有那些价格（去重）.
17select distinct price from product;	
18-- 需求:将所有商品的价格+10元进行显示. (查询结果可参与运算)
19select *,IFNULL(price,0)+10 from product;
20====================================================================
21条件查询：
22-- 格式
23select 字段，...from 表名 where 条件;
24比较运算符
25-- 需求：查询价格大于3000的商品
26select * from product where price>3000;
27-- 需求：查询价格不等于3000的商品
28select * from product where price！=3000;
29select * from product where price<>3000;
30逻辑运算符
31and(&&) 多个条件同时满足 
32or(||) 多个条件其中一个满足 
33not(!) 不满足
34-- 需求：查询价格大于2000，且种类为c001的商品
35select * from product where price>2000 and categroy_id='c001';
36in关键字
37-- 格式
38select * from 表名 where 字段 in（数据1，数据2，...）;
39-- 需求：查询pid是1，3，4的商品
40select * from product where pid in(1,3,4);
41范围
42-- 格式
43select * from 表名 where 字段名 between 值1 and 值2；
44-- 需求：查询价格大于等于1000，且小于等于5000的商品信息
45select * from product where price between 1000 and 5000；
46模糊搜索like
47	%  	: 模糊所有
48	_   : 模糊一位
49例如:
50	"马%"  匹配以"马"字开头
51	"%马"  匹配以"马"字结尾
52	"_马"  匹配第二个字是"马"字的 
53	"__马%"  匹配第三个字是"马"字的
54	"%马%"	匹配包含"马"字的
55-- 格式
56select * from 表名 where 字段 like 条件;
57-- 需求：查询品牌包含花的商品
58select * from product where pname like '%花%';
59================================================================
60排序： order by
61单列排序
62asc(默认) : 升序   
63desc : 降序
64-- 格式
65select * from 表名 where 条件  order by 字段名1 desc/asc , 字段名2 [desc | asc]... ;
66-- 需求：查询所有数据,使用价格降序排序
67select * from product order by price desc;
68组合排序
69-- 需求：查询所有数据,使用价格降序，id升序排序
70select * from product order by price desc,pid asc;
71==============================================================
72聚合函数
73count() : 计数
74	IFNULL(字段名,0) : 如果指定字段的值为null,则使用0表示
75sum() : 求和
76max() : 求最大值
77min() : 求最小值
78avg() : 求平均值
79-- 查询商品总数
80select count（*） from product;
81由于对于NULL的记录不会统计，如果记录数据为null，给个默认值，这样统计的数据就不会遗漏
82select count（ifnull(categroy_id,0)） from product;
83-- 查询价格总数
84select sum(price) from product;
85=============================================================
86分组: group by 
87将分组字段结果中相同内容作为一组
88
89
90-- 需求1：查询购买的每种商品的总价。
91select product,sum(price) from orders group by product;

在这里插入图片描述

1-- 需求2：根据商品和单价进行分组 查询每种商品的总价  
2-- group by 多字段：只有多个字段相同，才能划分为一组
3select product ,sum(price) from orders group by product,price;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TDHpw0vz-1598621923735)(E:/%E6%95%99%E5%AD%A6%E8%A7%86%E9%A2%91/project/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%A7%86%E9%A2%91/%E8%A7%86%E5%B1%8F%E7%AC%94%E8%AE%B0/day1-%E4%B8%8A%E8%AF%BE%E6%BC%94%E7%A4%BA%E7%AC%94%E8%AE%B0/day1-%E4%B8%8A%E8%AF%BE%E6%BC%94%E7%A4%BA%E7%89%88%E6%9C%AC/imgs/image-20200828163315298.png)]

1-- 需求3：查询每一种商品的总价大于30的商品，并显示总价
2select product,sum(price) from orders group by product having sum(price) >30;

[外链图片转存中…(img-eWDYvY0W-1598621923733)]

1-- 需求2：根据商品和单价进行分组 查询每种商品的总价  
2-- group by 多字段：只有多个字段相同，才能划分为一组
3select product ,sum(price) from orders group by product,price;

[外链图片转存中…(img-TDHpw0vz-1598621923735)]

1-- 需求3：查询每一种商品的总价大于30的商品，并显示总价
2select product,sum(price) from orders group by product having sum(price) >30;