Download
1 / 24
260 likes | 639 Vues
MySQL. Agenda. Introduction to MySQL Get Connected Basic MySQL Commands MySQL Syntax Data Retrieval. Overview of MySQL. MySQL is a database management system. MySQL is a relational database management system. The MySQL Database Server is very fast, reliable, and easy to use.
E N D
Agenda • Introduction to MySQL • Get Connected • Basic MySQL Commands • MySQL Syntax • Data Retrieval
Overview of MySQL • MySQL is a database management system. • MySQL is a relational database management system. • The MySQL Database Server is very fast, reliable, and easy to use. • MySQL Server works in client/server or embedded systems. • A large amount of contributed MySQL software is available. • MySQL software is Open Source. • Download MySQL reference manual http://dev.mysql.com/doc/ • Download MySQL http://dev.mysql.com/downloads/
Download MySQL • Http://dev.mysql.com/downloads/ • GUI Tools: • MySQL Administrator 1.2 Generally Available (GA) • MySQL Query Browser 1.2 Generally Available (GA) • MySQL Migration Toolkit 1.1 Generally Available (GA)
Get connected • To connect to the server, use the MySQL user name when you invoke mysql and, most likely, a password. • If the server runs on a machine other than the one where you log in, you will also need to specify a host name. shell> mysql -h host -u user -p Enter password: ******** Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> • Disconnect it by typing QUIT (or \q) at the mysql> prompt: mysql> QUIT
Connection • There are numerous command line options, but the most essential options and syntax are • mysql -u<username> -h<hostname> -p[password] [database_name] • If you leave out the -u option MySQL assumes the current shell user. • If you leave out the host mysql assumes localhost – i.e., the machine you're logged onto. • If you give it the -p option but no password, mysql prompts you for it.
Create and Use Database • Create a database mysql> CREATE DATABASE CSC411; • Your database needs to be created only once, but you must select it for use each time you begin a mysql session. mysql> USE CSC411 Database changed • or shell> mysql -h host -u user -p CSC411 Enter password: ********
Show Database • Use the SHOW statement to find out what databases currently exist on the server: mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+ • If the test database exists, try to access it: mysql> USE test
Creating a Table • 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.00 sec) • Use a CREATE TABLE statement to specify the layout of your table: mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); • Once you have created a table, SHOW TABLES should produce some output: mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+
Describe statement • To verify that your table was created the way you expected, use a DESCRIBE statement: mysql> DESCRIBE pet;
MySQL Data Types • In the most general terms, there are three types: numeric, string, and date/time. • The concept of the NULL value • A common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string. This is not the case. • For example, the following statements are completely different: • mysql> INSERT INTO my_table (phone) VALUES (NULL); • mysql> INSERT INTO my_table (phone) VALUES ('');
Loading Data into a Table • After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this. • To load the text file pet.txt into the pet table, use this command: mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; • Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use: mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet -> LINES TERMINATED BY '\r\n'; • Add a New record using an INSERT statement: mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Entering Queries • Here's a simple command that asks the server to tell you its version number and the current date. • Type it in as shown here following the mysql> prompt and press Enter: mysql> SELECT VERSION(), CURRENT_DATE; +------------+--------------+ | VERSION() | CURRENT_DATE | +------------+--------------+ | 4.1.14-Max | 2005-09-03 | +------------+--------------+ 1 row in set (0.01 sec) mysql> SELECT NOW();
Executing SQL Statements from a Text File • Put your SQL statements in a file and then tell mysql to read its input from that file. • Create a text file text_file that contains the statements you wish to execute. shell> mysql db_name < text_file • If you are running mysql, you can execute an SQL script file using the source or \. command: mysql> source filename mysql> \. filename
Use Scripts from MySQL Prompt CREATE TABLE Sailors( sid VARCHAR(8), sname varchar(20), age numeric, primary key (sid)); CREATE TABLE Boats( bid VARCHAR(8), bname varchar(20), color varchar(10), primary key (bid)); CREATE TABLE Reserve( sid VARCHAR(8) not null references Sailors(sid), bid varchar(8) not null references Boads(did), day date, primary key (sid, bid));
Retrieving Information from a Table • The SELECT statement is used to pull information from a table. The general form of the statement is: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy; • Selecting All Data: mysql> SELECT * FROM pet;
Selecting Particular Rows mysql> SELECT * FROM pet WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE birth > '1998-1-1';
Examples mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f');
Selecting Particular Columns mysql> SELECT name, birth FROM pet; mysql> SELECT name, species, birth FROM pet -> WHERE species = 'dog' OR species = 'cat';
Sorting Rows • To sort a result, use an ORDER BY clause. • In ascending order mysql> SELECT name, birth FROM pet ORDER BY birth; • In descending order mysql> SELECT name, birth FROM pet ORDER BY birth DESC; • You can sort on multiple columns mysql> SELECT name, species, birth FROM petORDER BY species, birth DESC; • Note that the DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order.
Counting Rows mysql> SELECT COUNT(*) FROM pet; mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = 'dog' OR species = 'cat' -> GROUP BY species, sex;
Using More Than one Table mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event; mysql> SELECT pet.name, -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, -> remark -> FROM pet, event -> WHERE pet.name = event.name AND event.type = 'litter'; Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if date occurs earlier in the year than birth.
Using More Than one Table • Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. • For example, to find breeding pairs among your pets, you can join the pet table with itself to produce candidate pairs of males and females of like species: mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm'; +--------+------+--------+------+---------+ | name | sex | name | sex |species| +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser| m | dog | +--------+------+--------+------+---------+
