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:~#