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

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

Mysql服务器

启动Mysql服务器的方式：

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

方式2: DOS命令方式启动

​ 启动服务的命令: net start mysql56

​ 停止服务的命令: net stop mysql

连接Mysql服务器的方式：

方式1: DOS连接Mysql

将DOS命令窗口当做Mysql的客户端连接并操作Mysql数据库:
	登录命令:
		mysql -u用户名 -p密码	
	mysql -u用户名 -p
		密码
	
	mysql -h主机地址 -u用户名 -p密码
	
	mysql -h主机地址 -u用户名 -p
		密码
		
登录的方式有很多种,使用任意一种即可.
    用户名(根用户): root
    密码(根密码): root

方式2：SQLyog连接Mysql

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

SQL

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

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通用语法

1. SQL语句可以单行或多行书写，以分号结尾。

2. 可使用空格和缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写。

4. 3种注释

   注释的语法	说明
   –空格	单行注释,通用的注释
   #	单行注释,只有mysql中可以使用
   /* */	多行注释,

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

DDL操作数据库

创建数据库
-- 需求: 创建名称为db1的数据库
	create database db1;
-- 需求: 当db2数据库不存在时创建名称为db2数据库
	create database if not exists db2; 
-- 需求: 创建名称为db3数据库,并指定字符集为gbk
	create database db3 character set gbk;
查看数据库
-- 需求:查询Mysql数据管理系统中所有的数据库
	show databases;
-- 需求:查询db3数据库的建库语句
	show create database db3;
修改数据库(了解)
-- 需求:将db3数据库的字符集改成utf8
	alter database db3 default character set utf8;
-- 注意:utf8没有中间的横杠
-- 从数据库管理系统中删除名称为db2的数据库
	drop database db2;
-- 查看正在使用的数据库
	select database();
-- 使用db1数据库
	use db1;

DDL操作表

创建表
-- 需求:创建student表包含
--		id整数
--		name变长字符串长20
--		sex性别定长型1
--		birthday字段日期类型
create table student(
     id int,   -- 编号
     name varchar(20),  -- 姓名
     sex char(1),   -- 性别
     birthday date  -- 生日
);
-- 需求:创建一个student2表,结构与student相同
	create table student2 like student;
	
查看表
-- 需求:查看mysql数据库中的所有表
	show tables;
-- 需求:查看student表的结构
	desc student;
-- 需求:查看student表的建表语句
	show create table student;
	
修改表结构(了解)
  添加表列
-- 需求:为student表添加一个新的字段nianling,类型为varchar(2)
	alter table student add nianling varchar(2);
  修改列类型  
-- 需求:将student表中的nianling字段的类型改为int(11)
	alter table student modify nianling int(11);
  修改列名 
-- 需求:将student表中的nianling字段名改成age,类型int(2)
	alter table student change nianling age int(2);
  删除列  
-- 需求:将student表中的age字段从表中删除
  	alter table student drop age;
  修改表名  
-- 需求:将student表名称改为stu
	rename table student to stu;
  修改字符集
-- 需求:将stu表的字符集修改为gbk, 再将其修改回来
	alter table stu character set gbk;
	alter table stu character set utf8;
	
删除表
-- 需求:直接删除student2表
	drop table student2;
-- 需求:判断表是否存在,若存在则删除表
	drop table if exists student2;

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

新增记录
   新增全部字段数据
-- 罗列所有的字段
  INSERT INTO 表名 (字段名1, 字段名2, 字段名3…)  VALUES (值1, 值2, 值3);
-- 不写字段名
  INSERT INTO 表名 VALUES (值1, 值2, 值3…);
   新增部分字段数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 
关键字说明及注意事项如下：
1. 关键字说明
   INSERT INTO 表名 //表示往哪张表中添加数据
   (字段名1, 字段名2, …)  //要给哪些字段设置值
   VALUES (值1, 值2, …); //设置具体的值
2. 注意
   值与字段个数,顺序，类型都必须对应
   值的数据大小必须在字段的长度范围内
   除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
   如果要插入空值，可以不写字段，或者插入null
-- 添加一条记录(罗列所有字段方式): id为1,姓名为tom,性别为男,生日为2020-05-20
	insert into stu (id,sex,name,birthday) values (1,'男','tom','2020-05-20');
	
蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中 
-- 1.快速创建一张stu2表,结构与stu一样
	create table stu2 like stu;
-- 2.查询stu2表结构和表中的数据
	desc stu2;
	select * from stu2;
-- 3.蠕虫复制stu表中的数据到stu2表中
	insert into stu2 select * from stu;

更新表记录
-- 不带条件修改数据  
 UPDATE 表名 SET 字段名=值,字段名=值;
-- 带条件修改数据  
  UPDATE 表名 SET 字段名=值 WHERE 字段名=值 AND字段名称=值;
-- 需求:修改表中的性别为'女'
	update stu set sex='女'; 
-- 需求:将表中id为1的性别改为男
	update stu set sex='男' where id=1;
	
删除表记录
-- 不带条件删除数据 
  DELETE FROM 表名;
-- 根据条件删除数据  
  DELETE FROM 表名 WHERE 字段名=值;
-- truncate删除表记录  
  TRUNCATE TABLE 表名;
delete和truncate的区别：
 	delete是将表中的数据一条一条删除
 	truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样
-- 需求:删除id为2的学生信息
	delete from stu where id=2;
-- 需求:删除整张表中的所有数据
	delete from stu;
	truncate table stu;
	

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

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

创建商品表：product

无条件查询：

查询所有
-- 需求:查询所有的商品.
SELECT * FROM product;
查询指定列
-- 需求:查询商品名和商品价格.
SELECT pname,price FROM product;
别名查询
-- 需求3.别名查询.使用的关键字是as（as可以省略的）.	
--   3.1表别名:查询商品名称和价格
	SELECT pro.pname,pro.price FROM product AS pro;
--   3.2列别名：查询商品名称和价格
	SELECT pname AS "商品名称",price "商品价格" FROM product;
去重复查询
-- 需求:查看商品表中有那些价格（去重）.
select distinct price from product;	
-- 需求:将所有商品的价格+10元进行显示. (查询结果可参与运算)
select *,IFNULL(price,0)+10 from product;
====================================================================
条件查询：
-- 格式
select 字段，...from 表名 where 条件;
比较运算符
-- 需求：查询价格大于3000的商品
select * from product where price>3000;
-- 需求：查询价格不等于3000的商品
select * from product where price！=3000;
select * from product where price<>3000;
逻辑运算符
and(&&) 多个条件同时满足 
or(||) 多个条件其中一个满足 
not(!) 不满足
-- 需求：查询价格大于2000，且种类为c001的商品
select * from product where price>2000 and categroy_id='c001';
in关键字
-- 格式
select * from 表名 where 字段 in（数据1，数据2，...）;
-- 需求：查询pid是1，3，4的商品
select * from product where pid in(1,3,4);
范围
-- 格式
select * from 表名 where 字段名 between 值1 and 值2；
-- 需求：查询价格大于等于1000，且小于等于5000的商品信息
select * from product where price between 1000 and 5000；
模糊搜索like
	%  	: 模糊所有
	_   : 模糊一位
例如:
	"马%"  匹配以"马"字开头
	"%马"  匹配以"马"字结尾
	"_马"  匹配第二个字是"马"字的 
	"__马%"  匹配第三个字是"马"字的
	"%马%"	匹配包含"马"字的
-- 格式
select * from 表名 where 字段 like 条件;
-- 需求：查询品牌包含花的商品
select * from product where pname like '%花%';
================================================================
排序： order by
单列排序
asc(默认) : 升序   
desc : 降序
-- 格式
select * from 表名 where 条件  order by 字段名1 desc/asc , 字段名2 [desc | asc]... ;
-- 需求：查询所有数据,使用价格降序排序
select * from product order by price desc;
组合排序
-- 需求：查询所有数据,使用价格降序，id升序排序
select * from product order by price desc,pid asc;
==============================================================
聚合函数
count() : 计数
	IFNULL(字段名,0) : 如果指定字段的值为null,则使用0表示
sum() : 求和
max() : 求最大值
min() : 求最小值
avg() : 求平均值
-- 查询商品总数
select count（*） from product;
由于对于NULL的记录不会统计，如果记录数据为null，给个默认值，这样统计的数据就不会遗漏
select count（ifnull(categroy_id,0)） from product;
-- 查询价格总数
select sum(price) from product;
=============================================================
分组: group by 
将分组字段结果中相同内容作为一组

-- 需求1：查询购买的每种商品的总价。
select product,sum(price) from orders group by product;

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

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

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

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

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

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