第一章 熟悉 mysql workbench

一、创建mysql 连接

二、介绍操作面板

 三、创建数据库

  1 # 创建数据库
  2 `DROP DATABASE IF EXISTS `sql_invoicing`;
  3 CREATE DATABASE `sql_invoicing`; 
  4 USE `sql_invoicing`;
  5 
  6 SET NAMES utf8 ;
  7 SET character_set_client = utf8 ;
  8 
  9 CREATE TABLE `payment_methods` (
 10   `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,
 11   `name` varchar(50) NOT NULL,
 12   PRIMARY KEY (`payment_method_id`)
 13 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 14 INSERT INTO `payment_methods` VALUES (1,'Credit Card');
 15 INSERT INTO `payment_methods` VALUES (2,'Cash');
 16 INSERT INTO `payment_methods` VALUES (3,'PayPal');
 17 INSERT INTO `payment_methods` VALUES (4,'Wire Transfer');
 18 
 19 CREATE TABLE `clients` (
 20   `client_id` int(11) NOT NULL,
 21   `name` varchar(50) NOT NULL,
 22   `address` varchar(50) NOT NULL,
 23   `city` varchar(50) NOT NULL,
 24   `state` char(2) NOT NULL,
 25   `phone` varchar(50) DEFAULT NULL,
 26   PRIMARY KEY (`client_id`)
 27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 28 INSERT INTO `clients` VALUES (1,'Vinte','3 Nevada Parkway','Syracuse','NY','315-252-7305');
 29 INSERT INTO `clients` VALUES (2,'Myworks','34267 Glendale Parkway','Huntington','WV','304-659-1170');
 30 INSERT INTO `clients` VALUES (3,'Yadel','096 Pawling Parkway','San Francisco','CA','415-144-6037');
 31 INSERT INTO `clients` VALUES (4,'Kwideo','81674 Westerfield Circle','Waco','TX','254-750-0784');
 32 INSERT INTO `clients` VALUES (5,'Topiclounge','0863 Farmco Road','Portland','OR','971-888-9129');
 33 
 34 CREATE TABLE `invoices` (
 35   `invoice_id` int(11) NOT NULL,
 36   `number` varchar(50) NOT NULL,
 37   `client_id` int(11) NOT NULL,
 38   `invoice_total` decimal(9,2) NOT NULL,
 39   `payment_total` decimal(9,2) NOT NULL DEFAULT '0.00',
 40   `invoice_date` date NOT NULL,
 41   `due_date` date NOT NULL,
 42   `payment_date` date DEFAULT NULL,
 43   PRIMARY KEY (`invoice_id`),
 44   KEY `FK_client_id` (`client_id`),
 45   CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE
 46 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 47 INSERT INTO `invoices` VALUES (1,'91-953-3396',2,101.79,0.00,'2019-03-09','2019-03-29',NULL);
 48 INSERT INTO `invoices` VALUES (2,'03-898-6735',5,175.32,8.18,'2019-06-11','2019-07-01','2019-02-12');
 49 INSERT INTO `invoices` VALUES (3,'20-228-0335',5,147.99,0.00,'2019-07-31','2019-08-20',NULL);
 50 INSERT INTO `invoices` VALUES (4,'56-934-0748',3,152.21,0.00,'2019-03-08','2019-03-28',NULL);
 51 INSERT INTO `invoices` VALUES (5,'87-052-3121',5,169.36,0.00,'2019-07-18','2019-08-07',NULL);
 52 INSERT INTO `invoices` VALUES (6,'75-587-6626',1,157.78,74.55,'2019-01-29','2019-02-18','2019-01-03');
 53 INSERT INTO `invoices` VALUES (7,'68-093-9863',3,133.87,0.00,'2019-09-04','2019-09-24',NULL);
 54 INSERT INTO `invoices` VALUES (8,'78-145-1093',1,189.12,0.00,'2019-05-20','2019-06-09',NULL);
 55 INSERT INTO `invoices` VALUES (9,'77-593-0081',5,172.17,0.00,'2019-07-09','2019-07-29',NULL);
 56 INSERT INTO `invoices` VALUES (10,'48-266-1517',1,159.50,0.00,'2019-06-30','2019-07-20',NULL);
 57 INSERT INTO `invoices` VALUES (11,'20-848-0181',3,126.15,0.03,'2019-01-07','2019-01-27','2019-01-11');
 58 INSERT INTO `invoices` VALUES (13,'41-666-1035',5,135.01,87.44,'2019-06-25','2019-07-15','2019-01-26');
 59 INSERT INTO `invoices` VALUES (15,'55-105-9605',3,167.29,80.31,'2019-11-25','2019-12-15','2019-01-15');
 60 INSERT INTO `invoices` VALUES (16,'10-451-8824',1,162.02,0.00,'2019-03-30','2019-04-19',NULL);
 61 INSERT INTO `invoices` VALUES (17,'33-615-4694',3,126.38,68.10,'2019-07-30','2019-08-19','2019-01-15');
 62 INSERT INTO `invoices` VALUES (18,'52-269-9803',5,180.17,42.77,'2019-05-23','2019-06-12','2019-01-08');
 63 INSERT INTO `invoices` VALUES (19,'83-559-4105',1,134.47,0.00,'2019-11-23','2019-12-13',NULL);
 64 
 65 CREATE TABLE `payments` (
 66   `payment_id` int(11) NOT NULL AUTO_INCREMENT,
 67   `client_id` int(11) NOT NULL,
 68   `invoice_id` int(11) NOT NULL,
 69   `date` date NOT NULL,
 70   `amount` decimal(9,2) NOT NULL,
 71   `payment_method` tinyint(4) NOT NULL,
 72   PRIMARY KEY (`payment_id`),
 73   KEY `fk_client_id_idx` (`client_id`),
 74   KEY `fk_invoice_id_idx` (`invoice_id`),
 75   KEY `fk_payment_payment_method_idx` (`payment_method`),
 76   CONSTRAINT `fk_payment_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE,
 77   CONSTRAINT `fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON UPDATE CASCADE,
 78   CONSTRAINT `fk_payment_payment_method` FOREIGN KEY (`payment_method`) REFERENCES `payment_methods` (`payment_method_id`)
 79 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 80 INSERT INTO `payments` VALUES (1,5,2,'2019-02-12',8.18,1);
 81 INSERT INTO `payments` VALUES (2,1,6,'2019-01-03',74.55,1);
 82 INSERT INTO `payments` VALUES (3,3,11,'2019-01-11',0.03,1);
 83 INSERT INTO `payments` VALUES (4,5,13,'2019-01-26',87.44,1);
 84 INSERT INTO `payments` VALUES (5,3,15,'2019-01-15',80.31,1);
 85 INSERT INTO `payments` VALUES (6,3,17,'2019-01-15',68.10,1);
 86 INSERT INTO `payments` VALUES (7,5,18,'2019-01-08',32.77,1);
 87 INSERT INTO `payments` VALUES (8,5,18,'2019-01-08',10.00,2);
 88 
 89 
 90 DROP DATABASE IF EXISTS `sql_store`;
 91 CREATE DATABASE `sql_store`;
 92 USE `sql_store`;
 93 
 94 CREATE TABLE `products` (
 95   `product_id` int(11) NOT NULL AUTO_INCREMENT,
 96   `name` varchar(50) NOT NULL,
 97   `quantity_in_stock` int(11) NOT NULL,
 98   `unit_price` decimal(4,2) NOT NULL,
 99   PRIMARY KEY (`product_id`)
100 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
101 INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
102 INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
103 INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
104 INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
105 INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
106 INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
107 INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
108 INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
109 INSERT INTO `products` VALUES (9,'Longan',67,2.26);
110 INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);
111 
112 
113 CREATE TABLE `shippers` (
114   `shipper_id` smallint(6) NOT NULL AUTO_INCREMENT,
115   `name` varchar(50) NOT NULL,
116   PRIMARY KEY (`shipper_id`)
117 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
118 INSERT INTO `shippers` VALUES (1,'Hettinger LLC');
119 INSERT INTO `shippers` VALUES (2,'Schinner-Predovic');
120 INSERT INTO `shippers` VALUES (3,'Satterfield LLC');
121 INSERT INTO `shippers` VALUES (4,'Mraz, Renner and Nolan');
122 INSERT INTO `shippers` VALUES (5,'Waters, Mayert and Prohaska');
123 
124 
125 CREATE TABLE `customers` (
126   `customer_id` int(11) NOT NULL AUTO_INCREMENT,
127   `first_name` varchar(50) NOT NULL,
128   `last_name` varchar(50) NOT NULL,
129   `birth_date` date DEFAULT NULL,
130   `phone` varchar(50) DEFAULT NULL,
131   `address` varchar(50) NOT NULL,
132   `city` varchar(50) NOT NULL,
133   `state` char(2) NOT NULL,
134   `points` int(11) NOT NULL DEFAULT '0',
135   PRIMARY KEY (`customer_id`)
136 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
137 INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
138 INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
139 INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
140 INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
141 INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
142 INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
143 INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
144 INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
145 INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
146 INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);
147 
148 
149 CREATE TABLE `order_statuses` (
150   `order_status_id` tinyint(4) NOT NULL,
151   `name` varchar(50) NOT NULL,
152   PRIMARY KEY (`order_status_id`)
153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
154 INSERT INTO `order_statuses` VALUES (1,'Processed');
155 INSERT INTO `order_statuses` VALUES (2,'Shipped');
156 INSERT INTO `order_statuses` VALUES (3,'Delivered');
157 
158 
159 CREATE TABLE `orders` (
160   `order_id` int(11) NOT NULL AUTO_INCREMENT,
161   `customer_id` int(11) NOT NULL,
162   `order_date` date NOT NULL,
163   `status` tinyint(4) NOT NULL DEFAULT '1',
164   `comments` varchar(2000) DEFAULT NULL,
165   `shipped_date` date DEFAULT NULL,
166   `shipper_id` smallint(6) DEFAULT NULL,
167   PRIMARY KEY (`order_id`),
168   KEY `fk_orders_customers_idx` (`customer_id`),
169   KEY `fk_orders_shippers_idx` (`shipper_id`),
170   KEY `fk_orders_order_statuses_idx` (`status`),
171   CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON UPDATE CASCADE,
172   CONSTRAINT `fk_orders_order_statuses` FOREIGN KEY (`status`) REFERENCES `order_statuses` (`order_status_id`) ON UPDATE CASCADE,
173   CONSTRAINT `fk_orders_shippers` FOREIGN KEY (`shipper_id`) REFERENCES `shippers` (`shipper_id`) ON UPDATE CASCADE
174 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
175 INSERT INTO `orders` VALUES (1,6,'2019-01-30',1,NULL,NULL,NULL);
176 INSERT INTO `orders` VALUES (2,7,'2018-08-02',2,NULL,'2018-08-03',4);
177 INSERT INTO `orders` VALUES (3,8,'2017-12-01',1,NULL,NULL,NULL);
178 INSERT INTO `orders` VALUES (4,2,'2017-01-22',1,NULL,NULL,NULL);
179 INSERT INTO `orders` VALUES (5,5,'2017-08-25',2,'','2017-08-26',3);
180 INSERT INTO `orders` VALUES (6,10,'2018-11-18',1,'Aliquam erat volutpat. In congue.',NULL,NULL);
181 INSERT INTO `orders` VALUES (7,2,'2018-09-22',2,NULL,'2018-09-23',4);
182 INSERT INTO `orders` VALUES (8,5,'2018-06-08',1,'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.',NULL,NULL);
183 INSERT INTO `orders` VALUES (9,10,'2017-07-05',2,'Nulla mollis molestie lorem. Quisque ut erat.','2017-07-06',1);
184 INSERT INTO `orders` VALUES (10,6,'2018-04-22',2,NULL,'2018-04-23',2);
185 
186 
187 CREATE TABLE `order_items` (
188   `order_id` int(11) NOT NULL AUTO_INCREMENT,
189   `product_id` int(11) NOT NULL,
190   `quantity` int(11) NOT NULL,
191   `unit_price` decimal(4,2) NOT NULL,
192   PRIMARY KEY (`order_id`,`product_id`),
193   KEY `fk_order_items_products_idx` (`product_id`),
194   CONSTRAINT `fk_order_items_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,
195   CONSTRAINT `fk_order_items_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
196 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
197 INSERT INTO `order_items` VALUES (1,4,4,3.74);
198 INSERT INTO `order_items` VALUES (2,1,2,9.10);
199 INSERT INTO `order_items` VALUES (2,4,4,1.66);
200 INSERT INTO `order_items` VALUES (2,6,2,2.94);
201 INSERT INTO `order_items` VALUES (3,3,10,9.12);
202 INSERT INTO `order_items` VALUES (4,3,7,6.99);
203 INSERT INTO `order_items` VALUES (4,10,7,6.40);
204 INSERT INTO `order_items` VALUES (5,2,3,9.89);
205 INSERT INTO `order_items` VALUES (6,1,4,8.65);
206 INSERT INTO `order_items` VALUES (6,2,4,3.28);
207 INSERT INTO `order_items` VALUES (6,3,4,7.46);
208 INSERT INTO `order_items` VALUES (6,5,1,3.45);
209 INSERT INTO `order_items` VALUES (7,3,7,9.17);
210 INSERT INTO `order_items` VALUES (8,5,2,6.94);
211 INSERT INTO `order_items` VALUES (8,8,2,8.59);
212 INSERT INTO `order_items` VALUES (9,6,5,7.28);
213 INSERT INTO `order_items` VALUES (10,1,10,6.01);
214 INSERT INTO `order_items` VALUES (10,9,9,4.28);
215 
216 CREATE TABLE `sql_store`.`order_item_notes` (
217   `note_id` INT NOT NULL,
218   `order_Id` INT NOT NULL,
219   `product_id` INT NOT NULL,
220   `note` VARCHAR(255) NOT NULL,
221   PRIMARY KEY (`note_id`));
222 
223 INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('1', '1', '2', 'first note');
224 INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('2', '1', '2', 'second note');
225 
226 
227 DROP DATABASE IF EXISTS `sql_hr`;
228 CREATE DATABASE `sql_hr`;
229 USE `sql_hr`;
230 
231 
232 CREATE TABLE `offices` (
233   `office_id` int(11) NOT NULL,
234   `address` varchar(50) NOT NULL,
235   `city` varchar(50) NOT NULL,
236   `state` varchar(50) NOT NULL,
237   PRIMARY KEY (`office_id`)
238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
239 INSERT INTO `offices` VALUES (1,'03 Reinke Trail','Cincinnati','OH');
240 INSERT INTO `offices` VALUES (2,'5507 Becker Terrace','New York City','NY');
241 INSERT INTO `offices` VALUES (3,'54 Northland Court','Richmond','VA');
242 INSERT INTO `offices` VALUES (4,'08 South Crossing','Cincinnati','OH');
243 INSERT INTO `offices` VALUES (5,'553 Maple Drive','Minneapolis','MN');
244 INSERT INTO `offices` VALUES (6,'23 North Plaza','Aurora','CO');
245 INSERT INTO `offices` VALUES (7,'9658 Wayridge Court','Boise','ID');
246 INSERT INTO `offices` VALUES (8,'9 Grayhawk Trail','New York City','NY');
247 INSERT INTO `offices` VALUES (9,'16862 Westend Hill','Knoxville','TN');
248 INSERT INTO `offices` VALUES (10,'4 Bluestem Parkway','Savannah','GA');
249 
250 
251 
252 CREATE TABLE `employees` (
253   `employee_id` int(11) NOT NULL,
254   `first_name` varchar(50) NOT NULL,
255   `last_name` varchar(50) NOT NULL,
256   `job_title` varchar(50) NOT NULL,
257   `salary` int(11) NOT NULL,
258   `reports_to` int(11) DEFAULT NULL,
259   `office_id` int(11) NOT NULL,
260   PRIMARY KEY (`employee_id`),
261   KEY `fk_employees_offices_idx` (`office_id`),
262   KEY `fk_employees_employees_idx` (`reports_to`),
263   CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),
264   CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE
265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
266 INSERT INTO `employees` VALUES (37270,'Yovonnda','Magrannell','Executive Secretary',63996,NULL,10);
267 INSERT INTO `employees` VALUES (33391,'D\'arcy','Nortunen','Account Executive',62871,37270,1);
268 INSERT INTO `employees` VALUES (37851,'Sayer','Matterson','Statistician III',98926,37270,1);
269 INSERT INTO `employees` VALUES (40448,'Mindy','Crissil','Staff Scientist',94860,37270,1);
270 INSERT INTO `employees` VALUES (56274,'Keriann','Alloisi','VP Marketing',110150,37270,1);
271 INSERT INTO `employees` VALUES (63196,'Alaster','Scutchin','Assistant Professor',32179,37270,2);
272 INSERT INTO `employees` VALUES (67009,'North','de Clerc','VP Product Management',114257,37270,2);
273 INSERT INTO `employees` VALUES (67370,'Elladine','Rising','Social Worker',96767,37270,2);
274 INSERT INTO `employees` VALUES (68249,'Nisse','Voysey','Financial Advisor',52832,37270,2);
275 INSERT INTO `employees` VALUES (72540,'Guthrey','Iacopetti','Office Assistant I',117690,37270,3);
276 INSERT INTO `employees` VALUES (72913,'Kass','Hefferan','Computer Systems Analyst IV',96401,37270,3);
277 INSERT INTO `employees` VALUES (75900,'Virge','Goodrum','Information Systems Manager',54578,37270,3);
278 INSERT INTO `employees` VALUES (76196,'Mirilla','Janowski','Cost Accountant',119241,37270,3);
279 INSERT INTO `employees` VALUES (80529,'Lynde','Aronson','Junior Executive',77182,37270,4);
280 INSERT INTO `employees` VALUES (80679,'Mildrid','Sokale','Geologist II',67987,37270,4);
281 INSERT INTO `employees` VALUES (84791,'Hazel','Tarbert','General Manager',93760,37270,4);
282 INSERT INTO `employees` VALUES (95213,'Cole','Kesterton','Pharmacist',86119,37270,4);
283 INSERT INTO `employees` VALUES (96513,'Theresa','Binney','Food Chemist',47354,37270,5);
284 INSERT INTO `employees` VALUES (98374,'Estrellita','Daleman','Staff Accountant IV',70187,37270,5);
285 INSERT INTO `employees` VALUES (115357,'Ivy','Fearey','Structural Engineer',92710,37270,5);
286 
287 
288 DROP DATABASE IF EXISTS `sql_inventory`;
289 CREATE DATABASE `sql_inventory`;
290 USE `sql_inventory`;
291 
292 
293 CREATE TABLE `products` (
294   `product_id` int(11) NOT NULL AUTO_INCREMENT,
295   `name` varchar(50) NOT NULL,
296   `quantity_in_stock` int(11) NOT NULL,
297   `unit_price` decimal(4,2) NOT NULL,
298   PRIMARY KEY (`product_id`)
299 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
300 INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
301 INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
302 INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
303 INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
304 INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
305 INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
306 INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
307 INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
308 INSERT INTO `products` VALUES (9,'Longan',67,2.26);
309 INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);

 四、介绍数据库对象

 五、查看表

 第二章保护数据库

一、创建用户

 1 create user xiaohei;　　　　# 创建用户'xiaohei'
 2 
 3 create user xiaohei@127.0.0.1;　　　　#创建用户'xiaohei'，指定使用127.0.0.1 IP地址连接
 4 
 5 create user xiaohei@localhost;　　　　#创建用户'xiaohei'，指定使用localhost主机名连接
 6 
 7 create user xiaohei@baidu.com;　　　　#创建用户'xiaohei'，指定使用baidu.com域连接，但是无法从baidu.com的子网域连接
 8 
 9 create user xiaohei@‘%.baidu.com’;　　　　#创建用户'xiaohei'，指定使用baidu.com域连接，还可以用baidu.com的子网域连接，其中#.表示任何子网域
10 
11 create user xiaohei identified by '123456';　　　　#创建用户'xiaohei'，创建密码'123456'　　IDENTIFIED BY   创建密码

二、查看用户

第一种：通过语句查看

 1 select * from mysql.user;　　　　#mysql数据库名　　user表名 

 第二种：通过查看数据库配置的方式

三、删除用户

 1 drop user xiaohong@localhost;　　　　#删除用户时要把用户在添加时的方式全部加上 

四、修改密码

第一种方式：用语句方式

 1 set password for xiaohei = '1234'; 

第二种方式：通过管理数据库

 五、授予权限

1.授予读写权限：

1 grant select, insert, update, delete, execute on sql_store.* to moon_app;　　#sql_store数据库名；　　*指所有表；　　sql_store.customers指定sql_store数据库下customers表；　　on 指定位置；　　to 指定用户

2.管理员的所有权限（创建数据库，表等）：

grant ALL on *.* to john;　　　　#ALL是mysql中最高权限；*.*指所有数据下的所有表。

六、查看权限

查看用户权限：

show grants for xiaohei;　　　　#for指定用户

通过管理设置查看用户权限：

 七、撤销权限

revoke create view on sql_store.* from xiaohei;　　　　#from指定用户