テーブルの作成、さまざまな制約の追加、テーブル構造の生成、テーブルの変更と削除など、MySQL のさまざまな操作を学習している。実践的な演習を行い、データ テーブルの基本操作の基礎を包括的に確認します。
ケース: データベース会社を作成し、次の 2 つのテーブルに示されているテーブル構造に従って会社データベースにオフィスと従業員の 2 つのデータ テーブルを作成し、データの基本操作を完了します。操作プロセスに応じたテーブル。
# (無料学習の推奨事項: mysql ビデオ チュートリアル #)
##
操作过程如下:(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 ステートメントを入力できます。
create database company;
mysql> create database company;Query OK, 1 row affected (0.06 sec)
作成が成功したら、会社データベースにデータ テーブルを作成します。最初にデータベースを選択する必要があります。 SQL ステートメントは次のとおりです。
mysql> use company;Database changed
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)
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)
作成は成功しました。2 つのテーブルの構造を確認してください:
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)
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)
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)
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)
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)
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)
①テーブルの外部キー制約を削除します:
alter tableemployeesdropforeignkeyoffice_fk;②テーブルofficeを削除します:
drop table office;<pre class="brush:php;toolbar:false">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)</pre>
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)
alter table employees rename employees_info;
rree
関連する無料学習の推奨事項:mysql データベース(ビデオ)
以上がMySQL 演習 1: データ テーブルの基本操作の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。