首页 / MYSQL / MySQL入门(一)
MySQL入门(一)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL入门(一),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13237字,纯文字阅读大概需要19分钟。
内容图文
![MySQL入门(一)](/upload/InfoBanner/zyjiaocheng/1335/0d4cf0ee8ce948af9beabbd67c8ac06d.jpg)
本学习笔记参考《MySQL必知必会》和官方手册MySQL 5.6 Reference Manual
一、MySQL初体验
我的 mysql 版本是5.6.24,利用 apt-get 安装在 Ubuntu-12.04LTS上。
$ 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
########################
CREATETABLE customers
(
cust_id intNOTNULL AUTO_INCREMENT,
cust_name char(50) NOTNULL ,
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 ,
PRIMARYKEY (cust_id)
) ENGINE=InnoDB;
#########################
# Create orderitems table
#########################
CREATETABLE orderitems
(
order_num intNOTNULL ,
order_item intNOTNULL ,
prod_id char(10) NOTNULL ,
quantity intNOTNULL ,
item_price decimal(8,2) NOTNULL ,
PRIMARYKEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# Create orders table
#####################
CREATETABLE orders
(
order_num intNOTNULL AUTO_INCREMENT,
order_date datetime NOTNULL ,
cust_id intNOTNULL ,
PRIMARYKEY (order_num)
) ENGINE=InnoDB;
#######################
# Create products table
#######################
CREATETABLE products
(
prod_id char(10) NOTNULL,
vend_id intNOTNULL ,
prod_name char(255) NOTNULL ,
prod_price decimal(8,2) NOTNULL ,
prod_desc text NULL ,
PRIMARYKEY(prod_id)
) ENGINE=InnoDB;
######################
# Create vendors table
######################
CREATETABLE vendors
(
vend_id intNOTNULL AUTO_INCREMENT,
vend_name char(50) NOTNULL ,
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 ,
PRIMARYKEY (vend_id)
) ENGINE=InnoDB;
###########################
# Create productnotes table
###########################
CREATETABLE productnotes
(
note_id intNOTNULL AUTO_INCREMENT,
prod_id char(10) NOTNULL,
note_date datetime NOTNULL,
note_text text NULL ,
PRIMARYKEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
#####################
# Define foreign keys
#####################
ALTERTABLE orderitems ADDCONSTRAINT fk_orderitems_orders FOREIGNKEY (order_num) REFERENCES orders (order_num);ALTERTABLE orderitems ADDCONSTRAINT fk_orderitems_products FOREIGNKEY (prod_id) REFERENCES products (prod_id);ALTERTABLE orders ADDCONSTRAINT fk_orders_customers FOREIGNKEY (cust_id) REFERENCES customers (cust_id);ALTERTABLE products ADDCONSTRAINT fk_products_vendors FOREIGNKEY (vend_id) REFERENCES vendors (vend_id);
populate.sql
########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################
##########################
# Populate customers table
##########################
INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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
########################
INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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
#########################
INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO 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‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘OL1‘, 1002, ‘Oil can‘, 8.99, ‘Oil can, red‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘FU1‘, 1002, ‘Fuses‘, 3.42, ‘1 dozen, extra long‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘SLING‘, 1003, ‘Sling‘, 4.49, ‘Sling, one size fits all‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘TNT1‘, 1003, ‘TNT (1 stick)‘, 2.50, ‘TNT, red, single stick‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘TNT2‘, 1003, ‘TNT (5 sticks)‘, 10, ‘TNT, red, pack of 10 sticks‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘FB‘, 1003, ‘Bird seed‘, 10, ‘Large bag (suitable for road runners)‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘FC‘, 1003, ‘Carrots‘, 2.50, ‘Carrots (rabbit hunting season only)‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘SAFE‘, 1003, ‘Safe‘, 50, ‘Safe with combination lock‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘DTNTR‘, 1003, ‘Detonator‘, 13, ‘Detonator (plunger powered), fuses not included‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘JP1000‘, 1005, ‘JetPack 1000‘, 35, ‘JetPack 1000, intended for single use‘);INSERTINTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘JP2000‘, 1005, ‘JetPack 2000‘, 55, ‘JetPack 2000, multi-use‘);
#######################
# Populate orders table
#######################
INSERTINTO orders(order_num, order_date, cust_id)
VALUES(20005, ‘2005-09-01‘, 10001);INSERTINTO orders(order_num, order_date, cust_id)
VALUES(20006, ‘2005-09-12‘, 10003);INSERTINTO orders(order_num, order_date, cust_id)
VALUES(20007, ‘2005-09-30‘, 10004);INSERTINTO orders(order_num, order_date, cust_id)
VALUES(20008, ‘2005-10-03‘, 10005);INSERTINTO orders(order_num, order_date, cust_id)
VALUES(20009, ‘2005-10-08‘, 10001);
###########################
# Populate orderitems table
###########################
INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, ‘ANV01‘, 10, 5.99);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, ‘ANV02‘, 3, 9.99);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, ‘TNT2‘, 5, 10);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, ‘FB‘, 1, 10);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, ‘JP2000‘, 1, 55);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, ‘TNT2‘, 100, 10);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, ‘FC‘, 50, 2.50);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, ‘FB‘, 1, 10);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, ‘OL1‘, 1, 8.99);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, ‘SLING‘, 1, 4.49);INSERTINTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, ‘ANV03‘, 1, 14.99);
#############################
# Populate productnotes table
#############################
INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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).‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, ‘SLING‘, ‘2005-09-02‘,
‘Shipped unassembled, requires common tools (including oversized hammer).‘
);INSERTINTO 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.‘
);INSERTINTO 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.‘
);INSERTINTO 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;
原文:http://blog.csdn.net/qiao_yihan/article/details/46387281
内容总结
以上是互联网集市为您收集整理的MySQL入门(一)全部内容,希望文章能够帮你解决MySQL入门(一)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。