一. 增删
1. 显示当前所有的数据库
show databases;
2. 创建数据库
create database 数据库名;
如果在 database 后面加上 if not exists ,作用是:
如果这个数据库不存在,就创建。如果存在,也不会报错
3. 选中数据库
use 数据库名;
4. 删除数据库 (谨慎使用)
drop database 数据库名;
5. 常用数据类型
数据类型 | 大小 | 说明 |
int | 4字节 | 相当于 Java 中的 Integer |
bigint | 8字节 | 相当于 Java 中的 Long |
double(M,D) | 8字节 | 相当于 Java 中的 Double |
decimal(M,D) | M/D最大值+2 | 双精度, M 指定长度, D 表示小数点位数, 精确数值.相当于 Java 中的 BigDecimal |
varchar(size) | 0~65535字节 | 可变长度字符串. 相当于 Java 中的 String |
datetime | 8字节 | 范围从 1000 到 9999年, 不会进行时区的检索及转换。相当于 Java 中的 java.util.Date . |
6. 创建表
create table 表名 (列名 类型, 列名 类型......);
注意:同一个数据库中不可以有同名的表,名字也不能与关键字重复,如果需要重复需使用 `` 引起来
7. 查看当前数据库中,有哪些表
show tables;
8. 查看指定的表结构
desc 表名;
Type: 数据类型,'11'和数据存储无关,描述的是 “打印的格式”;
'20'是只最多能存 20 个字符。
Null: 是否允许填为空
Default: 默认值
9. 删除表 (谨慎使用)
drop table 表名
10. 新增
insert into 表名 values (值,值.....);
// 值的 类型 和 个数 要与表 的列 的类型 和 个数匹配
还可以进行指定列插入
insert into 表名 (列名) values (值,值.....);
// 多个列之间使用逗号分隔
也可以一次插入多行记录
insert into 表名 values (值,值.....), (值,值.....), (值,值.....);
二. 查询
1. 全列查找 (查询出当前表的所以行和列)
注意:当存储的数据量级非常大时,这个操作可能会导致服务器卡死
select * from 表名;
// ' * '称为通配符,表示所有的列.
2. 指定列查询
select 字段1, 字段2... from 表名;
// 可以写多个列名,中间使用逗号分隔
3. 表达式查询
select 字段1+100,字段2+字段3 from 表名;
4. 给查询表起别名
select 字段1 别名1, 字段2 别名2 from 表名;
5. 查询时进行去重
select distinct 字段 from 表名
// 可以针对多个列去重, 只有多个列相同时,才视为重复
6. 对查询结果排序
select 字段 from 表名 order by 列名;
降序: select 字段 from 表名 order by 列名 desc;
也可以指定多个列排序:select 字段 from 表名 order by 列名 desc, 列名, 列名 desc;
// 指定多个列时,那个列靠前,那个列优先级就高
7. 条件查询
select 字段 from 表名 where 条件;
// where 条件可以使用表达式,但不能使用别名
// AND 的优先级高于 OR ,在同时使用时,需要使用小括号 () 包裹优先执行的部分
8. 查询前 N 条记录
select 字段 from 表名 limit N;
从第 M 条记录开始,查询 N 条记录 (M 从 0 开始算)
select 字段 from 表名 limit N offset M;
三. 修改(Update)
update 表名 set 列名 = 值, 列名 = 值...... where 条件;
// where 条件 可以不写,意味着对表中的所有行进行修改
// 列名 = 值 就是修改为的内容
// 这个条件语句也可以使用 order by 和 limit 等
// 例如这里,为总成绩倒数前三的学生在 math +10
// NULL 与任何数据进行算数运算都是NULL,因此 ‘孙尚香’ 的数学成绩没有改变
四. 删除 (delete)
delete from 表名 where 条件;
五. 数据库约束
1. 指定列不能为空
not null
2. 指定列值唯一
unique
3. 设置默认值
default '默认值'
4. 指定主键
primary key (auto_increment)
// 不加括号
// 主键唯一且不为空
auto_increment
5. 外键约束
foreign key
可以理解为,当前这张表的这一列和另外哪张表里的哪一列建立关联关系
当外键约束建立好了之后,在当前学生表中插入或修改记录,classId 必须在班级表中存在
班级表(父表) 对 学生(子表) 表产生了约束
此处要求,子表中引用父表的这一列,务必是 primary key, 或者 unique
在子表存在时,不能直接删除父表;在记录被子表引用时,也不能通过父表直接修改或删除记录
六. 表的设计
1. 新增
insert into 复制到的表名 [(列名 [, 列名 ...])] select 列名,列名 from 被复制的表名
2. 查询
2.1 聚合查询
2.1.1 聚合函数
函数 | 作用 |
count([distinct] expr) | 计算行数 |
sum([distinct] expr) | 针对这一列的若干行进行加和,不是数字没有意义 |
avg([distinct] expr) | 求这些行的平均值,不是数字没有意义 |
max([distinct] expr) | 求这些行的最大值,不是数字没有意义 |
min([distinct] expr) | 求这些行的最小值,不是数字没有意义 |
// count(*) 即使是 空值(NULL) 也会记录到行数里;count(列名) 就不会把空值这一行记录到行数中.
// sum() 如果这一列中有 NULL,则 NULL不参与运算直接跳过
2.1.2 分组查询/聚合
group by 列名
使用 group by 查询时,只有进行分组的列可以被直接查询,其他的列都应该放到聚合函数里
根据查询结果进行分组,把值相同的记录分为一组
假设有这样一个表
我们对每个岗位分别进行求平均值
分组查询还可以指定条件:
1.分组之前,使用条件筛选;使用 where
例如:先把张三去掉再分组求平均
having
例如:查询平均薪资 > 10000 的岗位
先分组,算平均,再筛选
3.这两个筛选可以一起使用
2.2 联合查询
select * from 表名,表名
select * from 表名 join 表名
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
2.2.1 内连接
select 字段 from 表1 join 表2 on 条件 where 其他条件;
-- inner可以省去
select 字段 from 表1 ,表2 where 连接条件 and 其他条件;
2.2.2 外连接
1) 内外连接的区别:
上述数据,左侧表中的每条记录在右侧都有所体现 , 右侧表的每条记录也在左侧表有所体现,此时,内连接与外连接就没什么区别
而我们发现这个数据的左侧表中,没有王五同学的成绩;右侧表中也没有4号同学的信息,内外连接的区别就会很明显
2)
-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件 where 其他条件;
-- 右外连接,表 2 完全显示
select 字段 from 表名1 right join 表名2 on 连接条件 where 其他条件;
2.2.3 自连接
select 字段 from 表1 别名1 [inner] join 表1 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表1 别名2 where 连接条件 and 其他条件;
自连接是指在同一张表连接自身进行查询。
2.2.4 子查询
子查询是指嵌入在其他sql 语句中的 select 语句, 也叫嵌套查询
* 单行子查询:返回一行记录的子查询
select 字段 from 表1 where 列名 = (select 字段 from 表名 where条件);
多行子查询:返回多行记录的子查询
1. [NOT] IN关键字:
-- 使用 IN
select 字段 from 表1 where 列名 in (select 字段 from 表名 where 条件 or 条件);
-- 使用 NOT IN
select 字段 from 表1 where 列名 noy in (select 字段 from 表名 where 条件 and 条件);
exists 在执行效率和可读性都远低于 in, 只有在一些极端情况下使用 exists, in 里的子查询的结果是在内存中的,如果 子查询结果集特别大,内存放不下,需要使用 exists
2. [NOT] EXISTS 关键字:
select 字段 from 表1 where exists (select 字段 from 表名 where
条件 and 条件);
2.2.5 合并查询
使用 union(去重) 或 union all (不去重)
select 字段 from 表1 where 条件 union select 字段 from 表1 where 条件;