利用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。
字段的约束
-
主键约束:保证所约束列中的值必须唯一且不能为空。
- 添加主键约束(创建表时):
col_name datatype primary key
- 更新表时:
Alter table tabname add primary key(col_name)
- 设置主键自动增长:
col_name datatype primary key auto_increment
- 添加主键约束(创建表时):
-
唯一约束:
unique
保证所约束列的值必须唯一,即不能重复。NULL
和任何值都不等,包括它本身 -
非空约束:
not null
保证所约束列的值不能为空 -
外键约束
操纵表
-
更新表名:
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;
-
插入数据
-
插入整行字段:
-
INSERT INTO 表名 VALUES (v1,v2,v3,v4)
;
- 值的顺序与表中的字段顺序必须一致,不安全
-
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;
-
WHERE用来筛选要删除的记录,如果不使用WHERE子句,将删除表中所有数据 (逐条删除,效率不高)。
TRUNCATE TABLE 表名
(删除表中所有记录,直接摧毁表,然后重建表,效率高) -
DELETE 语句不能删除某一列的值,应使用
ALTER TABLE 表名 DROP COLUMN 列名
-
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语句中 AND 和 OR 优先级相同
-
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)”展示股票名称和股票代码?
- Access、SQL Server:使用拼接操作符加号(+)
SELECT stock_name + '(' + stock_code + ')' FROM t_stock_trans_dtl;
-
DB2、Oracle、Teradata:使用拼接操作符两个竖杠(||)
SELECT stock_name || '(' || stock_code || ')' FROM t_stock_trans_dtl;
-
MySQL:使用函数 CONCAT()
SELECT CONCAT(stock_name,'(',stock_code,')') FROM t_stock_trans_dtl;
-
实现数值型字段的拼接
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 关联表(关联列)
- 外键约束会导致效率低,不灵活,所以一般开发时不用,而是通过代码维护
多表设计
- 一对多:在多的一方添加列保存一的一方的主键来作为外键,来保存两张表之间的关系
- 一对一:在任意一方添加列保存另一方的主键作为外键, 来保存两张表之间的关系
- 多对多:在一张第三方的表中分别保存两张表的主键作为外键,来保存两张表之间的关系,可以把多对多的关系拆分成两个一对多的关系来理解
子查询:嵌套在其他查询中的查询
-
使用子查询作为计算字段
-- 如何同时查询出学生编号、学生姓名、老师编号、老师姓名? 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;
需要注意的几点:
- 待合并的结果集的字段数量必须一致。
- 合并后的结果集的title与第一个结果集保持一致。
- 待合并的结果集的字段顺序、字段类型的大类及字段值的含义尽量保持一
致。
- 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 = '男';
- UNION ALL与UNION的执行优先级一致,谁在前谁先执行;
- 不可以使用括号改变执行优先级;
-
组合查询结果集的排序
-- 如何同时查询出年龄为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的关联条件有问题,需要仔细确认是否与需求相符。