DQL数据查询语句
调试源码
select 100%3;
select 99+1;
select '99'+1;
select '99.9'+1;
select 'zhangsan'+1;
select null+1;
select first_name from employees;
select first_name,last_name from employees;
select * from employees;
-- as另命名
select first_name as '姓氏' ,last_name as '名字' from employees;
-- distinct去重
select DISTINCT first_name from employees;
-- 字符长度
select length('123');
select length(first_name) from employees;
-- concat 字符串连接
select CONCAT(first_name,'-',last_name) from employees;
-- UPPER/LOWER转换大小写
select UPPER(first_name) from employees;
select LOWER(CONCAT(first_name,'-',last_name)) from employees;
-- substr截取字符串
select SUBSTR('my name is lihua' FROM 4 FOR 4);
SELECT SUBSTR('my name is lihua' FROM 4 );
-- instr 返回字符索引
select INSTR('ohhhhhhh','hh');
-- trim首位去除规定字符,默认空格
select TRIM(' my name is xiao ming ');
select TRIM('1'from '111 my name is xiaoming 111');
lpad\rpad用字符左右填充
select LPAD('left',10,'*');
select RPAD('right',10,'*');
-- replace字符串替换
select REPLACE('my name is xiaoming','xiaoming','zhangsan');
语句顺序
- 书写顺序:SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
- 执行顺序:FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT
Select
查询常量
select 100;
select 'zhangsan';
查询表达式
select 100%3; //取余,结果为1
select 99+1; //结果为100
select '99'+1; //结果为100,转int
select '99.9'+1;//结果为100.9
select 'zhangsan'+1;//结果为1
select null+1; //结果为null
对于"+"运算符,仅用于数字类型的相加。若运算数为字符,尝试转化为数字,若转换失败,则认为是0;若运算数为null,结果为null
查询字段
select first_name from employees;
select first_name,last_name from employees;
select * from employees;
--使用别名
select first_name as '姓氏' ,last_name as '名字' from employees;
--去重
select DISTINCT first_name from employees;
执行结果:
查询函数
SQL提供了很多现成函数,常用的大致可分为单行函数和分组函数。
- 单行函数为处理一条数据,输出一个结果,如对字符串的处理等。
- 分组函数又称聚合函数、统计函数或组函数,是对多条记录的统计结果,如求和等。
单行函数
根据处理的数据类型不同,单行函数又可细分为字符函数、数学函数、日期函数、流程控制函数等
-
字符函数
LENGTH(str) 返回字符串长度select length('123'); select length(firstname);
执行结果:
CONCAT(str1,str2) 拼接字符串
-- concat 字符串连接
select CONCAT(first_name,'-',last_name) from employees;
执行结果:
UPPER(str) | LOWER(str) 转换为大/小写
-- UPPER/LOWER转换大小写
select UPPER(first_name) from employees;
select LOWER(CONCAT(first_name,'-',last_name)) from employees;
SUBSTR(str,pos,len) 截取字符串,字符串索引从1开始
-- substr截取字符串
select SUBSTR('my name is lihua' FROM 4 FOR 4);
SELECT SUBSTR('my name is lihua' FROM 4 );
INSTR(str,substr) 返回子串第一次出现的索引,字符串索引从1开始
-- instr 返回字符索引
select INSTR('ohhhhhhh','hh');
-- trim首位去除规定字符,默认空格
select TRIM(' my name is xiao ming ');
select TRIM('1'from '111 my name is xiaoming 111');
lpad\rpad用字符左右填充
-- lpad\rpad用字符左右填充
select LPAD('left',10,'*');
select RPAD('right',10,'*')
执行结果:
Replace字符串替换
-- replace字符串替换
select REPLACE('my name is xiaoming','xiaoming','zhangsan');
执行结果: