全部频道

首页 热点资讯 义务教育 高等教育 出国留学 考研考公
您的当前位置：首页正文

MySQL数据库自学

2023-11-11 来源：花图问答

亲爱的自己和各位读者朋友：

    您们好！

    这是作者本人自学Java编程开发的一系列文章，不具有一定的权威性，也算是自己一个人的学习笔记和总结，希望自己通过博客的形式将我自己的学习效率得到提高。如自学的稳重存在不足或错误的地方希望广大的博客朋友们多多指教。本人在此不胜感激！

    天下数据库同属一家，可以说是兄弟关系。学习MySQL数据库可以说是一件很有趣的学习过程，就目前市场上对MySQL数据库的程序员需求量比较大。当你自己应聘一份工作的时候，MySQL数据库的技术技能是你必须要掌握的。

    学习MySQL数据库你就必须要会几样东西，数据库的安装、sql语句、MySQL函数、MySQL索引、MySQL存储过程、MySQL触发器、MySQL事物以及数据备份和还原。这几项都是你学完这门课程以后必须要会的东西，只有不仅学会还要会运用才是真正的掌握这门技术。

    学习MySQL数据库我给大家推荐几本教材吧，如《MySQL自学视频教程》、《深入浅出MySQL》等等。同时还有许多的免费资源网站可以提供给大家。

    好了，话不多说，我们就正式进入到MySQL数据库的学习中。

本文出自 “孤独一夜” 博客，转载请与作者联系！

MySQL数据库自学

标签：mysql数据库自学序言

小编还为您整理了以下内容，可能对您也有帮助：

MySQL新手如何快速入门

sudo service mysql stop$ sudo service mysql start$ sudo service mysql status$ mysql --help$ mysql -h host -u user -p

连接到服务器上，host为主机名，user为用户名，如果服务器所在的机器是现在所登陆的机器，则不需要指定主机名。

登陆后的提示符为 mysql>，每条 SQL 语句以分号 ; 结束。

mysql> SHOW DATABASES;
显示当前存在在服务器上的数据库。
mysql> USE [database-name]
访问或进入指定的数据库，该条语句可以像 QUIT 一样不加分号（加了也可以）。
mysql> CREATE DATABASE [ database-name ];
mysql> USE [ database-name ]
$ mysql -h host -u user -p [ database-name ]
mysql> SHOW TABLES;

采用 MySQL必知必会 一书的例子进行学习。下面是本例子采用的数据库的生成脚本。
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‘, ‘ylee@coyote.com‘);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‘, ‘rabbit@wascally.com‘);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‘, ‘sam@yosemite.com‘);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> SHOW TABLES;
+———————–+
| Tables_in_crashcourse |
+———————–+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+———————–+
6 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM products;
技术分享

mysql> SELECT prod_id, vend_id, prod_name, prod_price FROM products;
技术分享

MySQL入门（一）

标签：mysql   数据库   

MySQL新手如何快速入门

sudo service mysql stop$ sudo service mysql start$ sudo service mysql status$ mysql --help$ mysql -h host -u user -p

连接到服务器上，host为主机名，user为用户名，如果服务器所在的机器是现在所登陆的机器，则不需要指定主机名。

登陆后的提示符为 mysql>，每条 SQL 语句以分号 ; 结束。

mysql> SHOW DATABASES;
显示当前存在在服务器上的数据库。
mysql> USE [database-name]
访问或进入指定的数据库，该条语句可以像 QUIT 一样不加分号（加了也可以）。
mysql> CREATE DATABASE [ database-name ];
mysql> USE [ database-name ]
$ mysql -h host -u user -p [ database-name ]
mysql> SHOW TABLES;

采用 MySQL必知必会 一书的例子进行学习。下面是本例子采用的数据库的生成脚本。
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‘, ‘ylee@coyote.com‘);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‘, ‘rabbit@wascally.com‘);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‘, ‘sam@yosemite.com‘);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> SHOW TABLES;
+———————–+
| Tables_in_crashcourse |
+———————–+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+———————–+
6 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM products;
技术分享

mysql> SELECT prod_id, vend_id, prod_name, prod_price FROM products;
技术分享

MySQL入门（一）

标签：mysql   数据库   

自学数据库MySQL需要注意哪些方面

一、熟悉MySQL的运行环境
MySQL数据库是在Linux环境下运行的，建议先熟练Linux系统。选定好架构模式之后就可以开始进入程序开发和数据处理的环节。

二、有扎实的数据库理论知识
MySQL作为关系型数据库，在实际的应用中也要学会分析存储数据的关系型数据结构，关系操作集合等。只有对这些内容有一个明确的认识，在设计数据库字段、表与表的关系才能考虑周全，避免出现错误。

三、熟练的SQL语言运用
任何一种数据库的学习，包括MySQL，SQL语句都是位于核心部分的内容。需要注意的是SQL语言的使用要力求简明扼要，能用一个select搞定的问题并不需要写更多的union。所以在SQL语言的应用方面要注意高效。

自学数据库MySQL需要注意哪些方面

一、熟悉MySQL的运行环境
MySQL数据库是在Linux环境下运行的，建议先熟练Linux系统。选定好架构模式之后就可以开始进入程序开发和数据处理的环节。

二、有扎实的数据库理论知识
MySQL作为关系型数据库，在实际的应用中也要学会分析存储数据的关系型数据结构，关系操作集合等。只有对这些内容有一个明确的认识，在设计数据库字段、表与表的关系才能考虑周全，避免出现错误。

三、熟练的SQL语言运用
任何一种数据库的学习，包括MySQL，SQL语句都是位于核心部分的内容。需要注意的是SQL语言的使用要力求简明扼要，能用一个select搞定的问题并不需要写更多的union。所以在SQL语言的应用方面要注意高效。

MySQL有什么推荐的学习书籍

1、《MySQL技术内幕:InnoDB存储引擎》

《MySQL技术内幕：InnoDB存储引擎》的作者是姜承尧。本书从源代码的角度深度解析了InnoDB

的体系结构、实现原理、工作机制，并给出了大量最佳实践。

2、《MySQL完全手册》

《MySQL完全手册》详细介绍了如何使用可定制的MySQL数据库管理系统支持健壮的、可靠的、任

务关键的应用程序。

3、《深入浅出mysql》

《深入浅出mysql》从数据库的基础、开发、优化、管理维护4个方面对MySQL进行了详细的介绍，

其中每一部分都成篇。

基础篇主要适合于MySQL的初学者，内容包括MySQL的安装与配置、SQL基础、MySQL支持的数

据类型、MySQL中的运算符、常用函数、图形化工具的使用等。

4、《 数据库索引设计与优化》

本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL

支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同。

5、《高性能MySQL》

《高性能MySQL》是分享MySQL实用经验的图书。它不但可以帮助MySQL初学者提高使用技巧，

更为有经验的MySQL DBA指出了开发高性能MySQL应用的途径。

 扩展资料：

《MySQL技术内幕:InnoDB存储引擎》是国内目前唯一的一本关于InnoDB的著作，由资深MySQL

专家亲自执笔，中外数据库专家联袂推荐，权威性毋庸置疑。

它能为读者设计和管理高性能、高可用的数据库系统提供绝佳的指导。注重实战，全书辅有大量的

案例，可操作性极强。全书首先全景式地介绍了MySQL独有的插件式存储引擎，分析了MySQL的各

种存储引擎的优势和应用环境。

参考资料：百度百科——MySQL技术内幕:InnoDB存储引擎

数据库应该怎么学习，零基础。

1、 自己在windows和linux上安装了mysql，自学linux的基础知识，学习mysql的最基础的知识，即怎么写sql，存储过程，表的设计等，从0到熟悉大概花了3个月 ，推荐《mysql入门很简单》。

2、系统地较为深入地学习mysql的sql优化，备份和恢复，参数优化，架构优化，硬件层面的优化，高可用方案，复制技术等等，这段时间你不一定能实际接触到这些，就像我当初那样，肯定没什么公司招一个小白。

我选择自己看书，推荐《高性能mysql》，里面所有的章节都需要看一遍，以现在的水平肯定看不懂，但需要知道大概怎么回事，为后续的找mysql初级dba的工作打一个铺垫，这个过程大概也需要3个月。

3、 纸上得来终觉浅，完成以上两步，我开始准备找一份mysql相关的工作，而不是天天用着excel表格做着select * from table_sb这样的工作。

当然我这么猥琐的人肯定不会裸辞，该画的电路板也一样画，业余时间开始投初级mysql dba的工作，并且不间断地学习，网上各种找mysql面试的相关题目（实际上我当时完全没有任何实战经验），陆续收到一些面试，凭借之前自学的mysql知识，开始胡乱吹牛，先混进去再说。

你不做mysql实际相关的工作，永远也不知道自己之前认知的db知识有多幼稚。

友情提示一点，一般公司都没有专职dba的，所以面试的时候一定要自信，其实你学了这么多，虽然毫无实战经验，理论知识很大概率比面试你的人牛，所以各种吹，我就这样真正进入初级dba的圈子（由于这时对linux还处于cd ls的水平，所以之前也根本没做过运维），这个边工作边找工作的过程又持续了2个月。

4、真正进入互联网，接触生产环境后，这是我进步最大的时候。

第一步需要将之前所学真正地应用起来，并且应用的过程中，再回头看之前的书籍，这时候需要真正去理解，而不是似是而非，一知半解。

这时再推荐《高性能mysql 第三版》，全本再看一遍，这时需要全部看懂，另外还有《mysql技术内幕：innodb存储引擎》等等。

总之这段时间就需要开始关注mysql一些细节了，比如db故障处理，高可用，负载均衡等等的具体实现了。

另外，linux的知识同步也要深入去学习，至少会写shell脚本，常见的linux知识等，我在这花了1年多；

5、 dba的工作一般是非常轻闲的，毕竟不是大公司，技术能力有限，该学的也学得差不多了，接触不到海量数据，高并发等比较锻炼人的场合，于是我又准备跳了。

于是来了公有云，现在每天运维万多个db实例，平均每天处理5+个紧急db故障，几乎mysql会遇到的问题，感觉都遇到了，能感觉到技术实力和经验也在每天都在积累，在进步。

但是感觉还是欠缺了很多，下一步就看你选择了，是再去研究源代码，底层原理的东西多点，还是数据库运维和应用多一点，就比如业界姜承尧，何登成与叶金荣的区别。

由于我的历史原因，对c++等几乎不懂，平时也用不到，所以看代码等事实际太累，于是我再去学mongodb，接了公司mongodb运维的活，算是在广度上的一个扩展，万一哪天mysql不行了呢

6、 总之，对于db小白来说，最重要的一点就是，学习的过程不能断。

PS 上面的方法比较野路子，适合没什么基础的童鞋，如果本来就是DBA，比如从oracle转到mysql，那么建议直接看mysql官方文档，而官方文档是db达到一定水平后必看，出问题时必查的权威文档。

显示全文