DDL——操作数据库、表等
-- 操作数据库
-- 查询
show databases;
-- 创建
create database db1;
create database if not exists db1;
-- 删除
drop database db1;
drop database if exists db1;
-- 使用
use db1;
select database(); -- 查询当前正在使用的数据库
-- 操作表
-- 查询
show tables;
desc tab1; -- 查询表结构
-- 创建
create table tab1(
id int,
name varchar(20)
);
create table if not exists tab1(
id int,
name varchar(20)
);
-- 删除
drop table tab1;
drop table if exists tab1;
-- 修改
alter table tab1 rename to tab2; -- 修改表名
alter table tab1 add score double(5,2); -- 添加一列
alter table tab1 modify name char(10); -- 修改数据类型
alter table tab1 change name newname char(10); -- 修改列名和数据类型
alter table tab1 drop score; -- 删除列
sql中的数据类型
- 数值
- 日期
- 字符串
char(10):定长,存储性能高,浪费空间
varchar(10):变长,存储性能低,节省空间
DML——对表中的数据进行增删改
-- 添加数据
insert into tab1(id,score) values(1,99); -- 指定列
insert into tab1(id,score) values(1,99),(2,98); -- 批量添加
insert into tab1 values(1,'a',99); -- 全部列
insert into tab1 values(1,'a',99),(2,'b',98); -- 批量添加
-- 修改数据
update tab1 set score=0; -- 不加where修改表中所有行
update tab1 set score=99,name='a' where id=1; -- 修改id=1的行
-- 删除数据
delete from tab1; -- 删除所有行
delete from tab1 where id=1; -- 删除id=1的行
DQL——对表中的数据进行查询
-- 基础查询
-- 查询多个字段
select * from tab1; -- 查询所有数据,不建议使用
select id,score from tab1; -- 查询字段
-- 去除重复记录
select distinct id from tab1;
select distinct * from tab1;
-- 起别名
select id,score as 分数 from tab1; -- as可以省略
-- 条件查询
select * from stu where age > 20;
select * from stu where age >= 20;
select * from stu where age >= 20 and age <= 30;
select * from stu where age between 20 and 30; -- [20, 30]闭区间
select * from stu where age > 20;
select * from stu where hire_day between '2001-09-01' and '2022-01-01'; -- 也可以用大/小于号
select * from stu where age = 20;
select * from stu where age != 20;
select * from stu where age <> 20; -- 也是不等于
select * from stu where age = 20 or age = 18 or age = 22;
select * from stu where age in (18, 20, 22); -- 与上一条相同
select * from stu where score is null; -- null的比较不能用=或!=,需要用is或is not
-- 模糊查询:通配符:_单个任意字符 %多个(>=0)任意字符
select * from stu where name like 'a%'; -- 第一个字符为a的
select * from stu where name like '_a%'; -- 第二个字符为a的
select * from stu where name like '%a%'; -- 包含a的
-- 排序查询
select id,score from tab1 order by id asc,score desc;
-- ASC:升序 DESC:降序 默认ASC
-- 排序方式由左往右优先级降低
select * from stu order by math desc, english asc;
-- 分组查询
-- 聚合函数分类
-- count(id) 统计数量,不统计null
-- max(id) 最大值
-- min(id) 最小值
-- sum(id) 求和
-- avg(id) 平均值
-- null值不参与*所有*聚合函数计算
select count(id) from stu;
select count(*) from stu;
select max(math) from stu;
select min(math) from stu;
select sum(math) from stu;
select avg(math) from stu;
select sex,avg(math) from stu group by sex; -- 查询字段为聚合函数或分组字段,查询其他字段无意义
select sex,avg(math),count(*) from stu group by sex;
select sex,avg(math),count(*) from stu where math > 70 group by sex;
select sex,avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;
-- where:分组前限定,不满足where不参与分组,不能用来判断聚合函数
-- having:分组后过滤,不满足having不保留结果,可以用来判断聚合函数
-- 执行顺序:where -- 聚合函数 -- having
-- 分页查询
select * from stu limit 0,3; -- 从0开始,查3条
select * from stu limit 0,3; -- 每页三条,查第一页
select * from stu limit 3,3; -- 每页三条,查第二页
select * from stu limit 6,3; -- 每页三条,查第三页
DCL——对数据库进行权限控制
约束
非空约束 : not null
唯一约束 : unique
主键约束 : primary key
检查约束 : check mysql不支持检查约束
默认约束 : default
外键约束 : foreign key
-- 建表时添加约束
create table emp(
id int primary key auto_increment, -- 主键且自增长
ename varchar(20) not null unique, -- 非空且唯一
joindate date not null, -- 非空
salary double(8,2) not null, -- 非空
bonus double(8,2) default 0 -- 默认为0
);
-- 建表后添加约束
alter table emp modify ename varchar(20) not null; -- 非空约束
alter table emp modify ename varchar(20) unique; -- 唯一约束
alter table emp add primary key(id); -- 主键约束
alter table emp alter bonus set default 0; -- 默认约束
-- 建表后删除约束
alter table emp modify ename varchar(20); -- 非空约束
alter table emp drop index ename; -- 唯一约束
alter table emp drop primary key; -- 主键约束
alter table emp alter bonus drop default; -- 默认约束
-- 外键约束
create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 创建表时添加外键dep_id,关联dept表的id主键
constraint fk_emp_dept foreign key(dep_id) references dept(id)
-- fk_emp_dept可以任意命名,这里表示fk:foreign key emp从表 dept主表
);
-- 删除外键
alter table drop foreign key fk_emp_dept;
-- 创建表后添加外键
alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);
数据库设计
需求分析
逻辑分析(ER图)
物理设计
维护设计
表
字段
表关系
表关系
1. 一对一
人 身份证
用户 用户详情
实现方式:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一
-- 一对一例
-- 用户表
create table tb_user(
id int primary key auto_increment,
nickname varchar(20),
age int
);
-- 用户详情表
create table tb_user_desc(
id int primary key auto_increment,
city varchar(20),
gender varchar(20)
tb_user_id int unique
);
alter table tb_user_desc add constraint fk_desc_user foreign key(tb_user_id) references tb_user(id);
2. 一对多(多对一)
部门 员工
实现方式:在多的一方建立外键,指向一的一方的主键
-- 一对多例子
-- 部门表
create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int
);
alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);
3. 多对多
商品 订单
实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
-- 多对多例
-- 订单表
create table tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type tinyint,
status tinyint
);
-- 商品表
create table tb_goods(
id int primary key auto_increment,
title varchar(20),
price double(10,2)
);
-- 订单商品中间表
create table tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);
多表查询
-- 笛卡尔积
select * from table1,table2;
连接查询
内连接
外连接
左外连接
右外连接
子查询
-- 部门表
create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int
);
alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);
连接查询
-- 内连接
-- 隐式内连接
select 字段列表 from table1,table2,... where 条件 and 条件 and ...;
-- 给表起别名
select t1.name, t2.dep_name from emp as t1, dept as t2 where t1.dep_id = t2.id;
-- 显式内连接
select 字段列表 from table1 [inner] join table2 on 条件
[inner] join table2 on 条件
...;
select * from emp inner join dept on emp.dep_id = dept.id;
select * from emp join dept on emp.dep_id = dept.id;
-- 外连接
-- 左外连接
select 字段列表 from table1 left [outer] join table2 on 条件;
select * from emp left outer join dept on emp.dep_id = dept.id;
select * from emp left join dept on emp.dep_id = dept.id;
-- 右外连接
select 字段列表 from table1 right [outer] join table2 on 条件;
select * from emp right outer join dept on emp.dep_id = dept.id;
select * from emp right join dept on emp.dep_id = dept.id;
子查询
查询中嵌套查询,称嵌套查询为子查询
单行单列
多行单列
多行多列
-- 单行单列
select 字段列表 from tab where 字段名 = (子查询);
-- 多行单列
select 字段列表 from tab where 字段名 in (子查询);
-- 多行多列
select 字段列表 from (子查询) where 条件;
-- 单行单列
-- 查询年龄高于aaa的员工
select * from emp where age > (select age from emp where name = 'aaa');
-- 多行单列
-- 查询部门1和部门2所有员工信息
select * from emp where dep_id in (select id from dept where dep_name in ('部门1','部门2'));
-- 多行多列
-- 查询年龄大于20岁的员工信息和部门信息
select * from (select * from emp where age > 20) as t1, dept where t1.dep_id = dept.id;
-- (select * from emp where age > 20)作为虚拟表
事务
数据库的事务包含一组数据库操作指令
这一组数据库命令要么同时成功,要么同时失败
开启事务
start transaction;
或begin;
无异常提交事务
commit;
出异常则回滚事务
rollback;
-- 转账操作
-- 查询a余额(不影响数据库)
-- 开启事务
begin;
-- a金额减500
update account set money = money - 500 where name = 'a';
-- b金额加500
update account set money = money + 500 where name = 'b';
-- 无异常时:提交事务
commit;
-- 有异常时:回滚事务
rollback;
事务的四大特征(ACID)
原子性(Atomicity):
事务是不可分割的最小操作单位,要么同时成功,要么同时失败
一致性(Consistency):
事务完成时,必须使所有的数据都保持一致
隔离性(Isolation):
多个事务之间,操作的可见性
持久性(Durability):
事务一旦提交或回滚,他对数据库中的数据的改变就是永久的
-- 查看事务的默认提交方式
select @@autocommit;
-- 更改事务的提交方式
set @@autocommit = 0; -- 手动提交
set @@autocommit = 1; -- 自动提交