mysql数据表的基本操作

2022-10-14 13:53:18

1. 使用主键约束

主键约束要求主键列的数据唯一，并且不允许为空。
1）. 单字段主键

语法： 字段名 数据类型 PRIMARY KEY [默认值]
mysql> create database  test01; #创建库
Query OK, 1 row affected (0.00 sec)

mysql> use test01; #进入所创建的库
Database changed
mysql> create table test1 
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25), 
    -> deptID INT(11),
    -> salary FLOAT
    -> ); 
Query OK, 0 rows affected (0.04 sec)

查看数据表的结构 DESCRIBE 表名；或 DESC 表名；

2）. 在定义完所有列之后指定主键

语法：[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
mysql> create table test2
    -> (
    -> id INT(11), 
    -> name VARCHAR(25), 
    -> deptID INT(11),
    -> salary FLOAT, 
    -> PRIMARY KEY(id)
    -> ); 
Query OK, 0 rows affected (0.00 sec)

3). 多字段联合主键

语法：PRIMARY KEY [字段1，字段2，....]
mysql> create table test3 
    -> (
      -> id INT(11),
        -> name VARCHAR(25),
    -> deptID INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
        Query OK, 0 rows affected (0.00 sec)

2.设置表的属性值自动增加

语法：字段名 数据类型 AUTO_INCREMENT
mysql> create table test5
    -> (
    ->  id INT(11) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(25) NOT NULL,
    ->  deptId INT(11),
    -> salary FLOAT
    -> ); 
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test5(name,salary)
 -> VALUES(‘lucy‘,1000),(‘lura‘,1200),(‘kevin‘,1500);
 mysql>  SELECT * FROM test5;
+----+-------+--------+--------+
| id | name  | deptId | salary |
+----+-------+--------+--------+
|  1 | lucy  |   NULL |   1000 |
|  2 | lura  |   NULL |   1200 |
|  3 | kevin |   NULL |   1500 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

3.查看表的详细结构语句
语法：SHOW CREATE TABLE <表名\G>

4.修改数据表名
语法：ALTER TABLE <旧表名> RENAME [TO] <新表名>.

5.修改字段的数据类型
语法：ALTER TABLE <表名> MODIFY <字段名> <数据类型>

6.修改字段名
语法：ALTER TABLE <表名> CHANGE<旧字段名><新字段名> <新数据类型>

7. 添加字段
语法：ALTER TABLE <表名> ADD <新字段名><数据类型> [约束条件] [FIRST|AFTER 已存在字段名]

mysql> desc test2; 
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name11 | varchar(30) | YES  |     | NULL    |       |
| deptID | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE test2 ADD column1 VARCHAR(12) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test2;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name11  | varchar(30) | YES  |     | NULL    |       |
| deptID  | int(11)     | YES  |     | NULL    |       |
| salary  | float       | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

也可以这样：

8.删除字段
mysql> ALTER TABLE tb_dept1 DROP column2;

9.修改字段的排列位置
语法：ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST | AFTER <字段2>

10.更改表的存储引擎

11.删除表的外键约束
语法：ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

mysql> create table test6 
    -> (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(25),
    -> deptID INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES test1(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test6\g
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test6 | CREATE TABLE `test6` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptID` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptID`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptID`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test6 DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test6\g                                                                                                                     
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test6 | CREATE TABLE `test6` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptID` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

12.删除数据表
语法：DROP TABLE [IF EXISTS]表1，表2

1）先创建ts3
mysql> create table  ts3 
    -> (
    -> id INT(11) PRIMARY KEY,
    ->  name VARCHAR(22),
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

2）创建test7
mysql> create table test7 
    -> (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(25),
    -> deptId INT(11),
    ->  salary FLOAT,
    ->  CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES ts3(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

3）直接删除父表ts3

就可以看见在外键的约束时，主表不能直接删除。
4）解除关联子表test7的外键约束

mysql>  ALTER TABLE test7 DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

5）这样主表就可以删除了

mysql> drop table ts3;
Query OK, 0 rows affected (0.01 sec)

mysql数据表的基本操作

码农公寓

相关文章