操作之前应连接某个数据库。
6.1 建立表
create table ( [,.. ]);
create table tablename (col1 type1 [not null] [primary key],col2 type2 [not null],..);
mysql> create table myclass (
-> id int(4) not null primary key auto_increment,
-> name char(20) not null,
-> sex int(4) not null default '0',
-> degree double(16,2));
Query OK, 0 rows affected (0.04 sec)
mysql>
补充:根据已有的表创建新表。
create table tab_new like tab_old; (只有表结构)
create table tab_new as select * from tab_old; (既包含表结构,又包含表数据)
只包含表结构:
mysql> create table myclass2 like myclass;
Query OK, 0 rows affected (0.00 sec)
mysql>
既包含表结构,又包含表数据:
mysql> insert into myclass values(1, 'david', 1, 20130417.16);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql>
mysql> create table myclass3 as select * from myclass;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from myclass3;
+----+-------+-----+-------------+
| id | name | sex | degree |
+----+-------+-----+-------------+
| 1 | david | 1 | 20130417.16 |
+----+-------+-----+-------------+
1 row in set (0.02 sec)
mysql>
6.2 获取表结构
命令:
desc 表名;
or
show columns from 表名;
获取myclass & myclass2 表结构
mysql> desc myclass;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| sex | int(4) | NO | | 0 | |
| degree | double(16,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show columns from myclass2;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| sex | int(4) | NO | | 0 | |
| degree | double(16,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
6.3 删除表
命令:drop table ;
例:删除表名为 myclass3 的表
mysql> drop table myclass3;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_david |
+-----------------+
| emp |
| myclass |
| myclass2 |
+-----------------+
3 rows in set (0.00 sec)
mysql>
6.4 更改表名
命令:rename table 原表名 to 新表名;
例:将表 myclass2 名字更改为 myclass4
mysql> rename table myclass2 to myclass4;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_david |
+-----------------+
| emp |
| myclass |
| myclass4 |
+-----------------+
3 rows in set (0.00 sec)
mysql>
6.5 在表中增加字段
命令:alter table 表名 add 字段 类型 其他;
例:在表 myclass 中添加了一个字段passtest,类型为int(4),默认值为0。
mysql> alter table myclass add passtest int(4) default '0';
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc myclass;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| sex | int(4) | NO | | 0 | |
| degree | double(16,2) | YES | | NULL | |
| passtest | int(4) | YES | | 0 | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
七、数据操作
7.1 插入数据
命令:insert into [( [,.. ])] values ( 值1 )[, ( 值n )];
例:向 myclass 表中插入以下记录,留空的表示使用默认值。
mysql> insert into myclass (id, name, sex, degree, passtest) values(1, 'david', 1, 80.56, 78);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myclass values(2, 'sandy', 0, 100, 90);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myclass (id, name, sex, degree) values(3, 'renee', 0, 90.34);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myclass (id, name) values(4, 'china');
Query OK, 1 row affected (0.00 sec)
mysql>
7.2 查询表中的数据
a. 查询所有行
命令:select from < 表名 > where < 表达式 >;
例1:查看表 myclass 中所有数据
mysql> select * from myclass;
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 80.56 | 78 |
| 2 | sandy | 0 | 100.00 | 90 |
| 3 | renee | 0 | 90.34 | 0 |
| 4 | china | 0 | NULL | 0 |
+----+-------+-----+--------+----------+
4 rows in set (0.00 sec)
mysql>
例2:查询表 david 相关信息
mysql> select * from myclass where name='david';
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 80.56 | 78 |
+----+-------+-----+--------+----------+
1 row in set (0.00 sec)
mysql>
b. 查询前几行数据
例如:查看表 myclass 中前2行数据
mysql> select * from myclass limit 2;
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 80.56 | 78 |
| 2 | sandy | 0 | 100.00 | 90 |
+----+-------+-----+--------+----------+
2 rows in set (0.00 sec)
mysql>
或者:
mysql> select * from myclass order by id limit 2;
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 80.56 | 78 |
| 2 | sandy | 0 | 100.00 | 90 |
+----+-------+-----+--------+----------+
2 rows in set (0.01 sec)
mysql>
7.3 删除表中的数据
命令:delete from 表名 where 表达式;
例如:删除表 myclass 中编号为4的记录
mysql> delete from myclass where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from myclass;
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 80.56 | 78 |
| 2 | sandy | 0 | 100.00 | 90 |
| 3 | renee | 0 | 90.34 | 0 |
+----+-------+-----+--------+----------+
3 rows in set (0.00 sec)
mysql>
7.4 修改表中的数据
update 表名 set 字段 = 新值,… where 条件;
例:修改 myclass 表中编号为1的记录,将degree 值改成89.99
mysql> update myclass set degree=89.99 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from myclass;
+----+-------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+-------+-----+--------+----------+
| 1 | david | 1 | 89.99 | 78 |
| 2 | sandy | 0 | 100.00 | 90 |
| 3 | renee | 0 | 90.34 | 0 |
+----+-------+-----+--------+----------+
3 rows in set (0.00 sec)
mysql>
八、数据的导入导出