mysql 插入表 始终插入到第一排

3.1表的存储方式

• 存储方式一: MyISAM 5.5及以下默认存储方式

• 存储文件个数:表结构表中的数据,索引
• 适合做读,插入数据比较频繁的,对修改和删除涉及较少,索引和数据分开存储
• 支持表级锁
• 不支持行级锁 不支持事务 不支持外键

• 存储方式二: innoDB 5.6及以上默认存储方式

• 存储文件个数 : 表结构,表中的数据
• 适合并发较高,对事物一致性要求较高,行队更适应频繁的删除和修改操作,索引和数据存在一起
• 支持行级锁和表级锁
• 支持事务
• 支持外键

• 存储方式三: MEMORY内存

• 存储文件个数:表结构
• 优势 : 增删改查都很快(用于热点新闻)
• 数据存在内存中,表结构存在硬盘上,查询速度快
• 劣势 : 重启数据消失,容量有限

3.2表的创建

表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

3.2.1建表语句

#语法:
mysql>create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件],
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

• 例一: mysql>create table t1 (id int,name char(4));
• 例二: mysql>create table t3 (id int,name char(4)) engine=myisam;

3.2.2.查看表结构

• mysql>desc 表名 查看表字段的基础信息

• mysql>describe 表名;

• mysql>show create table 表名; 能够看到和这张表相关的所有信息
• mysql>show create table 表名 \G 能显示较为整洁

• mysql>show create table 表名 \G; 能显示较为整洁(加分号会报错)

• 使用分号或者\G

证明使用memory存储退出数据库后登录会清空.

3.3字段的数据类型

3.3.1数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

1.整数

• 总结

• int默认是有符号的
• 它能表示的数字范围不被宽度约束
• 他只能约束数字的显示宽度

#默认创建(有符号)
mysql>create table t4 (id1 int,id2 int);
#创建一个无符号的int数据
mysql>create table t5 (id1 int unsigned,id2 int);

2.小数

# create table t6 (f1 float(5,2),d1 double(5,2));   会四舍五入
# create table t7 (f1 float,d1 double);


create table t8 (d1 decimal,d2 decimal(25,20));   
#非常准确,能表示小数点后30位,后面不指定位数时默认保留整数

3.3.2时间类型

1.date / time / datetime

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

2.timestemp

#示例一:
mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

# 插入数据null，会自动插入当前时间的时间
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+
| id1                 |
+---------------------+
| 2018-09-21 14:56:50 |
+---------------------+
1 row in set (0.00 sec)

#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

# 手动修改新的列默认值为当前时间
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

#示例二
mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01 sec)

mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+---------------------+
| t1                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
# timestamp时间的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql>

3.year

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+------+
| y    |
+------+
| 2018 |
+------+
1 row in set (0.00 sec)

4.时间的写入

写入当前时间

mysql> create table tt1(y year,d date,dt datetime,ts timestamp);
Query OK, 0 rows affected (0.96 sec)

mysql> insert into tt1 values(now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.21 sec)

mysql> select * from tt1;
+------+------------+---------------------+---------------------+
| y    | d          | dt                  | ts                  |
+------+------------+---------------------+---------------------+
| 2019 | 2019-09-28 | 2019-09-28 16:48:49 | 2019-09-28 16:48:49 |
+------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into tt1 (y) values (2018);
Query OK, 1 row affected (0.14 sec)

mysql> select * from tt1;
+------+------------+---------------------+---------------------+
| y    | d          | dt                  | ts                  |
+------+------------+---------------------+---------------------+
| 2019 | 2019-09-28 | 2019-09-28 16:48:49 | 2019-09-28 16:48:49 |
| 2018 | NULL       | NULL                | 2019-09-28 16:50:38 |
+------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> show create table tt1;
+-------+----------------------------------------------------------------------------------
| Table | Create Table
--------------------------------------------------------+
| tt1   | CREATE TABLE `tt1` (
  `y` year(4) DEFAULT NULL,
  `d` date DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------
create table t9(
    y year,d date,
    dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ts timestamp
);

写入任意时间

#数字型写入
mysql> insert into tt1 values(2019,20180701,20170701121212,20160102030405);
Query OK, 1 row affected (0.10 sec)

#字符串型写入
mysql> insert into tt1 values(2019,20180701,'2017-07-01 12:12:12','2016-01-02 03:04:05');
Query OK, 1 row affected (0.11 sec)

mysql> select * from tt1;
+------+------------+---------------------+---------------------+
| y    | d          | dt                  | ts                  |
+------+------------+---------------------+---------------------+
| 2019 | 2018-07-01 | 2017-07-01 12:12:12 | 2016-01-02 03:04:05 |
| 2019 | 2018-07-01 | 2017-07-01 12:12:12 | 2016-01-02 03:04:05 |
+------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

另外这种方式也可以写入:

3.3.3字符串类型

• char 定长的单位

• 存储时 :alex -----> alex ,

• varchar 变长的单位

• 存储时:alex ------> alex4

• 哪一种存储方式好?

• vachar :节省空间,存储效率相对低
• char :浪费空间,存储效率相对高,长度变化小的

#手机号码,身份证号,用户名,密码   char  *****
#评论,微博,说说,微信状态        varchar

create table t11 (name1 char(5),name2 varchar(5));

3.3.4ENUM和SET类型

#ENUM  枚举
#set   多选,自动去重

create table tt2(
name char(12),
gender ENUM('male','female'),
hobby set('抽烟','喝酒','烫头','洗脚')    
);

3.3.5对象

blob   二进制大对象(存储图片,视频等媒体流信息)
clob   字符大对象(存储较大文本)

3.4约束

3.4.1 unsigned

• 设置某一个数字无符号

3.4.2 not null

• 某一个字段不能为空

create table t1(
    id int not null,
    name char(12) not null,
    age int
);

　　　

• not null 不生效

#not null不生效：
    不支持对not null字段插入null值
    不支持对自增长字段插入"”值
    不支持text字段有默认值

#直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

#配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

3.4.3default

• 给某个字段设置默认值
• 我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
• 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

#创建表
create table t2(
    id int not null,
    name char(12) not null,
    age int not null,
    gender enum('male','female') not null default 'male'
);

#插入数据
insert into t2(id,name,age) values(1,'张三','20');

3.4.4 unique

• 设置某一个字段不能重复

#创建表
create table t3(
 id int unique,
 username char(12) unique,
 password char(18)
);

#插入数据
insert into t3 values(1,'张三','12345');

联合唯一

#创建表
create table t4(
 id int,
 ip char(15),
 server char(10),
 port int,
 unique(ip,port)
);

#插入数据
insert into t4 values(1,"192.168.12.11","mysql",3306);

3.4.5 auto_increment

• 设置某一个int类型的字段,自动增加
• 前提 :自增字段必须是 数字 且 必须唯一 ,自带非空属性

#

创建表
create table t5(
    id int unique auto_increment,
    name char(12)
);

#插入数据
insert into t5 values(1,'张三');

3.4.6 primary

• 主键 :约束这个字段 非空(not null)且唯一(unique),的字段,你指定的第一个非空且唯一自动定义为主键
• 一张表只能设置一个主键且最好设置一个主键

单字段主键

#方法一：not null+unique
create table department1(
    id int not null unique, #主键
    name varchar(20) not null unique,
    comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一个字段后用primary key
create table department2(
    id int primary key, #主键
    name varchar(20),
    comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key
create table department3(
    id int,
    name varchar(20),
    comment varchar(100),
    primary key(id) #创建主键并为其命名pk_name
);

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

# 方法四：给已经建成的表添加主键约束
mysql> create table department4(
    -> id int,
    -> name varchar(20),
    -> comment varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table department4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

多字段主键

create table service(
    ip varchar(15),
    port char(5),
    service_name varchar(10) not null,
    primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

联合主键(不常用)

3.4.7foreign key

#创建表

#部门表
create table post(
    pid int primary key,
    postname char(10) not null unique,
    comment varchar(255),
    phone_num char(11)
);   

#员工表
create table staff(
    id int primary key auto_increment,
    age int,
    gender enum('male','female'),
    salary float(8,2),
    hire_date date,
    post_id int,
    foreign key(post_id) references post(pid)
);
#注:外键关联的那张表的字段必须为unique

#插入数据
insert into post values(1,'java部','一个专职于java的部门','1234567');
insert into staff values(1,20,'male',10000,'20181201030405',1);

• cascade方式 :在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null
• Set default方式 :父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

create table staff2(
    id  int primary key auto_increment,
    age int,
    gender  enum('male','female'),
    salary  float(8,2),
    hire_date date,
    post_id int,
    foreign key(post_id) references post(pid) on update cascade on delete set null
);

3.5 表结构的修改

• 修改表名

• alter table 表名 rename 新表名;

• 增加字段

• alter table 表名 add 字段名 数据类型 [完整性约束条件];

• 删除字段

• alter table 表名 drop 字段名;

• 修改字段

• alter table 表名 modify 字段名 数据类型 [完整性的约束条件];
  修改已经存在的字段的类型,宽度和约束
• alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
  修改已经存在的字段的类型,宽度,约束 及字段名字

• 修改字段排列顺序/在增加的时候指定字段位置
  例 : 原字段顺序 id name age

• alter table 表名 modify age int not null after id
  将age 字段放在name字段之前
• alter table 表名 modify age int not null first

3.6 表与表之间的关系

3.6.1一对多

• foreign key 永远是在多的那张表中设置外键
• 多个学生都是一个班级的
• 学生表 关联 班级表
• 学生是多 ,班级是一

3.6.2一对一

• foreign key +unique 后出现的的那张表中的数据作为外键(并且约束这个外键唯一)
• 客户关系表 :手机号码,招生老师,上次联系时间,备注信息
• 学生表 :姓名,入学日期,缴费日期,结业时间

3.6.3多对多

• 产生第三张表,把两个关联关系的字段作为第三张表的外键
• 书
• 作者