mysql创建表
程序员文章站
2022-06-01 17:29:46
...
temporary创建临时表 使用关键字temporary可以创建临时表。临时表只能对创建它的用户课件。临时表可以和持久表有同样的表明,此时临时表会隐藏持久表。 例如: 原始表数据: mysql select * from student;+--------+----------+---------+-----------+| stu_
temporary创建临时表
使用关键字temporary可以创建临时表。临时表只能对创建它的用户课件。临时表可以和持久表有同样的表明,此时临时表会隐藏持久表。例如:
原始表数据:
mysql> select * from student; +--------+----------+---------+-----------+ | stu_id | stu_name | stu_tel | stu_score | +--------+----------+---------+-----------+ | 1 | a | 151 | 60 | | 2 | b | 152 | 61 | | 3 | c | 153 | 62 | | 4 | d | 154 | 63 | +--------+----------+---------+-----------+ 4 rows in set (0.00 sec)创建临时表:
mysql> create temporary table student( -> stu_id int, -> stu_name varchar(5), -> stu_tel int(5), -> stu_score int(2) -> ); Query OK, 0 rows affected (0.14 sec)
查询表student:
mysql> select * from student; Empty set (0.00 sec)没有任何数据,是因为临时表隐藏了永久表。
if not esists 判断表存在
如果表已经存在时不能再创建永久表。可通过if not exists 判断是否存在
也可以通过if exists 判断并删除一个表。
create table if not exists student( stu_id int, stu_name varchar(5), stu_tel int(5), stu_score int(2) ); drop table if exists student;
复制表
create table student1 like student;
复制表结构的同时复制数据,并更改列名
mysql> create table student1 as -> (select stu_id as id,stu_name as name,stu_tel as tel,stu_score as score -> from student); Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student1; +----+------+------+-------+ | id | name | tel | score | +----+------+------+-------+ | 1 | a | 151 | 60 | | 2 | b | 152 | 61 | | 3 | c | 153 | 62 | | 4 | d | 154 | 63 | +----+------+------+-------+ 4 rows in set (0.00 sec)复制表时可以给新表设置新的约束
例如将新表的id设置为主键。
mysql> create table student1(stu_id int primary key) as (select * from student); Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0列名表中没有说明的行默认使用原表中的约束。
其他参数:
auto_increment为新添加的行自动递增的分配一个id,也可设置递增分配id的起始id
default 为列设定默认值。
comment 为列添加说明
例:
create table student( stu_id int primary key auto_increment comment '学生ID', stu_name varchar(5) comment '学生姓名', stu_tel int(5) comment '学生电话', stu_score int(2) default 60 comment '学生成绩' )auto_increment = 100;
mysql> insert into student values(NULL,'a',150,default); Query OK, 1 row affected (0.03 sec)
mysql> select * from student; +--------+----------+---------+-----------+ | stu_id | stu_name | stu_tel | stu_score | +--------+----------+---------+-----------+ | 100 | a | 150 | 60 | +--------+----------+---------+-----------+ 1 row in set (0.00 sec)
mysql> select column_name,column_comment -> from information_schema.columns -> where table_name = 'student'; +-------------+----------------+ | column_name | column_comment | +-------------+----------------+ | stu_id | 学生ID | | stu_name | 学生姓名 | | stu_tel | 学生电话 | | stu_score | 学生成绩 | +-------------+----------------+ 4 rows in set, 0 warnings (0.33 sec)
下一篇: 详解在Python中执行系统命令的方法