学步园

1.login as root

root@vinco:~#mysql -h locahost -u root -p

Enter password:(*******)

2.show all database

mysql> show databases

    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| phpmyadmin         | 
+--------------------+
3 rows in set (0.01 sec)

3.select one database of them

mysql> use mysql

Database changed

4.show all tables

mysql> show tables

    -> ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              | 
| db                        | 
| event                     | 
| func                      | 
| general_log               | 
| help_category             | 
| help_keyword              | 
| help_relation             | 
| help_topic                | 
| host                      | 
| ndb_binlog_index          | 
| plugin                    | 
| proc                      | 
| procs_priv                | 
| servers                   | 
| slow_log                  | 
| tables_priv               | 
| time_zone                 | 
| time_zone_leap_second     | 
| time_zone_name            | 
| time_zone_transition      | 
| time_zone_transition_type | 
| user                      | 
+---------------------------+
23 rows in set (0.00 sec)

6. create table

mysql> create table classmates(
    -> name char(15),
    -> telephone varchar(20),
    -> qq varchar(12)
    -> );
Query OK, 0 rows affected (0.23 sec)


mysql> show tables
    -> ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| classmates                | 
| columns_priv              | 
| db                        | 
| event                     | 
| func                      | 
| general_log               | 
| help_category             | 
| help_keyword              | 
| help_relation             | 
| help_topic                | 
| host                      | 
| ndb_binlog_index          | 
| plugin                    | 
| proc                      | 
| procs_priv                | 
| servers                   | 
| slow_log                  | 
| tables_priv               | 
| time_zone                 | 
| time_zone_leap_second     | 
| time_zone_name            | 
| time_zone_transition      | 
| time_zone_transition_type | 
| user                      | 
+---------------------------+
24 rows in set (0.01 sec)



<pre name="code" class="sql">mysql> describe classmates
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | char(15)    | YES  |     | NULL    |       | 
| telephone | varchar(20) | YES  |     | NULL    |       | 
| qq        | varchar(12) | YES  |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.15 sec)

7.insert item into the  created table

mysql> insert into classmates values(
    -> "vinco","15572762738","910923899"
    -> );

Query OK, 1 row affected (0.20 sec)

mysql> insert into classmates values(
    -> "zhang","02787617327","1402088466"

    -> );

Query OK, 1 row affected (0.00 sec)

mysql> insert into classmates values(
    -> "aa","123456465","8763257123"
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> select * from classmates;
+-------+-------------+------------+
| name  | telephone   | qq         |
+-------+-------------+------------+
| vinco | 15572762738 | 910923899  | 
| zhang | 02787617327 | 1402088466 | 
| aa    | 123456465   | 8763257123 | 
+-------+-------------+------------+

3 rows in set (0.00 sec)

8. update the data in the table

mysql> update classmates set name="alice" where name="aa";
Query OK, 1 row affected (0.42 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from classmates
    -> ;
+-------+-------------+------------+
| name  | telephone   | qq         |
+-------+-------------+------------+
| vinco | 15572762738 | 910923899  | 
| zhang | 02787617327 | 1402088466 | 
| alice | 123456465   | 8763257123 | 
+-------+-------------+------------+
3 rows in set (0.00 sec)

9.delete an entry from the table

mysql> delete from classmates where name="alice";
Query OK, 1 row affected (0.05 sec)

mysql> select * from classmates;
+-------+-------------+------------+
| name  | telephone   | qq         |
+-------+-------------+------------+
| vinco | 15572762738 | 910923899  | 
| zhang | 02787617327 | 1402088466 | 
+-------+-------------+------------+
2 rows in set (0.00 sec)

10. add a new user and grant some privileges to her

mysql> grant select,insert,update,delete on classmates.* to zhang@localhost identified by "zhang";

Query OK, 0 rows affected (0.58 sec)

/* open a new console here now */

zhang@vinco:~o$ whoami

zhang
zhang@vinco:/home/vinco$ mysql -u zhang -h localhost -p
Enter password: (zhang)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.1.37-1ubuntu5.5 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

11. privileges manegment

mysql> grant select,insert,update,delete on classmates.* to zhang@localhost identified by "zhang";
Query OK, 0 rows affected (0.58 sec)

mysql> revoke delete on classmates.* from zhang@localhost ;
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all privileges on classmates.* from zhang@localhost;
Query OK, 0 rows affected (0.00 sec)

12 quit from mysql

mysql> quit
Bye
root@vinco:~#