学数据库的第二天

学习回顾

1. 数据库相关SQL

• 查看所有数据库 show databases;
• 创建数据库 create database db1;
• 查看数据库详情 show create database db1;
• 创建数据库指定字符集 create database db1 character set utf8/gbk;
• 删除数据库 drop database db1;
• 使用数据库 use db1;

2. 表相关

• 创建表 create table t1(name varchar(10),age int);
• 查询所有表 show tables;
• 查看表详情 show create table t1;
• 查看表字段 desc t1;
• 创建表指定引擎字符集 create table t1(name varchar(10),age int) engine=innodb/myisam charset=utf8/gbk;
• 删除表 drop 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 (字段1,字段2) values(值1,值2),(值1,值2);
• 查询数据 select name,age from t1 where 条件;
• 修改数据 update t1 set age=18 where id<10;
• 删除数据 delete from t1 where id=5;

练习：

1. 创建数据库db2 并使用 create database db2; use db2;
2. 创建员工表emp 有id和name字段 create table emp(id int,name varchar(10));
3. 修改表名为t_emp rename table emp to t_emp;
4. 添加部门名称的字段dept alter table t_emp add dept varchar(10);
5. 在name后面添加age字段 alter table t_emp add age int after name;
6. 插入刘关张三个人id分别为1,2,3部门都是三国部，再插入唐僧师徒4个人，id分别为4567 部门为取经部
   insert into t_emp (id,name,dept) values(1,‘刘备’,‘三国部’),(2,‘关羽’,‘三国部’),(3,‘张飞’,‘三国部’),(4,‘唐僧’,‘取经部’),(5,‘悟空’,‘取经部’),(6,‘八戒’,‘取经部’),(7,‘沙僧’,‘取经部’);
7. 给表再添加工资字段，添加到部门字段的前面
   alter table t_emp add sal int after age;
8. 修改取经部门的工资为100
   update t_emp set sal=100 where dept=‘取经部’;
9. 修改唐僧的名字为唐长老
   update t_emp set name=‘唐长老’ where name=‘唐僧’;
10. 修改三国部为蜀国部
    update t_emp set dept=‘蜀国部’ where dept=‘三国部’;
11. 删除猪八戒
    delete from t_emp where id=6;
12. 删除蜀国部的人
    delete from t_emp where dept=‘蜀国部’;
13. 修改所有员工的工资为800
    update t_emp set sal=800;
14. 删除所有人
    delete from t_emp;
15. 删除表
    drop table t_emp;

主键约束

• 主键：用于表示唯一性的字段称为主键
• 约束： 创建表时给字段添加的限制条件
• 主键约束：让字段不能重复并且不能为空(唯一且非空)
• 格式： 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(1,‘AAA’); //1
  insert into t2 values(null,‘BBB’); //2
  insert into t2 values(10,‘CCC’); //10
  insert into t2 values(null,‘DDD’); //11
  delete from t2 where id>=10; //删除数据自增数值不减少
  insert into t2 values(null,‘CCC’); //12

注释 comment

• 给字段添加的描述
• 格式: create table t3(id int primary key auto_increment comment ‘这是个id’, name varchar(10) comment ‘这是名字’);
• 通过 show create table t3; 查看注释

` 的作用

• 用于创建表时 修饰表名和字段名， 可以省略
  create table t4(id int);

冗余

• 由于表设计不够合理，导致出现的大量重复数据，称为数据冗余，通过合理拆分表的形式解决冗余问题

• 练习：
  保存集团总部下的教学研发部下的java教研部的员工苍老师年龄18岁，性别男，工资100
  创建两张表 分别是员工表和部门表

• 创建员工表
  create table emp(id int primary key auto_increment,name varchar(10), age int,gender varchar(5),sal int,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);

  insert into emp values(null,‘苍老师’,18,‘女’,100,3);

• 练习2：

  1. 保存家电分类下电视机分类下的小米电视 价格(price)1888 库存(num)200
  2. 办公用品分类下的打印机分类下的惠普打印机 价格1500 库存100
     -需要创建商品表(item)和分类表(category)
     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);

事物

is null 和 is not null

• 判断字段值为null不能用=，要使用is null
  create table person (id int primary key auto_increment,name varchar(10),age int);
  insert into person values(null,‘张三’,null),(null,‘李四’,18),(null,‘王五’,null);

1. 查询age为null的姓名
   select name from person where age is null;
2. 查询age不为null的姓名
   select name from person where age is not null;

别名

select name from person;
select name as '姓名' from person;
select name '姓名' from person;
select name 姓名 from person;

事务

• 什么是事务： 事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL语句全部执行成功，或全部执行失败，不会出现部分成功部分失败的情况。

• 事务相关指令：

  1. 开启事务： begin;
  2. 提交事务： commit;
  3. 回滚事务： rollback;

• 转账业务：
  create table user(id int primary key auto_increment,name varchar(10), money int,status varchar(5));
  insert into user values(1,‘超人’,50,‘冻结’),(2,‘蝙蝠侠’,2000,‘正常’);

  转账SQL：蝙蝠侠-100
  update user set money=money-100 where id=2 and status=‘正常’;
  转账SQL：超人+100
  update user set money=money+100 where id=1 and status=‘正常’;

• 验证事务回滚流程：

  1. 开启事务
     begin;
  2. 蝙蝠侠-100
     update user set money=money-100 where id=2 and status=‘正常’;
  3. 此时开启另外一个终端查询数据是否发生改变(没变，改变的是内存数据)
  4. 超人+100
     update user set money=money+100 where id=1 and status=‘正常’;
  5. 此时判断出两条SQL只成功了一条，所以需要将内存中的改动回滚
     rollback;

• 验证事务提交流程：
  insert into user values(3,‘灭霸’,8888,‘正常’);

  1. 开启事务
     begin;
  2. 灭霸-5000
     update user set money=money-5000 where id=3 and status=‘正常’;
  3. 蝙蝠侠+5000
     update user set money=money+5000 where id=2 and status=‘正常’;
  4. 经过判断两条SQL全部执行成功，此时可以将内存中的改动一次性提交到磁盘中
     commit;

• 保存回滚点
  begin;
  update user set money=6000 where id=2;
  savepoint s1;
  update user set money=5000 where id=2;
  savepoint s2;
  update user set money=4000 where id=2;

• 回滚到指定的回滚点
  rollback to s2;

SQL分类

1. DDL：Data Definition Language，数据定义语言 包括：create、drop、alter、truncate 不支持事物
   • truncate table 表名;
     删除表并创建新表， 比删除全表数据执行效率高，删除数据自增数值不变，这种方式自增数值清零
2. DML：Data Manipulation Language, 数据操作语言 包括:insert、delete、update、select（DQL） 支持事务
3. DQL： Data Query Language，数据查询语言 只包括select。
4. TCL： Transaction Control Language，事务控制语言 包括：begin,commit rollback,savepoint xxx,rollback to xxx
5. DCL： Date Control Language，数据控制语言， 分配用户权限相关的SQL。

数据类型

1. 整数： int(m) bigint(m) ,bigint对应java 中的long ，m代表显示长度
   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代表的是小数长度 25.321 m=5 d=3 ,decimal(m,d)是超高精度浮点数 只有涉及超高精度运算时使用

3. 字符串：

• char(m):固定长度，char(10) “abc” 10 ,执行效率高 最大长度255
• varchar(m):可变长度,varchar(10) “abc” 3 , 节省空间 最大长度65535 超过255建议使用text
• text(m):可变长度 最大长度65535

4. 日期：

• date：只能保存年月日
• time：只能保存时分秒
• datetime：保存年月日时分秒，默认值为null，最大值9999-12-31
• timestamp：时间戳（距1970年1月1日 08：00：00毫秒数）保存年月日时分秒，最大值是2038-01-19,默认值当前系统时间
  create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
  insert into t_date values(‘2019-04-18’,null,null,null);
  insert into t_date values(null,‘16:33:20’,‘2019-04-18 11:00:00’,null);

5. 其它

导入*.sql文件

1. linux系统： 把文件放到桌面 执行以下指令、
   source /home/soft01/桌面/tables.sql;

2. window系统：把文件放到D盘根目录
   source d:/tables.sql;

3. 导入后执行 show databases; 查看是否出现了newdb3 出现的话 使用 newdb3
   然后再查看里面是否有四张表

is null 和 is not null

1. 查询 没有上级领导的员工姓名和工资
   select ename,sal from emp where mgr is null;
2. 查询有奖金的员工姓名，工资，奖金
   select ename,sal,comm from emp where comm is not null and comm>0;

课程回顾

day01

1. 数据库相关

• 查看所有 show databases;
• 创建数据库 create database db1 character set utf8/gbk;
• 查看详情 show create database db1;
• 删除数据库 drop database db1;
• 使用 use db1;

2. 表相关

• 创建表 create table t1(id int,name varchar(10)) engine=innodb/myisam charset=utf8/gbk;
• 查看所有表 show tables;
• 查看详情 show create table t1;
• 查看表字段 desc t1;
• 删除表 drop table t1;
• 删除并创建新表 truncate 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. 数据相关

• insert into t1 (name,age) values(值1,值2),(值1,值2),(值1,值2);
• select * from t1 where 条件;
• update t1 set xxx=xxx where 条件;
• delete from t1 where 条件;

day02

1. 主键约束： 唯一且 非空 primary key
2. 自增 auto_increment 历史最大值+1 只增不减
3. 注释 comment
4. ` 用于修饰表名和字段名 可以省略
5. 冗余： 表设计不合理导致的大量重复数据， 通过合理拆分表解决
6. is null 和 is not null
7. 事务： 数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部失败。 begin commit rollback savepoint xxxx; rollback to xxxx;
8. SQL分类

• DDL 数据定义语言 包括：create drop alter truncate 不支持事务
• DML 数据操作语言 包括： insert update delete select（DQL） 支持事务
• DQL 数据查询语言 包括：select
• TCL 事务控制语言 包括：begin commit rollback savepoint xxxx; rollback to xxxx;
• DCL 数据控制语言 分配用户权限相关SQL

9. 数据类型

• 整数 int(m) 和bigint(m) m代表显示长度 需要结合zerofill使用
• 浮点数 double(m,d) m代表总长度 d小数长度 decimal（m,d）
• 字符串： char 不可变长度 最大255 varchar 可变长度 最大65535超过255建议使用text text 可变长度 最大65535
• 日期： date time datetime 9999-12-31 timestamp 2038-1-19

10. 导入SQL文件
    source 文件路径

练习题：

1. 查询员工表的所有员工姓名、工资、部门编号、上级领导编号
   select ename,sal,deptno,mgr from emp;
2. 查询工资低于2000的员工信息
   select * from emp where sal<2000;
3. 查询20号部门的员工姓名、工资
   select ename,sal from emp where deptno=20;
4. 查询10号部门工资低于2000的员工姓名和奖金
   select ename,comm from emp where deptno=10 and sal<2000;
5. 查询工资大于1000 并且有奖金的员工姓名、工资、奖金
   select ename,sal,comm from emp where sal>1000 and comm>0;
6. 查询10号部门有领导的员工姓名和领导编号
   select ename,mgr from emp where deptno=10 and mgr is not null;
7. 查询商品表中单价低于100的 商品标题和商品的单价
   select title,price from t_item where price<100;