全网最详细的MySQL笔记（二）

学习了上一节《Mysql笔记一》相信小伙伴们对mysql的一些基本概念，对数据库、表、数据的操作有了一定的了解。现在我们先回顾一下。

练习:

1. 创建db2数据库
   create database db2;
2. 在db2中创建员工表(emp) 字段:员工编号(empno),员工姓名(ename)
   use db2;
   create table emp(empno int, ename varchar(10));
3. 修改表名为t_emp
   rename table emp to t_emp;
4. 在表中添加工资字段(sal)
   alter table t_emp add sal int;
5. 在员工姓名的前面添加年龄字段(age)
   alter table t_emp add age int after empno;
6. 在员工姓名后面添加部门字段(dept varchar(10))
   alter table t_emp add dept varchar(10) after ename;
7. 添加刘关张和取经的四个人 共7个人,年龄随意,工资5000以内随意,刘关张为三国部 取经师徒四个人为取经部
   insert into t_emp values
   (1,20,‘刘备’,‘三国部’,2000),
   (2,21,‘关羽’,‘三国部’,3000),
   (3,22,‘张飞’,‘三国部’,4000),
   (4,23,‘唐僧’,‘取经部’,1000),
   (5,24,‘悟空’,‘取经部’,2000),
   (6,25,‘八戒’,‘取经部’,5000),
   (7,26,‘沙僧’,‘取经部’,4000);
8. 把取经部的年龄改成统一的1000岁
   update t_emp set age=1000 where dept=‘取经部’;
9. 删除工资低于2000的
   delete from t_emp where sal<2000;
10. 查询三国部的所有人的名字
    select ename from t_emp where dept=‘三国部’;

正式进入学习

1. 主键

• 什么是约束:就是创建表的时候给表字段添加的限制条件
• 主键约束的特点: 唯一并且非空,一个表中只有一个主键
• 如何使用:
  create table t1(id int primary key,name varchar(10));
  • 以下两行会报错,因为id重复了
    insert into t1 values(1,‘小明’);
    insert into t1 values(1,‘小红’);
  • 以下会报错 因为主键不能为null
    insert into t1 (name) values(‘小花’);
• 主键+自增
  primary key auto_increment
  • 如何使用:
    create table t2(id int primary key auto_increment,age int);
  1. 当自增字段的值为null时会自动赋值并自增
  2. 以表中曾出现的最大值+1
  3. 删除数据自增数值不减
  4. delete删除所有数据 自增值在原来基础上继续+1

2. 注释 comment

• 在创建表的时候可以通过comment对字段进行描述
  create table t3(id int primary key auto_increment comment ‘这是主键id’,comm int comment ‘这是奖金’);
• 如何查看注释
  show create table t3;

3. `和’的区别

• 是在创建表时 修饰表名和字段的名的 可以省略 create tablet4(id int,age` int);
• '是用来表示字符串的

4. 数据冗余

• 如果数据库设计不合理,保存大量数据后会出现大量的重复数据,这种现象称为数据的冗余 ,通过拆分表格的形式,把可能大量重复的数据,用单独一张表保存,在原表中只需要通过id建立关系即可。

5. 事务

• 事务是数据库中执行sql语句的最小工作单元,在同一个事务中的sql语句要么同时成功,要么同时失败.
  例子：

    create table person(id int,name varchar(10),money int);
    insert into person values(1,'超人',500),(2,'钢铁侠',1000);
    -超人和钢铁侠借300块钱  
    1. 超人+300
    	update person set money=800 where id=1;
    2. 钢铁侠-300
    	update person set money=700 where id=2;
  

• mysql数据库默认sql语句是自动提交的

• 关闭数据库的自动提交

  • 查看自动提交的状态:
    show variables like ‘%autocommit%’;
  • 关闭自动提交 如果需要打开自动提交把0换成1
    set autocommit=0;
  • 验证转账流程:
    update person set money=800 where id=1;
  • 此时打开一个新的终端,在新终端中查看是否修改(没改)
  • 回到原窗口执行下面sql commit之后再去新窗口验证(改了)
    update person set money=700 where id=2;
    commit;
  • 回滚 rollback
    执行rollback会回滚到上次提交的点或者关闭自动提交时的点
  • 保存回滚点 savepoint s1(标识);
    update person set money=100 where id=1;
    savepoint s1;
    update person set money=200 where id=1;
    rollback to s1;

6. SQL 分类

• DDL（Data Definition Language） 数据定义语言: create drop alter truncate 不支持事务
• DML（Data Manipulation Language） 数据操作语言:insert update delete select 支持事务
• DQL（Data Query Language） 数据查询语言:Select
• TCL（Transaction Control Language） 事务控制语言: commit rollback savepoint等
• DCL（Data Control Language） 数据控制语言:分配用户权限

7. 数据库数据类型

7.1整型

• 常用:int(m) bigint(m) m代表显示长度,如果字段数值长度不到m时 会在数值的前面补零,但是一定要和 zerofill结合使用
  例如: create table t_int(num int(10) zerofill);
  insert into t_int values(15);
  select * from t_int;

7.2浮点数

• 常用:double(m,d) m代表总长度,d代表小数长度 如:76.232 m=5 d=3;
• decimal(m,d) 超高精度小数,需要涉及高精度运算时使用decimal

7.3字符串

• char(m): 长度不可变 m=20 abc 20 执行效率高 最大值255
• varchar(m):长度可变 m=20 abc 3 节省资源 最大值:65535但是超过255建议使用text
• text: 可变长度,最大65535

7.4日期类型

• date: 只能保存年月日
• time: 只能保存时分秒
• datatime:年月日时分秒 9999-12-31 默认值为null
• timestamp:年月日时分秒 2038-01-19 默认值为当前时间

练习:创建时间的表
create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values(‘2018-03-22’,null,null,null);
insert into t_date values(‘2018-03-22’,‘12:22:38’,‘2008-08-08 18:08:18’,null);

补充

truncate

• 格式:truncate table 表名;
• 作用:删除表并创建一张空表,auto_increment数值清零

牛刀小试

1. 创建商品表(item) 商品id 商品名称 商品价格 分类id 库存
   create table item(id int primary key auto_increment,name varchar(10),price int,categoryid int,num int);
2. 创建分类表(category) 分类id 分类的名称 上级分类
   create table category(id int primary key auto_increment,name varchar(10),parentid int);
3. 表中插入 电器分类下电视机分类下的康佳电视价格3580,库存25.
   • 分类表插入以下数据
     insert into category values(null,‘电器’,null);
     insert into category values(null,‘电视机’,1);
   • 商品表插入以下数据
     insert into item values(null,‘康佳电视’,3580,2,25);

总结

1. 主键 自增
   primary key auto_increment
2. 注释
   comment
3. `和’
4. 数据冗余
5. 事务:执行sql语句的最小单元
   show variables like ‘%autocommit%’;
   set autocommit=0/1;
   rollback
   commit
   savepoint s1;
   rollback to s1;
6. 数据库sql分类
   • DDL 数据定义语言 不支持事务 create drop alter truncate
   • DML 数据操作语言 支持事务 insert update delete select
   • DQL 数据查询语言 select
   • TCL 事务控制语言
   • DCL 数据控制语言 分配用户权限
7. 数据类型
   -整型 int bigint(m) zerofill
   -浮点型 double(m,d) decimal(m,d)
   -字符串 char(m)不可变 255 varchar可变 65535 text 可变 65535 longtext
   -日期
   date 年月日
   time 时分秒
   datetime null 9999-12-31
   timestamp 当前值 2038-01-19