多表连接查询
1 笛卡尔集&内连接
1. 笛卡尔集
* 产生条件:
1. 省略连接条件
2. 连接条件无效
3. 所有表中所有行互相连接
* 解决方案:
添加有效筛选条件
3. 内连接
* SQL92语法:
SELECT 查询列表
FROM 表名1 别名1 , 表名2 别名2
WHERE 连接条件
AND 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选条件
ORDER BY 排序列表
* SQL99语法:
select 字段列表
from 表名1
[ inner ] join 表名2 on 条件
where 筛选条件
group by 分组条件
having 分组后的筛选条件
order by 排序字段
* 注意:
1. 使用表名前缀在多个表中区分相同的列
2. 在不同表中具有相同列名的列可以用表的别名加以区分
3. 如果使用了表别名,则在select 语句中需要使用表别名代替表名
4. 表别名最多支持32 个字符长度,但建议越少越好
2 外连接
1. 左外连接
* 语法:
select 字段列表
from 表1
left [ outer ] join 表2 on 条件
. . .
* 注意:
左外连接查询的是左表所有数据以及其交集部分
5. 右外连接
* 语法:
select 字段列表
from 表1
right [ outer ] join 表2 on 条件
. . .
* 注意:
右外连接查询的是右表所有数据以及其交集部分
3 子查询
* 概念:查询中嵌套查询,称嵌套查询为子查询
* 特点:
1 、子查询都放在小括号内
2 、子查询可以放在from 后面、select 后面、where 后面、having 后面,但一般放在条件的右侧
3 、子查询优先于主查询执行,主查询使用了子查询的执行结果
4 、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
结果集只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用:any 、all 、in 、not in
in : 属于子查询结果中的任意一个就行
any 和all 往往可以用其他查询代替
4 分页查询
* 语法:
select 字段| 表达式, . . .
from 表
where 条件
group by 分组字段
having 条件
order by 排序的字段
limit 起始的条目索引,条目数;
* 示例:每页显示3 条记录
* SELECT * FROM student LIMIT 0 , 3 ;
* SELECT * FROM student LIMIT 3 , 3 ;
* SELECT * FROM student LIMIT 6 , 3 ;
* 特点:
1. 起始条目索引从0 开始
2. limit 子句放在查询语句的最后
3. 公式:select * from 表 limit (page- 1 )* sizePerPage, sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
5 联合查询(union )
* 语法:
select 字段| 常量| 表达式| 函数 from 表 where 条件 union 【all 】
select 字段| 常量| 表达式| 函数 from 表 where 条件 union 【all 】
select 字段| 常量| 表达式| 函数 from 表 where 条件 union 【all 】
. . . . .
select 字段| 常量| 表达式| 函数 from 表 where 条件
* 特点:
1 、多条查询语句的查询的列数必须是一致的
2 、多条查询语句的查询的列的类型几乎相同
3 、union 代表去重,union all 代表不去重
DDL&DML
1 DDL
1.1 数据库操作
* 创建数据库:
* create database 数据库名称;
* 创建数据库,判断不存在,再创建:
* create database if not exists 数据库名称;
* 创建数据库,并指定字符集
* create database 数据库名称 character set 字符集名;
* 查询所有数据库的名称:
* show databases ;
* 查询某个数据库的字符集:查询某个数据库的创建语句
* show create database 数据库名称;
* 修改数据库的字符集
* alter database 数据库名称 character set 字符集名称;
* 删除数据库
* drop database 数据库名称;
* 判断数据库存在,存在再删除
* drop database if exists 数据库名称;
* 查询当前正在使用的数据库名称
* select database ( ) ;
* 使用数据库
* use 数据库名称;
1.2 数据表操作
* 表的创建
* create table 表名(
列名1 数据类型1 【字段约束】,
列名2 数据类型2 【字段约束】,
. . . .
列名n 数据类型n 【字段约束】
) ;
* 注意:最后一列,不需要加逗号(, )
* 查询当前数据库中所有的表
* show tables ;
* 查询某个数据库中所有的表
* show tables from 数据库名称;
* 查询表结构
* desc 表名;
* 修改表名
* alter table 表名 rename to 新的表名;
* 修改表的字符集
* alter table 表名 character set 字符集名称;
* 添加一列
* alter table 表名 add 列名 数据类型;
* 修改列名称 类型
* alter table 表名 change 列名 新列别 新数据类型;
* alter table 表名 modify 列名 新数据类型;
* 删除列
* alter table 表名 drop 列名;
* 删除表
* drop table 表名;
* drop table if exists 表名;
1.3 MySQL常用数据类型
1. int :整数类型
age int ,
2. double :小数类型
score double ( 5 , 2 ) ,
3. date :日期,只包含年月日,yyyy- MM- dd
4. datetime :日期,包含年月日时分秒 yyyy- MM- dd HH:mm:ss
5. timestamp :时间错类型 包含年月日时分秒 yyyy- MM- dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null ,则默认使用当前的系统时间,来自动赋值
6. varchar :字符串
name varchar ( 20 ) :姓名最大20 个字符
zhangsan 8 个字符 张三 2 个字符
1.4 约束
* 概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性。
* 分类:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
4. 外键约束:foreign key
* 非空约束:not null ,某一列的值不能为null
1. 创建表时添加约束
CREATE TABLE stu(
id INT ,
NAME VARCHAR ( 20 ) NOT NULL
) ;
2. 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR ( 20 ) NOT NULL ;
3. 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR ( 20 ) ;
* 唯一约束:unique ,某一列的值不能重复
1. 注意:
* 唯一约束可以有NULL 值,但是只能有一条记录为null
2. 在创建表时,添加唯一约束
CREATE TABLE stu(
id INT ,
phone_number VARCHAR ( 20 ) UNIQUE
) ;
3. 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
4. 在表创建完后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR ( 20 ) UNIQUE ;
* 主键约束:primary key 。
1. 注意:
1. 含义:非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
2. 在创建表时,添加主键约束
create table stu(
id int primary key ,
name varchar ( 20 )
) ;
3. 删除主键
ALTER TABLE stu DROP PRIMARY KEY ;
4. 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY ;
5. 自动增长:
1. 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
2. 在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment ,
name varchar ( 20 )
) ;
3. 删除自动增长
ALTER TABLE stu MODIFY id INT ;
4. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT ;
* 外键约束:foreign key , 让表于表产生关系,从而保证数据的正确性。
1. 在创建表时,可以添加外键
* 语法:
create table 表名(
. . . .
外键列
constraint 外键名称 foreign key ( 外键列名称) references 主表名称( 主表列名称)
) ;
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段名称) REFERENCES 主表名称( 主表列名称) ;
4. 级联操作
1. 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY ( 外键字段名称) REFERENCES 主表名称( 主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
2. 分类:
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
2 DML
1. 添加数据:
* 语法:
* insert into 表名( 列名1 , 列名2 , . . . 列名n) values ( 值1 , 值2 , . . . 值n) ;
* 注意:
1. 列名和值要一一对应。
2. 如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values ( 值1 , 值2 , . . . 值n) ;
3. 除了数字类型,其他类型需要使用引号( 单双都可以) 引起来
2. 删除数据:
* 语法:
* delete from 表名 [ where 条件]
* 注意:
1. 如果不加条件,则删除表中所有记录。
2. 如果要删除所有记录
1. delete from 表名;
2. TRUNCATE TABLE 表名;
* 面试题:delete 与TRUNCATE 区别?
1. truncate 不能加where 条件,而delete 可以加where 条件
2. truncate 的效率高
3. truncate 删除带自增长的列的表后,如果再插入数据,数据从1 开始
4. delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
5. truncate 删除不能回滚,delete 删除可以回滚
3. 修改数据:
* 语法:
* 修改单表: update 表名 set 列名1 = 值1 , 列名2 = 值2 , . . . [ where 条件] ;
* 修改多表: update 表1 别名1 , 表2 别名2 set 字段= 新值,字段= 新值 where 连接条件 and 筛选条件
* 注意:
1. 如果不加任何条件,则会将表中所有记录全部修改