Last updated: Wednesday 30th September 2015, 7:20 PT, AD
This web page remains for historical reasons.
I have created a new text page showing how to create a simple database
and query the database through a web page using PHP.
It assumes a clean Ubuntu server setup with MySQL installed has already been done.
See my server notes on my home page (www.annedawson.net) for server installation instructions.
My latest MySQL notes
Codecademy SQL Tutorial
W3Schools SQL Tutorial
This document is an aide-memoire for the author and is not a tutorial for beginners.
****************************************
$ mysql -h localhost -u adawson -p adawson
mysql> show tables;
mysql> CREATE TABLE Books (title VARCHAR(20), author VARCHAR(20),subject VARCHAR(20), isbn VARCHAR(15));
mysql> LOAD DATA LOCAL INFILE 'books.data' INTO TABLE Books LINES TERMINATED BY '\r\n';
mysql> select * from Books;
mysql> delete from Books where title = "";
mysql> delete FROM Books WHERE author = '';
To delete the entire table from a database, use:
mysql> drop table tablename;
SQL Hint Sheet
MySQL Server
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';
_______________________________________________________________________________________
*** NOTE ***
If you get this error:
"The used command is not allowed with this MySQL version"
when doing this in MySql:
load data local infile '/var/lib/mysql/adawson/books.data' into table books lines terminated by '\r\n';
The fix is below...
http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile
the fix:
need to update /etc/mysql/my.cnf with
local-infile=1 in the [mysql] and [mysqld] sections
load data local infile '/var/lib/mysql/adawson/books.data' into table books lines terminated by '\r\n';
now table loads OK after the my.cnf file updated as above.
*** END OF NOTE ***
_______________________________________________________________________________________
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
