http://dev.mysql.com/doc/refman/5.1/en/mysql.html
%sql –horion.csl.mtu.edu –uyourname –p
Press enter after the –p, don’t put your password on the command line. “ps command” will show it.
4. Use the SHOW statement to find out the list of the databases that you are allowed to see:
mysql> show databases;
5. Switch/set the default database
mysql> USE your_own_database
Database changed
mysql> select database();
6. List mysql commands and pay attention to the commands that are interesting
mysql> help
Pay attention to
clear \c,
ego(\G),
edit (\e),
delimiter (\d),
system(\!),
connect(\r)
7. Create the student table student(id, name, dept_name, total_credits);
Attribute id is the primary key
Create table ..
8. Modify the student table to add a new column age;
Alter table …
9. Check the properties of table
mysql> describe student;
mysql> show create table student;
10. Insert 3 students into student
mysql> insert into student values ( , ,…);
11. List all the data
mysql> select * from student;
12. Try to insert a student with
existing ID, and see what happen!!!
13. How many students in the table
mysql> select count(*) from student;
14. Insert into the student with duplicate name
insert into students values(…);
insert into students(id,name) values ( … , …);
15. Find the duplicate names using self join. (use distinct to get rid of duplicates)
select distinct a.name from students a, students b where a.name=b.name and a.id != b.id;
16. Find the duplicate names using grouping
select name from students group by name having count(*) >1
17. Delete a students by its id=1
mysql> delete from student where id = …
18. Update student ID=1’s age
Update student set age=21 where id=1;
19. To create a data file
Type you input in a local file, say "testin.txt", like below, separated by tab
10 Alice CS 80 21
20 Joe EE 40 20
20. To load the text file `student.data' into the table, use this command:
mysql> LOAD DATA LOCAL INFILE "/home/major/…/student.data " INTO TABLE student;
if there are any warnings, show the warnings:
mysql> show warnings;
21. Run a batch job from command directory
Save your sql commands separated by “;” ( like the following) into a file, say myquery.sql,
select "____________";
describe students;
select "____________";
select count(*) from students;
select "____________";
select avg(credits) from students;
and invoke it with the command as below from the terminal
mysql –h … -u… -p [database_name] < myquery.sql > myquery.out