mysql常用命令案例_MySQL

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:/Users/Administrator>net start mysql56
MySQL56 服务正在启动 ..
MySQL56 服务已经启动成功。


C:/Users/Administrator>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 1
Server version: 5.6.10 MySQL Community Server (GPL)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| book               |
| connect            |
| db_bbs             |
| db_database25      |
| dreamtimenews      |
| hibernate          |
| hrsystem           |
| jeebbs             |
| jeecmsv5           |
| meiupic            |
| mysql              |
| news               |
| nmsdb              |
| oscommerce         |
| performance_schema |
| sakila             |
| test               |
| vote               |
| world              |
+--------------------+
21 rows in set (0.23 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employees      |
| student        |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from employees;
+---------+----------+--------+
| empname | title    | salary |
+---------+----------+--------+
| 中签    | 职员     |   5000 |
| 公共    | 职员     |   4500 |
| 寝室    | 职员     |   3500 |
| 就是    | 职员     |   5500 |
| 张三    | 部门经理 |   8000 |
| 李四    | 职员     |   4000 |
| 李帅    | 职员     |   3000 |
| 李波    | 职员     |   3000 |
| 王五    | 职员     |   4000 |
| 高就    | 经理     |   6000 |
+---------+----------+--------+
10 rows in set (0.10 sec)

mysql> create table persons
    -> (
    -> id int not null,
    -> name varchar(20),
    -> mgrid varchar(20)
    -> );
Query OK, 0 rows affected (0.11 sec)


mysql> insert into persons(id,name) values(1,'zwh1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into persons(id,name) values(2,'zwh2');
Query OK, 1 row affected (0.00 sec)

mysql> alter table persons
    -> modify column mgrid
    -> int;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc persons;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| mgrid | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> select * from persons;
+----+------+-------+
| id | name | mgrid |
+----+------+-------+
|  1 | zwh1 |  NULL |
|  2 | zwh2 |  NULL |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> insert into persons(id,name,mgrid) values(2,'zwh2','1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into persons(id,name,mgrid) values(4,'zwh4','2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from persons;
+----+------+-------+
| id | name | mgrid |
+----+------+-------+
|  1 | zwh1 |  NULL |
|  2 | zwh2 |  NULL |
|  2 | zwh2 |     1 |
|  4 | zwh4 |     2 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> update persons set id=3 where mgrid=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update persons set name='zwh3' where mgrid=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from persons;
+----+------+-------+
| id | name | mgrid |
+----+------+-------+
|  1 | zwh1 |  NULL |
|  2 | zwh2 |  NULL |
|  3 | zwh3 |     1 |
|  4 | zwh4 |     2 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select id,name,person2.mgrid,person2.name as mgrname
    -> from persons inner join persons as person2
    -> on persons.id=person2.mgrid;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> select persons.id,persons.name,person2.mgrid,person2.name as mgrname
    -> from persons inner join persons as person2
    -> on persons.id=person2.mgrid;
+----+------+-------+---------+
| id | name | mgrid | mgrname |
+----+------+-------+---------+
|  1 | zwh1 |     1 | zwh3    |
|  2 | zwh2 |     2 | zwh4    |
+----+------+-------+---------+

2 rows in set (0.03 sec)

mysql> select persons.id as mgrid,persons.name as mgrname,person2.id,person2.name
    -> from persons inner join persons as person2
    -> on persons.id=person2.mgrid;
+-------+---------+----+------+
| mgrid | mgrname | id | name |
+-------+---------+----+------+
|     1 | zwh1    |  3 | zwh3 |
|     2 | zwh2    |  4 | zwh4 |
+-------+---------+----+------+
2 rows in set (0.00 sec)

mysql>