目录
安装及基本使用
1 安装时的要点
2 使用自己的数据库
基础语法
实践项目中遇到的问题
1 comment添加备注
2 Can't connect to MySQL server on 'localhost' (10061)
3 Duplicate entry '1' for key 'PRIMARY'
安装及基本使用
1 安装时的要点
安装时一定要修改路径，记住数据被存放的位置，否则会卸载不干净，无法删除。在Java后阶视频里有具体安装方法，还有一个安装文档，不过不详细，最好还是看视频安装不会出错。不过目前mysql已经升级了好多版本，视频中的方法可能有点不适用了，这是可以搜博客记录。
2 使用自己的数据库
在初始界面是不要用自带的，一定要新建数据库，下图是初始界面的数据库
新建一个mybase数据库然后在mybase数据库中进行操作
把界面刷新一下便可以显示出来自己新建的数据库，一定要注意检查是否是在自己新建的数据库中进行的操作。
基础语法
此部分以MySQL必知必会为，在此附上用到的建表数据：
创建表数据部分：
######################## # 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 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.' );
实践项目中遇到的问题
1 comment添加备注
在创建表时在设定列的属性时添加备注可以方便后面理解
username VARCHAR(50) COMMENT '登录账号'
显示出备注信息：
SHOW FULL COLUMNS FROM users;
2 Can't connect to MySQL server on 'localhost' (10061)
在启动MYSQL时出现问题：“ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)”。在cmd模式下输入net start mysql即可，有时弹出拒绝访问，管理员身份运行即可
3 Duplicate entry '1' for key 'PRIMARY'
在MySQL调试代码出现如下问题：
INSERT INTO product (id,pname,price) VALUES (1,'笔记本',5555.99);
Query : insert into product (id,pname,price) values (1,'笔记本',5555.99)
Error Code : 1062
Duplicate entry '1' for key 'PRIMARY'
后来发现是由于重复执行语句造成的，设定了id为PRIMARY KEY的格式，重复执行就会造成错误，解决方法可以删除表再重新执行就行
0