mysql练习之一：数据表的基本操作

已经学习了MySQL的各种操作，如创建表、添加各种约束、产看表结构、以及修改和删除表。给出一个实战演练，全面复习一下数据表的基本操作基础。

案例:创建数据库company，按照下面两个表给出的表结构在company数据库中创建两个数据表offices和employees，按照操作过程完成数据表的基本操作。

（免费学习推荐：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语句进行操作。

(2)：创建数据库company。

create database company;

mysql> create database company;Query OK, 1 row affected (0.06 sec)

创建成功后，在company数据库中创建数据表，必须先选择该数据库。SQL语句如下：

mysql> use company;Database changed

(3):创建表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)：创建表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)

创建成功，查看两个表的结构:

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        | <i>本文来源gaodai$ma#com搞$$代**码)网@</i>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)

喜欢 (0)赏

[搞代码]

分享 (0)