|
Last
Digit of your Student ID % 5 |
Designated MySQL for Web Programming Exercises |
|
0 |
sanluis |
|
1 |
blanca |
|
2 |
crestone |
|
3 |
shavano |
|
4 |
wetterhorn |
The following shows a typical Linux/Window installation. You may want to replace the instructions with those new versions from mysql web site.
Linux Installation
Enter the appropriate number:
[3] 1
Choose 1.
Type 'help' for help.
mysql> create DATABASE chDB;
Query OK, 1 row affected (0.01 sec)
mysql> use cs301db;
Database changed
mysql> create table inventory (cookie char(20), unitPrice
decimal(8,2) NOT NULL
, noOfBoxes integer);
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-------------------+
| Tables in cs301db |
+-------------------+
| inventory
|
| john
|
+-------------------+
2 rows in set (0.00 sec)
mysql> show columns from inventory;
+-----------+--------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| cookie | char(20)
| YES | | NULL |
|
| unitPrice | decimal(8,2) |
| | 0.00 |
|
| noOfBoxes | int(11)
| YES | | NULL |
|
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into inventory (cookie, unitPrice,
noOfBoxes) values
-> ('mm', 1.5, 300);
Query OK, 1 row affected (0.00 sec)
Note that the following insert is brought back by
hitting up arrow key twice.
All the mysql commands are saved in the buffer.
You can use arrow key to bring them back.
mysql> insert into inventory (cookie, unitPrice, noOfBoxes)
values
-> ('chocolate', 2.3, 500);
Query OK, 1 row affected (0.00 sec)
mysql> insert into inventory (cookie, unitPrice, noOfBoxes)
values
-> ('mint', 1.0, 400);
Query OK, 1 row affected (0.00 sec)
mysql> insert into inventory (cookie, unitPrice, noOfBoxes)
values
-> ('butter', 0.8, 200);
Query OK, 1 row affected (0.00 sec)
mysql> insert into inventory (cookie, unitPrice, noOfBoxes)
values
-> ('peanut', 0.5, 1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from inventory;
+-----------+-----------+-----------+
| cookie | unitPrice | noOfBoxes
|
+-----------+-----------+-----------+
| mm |
1.50 | 300 |
| chocolate | 2.30
| 500 |
| mint |
1.00 | 400 |
| butter |
0.80 | 200 |
| peanut |
0.50 | 1000 |
+-----------+-----------+-----------+
mysql> select * from inventory where unitPrice>1.3;
+-----------+-----------+-----------+
| cookie | unitPrice | noOfBoxes
|
+-----------+-----------+-----------+
| mm |
1.50 | 300 |
| chocolate | 2.30
| 500 |
+-----------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> update inventory set unitPrice=1.40 where
cookie='mint';
Query OK, 1 row affected (0.00 sec)
mysql> select * from inventory where unitPrice>1.3;
+-----------+-----------+-----------+
| cookie | unitPrice | noOfBoxes
|
+-----------+-----------+-----------+
| mm |
1.50 | 300 |
| chocolate | 2.30
| 500 |
| mint |
1.40 | 400 |
+-----------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> delete from inventory where cookie='mint';
mysql> create table customer (firstName char(20),
middleInitial char(1),
-> lastName char(20), email char(30),
phoneNo char(15), address char(60),
-> city char(20), state char(2),
country char(20), creditCard char(16),
-> expireDate char(5));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into customer (firstName, middleInitial,
lastName, email,
-> phoneNo, address, city, state,
country, creditCard, expireDate) values
-> ('Edward', ' ', 'Chow', 'chow@cs.uccs.edu',
'(719)262-3110',
-> '1420 Austin Bluffs Parkway',
'Colorado Springs', 'CO', 'USA',
-> '1111222233334444', '11/02');
Query OK, 1 row affected (0.02 sec)
Ohno! We forgot to add zip code column.
Luckily we have alter table statement.
mysql> alter table customer add column zip char(10)
after state;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> update customer SET zip='80933-7150' where
lastName='Chow';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings:
0
mysql> select * from customer;
+-----------+---------------+----------+------------------+---------------+-----
-----------------------+------------------+-------+------------+---------+------
------------+------------+
| firstName | middleInitial | lastName | email
| phoneNo | addr
ess
| city
| state | zip | country | credi
tCard | expireDate
|
+-----------+---------------+----------+------------------+---------------+-----
-----------------------+------------------+-------+------------+---------+------
------------+------------+
| Edward |
| Chow | chow@cs.uccs.edu | (719)262-3110 | 1420
Austin Bluffs Parkway | Colorado Springs |
CO | 80933-7150 | USA | 11112
22233334444 | 11/02
|
+-----------+---------------+----------+------------------+---------------+-----
-----------------------+------------------+-------+------------+---------+------
------------+------------+
1 row in set (0.01 sec)
We can use drop and delete
command to remove tables or rows of a table.
mysql>drop table inventory,
customer;
mysql>delete from table
where cookie='mm';
mysql> quit
Bye
Welcome to the MySQL monitor. Commands end with
; or \g.
Your MySQL connection id is 24 to server version:
3.22.32
Type 'help' for help.
mysql> GRANT all privileges
on *.* to cs301@"%.uccs.edu"
-> identified
by 'yyy';
Query OK, 0 rows affected (0.01 sec)
Note that here cs301 does not have to be an actual account on uccs.edu machines.
to access SQL server at crestone.uccs.edu with user
account 'cs301' and password 'yyy'
we use the following mysql command, here the
"yyy" is the password
wetterhorn.uccs.edu> mysql
-h crestone -u cs301 -pyyy
Welcome to the MySQL monitor. Commands end
with ; or \g.
Your MySQL connection id is 25 to server version:
3.22.32
Type 'help' for help.
mysql>
If you entered the password wrong, you will get
wetterhorn.uccs.edu> mysql
-h crestone -u cs301 -pwrong
ERROR 1045: Access denied
for user: 'cs301@wetterhorn.uccs.edu' (Using password:
YES)