实验2 MySQL数据库对象管理_MySQL笔记（二）数据库对象的创建和管理

学校用 sqlserver ，记录数据移植到 mysql 过程中的一些问题(对应数据类型，主键外键等)。

索引：

1、查看 MySql数据库物理文件存放路径

mysql> show global variables like "%datadir%";+---------------+---------------------------------------------+

| Variable_name | Value |

+---------------+---------------------------------------------+

| datadir | C:\ProgramData\MySQL\MySQL Server 5.5\Data\ |

+---------------+---------------------------------------------+

1 row in set (0.00 sec)

所有数据库都放在这个默认目录下(可以自行修改)，随便打开一个数据库，内容如下：

我发现 db.opt 是可以打开来编辑的，可以修改字符集什么的。

2、Getting Information About Tables

mysql>SHOW TABLES;+-------------------+

| Tables_in_mangast |

+-------------------+

| manga |

+-------------------+

1 row in set (0.00sec)

mysql> SHOW CREATE TABLEmanga;+-------+-----------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------+

| manga | CREATE TABLE`manga` (

`manga_id`bigint(20) NOT NULL AUTO_INCREMENT COMMENT '漫画id',

`manga_name`varchar(40) NOT NULL COMMENT '漫画名字',

`manga_discription`varchar(120) DEFAULT NULL COMMENT '漫画描述',

`manga_status`tinyint(4) NOT NULL DEFAULT '0' COMMENT '漫画描述',PRIMARY KEY(`manga_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8 COMMENT='漫画表' |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> DESCmanga;+-------------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+--------------+------+-----+---------+----------------+

| manga_id | bigint(20) | NO | PRI | NULL | auto_increment |

| manga_name | varchar(40) | NO | | NULL | |

| manga_discription | varchar(120) | YES | | NULL | |

| manga_status | tinyint(4) | NO | | 0 | |

+-------------------+--------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

mysql>DESCRIBE manga;+-------------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+--------------+------+-----+---------+----------------+

| manga_id | bigint(20) | NO | PRI | NULL | auto_increment |

| manga_name | varchar(40) | NO | | NULL | |

| manga_discription | varchar(120) | YES | | NULL | |

| manga_status | tinyint(4) | NO | | 0 | |

+-------------------+--------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

DESC 是 DESCRIBE 的简写形式

3、删库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.

4、创建表(要求在创建的过程中，分别为每张表合理建立主键、外键约束)

相关资料

CHAR 与 VARCHAR 对比

如果是定长的指示字段例如 Y 或者 N ，建议用 CHAR 比较节省空间， VARCAHR 要两个字节， CHAR 只要一个字节；非定长的用 VARCHAR。

Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC ------- ps. 定点类型(准确值)  decimal = 十进制

这个数据类型用于存储精确的数值，例如货币。

MySQL 中的 DECIMAL 和 SQLServer 中的 NUMERIC 是类似的。

例如说 salary DECIMAL(5,2) 的存储范围为 -999.99 到 999.99

5、外键约束

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same. (应用条件)

For storage engines supporting foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. (对子表的影响)

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified using ON UPDATE and ON DELETEsubclauses of the FOREIGN KEY clause. (父表变动如何影响子表 ↓)

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same asRESTRICT.(和 RESTRICT 完全相同。)

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT. (默认行为)

DROP DATABASE IF EXISTSorderdb;CREATE DATABASEorderdb;USEorderdb;CREATE TABLEemployee (

employee_noVARCHAR(8),

employee_nameVARCHAR(10),

sexCHAR(1),

birthday DATE,

addressVARCHAR(50),

telephoneVARCHAR(20),

hiredate DATE COMMENT'聘用日期',

departmentVARCHAR(30),

headshipVARCHAR(10) COMMENT '职务',

salaryDECIMAL(8,2),PRIMARY KEY(employee_no)

) ENGINE=INNODB;CREATE TABLEcustomer (

customer_noVARCHAR(9),

customer_nameVARCHAR(40),

telephoneVARCHAR(20),

addressVARCHAR(40),

zipVARCHAR(6),PRIMARY KEY(customer_no)

) ENGINE=INNODB;CREATE TABLEproduct (

product_noVARCHAR(9),

product_nameVARCHAR(40),

product_classVARCHAR(20),

product_priceDECIMAL(7,2),PRIMARY KEY(product_no)

) ENGINE=INNODB;CREATE TABLEorder_master (

order_noVARCHAR(12),

customer_noVARCHAR(9),

saler_noVARCHAR(8),

order_date DATE,

order_sumDECIMAL(9,2),

invoicenoCHAR(10) COMMENT '发票号码',PRIMARY KEY(order_no),FOREIGN KEY(customer_no)REFERENCEScustomer(customer_no)

) ENGINE=INNODB;CREATE TABLEorder_detail (

order_noVARCHAR(12),

product_noVARCHAR(9),

quantityINT,

priceDECIMAL(7,2),PRIMARY KEY(order_no, product_no),FOREIGN KEY(order_no)REFERENCESorder_master(order_no),FOREIGN KEY(product_no)REFERENCESproduct(product_no)

) ENGINE=INNODB;

6、表结构的修改

修改客户表结构，要求客户名称和客户电话属性为not null

修改员工表结构，要求员工姓名和电话属性为not null

修改订单表结构，要求发票号码属性为not null

一种方式是修改上面的 schema.sql 然后重新跑这个脚本(没有数据的情况下)，另外一种方法是：

mysql> ALTER TABLE customer MODIFY customer_name VARCHAR(40) NOT NULL, MODIFY telephone VARCHAR(20) NOT NULL;

mysql> ALTER TABLE employee MODIFY employee_name VARCHAR(10) NOT NULL, MODIFY telephone VARCHAR(20) NOT NULL;

mysql> ALTER TABLE order_master MODIFY invoiceno CHAR(10) NOT NULL;

7、索引操作

在已创建的基本表的基础上，完成以下索引

(1)在员工表中按所得薪水建立一个非聚集索引salaryIdx

(2)在订单主表中，首先按订金金额的升序，然后按业务员编号的降序建立一个非聚集索引salenosumIdx。