Mysql 简单的控制台语句.doc

Mysql 简单的控制台语句使用SHOW语句找出在服务器上当前存在什么数据库mysql show databases;+-+| 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: 0mysql 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 birthday asc;+-+-+| 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 se