06-mysql入门基础02-库表操作
SQL 语言
关系型数据库的常见组件
- 数据库：database
- 表：table，行：row 列：column
- 索引：index
- 视图：view
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
- 用户：user
- 权限：privilege
SQL语句分类
- DDL: Data Defination Language 数据定义语言
- CREATE，DROP，ALTER
- DML: Data Manipulation Language 数据操纵语言
- INSERT，DELETE，UPDATE
- 软件开发：CRUD
- DQL：Data Query Language 数据查询语言
- SELECT
- DCL：Data Control Language 数据控制语言
- GRANT，REVOKE
- TCL：Transaction Control Language 事务控制语言
- COMMIT，ROLLBACK，SAVEPOINT
查看支持所有字符集
show variables like 'collation%'; show variables like '%character%';
管理数据库
创建数据库
#命令格式 CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME' CHARACTER SET 'character set name' COLLATE 'collate name'; #范例 create database [if not exists] magedu default character set gbk collate gbk_chinese_ci; create database [if not exists] magedu default character set utf8 collate utf8_general_ci; create database [if not exists] magedu default character set utf8 collate utf8mb4_general_ci;
- 范例: 以容器方式启动并创建数据库
[root@centos8 ~]#docker run --name mysql-server -t \ -e MYSQL_DATABASE="zabbix" \ -e MYSQL_USER="zabbix" \ -e MYSQL_PASSWORD="zabbix_pwd" \ -e MYSQL_ROOT_PASSWORD="root_pwd" \ -d mysql:5.7 \ --character-set-server=utf8 --collation-server=utf8_bin [root@centos8 ~]#docker run -d -p 3306:3306 --name mysql \ -e MYSQL_ROOT_PASSWORD=123456 \ -e MYSQL_DATABASE=jumpserver -e MYSQL_USER=jumpserver -e MYSQL_PASSWORD=123456 -v /data/mysql:/var/lib/mysql -v /etc/mysql/mysql.conf.d/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \ -v /etc/mysql/conf.d/mysql.cnf:/etc/mysql/conf.d/mysql.cnf mysql:5.7.30
修改数据库
#命令格式
ALTER DATABASE DB_NAME character set utf8;
#范例
ALTER DATABASE db1 character set utf8 COLLATE utf8_bin;
删除数据库
#命令格式 DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; #范例 drop database testmagedu;
数据类型
整数型
- tinyint(m) 1个字节 范围(-128~127)
- smallint(m) 2个字节 范围(-32768~32767)
- mediumint(m) 3个字节 范围(-8388608~8388607)
- int(m) 4个字节 范围(-2147483648~2147483647)
- bigint(m) 8个字节 范围(+-9.22*10的18次方)
- 上述数据类型，如果加修饰符unsigned后，则最大值翻倍
浮点型(float和double)，近似值
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
- double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数
- 假设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
字符串(char,varchar,text)
- char(n) 固定长度，最多255个字符,注意不是字节
- varchar(n) 可变长度，最多65535个字符
- tinytext 可变长度，最多255个字符
- text 可变长度，最多65535个字符
- mediumtext 可变长度，最多2的24次方-1个字符
- longtext 可变长度，最多2的32次方-1个字符
- BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
- VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
- 内建类型：ENUM枚举, SET集合
- char和varchar的比较：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快
- varchar 和 text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
- 面试题：VARCHAR(50) 能存放几个 UTF8 编码的汉字？
存放的汉字个数与版本相关。 mysql 4.0以下版本，varchar(50) 指的是 50 字节，如果存放 UTF8 格式编码的汉字时（每个汉字3字节），只能存放16 个。 mysql 5.0以上版本，varchar(50) 指的是 50 字符，无论存放的是数字、字母还是 UTF8 编码的汉字，都可以存放 50 个。
修饰符
适用所有类型的修饰符：
- NULL：数据列可包含NULL值，默认值
- NOT NULL：数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
- DEFAULT：默认值
- PRIMARY KEY：主键，所有记录中此字段的值不能重复，且不能为NULL
- UNIQUE KEY：唯一键，所有记录中此字段的值不能重复，但可以为NULL
-
CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
- AUTO_INCREMENT：自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key
- UNSIGNED：无符号
DDL 语句
创建表
参考文档：https://dev.mysql.com/doc/refman/8.0/en/create-table.html
创建表的方法1：直接创建
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...) #字段信息 col type1 PRIMARY KEY(col1,...) INDEX(col1, ...) UNIQUE KEY(col1, ...) #表选项： ENGINE [=] engine_name ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} #范例：id字段以10初始值 CREATE TABLE student ( id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, #height DECIMAL(5,2), gender ENUM('M','F') default 'M' )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
desc student;
- 注意
1、Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎 2、同一库中不同表可以使用不同的存储引擎 3、同一个库中表建议要使用同一种存储引擎类型
- 范例：auto_increment 属性
#字段自增长，起始值为3，步长为10
SHOW VARIABLES LIKE 'auto_inc%'; SET @@auto_increment_increment=10; SET @@auto_increment_offset=3; SHOW VARIABLES LIKE 'auto_inc%'; CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); SELECT col FROM autoinc1;
- 范例：时间类型
create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL); insert testdate ()values()()(); select * from testdate;
创建表的方法2：通过查询现存表创建；新表会被直接插入查询而来的数据
#格式 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement #范例 create table user select user,host,password from mysql.user;
创建表的方法3：通过复制现存的表的表结构创建，但不复制数据
#格式 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } #范例 desc student; create table teacher like student;
- 范例: 创建外键表
create table school(id int primary key auto_increment,name varchar(10)); create table teacher(id int primary key auto_increment,name varchar(10),school_id int,foreign key(school_id) references school(id)); desc school; desc teacher; show create table teacher; insert school values(0,'magedu'),(0,'wangedu'); select * from school; insert teacher values(0,'xiaoming',1); insert teacher values(0,'xiaohong',2); select * from teacher;
表查看
#查看表： SHOW TABLES [FROM db_name] #查看表创建命令： SHOW CREATE TABLE tbl_name #查看表结构 DESC [db_name.]tb_name SHOW COLUMNS FROM [db_name.]tb_name #查看表状态 SHOW TABLE STATUS LIKE 'tbl_name' #查看库中所有表状态 SHOW TABLE STATUS FROM db_name #查看支持的engine类型 SHOW ENGINES;
修改和删除表
修改表
ALTER TABLE 'tbl_name' #字段： #添加字段：add ADD col1 data_type [FIRST|AFTER col_name] #删除字段：drop #修改字段： alter（默认值）, change（字段名）, modify（字段属性） #查看修改表帮助 Help ALTER TABLE
删除表
DROP TABLE [IF EXISTS] 'tbl_name';
- 修改表范例
#修改表名 ALTER TABLE students RENAME s1;
#添加字段 ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型 ALTER TABLE s1 MODIFY phone int; #修改字段名称和类型 ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); #删除字段 ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集 ALTER TABLE s1 character set utf8;
#修改数据类型和字符集 ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段 ALTER TABLE students ADD gender ENUM('m','f'); alter table student modify is_del bool default false; #修改字段名和类型 ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
#删除字段 ALTER TABLE students DROP age;
#查看表结构 DESC students;
#新建表无主键，添加和删除主键 CREATE TABLE t1 SELECT * FROM students; ALTER TABLE t1 add primary key (stuid); ALTER TABLE t1 drop primary key;
#添加外键 ALTER TABLE students add foreign key(TeacherID) references teachers(tid); #删除外键 SHOW CREATE TABLE students #查看外键名 ALTER TABLE students drop foreign key <外键名>;
DML 语句
- DML: INSERT, DELETE, UPDATE
INSERT 语句
- 功能：一次插入一行或多行数据
- 语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE #如果重复更新之 col_name=expr [, col_name=expr] ... ] INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] #简化写法 INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
UPDATE 语句
- 语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
- 注意：一定要有限制条件，否则将修改所有行的指定字段
DELETE 语句
- 删除表中数据，但不会自动缩减数据文件的大小。
- 语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count] #可先排序再指定删除的行数
- 注意：一定要有限制条件，否则将清空表中的所有数据
- 如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;
- 缩减表大小 ：OPTIMIZE TABLE tb_name
DQL 语句
单表操作
- 语法：
SELECT [ALL | DISTINCT | DISTINCTROW ] [SQL_CACHE | SQL_NO_CACHE] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [FOR UPDATE | LOCK IN SHARE MODE]
- 说明
1、字段显示可以使用别名： col1 AS alias1, col2 AS alias2, ...
2、WHERE子句：指明过滤条件以实现"选择"的功能： 过滤条件：布尔型表达式 算术操作符：+, -, *, /, % 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <= 范例查询: BETWEEN min_num AND max_num 不连续的查询: IN (element1, element2, ...) 空查询: IS NULL, IS NOT NULL DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students; 模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符 RLIKE：正则表达式，索引失效，不建议使用 REGEXP：匹配字符串可用正则表达式书写模式，同上 逻辑操作符：NOT，AND，OR，XOR
3、GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算 常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计 HAVING: 对分组聚合运算后的结果指定过滤条件 一旦分组 group by ，select语句后只跟分组的字段，聚合函数
4、ORDER BY: 根据指定的字段对查询结果进行排序 升序：ASC 降序：DESC
5、LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0
6、对查询结果中的数据请求施加"锁" FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作 LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作
- 常用命令
#范例：字段别名 select stuid 学员ID,name as 姓名,gender 性别 from students; #范例：简单查询 DESC students; INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f'); INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen'); SELECT * FROM students WHERE id < 3; SELECT * FROM students WHERE gender='m'; SELECT * FROM students WHERE gender IS NULL; SELECT * FROM students WHERE gender IS NOT NULL; SELECT * FROM students ORDER BY name DESC LIMIT 2; SELECT * FROM students ORDER BY name DESC LIMIT 1,2; SELECT * FROM students WHERE id >=2 and id <=4 SELECT * FROM students WHERE BETWEEN 2 AND 4 SELECT * FROM students WHERE name LIKE 't%' SELECT * FROM students WHERE name RLIKE '.*[lo].*'; SELECT id stuid,name as stuname FROM students select * from students where classid in (1,3,5); select * from students where classid not in (1,3,5); #范例：判断是否为NULL select * from students where classid is null; select * from students where classid <=> null; select * from students where classid is not null; #ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值 select stuid,name,ifnull(classID,'无班级') from students where classid is null; #范例： 记录去重 select distinct gender from students; #将age和gender多个字段重复的记录去重 select distinct age,gender from students; #范例：SQL 注入攻击 select * from user where name='admin' and password='' or '1'='1'; select * from user where name='admin' and password='' or '1=1'; select * from user where name='admin'; -- ' and password='magedu123'; select * from user where name='admin'; # ' and password='magedu123'; #范例: 分页查询 select * from students limit 0,3; select * from students limit 3; select * from students limit 1,3; # 查询第n页的数据,每页显示m条记录 mysql> select * from students limit (n-1) * m,m;
- 聚合函数
select sum(age)/count(*) from students where gender ='M';
select sum(age)/count(*) from students where gender ='F';
- 分组统计
select classid, count(*) 数量 from students group by classid; select classid,gender, count(*) 数量 from students group by classid,gender;
#分组统计 select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 >30 ; select gender,avg(age) 平均年龄 from students group by gender having gender='M'; #多个字段分组统计 select classid,gender,count(*) 数量 from students group by classid,gender; select classid,gender,count(*) 数量 from students group by gender,classid;
#范例: group_concat函数实现分组信息的集合 select gender,group_concat(name) from students group by gender; # with rollup 分组后聚合函数统计后再做汇总 select gender,count(*) from students group by gender with rollup; select gender,group_concat(name) from students group by gender with rollup;
- 范例: 分组统计
#注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select；’后面,否则根据系统变量SQL_MODE的值不同而不同的结果 #以下为MySQL8.0.17 的执行结果 use hellodb select classid,count(*) 数量 from students group by classid; select classid,count(*),stuid 数量 from students group by classid; select @@sql_mode; #以下是Mariadb10.3.17的执行结果 select classid, count(*), stuid from students group by classid; select @@sql_mode;
- 排序
#只取前3个 select * from students order by age desc limit 3; #跳过前3个只显示后续的2个 select * from students order by age desc limit 3,2; #范例：排序 select classid,sum(age) from students where classid is not null group by classid order by classid; select classid,sum(age) from students group by classid having classid is not null order by classid; select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3; #必须先过滤，再排序 select * from students where classid is not null order by gender desc, age asc ; #多列排序 select * from students order by gender desc, age asc; #范例：正序排序时将NULL记录排在最后 #对classid 正序排序，NULL记录排在最后 select * from students order by -classid desc; #范例：分组和排序 select classid,count(*) 数量 from students group by classid order by 数量; #分组后再排序 MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid; select * from students order by age limit 10; select * from students order by age limit 3,10; select distinct age from students order by age limit 3 ; select distinct age from students order by age limit 3,5 ; #顺序: group by,having,order by mysql> select classid,count(*) from students group by classid having classid is not null order by classid; #范例: 分组和排序的次序 group by,having,order by mysqlselect classid,count(*) from students group by classid having classid is not null order by classid;
范例：时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新 create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert testdate () values(); insert testdate values(),(),(); select * from testdate; select * from testdate where date between '2020-06-03 select * from testdate where date >= '2020-06-03 15:21:12' and date<= '2020-06-03 18:27:40'; ##修改其它字段,会自动更新timestamp字段 update testdate set id=10 where id=1; select * from testdate3;
多表查询
多表查询，即简单查询来自于多账簿
- 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
- 联合查询：UNION
- 交叉连接：笛卡尔乘积 CROSS JOIN
- 内连接：
- 等值连接：让表之间的字段以"等值"建立连接关系
- 不等值连接
- 自然连接：去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
- 外连接：
- 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
- 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
- 完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法
- 自连接：本表和本表进行连接查询
子查询
- 子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下四种常见的用法.
1. 用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers); update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
2. 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
3. 用于EXISTS 和 Not EXISTS
select * from students s where EXISTS (select * from teachers t where s.teacherid=t.tid); select * from students s where NOT EXISTS (select * from teachers t where s.teacherid=t.tid);
#说明： 1、EXISTS (或 NOT EXISTS) 用在 where之后，且后面紧跟子查询语句（带括号） 2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询的结果具体是什么 3、上述语句把students的记录逐条代入到Exists后面的子查询中，如果子查询结果集不为空，即说明存在，那么这条students的记录出现在最终结果集，否则被排除
4. 用于FROM子句中的子查询
#使用格式： SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause; #范例： SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
- 范例
#子查询：select 的执行结果，被其它SQL调用 select stuid,name,age from students where age > (select avg(age) from students); #子查询用于更新表 update teachers set age=(select avg(age) from students) where tid=4;
联合查询
- 联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
- 案例
#多表纵向合并union select * from teachers union select * from students; select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students; select * from teachers union select * from teachers; select * from teachers union all select *from teachers; select * from user union select * from user; select distinct * from user; #去重记录 select * from emp; select distinct * from emp; select * from emp union select * from emp; #union all 不去重 select * from emp union all select * from emp;
交叉连接
- cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加, "雨露均沾"
- 比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
- 交叉连接生成的记录可能会非常多,建议慎用
select * from students cross join teachers; select * from teachers,students; select stuid,students.name student_name,students.age,tid,teachers.name teacher_name,teachers.age from teachers cross join students; select stuid,s.name student_name,s.age student_age,tid,t.name teacher_name,t.age teacher_age from teachers t cross join students s;
内连接
- inner join 内连接取多个表的交集
#内连接inner join select * from students inner join teachers on students.teacherid=teachers.tid; #如果表定义了别名，原表名将无法使用 select stuid,s.name as student_name,tid,t.name as teacher_name from students as s inner join teachers as t on s.teacherid=t.tid; select stuid,s.name studentname,s.age studentage,tid,t.name as teachername,t.age teacherage from students as s inner join teachers t on s.teacherid=t.tid; select * from students , teachers where students.teacherid=teachers.tid; select s.name 学生姓名,s.age 学生年龄,s.gender 学生性别,t.name 老师姓名,t.age 老师年龄,t.gender 老师性别 from students s inner join teachers t on s.gender <> t.gender; select stuid,s.name,tid,t.name from students s,teachers t where s.teacherid=t.tid; #内连接后过滤数据 select * from students s inner join teachers t on s.teacherid=t.tid and s.age >30; select * from students s inner join teachers t on s.teacherid=t.tid where s.age>30;
自然连接
- 当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
- 在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）
- 语法：(SQL:1999)SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;
create table t1 ( id int,name char(20)); create table t2 ( id int,title char(20)); insert t1 values(1,'mage'),(2,'wang'),(3,'zhang'); insert t2 values(1,'ceo'),(2,'cto'); select * from t1; select * from t2; select * from t1 NATURAL JOIN t2; select t1.name,t2.title from t1 NATURAL JOIN t2;
左和右外连接
- 左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
- 右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充
#左外连接 select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid; #左外连接扩展 select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null; #多个条件的左外连接 select * from students s left outer join teachers t on s.teacherid=t.tid and s.teacherid is null; #先左外连接，再过滤 select * from students s left outer join teachers t on s.teacherid=t.tid where s.teacherid is null; #右外连接 select * from students s right outer join teachers t on s.teacherid=t.tid; #右外连接的扩展用法 select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
完全外连接
- MySQL 不支持完全外连接full outer join语法
#MySQL不支持完全外连接 full outer join,利用以下方式法代替 select * from students left join teachers on students.teacherid=teachers.tid union select * from students right join teachers on students.teacherid=teachers.tid; select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid union select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right join teachers as t on s.teacherid=t.tid;
#完全外连接的扩展示例 select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null union select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null; select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
自连接
- 自连接, 即表自身连接自身
create table `emp` (`id` int(10) auto_increment,`name` varchar(10) DEFAULT NULL,`leaderid` int(10) NOT NULL,primary key(`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; insert into emp VALUES(1,'mage','0'),(2,'zhangsir','1'); insert into emp VALUES(3,'wang','2'),(4,'zhang','3'); select * from emp; select e.name,l.name from emp as e inner join emp as l on e.leaderid=l.id; select e.name,IFNULL(l.name,'无上级') from emp as e left join emp as l on e.leaderid=l.id; select e.name emp,IFNULL(l.name,'无上级') leader from emp as e left join emp as l on e.leaderid=l.id; 范例：三表连接 #三张表连接示例 select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
SELECT 语句处理的顺序
- 查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引
- SELECT语句的执行流程：FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
select * from students where age > 25 and gender='M';
2. 以ClassID为分组依据，显示每组的平均年龄
select classid,avg(age) from students group by classID;
3. 显示第2题中平均年龄大于30的分组及平均年龄
select classid,avg(age) from students group by classID having avg(age) > 30;
4. 显示以L开头的名字的同学的信息
select * from students where name like 'L%';
5. 显示TeacherID非空的同学的相关信息
select * from students where teacherid is not null;
6. 以年龄排序后，显示年龄最大的前10位同学的信息
select * from students order by age desc limit 10;
7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
select * from students where age >= 20 and age <=25;
8. 以ClassID分组，显示每班的同学的人数
select classid,count(*) from students where classid is not null group by classid;
9. 以Gender分组，显示其年龄之和
select gender,sum(age) from students group by gender;
10. 以ClassID分组，显示其平均年龄大于25的班级
select classid,avg(age) from students group by classid having avg(age) > 25 and classid is not null;
11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
select gender,sum(age) from students where age > 25 group by gender;
12. 显示前5位同学的姓名、课程及成绩
select st.stuid,st.name,sc.score,courses.Course from scores sc inner join students st on sc.stuid=st.stuid inner join courses on sc.courseid=courses.Courseid where st.stuid <=5 ;
13. 显示其成绩高于80的同学的名称及课程
select st.name,sc.score,courses.Course from scores sc inner join students st on sc.stuid=st.stuid inner join courses on sc.courseid=courses.Courseid where sc.score > 80 ;
14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
select students.name,avg(scores.score) from scores inner join students on students.stuid=scores.stuid group by scores.stuid order by avg(scores.score) desc limit 3;
15. 显示每门课程课程名称及学习了这门课的同学的个数
select courses.CourseID,courses.Course,scores.StuID,count(courses.CourseID) from courses inner join scores on scores.CourseID=courses.CourseID group by courses.CourseID;
16. 显示其年龄大于平均年龄的同学的名字
select * from students where age > (select avg(age) from students);
17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
select stuid,name from students where stuid in (select distinct stuid from scores where courseid in (1,2,4,7));
18. 显示其成员数最少的3个的班级的同学中年龄大于同班同学平均年龄的同学
select * from students inner join (select classid from students group by classid having classid is not null order by count(classid) limit 3 ) as sc on students.ClassID=sc.Classid where age > (select avg(age) from students);
19. 统计各班级中年龄大于全校同学平均年龄的同学
select * from students where age > (select avg(age) from students) and classid is not null;
浙公网安备 33010602011771号