Difference between revisions of "CSC220 Lab 5 MySQL"
(Created page with ' =Creating the Databases= * This is just for completeness and does not have to be performed. * for all accounts from aa to ao, run the query CREATE DATABASE `220a-ac`CHARACTE…') |
(→Creating the Databases) |
||
| Line 1: | Line 1: | ||
| − | =Creating the Databases= | + | =Creating the Databases and the Users= |
| − | * This is just for completeness and does not have to be performed. | + | * This section is just for completeness and does not have to be performed. Only the admin of the database can create databases. Just read this section and start working on the next one. |
* for all accounts from aa to ao, run the query | * for all accounts from aa to ao, run the query | ||
| Line 9: | Line 9: | ||
* Similarly, create users with same name | * Similarly, create users with same name | ||
| + | |||
| + | INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv, | ||
| + | update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, | ||
| + | process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv, | ||
| + | show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, | ||
| + | execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, | ||
| + | create_routine_priv, alter_routine_priv, create_user_priv ) | ||
| + | VALUES ( 'localhost', '220a-ac', PASSWORD('AStrangeWordUsedForPassword'), | ||
| + | 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', | ||
| + | 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' ); | ||
| + | |||
| + | * Then make that user get all privileges on the database with the same name | ||
| + | |||
| + | DELETE FROM mysql.db WHERE USER='220a-ac' AND HOST='localhost'; | ||
| + | DELETE FROM mysql.tables_priv WHERE USER='220a-ac' AND HOST='localhost'; | ||
| + | DELETE FROM mysql.columns_priv WHERE USER='220a-ac' AND HOST='localhost'; | ||
| + | DELETE FROM mysql.procs_priv WHERE USER='220a-ac' AND HOST='localhost'; | ||
| + | INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, " | ||
| + | Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv," | ||
| + | Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv," | ||
| + | Create_routine_priv, Alter_routine_priv, Execute_priv) " | ||
| + | VALUES( 'localhost', '220a-ac', '220a-ac', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'," | ||
| + | 'Y', 'Y', 'Y', 'Y' ); | ||
| + | |||
| + | =Login to the Database= | ||
| + | |||
| + | <onlysmith> | ||
| + | * Point your browser to http://hadoop110.dyndns.org/phpmyadmin or http:/hadoop101.dyndns.org/phpmyadmin, and enter your 220a-xx account and your original password (from the account forms given to you at the beginning of the semester). | ||
| + | </onlysmith> | ||
| + | |||
| + | |||
| + | =Create Tables= | ||
| + | |||
| + | * You are going to create 3 tables: '''users''', '''orders''', and '''items''' | ||
| + | ** '''users''' contains an Id and a name field. | ||
| + | ** '''orders''' contains an Id for the order, an Id for the user ordering, and an '''item''' ordered | ||
| + | ** '''items''' contains and Id, a name, and a price | ||
| + | |||
| + | ==Create the Tables== | ||
| + | |||
| + | * | ||
Revision as of 10:17, 12 October 2010
Contents
Creating the Databases and the Users
- This section is just for completeness and does not have to be performed. Only the admin of the database can create databases. Just read this section and start working on the next one.
- for all accounts from aa to ao, run the query
CREATE DATABASE `220a-ac`CHARACTER SET utf8 COLLATE utf8_general_ci;
- Similarly, create users with same name
INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv,
update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv,
process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv,
show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv,
execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv,
create_routine_priv, alter_routine_priv, create_user_priv )
VALUES ( 'localhost', '220a-ac', PASSWORD('AStrangeWordUsedForPassword'),
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );
- Then make that user get all privileges on the database with the same name
DELETE FROM mysql.db WHERE USER='220a-ac' AND HOST='localhost';
DELETE FROM mysql.tables_priv WHERE USER='220a-ac' AND HOST='localhost';
DELETE FROM mysql.columns_priv WHERE USER='220a-ac' AND HOST='localhost';
DELETE FROM mysql.procs_priv WHERE USER='220a-ac' AND HOST='localhost';
INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, "
Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv,"
Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv,"
Create_routine_priv, Alter_routine_priv, Execute_priv) "
VALUES( 'localhost', '220a-ac', '220a-ac', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',"
'Y', 'Y', 'Y', 'Y' );
Login to the Database
Create Tables
- You are going to create 3 tables: users, orders, and items
- users contains an Id and a name field.
- orders contains an Id for the order, an Id for the user ordering, and an item ordered
- items contains and Id, a name, and a price
