MySQL operation
GW: created database "adawson" - see email: 29 Jan 2006
**** Created a database "adawson"
See p733
Internet & World Wide Web
How to program
by H.M.Deitel, P.J.Deitel, A.B.Goldberg
3rd Edition
PearsonEd, Prentice Hall
From my own Koha notes:
From a Linux shell:
mysql -u root -p
so
mysql -u adawson -p
MySQL will then prompt for the password (see C:\AD)
When you're in, you get the MySQL prompt:
mysql>
To quit MySQL
type: exit
To find out which version of MySQL you are using:
mysql> status
mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)
To connect user adawson to database adawson:
$ mysql -h localhost -u adawson -p adawson
MySQL Tutorial
SQL Hint Sheet
Creating the database is the easy part, but at this point it's empty, as SHOW TABLES tells you:
mysql> show tables;
Empty set (0.01 sec)
**********
Note: if you forget to enter the semi-colon in the command above, the prompt will change to a -> to remind you that more input is needed.
**********
see p364 of Web Programming in Python - Techniques for integrating Linux, Apache and MySQL by Thiruvathukal et al, Prentice Hall, 2002, ISBN: 0-13-041065-9
Create a table called Books to stores all data on the college library books, e.g. Title, Author, Subject (Computer Science, ESL, Geography), Call number, Publisher, Year published, Keywords
ISBN, Format (DVD, video, book, article):
mysql> CREATE TABLE Books (title VARCHAR(20), author VARCHAR(20),subject VARCHAR(20), isbn VARCHAR(15));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> show tables;
+-------------------+
| Tables_in_adawson |
+-------------------+
| Books |
+-------------------+
1 row in set (0.00 sec)
mysql>
Add data to the tables:
mysql> LOAD DATA LOCAL INFILE 'books.data' INTO TABLE Books LINES TERMINATED BY '\r\n';
N.B.: at the end of books.data, the last value in the file is followed by a tab but no newline character (otherwise you get null vales inserted into extra rows...
Query OK, 5 rows affected (0.01 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 6
mysql> select * from Books;
+--------------+----------------------+------------------+------------+
| title | author | subject | isbn |
+--------------+----------------------+------------------+------------+
| "Author" | "Title" | "Subject" | "ISBN" |
| Deitel et al | Web Programming | Computer Science | 1234567890 |
| Dawson, A. | Internet Programming | Computer Science | 1122334455 |
| | NULL | NULL | NULL |
| | NULL | NULL | NULL |
+--------------+----------------------+------------------+------------+
5 rows in set (0.00 sec)
mysql>
mysql> delete from Books where title = "";
Query OK, 2 rows affected (0.00 sec)
mysql> select * from Books;
+--------------+----------------------+------------------+------------+
| title | author | subject | isbn |
+--------------+----------------------+------------------+------------+
| "Author" | "Title" | "Subject" | "ISBN" |
| Deitel et al | Web Programming | Computer Science | 1234567890 |
| Dawson, A. | Internet Programming | Computer Science | 1122334455 |
+--------------+----------------------+------------------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> delete from Books where title = ""Author"";
ERROR 1064: You have an error in your SQL syntax near 'Author""' at line 1
mysql> delete from Books where title ='"Author"';
Query OK, 1 row affected (0.00 sec)
delete FROM Books WHERE author = '';
(delete from Books; will delete ALL rows)
To delete the entire table from a database, use:
mysql> drop table tablename;
-----------------------------------
Instructions for assessments tables
-----------------------------------
$ mysql -h localhost -u adawson -p adawson
show tables;
delete from TABLE_NAME; // deletes all rows - TABLE_NAME case sensitive
drop table TABLE_NAME; // deletes table TABLE_NAME - TABLE_NAME case sensitive
rename table 237_PQ1_FA06 to BUSI237A_PQ1_FA06; // to rename a table
To create backup copies of tables:
SELECT * INTO new_table_name FROM original_table_name;
SQL Hint Sheet