第一部分 命令
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被删除）