這篇文章主要介紹“怎么創(chuàng )建數據表”，在日常操作中，相信很多人在Mysql怎么創(chuàng )建數據表問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”Mysql怎么創(chuàng )建數據表”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！
C:\Users\admin>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完整性約束條件
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 對列進(jìn)行重命名或更改列的類(lèi)型，需給定舊的列名稱(chēng)和新的列名稱(chēng)、當前的類(lèi)型 MODIFY 可以改變列的類(lèi)型，此時(shí)不需要重命名（不需給定新的列名稱(chēng)）
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)
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
