阅读量:2
一、库的建立
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb2_stuinfo | | mydb3_employee | | mydb4_product | | mydbl_test | | mysql | | performance_schema | | sys | | temp2 | +--------------------+ 9 rows in set (0.00 sec) mysql> create database mydb6_product; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb2_stuinfo | | mydb3_employee | | mydb4_product | | mydb6_product | | mydbl_test | | mysql | | performance_schema | | sys | | temp2 | +--------------------+ 10 rows in set (0.00 sec)二、建立employees表 （有主键，非空，默认值的要求）
mysql> create table employees( -> id int primary key, -> name varchar(50) not null, -> age int, -> gender varchar(10) not null default 'unknown', -> salary float); Query OK, 0 rows affected (0.03 sec) mysql> desc employees; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(50) | NO | | NULL | | | age | int | YES | | NULL | | | gender | varchar(10) | NO | | unknown | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)三、建立 orders表 （有主键和非空的要求）
mysql> create table orders( -> id int primary key, -> name varchar(100) not null, -> price float, -> quantity int, -> category varchar(50)); Query OK, 0 rows affected (0.03 sec) mysql> desc orders; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(100) | NO | | NULL | | | price | float | YES | | NULL | | | quantity | int | YES | | NULL | | | category | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) 四、建立invoices表 （有主键自增长，外键关联，数值范围的要求）
mysql> create table invoices( -> number int primary key, -> order_id int,foreign key (order_id) references orders(id), -> in_date date, -> total_amount float ,check(total_amount>0)); Query OK, 0 rows affected (0.04 sec) mysql> alter table invoices modify number int auto_increment; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc invoices; +--------------+-------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------+------+-----+---------+----------------+ | number | int | NO | PRI | NULL | auto_increment | | order_id | int | YES | MUL | NULL | | | in_date | date | YES | | NULL | | | total_amount | float | YES | | NULL | | +--------------+-------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)