SQL数据库学习

DDL(数据定义语言）:

create table;  //创建表
create index;  //创建索引
create view;  //创建视图
drop   able;  //删除表
drop   index;  //删除索引
drop   view;  //删除视图
alter  able;  //修改表

————————————————————————————

DML(数据操作语言):

insert         插入（创建记录）
delete         删除（删除记录）
update         更新（修改记录）
select         查询（查询记录）

————————————————————————————

DCL（数据控制语言）:

GRANT          授予权限
REVOKE         撤销授权
SET            设置

————————————————————————————

WHERE子句操作符表

项目	Value
=	等于
<>	不等于
!=	表单映
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两个值之间
IS NULL	为NULL值

ISNULL：如果第一个参数为空，则转换为第二个参数。
例：Select ISNULL(字段名,0) from 表名
释：查询某表的某字段，并将该字段中为null值的数转换为0

————————————————————————————
创建数据库

Create database 数据库名

创建指定字符集数据库

create database 数据库名 character set 字符集

显示所有数据库

show  databases

查询指定数据库

show create database 数据库名

使用指定数据库

use 数据库名

删除数据库

Drop database 数据库名    

————————————————————————————
使用本地文件表

source e:/emp.sql;

source 盘符:/文件名

创建新表

Create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
)

创建指定字符集的表

create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
)charset=字符集;

创建带主键的表

create table 表名(
字段名1 数据类型 primary key,
字段名2 数据类型
);

创建带主键自增的表

create table 表名(
字段名1 数据类型 primary key auto_increment,
字段名2 数据类型
);

zerofill自动补零

create table 表名(字段名1 int(5)zerofill);

浮点数double

create table 表名(字段名1 double(m,d)) //m代表总长度，d代表小数长度

查询所有表

show tables

查询指定表字符集

show create table 表名

查询表字段

desc 表名

删除表

Drop table 表名

修改表名

rename table 原表名 to 新表名

————————————————————————————

添加字段到最后面

Alter table 表名 add 字段名 数据类型

添加字段到最前面

alter table 表名 add 字段名 数据类型 first

添加字段到指定字段的后面

Alter table 表名 add 字段名 数据类型 after 指定字段

删除字段

Alter table 表名 drop  字段名

修改字段名

Alter table 表名 change 原名 新名 新类型

修改字段类型

Alter table 表名 MODIFY 字段名 新类型

添加主键

Alter table 表名 [add constraint 约束名] primary key (字段名)

删除主键

Alter table 表名 drop constraint 约束名

————————————————————————————

查询某表全部数据

select * from 表名

筛选某表的两个字段

select 字段1,字段2 form 表名

查询某表并按照字段排序（order by必须置后）

select * from 表名 order by 字段名

查询某表，并按照指定字段排序

Select * from 表名 order by 字段1,字段2 

按照当前表的字段列序号，进行先后查询

Select * from 表名 order by 字段序号1,字段序号2  

降序排序查询（可以和其它查询方法并用，在字段名后加,然后排序字段）

Select * from 表名order by 字段名 DESC

筛选当前表，所选字段满足条件的数据

select 字段名 from 表名 where 字段名 操作符 条件

范围值检查，设置一个范围的起始值和一个末尾值

Select * from 表名 where 字段名 BETWEEN 起始 and 末尾

空值检查

Select * from 表名 where 字段名 is null         //查找字段为空值的数据
Select * from 表名 where 字段名 is not null      //查找字段不为空值的数据

插入insert into

insert into 表名 values(数值1,数值2,数值n) //全表插入：数值的数量与顺序要求与表字段相同
Insert into 表名(字段1,字段2) values(数值1,数值2)//单条插入
insert into 表名 values(数值1,数值2,数值n), (数值1,数值2,数值n)//多条插入1
Insert into 表名(字段1,字段2) values(数值1,数值2),(数值1,数值2)//多条插入2
Insert into 表名(字段) select 源字段 from 数据源表 where 筛选条件//批量插入

删除数据

Delete from 表名 where 范围

清除表内所有数据

truncate table 表名

修改数据

Update 表名 set 字段 = ‘数值’ where 需改的字段 = ‘条件’

————————————————————————————

IDENTITY_INSERT 设置表中的标识列插入显式值

Set identity_insert 表名  ON--打开
Set identity_insert 表名 OFF--关闭

AND操作符:

Select * from 表名 where 字段名 操作符 条件 and 字段名2 操作符2 条件2

例：select * from 表 where id = 2 and price <>0
释：筛选某表，选出其中id为2的数据，再筛选price不为0的数据，类似逻辑运算的 与

OR操作符:

Select * from 表名 where 字段名 操作符 条件 or 字段名2 操作符2 条件2

例：select * from 表 where id = 2 or price <>0
释：筛选某表，若条件1满足则直接输出，不再计算条件2；类似于逻辑运算的 或

计算次序:（and的优先级高于or的优先级，若要改变优先级，需加圆括号）

Select * from 表名 where 字段名1 操作符1 条件1 
OR 字段名2 操作符2 条件2 AND 字段名3 操作符3 条件3

IN操作符:（用于指定条件范围，范围内的条件都能输出，用逗号分隔）

Select * from 表名 where 字段名 in(‘条件’,’条件’)

Not操作符:

Select * from 表名 where not 字段名 操作符 ‘条件’

//查询该字段中不满足条件的所有数据

百分号(%)通配符

Select * from 表名 where 字段名 LIKE ‘字符%’

例：select * from 表 where name like ‘Fish%’
释：筛选字段name，检索任意以Fish起头的词，%是告诉DBMS接受Fish之后的任意字符
//通配符可以在任意位置使用，并且可以多个存在
例：select * from 表名 where 字段名 LIKE ‘%Fish%’
释：’%Fish%’表示匹配任何位置包含文本Fish的值，而不论它之前或之后出现什么字符

下划线( _ )通配符 //用途和%号一样，但下划线通配符只匹配单个字符

Select*from 表名 where 字段名 LIKE ‘字符_’

方括号( [ ] )通配符 //用于指定一个字符集，必须匹配指定位置(通配符的位置)的一个字符
例：Select 字段名 from 表名 where 字段名 LIKE ‘[Fish]%’ order by 字段名
释：匹配任何以方括号[ ]中字符开头的数据，但是只能匹配单个字符，因此加%通配符匹配第一个字符后的任意数目的字符，并排序
//此通配符可以用前缀字符^（脱字号）来否定
例：select 字段名 from 表名 where 字段名 like ‘[^Fish]%’

//通配符注意要点：通配符计算速度慢，能使用其它操作符完成的话就不要用通配符，通配符编写时应当置后

limit

select * from 表名 limit 5,5;  // 检索记录行 6-10

//第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目

项目	Value
+	加
-	减
*	乘
/	除
%	余

拼接字段（将两个字段的数据联结到一起构成单个值）
Select 字段1 + 字段2 from 表名
注：所选字段不能为纯整数，若为整数会按照加法计算，达不到拼接的效果

列别名（alias）
Select 字段1 + 字段2 AS 字段别名 from 表名

函数
DBMS函数的差异表

函数	语法
提取串的组成部分	MYSQL和SQL Server使用SUBSTRING()
数据类型转换	MYSQL和SQL Server使用CONVERT() CAST
提取当前日期	MYSQL使用CURDATE（）；SQL Sever使用GETDATE（）；
注：SQL语句可移植，SQL函数不可移植

例：Select TRIM(字段名1) + TRIM(字段名2) AS 字段别名 from 表名
释：查询某表的字段1和字段2，将它们两侧的空格移除，然后拼接移入别名字段

UPPER（）函数：
例：SELECT 字段1,UPPER(字段2) AS 字段别名 from 表名 ORDER BY 字段1
释：查询某表的两个字段，将字段2的文本转换为大写，再将转换后的数据移入别名字段，然后按照字段1进行排序

常用的文本处理函数表

函数	说明
LEFT() （或使用子字符串函数）	返回串左边的字符
RIGHT() （或使用子字符串函数）	返回串右边的字符
LENGTH() （也使用DATALENGTH()或LEN()）	返回串的长度
TRIM()	移除字符串两侧的空格
RTRIM()函数	移除字符串右侧的空格
LTRIM()函数	移除字符串左侧的空格
LOWER()	将串转换为小写
UPPER()	将串转换为大写
SOUNDEX()	返回串的SOUNDEX值

注：SOUNDEX是将文本串转换为描述其语音表示的字母数字模式的算法，其考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

日期和时间处理函数
查询某一天

SELECT * FROM 表名 WHERE DATE_FORMAT(CREATETIME,'%Y-%m-%d')='2023-01-01';

查询某一时间段

SELECT * FROM 表名 WHERE CREATETIME BETWEEN '2023-01-01' AND '2023-06-30';

数值处理函数
数值处理函数只用于处理数值数据，如代数、几何等，因此没有串或日期-时间处理函数的使用那么频繁。
常用数值处理函数表

函数	说明
SIN()	返回一个角度的正弦
COS()	返回一个角度的余弦
TAN()	返回一个角度的正切
ABS()	返回一个数的绝对值
EXP()	返回一个数的指数值
SQRT()	返回一个数的平方根
PI()	返回圆周率

汇总数据
SQL聚集函数表

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列值最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG()函数通过对表中行数计数并计算特定列值之和，求得该列的平均值

Select avg(字段名) as 字段别名 from 表名

Count()函数有两种使用方式：
count()对表中所有行进行计数，无论是否为NULL值
Count(字段名)对指定字段的行进行计数，忽略NULL值

concat()合并函数
查询时将数据合并

select concat(id,name,age) as user from user;

在更新某个字段时追加数据

UPDATE user SET name= CONCAT('值',name);

数字可以直接加

UPDATE user SET age= age+10;

聚集不同值
DISTINCT参数，在计算中若加入则只计算不同的参数
例：select AVG(DISTINCT 字段名) as 字段别名 from 表名
释：计算平均数时将不会再计算相同项
注：DISTINCT只能用于指定列，不能用于计算或表达式
例：Select distinct 字段名 from 表名
释：将该字段所有唯一的值列出

Top子句
例：Select top 数目 * from 表名
释：返回规定数目的记录
例：Select top 50 percent * from 表名
释：返回规定百分比的记录

组合聚集函数

Select count(*) as 字段别名,MIN(字段) as 字段别名 ，MAX(字段) as 字段别名 from 表名

Group by分组函数
按照一定条件将数据进行分组，受后续条件影响，细分越多展开越多

order by排序

select * from 表名 order by id;按照id升序
select * from 表名 order by id desc;按照id降序

With(NOLOCK)提高效率防止自锁

having
后面专门写聚合函数,需要和group by结合使用

四舍五入

Round(计算数值,保留小数位) 

子查询(嵌套查询)

select * from 表名 where 字段名>(select avg(sal) from emp)

连接

select * from 表名1,表名2

内连接

select a.id,a.字段,b.id,b.字段 from 表名1 a join 表名2 b on a.id=b.id

左连接（左外连接）：返回left join 子句中指定的左表的所有行，以及右表所匹配的行。

select a.id,a.字段,b.id,b.字段 from 表名1 a left join 表名2 b on a.id=b.id

右连接（右外连接）：返回right join 子句中指定的右表的所有行，以及左表所匹配的行。

select a.id,a.字段,b.id,b.字段 from 表名1 a right join 表名2 b on a.id=b.id

完整外连接：等同左连接+右连接

 select a.id,a.字段,b.id,b.字段 from 表名1 a full join 表名2 b on a.id=b.id

交叉连接：没有两个表之间关系的交叉连接，将产生连接所涉及的表的笛卡尔积。

select a.id,a.字段,b.id,b.字段 from 表名1 a cross join 表名2 b

自连接：一个表和其本身连接。

select a.id,a.字段,b.id,b.字段 from 表名1 a,表名1 b where a.id=b.id