mysql study_MySql study note 1

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)

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