Mysql怎么创造数据表
发布时间：2022-01-16 01:21:51 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍Mysql怎么创建数据表，在日常操作中，相信很多人在Mysql怎么创建数据表问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答Mysql怎么创建数据表的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ C:Usersad
|
这篇文章主要介绍“Mysql怎么创建数据表”，在日常操作中，相信很多人在Mysql怎么创建数据表问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql怎么创建数据表”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ C:Usersadmin>mysql -h localhost -u root -pmysql mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.14 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> use mydata Database changed mysql> create table mydata1( -> id int, -> name varchar(20), -> sex boolean -> ); Query OK, 0 rows affected (0.36 sec) mysql> desc mydata1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) mysql> show tables; +------------------+ | Tables_in_mydata | +------------------+ | mydata1 | +------------------+ 1 row in set (0.00 sec) 5.1完整性约束条件 Primary key 主键，标识唯一 Foreign key 标识该属性为该表的外键，联系表的主键 Not null 属性不能为空 Unique 属性的值是唯一的 Auto_increment 值自动增加,mysql的sql语句的特色 Default 列设置默认值 5.2 主键 单字段主键和多字段主键 mysql> create table mydata2( -> id int primary key, #单一字段主键 -> name varchar(20), -> sex boolean); Query OK, 0 rows affected (0.23 sec) mysql> show tables; +------------------+ | Tables_in_mydata | +------------------+ | mydata1 | | mydata2 | +------------------+ 2 rows in set (0.00 sec) mysql> desc mydata2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table mydata2 drop primary key; Query OK, 0 rows affected (0.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc mydata2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table mydata2 add primary key(id,name); #设置多字段主键 Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc mydata2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | PRI | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 也可以在create table 定义中定义primary key mysql> create table mydata3( -> id int, -> name varchar(20), -> sex boolean, -> primary key(id,name) -> ); Query OK, 0 rows affected (0.24 sec) mysql> desc mydata3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | PRI | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 5.3 外键 foreign key mysql> create table mydata4( -> id int primary key, -> name varchar(30), -> sex boolean, -> constraint my_fk foreign key(id) references mydata3(id) -> ); Query OK, 0 rows affected (0.26 sec) mysql> desc mydata4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 5.4 not null 非空 mysql> create table mydata5( -> id int primary key, -> name varchar(20) not null); Query OK, 0 rows affected (0.28 sec) mysql> desc mydata5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 5.5 unique 唯一性 mysql> create table mydata6( -> id int primary key, -> name varchar(20) unique); Query OK, 0 rows affected (0.35 sec) mysql> desc mydata6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 5.6 auto_increment 必须为主键的一部分 mysql> create table mydata7( -> id int primary key auto_increment, -> name varchar(20)) -> ; Query OK, 0 rows affected (0.24 sec) mysql> desc mydata7; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) 5.7 默认值 mysql> create table mydata8( -> id int primary key auto_increment, -> name varchar(20) unique, -> address varchar(100) not null, -> city varchar(20) default 'suzhou', -> socre float default 0); Query OK, 0 rows affected (0.35 sec) mysql> desc mydata8; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | UNI | NULL | | | address | varchar(100) | NO | | NULL | | | city | varchar(20) | YES | | suzhou | | | socre | float | YES | | 0 | | +---------+--------------+------+-----+---------+----------------+ 5 rows in set (0.04 sec) 5.8 查看表结构 mysql> show create table mydata1 G; *************************** 1. row *************************** Table: mydata1 Create Table: CREATE TABLE `mydata1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` tinyint(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified mysql> desc mydata1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 5.9 修改表结构 mysql> alter table mydata1 rename to mydata; #修改表名 Query OK, 0 rows affected (0.23 sec) mysql> alter table mydata1 modify sex varchar(1); #修改列属性 Query OK, 0 rows affected (0.77 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 change city address varchar(20); mysql> alter table mydata1 change sex city int; #修改列名和属性 Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 add city int; #添加列名 Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 add sal int after address; #在address栏位后面加列 Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 add uid int first; #加列为首列 Query OK, 0 rows affected (0.45 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 drop city; #删除列 Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 modify sal int after name; #修改列的位置 Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table mydata1 modify id int first; #修改为首列 Query OK, 0 rows affected (0.54 sec) Records: 0 Duplicates: 0 Warnings: 0 CHANGE 对列进行重命名或更改列的类型，需给定旧的列名称和新的列名称、当前的类型 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称） mysql> alter table mydata1 engine=myisam; #修改表的存储引擎 Query OK, 0 rows affected (1.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop table mydata8; #删除表 Query OK, 0 rows affected (0.22 sec) 到此，关于“Mysql怎么创建数据表”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！  （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
