MySQL学习笔记——基本概念与数据库的访问

1. 数据库基本概念

• 数据库：保存有组织数据的容器。
  注意：数据库软件不是数据库，数据库软件成为DBMS（数据库管理系统），用于创建、控制和访问数据库。
• 表：某种特定类型数据的结构化清单。
  注意：为方便检索和访问，一般一个表中的数据应当是一种类型的数据或一个清单，且表名具有唯一性。
• 模式（schema）：数据库和表的布局与特性的信息。
• 列：表中的一个字段。
  注意：在数据库中，数据类型是容器中所允许的数据的类型，每个表列的数据类型限制了该列中存储的数据。

• 行：表中的一个记录。

  行 & 列：例如一个存储用户信息的数据表，所有用户的用户名为一列，某个用户的所有信息为一行。

• 主键：唯一标识表中每一行的一列（一组列）称为主键

  • 任意两行的主键值不能相同
  • 每行必须有一个主键值，不能为NULL
  • 所有列值（或列值的组合）必须唯一
  • 不更新、不重用主键列的值
  • 不再主键列中使用可能更改的值

• SQL：结构化查询语言，用于与数据库通信

• DBMS：数据库管理系统，即我们所用的数据库软件，如MySQL
  
  • 基于文件共享系统的DBMS：用作桌面用途，典型的有Microsoft Access、FileMake等。
  • 基于客户机-服务器的DBMS：用户通过客户机向服务器（DBMS）发送请求，服务器完成所有数据的访问与处理，并将结果返回给用户，典型的有MySQL、Oracle、Microsoft SQL Server等。

2. MySQL基本操作

• 建立第一个数据库（本实例引用自《MySQL必知必会》）：

  • MySQL配置完成后，打开MySQL Administrator ，用安装时设置的密码登录客户机软件。

    

  • 登录后点击catalogs, 在下方的Schemata区域右击新建一个数据源，输入数据库名称。

    

  • 为数据库创建数据表，这里使用MySQL Query Browser进行操作，按同样方法登录该客户机软件，依次点击“File->Open Script”,打开文件create.sql，点击“Execute”运行脚本即可。

  • 为数据表填充数据，按同样方法打开并运行脚本populate.sql，这是右边的Schemata区域中对应的数据库中已经有数据，数据库创建完成。

create.sql =>

######################################### MySQL Crash Course# http://www.forta.com/books/0672327120/# Example table creation scripts################################################################# Create customers table########################CREATE TABLE customers(  cust_id      int       NOT NULL AUTO_INCREMENT,  cust_name    char(50)  NOT NULL ,  cust_address char(50)  NULL ,  cust_city    char(50)  NULL ,  cust_state   char(5)   NULL ,  cust_zip     char(10)  NULL ,  cust_country char(50)  NULL ,  cust_contact char(50)  NULL ,  cust_email   char(255) NULL ,  PRIMARY KEY (cust_id)) ENGINE=InnoDB;########################## Create orderitems table#########################CREATE TABLE orderitems(  order_num  int          NOT NULL ,  order_item int          NOT NULL ,  prod_id    char(10)     NOT NULL ,  quantity   int          NOT NULL ,  item_price decimal(8,2) NOT NULL ,  PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;###################### Create orders table#####################CREATE TABLE orders(  order_num  int      NOT NULL AUTO_INCREMENT,  order_date datetime NOT NULL ,  cust_id    int      NOT NULL ,  PRIMARY KEY (order_num)) ENGINE=InnoDB;######################## Create products table#######################CREATE TABLE products(  prod_id    char(10)      NOT NULL,  vend_id    int           NOT NULL ,  prod_name  char(255)     NOT NULL ,  prod_price decimal(8,2)  NOT NULL ,  prod_desc  text          NULL ,  PRIMARY KEY(prod_id)) ENGINE=InnoDB;####################### Create vendors table######################CREATE TABLE vendors(  vend_id      int      NOT NULL AUTO_INCREMENT,  vend_name    char(50) NOT NULL ,  vend_address char(50) NULL ,  vend_city    char(50) NULL ,  vend_state   char(5)  NULL ,  vend_zip     char(10) NULL ,  vend_country char(50) NULL ,  PRIMARY KEY (vend_id)) ENGINE=InnoDB;############################ Create productnotes table###########################CREATE TABLE productnotes(  note_id    int           NOT NULL AUTO_INCREMENT,  prod_id    char(10)      NOT NULL,  note_date datetime       NOT NULL,  note_text  text          NULL ,  PRIMARY KEY(note_id),  FULLTEXT(note_text)) ENGINE=MyISAM;###################### Define foreign keys#####################ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

populate.sql =>

######################################### MySQL Crash Course# http://www.forta.com/books/0672327120/# Example table population scripts################################################################### Populate customers table##########################INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', '[e-mail]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', '[e-mail]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', '[e-mail]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');######################### Populate vendors table########################INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');########################## Populate products table#########################INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');######################## Populate orders table#######################INSERT INTO orders(order_num, order_date, cust_id)VALUES(20005, '2005-09-01', 10001);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20006, '2005-09-12', 10003);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20007, '2005-09-30', 10004);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20008, '2005-10-03', 10005);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20009, '2005-10-08', 10001);############################ Populate orderitems table###########################INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 1, 'ANV01', 10, 5.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 2, 'ANV02', 3, 9.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 3, 'TNT2', 5, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 4, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20006, 1, 'JP2000', 1, 55);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20007, 1, 'TNT2', 100, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20008, 1, 'FC', 50, 2.50);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 1, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 2, 'OL1', 1, 8.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 3, 'SLING', 1, 4.49);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 4, 'ANV03', 1, 14.99);############################## Populate productnotes table#############################INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(101, 'TNT2', '2005-08-17','Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(102, 'OL1', '2005-08-18','Can shipped full, refills not available.Need to order new can if refill needed.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(103, 'SAFE', '2005-08-18','Safe is combination locked, combination not provided with safe.This is rarely a problem as safes are typically blown up or dropped by customers.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(104, 'FC', '2005-08-19','Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(105, 'TNT2', '2005-08-20','Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(106, 'TNT2', '2005-08-22','Matches not included, recommend purchase of matches or detonator (item DTNTR).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(107, 'SAFE', '2005-08-23','Please note that no returns will be accepted if safe opened using explosives.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(108, 'ANV01', '2005-08-25','Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(109, 'ANV03', '2005-09-01','Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(110, 'FC', '2005-09-01','Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(111, 'SLING', '2005-09-02','Shipped unassembled, requires common tools (including oversized hammer).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(112, 'SAFE', '2005-09-02','Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(113, 'ANV01', '2005-09-05','Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(114, 'SAFE', '2005-09-07','Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.Comment forwarded to vendor.');

• 选择数据源：
  在命令行窗口中输入mysql -uroot -p 登录数据库，键入如下命令即可选择刚创建的数据库（crashcourse为数据库名称）

use crashcourse;

• 查看数据库内容：
  

查看所有数据库：show databases;
查看当前选择数据库所有表：show tables;
查看某个表中所有列：show columns from tablename;
更多关于show 命令的用法请查阅相关书籍……