Transcript mysql

mysql
YUN YEO JOONG
1 Connecting to and Disconnecting
from the Server
shell> mysql –h host -u user -p (Enter)
password: ********
 Welcome to the MySQL monitor.
Commands end with ; or \g.
 Your MySQL connection id is 459 to
server version: 3.22.20a-log
 Type 'help' for help.
 mysql>

a SQL statement followed by a
semicolon. (some exceptions QUIT)
 When you issue a command, mysql
sends it to the server for execution
and displays the results,
 then prints another mysql>.

mysql displays query output as a
table (rows and columns).
 Normally, column labels are the
names of the columns you fetch from
database tables.

– If you're retrieving the value of an
expression, mysql labels the column
using the expression itself.










Here's a simple multiple-line statement:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+

In this example, notice how the
prompt changes from mysql> to ->
after you enter the first line of a
multiple-line query.
– This is how mysql indicates that it hasn't
seen a complete statement and is
waiting for the rest.
mysql> SELECT * FROM my_table
WHERE name = "Smith AND age < 30;
 ">

2 Creating and Using a Database

mysql> CREATE DATABASE
menagerie;
shell> mysql -hhost -uuser -p
menagerie Enter password: ********
 Creating a Table

– mysql> SHOW TABLES;
– Empty set (0.00 sec)





mysql> CREATE TABLE pet (name
VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1),
birth DATE, death DATE);
mysql> DESCRIBE pet;
mysql> INSERT INTO pet
-> VALUES
('Puffball','Diane','hamster','f','1999-0330',NULL);
3 Retrieving Information from a Table
SELECT what_to_select
 FROM which_table
 WHERE conditions_to_satisfy

– mysql> SELECT * FROM pet;

mysql> UPDATE pet SET birth = "198908-31" WHERE name = "Bowser";
mysql> SELECT * FROM pet WHERE
(species = "cat" AND sex = "m")
 -> OR (species = "dog" AND sex = "f");
 Selecting Particular Columns

– SELECT name, birth FROM pet;
– SELECT DISTINCT owner FROM pet;
– mysql> SELECT name, species, birth FROM
pet -> WHERE species = "dog" OR species =
"cat";

Sorting Rows
– mysql> SELECT name, birth FROM pet
ORDER BY birth;
– mysql> SELECT name, birth FROM pet
ORDER BY birth DESC;

Pattern Matching
– mysql> SELECT * FROM pet WHERE
name LIKE "b%";

Counting Rows
– mysql> SELECT COUNT(*) FROM pet;
– mysql> SELECT species, COUNT(*)
FROM pet GROUP BY species;
– mysql> SELECT sex, COUNT(*) FROM
pet GROUP BY sex;
4 Getting Information About Databases
and Tables
SELECT article, dealer, price
 FROM shop
 WHERE price=(SELECT MAX(price)
FROM shop)
 SELECT article, dealer, price FROM shop
s1 WHERE price=(SELECT
MAX(s2.price) FROM shop s2 WHERE
s1.article = s2.article);

5.API
MYSQL *mysql_connect(MYSQL
*mysql, const char *host, const char
*user, const char *passwd)
 int mysql_create_db(MYSQL *mysql,
const char *db)
 int mysql_drop_db(MYSQL *mysql,
const char *db)

my_bool mysql_change_user(MYSQL
*mysql, const char *user, const char
*password, const char *db)
 void mysql_close(MYSQL *mysql)
 MYSQL *mysql_connect(MYSQL
*mysql, const char *host, const char
*user, const char *passwd)


int mysql_create_db(MYSQL *mysql,
const char *db)