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>