MySQL单表查询与多表联查

最新推荐文章于 2024-01-14 22:58:56 发布

小李爱唱歌

最新推荐文章于 2024-01-14 22:58:56 发布

阅读量938

点赞数 1

文章标签： mysql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_50528280/article/details/128971324

版权

本文详细介绍了MySQL中如何创建和管理数据表，包括创建表、数据类型、查看与删除表、修改表结构、约束（非空、唯一、主键、外键）、INSERT、REPLACE、UPDATE和DELETE语句的使用，以及SELECT查询、数据分组、多表关联查询等核心概念和操作。

摘要由CSDN通过智能技术生成

1、创建表

数据表的每行称为一条记录（record）；每一列称为一个字段（field）【列之间以英文逗号隔开】。

简单语法：在当前数据库中创建一张表
    CREATE TABLE 表名(
    列名 列数据类型，
    列名 列数据类型
    );
mysql8.0 [chap03]>create table t1(id int,name char(30));

2、数据类型

在 MySQL 中，有三种主要的类型：文本、数值和日期/时间类型。

文本类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)，二进制形式的长文本数据。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)，二进制形式的中等长度文本数据。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)，二进制形式的极大文本数据。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET	与 ENUM 类似， SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

数值类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。 0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。 0 到 65535 无符号。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。 0 to 16777215 无符号。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。 0 到 4294967295 无符号。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。 0 到18446744073709551615 无符号。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED属性，那么范围将从 0 开始，而不是某个负数。

日期/时间类型：

数据类型	描述
DATE	日期。格式： YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME	日期和时间的组合。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是'1000-01-01 00:00:00' 到 '9999-12- 31 23:59:59'
TIMESTAMP	时间戳。 TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式： YYYY-MM-DD HH:MM:SS注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC
TIME	时间。格式： HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR	2 位或 4 位格式的年。注释： 4 位格式所允许的值： 1901 到 2155。 2 位格式所允许的值： 70 到69，表示从 1970 到 2069

常用数据类型：

3、查看表

#查看某数据库中的所有表
语法：SHOW TABLES[FROM 数据库名][LIKE wild];
mysql8.0 [chap03]>show tables from mysql;

#显示当前数据库中已有的数据表的信息【结构和创建信息】
1、语法：{DESCRIBE|DESC} 表名[列名];
mysql8.0 [chap03]>describe mysql.user;
mysql8.0 [chap03]>desc mysql.user;
2、语法：show columns from 表名称;
mysql8.0 [chap03]>show columns from mysql.user;

#查看数据表中各列的信息
语法：SHOW CREATE TABLE 表名\G
mysql8.0 [chap03]>show create table mysql.user\G
说明：\G表示向mysql服务器发送命令，垂直显示结果

4、删除表

#删除指定的表
语法：DROP TABLE [IF EXISTS] 表名;
mysql8.0 [chap03]>drop table  t1;

5、修改表的结构

mysql8.0 [chap03]>create table t1(id int,name char(30));
mysql8.0 [chap03]>desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#修改列类型：ALTER TABLE 表名 MODIFY 列名 列类型;
mysql8.0 [chap03]>alter table t1 modify name varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql8.0 [chap03]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
    
#增加列：ALTER TABLE 表名 ADD 列名 列类型;
mysql8.0 [chap03]>alter table t1 add bir date;    
#删除列：ALTER TABLE 表名 DROP 列名;
mysql8.0 [chap03]>alter table t1 drop bir;    
#修改列名：ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
mysql8.0 [chap03]>alter table t1 change id user_id int;
#更改表名：
方式1：ALTER TABLE 表名 RENAME 新表名;
方式2：RENAME TABLE 表名 TO 新表名;
mysql8.0 [chap03]>alter table t1 rename t2;
mysql8.0 [chap03]>rename table t2 to t3;
mysql8.0 [chap03]>show tables;
+------------------+
| Tables_in_chap03 |
+------------------+
| t3               |
+------------------+

6 、复制表的结构

#复制一个表结构的实现方法有两种：
方法1：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法：create table 新表名 like 源表
mysql8.0 [chap03]>create table t4 like t3;

方法2：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。
语法：create table 新表名 select * from 源表
mysql8.0 [chap03]>create table t5 select * from t4;

方法3：如果已经存在一张结构一致的表，复制数据
语法：insert into 表 select * from 原表;

7 、表的约束

约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时，可以保护相关的数据不被删除。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

根据约束数据列的限制，约束可分为：

单列约束：每个约束只约束一列。

多列约束：每个约束可约束多列数据。

根据约束的作用范围，约束可分为：

列级约束：只能作用在一个列上，跟在列的定义后面，语法：列定义约束类型

表级约束：可以作用在多个列上，不与列一起，而是单独定义

语法：[CONSTRAINT 约束名] 约束类型(列名)
约束名的取名规则，推荐采用：表名_列名_约束类型
例如：alter table 表名 add constraint 约束名 约束类型(要约束的列名)
表级约束类型有四种：主键、外键、唯一、检查

根据约束起的作用，约束可分为：

NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束

表的约束示例：

1、非空约束(NOT NULL)

列级约束，只能使用列级约束语法定义。确保字段值不允许为空。

mysql8.0 [chap03]>create table t6_stu(
    -> id int,
    -> name char(30) not null);
说明：所有数据类型的值都可以是NULL。空字符串不等于NULL。0也不等于NULL。

2、唯一约束（UNIQUE）

唯一性约束条件确保所在的字段或者字段组合不出现重复值。

唯一性约束条件的字段允许出现多个NULL。

同一张表内可建多个唯一约束。

唯一约束可由多列组合而成。

建唯一约束时MySQL会为之建立对应的索引。

如果不给唯一约束起名，该唯一约束默认与列名相同。

mysql8.0 [chap03]>create table t6_stu1( id int  unique, name char(30) not null);
Query OK, 0 rows affected (0.00 sec)

mysql8.0 [chap03]>desc t6_stu1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  | UNI | NULL    |       |
| name  | char(30) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

3、主键约束（PRIMARY KEY）

主键从功能上看相当于非空且唯一，一个表中只允许一个主键，主键是表中唯一确定一行数据的字段，主键字段可以是单字段或者是多字段的组合当建立主键约束时，MySQL为主键创建对应的索引。

mysql8.0 [chap03]>create table t6_stu2( id int  primary key, name char(30) not null);
Query OK, 0 rows affected (0.06 sec)

mysql8.0 [chap03]>desc t6_stu2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(30) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#自动增长：auto_increment ：自动增长,为新的行产生唯一的标识，一个表只能有一个auto_increment，且该属性必须为主键的一部分。auto_increment的属性可以是任何整数类型。
mysql8.0 [chap03]>create table t_auto(id int primary key auto_increment);

4、外键约束（FOREIGN KEY）

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系，外键确保了相关的两个字段的两个关系。子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。当主表的记录被子表参照时，主表记录不允许被删除。外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

语法：FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

mysql8.0 [chap03]>create table tb_dept(dept_id int primary key,name char(30));
mysql8.0 [chap03]>create table tb_employee(employee_id int primary key,name char(30),dept_id int,foreign key(dept_id)  references tb_dept(dept_id));

5、检查约束（CHECK ）

# 注意检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通过触发器完成），8之后就开始正式支持这个约束了。
mysql8.0 [chap03]>create table t7(
    -> id int,
    -> age int check(age > 18),
    -> gender char(1) check(gender in ('M','F'))
    -> );

6、默认值约束（DEFAULT）

可以使用default关键字设置每一个字段的默认值。

#设置默认值约束
mysql8.0 [chap03]>create table t8(
    -> id int unique,
    -> name char(30) not null,
    -> gender char(1) default 'M',
    -> primary key (id));
Query OK, 0 rows affected (0.01 sec)
#删除默认值约束
mysql8.0 [chap03]>alter table t8 modify gender char;

删除表的约束：

#删除NOT NULL约束，alter table 表名 modify 列名 类型;
mysql8.0 [chap03]>alter table t6_stu modify name char;
#删除UNIQUE约束，alter table 表名 drop index 唯一约束名;
mysql8.0 [chap03]>alter table t6_stu1 drop index id;
#删除PRIMARY KEY约束，alter table 表名 drop primary key;
mysql8.0 [chap03]>alter table t6_stu2 drop primary key;
#删除FOREIGN KEY约束，alter table 表名 drop foreign key 外键名;
mysql8.0 [chap03]>show create table tb_employee\G
*************************** 1. row ***************************
       Table: tb_employee
Create Table: CREATE TABLE `tb_employee` (
  `employee_id` int NOT NULL,
  `name` char(30) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `tb_employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `tb_dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql8.0 [chap03]>alter table tb_employee drop foreign key dept_id;

8、INSERT语句

INSERT INTO table [(column [, column...])] VALUES(value [, value...]);
默认情况下，一次插入操作只插入一行

一次性插入多条记录：
INSERT INTO table [(column [, column...])] 
    VALUES(value [, value...])，(value [, value...])
    
如果为每列都指定值，则表名后不需列出插入的列名
如果不想在表名后列出列名，可以为那些无法指定的值插入null
可以使用如下方式一次插入多行
    insert into 表名[(列名,…)]
    select 语句——可以非常复杂。
    
如果需要插入其他特殊字符，应该采用\转义字符做前缀

9、REPLACE语句

replace语句的语法格式有三种语法格式。

语法格式1：replace into 表名 [（字段列表）] values （值列表）

语法格式2：
    replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式
    
语法格式3：
    replace [into] 表名 set 字段1=值1, 字段2=值2

REPLACE与INSERT语句区别：

replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。

使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新)，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了

10、UPDATE语句

UPDATE table
SET column = value [, column = value] 
[WHERE condition];

修改可以一次修改多行数据，修改的数据可用where子句限定，where子句里是一个条件表达式，只有符合该条件的行才会被修改。没有where子句意味着where字句的表达式值为true。也可以同时修改多列，多列的修改中间采用逗号(,)隔开

11、DELETE和TRUNCATE语句

DELETE FROM table_name [where 条件];
TRUNCATE TABLE table_name

完全清空一个表。 DDL语句。

DROP、TRUNCATE、DELETE的区别：

delete：删除数据，保留表结构，可以回滚，如果数据量大，很慢

truncate：删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快

drop：删除数据和表结构,删除速度最快。

12、SELECT语句

简单的SELECT语句：
SELECT {*, column [alias],...}
FROM table;

说明：
–SELECT列名列表。*表示所有列。
–FROM 提供数据源(表名/视图名)
–默认选择所有行

SELECT语句中的算术表达式：

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。
    SELECT last_name, salary, salary*12
    FROM employees;
    
补充：+说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

运算符的优先级：

乘法和除法的优先级高于加法和减法
同级运算的顺序是从左到右
表达式中使用括号可强行改变优先级的运算顺序
    SELECT last_name, salary, salary*12+100
    FROM employees;
    
    SELECT last_name, salary, salary*(12+100)
    FROM employees;

NULL值的使用：

空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算，得到也是null.

补充说明：

安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL 如：where salary is NULL/(is not NULL) ->where salary
<=>NULL
示例1：查询emp表奖金为空的员工信息。
    select * from emp where comm <=> NULL;

示例2：查询emp表奖金为50000的员工信息
    select * from emp where comm <=> 50000;

定义字段的别名：

改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
    SELECT last_name as “姓名”, salary “薪水”
    FROM employees;
    
    SELECT last_name, salary*12 “年薪”
    FROM employees;

重复记录：

缺省情况下查询显示所有行,包括重复行
    SELECT department_id
    FROM employees;
    
使用DISTINCT关键字可从查询结果中清除重复行
    SELECT DISTINCT department_id
    FROM employees;
    
DISTINCT的作用范围是后面所有字段的组合
    SELECT DISTINCT department_id , job_id
    FROM employees;

限制所选择的记录：

使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
    SELECT[DISTINCT] {*, column [alias], ...}
    FROM table–[WHEREcondition(s)];
    
WHERE中的字符串和日期值
字符串和日期要用单引号扩起来
字符串是大小写敏感的,日期值是格式敏感的
    SELECT last_name, job_id, department_id
    FROM employees
    WHERE last_name = "king";
    
    
WHERE中比较运算符:
    SELECT last_name, salary, commission_pct
    FROM employees
    WHERE salary<=1500;    
    
其他比较运算符
使用BETWEEN运算符显示某一值域范围的记录
    SELECTlast_name, salary
    FROM employees
    WHERE salary BETWEEN 1000 AND 1500; 
    
使用IN运算符
使用IN运算符获得匹配列表值的记录
    SELECTemployee_id, last_name, salary, manager_id
    FROM employees
    WHERE manager_id IN (7902, 7566, 7788);
    
使用LIKE运算符
  使用LIKE运算符执行模糊查询
  查询条件可包含文字字符或数字
  (%) 可表示零或多个字符
  ( _ ) 可表示一个字符
    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_A%';
    
使用IS NULL运算符
  查询包含空值的记录
      SELECT last_name, manager_id
      FROM employees
      WHERE manager_id IS NULL;
  
  
  逻辑运算符
  使用AND运算符
    AND需要所有条件都是满足T.
    
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100–4 AND job_id='CLERK';
  使用OR运算符
    OR只要两个条件满足一个就可以
  
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100 OR job_id='CLERK';
  使用NOT运算符
    NOT是取反的意思
        SELECT last_name, job_id
        FROM employees
        WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');
        
        
  使用正则表达式：REGEXP
      <列名> regexp '正则表达式'
      select * from product where product_name regexp '^2018';
      
  
  数据分组--GROUP BY
  GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
    分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。
    分组函数忽略空值,。
    结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
    SELECT column, group_function
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [ORDER BY column];
    #每个部门的平均工资
    SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno
   
   #查每个部门的整体工资情况
    #如果select语句中的列未使用组函数，那么它必须出现在GROUP BY子句中
    #而出现在GROUP BY子句中的列，不一定要出现在select语句中
    SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
    FROM TB_EMP
    GROUP BY deptno #根据部门编号分组
    #每个部门每个职位的平均工资
    SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
    
    分组函数重要规则
    如果使用了分组函数，或者使用GROUP BY 的查询：出现在SELECT列表中的字段，要么出现在组合函数里，
    要么出现在GROUP BY 子句中。
    GROUP BY 子句的字段可以不出现在SELECT列表当中。
    使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。
    
 数据分组--限定组的结果：HAVING子句
    HAVING子句用来对分组后的结果再进行条件过滤。
    SELECT column, group_function
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [HAVING group_condition]
    [ORDER BYcolumn];
    HAVING子句用来对分组后的结果再进行条件过滤。
    #查询部门平均工资大于2000的
    #分组后加条件 使用having
    #where和having都是用来做条件限定的,但是having只能用在group by之后
    SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
    FROM TB_EMP
    GROUP BY deptno
    HAVING AVG(sal) > 2000
    HAVING与WHERE的区别
    WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，WHERE子句中不能使用聚合函数，
    HAVING子句可以使用聚合函数。
    组函数的错误用法
     不能在WHERE 子句中限制组.
     限制组必须使用HAVING 子句.
     不能在WHERE 子句中使用组函数
    补充：MySQL 多行数据合并 GROUP_CONCAT
    Syntax: GROUP_CONCAT(expr)
    示例：fruits表按s_id,将供应水果名称合并为一行数据
    mysql> select s_id,group_concat(f_name)
     -> from fruits
     -> group by s_id;
    +------+-------------------------+
    | s_id | group_concat(f_name)  |
    +------+-------------------------+
    |  101 | apple,blackberry,cherry |
    |  102 | orange,banana,grape   |
    |  103 | apricot,coconut     |
    |  104 | berry,lemon       |
    |  105 | melon,xbabay,xxtt    |
    |  106 | mango          |
    |  107 | xxxx,xbababa      |
    +------+-------------------------+
    7 rows in set (0.00 sec)
    注意：使用 GROUP_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行
  
   
   
对结果集排序
  查询语句执行的查询结果，数据是按插入顺序排列
  实际上需要按某列的值大小排序排列
  按某列排序采用order by 列名[desc],列名…
  设定排序列的时候可采用列名、列序号和列别名
  如果按多列排序，每列的asc，desc必须单独设定   
  
  
联合查询
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION   去重复
UNION ALL 不去重复

查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子：
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/


MySQL中的通配符：
MySQL中的常用统配符有三个：
   %：用来表示任意多个字符，包含0个字符
   _ : 用来表示任意单个字符
   escape：用来转义特定字符

13、多表关联查询

1. inner join：代表选择的是两个表的交差部分。
 内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。语法如下：
 SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
 
2. left join：代表选择的是前面一个表的全部。
左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。语法如下：
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

3. right join：代表选择的是后面一个表的全部
同理，右连接将会以右边作为基准，进行检索。语法如下：
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

4.自连接
自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)

5.交叉连接：不适用任何匹配条件。生成笛卡尔积


联合查询

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION   去重复
UNION ALL 不去重复
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

14、SQL函数

聚合函数

聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
COUNT(*|列名) 统计行数
AVG(数值类型列名) 平均值
SUM (数值类型列名) 求和
MAX(列名) 最大值
MIN(列名) 最小值
除了COUNT()以外，聚合函数都会忽略NULL值。

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

count(*) 和 count(1)和count(列名)区别

count(1) and count(*)
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count( )用时多了！从执行计划来看，count(1)和count()的效果是一样的。但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。因为count( ),自动会优化指定到那一个字段。所以没必要去count(1)，用count()，sql会帮你完成优化的因此：count(1)和count(*)基本没有差别！
count(1) and count(字段)两者的主要区别是（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
count(*) 和 count(1)和count(列名)区别
执行效果上：count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULLcount(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率上：列名为主键，count(列名)会比count(1)快列名不为主键，count(1)会比count(列名)快如果表多个列并且没有主键，则 count（1）的执行效率优于 count（*）如果有主键，则 select count（主键）的执行效率是最优的如果表只有一个字段，则 select count（*）最优。

数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求平方根
POW 和 POWER	两个函数的功能相同，返回参数的幂次方
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH	计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(str,pos,len,newstr)	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT(str,len)	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE(s，s1，s2)	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len)	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos])	返回第一次出现子串的位置
INSTR(str,substr)	返回第一次出现子串的位置

日期和时间函数

函数名称	作用
CURDATE() CURRENT_DATE()CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIMECURRENT_TIME()CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW	返回当前系统的日期和时间值
SYSDATE	返回当前系统的日期和时间值
DATE	获取指定日期时间的日期部分
TIME	获取指定日期时间的时间部分
MONTH	获取指定日期中的月份
MONTHNAME	获取指定曰期对应的月份的英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
YEAR	获取年份，返回值范围是 1970〜2069
DAYOFWEEK	获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
DATEDIFF(expr1,expr2)	返回两个日期之间的相差天数，如SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
TIME_TO_SEC	将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

流程控制函数

函数名称	作用
IF(expr,v1,v2)	判断，流程控制，当expr = true时返回 v1，当expr = false、null 时返回v2
IFNULL(v1,v2)	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE	搜索语句

15、单表查询练习

素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
#建表语句：
CREATE TABLE `worker` (
 `部门号` int(11) NOT NULL,
 `职工号` int(11) NOT NULL,
 `工作时间` date NOT NULL,
 `工资` float(8,2) NOT NULL,
 `政治面貌` varchar(10) NOT NULL DEFAULT '群众',
 `姓名` varchar(20) NOT NULL,
 `出生日期` date NOT NULL,
 PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

1、显示所有职工的基本信息。

mysql> select * from worker;

2、查询所有职工所属部门的部门号，不显示重复的部门号。

mysql> select distinct `部门号` from worker;

3、求出所有职工的人数。

mysql> select count(`职工号`) from worker;

4、列出最高工资和最低工资。

mysql> select max(`工资`) '最高工资',min(`工资`) '最低工资' from worker;

5、列出职工的平均工资和总工资。

mysql8.0 [yyqx]>select avg(工资) '平均工资',sum(工资) '总工资' from worker;

6、创建一个只有职工号、姓名和工作时间的新表，名为工作日期表。

mysql8.0 [yyqx]>create table 工作日期表 (参加工作 varchar(30)) select 姓名,职工号 from worker;

7、显示所有女职工的年龄。

mysql8.0 [yyqx]>select 姓名,date_format(from_days(to_days(now())-to_days(出生日期)),'%y')+0 as age from worker where 性别='女';

8、列出所有姓李的职工的职工号、姓名和出生日期。

mysql8.0 [yyqx]>select 职工号,姓名,出生日期 from worker where 姓名 like '李%';

9、列出1990年以前出生的职工的姓名、参加工作日期。

mysql8.0 [yyqx]>select 姓名,工作时间,出生日期 from worker where 出生日期<'1990-01-01';

10、列出工资在3000－4000之间的所有职工姓名。

mysql8.0 [yyqx]>select 姓名 from worker where 工资 between 3000 and 4000;

11、列出所有陈姓和李姓的职工姓名。

mysql8.0 [yyqx]>select 姓名 from worker where 姓名 like '陈%' or 姓名 like '李%';

12、列出所有部门号为102和103的职工号、姓名、党员否。

mysql8.0 [yyqx]>select 职工号,姓名,政治面貌 from worker where 部门号=102 or 部门号=103;

13、将职工表worker中的职工按出生的先后顺序排序。

mysql8.0 [yyqx]>select * from worker order by 出生日期;

14、显示工资最高的前3名职工的职工号和姓名。

mysql8.0 [yyqx]>select 职工号,姓名 from worker order by 工资 desc limit 3;

15、求出各部门党员的人数。

mysql8.0 [yyqx]>select 部门号,count(*) 党员人数 from worker where 政治面貌='党员' group by 部门号;

16、统计各部门的工资和平均工资

mysql8.0 [yyqx]>select 部门号,sum(工资),avg(工资) from worker group by 部门号;

17、列出总人数大于4的部门号和总人数。

mysql8.0 [yyqx]>select 部门号,count(*) from worker group by 部门号 having count(*)>2;

16、多表联查练习

student表：
mysql8.0 [yyqx]>create table student(
    -> id int(10) NOT NULL unique primary key,
    -> name varchar(20) NOT NULL,
    -> sex varchar(4),
    -> birth year,
    -> department varchar(20),
    -> address varchar(50)
    -> );
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

score表：
mysql8.0 [yyqx]>create table score(
    -> id int(10) NOT NULL unique primary key auto_increment,
    -> stu_id int(10) NOT NULL,
    -> c_name varchar(20),
    -> grade int(10)
    -> );

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

查询student表的所有记录

mysql8.0 [yyqx]>select * from student;

2.查询student表的第2条到4条记录

mysql8.0 [yyqx]>select * from student limit 1,3;

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

mysql8.0 [yyqx]>select id,name,department from student;

4.从student表中查询计算机系和英语系的学生的信息

mysql8.0 [yyqx]>select * from student where department in ('计算机系','英语系');

5.从student表中查询年龄18~22岁的学生信息

mysql8.0 [yyqx]>select * from student where 2023-birth between 18 and 22;

6.从student表中查询每个院系有多少人

mysql8.0 [yyqx]>select department,count(department) from student group by department;

7.从score表中查询每个科目的最高分

mysql8.0 [yyqx]>select c_name,max(grade) from score group by c_name;

8.查询李四的考试科目（c_name）和考试成绩（grade）

mysql8.0 [yyqx]>select c_name,grade from score where stu_id=(select id from student where name='李四');

9.用连接的方式查询所有学生的信息和考试信息

mysql8.0 [yyqx]>select * from student,score where student.id=score.stu_id;

10.计算每个学生的总成绩

mysql8.0 [yyqx]>select student.id,name,sum(grade) from student,score where student.id=score.stu_id group by id;

11.计算每个考试科目的平均成绩

mysql8.0 [yyqx]>select c_name,avg(grade) from score group by c_name;

12.查询计算机成绩低于95的学生信息

mysql8.0 [yyqx]>select * from student where id in (select stu_id from score where c_name='计算机' and grade<95);

13.查询同时参加计算机和英语考试的学生的信息

mysql8.0 [yyqx]>select * from student where id=any (select stu_id from score where stu_id in (select stu_id from score where c_name='计算机' )and c_name='英语');

14.将计算机考试成绩按从高到低进行排序

mysql8.0 [yyqx]>select stu_id,grade from score where c_name='计算机' order by grade desc;

15.从student表和score表中查询出学生的学号，然后合并查询结果

mysql8.0 [yyqx]>select id from student union select stu_id from score;

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql8.0 [yyqx]>select student.id,name,sex,birth,department,address,c_name,grade from student,score where (name like '张%' or name like '王%') and student.id=score.stu_id;

17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql8.0 [yyqx]>select student.id,name,sex,birth,department,address,c_name,grade from student,score where address like '湖南%' and student.id=score.stu_id;