#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122:
#Lesson 1
#Spring 2005 (Updated on Thursday 05/12/05)

- To view what version of mysql that we  are using, we issue the command:
	select version();

- To view the current date, we issue the command:
	select current_date();

- To view the current time, we issue the command:
	select current_time();

- To view the current user, we issue the command:
	select current_user();

- To see how many databases that a specific user is granted the permission to see (and use),
we use the "show databases" key words, follow  by a semi-colon.
	show databases;

- To use a specific database, we need to instruct the database server by issuing the command:
   	use database_name
  where database_name is the name of your database.
   	
For example, if there exists a database called "CS122", then to use this database (with the
given permission), we issue the mysql command:
	use CS122; 

this will change our current databse to the existing CS122 database.

- To see how many tables that a specific user is granted the permission to see (and use),
we use the "show tables" key words, follow  by a semi-colon.
	show tables;

- Note: We can issue as many sql statements as we want, as long as we seperate the end of each
statement by a semicolon.
  Ex:
  	show databases; show tables;
  	
  The above combined commands will be executed one at a time. Thus the command "show databases"
  is performed first follow by the "show tables" command.

- To see the structure of a table, we issue the command:
	desc table_name 
  Ex.:
	desc Allstar_BS;

   will give:
	+----------+-------------+------+-----+---------+-------+
	| Field    | Type        | Null | Key | Default | Extra |
	+----------+-------------+------+-----+---------+-------+
	| playerID | varchar(9)  |      | PRI |         |       |
	| yearID   | smallint(6) |      | PRI | 0       |       |
	| lgID     | char(2)     |      | PRI |         |       |
	+----------+-------------+------+-----+---------+-------+
	3 rows in set (0.00 sec)

- To see the details of the creation of a table, we issue the command:
	show create table TABLE_NAME; 
  where you substitute the TABLE_NAME with the name of your table.
  Ex.:
	show create table Allstar_BS;

  will give:
	+------------+-----------------------------------------------+
	| Table      | Create Table                                  |                                                                                                                                                                                                |
	+------------+-----------------------------------------------+
	| Allstar_BS | CREATE TABLE `Allstar_BS` (		     |
	|  	     |	`playerID` varchar(9) NOT NULL default '',   |
	|  	     |	`yearID` smallint(6) NOT NULL default '0',   |
	|  	     |	`lgID` char(2) NOT NULL default '',          |
	|  	     |	PRIMARY KEY  (`playerID`,`yearID`,`lgID`)    |
	|	     |	) ENGINE=MyISAM DEFAULT CHARSET=latin1       |  
	+------------+-----------------------------------------------+
	1 row in set (0.00 sec)

- To create a new table (let's name it Course), we issue the command "create table" follow
by the table's name, then an open parentheses, follow by the fields and their data type as 
well as their specifications, then close it with a closed parentheses and a semi-colon.

	create table Course(
		courseName varchar(35),
		CourseNum varchar(10),
		section tinyint,
		courseAbbr varchar(6));

- To create a new table (let's name it Course), we issue the command "create table" follow
by the table's name, then an open parentheses, follow by the fields and their data type as 
well as their specifications, then close it with a closed parentheses and a semi-colon.
   If there is a primary key, then we can declare this primary key in the CREATE TABLE statement
as follow: (let's assume that the primary key is the field courseNum)

	create table Course(
		courseName varchar(35),
		courseNum varchar(10) primary key,
		section tinyint,
		courseAbbr varchar(6));

- To create a new table (let's name it Course), we issue the command "create table" follow
by the table's name, then an open parentheses, follow by the fields and their data type as 
well as their specifications, then close it with a closed parentheses and a semi-colon.
   If there is more than ONE primary key, then we have to declare these primary keys in 
a seperate statement. Let's assume that there are three primary keys: courseName, courseNum,
and courseAbbr. Then we will create the new table as follow:

		create table Course(
		courseName varchar(35),
		courseNum varchar(10),
		section tinyint,
		courseAbbr varchar(6),
		primary key(courseName, courseNum, courseAbbr));

- To declare a default value for a field (let's say the field courseName) to be "N/A", we
can declare the default value in the CREATE TABLE statement as follow:
		
		create table Course(
			courseName varchar(35) default 'N/A',
			courseNum varchar(10),
			section tinyint,
			courseAbbr varchar(10),
			primary key(courseName, courseNum, courseAbbr))


- To delete a table, we use the ley word "drop" follow by the table's name.
CAUTION: ONCE a table is deleted then you would lose all data contains in that table.
Therefore make sure that you have a back up BEFORE you delete a table.
	drop table Course;

- To insert data into a table, we issue the command "insert into" follow by the table's 
name, then the word "values", then an open parentheses, then the data. If the data is a 
string, then enclose the string in a pair of single quotes. If the data is a number then
do not enclose it with anything.
Be careful when you enter the date type data. The default date format for mysql is 
'yyyy-mm-dd'; whereas we are used to the format 'mm-dd-yyyy'.

	insert into Course 
		values('Computer Science','CS122',3,'CS');

- We can insert multiple rows ito an existing table as follow:

	insert into Course
                values('Mathematics','Math102',3,'Math'),
                ('History','Hist101',4,'Hist'),
                ('English','Engl101',4,'Engl');  

- We can populate the records from an existing table into another table by issuing the 
query: (note that these 2 tables DO NOT NEED to have the same number of fields. As long
as the fields that we import match the data type of the fields of the table that we want
to import to, then we are fine)

	insert into Some_Table SELECT courseName, courseNum, courseAbbr from Course;

  The above query will add all records with three fields: courseName, courseNum, and
courseAbbr into the existing table Some_Table.

- We can create a table and create records of that table on the fly, if the structure of that table
matches the structure of an existing tableas follow:

	create table NEW_TABLE AS select * from  Course;
  OR
	create table NEW_TABLE AS select courseName, courseNum from Course;

  The only things that we have to keep in mind is that the primary keys, default values,
table type, etc.. may not be transferred over. You may need to alter the NEW-TABLE to add
these constraints.

CAUTION:
	(1) If you made a typing mistake in the middle of your query, then press "ENTER"
	to go to the next line. 
	At the beginning of the next line, type \c then press "ENTER". This will signal
	Mysql to bypass your current query.
	
	(2) If you see the '> prompt on the next line, then this means that you missed 
	out one single quote somewhere in your previous line of your query. To remedy 
	this situtation, press "ENTER" then go to the next line. At the BEGINNING of 
	the next line, type '\c then press "ENTER". this will signal to Mysql that you 
	would like to ignore the current query.
	
	(3) If you see the "> prompt on the next line, then this means that you missed
	out a double quote somwhere in your previous line of query. To abandon this query,
	press "ENTER" to go to the next line. At the BEGINNING of the next line, type >\c
	then press "ENTER". This will skip your query and bring you back to the mysql 
	prompt.