MySQL 之DDL

MySQL 之DDL

#进入数据库创建表

MariaDB [test]> CREATE TABLE student(id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, gender ENUM('M','F') default 'M' )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.003 sec) --------------------------------------------------------- # int UNSIGNED 正整数 # AUTO_INCREMENT 自动增长 # PRIMARY KEY 主键 ---------------------------------------------- # VARCHAR(20) 可变的字串，最多20个 ----------------------------------------- # tinyint UNSIGNED 一个字节的正整数 ----------------------------------------- #ENUM('M','F') default 'M' 性别是2种类型，默认是M ------------------------------------------- MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ #在数据库里面创建表的时候，文件夹里面就相当于数据库，文件相当于表 [root@c7-147 ~]# ll /var/lib/mysql/test/ total 304 -rw-rw----. 1 mysql mysql 65 Jun 10 05:43 db.opt -rw-rw---- 1 mysql mysql 987 Aug 29 01:38 employee.frm -rw-rw---- 1 mysql mysql 98304 Aug 29 01:38 employee.ibd -rw-rw---- 1 mysql mysql 1058 Aug 29 02:02 student.frm #相当于表结构，不会变大 -rw-rw---- 1 mysql mysql 98304 Aug 29 02:02 student.ibd #相当于表里面的数据，会不断的变大 -rw-rw---- 1 mysql mysql 948 Aug 29 01:49 testdate.frm -rw-rw---- 1 mysql mysql 98304 Aug 29 01:49 testdate.ibd #在表里面添加内容：字符串要加引号，xiaoming是字符串所以要加引号 MariaDB [test]> insert student (name,age,gender)values('xiaoming',20,'M'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student -> ; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | +----+----------+------+--------+ 1 row in set (0.000 sec)

#复合主键 单独放一行写

MariaDB [test]> CREATE TABLE employee_01 (id int UNSIGNED NOT NULL , name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, PRIMARY KEY(id,name)); Query OK, 0 rows affected (0.005 sec)

#

MariaDB [test]> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.000 sec)\ #修改起始值和自动增长的值 MariaDB [test]> SET @@auto_increment_increment=10; #起始值为10 Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SET @@auto_increment_offset=3; #步长为3 Query OK, 0 rows affected (0.000 sec) MariaDB [test]> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 3 | +--------------------------+-------+ MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | +----+----------+------+--------+ 1 row in set (0.000 sec) MariaDB [test]> insert student (name,age,gender) value ('xiaohong',20,'F'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | +----+----------+------+--------+ 2 rows in set (0.000 sec) MariaDB [test]> insert student (name,age,gender) value ('A',20,'F'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | +----+----------+------+--------+ 3 rows in set (0.000 sec) MariaDB [test]> insert student (name,age,gender) value ('B',20,'F'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | +----+----------+------+--------+ 4 rows in set (0.000 sec) ----------------------------------------------------- #创建一张新表的，即使是空也不会为空，因为表结构定义了 MariaDB [test]> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.003 sec) MariaDB [test]> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.001 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 3 | | 13 | | 23 | | 33 | +-----+

#时间戳

#主键虽然默认为空，但是也不为空 MariaDB [test]> create table testdate_01 (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL); Query OK, 0 rows affected (0.002 sec) MariaDB [test]> desc testdate_01; +-------+-----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | timestamp | NO | | current_timestamp() | | +-------+-----------+------+-----+---------------------+----------------+ #添加时间戳 MariaDB [test]> insert testdate_01 (id) value (1); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from testdate_01; +----+---------------------+ | id | date | +----+---------------------+ | 1 | 2021-08-29 03:00:38 | +----+---------------------+ 1 row in set (0.000 sec) MariaDB [test]> insert testdate_01 (id) value (2); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from testdate_01; +----+---------------------+ | id | date | +----+---------------------+ | 1 | 2021-08-29 03:00:38 | | 2 | 2021-08-29 03:01:10 | +----+---------------------+

#复制前一张表的结果

MariaDB [test]> create table testdate_02 select * from testdate_01; Query OK, 2 rows affected (0.003 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from testdate_02; +----+---------------------+ | id | date | +----+---------------------+ | 1 | 2021-08-29 03:00:38 | | 2 | 2021-08-29 03:01:10 | +----+---------------------+ 2 rows in set (0.000 sec) #创建表的方法二：数据一样但是表结构还是发生了变化的 MariaDB [test]> desc testdate_01; +-------+-----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | timestamp | NO | | current_timestamp() | | +-------+-----------+------+-----+---------------------+----------------+ 2 rows in set (0.000 sec) MariaDB [test]> desc testdate_02; +-------+-----------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+-------+ | id | int(11) | NO | | 0 | | | date | timestamp | NO | | current_timestamp() | | +-------+-----------+------+-----+---------------------+-------+ 2 rows in set (0.000 sec)

#创建表的方法三：只是复制表结构，表结构一样但是无内容

MariaDB [test]> create table testdate_03 like testdate_01; Query OK, 0 rows affected (0.004 sec) MariaDB [test]> desc testdate_03; +-------+-----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | timestamp | NO | | current_timestamp() | | +-------+-----------+------+-----+---------------------+----------------+ MariaDB [test]> desc testdate_01; +-------+-----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | timestamp | NO | | current_timestamp() | | +-------+-----------+------+-----+---------------------+----------------+ 2 rows in set (0.001 sec) 2 rows in set (0.001 sec) MariaDB [test]> select * from testdate_03; Empty set (0.000 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。