mysql中數(shù)據(jù)表基本操作的示例
這篇文章主要介紹MySQL中數(shù)據(jù)表基本操作的示例，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
專注于為中小企業(yè)提供網(wǎng)站建設(shè)、網(wǎng)站制作服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)寧德免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了千余家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
案例:創(chuàng)建數(shù)據(jù)庫company，按照下面兩個表給出的表結(jié)構(gòu)在company數(shù)據(jù)庫中創(chuàng)建兩個數(shù)據(jù)表offices和employees，按照操作過程完成數(shù)據(jù)表的基本操作。
操作過程如下：
(1)：登錄MySQL。
mysql -h localhost -u root -p
打開windows命令行，輸入登錄用戶名和密碼：
C:\Users\Hudie>mysql -h localhost -u root -p Enter password: ********Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 19Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, 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>_
登錄成功，可以輸入SQL語句進行操作。
(2)：創(chuàng)建數(shù)據(jù)庫company。
create database company;
mysql> create database company;Query OK, 1 row affected (0.06 sec)
創(chuàng)建成功后，在company數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)表，必須先選擇該數(shù)據(jù)庫。SQL語句如下：
mysql> use company;Database changed
(3):創(chuàng)建表offices。
create table offices
mysql> create table offices -> ( -> officeCode int(10) not null unique, -> city varchar(50) not null, -> address varchar(50) not null, -> country varchar(50) not null, -> postalCode varchar(15) not null, -> primary key (officeCode) -> );Query OK, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| offices |+-------------------+1 row in set (0.00 sec)
(4)：創(chuàng)建表enployees。
create table employees
mysql> create table employees -> ( -> employeeNumber int(11) not null primary key auto_increment, -> lastNamee varchar(50) not null, -> firstName varchar(50) not null, -> mobile varchar(25) not null, -> officeCode int (10) not null, -> jobTitle varchar(50) not null, -> birth datetime, -> noth varchar(25), -> sex varchar(5), -> constraint office_fk foreign key(officeCode) references offices(officeCode) -> );Query OK, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees || offices |+-------------------+2 rows in set (0.01 sec)
創(chuàng)建成功，查看兩個表的結(jié)構(gòu):
mysql> desc offices;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| officeCode | int(10) | NO | PRI | NULL | || city | varchar(50) | NO | | NULL | || address | varchar(50) | NO | | NULL | || country | varchar(50) | NO | | NULL | || postalCode | varchar(15) | NO | | NULL | |+------------+-------------+------+-----+---------+-------+5 rows in set (0.06 sec)mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || mobile | varchar(25) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || jobTitle | varchar(50) | NO | | NULL | || birth | datetime | YES | | NULL | || noth | varchar(25) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(5)：將表employees的mobile字段修改到officeCode字段后面。
alter table employees modify mobile varchar(25) after officeCode;
mysql> alter table employees modify mobile varchar(25) after officeCode;Query OK, 0 rows affected (0.18 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || mobile | varchar(25) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | || birth | datetime | YES | | NULL | || noth | varchar(25) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(6)：將表employees的birth字段改名為employee_birth。
alter table employees change birth employee_birth datetime;
mysql> alter table employees change birth employee_birth datetime;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || mobile | varchar(25) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | || employee_birth | datetime | YES | | NULL | || noth | varchar(25) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
(7)：修改sex字段，設(shè)置數(shù)據(jù)類型為char(1),非空約束。
alter table employees modify sex char(1) not null;
mysql> alter table employees modify sex char(1) not null;Query OK, 0 rows affected (0.20 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || mobile | varchar(25) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | || employee_birth | datetime | YES | | NULL | || noth | varchar(25) | YES | | NULL | || sex | char(1) | NO | | NULL | |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)
(8)：刪除字段noth。
alter table employees drop noth;
mysql> alter table employees drop noth;Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || mobile | varchar(25) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | || employee_birth | datetime | YES | | NULL | || sex | char(1) | NO | | NULL | |+----------------+-------------+------+-----+---------+----------------+8 rows in set (0.01 sec)
(9):增加字段名favoriate_activity,數(shù)據(jù)類型為varchar(100)
alter table employees add favoriate_activity varchar(100);
mysql> alter table employees add favoriate_activity varchar(100);Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+--------------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11) | NO | PRI | NULL | auto_increment || lastNamee | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || officeCode | int(10) | NO | MUL | NULL | || mobile | varchar(25) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | || employee_birth | datetime | YES | | NULL | || sex | char(1) | NO | | NULL | || favoriate_activity | varchar(100) | YES | | NULL | |+--------------------+--------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
(10):刪除主表offices
①刪除表的外鍵約束:alter table employees drop foreign key office_fk;
②刪除表offices:drop table offices;
mysql> alter table employees drop foreign key office_fk;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> drop table offices;Query OK, 0 rows affected (0.03 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees |+-------------------+1 row in set (0.06 sec)
(11):修改表employees存儲引擎為MyISAM。
alter table employees ENGINE=MyISAM;
mysql> alter table employees ENGINE=MyISAM;Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table employees \G*************************** 1. row *************************** Table: employeesCreate Table: CREATE TABLE `employees` ( `employeeNumber` int(11) NOT NULL AUTO_INCREMENT, `lastNamee` varchar(50) NOT NULL, `firstName` varchar(50) NOT NULL, `officeCode` int(10) NOT NULL, `mobile` varchar(25) DEFAULT NULL, `jobTitle` varchar(50) NOT NULL, `employee_birth` datetime DEFAULT NULL, `sex` char(1) NOT NULL, `favoriate_activity` varchar(100) DEFAULT NULL, PRIMARY KEY (`employeeNumber`), KEY `office_fk` (`officeCode`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
(12)將表employees名稱修改為employees_info。
alter table employees rename employees_info;
mysql> alter table employees rename employees_info;Query OK, 0 rows affected (0.07 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees_info |+-------------------+1 row in set (0.00 sec)
以上是“mysql中數(shù)據(jù)表基本操作的示例”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
本文題目：mysql中數(shù)據(jù)表基本操作的示例
文章源于：http://aaarwkj.com/article26/pesgcg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、網(wǎng)站設(shè)計公司、面包屑導航、微信公眾號、網(wǎng)站策劃、網(wǎng)站導航
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 高端定制網(wǎng)站策劃需要要怎么做 2021-08-17
- 手機網(wǎng)站策劃與手機網(wǎng)站建設(shè)的三大原則 2014-05-30
- 網(wǎng)站策劃 2022-01-19
- 企業(yè)建站前要做好哪些網(wǎng)站策劃？ 2021-06-20
- 淄博網(wǎng)站建設(shè)-網(wǎng)站策劃說明書的書寫 2021-11-16
- 網(wǎng)站建設(shè)第一步：網(wǎng)站策劃 2021-01-08
- 網(wǎng)站建設(shè)怎么樣才能做好網(wǎng)站策劃 2021-12-19
- 一個優(yōu)質(zhì)的網(wǎng)站策劃方案離不開seo優(yōu)化推廣的技術(shù)支持 2016-11-25
- 企業(yè)網(wǎng)站策劃書該如何進行設(shè)計？ 2022-11-28
- 建筑設(shè)計行業(yè)網(wǎng)站策劃 2023-02-06
- 淺析網(wǎng)站策劃方案的七大步驟 2016-10-20
- 網(wǎng)站策劃對建站的重要性 2020-04-09