当前位置：首页 > article >正文

SQL自学，mysql从入门到精通 --- 第 14天，主键、外键的使用

article 2025/2/10 20:58:29

1.主键

PRIMARY KEY 主键的使用

字段值不允许重复，且不允许赋NULL值

创建主键

root@mysqldb 10:11:  [d1]> CREATE TABLE t3(
    -> name varchar(10) PRIMARY KEY,
    -> age int,
    -> class varchar(8)
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:12:  [d1]> create table  t4 (
    -> name char(10), pay float(7,2) default  20000,
    -> primary key(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 创建复合主键
root@mysqldb 10:15:  [d1]> create  table t5( 
    -> cip char(15) , port int  , stuat enum("yes","no") , 
    -> primary key(cip , port ) );
Query OK, 0 rows affected (0.01 sec)

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

root@mysqldb 10:16:  [d1]> ALTER TABLE t4 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 在已有的表中添加主键
root@mysqldb 10:20:  [d1]> DESC t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| cip   | char(15)         | NO   | PRI | NULL    |       |
| port  | int(11)          | NO   | PRI | NULL    |       |
| stuat | enum('yes','no') | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

root@mysqldb 10:21:  [d1]> ALTER TABLE t5 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@mysqldb 10:21:  [d1]> DESC t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| cip   | char(15)         | NO   |     | NULL    |       |
| port  | int(11)          | NO   |     | NULL    |       |
| stuat | enum('yes','no'