SQL常用函数总结(一)

SQL从功能上大致分为三类：

• ---

  DDL（Data Definition Languages数据定义语言），主要语句关键字有：CREATE、DROP、ALTER、RENAME、TRUNCATE（清空）
• DML（Data Manipulation Languages数据操作语言），主要语句关键字有：INSERT、DELETE、UPDATE、SELECT
• DCL（Data Control Languages数据控制语言），主要关键字有：GRANT(授予权限)、REVOKE（回收权限）、COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点）

！MySQL在windows环境下大小写不敏感，在linux环境下大小写敏感。oracle大小写敏感

一、基础语句

1.导入现有数据表、表的数据

方式1：在命令行导入：source+文件的全路径名

方式2：基于具体的图形化界面的工具导入数据eg：SQLyog中，选择“工具”--“执行sql脚本”--选择xxx.sql即可

2.基本的select语句

(1)SELECT 字段1，字段2，…FROM 表名

eg：

SELECT employee_id,last_name FROM employees;

(2)列的别名

①as 全称alias，可省略

②使用一对双引号" "引起来

③空格即可eg：

SELECT employee_id  emp_id,last_name as lname,department_id  "dept_id''

FROM employees;

(3)去除重复行DISTINCT

#正确去重情况
SELECT DISTINCT department_id FROM employees;
#错误去重情况（未报错但无意义）
SELECT DISTINCT department_id,salary FROM employees;#不知道该根据哪个字段去重

（4）空值null

①空值null不等同于0

②空值null参与运算，结果一定为空

涉及此情况时，常使用ifnull（xx，0）若xx为null则拿0替换

（5）着重号``

反引号``,英文状态下数据1左边键，当字段名与sql的关键字重复时代码通过会报错，此时需要用着重号，将字段名引起来

（6）查询常数

若查询原表中没有的常数，显示时会给每一行都加上该常数

（7）显示表结构

使用DESCRIBE或DESC查询表中字段的详细信息

DESC employees;#employees为表名

 （8）过滤数据

通常使用WHERE关键词+条件进行过滤数据

3.运算符

（1）算术运算符：+、-、*、/ 或者div、取模%或mod

SELECT 100+0，100-0,100+50,11+50*30,100+35.5,100-35.5,12%3,12 mod -5
FROM DUAL#dual是伪表

sql中，+没有连接的作用，只表示加法运算，当二者数据类型不一致时，存在隐式转换，例如

SELECT 100+'1' FROM DUAL #此处1为字符串，100为int，会将1转为数值，结果为101
SELECT 100+'a' FROM DUAL #a等不是数值的字符串通通转化为0处理
SELECT 100+null FROM DUAL #null参与运算，结果为null

(2)比较运算符

左右进行比较，结果为真返回1，结果为假返回0，其他情况返回null

①=，安全等于<=>,不等于!<>,!=,<,<=,>,>=

SELECT 1=2,1!=2,1='1',1='a',0='a' FROM DUAL
#结果分别为：0，1，1，0，1
#字符串存在隐式转换，若转换数值不成功，则看作0
SELECT 'a'='a','ab'='ab','a'='b' FROM DUAL
#结果分别为：1，1，0 第三种情况两边都是字符串则按照ANSI的比较规则进行比较
SELECT 1=null,null=null FROM DUAL
#结果分别为 null, null

 <=>完全等于，为NULL而生

SELECT 1<=>NULL,NULL<=>NULL FROM DUAL
#结果分别为0，1

②IS NULL、IS NOT NULL、ISNULL

SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct IS NULL
WHERE commission_pct IS NOT NULL
WHERE ISNULL(commission_pct )
WHERE NOT commission_pct <=> NULL
#1,3种效果一样，2，4种效果也一样

③LEAST()、GREATEST()最小、最大

SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
#结果为：b，t

 常与LENGTH()同用，返回长度最短、最长的字段

④BETWEEN 条件1 AND 条件2  查询条件1和条件2范围内的数据，包括边界。

#查询工资在6000到8000的员工信息
SELECT employee_id,last_name,salary FROM employees
WHERE salary BETWEEN 6000 AND 8000
#或者WHERE salary >= 6000 && salary<=8000;

#查询工资不在6000到8000的员工信息
SELECT employee_id,last_name,salary FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000
#或者WHERE salary <= 6000 or salary>=8000;

⑤ in （set）、not in (set)

#查询部门为10，20，30部门的员工信息
SELECT last_name,salary,department_id FROM employees
WHERE department_id IN(10,20,30);

#查询工资不是6000，7000，8000的员工信息
SELECT last_name,salary,department_id FROM employees
WHERE salary NOT IN(6000,7000,8000);

⑥ LIKE：模糊查询

%代表不确定个数的字符（0，1，多个），_代表一个不确定的字符，

转义字符为\,例如\_表示该字符是‘_’,而不是一个不确定的字符

SELECT last_name FROM employees
#查询名字中包含a的员工信息
WHERE last_name LIKE '%a%';
#查询以a开头的员工名字
WHERE last_name LIKE 'a%';
#查询名字中包含a且包含e的员工信息
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%'#写法2
#查询第二个字符是a的员工名字
WHERE last_name LIKE '_a%';
#查询第三个字符是a的员工名字
WHERE last_name LIKE '__a%';
#查询第二个字符是_且第三个字符是a的员工名字
WHERE last_name LIKE '_\_a%';

⑦正则表达式 REGEXP、RLIKE

[a-z]匹配任何字母，[0-9]匹配任何数字，‘X*’匹配任何数量的X字符，“*”匹配任何数量的任何字符，“·”匹配任何一个单字符，[…]匹配方括号内任何字符

#⑥中最后一个可以用正则表达式写
#查询第二个字符是_且第三个字符是a的员工名字
WHERE last_name LIKE '_$_a%' ESCAPE '$';
#^表示以shk开头的，$表示以t结尾的，最后一个表示包含hk的
SELECT 'shkstart' REGEXP '^shk' ,'shkstart' REGEXP 't$','shkstart' REGEXP 'hk' FROM DUAL;

⑧逻辑运算符 OR，|| 或；AND，&& 与；NOT，！ 非； XOR，异或。

OR满足一个条件即可

AND两个条件都满足

XOR满足条件1，不满足条件2；不满足条件1，满足条件2.

OR、AND可一起使用，AND优先级高于OR，使用（），优先级更高

⑨位运算符 &与，|或，^异或，~取反，>>按位右移，<<按位左移