开始学习数据库,记录简单基础语句
#基础查询
选中要执行的内容即可执行,在写sql时,注意中英文字符,以分号结束整个语句
SELECT
last_name
FROM
employees ;
SELECT
*
FROM
employee ;
SELECT 100*98;
SELECT VERSION();
设置别名:
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#起别名:可以用as,也可以用空格,如果气的名称中含有空格、标识符,则用双引号将别名引起来
SELECT salary "out put" FROM employees;
#去重
SELECT DISTINCT department_id FROM employees;
#+号只能是运算符
#连接符:concat
SELECT
CONCAT(last_name, first_name) AS 姓名
FROM
employees ;
#显式表的结构
DESC departments;
#模糊查询 like 转义字符,like后的转移内容要用单引号引起来
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '%a%' ;
自定义转移字符
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '_$_%' ESCAPE '$' ;
SELECT last_name,job_id,salary AS sal FROM employees;
SELECT DISTINCT job_id FROM employees;
#显式出表employee是的全部列,各个列之间用逗号链接,列头显式out_put
SELECT
CONCAT(first_name,
',',
last_name,
',',
job_id,
',',
IFNULL(commission_pct, 0)) AS out_put FROM employees ;
#注意中英文状态,括号匹配等问题
#ifnull函数
# 功能:判断某字段或表达式是否为null,如果是,则返回指定的值,否则返回原本的值
#isnull函数
# 功能:判断某字段或表达式是否未null,如果是,则返回1,否则,返回0;
#先from后where在select
#简单运算符:安全运算符<=>;逻辑运算符;模糊运算符
#is null 和安全等于
#排序查询
/*
排序查询格式
select 查询列表
from 表
where 筛选条件
order by 排序列表 desc|asc
总结:
首先,数据库要注意语句结尾要以分号结尾
排序不写默认是升序排序
如何在排序中加入where筛选
order by 可以支持单个、多个字段,表达式,函数、别名
order by子句一般是放在查询最后面,除了limit子句
select中一定要*在前,再加其他查询信息
*/
SELECT
*
FROM
employees
#where salary >1200
ORDER BY salary;
#加入条件筛选的排序
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ;
#按表达式排序
#按员工年薪的高低显示员工的信息和年薪
SELECT *,salary * 12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary * 12*(1+IFNULL(commission_pct,0)) DESC;
#按别名排序
SELECT *,salary * 12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 ;
#按照函数排序
#按姓名的长度显示员工的姓名和工资
SELECT
LENGTH(last_name) 字节长度,
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) ;
#双重排序
#查询员工信息,要求先工资排序,再员工姓名长度排序
SELECT
LENGTH(last_name) AS 长度,
last_name AS 姓名,
department_id AS 部门号,
salary * 12 *(1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC,
last_name ;
SELECT
last_name AS 姓名,
salary AS 工资
FROM
employees
WHERE salary NOT BETWEEN 8000
AND 17000
ORDER BY salary DESC ;
SELECT *,LENGTH(email) 邮箱字节数
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id;
/*
常见函数
select 函数名() from 表;
特点:函数名,函数功能
分类:
单行函数:concat、length ifnull
分组函数:做统计用
*/
SELECT LENGTH('jhon');
SELECT LENGTH('李易峰');
SELECT CONCAT(last_name,'_',first_name) FROM employees;
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;
#substr 索引从1开始
SELECT SUBSTR('蜜雪冰城甜蜜蜜',5) out_put;
#从指定索引处指定长度
SELECT SUBSTR('蜜雪冰城甜蜜蜜',1,4) out_put;
#instr
SELECT INSTR('蜜雪冰城甜蜜蜜','蜜') out_put;
#trim去除收尾的字符
SELECT TRIM('a' FROM 'aaaaaaabbbbbbaaaaa');
#lpad 用指定字符实现左填充指定长度;长度为填充后的总长度,rpad右填充
SELECT LPAD('bbbccc',4,'a') out_put1;
SELECT RPAD('aaabbb',12,'c') out_put2;
#replace
SELECT REPLACE('aaabbbccaaaac','a','e') out_put3;
trim是去除首尾的字符
/*
数学函数
*/
#round四舍五入
SELECT ROUND(1.45);
SELECT ROUND(1.8586,2);
#ceil 向上取整;floor 向下取整
SELECT CEIL(-1.42);
SELECT FLOOR(-1.42);
#truncate 截断
SELECT TRUNCATE(1.69999,2);
#mod
/*
日期函数
str_to_date:将日期格式的字符转换成指定格式的日期
%Y----四位年份
%y----2位年份
%m----月份(01,02,..11,12)
%c----月份(1,2,....11,12)
%d----日(01,02...)
%H----小时(24小时制)
%h----小时(12小时制)
%i----分钟
%s----秒
date_formate:将日期转换成字符
在mysql中一定要注意中英文转换
*/
#now
SELECT NOW();
#curtime,curdate
SELECT *
FROM employees
WHERE hiredate = '1992-4-3';
SELECT *
FROM employees
WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put1;
SELECT last_name 姓名,DATE_FORMAT(hiredate,'%c月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
#流程控制函数
#1.if函数:if else的效果
SELECT IF(10<5,'yes','no');
SELECT IF(5<10,'a','b');
SELECT last_name 姓名, commission_pct 奖金, IF(commission_pct IS NULL,'没有奖金,努力','有奖金,再努力')
FROM employees;