mysql应用基础_mysql命令基本应用

第一部分 命令

mysql命令以分号；结束

创建数据库：create database 库名 ；显示所有数据库: show databases;

当前库数据表结构:show tables;创建数据表:CREATE TABLE 表名([字段名] [字段类型]([字段要求]) [字段参数], ......);显示数据表字段:desc  表名;增加各个字段alter table 表名 add 字段名 字段类型；删库:drop database 库名;删表:drop table 表名；

查询: select 字段名表名 where条件;

修改：update表名set 修改内容 where条件；

第二部分 例子

1.查看当前mysql的版本

mysql>select version();

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

| version()  |

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

| 5.5.33-log |

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

1 row in set (0.00 sec)

2.创建个新库

mysql>create database db9;

Query OK, 1 row affected (0.03 sec)

mysql>show databases;

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

| Database           |

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

| information_schema |

| db1                |

|db9               |

| hellodb            |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

8 rows in set (0.00 sec)

3.在数据库db9中创建表students

mysql> use db9;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table students ( ID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY, Name CHAR(30) NOT NULL, Gender ENUM('F','M') NOT NULL);

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

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

| Tables_in_db9 |

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

| students      |

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

1 row in set (0.01 sec)

4.查看表的字段

mysql> desc students;

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

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

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

| ID     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| Name   | char(30)         | NO   |     | NULL    |                |

| Gender | enum('F','M')    | NO   |     | NULL    |                |

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

3 rows in set (0.00 sec)

5.往表内输入数据

mysql> insert into students values ( 1,'Tom','M'),(2,'David','M'),(3,'July','F'),(4,'Alx','F');

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

6.显示表里内容

mysql> select * from students;

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

| ID | Name  | Gender |

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

|  1 | Tom   | M      |

|  2 | David | M      |

|  3 | July  | F      |

|  4 | Alx   | F      |

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

4 rows in set (0.00 sec)

7.往表里再添加一行数据

mysql> insert into students values ( 5,'Jerry','M');

Query OK, 1 row affected (0.07 sec)

mysql> select * from students;

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

| ID | Name  | Gender |

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

|  1 | Tom   | M      |

|  2 | David | M      |

|  3 | July  | F      |

|  4 | Alx   | F      |

|  5 | Jerry | M      |

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

5 rows in set (0.02 sec)

8.添加一个字段 Age (在Name后面)

mysql> alter table students add Age TINYINT UNSIGNED NOT NULL AFTER Name;

Query OK, 5 rows affected (0.03 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from students;

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

| ID | Name  | Age | Gender |

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

|  1 | Tom   |   0 | M      |

|  2 | David |   0 | M      |

|  3 | July  |   0 | F      |

|  4 | Alx   |   0 | F      |

|  5 | Jerry |   0 | M      |

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

5 rows in set (0.03 sec)

9.将年龄输入到Age字段 (修改表)

mysql>update students set Age = 20 where ID = 1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;

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

| ID | Name  | Age | Gender |

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

|  1 | Tom   | 20 | M      |

|  2 | David |  23 | M      |

|  3 | July  |  13 | F      |

|  4 | Alx   |  17 | F      |

|  5 | Jerry |  27 | M      |

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

10.查询年龄小于22的学生，显示名字和各自的年龄

mysql> select Name,Age from students where Age <= 22;

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

| Name | Age |

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

| Tom  |  20 |

| July |  13 |

| Alx  |  17 |

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

11.删除字段Gender

mysql> alter table students drop Gender;

Query OK, 5 rows affected (0.02 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from students;

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

| ID | Name  | Age |

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

|  1 | Tom   |  20 |

|  2 | David |  23 |

|  3 | July  |  13 |

|  4 | Alx   |  17 |

|  5 | Jerry |  27 |

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

12.删除表

mysql> drop table students;

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

Empty set (0.00 sec)

13.删除库

mysql> drop database db9;

Query OK, 0 rows affected (0.05 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| db1                |

| hellodb            |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

(db9被删除)