2019年6月20日数据库 : 第二天
课程回顾
1. 数据库相关SQL
- 查询所有数据库 show databases;
- 创建数据库 create database db1 character set utf8/gbk;
- 查看数据库详情 show create database db1;
- 删除数据库 drop database db1;
- 使用数据库 use db1;
2. 表相关SQL
- 创建表 create table t1(name varchar(10),age int) engine=innodb/myisam charset=utf8/gbk;
- 查询所有表 show tables;
- 查询表详情 show create table t1;
- 查询表字段 desc t1;
- 删除表 drop table t1;
- 修改表名 rename table t1 to t2;
- 修改表引擎和字符集 alter table t1 engine=innodb/myisam charset=utf8/gbk;
- 添加表字段 alter table t1 add 字段名 类型 first/after xxx;
- 删除表字段 alter table t1 drop 字段名;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型
- 修改字段类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;
##3. 数据相关SQL - 插入数据 insert into 表名 values(值1,值2),(值1,值2),(值1,值2);
insert into 表名 (字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2); - 查询数据 select 字段信息 from 表名 where 条件;
- 修改数据 update 表名 set 字段名=值 where 条件;
- 删除数据 delete from 表名 where 条件;
练习题
- 创建数据库mydb2 字符集utf8 并使用
create database mydb2 character set utf8;
use mydb2; - 创建员工表emp 有id和name字段
create table emp (id int,name varchar(10)); - 修改表名为t_emp
rename table emp to t_emp; - 添加部门名称字段dept
alter table t_emp add dept varchar(10); - 在name后面添加age字段
alter table t_emp add age int after name; - 插入刘关张三个人id分别为1,2,3部门都是三国部,再插入唐僧师徒4个人,id分别为4567 部门为取经部
insert into t_emp values
(1,‘刘备’,38,‘三国部’),(2,‘关羽’,37,‘三国部’),(3,‘张飞’,36,‘三国部’),(4,‘唐僧’,30,‘取经部’),(5,‘悟空’,500,‘取经部’),(6,‘八戒’,300,‘取经部’),(7,‘沙僧’,200,‘取经部’); - 给表再添加工资字段,添加到部门字段的前面
alter table t_emp add sal int after age; - 修改取经部门的工资为100
update t_emp set sal=100 where dept=‘取经部’; - 修改唐僧的名字为唐长老
update t_emp set name=‘唐长老’ where name=‘唐僧’; - 修改三国部为蜀国部
update t_emp set dept=‘蜀国部’ where dept=‘三国部’; - 删除猪八戒
delete from t_emp where name=‘八戒’; - 删除蜀国部的人
delete from t_emp where dept=‘蜀国部’; - 修改所有员工的工资为800
update t_emp set sal=800; - 删除所有人 15. 最后删除表
delete from t_emp;
drop table t_emp;
主键约束 primary key
-
什么是主键: 表示数据唯一性的字段称为主键
-
什么是约束: 是创建表时给表字段添加的限制条件
-
主键约束: 让该字段的数据唯一且非空(不能重复,不能null)
-
格式:
create table t1(id int primary key,name varchar(10)); insert into t1 values(1,'AAA'); //成功! insert into t1 values(1,'BBB');//报错 不能重复 insert into t1 values(null,'CCC'); //报错 不能为null
主键约束+自增
-
自增数值只增不减,从历史最大值基础上+1
-
格式:
create table t2(id int primary key auto_increment,name varchar(10)); insert into t2 values(null,'aaa'); //1 insert into t2 values(null,'bbb'); //2 insert into t2 values(3,'ccc'); //3 insert into t2 values(10,'ddd'); //10 insert into t2 values(null,'eee'); //11 delete from t2 where id>=10; insert into t2 values(null,'fff'); //12
注释
-
格式:
create table t3 (id int primary key auto_increment comment '这是主键',name varchar(10) comment '这是名字');
` 的作用
-
用于修饰表名和字段名,可以省略
create table `t4`(`id` int,`name` varchar(10));
冗rong余
- 由于表设计不够合理导致的大量重复数据称为数据冗余
- 练习:
- 创建表保存以下数据
集团总部下的教学研发部下的Java教研部下的苍老师工资200年龄18性别男
集团总部下的市场部下的市场A部下的小明工资5000年龄25性别男
-
创建员工表和部门表
create table emp(id int primary key auto_increment,name varchar(10),age int,sal int,gender varchar(10),dept_id int); create table dept(id int primary key auto_increment,name varchar(10),parent_id int);
-
插入数据:
insert into dept values(null,'集团总部',null),(null,'教学研发部',1),(null,'Java教研部',2),(null,'市场部',1),(null,'市场A部',4); insert into emp values(null,'苍老师',18,200,'男',3),(null,'小明',25,5000,'男',5);
- 创建表保存以下数据
- 练习2:
创建商品表item和分类表category保存以下数据- 保存家电分类下电视机分类下的小米电视 价格(price)1888 库存(num)200
- 办公用品分类下的打印机分类下的惠普打印机 价格1500 库存100
-
创建表
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int); create table category(id int primary key auto_increment,name varchar(10),parent_id int);
-
插入数据:
insert into category values(null,'家电',null),(null,'电视机',1),(null,'办公用品',null),(null,'打印机',3); insert into item values(null,'小米电视',1888,200,2),(null,'惠普打印机',1500,100,4);
###事务
-
什么是事务:事务是数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败.
-
事务相关指令:
- 开启事务 begin;
- 提交事务 commit;
- 回滚 rollback;
-
验证转账流程:
create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5)); insert into user values(null,'超人',50,'冻结'),(null,'蝙蝠侠',5000,'正常'),(null,'灭霸',20,'正常');
-
转账的SQL:
update user set money=money-2000 where id=2 and status='正常'; update user set money=money+2000 where id=1 and status='正常';
-
有事务保护的情况下 回滚流程:
-
开启事务
begin;
-
蝙蝠侠-2000
update user set money=money-2000 where id=2 and status='正常';
-
此时在当前终端查询数据时 数据已经改变(因为查询到的是内存中的改动), 开启另外一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)
-
超人+2000
update user set money=money+2000 where id=1 and status='正常';
-
此时从执行结果中发现一条成功一条失败,应该执行回滚操作
rollback;
-
-
有事务保护的情况下 提交流程:
-
开启事务
begin;
-
蝙蝠侠-2000
update user set money=money-2000 where id=2 and status='正常';
-
此时仍然是在内存中改动 磁盘数据没有发生改变
-
灭霸+2000
update user set money=money+2000 where id=3 and status='正常';
-
此时两次改动都是在内存中改完,发现两次全部成功,所以执行提交
commit;
-
-
保存回滚点:
begin; update user set money=1 where id=2; savepoint s1; update user set money=2 where id=2; savepoint s2; update user set money=3 where id=2; rollback to s2;
-
事务的ACID特性 面试常考
保证事务正确执行的四大基本要素1. Atomicity 原子性: 最小不可拆分 保证全部执行成功或全部执行失败 2. Consistency 一致性: 从一个一致状态到另一个一致状态 3. Isolation 隔离性: 多个事务之间互相隔离互不影响 4. Durability 持久性: 当事务提交后数据保存到磁盘中持久生效
SQL分类
DDL Data Definition Language数据定义语言
- truncate table 表名;
删除表并创建新表 让自增数值清零 - 包括: create drop alter truncate
- 不支持事务
DML Data Manipulation Language数据操作语言
- 包括: insert update delete select
- 支持事务
DQL Data Query Language 数据查询语言
- 只包括: select
TCL Transaction Control Language 事务控制语言
- 包括: begin,commit,rollback,savepoint xxx, rollback to xxx;
DCL Data Control Language 数据控制语言
- 负责分配用户权限相关的SQL
数据类型
1. 整数: int(m) 和 bigint(m) m代表的是显示长度,需要结合zerofill使用
create table t_int(id int,age int(10) zerofill);
insert into t_int values(1,18);
select * from t_int;
2. 浮点数: double(m,d) m代表的是总长度 d代表小数长度,超高精度的浮点数decimal(m,d)
25.234 m=5 d=3
3. 字符串:
- char(m) 固定长度 最大长度255 好处执行效率略高
- varchar(m) 可变长度 好处节省空间 最大长度65535(但是超过255建议使用text)
- text(m) 可变长度 最大长度65535
4. 日期:
- date: 保存年月日
- time: 保存时分秒
- datetime: 保存年月日时分秒,默认值为null,最大9999-12-31
- timestamp(时间戳):保存年月日时分秒,默认值为当前系统时间,最大值2038-1-19
-
create table t1_date(t1 date,t2 time,t3 datetime,t4 timestamp); insert into t1_date values('2019-6-20',null,null,null); insert into t1_date values(null,'16:46:30','2019-06-20 16:30:30',null); +------------+----------+---------------------+---------------------+ | t1 | t2 | t3 | t4 | +------------+----------+---------------------+---------------------+ | 2019-06-20 | NULL | NULL | 2019-06-25 21:00:32 | | NULL | 16:46:30 | 2019-06-20 16:30:30 | 2019-06-25 21:00:32 | +------------+----------+---------------------+---------------------+
导入*.sql文件
-
windows系统 把文件放在C或D盘的根目录
source d:/tables.sql;
-
linux系统 把文件放在桌面
source /home/soft01/桌面/tables.sql show databases; use newdb3; show tables; 四张表 select * from t_item; 如果是乱码 执行 set names gbk;
课程回顾
day01
1. 数据库相关SQL
- show databases;
- create database db1 character set utf8/gbk;
- show create database db1;
- drop database db1;
- use db1;
2. 表相关SQL
- create table t1(name varchar(10),age int);
- show tables;
- show create table t1;
- desc t1;
- drop table t1;
- truncate table t1;
- rename table t1 to t2;
- alter table t1 engine=myisam/innodb charset=utf8/gbk;
- alter table t1 add 字段名 类型 first/after xxx;
- alter table t1 drop 字段名;
- alter table t1 change 原名 新名 新类型
- alter table t1 modify 字段名 新类型 first/after xxx;
3. 数据相关
- insert into t1 (name,age) values(值1,值2),(值1,值2),(值1,值2);
- select * from t1 where 条件;
- update t1 set xxx=xxxx where 条件;
- delete from t1 where 条件;
day02
1. 主键约束: 唯一且非空 primary key
2. 主键+自增 auto_increment
只增不减 从历史最大值+1
3. 注释 comment
4. ` 用于修饰表名和字段名 可以省略
5. 冗余: 由于表设计不够合理导致的大量重复数据 ,通过合理拆分表解决
6. 事务: 数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败
- 相关指令: 开启事务begin 提交事务 commit 回滚 rollback
- 保存回滚点 savepoint xxx; rollback to xxx;
四大特性
1. 原子性: 最小不可拆分 保证全部成功或全部失败
2. 一致性: 从一个一致状态到另一个一致状态
3. 隔离性: 多个事务互相隔离 互不影响
4. 持久性: 事务提交后 数据保存到磁盘中持久生效
7. 数据类型
- 整数: int 和bigint(m) m代表显示长度 结合zerofill使用
- 浮点数: double(m,d) m代表总长度 d小数长度 超高精度浮点数 decimal(m,d)
- 字符串: char 固定长度 最大255 执行效率高 varchar(m) 可变长度 最大65535超过255建议使用text text 可变长度 最大65535
- 日期: date 年月日 time 时分秒 datetime 最大9999-12-31 默认null timestamp 最大2038-1-19 默认当前系统时间
8. 导入sql文件
source 路径;