mysql資料表的基本操作

標籤：刪除   欄位   odi   big   duplicate   聯合   使用者   prim   員工   

好久沒梳理下Mysql基礎命令了.今天抽空整理了下，雖然很簡單...但是還是有必要鞏固下基礎滴啦

1.建立表：之前需要use database database_name 然後create table 表名（）；

例：建立員工表tb_employee1，結構如下表所示

欄位名稱	資料類型	備忘
id	int（11）	員工編號
name	varchar（25）	員工名稱
depld	int（11）	所在部門編號
salary	float	工資

mysql> create database aa;mysql> use aa;mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float);

 2.主鍵約束：主鍵又稱主碼，是表中一列或多列的組合。主鍵約束要求主鍵列的資料唯一，並且不可為空。主鍵能夠唯一標識表中的一條記錄，可以結合外鍵來定義不同資料表之間的關係，並且可以加快資料庫查詢速度。主鍵分為兩種類型：單欄位主鍵和多欄位聯合主鍵。

（1）單欄位主鍵

mysql> create table tb_employee1(id int(11)primary key,name varchar(25),depld int(11),salary float);mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id));

（2）多欄位聯合主鍵

mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id,name));

 3.外鍵約束：外鍵用來在兩個表之間建立聯絡，它可以是一列或者多列。一個表可以有一個或者多個外鍵。外鍵對應的是參照完整性，一個表的外鍵可以是空值，若不為空白值，則每一個外鍵必須等於另一個表中主鍵的某個值。

下面介紹幾個概念。是表中的一個欄位，它可以不是本表的主鍵，但對應另外一個表的主鍵。外鍵的主要作用是保證資料引用的完整性，定義外鍵後，不允許刪除在另一個表中具有關聯關係的主鍵。例如：部分表的主鍵id，在表tb中有一個鍵deptld與這個id關聯。

主表（父表）：對於兩個具有關聯關係的表而言，相關欄位中主鍵所在的那個表就是主表。

從表（自表）：對於兩個具有關聯關係的表而言，相關欄位中外鍵所在的那個表就是從表。

例：定義資料表tb_employee5，並且在該表中建立外鍵約束

建立一個部門表tb_dept1，表結構如下表所示

欄位名稱	資料類型	備忘
id	int（11）	部門編號
name	varchar（22）	部門名稱
location	varchar（50）	部門位置

mysql> create table tb_dept1(id int(11)primary key,name varchar(22),location varchar(50));定義資料表tb_employee5，讓它的deptld欄位作為外部索引鍵關聯到tb_dept1的主鍵id：mysql> create table tb_employee5(id int(11)primary key,name varchar(25),deptld int(11),salary float,constraint fk_emp_dept1 foreign key(deptld) references tb_dept1(id));

 4.非空約束（not null）：是指欄位的值不可為空。對於使用了非空約束的欄位，如果使用者在添加資料時沒有指定值，資料庫系統會報錯。文法規則：欄位名 資料類型 not null

例：定義資料表tb_employee6，指定員工的名稱不可為空

mysql> create table tb_employee6(id int(11)primary key,name varchar(25)not null,deptld int(11),salary float);

 5.唯一性限制式：要求該列唯一，允許為空白，但只能出現一個空值。唯一性限制式可以確保一列或者多列都不能出現重複值。

（1）在定義完列之後直接指定唯一約束

文法規則：欄位名 資料類型 unique

mysql> create table tb_dept2(id int(11)primary key,name varchar(22)unique,location varchar(50));

（2）在定義完所有列之後指定唯一約束

文法規則：constraint 約束名 unique（欄位名）

mysql> create table tb_dept3(id int(11)primary key,name varchar(22),location varchar(50),constraint sth unique(name));

unique和primary key的區別：一個表中可以有多個欄位聲明為unique，但只能有一個primary key聲明；聲明為primary key的列不允許有空值，但是聲明為unique的欄位允許空值的存在。

 6.預設約束：指定某列的預設值。如男性同學較多，性別就可以預設為“男”。如果插入一條新的記錄時沒有為這個欄位賦值，那麼系統自動會為這個欄位賦值為男。

文法規則：欄位名 資料類型 default 預設值

例：mysql> create table tb_employee7(id int(11)primary key,name varchar(25)not null,deptld int(11)default 1111,salary float);

 7.設定資料表的屬性值自動增加：在資料庫應用中，經常希望在每次插入新資料時，系統會自動產生欄位的主索引值。可以為表主鍵添加auto_increment關鍵字來實現。在mysql中，預設情況下auto_increment初始值為1，每新增一條記錄，欄位自動加1.一個表只能有一個欄位使用auto_increment約束，且每個欄位必須為主鍵的一部分。auto_increment約束的欄位可以是任何整數類型（tinyint/samllint/int/bigint）。

文法規則：欄位名 資料類型 auto_increment

例：定義資料表tb_employee8，指定員工標號自動增加

mysql> create table tb_employee8(id int(11)primary key auto_increment,name varchar(25)not null,deptld int(11),salary float);

 

mysql> insert into tb_employee8(name,salary) values(‘lucy‘,1000),(‘lii‘,800),(‘cai‘,20000);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from tb_employee8;+----+------+--------+--------+| id | name | deptld | salary |+----+------+--------+--------+|  1 | lucy |   NULL |   1000 ||  2 | lii  |   NULL |    800 ||  3 | cai  |   NULL |  20000 |+----+------+--------+--------+3 rows in set (0.00 sec)

 

 8.查看資料表的結構

查看錶基本結構：describe 表名 或 desc 表名

mysql> desc tb_employee8;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| id     | int(11)     | NO   | PRI | NULL    | auto_increment || name   | varchar(25) | NO   |     | NULL    |                || deptld | int(11)     | YES  |     | NULL    |                || salary | float       | YES  |     | NULL    |                |+--------+-------------+------+-----+---------+----------------+

（1）NULL：表示該列是否可以儲存null值

（2）key：表示該列是否已經編製索引。pri為該列主鍵的一部分；uni表示該列是unique索引的一部分；mul表示在列中某個給定值允許出現多次。

 9.查看錶詳細結構

文法規則：show create table 表名\G

mysql> show create table tb_employee8\G*************************** 1. row ***************************       Table: tb_employee8Create Table: CREATE TABLE `tb_employee8` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(25) NOT NULL,  `deptld` int(11) DEFAULT NULL,  `salary` float DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin11 row in set (0.00 sec)

 10.修改資料表：修改資料庫中已經存在的資料表結構。常用的修改表的操作有：修改表名，修改欄位資料類型或欄位名，增加和刪除欄位，修改欄位的排列位置，更改表的儲存引擎，刪除表的外鍵約束等。

（1）修改表名

alter table 舊錶名 rename 新表名例：mysql> alter table tb_dept3 rename tb_deptment3；

（2）修改欄位資料類型

alter table 表名 modify 欄位名 資料類型例：mysql> alter table tb_dept1 modify name varchar(30);

（3）添加欄位

alter table 表名 add 新欄位名 資料類型例1：mysql> alter table tb_dept1 add managerld int(20);（沒有完整性條件約束條件的欄位）例2：mysql> alter table tb_dept1 add column1 varchar(12) not null;（添加有約束條件）例3：mysql> alter table tb_dept1 add column2 int(11) first;（添加到第一列）例4：mysql> alter table tb_dept1 add column3 int(11) after name;（添加到name後一列）

（4）刪除欄位

alter table 表名 drop 欄位名例：mysql> alter table tb_dept1 drop column3;

（5）修改欄位排序

alter table 表名 modify 欄位1 資料類型 first after 欄位2例1：mysql> alter table tb_dept1 modify id int(11) first;例2：mysql> alter table tb_dept1 modify column2 int(11) after column1;

（6）更改表的資料引擎

alter table 表名 engine=更改後的儲存引擎例：mysql> alter table tb_dept1 engine=innodb;

（7）刪除表的外鍵約束

alter table 表名 drop foreign key 外鍵約束名

（8）修改欄位名

mysql> alter table customers change c_contact c_phone varchar(50);

 11.刪除資料表

drop table 表1 表2 。。。

刪除被其他表關聯的主表：先刪除字表，再刪除主表（或取消外鍵約束，刪除主表）

mysql資料表的基本操作