(转载)Mysql 简单的控制台语句

使用SHOW语句找出在服务器上当前存在什么数据库

mysql> showdatabases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| edu               |

| mysql             |

| performance_schema |

| sakila            |

| test               |

| webclass          |

| world             |

+--------------------+

8 rows in set (0.00 sec)

选择你所创建的数据库

mysql> use webclass;

Database changed

查看现在的数据库中存在什么表

mysql> show tables;

+--------------------+

| Tables_in_webclass |

+--------------------+

| stu                |

+--------------------+

1 row in set (0.00 sec)

显示表的结构

mysql> desc stu;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         |Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| id    | int(10)      | NO  | PRI | NULL    | auto_increment |

| sname | char(30)     |NO   |    | NULL    |                |

| sex   | tinyint(1)   | YES |     | 0       |                |

| qq    | varchar(255) |YES  |    | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)
向表中添加一列

mysql>alter table stu

    -> add birthday date;

Query OK, 0 rows affected (0.11 sec)

Records: 0  Duplicates: 0 Warnings: 0

表中加入记录
mysql> insert into stu(sname,sex,qq,birthday)

    -> values('张三',1,1126490093,'1999-1-1');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu(sname,sex,qq,birthday)

    -> values('李四',0,1144490093,'1958-8-1');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu(sname,sex,qq,birthday)

    -> values('王五',0,2225552093,'1989-8-1');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu(sname,sex,qq,birthday)

   -> values('赵六',1,5555552093,'1549-8-1');

Query OK, 1 row affected (0.00 sec)

mysql>select * from stu;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

| 1 | 张三 |    1 | 1126490093 | 1999-01-01 |

| 2 | 李四 |    0 | 1144490093 | 1958-08-01 |

| 3 | 王五 |    0 | 2225552093 | 1989-08-01 |

| 4 | 赵六 |    1 | 5555552093 | 1549-08-01 |

+----+-------+------+------------+------------+

4 rows in set (0.00 sec)

更新表中数据

mysql>update stu

    -> set birthday = '1986-3-4'

    -> where id = 4;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql>select * from stu;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

|  1 | 张三  |    1| 1126490093 | 1999-01-01 |

| 2 | 李四 |    0 | 1144490093 | 1958-08-01 |

| 3 | 王五 |    0 | 2225552093 | 1989-08-01 |

| 4 | 赵六 |    1 | 5555552093 | 1986-03-04 |

+----+-------+------+------------+------------+

4 rows in set (0.00 sec)

排序【按生日排序】

mysql>select * from stu order by birthday ;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

| 2 | 李四 |    0 | 1144490093 | 1958-08-01 |

| 4 | 赵六  |    1 | 5555552093 | 1986-03-04 |

| 3 | 王五 |    0 | 2225552093 | 1989-08-01 |

| 1 | 张三 |    1 | 1126490093 | 1999-01-01 |

+----+-------+------+------------+------------+

4 rows in set (0.00 sec)

mysql>select * from stu order by id desc limit 2;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

| 4 | 赵六 |    1 | 5555552093 | 1986-03-04 |

| 3 | 王五 |    0 | 2225552093 | 1989-08-01 |

+----+-------+------+------------+------------+

2 rows in set (0.00 sec)

mysql>select * from stu order by birthday asc limit 1;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

| 2 | 李四 |    0 | 1144490093 | 1958-08-01 |

+----+-------+------+------------+------------+

1 row in set (0.01 sec)

mysql>select * from stu order by birthday desc limit 1;

+----+-------+------+------------+------------+

| id | sname | sex  | qq        | birthday   |

+----+-------+------+------------+------------+

| 1 | 张三 |    1 | 1126490093 | 1999-01-01 |

+----+-------+------+------------+------------+

1 row in set (0.00 sec)

查询年龄第二大的学生

mysql> select sname,birthday from stu order by birthdayasc;

+-------+------------+

| sname | birthday   |

+-------+------------+

| 李四  | 1958-08-01 |

| 赵六  | 1986-03-04 |

| 王五  | 1989-08-01 |

| 张三  | 1999-01-01 |

+-------+------------+

4 rows in set (0.00 sec)

mysql>select sname,birthday from stu order by birthday limit 0,1;

+-------+------------+ 【0,1 从0条开始取一条 ，包含本条】

| sname | birthday   |

+-------+------------+

| 李四  | 1958-08-01 |

+-------+------------+

1 row in set (0.00 sec)

mysql>select sname,birthday from stu order by birthday limit 1,1;

+-------+------------+ 【1,1 从1条开始取一条 ，包含本条】

| sname | birthday   |

+-------+------------+

| 赵六  | 1986-03-04 |

+-------+------------+

1 row in set (0.00sec)