Mysql_Note1

1.一个完整的数据存储过程

创建数据库 ->确认字段 ->创建数据表->插入数据

1.1 创建数据库

名称	作用	补充
information_schema	存储数据库服务系统信息	数据库的名称,数据表的名称,字段名称,存取权限,数据文件所在的文件夹和系统使用的文件夹
mysql	数据库服务器运行时需要的系统信息	数据文件夹、当前使用的字符集、约束检查信息
performance_schema	监控 MySQL 的各类性能指标。
sys	以一种更容易被理解的方式展示数据库服务器的各类性能指标

1.2 确认字段

1.3创建数据表

--创建数据表
CREATE TABLE demo.test
( 
  barcode text,
  goodsname text,
  price int
); 
--查看表结构
describe demo.test
--mysql中空值不等于空字符串,空字符串长度为0,空值得长度为空且占用空间

--选择数据库
use demo;
--查看数据库中所有的表
show tables


--建议一定要给表定义主键,并且养成习惯。因为主键可以帮助你减少错误数据，并且提高查询的速度
-- 必须唯一
-- 不能重复 
-- 不能是空；必须可以唯一标识数据表中的记录
alter table demo.test add column itemnumber int primary key auto_increment;

-- 向表中添加数据
insert into demo.test (barcode,goodsname,price) values ('001',"本",3)

1.4思考题

1.请你思考一下，假设用户现在要销售商品，你能不能帮它设计一个销售表，把销售信息（商品名称、价格、数量、金额等）都保存起来

-- 设计销售表如下：
 
CREATE TABLE demo.sales
(
         goodsname text,
         salesprice decimal(10,2),
         quantity decimal(10,3),
         salesvalue decimal(10,2)
);

2.select count(*) from t; t中有id(主键)，name，age,sex4个字段。假设数据10条，对sex添加索引。用explain 查看执行计划发现用了sex索引，为什么不是主键索引呢?主键索引应该更快的

1. MySQL Innodb的主键索引是一个B+树，数据存储在叶子节点上，10条数据，就有10个叶子节点。
2. sex索引是辅助索引，也是一个B+树，不同之处在于，叶子节点存储的是主键值，由于sex只有2个可能的值：男和女，因此，这个B+树只有2个叶子节点，比主键索引的B+树小的多
3. 这个表有主键，因此不存在所有字段都为空的记录，所以COUNT(*)只要统计所有主键的值就可以了，不需要回表读取数据
4. SELECT COUNT(*) FROM t，使用sex索引，只需要访问辅助索引的小B+树，而使用主键索引，要访问主键索引的那个大B+树，明细工作量大，这就是为什么，优化器使用辅助索引的原因

2.字段类型

字段类型选择原则

1.确保数据不会超过取值范围

2.再考虑存储空间,可靠性的平衡问题

2.1整数类型

TINYINT,SMALLINT,MEDIUMINT,INT（INTEGER）和 BIGINT

INT占用4个字节

2.2浮点类型和定点类型

1.float单精度占用4个字节,double多精度占用8个字节

2.浮点数是不精确的,不适合精确度要求高的场景

3.REAL 默认就是 DOUBLE,SET sql_mode = “REAL_AS_FLOAT”;将sql_mode设置为float

4.float和double浮点类型是把十进制转换成二进制,decimal定点数类型是把十进制转换成二进制

2.3文本类型

字段名	含义
char	固定字符串
varchar(M)	可变长字符串
text	按照实际长度存储的字符串,且该字段类型不能作为主键
enum	枚举类型
set	字符串对象

text占65535字节

2.4日期与时间类型

实际项目中,建议使用datetime类型,占用5个字节

2.5总结

1.整数就用int,小数就用decimal,字符串只要不是主键就用text,时间就用datetime

2.修改字段类型

alter table demo.goodmaster modify column price double

2.5思考题

假设用户需要一个表来记录会员信息，会员信息包括会员卡编号、会员名称、会员电话、积分值。如果要你为这些字段定义数据类型，你会如何选择呢？为什么？

create table demo (
memberid int primary key,
merbername text ,
merberphone text ,
merberpoints text    
)

3.怎样创建和修改数据表

3.1如何创建数据表

create table 表名(
	字段名1 数据类型 [字段级别约束] [默认值],
    字段名1 数据类型 [字段级别约束] [默认值]
    ...
    [表级约束]
)

1.字段名需要避免系统关键字

3.2都有哪些约束

主键约束,外键约束,默认约束,非空约束,唯一性约束和自增约束

1.满足主键约束的字段,自动满足非空约束,但是满足唯一性约束的字段,则可以是空值

2.自增约束,只有整形字段才能定义自增约束, 自动以自增约束字段的最大值加 1 为新的字段值, 且不会重复

3.3总结

CREATE TABLE
(
字段名 字段类型 PRIMARY KEY
);
CREATE TABLE
(
字段名 字段类型 NOT NULL
);
CREATE TABLE
(
字段名 字段类型 UNIQUE
);
CREATE TABLE
(
字段名 字段类型 DEFAULT 值
);
-- 这里要注意自增类型的条件，字段类型必须是整数类型。
CREATE TABLE
(
字段名 字段类型 AUTO_INCREMENT
);
-- 在一个已经存在的表基础上，创建一个新表
CREATE TABLE demo.importheadhist LIKE demo.importhead;
-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;

1.指定表的存储引擎,alter table 表名 engine=innodb

2.指定存储文件自增空间大小 auto_extendsize

3.4思考题

1. 请你写一个 SQL 语句,将表 demo.goodsmaster 中的字段“salesprice”改成不能重复，并且不能为空

   alter table demo.goodsmaster modify salesprice  DECIMAL(10,2)  unique not null