SQL基础知识

SQL基础知识

最近在复习数据库相关的知识，把一些SQL语句及相关的基础知识简单总结一下，留作自用。

显示数据库

show databases;

属于数据库

use database_name;

显示表

show tables;

显示表结构

DESC table_name;

查询语句

查询常量值，显示字段为常量值

select 100；
select ‘abc’;

查询表达式

select 100*100；

查询函数

select version();

起别名 可使用关键字 as,或不使用

select last_name as 姓,first_name as 名 from tb_user;
select last_name 姓, first_name 名 from tb_user;
select name as "out put" from tb_user;

去重，使用关键字distinct

select distinct department_name from tb_employees;

判断是否为空 ifnull(A,B) 若A为空，则返回结果为B，不空则返回原结果

select ifnull(user_id, 0) user_id from tb_user;

条件查询

select user_name from tb_user where age=18;
select user_name from tb_user where age<>18;

模糊查询 like,between and, in, is null, is not null

select user_name from tb_user where last_name like '%a%'
select user_name from tb_user where last_name like '_a_'

//包含临界值
select user_id from tb_user where user_id between 100 and 200;

select user_id from tb_user where user_id in	(10,11,12);

selelct user_id from tb_user where department_id is null;
selelct user_id from tb_user where department_id is not null;

转义，可以使用默认的转义符号\，也可使用关键字 ESCAPE指定转义字符

//查询以_开头的用户姓名
select user_name from tb_user where user_name like '\_%';
select user_name from tb_user where user_name like '$_%' ESCAPE '$';

安全等于 <=> 可以进行正常查询和查询null

select user_id from tb_user department_id <=> 1;

排序查询： order by 排序列表 ASC(升序 可省)/DESC(降序)

select * from tb_user order by salary ASC;
select * from tb_user order by salary ;
select * from tb_user where age > 20 order by salary DESC;
select * from tb_user order by salary ASC,user_id DESC;

函数

一、字符函数
查询长度 LENGTH

select LENGTH ('john');
select LENGTH (‘李四’);

字符串拼接，使用关键字 CONCAT,若配拼接的某个字符串为null，则该返回结果为null

select CONCAT(last_name, frist_name) from tb_user;

大小写转换 UPPER（小写转大写） LOWER（大写转小写）

select CONCAT (UPPER(last_name),LOWER(first_name)) AS 姓名 from tb_user; 

截取字符串 SUBSTR

//注意：索引从1开始读取，不是0！
//输出结果为王五
select SUBSTR('张三李四王五'，5) AS output;
//输出结果为李四
select SUBSTR('张三李四王五',3,4) AS output;

返回子串第一次出现的索引 INSTR，若找不到，返回0

select INSTR ('张三李四王五'，‘李四’) AS output;

去掉前后指定字符，不指定则去掉空格 TRIM

select TRIM('  zhangsan  ') AS output;
select TRIM('a' from 'aaazhangsanaaa') AS output;

左填充指定字符 LPAD
右填充指定字符 RPAD

select LPAD('张三' , 10, 'a') AS output;
select RPAD('张三' , 10, 'a') AS output;

替换 REPLACE

//输出结果为 王五王五王五李四
select REPLACE ('张三张三张三李四','张三'， ‘王五’) AS output;

二、数学函数

四舍五入 ROUND

select ROUND (-1.45);
//保留两位小数
select ROUND(-1.458,2);

向上取整 CEIL 向下取整FLOOR

select CEIL(-.1.45);
select FLOOR(-1.45); 

截断 TRUNCATE(通俗来说就是保留数位小数)

select TRUNCATE(1.65,1)   

取余 MOD

select MOD(10,3);

三、日期函数
返回当前系统日期和时间 NOW
返回当前系统日期，不包含时间 CURDATE
返回当前时间不包含日期 CURTIME

select NOW();
select CURDATE();
select CURTIME(); 

获取指定的部分：年 YEAR 月 MOUTH 日 DAY 时 HOUR 分 MINUTE 秒 SECOND

select YEAR(NOW());

将字符转换为日期 STR_TO_DATE
将日期转换为字符 DATE_FORMAT

select STR_TO_DATEe('1998-1-1',%Y-%c-%d");
select DATE_FORMAT(NOW(),'%Y年%m月%d日');

查询个数 COUNT 忽略null

//若表中某一行的任意属性为null 则忽略该行
select COUNT (*) from user;
//相当于新建了数据全是‘1’的一列，并统计该列‘1’的个数
select COUNT (1) from user;

分组查询 GROUP BY

//查询每个部门中工作最高的员工的姓名和工资
select MAX(salary),user_name from user GROUP BY department_name; 
//查询部门员工个数大于2的部门
select COUNT(*),department_name from user GROUP BY department_name HAVING COUNT(*)>2;
//按用户姓名长度进行分组，查询每一组员工的个数，要求选出员工个数大于5的分组
select COUNT(*) ,LENGTH(user_name) AS len_name from tb_user GROUP BY LENGTH(user_name) HAVING COUNT(*)>5;