ProgMysql :: 2006/10/15 17:36

  1. Getting start
    1. Connecting to the MySQL Server
      1. shell> mysql -h 'host_name' -u 'user_name' -p
      2. host_name : The host name you want to connect, ''The default hostname is localhost.''
      3. user_name : The user name you want to login, ''The default username is ODBC on Windows and your Unix login name on Unix.''
      4. -p : No password is supplied if -p is missing.
    1. Getting Information About Databases and Tables
      1. It shows lists the databases managed by the server.
        mysql> show databases;
      2. To find out which database is currently selected.
        mysql> select database();
      3. To find what tables the default database contains.
        mysql> show tables;
      4. To find out about structure of a table.
        mysql> desc pet;
    1. Table
      1. Creating Table
        1. 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);
          mysql> SHOW TABLES;
          +---------------------+
          | Tables in menagerie |
          +---------------------+
          | pet |
          +---------------------+
          mysql> DESCRIBE pet;
          +---------+-------------+------+-----+---------+-------+
          | Field | Type | Null | Key | Default | Extra |
          +---------+-------------+------+-----+---------+-------+
          | name | varchar(20) | YES | | NULL | |
          | owner | varchar(20) | YES | | NULL | |
          | species | varchar(20) | YES | | NULL | |
          | sex | char(1) | YES | | NULL | |
          | birth | date | YES | | NULL | |
          | death | date | YES | | NULL | |
          +---------+-------------+------+-----+---------+-------+

        2. The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
          mysql> CREATE TABLE animals (
          id MEDIUMINT NOT NULL AUTO_INCREMENT,
          name CHAR(30) NOT NULL,
          PRIMARY KEY (id)
          );

          mysql> INSERT INTO animals (name) VALUES
          ('dog'),('cat'),('penguin'),
          ('lax'),('whale'),('ostrich');

          mysql> SELECT * FROM animals;

          Which returns:

          +----+---------+
          | id | name |
          +----+---------+
          | 1 | dog |
          | 2 | cat |
          | 3 | penguin |
          | 4 | lax |
          | 5 | whale |
          | 6 | ostrich |
          +----+---------+

  2. Database Administration
    1. User Account Management
      1. Finding users
        1. If you have root privilege, follow these step.
        2. We're going to use USER TABLEChoose database which we're going to use and find the USER TABLEThen we can query from USER TABLE.
          mysql> show databases;
          +----------+
          | Database |
          +----------+
          | mantis |
          | mysql |
          | phpbb |
          | ranking |
          | test |
          | wapcms |
          +----------+
          6 rows in set (0.00 sec)

          mysql> select database();
          +------------+
          | database() |
          +------------+
          | NULL |
          +------------+
          1 row in set (0.00 sec)

          mysql> use mysql
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A

          Database changed

          mysql> show tables;
          +---------------------------+
          | Tables_in_mysql |
          +---------------------------+
          | columns_priv |
          | db |
          | func |
          | help_category |
          | help_keyword |
          | help_relation |
          | help_topic |
          | host |
          | tables_priv |
          | time_zone |
          | time_zone_leap_second |
          | time_zone_name |
          | time_zone_transition |
          | time_zone_transition_type |
          | user |
          +---------------------------+
          15 rows in set (0.00 sec)

          mysql> select host,user from user;
          +----------------+--------+
          | host | user |
          +----------------+--------+
          | localhost | |
          | localhost | root |
          | localhost | sjnamo |
          | moz.namo.co.kr | |
          | moz.namo.co.kr | root |
          | moz.namo.co.kr | sjnamo |
          +----------------+--------+
          6 rows in set (0.00 sec)

      2. Adding user
        1. fill this section later~
      3. Assigning Account Passwords
        1. users such as root with update access to the '''mysql database''' can change the password for other users
        2. mysql> SET PASSWORD FOR 'jeffrey'@'%'  PASSWORD('biscuit');
        3. mysql> SET PASSWORD  PASSWORD('biscuit');
          1. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause
        4. mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
          1. You can also use a GRANT USAGE statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges
Trackback Address :: http://kbckbc.mireene.co.kr/tatter/kbckbc/trackback/50
Name
Password
Homepage
Secret