Using MySQL

bj2008_0201

               
Creating a MySQL Database  
Right now I am assuming you have already logged into MySQL,
    so you should be looking at something like the following on
your screen right now:
Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is 10663 to server version: 3.22.22
   
  Type 'help' for help.
   
  mysql>
First note before I continue, you must remember that ALL
      MySQL commands end with a semicolon ";".
Anyway, lets make a database called info. To make it, you
        would type
mysql> create database info;  
If you did this successfully, you will get something like
            this result;
Query OK, 1 row affected (0.03 sec)
               
To start using this database, type in
mysql> use info;  
You will get the result;
Database changed.
     
Great, you now have the info database working, and are now
                        using it.
Creating Your Tables
This is where the previously mentioned MySQL data types come in
  handy. When you create a table, you specify all of the
  columns and their data types. It is possible to add a
  column, or manipulate current columns after they are made,
which I will show you later on in the tutorial.
Ok, this example Table will be called users, and it will
    have 3 total columns. 1, the ID Number, 2, the Person's
    Name, and 3, the Persons E-Mail address.
mysql> CREATE TABLE users (
  -> id INT NOT NULL AUTO_INCREMENT,
  -> name VARCHAR (50),
  -> email VARCHAR (50),
  -> PRIMARY KEY (id));
Asking what the !@#$ does that mean? Well, I got some
        explaining to do.
The first column is probably the most cryptic one.
          Basically, it tells MySQL that this column will be called
          id, it is an integer, its value cannot be null, and it
          automatically increments the number from the prior entry.
          You will see how it is used in the next section, adding
          data.         
The name column is a Variable Length String, which I
            explained last week. Its length is a maximum of 50
            characters. The email column is pretty much the same thing
            as well.            
What's this about the Primary Key part though?
The Primary Key is a type of index MySQL uses. This index
                can do such things as;

Quickly find the rows that match a WHERE clause.
  

Retrieve rows from other tables when performing joins. 3. Sort or group a table if the sorting or grouping is done
on a leftmost prefix of a usable key
  
This can definitely help boost the speeds of your queries as
  well.
Adding Data to a Table
Adding your Data to a table is not that hard of a process at
  all. Basically you specify what table you are inserting the
  values into, then you go ahead and do so. The syntax is as
follows;
   mysql> INSERT INTO users VALUES ("NULL","Blair Ireland","bireland@thescripts.com");  
If successful, you should get something like the following
for a response;
Query Ok, 1 row affected (0.05 sec)

*Note: When you add data, and you do not have any values to
          go into a column, you must still have it listed in your
          entry. For example, if I didn't have my e-mail address, the
syntax would look like:
mysql> INSERT INTO users VALUES ("NULL","Blair Ireland","");
Viewing Data
After you add data to your table, you probably want to check
  it out to make sure everything went as planned. To do so,
you would utilize the SELECT command.
To view all data in the table, you would use something like
  this;
mysql> SELECT * FROM users;  
This will give you an output like this
+----+---------------+---------------------------------+
  | id | name          | email                           |
  +----+---------------+---------------------------------+
  | 1  | Blair Ireland | bireland@thescripts.com         |
  | 2  | Mark Hardy    | mhardy@thescripts.com           |
  +----+---------------+---------------------------------+
  2 rows in set (0.00 sec)
Say you want to select a particular row in this database
          though, you would use this sort of command;
mysql> SELECT * FROM users WHERE (name="Blair Ireland");  
This would give you
+----+---------------+---------------------------------+
  | id | name          | email                           |
  +----+---------------+---------------------------------+
  | 1  | Blair Ireland | bireland@thescripts.com         |
  +----+---------------+---------------------------------+
You can also select specific columns, like this; mysql> select name from users;
                                 
    +----------------+
    | name           |
    +----------------+
    | Blair Ireland  |
    | Mark Hardy     |
    +----------------+
    2 rows in set (0.06 sec)
Modifying Database Data
If you have data already in the database that needs some
  modifying, you would change it by utilizing the UPDATE
command in mysql.
Its use is something like this;
mysql> UPDATE users SET email = 'webmaster@thescripts.com'
-> WHERE email = "bireland@thescripts.com";
This would just change all rows with email set to
        bireland@thescripts.com and change them to
        webmaster@thescripts.com. In this case though, only one
        entry has bireland@thescripts.com as its email, so only one
entry would be changed.
Deleting Database Data
If you want to remove data in the database, you would use
MySQL's DELETE command. Its use would be as follows
mysql> DELETE FROM users WHERE (name="Mark Hardy");  
This would delete Mark Hardy's entry in the database,
    leaving only Blair Ireland's entry in it.
So far, a very small portion of MySQL's commands have been
        covered. There are quite a few more advanced commands you
      can integrate within your SQL queries.
First off, I will use the users table that has been used
        throughout these tutorials.
mysql> CREATE TABLE users (
  -> id INT NOT NULL AUTO_INCREMENT,
  -> name VARCHAR (50),
  -> email VARCHAR (50),
  -> PRIMARY KEY (id));
Search Functions
As you have seen in previous examples, MySQL most definitely
  has the ability to find specific search data. I have never
  covered general searches though, so here you go;
The % character in MySQL is the wildcard character. That is,
    it can represent anything, literally.
To do a general search, you would use the following syntax;
mysql> SELECT * FROM test WHERE
  -> (name LIKE "%B%");
This will result in finding anything with the capital letter
          B in the column name. Notice the two %'s used. This checks
          for anything before or after that letter. You can use just
          one if you like though.
You can place that % sign anywhere within the query though,
            as the search is based upon the placement of this character.
Another wildcard character is the _ character. It will match
              exactly one character.
To use a literal wildcard character in your searches, you Order By
mysql> SELECT * FROM users WHERE
  -> (name = "Joe%") ORDER BY id DESC;
This will return all the records containing someone with the
                    first name of Joe, and will output it from the greatest ID
                    Number, descend until the lowest ID number is reached.
The default for ORDER BY is ascending, so if you want it to go by
the lowest ID number first, you would just type in ORDER BY id, or you
could plug in the ASC keyword where DESC is currently. Both would give
you the same result.
               
               
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/73684/showart_1221307.html