Java教程

sql语句汇总

本文主要是介绍sql语句汇总,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

利用sql语句来操作数据库

创建数据库

语法:

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name]
  • 其中charset_name是为数据库指定的默认字符集

  • Collate是为数据库指定的默认校对规则(校对规则是在字符集内用于比较字符的一套规则,可以控制select查询时where条件大小写是否敏感的规则。)

示例一:创建一个名称为mydb1的数据库。

CREATE  DATABASE  IF NOT EXISTS  mydb1;

示例二:创建一个使用gbk字符集的mydb2数据库。

CREATE  DATABASE  IF NOT EXISTS  mydb2 CHARACTER SET gbk;

查看数据库

语法:

# 显示所有数据库
SHOW DATABASES
# 显示数据库创建语句
SHOW CREATE DATABASE db_name

修改数据库

注意: 数据库一旦创建成功, 其名字无法修改

语法:

ALTER DATABASE db_name [CHARACTER SET charset_name] [COLLATE collation_name]

示例:查看服务器中的数据库,并把mydb2库的字符集修改为utf8

ALTER DATABASE mydb2 CHARACTER SET utf8

删除数据库

语法:

DROP DATABASE [IF EXISTS] db_name 

选择数据库

语法

# 选择数据库 
USE db_name
# 查询当前选择的数据库 
SELECT DATABASE()

没有退出数据库的命令, 如果想退出当前数据库进入另一个数据,直接use切换到另一个数据库就可以了

利用sql语句来操作数据库表

创建表

CREATE TABLE student 
(
    student_id varchar(20) PRIMARY KEY COMMENT '学生编号',
    student_name varchar(20) NOT NULL COMMENT '学生姓名',
    gender char(10) NOT NULL DEFAULT '男' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(20) NOT NULL DEFAULT 'C100' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生表';
--查询表结构
desc student

注意

  • 不区分大小写,但最好关键字都大写

  • 关键字COMMENT设置备注

  • 除主键字段外,建议其他所有字段都设置默认值,根据字段的业务含义赋默认值

  • 永远不要更新主键的值

  • 判断是否为空 gender is null / gender is not null

字符串类型

  • 定长字符串 — char(n):长度固定的字符串类型,其长度是在创建表时指定的,默认开辟n个字符的空间大小,效率高。
  • 不定长字符串 — varchar(n):长度不固定的字符串类型,创建表时指定一个最大长度,或不指定长度。先判断字符长度,开辟对应长度的空间,效率低,节省空间。

数值类型

  • TINYINT: 占用1个字节,相对于java中的byte
  • SMALLINT:占用2个字节,相对于java中的short
  • INT:占用4个字节,相对于java中的int
  • BIGINT:占用8个字节,相对于java中的long
  • FLOAT:4字节单精度浮点类型,相对于java中的float
  • DOUBLE:8字节双精度浮点类型,相对于java中的double

日期类型:

  • DATE:日期 2017-11-05

  • TIME:时间 格式 ‘HH:MM:SS’ 19:19:19

  • DATETIME:日期时间 2017-11-05 19:19:19 年份范围:1000~9999

  • TIMESTAMP:时间戳 2017-11-05 19:19:19 年份范围:1970~2037

逻辑型:

  • BIT型字段只能取两个值:0或1。

字段的约束

  1. 主键约束:保证所约束列中的值必须唯一且不能为空。

    • 添加主键约束(创建表时): col_name datatype primary key
    • 更新表时:Alter table tabname add primary key(col_name)
    • 设置主键自动增长:col_name datatype primary key auto_increment
  2. 唯一约束: unique 保证所约束列的值必须唯一,即不能重复。

    NULL和任何值都不等,包括它本身

  3. 非空约束: not null 保证所约束列的值不能为空

  4. 外键约束

操纵表

  • 更新表名: RENAME TABLE 旧表名 TO 新表名;

  • 更新字段名:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型;

  • 更新字段类型:ALTER TABLE 表名 MODIFY 字段名 新字段类型;

  • 删除字段默认值:ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;

  • 设置主键:ALTER TABLE 表名 ADD PRIMARY KEY (主键字段列表);

  • 删除主键字段:ALTER TABLE 表名 DROP PRIMARY KEY;

  • 新增字段: ALTER TABLE 表名 ADD COLUMN 新字段名 字段类型及其他属性;

  • 删除字段: ALTER TABLE 表名 DROP COLUMN 字段名;

  • 删除表:DROP TABLE 表名;

RENAME TABLE teacher_db TO teacher;
ALTER TABLE teacher CHANGE teacher_id id varchar(20);
ALTER TABLE teacher MODIFY id varchar(30);
ALTER TABLE teacher DROP PRIMARY KEY;
ALTER TABLE teacher ADD PRIMARY KEY(id);
ALTER TABLE teacher ALTER COLUMN teacher_name DROP DEFAULT;
ALTER TABLE teacher DROP COLUMN gender;
ALTER TABLE teacher ADD COLUMN gender varchar(10) DEFAULT '男' COMMENT '性别';
SELECT * FROM teacher;
  • 插入数据

    • 插入整行字段:

      1. INSERT INTO 表名 VALUES (v1,v2,v3,v4);
      • 值的顺序与表中的字段顺序必须一致,不安全
      1. INSERT INTO 表名(字段1,字段2,字段3,字段4) VALUES (v1,v2,v3,v4);
      • 采用这种方式一一对应,安全
    • 插入部分字段:INSERT INTO 表名(字段1,字段2) VALUES (v1,v2);

    • 一次插入多行:

      • INSERT INTO 表名 VALUES (v1,v2,v3,v4), ('v1,v2,v3,v4);
    • 借助其他结果集插入:

      • INSERT INTO 表1 SELECT * FROM 表2;
    • 克隆表

      • SELECT * INTO teacher_bak FROM teacher;
      • CREATE TABLE teacher_bak AS SELECT * FROM teacher;
    SELECT * FROM teacher;
    INSERT INTO teacher VALUES('T100','李铭','男');
    INSERT INTO teacher(id,teacher_name)
           VALUES('T101','李刚'),
                 ('T102','李小龙'),
                 ('T103','王力');
    CREATE TABLE teacher_bak
    AS
    SELECT * FROM teacher;
    SELECT * FROM teacher_bak;
    
  • 更新数据

    UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]    
    #示例:
    UPDATE employee SET salary = 30000;
    
  • 删除数据

    DELETE FROM tab_name [WHERE where_definition]
    # 示例:
    DELETE FROM employee WHERE name IS NULL;
    
    1. WHERE用来筛选要删除的记录,如果不使用WHERE子句,将删除表中所有数据 (逐条删除,效率不高)。

      TRUNCATE TABLE 表名 (删除表中所有记录,直接摧毁表,然后重建表,效率高)

    2. DELETE 语句不能删除某一列的值,应使用ALTER TABLE 表名 DROP COLUMN 列名

    3. delete语句仅删除记录,不删除表本身。如要删除表,使用DROP TABLE 表名

查询数据

  • 查询部分列:SELECT 字段列表 FROM 表名;

  • 查询所有列

    • SELECT * FROM 表名;
    • SELECT 所有字段列表 FROM 表名;
  • 排序返回结果

    • SELECT 字段列表 FROM 表名 ORDER BY 字段1 [ASC/DESC],字段2 [ASC/DESC]…;
    • 默认为升序排序ASC
  • 去重

    • SELECT DISTINCT 字段名 FROM 表名 LIMIT 10

    • 限制返回结果

      • 返回前几行:关键字TOP、LIMIT

        • SELECT TOP 10 字段列表 FROM 表名;

        • SELECT 字段列表 FROM 表名 LIMIT 10;

        • 一般与ORDER BY关键字配合使用,返回按某些字段排序后的前几行;

        • Mysql数据库不支持top的写法,仅支持Limit的写法

      • 返回中间几行:关键字LIMIT M OFFSET N(从第N行开始,返回M行记录)

        • SELECT 字段列表 FROM 表名 LIMIT M OFFSET N;
        • SELECT 字段列表 FROM 表名 LIMIT N,M;
        • 一般与ORDER BY关键字配合使用,按M行为一页,返回某一个分页的记录;
      • 返回后几行: 关键字TOP、LIMIT、ORDER BY

        • SQL语句中,没有返回后几行的专用写法,一般转换为按返回前几行的相反方式排序后,再返回前几行。使用这种方式变相的返回后几行。
      SELECT student_id, student_name, birth_day, age FROM student;
      SELECT student_id, student_name, birth_day, age FROM student 
      ORDER BY age ASC, student_name ASC;
      /*等价于*/
      SELECT student_id, student_name, birth_day, age FROM student 
      ORDER BY 4 ASC, 2 ASC;
      SELECT  student_id, student_name, birth_day, age FROM student 
      ORDER BY 4 ASC, 2 ASC
      LIMIT 10;
      SELECT  student_id, student_name, birth_day, age FROM student 
      ORDER BY 4 ASC, 2 ASC
      LIMIT 10,20
      

过滤数据

  • 使用WHERE子句:SELECT 字段列表FROM 表名WHERE 过滤条件;

  • 常用操作符
    在这里插入图片描述

  • 过滤单个值

    SELECT * FROM student WHERE birth_day <= '2005-12-31';

  • 过滤NULL值

    SELECT * FROM student WHERE class_id IS NULL;

  • 过滤集合

    SELECT * FROM student WHERE birth_day BETWEEN ‘2005-01-31’ AND ‘2005-12-31’;

    SELECT * FROM student WHERE age IN (10,11,15)

    SELECT * FROM student WHERE student_id NOT IN ('S20160001','S20160002')

    IN不支持通配符

  • 使用通配符过滤数据:关键字LIKE

    符号含义
    百分号%匹配0~多个任意字符
    下划线_匹配1个任意字符
    方括号[]、[^]匹配1个字符集中的字符

    SELECT * FROM student WHERE student_name LIKE ‘陈%’

    Mysql中的转义符\

    Mysql数据库不支持[]和[^],Sql Server支持

    使用通配符的注意点:

    • 不要过度使用通配符;
    • 如果确实需要使用,也尽量不要把通配符用在匹配模式的开始处;
    • 要特别注意通配符的位置,否则很有可能返回的结果与预期不一致;
  • 组合WHERE子句

    • 使用逻辑操作符组合WHERE子句:

    • 比如:如何取出所有年龄大于15岁并且姓陈或男性同学?
      SELECT * FROM student WHERE age > 15 AND (student_name LIKE '陈%' OR gender = '男');

    • 在sql语句中 ANDOR 优先级相同

    • AND、OR谁在前面先执行谁

    • 在条件比较多的时候选用合理的()来消除歧义

      -- 知道老师的身份证号,想取出70年代出生的所有老师?
      -- 7-14位表示出生日期
      ALTER TABLE teacher ADD COLUMN identity_num char(18) DEFAULT '622301197012312360';
      SELECT * FROM teacher WHERE identity_num REGEXP '[0-9]{6}197.[0-9]{8}';
      

创建新的输出字段

  • 数值计算 操作符:+, -, *, /

    SELECT trans_id,stock_name,price,volume,price*volume FROM t_stock_trans_dtl;

    注意:Teradata: INT / INT 返回INT类型

  • 字段拼接

    示例:如何按“洪都航空(sh600316)”展示股票名称和股票代码?

    1. Access、SQL Server:使用拼接操作符加号(+)

    SELECT stock_name + '(' + stock_code + ')' ​ FROM t_stock_trans_dtl;

    1. DB2、Oracle、Teradata:使用拼接操作符两个竖杠(||)

      SELECT stock_name || '(' || stock_code || ')' FROM t_stock_trans_dtl;

    2. MySQL:使用函数 CONCAT()

      SELECT CONCAT(stock_name,'(',stock_code,')') FROM t_stock_trans_dtl;

    3. 实现数值型字段的拼接

      CAST()函数可以将某种数据类型的表达式转化为另一种数据类型
      SELECT CAST('678' AS INT) + 123;

      CONVERT()函数可以将指定的数据类型转换为另一种数据类型
      SELECT CONVERT(INT, '678') + 123;

  • 使用别名: 关键字AS

    SELECT CONCAT(stock_name,'(',stock_code,')') AS '股票名称和代码' FROM t_stock_trans_dtl;

    建议:不论中文别名中是否有空格,都用引号引起来

  • CASE WHEN逻辑转换

    • CASE WHEN语法一:

      CASE WHEN 条件表达式1 THEN 结果表达式1
      	 WHEN 条件表达式2 THEN 结果表达式2
      				…
      	 WHEN 条件表达式N THEN 结果表达式N
      	 [ELSE ELSE 结果表达式]
      END
      

      没有ELSE部分时,如果所有条件表达式都不满足,则返回NULL。

      SELECT * FROM t_stock_trans_dtl;
      SELECT trans_id, volume,
      CASE WHEN volume >=3000 THEN '大单'
           WHEN volume <3000 and volume > 300 THEN '中单'
           ELSE '小单'
      END
      AS '买入量'
      FROM t_stock_trans_dtl
      WHERE opt_typ = '买入';
      

      每一个CASE WHEN都应该有ELSE分支

    • CASE WHEN语法二:

      CASE 条件表达式
      	WHEN 匹配表达式1 THEN 结果表达式1
      	WHEN 匹配表达式2 THEN 结果表达式2
      	…
      	WHEN匹配表达式N THEN 结果表达式N
      	[ELSE ELSE结果表达式]
      END
      

      适用于条件表达式的结果,是一个可穷举的列表。但不适用于条件表达式的结果是一个范围的情况。

      SELECT trans_id, volume,
      CASE opt_typ
           WHEN '买入' THEN price*volume
           WHEN '卖出' THEN 0 - price*volume
           ELSE 0
      END
      AS '交易金额'
      FROM t_stock_trans_dtl;
      
    • 嵌套CASE WHEN:

      CASE WHEN 外层条件表达式1 THEN (CASE WHEN 内层条件表达式1 THEN 内层结果表达式1
      											…
      								   WHEN 内层条件表达式M THEN 内层结果表达式M
      								   ELSE 内层ELSE结果表达式
      							   END)
      	…
      	WHEN 外层条件表达式N THEN 外层结果表达式N
      	ELSE 外层ELSE结果表达式
      END
      

      两种语法格式,都可以多层嵌套,但不推荐多层嵌套。建议最多嵌套两层并使用括号格式化SQL语句。

使用函数创建新的输出字段

  • 数值函数

    函数作用示例
    abs(n)返回n的绝对值abs(-3) = 3
    round(n,d)返回n的四舍五入值,保留d位小数round(3.234,2) = 3.23
    rand()返回0~1之间的随机数0.2511382673
    pow(x,y)返回x的y次幂pow(2,3) = 8
    mod(m,n)返回m除以n的余数mod(7,3) = 1
    三角函数实现三角运算的函数sin(90)
    SELECT ABS(volume) AS 'volume', ROUND(fee,2) AS 'fee' FROM  t_stock_trans_dtl
    
  • 文本函数

    函数作用示例
    length(str)返回字符串str的长度length(‘ABCDE’) = 5
    left(str,n)返回字符串str的左端n个字符left('ABCDE’,3) = ‘ABC’
    right(str,n)返回字符串str的右端n个字符right('ABCDE’,3) = ‘CDE’
    trim(str)删除str左右两边的空格trim(‘ ABC ‘)=‘ABC’
    upper(str)将字符串str转为大写upper(‘Abc’) = ‘ABC’
    lower(str)将字符串str转为小写lower(‘Abc’) = ‘abc’
    substring(str,m,n)返回从字符串str第m位后面的n个字符substring('ABCDE’,2,3) = ‘BCD’
    instr(str1,str2)返回字符串str2在str1中首次出现的位置instr(‘foobarbar’, 'bar’) = 4
    concat(str1,str2,…)返回字符串str1,str2,…按顺序拼接后的结果concat(‘ABC’,‘DEFG’,'H’) ‘ABCDEFGH’
    -- 如何根据股票交易明细表中的股票代码,返回股票所在的证券交易所?
    SELECT stock_code,left(stock_code,2) FROM t_stock_trans_dtl;
    SELECT stock_code,substring(stock_code,1,2) FROM t_stock_trans_dtl;
    
  • 日期和时间函数

    函数作用示例
    current_date()返回当前日期’2018-02-27’
    current_time()返回当前时间’12:34:10’
    year(date)返回日期date所在的年份year(‘2018-02-27’) = 2018
    month(date)返回日期date所在的月份month(‘2018-02-27’) = 2
    day(date)返回日期date所在的日day(‘2018-02-27’) = 27
    hour(date)返回日期date所在的小时hour(’12:40:32’) = 12
    minute(date)返回日期date所在的分钟minute(’12:40:32’) = 40
    dayofyear(date)返回date是当年的第几天dayofyear(’2018-02-27’) = 58
    dayofmonth(date)返回date是当月的第几天dayofmonth(’2018-02-27’) = 27
    dayofweek(date)返回date是当周的第几天(第1天是周日)dayofweek(’2018-02-27’) = 3
    date_format(date,格式)返回date格式化后的结果date_format(current_date(),’%Y’)= 2018
  • IF函数(三元运算符的效果)

    • SELECT IF(10< 5, '大', '小')
  • 函数的嵌套

    -- 如何根据股票交易明细表中的股票代码,返回股票所在的证券交易所(要求返回大写字母)?
    SELECT stock_code,upper(left(stock_code,2)) FROM t_stock_trans_dtl;
    

汇总统计

  • 汇总统计

    总共有多少名学生?         SELECT COUNT(*) FROM student;
    所有学生的数学成绩总和?  	SELECT SUM(score) FROM student;
    所有学生的数学成绩最高分?   SELECT MAX(score) FROM student;
    所有学生的数学成绩最低分?	SELECT MIN(score) FROM student;
    所有学生的数学成绩平均分?	SELECT AVG(score) FROM student;
    
    • 统计结果忽略值为NULL的行!

    • 可以和DISTINCT搭配去重

      例如:SELECT COUNT(DISTINCT score) FROM student;

    • 和分组函数一起查询的字段要求是GROUP BY后的字段

  • 分组汇总统计 关键字:GROUP BY

    SELECT 分组函数, 列(要求出现在 GROUP BY 后面)
    FROM 表名
    [WHERE 筛选条件]
    GROUP BY 分组的列表
    [ORDER BY 子句] 
    

    比如:如何获取每个班级的数学成绩平均分(因为学生太多,这里先不计算成绩在80分以下的学生),以便比较不同班级的成绩?

    SELECT class_id,avg(score) FROM student WHERE score > 80 GROUP BY class_id;
    

在这里插入图片描述

-- 如何统计不同的交易所的总的买入金额?
SELECT left(stock_code,2) AS 'location',sum(price*volume) AS '成交量'
FROM t_stock_trans_dtl
GROUP BY location;

-- 如何统计不同的交易所的不同交易类型的总的交易金额
SELECT left(stock_code,2) AS 'location',opt_typ,abs(sum(price*volume)) AS '成交量'
FROM t_stock_trans_dtl
GROUP BY location, opt_typ;
  • 过滤分组结果 关键字:GROUP BY + HAVING

    • WHERE:过滤行,在GROUP BY前执行。
    • HAVING:过滤分组,在GROUP BY后执行。
    • WHERE用于过滤原始表中的数据,HAVING用于筛选分组后的结果集
    • 能够使用WHERE的,优先使用WHERE
    • 分组函数做条件一定放在HAVING子句中
  • 按多个字段分组

    案例:查询每个部门每个工种的员工的平均工资

    SELECT AVG(salary), department_id, job_id  
    FROM employees  
    GROUP BY department_id, job_id;
    
  • 排序分组结果 关键字:GROUP BY + ORDER BY

    • 集合具有无序性,如果每次查询都想得到确定的排序结果,则必须使用ORDER BY
    -- 如何获取每个班级的数学成绩平均分(不计算成绩在80分以下的学生且过滤掉平均分在90分以下的班级),然后以平均分从高到低排序?
    SELECT
    class_id,avg(score)
    FROM student
    WHERE score > 80
    GROUP BY class_id
    HAVING avg(score) > 90
    ORDER BY avg(score) DESC;
    
  • SELECT子句顺序

     一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下:
    <SELECT clause> 
    [<FROM clause>] 
    [<WHERE clause>] 
    [<GROUP BY clause>] 
    [<HAVING clause>]
    [<ORDER BY clause>] 
    [<LIMIT clause>]   
     SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。
    

    SQL Select语句完整的执行顺序:

    1、from子句组装来自不同数据源的数据; 
    2、where子句基于指定的条件对记录行进行筛选; 
    3、group by子句将数据划分为多个分组; 
    4、使用聚集函数进行计算; 
    5、使用having子句筛选分组; 
    6、计算所有的表达式; 
    7、select 的字段;
    8、使用order by对结果集进行排序。
    

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

子查询及EXISTS

外键约束

  • 外键:唯一标识其他表中的一条记录,用来通知数据库两张表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
  • 外键作用: 确保数据库数据的完整性一致性
  • 添加外键: foreign key(外键) references 关联表(关联列)
  • 外键约束会导致效率低,不灵活,所以一般开发时不用,而是通过代码维护

多表设计

  1. 一对多:在多的一方添加列保存一的一方的主键来作为外键,来保存两张表之间的关系
  2. 一对一:在任意一方添加列保存另一方的主键作为外键, 来保存两张表之间的关系
  3. 多对多:在一张第三方的表中分别保存两张表的主键作为外键,来保存两张表之间的关系,可以把多对多的关系拆分成两个一对多的关系来理解

子查询:嵌套在其他查询中的查询

  • 使用子查询作为计算字段

    -- 如何同时查询出学生编号、学生姓名、老师编号、老师姓名?
      SELECT * FROM student;
      SELECT student_id, student_name, teacher_id,
      (
       SELECT teacher_name 
       FROM teacher
       WHERE teacher.teacher_id = student.teacher_id
      )AS teacher_name 
      FROM student;
      -- 使用表别名消除字段歧义
      SELECT student_id, student_name, teacher_id,
      (
       SELECT teacher_name 
       FROM teacher b
       WHERE b.teacher_id = a.teacher_id
      )AS teacher_name 
      FROM student a;
    

    子查询只能对应一条记录

  • 使用子查询过滤数据(IN)

    -- 如何获取姓牛的老师教了哪些学生?
    SELECT student_id, student_name 
    FROM student
    WHERE teacher_id IN (
          SELECT teacher_id 
          FROM teacher
          WHERE teacher_name like '牛%' 
    );
    -- IN后是一个集合
    
  • 使用子查询过滤数据(EXISTS)

    -- 如何获取姓牛的老师教了哪些学生?
    SELECT student_id, student_name 
    FROM student
    WHERE EXISTS(
          SELECT 1 
          FROM teacher
          WHERE student.teacher_id = teacher.teacher_id
          AND teacher.teacher_name like '牛%' 
    );
    -- 如何获取除姓牛的老师之外的其他老师教了哪些学生?
    SELECT
    student_id,student_name
    FROM student a
    WHERE NOT EXISTS ( SELECT 1 FROM teacher b
                       WHERE a.teacher_id = b.teacher_id
                       AND b.teacher_name like '牛%'
    );
    
    • EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。

组合查询UNION ALL

  • 组合查询 UNION ALL

    • 用于合并多个结果集

    • 查询结果有重复数据(交集重复),得到结果集的全部数据

  • 组合查询 UNION

    • 合并结果去重
    -- 合并来源于不同的表的结果集
    -- 如何同时查询出所有的学生编号、学生姓名和老师编号、老师姓名?
    SELECT student_id,student_name FROM student
    UNION
    SELECT teacher_id,teacher_name FROM teacher;
    

    需要注意的几点:

    1. 待合并的结果集的字段数量必须一致。
    2. 合并后的结果集的title与第一个结果集保持一致。
    3. 待合并的结果集的字段顺序、字段类型的大类及字段值的含义尽量保持一
      致。
    • UNION ALL与UNION混用
    -- 同时查询出年龄为10岁或一年级一班(前面两个结果集需要去除重复)或性别为男(合并时不去除重复)的所有学生?
    SELECT * FROM student WHERE age = 10
    UNION
    SELECT * FROM student WHERE class_id = 'G0101'
    UNION ALL
    SELECT * FROM student WHERE gender = '男';
    
    1. UNION ALL与UNION的执行优先级一致,谁在前谁先执行;
    2. 不可以使用括号改变执行优先级;
  • 组合查询结果集的排序

    -- 如何同时查询出年龄为10岁或一年级一班的所有学生(按姓名升序排序)?
    SELECT * FROM student WHERE age = 10
    UNION ALL
    SELECT * FROM student WHERE class_id = 'G0101'
    ORDER BY student_name;
    

连接表

  • 关于笛卡尔积

    • 笛卡尔积:两个集合的乘积

在这里插入图片描述

  • 交叉连接CROSS JOIN

    使用CROSS JOIN实现两个集合的笛卡尔积。

    SELECT * FROM student;--3
    SELECT * FROM teacher;--4
    SELECT * FROM student CROSS JOIN teacher;--12=3*4
    
  • 内连接INNER JOIN

    示例:如何同时查询出学生编号、学生姓名、老师编号、老师姓名?
    在这里插入图片描述

    SELECT * FROM student a INNER JOIN teacher b
    ON a.teacher_id = b.teacher_id;
    
    SELECT * FROM student a INNER JOIN teacher b
    WHERE a.teacher_id = b.teacher_id;
    -- 非标准写法,MySQL独有,其他数据库不支持。不推荐!
    
    SELECT * FROM student a , teacher b
    WHERE a.teacher_id = b.teacher_id;
    -- INNER关键字可以省略
    -- 等值连接 sql92 标准
    
    SELECT 
      a.ename AS empName,
      b.ename AS managerName 
    FROM emp a,emp b 
    WHERE a.mgr = b.empno;
    -- INNER关键字可以省略 
    -- 等值连接 sql92 标准
    
  • 全外连接FULL OUTER JOIN

    示例:如何同时查询出学生编号、学生姓名、老师编号、老师姓名?
    在这里插入图片描述

    SELECT * FROM  student a FULL OUTER JOIN teacher b
    ON a.teacher_id = b.teacher_id;
    -- 虽然绝大数据数据库都支持,但MySQL不支持FULL OUTER JOIN。
    -- OUTER关键字可以省略
    

    Mysql可以使用UNION结合LEFT JOIN 和 RIGHT JOIN实现全外连接

  • 左外连接LEFT OUTER JOIN

    示例:如何同时查询出学生编号、学生姓名、老师编号、老师姓名?
    在这里插入图片描述

    SELECT * FROM student a LEFT OUTER JOIN teacher b
    ON a.teacher_id = b.teacher_id; 
    -- OUTER关键字可以省略
    
  • 右外连接RIGHT OUTER JOIN

    示例:如何同时查询出学生编号、学生姓名、老师编号、老师姓名?
    在这里插入图片描述

    SELECT * FROM student a RIGHT OUTER JOIN teacher b
    ON a.teacher_id = b.teacher_id; 
    -- OUTER关键字可以省略
    
  • 连接表的执行步骤

    • 第一步:参与连接的两个表做笛卡尔积;
    • 第二步:根据ON后的连接条件筛选笛卡尔积的结果;
    • 第三步:补充左表(LEFT JOIN)或右表(RIGHT JOIN)不满足连接条件的数据(INNER JOIN内关联时无此步骤);
    • 第四步:根据WHERE后的过滤条件筛选第三步的结果;
  • 多表连接:多表连接,就是第一张表与后面的表依次连接,重复执行表连接的步骤而已!

  • 非唯一键连接:表关联时,首先需要确认的一点,就是关联条件字段在关联表中是不是唯一。在绝大多数的情况下,关联条件字段都是关联表中的主键或能唯一确定一条 记录的字段。如果不是,很可能是SQL的关联条件有问题,需要仔细确认是否与需求相符。

这篇关于sql语句汇总的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!