1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
1、在student库里创建学生表stu1：id 学号,name 名字,age 年龄，gender 性别,phone 手机号,comr_time 入学时间
root@localhost:(none) > create database student;
root@localhost:(none) > use student;
root@localhost:student > create table stu1(id int,name varchar(10),age tinyint,gender enum('f','m'),phone char(11),come_time datetime);
2、查看表
root@localhost:student > show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu1 |
+-------------------+
show tables from+表名
3、查看表结构
root@localhost:student > desc stu1;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('f','m') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
| come_time | datetime | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4、往表里面插入数据
root@localhost:student > insert into student.stu1(id,name,age,gender,phone,come_time) values(1,'zhangsan',18,'f','12345678901',NOW());
male(男的) female(女的)
NOW()当前时间
查看数据
root@localhost:student > select * from stu1;
+------+----------+------+--------+-------------+---------------------+
| id | name | age | gender | phone | come_time |
+------+----------+------+--------+-------------+---------------------+
| 1 | zhangsan | 18 | f | 12345678901 | 2024-08-13 03:25:18 |
+------+----------+------+--------+-------------+---------------------+
root@localhost:student > create table stu2(
id int primary key auto_increment comment '学生学号',
name varchar(10) not null comment '学生姓名',
age tinyint unsigned not null comment '学生年龄',
gender enum('f','m') not null default 'm' comment '学生性别',
phone char(11) not null unique key comment '学手机号',
come_time datetime not null default NOW() comment '入学时间');
解释：
create table stu2(
id int primary key auto_increment comment '学生学号',
name varchar(10) not null comment '学生姓名',
age tinyint unsigned not null comment '学生年龄',
gender enum('f','m') not null default 'm' comment '学生性别',
phone char(11) not null unique key comment '学生手机号',
come_time datetime not null default NOW() comment '入学时间');
root@localhost:student > desc stu2;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('f','m') | NO | | m | |
| phone | char(11) | NO | UNI | NULL | |
| come_time | datetime | NO | | CURRENT_TIMESTAMP | |
+-----------+---------------------+------+-----+-------------------+----------------+
root@localhost:student > insert into student.stu2(name,age,phone) values('lisi',20,'12312312312');
root@localhost:student > insert into student.stu2(name,age,phone) values('wangwu',22,'12312312355');
root@localhost:student > select * from stu2;
+----+--------+-----+--------+-------------+---------------------+
| id | name | age | gender | phone | come_time |
+----+--------+-----+--------+-------------+---------------------+
| 1 | lisi | 20 | m | 12312312312 | 2024-08-13 04:19:56 |
| 2 | wangwu | 22 | m | 12312312355 | 2024-08-13 04:21:46 |
+----+--------+-----+--------+-------------+---------------------+
查看注释，建表语句里面有
root@localhost:student > show create table stu2;
| stu2 | CREATE TABLE `stu2` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
`name` varchar(10) NOT NULL COMMENT '学生姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('f','m') NOT NULL DEFAULT 'm' COMMENT '学生性别',
`phone` char(11) NOT NULL COMMENT '学手机号',
`come_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`),
UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
执行失败的语句也会自增，能不能插进去就是你的事，但是不插到表里面，生产中不会出现连续的自增，用户想删自己的数据就删，如果想要在生产中连号，就非常累，所以不需要在乎id号不连续
如果把表里面的数据全部删除，再插入数据，还是之前的序号记录还是在，序号不会从1开始
|
