mysql从入门到精通（2）-事务，数据库的数据类型

###总结上次        
####数据库相关SQL
1. 查询所有数据库 show databases;
2. 创建数据库 create database db1 character set utf8/gbk;
3. 查看详情 show create database db1;
4. 删除数据库  drop database db1;
5. 使用数据库 use db1;
####表相关SQL
1. 查询所有表 show tables;
2. 创建表 create table t1(name varchar(10),age int) engine=innodb/myisam charset=utf8/gbk;
3. 查看详情 show create table t1;
4. 表字段  desc t1;
5. 删除表 drop table t1;
6. 改表名 rename table t1 to t2;
7. 改引擎字符集 alter table t1 engine=innodb/myisam charset=utf8/gbk;
8. 添加字段 alter table t1 add age int first/ after xxx;
9. 删除字段 alter table t1 drop age;
10. 修改字段名字和类型 alter table t1 change sal salary int;
11. 修改类型和位置 alter table t1 modify sal int first/after xxx;
####数据相关
1. 插入数据 insert into t1 (name,age) values(值1,值2),(值1,值2);
2. 查询数据 select age,name from t1 where id<10;
3. 修改数据 update t1 set age=18 where id=10;
4. 删除数据 delete from t1 where id=10;

###练习：
1. 创建数据库db2指定字符集为utf8 并使用，在数据库中，创建emp表里面有id,name,age,salary,部门名称(dept)
    create database db2 character set utf8;
    use db2;
    create table emp(id int,name varchar(10),age int,salary int,dept varchar(10));
2. 往上面表格中插入 刘关张三人 和 取经四人，工资在3000-8000随意设置，年龄随意，id为1-7，刘关张属于三国部，剩下4个人属于取经部
    insert into emp values(1,'刘备',25,4000,'三国部'),(2,'关羽',24,8000,'三国部'),(3,'张飞',22,3000,'三国部'),(4,'悟空',250,5000,'取经部'),(5,'八戒',350,6000,'取经部'),(6,'沙僧',450,7000,'取经部'),(7,'唐僧',50,8000,'取经部');
3. 给表格添加一个性别字段在年龄的后面
    alter table emp add gender varchar(5) after age;
4. 修改所有的性别字段值为男
    update emp set gender='男';
5. 添加一个貂蝉，性别女，年龄随意，工资7000，部门为三国部
    insert into emp values(8,'貂蝉',18,'女',7000,'三国部');
6. 修改年龄小于30岁的员工工资为200
    update emp set salary=200 where age<30;
7. 修改取经部的所有人性别为未知
    update emp set gender='未知' where dept='取经部';
8. 查询三国部的员工姓名和工资
    select name,salary from emp where dept='三国部'
9. 删除所有女员工 
    delete from emp where gender='女';
10. 删除部门中工资低于5000的员工
    delete from emp where salary<5000;

###主键约束 primary key
- 主键：用于表示数据唯一性的字段称为主键
- 约束： 是给表字段添加的限制条件 
- 主键约束：限制主键字段值不能重复并且非空 (唯一且非空)
    create table t1(id int primary key,name varchar(10));
    insert into t1 values(1,'Tom');
    insert into t1 values(1,'Jerry');//失败 重复
    insert into t1 values(null,'ABC');//失败 不能为null

- 自增： auto_increment
    create table t2(id int primary key auto_increment,name varchar(10));
    insert into t2 values(null,'AAA');
    insert into t2 values(2,'AAA');
    insert into t2 values(10,'mm');
    insert into t2 values(null,'AAA');
    1. 当字段值为null的时候值会自己增长
    2. 自增字段值也可以手动赋值
    3. 增长规则：从曾经出现的最大值基础上+1
    4. 自增数值只增不减 (delete清空表 自增数值并不清零)

###注释 comment
- 创建表声明字段的时候给字段添加的介绍
    create table t3(id int primary key auto_increment comment '这是一个主键',name varchar(10) comment '这是员工的姓名');
###`和'
- `:用于修饰表名和字段名 可以省略(键盘上ESC下面的那个键,不是单引号哦！)
    create table `t4`(`id` int,`name` varchar(10));
- ':用于修饰字符串
###数据冗余 
- 如果表设计不够合理，保存大量数据的同时可能随之会出现大量重复数据，这些重复数据的现象就称为数据冗余，通过拆分表的形式解决冗余问题

###练习：设计表保存以下数据：
1. 集团总部下面的市场部下的市场A部的员工刘备，工资8000，性别男，年龄25
2. 教学部下 Java教学部的员工苍老师，工资100000，性别男，年龄18
- 创建员工表
    create table emp(id int primary key auto_increment,name varchar(10),sal int,gender varchar(5),age int,deptid int);
- 创建部门表
    create table dept(id int primary key auto_increment,name varchar(10),parentid int);
- 插入数据：
    insert into dept values(null,'集团总部',null);
    insert into dept values(null,'市场部',1);
    insert into dept values(null,'市场部A',2);
    insert into emp values(null,'刘备',8000,'男',25,3);
    insert into dept values(null,'教学部',1);
    insert into dept values(null,'Java教学部',4);
    insert into emp values(null,'白老师',100000,'男',18,5);
-设计表保存以下数据：
1. 保存男装分类(category)下西服分类下的商品皮尔卡丹西服，价格9800，库存98件
2. 保存家用电器分类下，电视机分类下的小米电视，价格2500，库存108件
- 创建商品表
    create table item(id int primary key auto_increment, name varchar(10),price int,num int,categoryid int);
- 创建分类表
    create table category(id int primary key auto_increment,name varchar(10),parentid int);
- 插入数据
    insert into category values(null,'男装',null),(null,'西服',1),(null,'家电',null),(null,'电视机',3);
    insert into item values(null,'皮尔卡丹',9800,98,2),(null,'小米电视',2500,108,4);

###事务
- 什么是事务：事务是数据库中执行SQL语句的最小工作单元，可以保证事务内的多条SQL语句要么全部成功，要么全部失败。
- 查看数据库自动提交的状态
    show variables like '%autocommit%';
- 关掉自动提交 0关闭  1开启 (mysql 默认是自动提交的)
    set autocommit=0; （针对的是表中数据，不是数据库定义语言，比如多建表无作用）
- 下面我们来测试转账：  
    -关掉自动提交： set autocommit=0;
    create table person(id int, name varchar(10),money int);
    insert into person values(1,'超人',500),(2,'钢铁侠',10000);
    
    1. 先让超人+2000
        update person set money=2500 where id=1;
    2. 开启另一个终端 验证 此时数据库文件中的数据为空，更不要说被改掉了。
    3. 执行提交
        commit;
        此时数据有了，而且被更改了。
    4. 让钢铁侠-2000
        update person set money=8000 where id=2;
     . 执行提交
        commit;
- 回滚 rollback; (前提是没有提交，比如数据库默认是自动提交的，不可回滚，
                    或者你关闭了自动提交，但是执行完命令，你commit后，也不可回滚)
                    
- 将内存中的修改回滚到上次提交（commit）的点
    update person set money=100 where id=1;
    rollback;
- 保存回滚点 savepoint （以下说的都是未commit状态）
     
    update person set money=100 where id=1;
    savepoint s1;
    update person set money=200 where id=1;
    savepoint s2;
    rollback to s1;
    commit;
    注意:当你commit后，回滚点将失效；

回顾： 
- 查看自动提交状态： show variables like '%autocommit%';
- 修改自动提交状态： set autocommit=0/1;
- 提交： commit;
- 回滚： rollback;
- 保存回滚点： savepoint 标识;
- 回滚到某个点: rollback to 标识;

###SQL的分类
####DDL Data Definition Language
- 数据定义语言，包括 create,alter,drop,truncate ,不支持事务
####DML Data Manipulation Language
- 数据操作语言， 包括 insert,delete,update,select(DQL),支持事务
####DQL Data Query Language
- 数据查询语言，只包括select，和事务没有关系因为并没有修改数据
####TCL Transaction Control Language
- 事务控制语言，包括 commit,rollback, savepoint,rollback to
####DCL Data Control Language
- 数据控制语言，用于处理分配用户权限相关的操作

###truncate 
- truncate table 表名;
- 删除表并且创建一个新表
- truncate、drop和delete的区别：
- delete用于删除数据，使用delete清空表时自增数值不清零 执行效率最低
- drop 用于删除表 执行效率最高
- truncate 用于删除表并创建新的空表，执行效率比delete要高，而且自增数值会清零
            可以理解成将表中所有数据全部格式化，包括自增长的主键，回到最开始创建表的那个时候；

###数据库的数据类型
####整数
- 常用整数有 int(m)和bigint(m)，m代表显示长度必须和zerofill结合使用
    create table t_int(num int(10) zerofill);
    insert into t_int values(123);
    select * from t_int;
####浮点数
- 常用浮点数double(m,d) m代表总长度 d代表小数长度   23.346 m=5 d=3 
- decimal超高精度浮点数，应用场景：涉及超高精度运算时使用

    create table t_double(num double(5,3));
    insert into t_double values(23.5678); 值为23.568
    insert into t_double values(23.5); 值为23.500
####字符串
- char(m): 固定长度  m=10 abc 占10，效率高，最大255
- varchar(m):可变长度 m=10 abc 占3，节省空间，最大65535，如果超过255建议使用text
- text(m):可变长度 最大65535
··注意：char 和varchar 都是字符串，这不是java哦，区别在一个是定长，一个是可变

####日期
- date:只能保存年月日
- time:只能保存时分秒
- datetime:保存年月日时分秒，默认值为null，最大值9999-12-31
- timestamp(时间戳距离19700101 08：00：00):保存年月日时分秒，默认值为当前系统时间,最大值2038-01-19 
    create table t_time(t1 date,t2 time,t3 datetime,t4 timestamp);
    insert into t_time values('2018-10-16',null,null,null);
    insert into t_time values
            (null,'20:06:32','2008-11-22 18:22:11',null);

配置URL：
jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8

三方SQL工具：sqlyog