SQL-Day02

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 条件;

练习题

  1. 创建数据库mydb2 字符集utf8 并使用
    create database mydb2 character set utf8;
    use mydb2;
  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 values
    (1,‘刘备’,38,‘三国部’),(2,‘关羽’,37,‘三国部’),(3,‘张飞’,36,‘三国部’),(4,‘唐僧’,30,‘取经部’),(5,‘悟空’,500,‘取经部’),(6,‘八戒’,300,‘取经部’),(7,‘沙僧’,200,‘取经部’);
  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 name=‘八戒’;
  12. 删除蜀国部的人
    delete from t_emp where dept=‘蜀国部’;
  13. 修改所有员工的工资为800
    update t_emp set sal=800;
  14. 删除所有人 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余

  • 由于表设计不够合理导致的大量重复数据称为数据冗余
  • 练习:
    1. 创建表保存以下数据
      集团总部下的教学研发部下的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保存以下数据
    1. 保存家电分类下电视机分类下的小米电视 价格(price)1888 库存(num)200
    2. 办公用品分类下的打印机分类下的惠普打印机 价格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全部执行成功或全部执行失败.

  • 事务相关指令:

    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(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='正常';
    
  • 有事务保护的情况下 回滚流程:

    1. 开启事务

       begin;
      
    2. 蝙蝠侠-2000

       update user set money=money-2000 where id=2 and status='正常';
      
    3. 此时在当前终端查询数据时 数据已经改变(因为查询到的是内存中的改动), 开启另外一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)

    4. 超人+2000

       update user set money=money+2000 where id=1 and status='正常';
      
    5. 此时从执行结果中发现一条成功一条失败,应该执行回滚操作

       rollback;
      
  • 有事务保护的情况下 提交流程:

    1. 开启事务

       begin;
      
    2. 蝙蝠侠-2000

       update user set money=money-2000 where id=2 and status='正常';
      
    3. 此时仍然是在内存中改动 磁盘数据没有发生改变

    4. 灭霸+2000

       update user set money=money+2000 where id=3 and status='正常';
      
    5. 此时两次改动都是在内存中改完,发现两次全部成功,所以执行提交

       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 路径;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值