课程回顾

1. 数据库相关sql
2. 查询所有show databases;
3. 创建数据库 create database db1 character set utf8/gbk;
4. 查看详情 show create database db1;
5. 删除数据库 drop database db1;
6. 使用数据库 use db1;
7. 表相关sql
8. 创建表 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
9. 查看详情 show create table t1;
10. 查看所有表 show tables;
11. 查看表字段 desc t1;
12. 删除表 drop table t1;
13. 修改表名 rename table t1 to t2;
14. 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
15. 添加字段 alter table t1 add age int first/ after xxx;
16. 删除字段 alter table t1 drop age;
17. 修改字段名和类型 alter table t1 change 原名 新名 新类型;
18. 修改类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;
19. 数据相关sql
20. 插入数据 insert into t1 (字段1,字段2) values(值1,值2),(值1,值2);
21. 查询数据 select name,age from t1 where age<50;
22. 修改数据 update 表名 set name='张三',age=13 where id=5;
23. 删除数据 delete from 表名 where id<10;

练习：

1. 创建数据库mydb1 字符集为utf8，并使用 create database mydb1 character set utf8; use mydb1;
2. 创建emp员工表 字段：id，名字name，年龄age，引擎为myisam 字符集为utf8 create table emp(id int,name varchar(10),age int) engine=myisam charset=utf8;
3. 添加部门dept字段在最后 alter table emp add dept varchar(10);
4. 添加salary字段在部门字段的前面 alter table emp add salary int after age;
5. 添加以下数据：1 诸葛亮 22 3000 法师部，2 周瑜 30 4000 法师部，3 关羽 3500 战士部，4 张飞 1500 战士部，5 孙尚香 18 6000 射手部， 6 狄仁杰 50 射手部 insert into emp values(1,'诸葛亮',22,3000,'法师部'),(2,'周瑜',30,4000,'法师部'),(3,'关羽',28,3500,'战士部'),(4,'张飞',23,1500,'战士部'),(5,'孙尚香',18,6000,'射手部'),(6,'狄仁杰',50,9000,'射手部');
6. 查询所有员工的姓名和工资 select name,salary from emp;
7. 修改法师部的名字为销售部 update emp set dept='销售部' where dept='法师部';
8. 修改20岁以下的员工 工资为888 update emp set salary=888 where age<20;
9. 删除射手部的所有员工 delete from emp where dept='射手部';
10. 删除年龄小于25岁的员工 delete from emp where age<25;
11. 删除表内所有数据 delete from emp;
12. 删除表 删除数据库 drop table emp; drop database mydb1;

课堂练习

1. 创建mydb2数据库字符集utf8 并使用 create database mydb2 character set utf8;
2. 创建商品表item 字段：id,商品标题title 引擎myisam 字符集为gbk create table item(id int,title varchar(10))engine=myisam charset=gbk;
3. 修改表名为items rename table item to items;
4. 修改引擎为innodb 字符集为utf8 alter table items engine=innodb charset=utf8;
5. 添加表字段单价price alter table items add price int;
6. 在单价前面添加库存num alter table items add num int after title;
7. 在库存后面添加分类字段category 字符串类型 alter table items add category varchar(10) after num;
8. 添加以下商品 1 苹果手机 100 手机 6888，2 华为手机 1000 手机 8888，3 小米电视 98 家电 2988，4 夏普电视 200 家电 4000，5 棉裤 2000 服装 38，6 棉袄 50 服装 68 insert into items values(1,'苹果手机',100,'手机',6888),(2,'华为手机',1000,'手机',8888),(3,'小米电视',98,'家电',2988),(4,'夏普电视',200,'家电',4000),(5,'棉裤',2000,'服装',38),(6,'棉袄',50,'服装',68);
9. 修改小米电视的库存为97价格为2888 update items set num=97,price=2888 where id=3;
10. 给表添加卖点sellpoint字段 字符串长度20 alter table items add sellpoint varchar(20);
11. 修改苹果手机的卖点为 买手机送ipad update items set sell_point='买手机送ipad' where id=1;
12. 修改棉裤和棉袄的卖点为 赠送袜子一只，多买多送 update items set sell_point='赠送袜子一只，多买多送' where category='服装';
13. 删除价格为68的商品 delete from items where price=68;
14. 删除家电分类的商品 delete from items where category='家电';
15. 删除表里所有数据 ，删除表，删除数据库 delete from items; drop table items; drop database mydb2;

主键约束

• 什么是主键：用于表示数据唯一性的字段称为主键
• 约束：是给表字段添加的限制条件
• 主键约束：限制主键字段的值 唯一并且非空
• 格式： create table t1(id int primary key,name varchar(10)); 测试： insert into t1 values(1,'悟空'); //成功 insert into t1 values(1,'八戒'); //失败 不能重复 insert into t1 values(null,'沙僧'); //失败 不能为null insert into t1 values(2,'玉帝哥哥');//成功

主键约束+自增 primary key auto_increment

• 格式： create table t2(id int primary key auto_increment,name varchar(10)); 测试： insert into t2 values(null,'悟空'); 1 insert into t2 (name) value ('八戒'); 2 insert into t2 values(10,'小龙女'); 10 insert into t2 values(null,'白龙马'); 11 delete from t2 where id>9; insert into t2 values(null,'黑熊怪'); 12 delete from t2; insert into t2 values(null,'奔波霸'); 13
• 自增数值只增不减
• 从历史最大值基础上+1

truncate

• 格式： truncate table t2;

• 删除表 并且创建新表

• 自增数值清零

注释 comment

• 格式： create table t3(id int primary key auto_increment comment '这是主键',name varchar(10) comment '这是名字'); 查看注释： show create table t3;

`和'区别

• :是修饰表名和字段名的 可以省略 create tablet4(id` int);
• ': 是用于修饰字符串的

数据冗余

• 如果表设计不够合理，当数据量增多时出现的大量重复数据，这种现象称为数据冗余，通过拆分表的方式解决冗余问题

• 外键： 用于建立关系的字段称为外键

• 请设计表保存以下数据：
  1. 集团总部下教学研发部下Java校验一部苍老师工资20年龄18，性别男，工作地点在北京
  2. 集团总部下市场部市场A部的员工小李工资50年龄80，性别女，工作地点在上海
• 创建两张表： create table emp(id int primary key autoincrement,name varchar(10),age int,sal int,gender varchar(2),deptid int); create table dept(id int primary key autoincrement,name varchar(10),loc varchar(10),parentid int);
• 部门表插入数据： insert into dept (name,loc,parent_id) values('集团总部','北京',null),('教学研发部','北京',1),('Java教学一部','北京',2),('市场部','上海',1),('市场A部','上海',4);
• 员工表插入数据 insert into emp (name,age,sal,gender,dept_id) values('苍老师',18,20,'男',3),('小李',80,50,'女',5);

练习:设计表保存以下数据

1. 家电分类下洗衣机分类下的海尔滚筒洗衣机，价格1888，库存100
2. 办公用品分类下打印机分类下的惠普激光打印机，价格2388，库存50
3. 创建两个表：商品表item(id,name,price,num,categoryid)和分类表category(id,name,parentid) create table item(id int primary key autoincrement,name varchar(10),price int,num int,categoryid int); create table category(id int primary key autoincrement,name varchar(10),parentid int);
4. 插入数据 insert into category values(null,'家电',null),(null,'洗衣机',1),(null,'办公用品',null),(null,'打印机',3); insert into item values(null,'海尔滚筒洗衣机',1888,100,2),(null,'惠普激光打印机',2388,50,4);

事务

• 事务是数据库中执行同一业务多条sql语句的工作单元 可以保证多条sql语句要么全部执行成功，要么全部执行失败。

• 创建用户表： create table user(id int,name varchar(10),money int,state varchar(5));

• 插入数据： insert into user values(1,'超人',50,'冻结'),(2,'蝙蝠侠',2000,'正常');

• 蝙蝠侠给超人转账200块钱 无事务保护： update user set money=money-200 where id=2 and state='正常'; update user set money=money+200 where id=1 and state='正常';

• 有事务保护： 转账失败 begin; //开启事务 update user set money=money-200 where id=2 and state='正常';

  update user set money=money+200 where id=1 and state='正常'; rollback; //回滚

• 有事务保护： 转账成功 insert into user values(3,'海王',10,'正常'); -蝙蝠侠给海王转账500 begin; update user set money=money-500 where id=2 and state='正常'; update user set money=money+500 where id=3 and state='正常'; commit; //提交
• 保存回滚点 savepoint 标识; begin; update user set money=money-10 where id=2; savepoint s1; update user set money=money-50 where id=2; update user set money=money-100 where id=2; rollback to s1;

SQL分类

DDL Data Definition Language 数据定义语言

• 包括： create，drop，alter，truncate
• 不支持事务

DML Data Manipulation Language 数据操作语言

• 包括： insert，update，delete，select（DQL）
• 支持事务 select和事务没有关系

DQL Data Query Language 数据查询语言

• 只包括select

TCL Transaction Control Language 事务控制语言

• 包括：begin 、 rollback、 commit、 savepoint s1 、rollback to s1

DCL Data Control Language 数据控制语言

• 涉及分配用户权限相关的SQL

数据库的数据类型

整数

• int对应java里面的int bigint对应java中的long
• int(m) m代表显示长度 需要结合zerofill使用 create table tint(id int,age int(10) zerofill); insert into tint values(1,18); select * from t_int;

浮点数

• double(m,d) m代表总长度 d代表小数长度 76.234 m=5 d=3
• decimal超高精度浮点数，涉及超高精度运算时使用

字符串

• char(m): 不可变长度字符串 m=10 "abc" 所占长度为10 ，执行效率略高
• varchar(m):可变长度字符串 m=10 "abc" 所占长度3 好处是节省空间 最大长度为65535，但是建议长度不要超过255 超过255建议使用text
• text(m):可变长度字符串 m=10 "abc" 所占长度3 最大长度65535

日期

• date：只保存年月日
• time：只保存时分秒
• datetime：年月日时分秒 默认值null 最大值9999-12-31

• timestamp：年月日时分秒 默认值当前时间 最大值2038-1-19，以时间戳的形式保存时间 create table tdate(t1 date ,t2 time ,t3 datetime ,t4 timestamp); insert into tdate values('2019-1-15',null,null,null);

  insert into t_date values(null,'16:57:38', '2018-10-22 20:18:30',null);

day01

1. 数据库相关sql
2. 查看所有 show databases;
3. 创建数据库 create database db1 character set utf8/gbk;
4. 查看详情 show create database db1;
5. 删除数据库 drop database db1;
6. 使用数据库 use db1;
7. 表相关sql
8. 创建表 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
9. 查看所有表 show tables;
10. 查看详情 show create table t1;
11. 表字段 desc t1;
12. 删除表 drop table t1;
13. 删除并创建新表 truncate table t1;
14. 修改表名 rename table t1 to t2;
15. 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
16. 添加表字段 alter table t1 add age int;
17. 删除表字段 alter table t1 drop age;
18. 修改字段名和类型 alter table t1 change 原名 新名 新类型
19. 修改类型和位置 alter table t1 modify 字段名 类型 first/after xxx;
20. 数据相关
21. 插入数据：insert into t1 (字段1,字段2,字段3) values(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
22. 删除数据 delete from t1 where id=5;
23. 修改数据 update t1 set age=20 where id<5;
24. 查询数据 select name,age from t1 where id=10;

day02

1. 主键约束：表示数据唯一性的字段称为主键, 唯一且非空 primary key
2. 主键+自增： 历史最大值+1 只增不减 create table t1(id int primary key auto_increment);
3. 注释 comment
4. `:用于修饰表名和字段名 可以省略，'：用于修饰字符串
5. 数据冗余： 表设计不合理导致的大量重复数据称为数据冗余，通过拆分表的形式解决
6. 事务：数据库中执行同一业务多条sql语句的工作单元，可以保证多条全部成功或全部失败
7. 开启事务 begin
8. 回滚事务 rollback
9. 提交事务 commit
10. 保存回滚点 savepoint xxx;
11. 回滚到某个回滚点 rollback to xxx;
12. SQL分类
13. DDL：数据定义语言 包括：create alter drop truncate 不支持事务
14. DML：数据操纵语言 包括：insert delete update select(DQL) 支持事务
15. DQL: 数据查询语言 只包括select
16. TCL：事务控制语言 包括：begin rollback commit savepoint rollback to
17. DCL: 分配用户权限相关sql
18. 数据类型
19. 整数 、 浮点数、字符串、日期、其它
20. 整数 int(m) bigint(m) m代表显示长度 需要结合zerofill使用
21. 浮点数 double(m,d) m代表总长度 d代表小数长度 decimal超高精度浮点数
22. 字符串 char:不可变长度 最大255 执行效率略高 varchar(m): 可变长度 最大65535 超过255建议使用text 节省空间 ， text可变长度 最大65535
23. 日期： date年月日 time 时分秒 datetime年月日时分秒 最大9999-12-31 默认值为null timestamp年月日时分秒 最大2038-1-19 默认值为当前时间