欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

【年薪百万之IT界大神成长之路十一】一篇就懂、带你从头到尾梳理SQL（基础篇）

程序员文章站 2022-01-13 10:57:28
...

 

[catalog]

 
  1.认识 SQL
  2. SQL 语言分类
  3. LIKE 模糊查询
  4. AS 别名
  5. UNION & JOIN 集合连接
  6. CONSTRAINT 约束
  7. INDEX 索引
  8. SEQUENCE / AUTO_INCREMENT 序列对象 / 自增列
  9. VIEW 视图
  10. FUNCTION 函数
  【每日一面】DROP、TRUNCATE、 DELETE 的区别

 

1.认识 SQL 

SQL（Structured Query Language）结构化查询语言是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL 对大小写不敏感，SELECT 与 select 是相同的。

  • Oracle角色权限
  • .connect 临时用户，简单权限，只可以登录，与服务器建立连接会话session的权限，不可以创建数据库结构
  • .resource 用户可以创建他们自己的表，序列，存储过程（procedure），触发器（trigger），索引（index）等。
  • .dba 所有系统权限，无限制的空间限额和给其他用户授予各种权限的能力，用户system拥有dba角色。

 

2. SQL 语言分类 

 

  • DQL 数据查询语言（Data Query Language）：WHERE 条件、ORDER BY 排序、GROUP BY 分组、HAVING 聚合条件
  • DML 数据操作语言（Data Manipulation Language）：INSERT 增、DELETE 删、UPDATE 改、SELECT 查
  • DDL 数据定义语言（Data Definition Language）：CREATE 创建、DROP 删除、ALTER 改变/修改
  • DCL 数据控制语言（Data Control Language）：GRANT 授权、REVOKE 撤销
  • TCL 事务控制语言（Transation Control Language）：COMMIT 提交、ROLLBACK 回滚
  • CCL 指针控制语言（Cursor Control Language）：DECLARE CURSOR、FETCH INTO、UPDATE WHERE CURRENT

 

  • DQL 示例
*****************************************************************	
・【ORDER BY】排序
	ORDER BY A,B        默认都按升序排列
	ORDER BY A DESC,B   A 降序，B 升序排列
	ORDER BY A,B DESC   A 升序，B 降序排列
*****************************************************************

 

  • DML 示例
*****************************************************************	
・【INSERT】插入数据
	INSERT INTO table_name
	VALUES
		( value1, value2, value3,...);
................................................................
	或者
	INSERT INTO table_name ( column1, column2, column3,...)
	VALUES
		( value1, value2, value3,...);
*****************************************************************		
・【INSERT INTO...FROM】插入表数据，要求目标表存在
	INSERT INTO table_name_new SELECT
	* 
	FROM
		table_name_old
................................................................
	或者
	INSERT INTO table_name_new ( column1, column2...) SELECT
	column1,
	column2...
	FROM
		table_name_old	
*****************************************************************	
・【SELECT...INTO FROM】要求目标表不存在，因为在插入时会自动创建（MySQL暂不支持）
	SELECT
		* INTO table_name_new 
	FROM
		table_name_old
*****************************************************************
・【DELETE】删除，可回滚
	DELETE 
	FROM
		table_name 
	WHERE
		some_column = some_value;
*****************************************************************	
・【UPDATE】更新，set sql_safe_updates=1; 表示开启更新条件（where）
	UPDATE table_name 
	SET column1 = value1,
	column2 = value2,
	...
	WHERE
		some_column = some_value;
*****************************************************************	
・【SELECT DISTINCT】在表中，一个列可能会包含多个重复值，DISTINCT 关键词用于返回唯一不同的值。
	SELECT DISTINCT
		column_name,
		column_name 
	FROM
		table_name;
*****************************************************************			
・【LIMIT】MySQL 规定要返回的记录的数目
	SELECT
		column_name
	FROM
		table_name 
		LIMIT number;
*****************************************************************	
・【ROWNUM】Oracle 规定要返回的记录的数目
	SELECT
		column_name
	FROM
		table_name 
	WHERE
		ROWNUM <= number
*****************************************************************	
・【TOP】SQL Server 规定要返回的记录的数目
	SELECT
		TOP number | percent column_name
	FROM
		table_name;
*****************************************************************

 

  • DDL 示例
*****************************************************************	
・【CREATE TABLE...AS】复制表结构及其数据
	CREATE TABLE table_name_new AS 
	SELECT
		* 
	FROM
		table_name_old;
*****************************************************************			
・【CREATE TABLE...LIKE】仅复制表结构
	CREATE TABLE table_name_new AS 
	SELECT
		* 
	FROM
		table_name_old 
	WHERE
		1 = 2;
................................................................
	或者
	CREATE TABLE table_name_new 
	LIKE table_name_old
*****************************************************************	
・【ALTER】修改表结构 - 修改列的数据类型
	ALTER TABLE table_name 
	MODIFY column_name datatype;
*****************************************************************
・【ALTER】修改表结构 - 修改列名
	ALTER TABLE table_name 
	RENAME COLUMN column_old TO column_new;
*****************************************************************
・【ALTER】修改表结构 - 增加列
	ALTER TABLE table_name 
	ADD column_name datatype;
*****************************************************************
・【ALTER】修改表结构 - 删除列
	ALTER TABLE table_name 
	DROP COLUMN column_name;
*****************************************************************
・【DROP】删除表 / 数据库
	DROP TABLE table_name;
	DROP DATABASE database_name;
	TRUNCATE TABLE table_name;
*****************************************************************
・【RENAME】修改表名
	RENAME table_name_old TO table_name_new;
*****************************************************************

 

  • DCL 示例
*****************************************************************	
・【GRANT】赋予角色权限
	GRANT connect,
		resource,
		dba 
	TO user_name;
*****************************************************************

 

3. LIKE 模糊查询

 

*****************************************************************	
・【LIKE】模糊查询
	SELECT
		column_name
	FROM
		table_name 
	WHERE
		column_name LIKE pattern;
*****************************************************************
通配符 说明
% 替代 0 个或多个字符
_ 仅替代一个字符
[ charlist ] 字符列中任意单一字符
[ ^charlist ] or [ !charlist ] 不在字符列中的任何字符 
*****************************************************************	
・【LIKE】MySQL 不支持 [charlist]。使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
	SELECT
		* 
	FROM
		Websites 
	WHERE
		NAME REGEXP '^[GFs]';
*****************************************************************

 

4. AS 别名

下面的情况下，使用别名很有用：在查询中涉及超过一个表；在查询中使用了函数；列名称很长或者可读性差；需要把两个列或者多个列结合在一起

*****************************************************************	
・【COLUMN】列的 SQL 别名
	SELECT
		column_name AS alias_name 
	FROM
		table_name;
*****************************************************************
・【COLUMN】列结合的 SQL 别名
	SELECT NAME
		,
		CONCAT( url, ', ', alexa, ', ', country ) AS site_info 
	FROM
		Websites;
*****************************************************************	
・【TABLE】表的 SQL 别名
	SELECT
		column_name
	FROM
		table_name AS alias_name;
*****************************************************************

 

5. UNION & JOIN 集合连接

1.MySQL 暂不支持 FULL JOIN，要使用 UNION 将一个左连接、和一个右连接去重合并。
2.UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中列的顺序必须相同。排序时只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

*****************************************************************	
・【INNER JOIN】内连接，INNER JOIN 与 JOIN 是相同的
	SELECT
		select_list 
	FROM
		table1
	JOIN / INNER JOIN table2 
	ON table1.column_name = table2.column_name
*****************************************************************
・【LEFT / RIGHT /FULL JOIN】外连接，基准表返回所有的行，连接表返回匹配的行
	SELECT
		select_list 
	FROM
		table_name1 
		LEFT / RIGHT /FULL JOIN table_name2 
	ON table1.column_name = table2.cloumn_name
*****************************************************************	
・【UNION】合并两个或多个 SELECT 语句的结果,只会选取不同的值。
	SELECT
		column_name
	FROM
		table1 UNION
	SELECT
		column_name
	FROM
		table2;
*****************************************************************	
・【UNION ALL】合并两个或多个 SELECT 语句的结果,如果允许重复的值，请使用 UNION ALL
	SELECT
		column_name
	FROM
		table1 UNION ALL
	SELECT
		column_name
	FROM
		table2;
*****************************************************************	
・【INTERSECT/ MINUS / EXCEPT】交集/并集/差集（MySQL 暂不支持）
	SELECT
		column_name
	FROM
		table1 INTERSECT/ MINUS / EXCEPT
	SELECT
		column_name
	FROM
		table2;
*****************************************************************

 

9. CONSTRAINT 约束

[ 可选 ] 如需命名约束使用：CONSTRAINT constraint_name

*****************************************************************	
・【PRIMARY KEY】主键约束，在一个表中有且只有一个，主键字段数据必须非空，主键字段数据必须唯一
	建表时：
	CREATE TABLE table_name  (
		 id number ( 2 ) PRIMARY KEY, 
		 NAME varchar2 ( 20 )
	 );
	 或者
	CREATE TABLE table_name  (
		 id number ( 2 ), 
		 NAME varchar2 ( 20 ), 
		 CONSTRAINT pk_name PRIMARY KEY ( id ) 
	 );
................................................................
	建表后：
	ALTER TABLE table_name 
	ADD CONSTRAINT pk_name 
	PRIMARY KEY ( column_name);
*****************************************************************	
・【FOREIGN KEY】外键约束，保证一个表中的数据匹配另一个表中的值的参照完整性
	建表时：
	CREATE TABLE table_name_order (
		 id number ( 2 ) REFERENCES table_name ( column_name ),
		 NAME varchar2 ( 20 )
	 );
	 或者
	CREATE TABLE table_name_order ( 
		id number ( 2 ), 
		NAME varchar2 ( 20 ), 
		CONSTRAINT fk_name FOREIGN KEY ( id ) REFERENCES table_name ( column_name ) 
	);
................................................................
	建表后：
	ALTER TABLE table_name_order  
	ADD CONSTRAINT fk_name 
	FOREIGN KEY ( column_name ) REFERENCES table_name ( column_name ) ;
*****************************************************************	
・【UNIQUE】唯一性约束，唯一标识数据库表中的每条记录
	CREATE TABLE table_name  (
		 id number ( 2 ) UNIQUE, 
		 NAME varchar2 ( 20 )
	 );
*****************************************************************	
・【CHECK】检查性约束，只允许特定的值。
	CREATE TABLE table_name ( 
		id number ( 2 ) CHECK ( id > 0 ), 
		NAME varchar2 ( 20 ) CHECK ( NAME = 'ITGod' ) 
	);
*****************************************************************	
・【DEFAULT】默认约束，向列中插入默认值，也可以插入系统值
	CREATE TABLE table_name  (
		 id number ( 2 ) DEFAULT 88, 
		 NAME varchar2 ( 20 ) ,
		 OrderDate date DEFAULT GETDATE()
	 );
*****************************************************************	
・【NOT NULL】非空约束，强制列不接受 NULL 值
	CREATE TABLE table_name  (
		 id number ( 2 ) NOT NULL UNIQUE, 
		 NAME varchar2 ( 20 )
	 );
*****************************************************************	
・【DROP】删除约束
	ALTER TABLE table_name 
	DROP CONSTRAINT constraint_name;
................................................................	
・【SELECT】不知道约束名，先查询约束名
	SELECT
		constraint_name 
	FROM
		information_schema.REFERENTIAL_CONSTRAINTS 
	WHERE
		constraint_schema = 'db_name' 
		AND table_name = 'table_name';
*****************************************************************

 

7. INDEX 索引

用户无法看到索引，它们只能被用来加速搜索/查询

*****************************************************************	
・【CREATE INDEX】创建索引（允许使用重复的值）
	CREATE INDEX index_name 
	ON table_name (column_name1, column_name2 [ asc / desc ])
................................................................	
・【CREATE INDEX】创建索引（不允许使用重复的值）
	CREATE UNIQUE INDEX index_name 
	ON table_name (column_name [ asc / desc ])	
*****************************************************************	
・【DROP INDEX】Oracle 删除索引
	DROP INDEX index_name
................................................................	
・【CREATE INDEX】MySQL 删除索引
	ALTER TABLE table_name 
	DROP INDEX index_name
*****************************************************************

 

8. SEQUENCE / AUTO_INCREMENT 序列对象 / 自增列

每次插入新记录时，自动地创建主键字段的值

*****************************************************************	
・【SEQUENCE】Oracle 序列对象
	CREATE  SEQUENCE  SEQUENCE_NAME
	[ START  WITH  START_VALUE ]---起始值【默认1】
	[ INCREMENT  BY  INCR_VALURE ]---增量，步长
	[ MAXVALUE  MAX_VALUE ]---最大值
	[ MINVALUE  MIN_VALUE ]---最小值
	[ CACHE  N ]---高速缓冲区
	[ CYCLE ] ；---循环（递增、递减）
................................................................	
・【SEQUENCE】Oracle 使用序列对象
	INSERT INTO Persons ( column_name1, column_name2)
	VALUES(
		SEQUENCE_NAME.nextval,
		'Values'
	)
*****************************************************************	
・【AUTO_INCREMENT】MySQL 自增列，默认1，自增1
	CREATE TABLE Persons (
		ID INT NOT NULL AUTO_INCREMENT,
		LastName VARCHAR ( 255 ) ,
		FirstName VARCHAR ( 255 ),
		Address VARCHAR ( 255 ),
		City VARCHAR ( 255 ),
		PRIMARY KEY ( ID ) 
	)
................................................................	
・【AUTO_INCREMENT】MySQL 修改自增列以其他值开始
	ALTER TABLE Persons AUTO_INCREMENT=100
*****************************************************************

 

9. VIEW 视图

是由查询结果生成的虚拟表，是动态数据的存储集合，视图只供查询，数据不可更改，不影响原表的数据和结构。简化了数据访问操作，还加强了安全性

*****************************************************************
・【CREATE VIEW】创建视图，用来保存动态数据
	CREATE VIEW view_name AS 
	SELECT
		column_name ( s ) 
	FROM
		table_name 
	WHERE
		CONDITION
*****************************************************************	
・【SELECT】查看视图
	SELECT * FROM view_name
*****************************************************************	
・【CREATE VIEW】更新视图
	CREATE OR REPLACE VIEW view_name AS 
	SELECT
		column_name ( s ) 
	FROM
		table_name 
	WHERE
		CONDITION
*****************************************************************	
・【DROP】删除视图
	DROP VIEW view_name
*****************************************************************

 

10. FUNCTION 函数

 

  • NULL 函数
    用于规定如何处理 NULL 值 
*****************************************************************
・【 NVL()】Oracle
	SELECT
		NVL ( column_name, 0 )
	FROM
		table_name
*****************************************************************
・【IFNULL() / COALESCE()】MySQL
	SELECT
		COALESCE / IFNULL ( column_name, 0 ) 
	FROM
		table_name
*****************************************************************

 

  • AGGREGATE 聚合函数
    是指对一组值进行操作，返回单一的结果
函数 说明
AVG() 平均值
COUNT() 统计数目
FIRST() 返回第一个记录的值
LAST() 返回最后一个记录的值
MAX() 最大值
MIN() 最小值
SUM() 求和

 

  • SCALAR 标量函数
    是指对单一值进行操作，返回单一的结果
函数 说明
UCASE() 将某个字段转换为大写
LCASE() 将某个字段转换为小写
MID() 从某个文本字段提取字符，MySql 中使用
SubString (str_exp，start，end) 从某个文本字段提取字符
LEN() 返回某个文本字段的长度
ROUND() 对某个数值字段进行指定小数位数的四舍五入
FORMAT() 格式化某个字段的显示方式
NOW() 返回当前的系统日期和时间

 

  • MATH 数学函数
函数 说明
ABS ( num_exp ) 绝对值
CEIL ( num_exp ) 上舍入
FLOOR ( num_exp ) 下舍入
ROUND ( num_exp [ , length ] ) 按指定长度四舍五入
MOD ( num_exp , num_exp2 ) 取模
POWER ( num_exp , n ) 返回表达式的n次方
SQRT ( num_exp ) 返回表达式的平方根
DBMS_RANDOM.VALUE 返回 0 - 1 之间的随机数
SIGN ( num_exp ) 返回表达式的正、负、或零
TRUNC ( num_exp [ , length ] ) 按指定长度进行截断
LEAST ( value_list ) 返回所给值列表中的最小值
GREATEST ( value_list ) 返回所给值列表中的最大值

 

  • STRING 字符串函数
函数 说明
ASCII ( str_exp ) 返回所给字符串表达式最左端所对应的ascii值
CHR ( num ) 返回所给整数对应的字符chr(9)空格 chr(13)换行
CONCAT( char_exp1 , char_exp2 ) 返回将两个字符串串联后的新字符串
INITCAP ( char_exp ) 返回将字符串首字符转换成大写后的字符串
REPLACE ( char_exp1 , char_exp2 , char_exp3 ) 原串、查找串、替换串 – 将第一个字符串出现的第二个字符串用第三个字符串替换
INSTR ( char_exp1 , char_exp2 [ , start_index , times ] ) 原串、查找串、起始值、次数 – 返回第二个字符串在第一个字符串中第几次出现的位置
SUBSTR( char_exp , start_index , length ) 返回指定位置截取指定个数的字符（从什么地方开始截取几个）
LOWER ( char_exp ) 返回指定字符转换成小写
UPPER ( char_exp ) 返回指定字符转换成大写
LENGTH ( char_exp ) 返回指定字符表达式的个数或长度
LTRIM ( char_exp1 [ , char_exp2 ] ) 返回删除指定字符串最左边字符或空格
RTRIM ( char_exp1 [ , char_exp2 ] ) 返回删除指定字符串最右边字符或空格
LPAD ( char_exp1 , length [ , char_exp2 ] ) 返回使用空格或字符串的左边补齐到指定长度位置处
RPAD ( char_exp1 , length [ , char_exp2 ] ) 返回使用空格或字符串的右边补齐到指定长度位置处

 

  • DATE 日期函数
函数 说明
sysdate 返回系统当前日期（只包含年月日）
systimestamp 返回系统当前日期（包含时分秒）
add_months（date_exp , num） 在指定日期表达式上增加一定的月份值
last_day（date_exp） 返回指定日期表达式所在月份的最后一天
months_between（date_exp1 , date_exp2） 返回两个日期相差的月份值
trunc（date_exp [ , format ]） 返回按指定日期格式截断后的日期

 

  • TRANSFORM 转换函数
函数 说明
TO_CHAR ( exp [ , format ] ) 将指定的表达式转换成指定格式的字符串
TO_DATE ( char_exp , format ) 将指定字符串表达式转换成指定格式的日期
NVL ( exp , default_value ) 如果指定的表达式中存在空值的话，将以指定的默认值来替换

 

【每日一面】

DROP、TRUNCATE、 DELETE 的区别

DROP 语句是 ddl，操作立即生效，原数据不放到 rollback segment 中，不能回滚。 操作不触发 trigger；drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)，依赖于该表的存储过程/函数将保留， 但是变为 invalid 状态，将表所占用的空间全部释放 。
TRUNCATE 语句是 ddl，操作立即生效，原数据不放到 rollback segment 中，不能回滚。 操作不触发 trigger；truncate 语句只删除数据不删除表的结构。
DELETE 语句是 dml，这个操作会放到 rollback segement 中，事务提交之后才生效。如果有相应的 trigger，执行的时候将被触发；delete 语句只删除数据不删除表的结构。
速度：一般来说: drop > truncate > delete ；安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候

相关标签： IT界大神成长之路 数据库 sql oracle mysql 字符串

上一篇： ubuntu声音太小的解决方式

下一篇： Ubuntu下VNC配置

推荐阅读