记录MariaDB-SQL语句学习过程

标准SQL语句可分为以下五类。

  • DQL :查询语言,用于查询库,表,数据等等,关键字:select ,凡拥有关键字的都是DQL。
  • DML :数据操作语言,用于操作数据表内数据,关键字:insert(增),delete(删),update(改),凡拥有关键字的都是DML。
  • DDL :数据定义语言,用于操作数据表本身结构等,关键字:create(增), drop(删),alter(改),凡拥有关键字的都是DDL。
  • TCL :事务控制语言 提交事务:commit,回滚事物:rollback
  • DCL :数据控制管理 授权 :grant,撤销授权:revoke 等等

MySQL常用命令

exit //退出
show databases; //查看有哪些数据库
show tables;  //查看当前数据库有哪些表

select version(); //查看数据库版本
select database(); //查看当前使用的数据库
select * from tablesName; //查看tablesName的所有(*)数据

desc tablesName
describe  tablesName //查看tablesName表的结构

create database mydb; //创建一个叫mydb的数据库

use mydb; //选择使用mydb数据库

SQL查询-SELECT

在SQL语句中不区分大小写但为了更直观,将SQL的关键字大写以做区分。

SELECT * FROM tablesName;

<*>代表全部所有的意思,tablesName代表表名。


SELECT name FROM tablesName;
SELECT name, id, times FROM tablesName;

代表查询字段,多字段用<,>英文逗号分隔。


SELECT name, id as ID FROM tablesName;
SELECT name, id ID FROM tablesName;

将查询结果中id字段显示为ID,效果为起别名显示,对原表无修改作用。as 关键字可省略为空格不写。


SELECT name, id  'I D' FROM tablesName;
SELECT name, id "I D" FROM tablesName;

别名中有空格需要用单引号或者双引号包括,建议使用单引号(SQL语法标准),因双引号在其他数据库如Oracle中不支持。


SELECT name '姓名', id '编号' FROM tablesName;

如果别名是中文需要单引号扩起来。


SELECT name, id+10 FROM tablesName;

字段可以进行简单数学公式计算显示。


SQL 条件查询

追加where关键字,使用常规条件表达式筛选符合条件的数据。 关键字:and,orin,is null,not,linkbetween ... and ...,like

SELECT  name, id FROM tablesName WHERE id < 10;

查询出id小于10的并打印name,id。


SELECT  name, id FROM tablesName WHERE id < 10 AND id > 3;

查询出id小于10并且又大于3的并打印name,id。


SELECT name, id FROM tablesName WHERE name = 'root';

查询出name等于’root’的并且打印name,id。


SELECT name, id FROM tablesName WHERE id BETWEEN 10 AND 20;

使用BETWEEN x1 AND x2 方法,但必须保证x1小于x2,遵循前小后大。


SELECT name, id FROM tablesName WHERE name is null;

!> 注意!如果需要查询出空值(Null)的数据项,无法使用 name = null 因为空值无法进行比较。需要使用 name is null


SELECT name, id FROM tablesName WHERE name is not null;

查询出name不为null的并打印


SELECT name, id FROM tablesName WHERE name = 'root' or name = 'docker';

查询name 等于root docker 的数据打印


SELECT name, id, age FROM tablesName WHERE id > 10 and (age > 18  or age < 30);

andor同时出现时and优先级较高,会先执行。可以给or加括号提高优先级先计算。


SELECT name id age FROM tablesName WHERE age in(18,25,35);

查询age18,25,35的数据。类似于多个or


SELECT name, id, age FROM tablesName WHERE age not in(18,25,35);

查询age不是18,25,35的数据。


SELECT name, id, age FROM tablesName WHERE name like '%o%'
SELECT name, id, age FROM tablesName WHERE name like '%t'
SELECT name, id, age FROM tablesName WHERE name like 'r%'
SELECT name, id, age FROM tablesName WHERE name like '_o'
SELECT name, id, age FROM tablesName WHERE name like '__o'
SELECT name, id, age FROM tablesName WHERE name like '%\_%'

关键字like为模糊查询,%表示任意多个字符,_表示任意一个字符。 需要模糊查询下划线字符时使用\符号转义。


SQL 排序查询

关键字: order by ... asc/desc

SELECT name, id FROM tablesName ORDER BY id;
SELECT name,id FROM tablesName ORDER BY id ASC;
SELECT name, id FROM tablesName ORDER BY id DESC;

order by 默认为asc意为升序排列。可以自行制定排序,降序为desc


SELECT name, age FROM tablesName ORDER BY age ASC, name ASC;

多个字段排序时使用英文逗号,分隔,但只有在前一个排序字段相等的时候才会执行后一个排序字段,也就是说如果这里的age 值全部不相同时根本不会执行name的排序,只有age出现相同值时才会使用name来确认排序顺序。


SELECT     3
    ....
FROM       1
    ....
WHERE      2
    ....
ORDER BY   4
    ....

执行顺序:FROM->WHERE->SELECT->ORDER BY。


SELECT 
    name AS '姓名', id AS '编号', age AS '年龄'
FROM
    tablesName
WHERE
    id BETWEEN 1 AND 20
ORDER BY
    age ASC, name ASC, id ASC;

从表中查询出id在1到200的人员数据,并且按照age年龄升序排列,在年龄相同情况下使用姓名升序排列,同名情况下使用id升序排列,最后打印。


MySQL函数

在MySQL中函数分为两种:单行处理函数与多行处理函数(又名:分组函数,聚合函数)。

  • 单行处理函数特点为一行一行处理数据,有多少行就处理多少行,处理时行与行的数据没有关联交互,只是针对单个处理。
  • 多行处理函数特点为集合全部行数据一起处理,处理时数据存在相互关联交互,针对所有行数据集合处理。

单行函数

关键字:

lower()        //转小写
upper()        //转大写
substr()       //取子串(截取字符串)
length()       //取长度
trim()         //去除空格

str_to_date()  //字符串(特定格式)转时间
data_format()  //格式化日期
format()       //设置千分位
orund ()       //四舍五入
rand()         //随机数
ifnull()       //null处理函数 null转为指定值

i> 日期相关的两个函数因为使用关系放到了另一篇文章

SELECT upper(name) FROM tablesName;
SELECT lower(upper(name)) FROM tablesName;

将姓名字段转为大写显示 将大写姓名转为小写显示 函数在写语句时可以视为返回值,可以嵌套使用。


select trim(substr(name,1,length(name))) from user;

截取name字段中的字符,从1开始到它本身的长度停止,然后去除空格。

!> 注意! substr() 函数的起始下标是1,不是常见的0开始。


SELECT 
    concat(upper(substr(User,1,1)),substr(User,2,length(User)-1)) name  
FROM 
    user;

将mysql库的user表内User字段显示为首字母大写,先截取首字母转为大写,在截取首字母之后的字符串,最后用concat拼接在一起显示。 效果:

name
Root
Mariadb.sys
Root

SELECT  name, ifnull(id,0)  FROM  tablesName

打印姓名与编号,编号空值为null时用0代替。


SELECT  orund(rand()*100,0)  FROM tablesName;
SELECT  orund(rand()*100,-1)  FROM tablesName;

rand()生成随机数(0~1之间),然后乘以100 最后用orund()四舍五入取整 orund()参数可为负数意为向小数点前取整


  • 特殊单行处理函数(类if语句) case when ... then ... when ... then .... else .... end 用于模拟if语句效果。
SELECT 
    name,  id, 
    (case id when 0 then upper(User) else User end) as tmp 
FROM
    tablesName;

将编号等于0的人姓名大写,别名为tmp。其他的不变。


分组函数(多行函数,聚合函数)

关键字:

count    //计数
sum      //求和
avg      //平均数
min      //最小
max      //最大

分组函数必须先对数据分组然后使用。 如果没有分组,那么会将一整张表视为一组。

i>分组函数会自动忽略null,不需要手动处理。


SELECT
    count(User) as '员工数量',
    sum(age) as '年龄之合',
    avg(age) as '平均年龄',
    min(age) as '最小年龄',
    max(age) as '最大年龄'
FROM
    tablesName;

查询有员工数量及年龄的各项数据。


分组-group by

关键字: group by

理一下执行顺序。

SELECT      4  查询哪些字段
    ...
FROM        1  那张表
    ...
WHERE       2  符合哪些条件
    ...
GROUP BY    3  把什么字段分组(不写默认一张表为一组)
    ...
ORDER BY    5  数据怎么排序显示
    ...

!> 注意! 因为 GROUP BY 在 WHERE 之后,所以在 WHERE 内无法使用分组函数,因为在这个阶段还没有组这个东西, SELECT 内可以使用是因为它在 GROUP BY 之后,就算你没有写分组语句,也会执行但效果就是会将这张表分为一组。

SELECT 
    deptno as '部门', max(sal) as '最高工资'
FROM 
    tablesName
GROUP BY
    deptno;

将员工数据按部门分组,然后查询之各个部门的最高工资。 !> 注意! 在使用分组之后,SELECT 内就只能查询分组的字段和分组函数。 不然打印的字段数据无意义,无法对应。 在MySQL中可以执行但无意义,在Oracle中无法执行。


SELECT
    deptno, job, max(sal)
FROM
    tablesName
GROUP BY
    deptno, job;

deptno,job分组,然后求每个部门内每个工作岗位的最高工资。


分组匹配-having

having用于处理筛选where无法提前筛选的数据。 有功能重合,差别在于having可以使用分组函数完成where无法做到的工作 注意!where可以完成的筛选工作不应该由having完成,因为会浪费资源。

SELECT
   deptno, avg(sal)
FROM
    tablesName
GROUP BY
    deptno
HAVING
    avg(sal) > 2500;

以上求平均薪资大于2500的部门,在where无法使用分组函数完成筛选的情况下,才可以使用having筛选。


SELECT
    job, avg(sal) as avgsal
FROM
    tablesName
WHERE
    deptno <> 'MANAGER'
GROUP BY
    job
HAVING
    avg(sal) > 1500
ORDER BY
    avgsal desc;

完整演示,执行顺序为:FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY 查询除MANAGER之外每个岗位评价薪资大于1500的按降序排列。


去重 distinct

SELECT  distinct, job  FROM  tablesName;
SELECT  distinct job, deptno  FROM  tablesName  ORDER BY deptno, job  asc
SELECT  count(distinct job)  FROM  tablesName;

1.将工作岗位去重显示(去除相同的岗位之保留一个):

job
CLERK
SALESM
MANAGER
ANALYST
PRESIDENT

2.将工作岗位和部门编号去重然后排序显示

job deptno
CLERK 10
MANAGER 10
PRESIDENT 10
ANALYST 20
CLERK 20
MANAGER 20
CLERK 30
MANAGER 30
SALESMAN 30

3.将显示结果计数,达到计算多少个工作岗位


连接查询

又称跨表查询,是多张表联合起来查询数据

语法按时间分为sql92和sql99 sql92对应1992年出现的语法 sql99对应1999年出现的语法

连接查询又分为 内连接: 等值连接,非等值连接,自连接 外连接:左外连接(又称左连接),右外连接(又称右连接) 全连接:待更….

内连接查询

SELECT 
    ename, dname
FROM
    emp , deptno;
  • sql92

!> 注意! 如果不加限制直接连接表查询 会出现笛卡尔积现象(维基百科) 表数据会一行一行与其他表全部行进行对比,emp表数据行乘以deptno表数据行的对比次数,性能损耗严重。


内连等值连接

SELECT
    e.ename, d.dname
FROM
    emp e, deptno d
WHERE
    e.deptno = d.deptno;
  • sql92 通过对比部门编号查询对应的部门名称。 在from内给表起别名,select中使用别名,起到确定该字段属于哪张表的作用减少性能开支,在where中设置部门编号相等条件,避免笛卡尔积现象。 执行效果:
    ename dname
    SMITH RESEARCH
    ALLEN SALES
    WARD SALES
    JONES RESEARCH
    MARTIN SALES
    BLAKE SALES
    CLARK ACCOUNTING
    SCOTT RESEARCH
    KING ACCOUNTING
    TURNER SALES
    ADAMS RESEARCH
    JAMES SALES
    FORD RESEARCH
    MILLER ACCOUNTING

i> 尽量避免表的连接次数,连接次数越多效率越低。


SELECT
    e.ename, d.dname
FROM
    emp e
INNER JOIN
    deptno d
ON
    e.deptno = d.deptno
WHERE
    e.ename <> 'KING'
  • sql99 inner 意为内连接,join 用于表连接,ON用于表连接匹配条件。 这是sql99的内连接等值语法。 99与92不同的是将表与表的匹配条件独立在了新的关键字join 中,不会像sql92一样表匹配条件和表字段筛选条件混合在一起,结构看起来更加清晰。

SELECT
    e.ename, e.sal, s.grade  
FROM
    EMP e   
INNER JOIN  
    SALGRADE s   
ON  e.sal between s.losal and s.hisal
ORDER BY 
    s.grade asc;

内连非等值连接

表匹配条件不是等某个值。 显示员工的姓名,工资,工资等级。

ename sal grade
SMITH 800.00 1
ADAMS 1100.00 1
JAMES 950.00 1
WARD 1250.00 2
MARTIN 1250.00 2
MILLER 1300.00 2
ALLEN 1600.00 3
TURNER 1500.00 3
CLARK 2450.00 4
SCOTT 3000.00 4
JONES 2975.00 4
FORD 3000.00 4
BLAKE 2850.00 4
KING 5000.00 5

SELECT
    d.ename '员工', m.ename '领导'
FROM 
    EMP d 
INNER JOIN 
    EMP m 
ON 
    d.mgr = m.empno;

内连之自连接

将一张表看为两张表(通过别名)然后在使用等值条件达到显示员工名与上级领导名。 执行效果

员工 领导
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
13 rows in set (0.005 sec)

一共十四条数据只输出了十三条,因为老板的上级领导为null没有匹配到所以没有显示。


外连接查询

外连接与内连接不同的是外连接表与表之间有主次关系。内连接没有主次关系,表之间平等。 外连接查询时会将主表数据全部查出,次表只查询匹配到的。

SELECT
    e.ename '员工', d.ename '领导'
FROM 
    EMP e 
LEFT OUTER JOIN 
    EMP d 
ON 
    e.mgr = d.empno;


SELECT
    e.ename '员工', d.ename '领导'
FROM 
    EMP d
RIGHT OUTER JOIN 
    EMP e
ON 
    e.mgr = d.empno;

left为左外连接,会将左边的表(from内的表)视为主表。 right为右外连接,会将右边的表(join内的表)视为主表。 outerinner性质一样,用于标识内外链接,但实际上区分内外连接的是看join前有没有left,right,为了看起来规范直观,但也可以省略不写。

  • 所有的左连接语句都有对应的右连接语句,是可以通过互换表位置达到一样效果的,如下面显示的这两个语句的查询结果(效果一样所以只放一个)。

在内连接无法查询到king的上级,所以不会显示king,但在外连接中e表(员工表)为主表,会查出所有员工名,在匹配次表的数据,没有的以null填充。 查询结果

员工 领导
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING NULL
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
14 rows in set (0.010 sec)

SELECT
    e.ename '员工姓名',
    e.sal '工资',
    s.grade '工资等级',
    d.ename '领导姓名',
    de.dname '部门名称'  
FROM 
    EMP e 
LEFT JOIN 
    EMP d 
ON 
    e.mgr = d.empno 
JOIN 
    SALGRADE s 
ON 
    e.sal between s.losal and s.hisal 
RIGHT JOIN 
    DEPT de 
ON 
    e.deptno = de.deptno
ORDER BY
    e.sal;

多表内外联合查询 e表左外自连,e表为主表,通过条件获取上级领导姓名,然后e表在于s表(工资等级表)内连,通过条件获取工作等级,然后右连接de表(部门表),通过条件获取部门名称,最后排序输出。

  • 内外连接可以混用,可以用join ... on处理多个表连接。 多表连接关系可以理解为:一次连接就是一次加法,然后用加法结果(表)去连接下一个表)

查询结果

员工姓名 工资 工资等级 领导姓名 部门名称
NULL NULL NULL NULL OPERATIONS
SMITH 800.00 1 FORD RESEARCH
JAMES 950.00 1 BLAKE SALES
ADAMS 1100.00 1 SCOTT RESEARCH
MARTIN 1250.00 2 BLAKE SALES
WARD 1250.00 2 BLAKE SALES
MILLER 1300.00 2 CLARK ACCOUNTING
TURNER 1500.00 3 BLAKE SALES
ALLEN 1600.00 3 BLAKE SALES
CLARK 2450.00 4 KING ACCOUNTING
BLAKE 2850.00 4 KING SALES
JONES 2975.00 4 KING RESEARCH
SCOTT 3000.00 4 JONES RESEARCH
FORD 3000.00 4 JONES RESEARCH
KING 5000.00 5 NULL ACCOUNTING
15 rows in set (0.009 sec)

子查询

就是嵌套在其他关键字之内的select被称为子查询。 select可以出现的位置:

SELECT
    ... select ...
FROM
    ... select ...
WHERE
    ... select ...

出现在这些位置的select被称为子查询。


WHERE-select

SELECT
    sal
FROM
   EMP
WHERE
    sal > (select min(sal) from EMP);

where中的子查询是将子查询结果当成值来处理。
用子查询先查询出了最小的工资,然后用来where条件筛选。 结果就是查询除最低工资外的所有工资


FROM-select

SELECT
    t.*, s.grade 
FROM 
    (select job, avg(sal) avgsal from EMP GROUP BY job) t 
JOIN 
    SALGRADE s 
ON 
    t.avgsal between s.losal and s.hisal 
ORDER BY 
    s.grade;
  • 查询岗位的平均工资和工资等级 from中的子查询是将查询结果当做一张临时表来处理。 将子查询结果当临时表并起别名为t,然后t与s表连接,匹配出工资等级,然后查询t表所有字段与s表的工资等级,最后排序输出。

!> 注意! 如果不给子查询中分组函数所对应的字段起别名,会在表连接或者其他地方使用时(此时该字段名为’avg(sal))因为字段带有()`缘故会被认为是函数处理,造成错误) 查询结果:

job avgsal grade
CLERK 1037.500000 1
SALESMAN 1400.000000 2
ANALYST 3000.000000 4
MANAGER 2758.333333 4
PRESIDENT 5000.000000 5

SELECT-select

SELECT
    e.ename, 
    e.deptno,
    (select d.dname from DEPT d WHERE e.deptno = d.deptno) as dname
FROM
    EMP e
ORDER BY
    e.deptno;
  • 查询员工名,部门编号,部门名 select中的子查询是将子查询结果当做字段数据列进行拼接 !> 注意! select 中的子查询只能返回一条数据,多于一条会报错 查询结果
    ename deptno dname
    MILLER 10 ACCOUNTING
    KING 10 ACCOUNTING
    CLARK 10 ACCOUNTING
    FORD 20 RESEARCH
    ADAMS 20 RESEARCH
    SCOTT 20 RESEARCH
    JONES 20 RESEARCH
    SMITH 20 RESEARCH
    BLAKE 30 SALES
    MARTIN 30 SALES
    TURNER 30 SALES
    WARD 30 SALES
    JAMES 30 SALES
    ALLEN 30 SALES

union

union用于处理一些情况下的大量表连接的查询,可以减少表连接次数,提高性能。

SELECT
    ename,job 
FROM
    EMP 
WHERE
    job = 'MANAGER' 
union
SELECT ename , job 
FROM
    EMP
WHERE
    job = 'SALESMAN';

暂时没有好的试例:只演示使用方法 !> 注意! union 集合结果集时,列的数量和列的数据类型必须统一。

mysql中规则较宽松,在Oracle中需要严格按照标准。 查询结果:

ename job
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN

limit

将查询结果集分一部分取出来,多用于分叶查询。 i> ‘limit 0,5’ 0为起始下标,5为取值长度。

SELECT 
    ename, sal 
FROM 
    EMP 
ORDER BY 
    sal desc 
LIMIT 0,5;

将工资降序排列,然后使用limit取从0开始长度为5的数据显示。 !> 在mysql中limit在order by之后执行 查询结果:

ename sal
KING 5000.00
FORD 3000.00
SCOTT 3000.00
JONES 2975.00
BLAKE 2850.00
SELECT 
    ename, sal 
FROM 
    EMP 
ORDER BY 
    sal desc 
LIMIT (pageno -1) *pagSize,pageSize;

简单分页公式:limit (pageNo -1)*pageSize,pageSize ,分叶号减一乘以长度。

执行顺序总结

SELECT
    ...
FROM
    ...
WHERE
    ...
GROUP BY
    ...
HAVING
    ...
ORDER BY
    ...
LIMIT
    ...;

√> 1 FROM, 2 WHERE, 3 GROUP BY, 4 HAVING, 5 ORDER BY, 6 LIMIT