MySQL 数据库表操作

第1章表的介绍

MySQL中的表就和我们excel表格中的sheet一样，一个excel里面可以有多个sheet。在MySQL数据库里面，一个库也可以有多个表，每个表中记录了无数条的数据。这些关系型的数据显示为列和行（类似excel表格）。我们队MySQL的表操作就是对一个sheet操作。

第2章表的操作

2.1查看表

表是存在于数据库中，所以我们看的表应该是在某个库的下面例如：database.sheet表示数据库database下面的sheet表。下面我们来学习SQL命令来查看表：

                         
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student         |
| 
test
|
| test2           |
+------------------+
3 rows 
in
set
(0.01 sec)
mysql> show create tabletest\G
*************************** 1. row***************************
       
Table:
test
Create Table: CREATE TABLE `
test
` (
  
`
id
` int(4)NOT NULL AUTO_INCREMENT,
  
`name`char(20) NOT NULL,
  
PRIMARY KEY(`
id
`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULTCHARSET=utf8
1 row 
in
set
(0.00 sec)
mysql> desc 
test
;
+-------+----------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra         |
+-------+----------+------+-----+---------+----------------+
| 
id
|int(4)   | NO   | PRI | NULL    | auto_increment |
| name  |char(20) | NO   |     | NULL   |                |
+-------+----------+------+-----+---------+----------------+
2 rows 
in
set
(0.01 sec)
小结：
1、show tables; 
#显示库里面包含的所有表
2、desc 
test
;    
#显示表的结构
3、show create table 
test
;  
#显示创建表的SQL语句
        
      

2.2表的创建

我们可以通过help create or help contents来查看帮助

                         
mysql> create table student_test( 
id
int(4) notnull, name char(20) not null, age tinyint(2) NOT NULL default 
'0'
, dept varchar(16) default NULL  );   
#
创建一个表为student_test，有4个字段
        
      

可以通过desc来查看表的结构

                         
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  |char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
4 rows 
in
set
(0.00 sec)
        
      

2.3表的删除

方法一：drop命令

                         
mysql> drop table student_test;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student         |
| 
test
|
| test2           |
+------------------+
3 rows 
in
set
(0.00 sec)
        
      

方法二：delete命令

我们用上面表创建的命令重新创建表：student_test

                         
create table student_test( 
id
int(4) not null, namechar(20) not null, age tinyint(2)  NOTNULL default 
'0'
, dept varchar(16) default NULL  );
        
      

查看表

                         
show tables
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student         |
| student_test    |
| 
test
|
| test2           |
+------------------+
4 rows 
in
set
(0.00 sec)
        
      

                         
删除表
delete 
from
表名：
删除所有记录，表结构还在，写日志，可以恢复的，速度慢
        
      

方法三：truncate

                         
truncate talbe 表名
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
小结：
delete from 表名：
删除所有记录，表结构还在，写日志，可以恢复的，速度慢
drop talbe 表名：
删除表的结构和数据
truncate talbe 表名
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
        
      

使用场合：

当你不再需要该表时，用 drop;

当你仍要保留该表，但要删除所有记录时，用 truncate;

当你要删除部分记录时（always with a whereclause), 用 delete.

注意：

对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器

2.4表的修改

2.4.1添加字段

                         
mysql> alter table student_test add sex char(2)after age;  
#这里使用了参数after，表示在什么之后插入，如果在第一行
QueryOK, 0 rows affected (0.02 sec)                            firs参数。
Records: 0 Duplicates: 0  Warnings: 0
 
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  |char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| sex   | char(2)    | YES  |     | NULL   |       |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
5 rows 
in
set
(0.00 sec)
        
      

2.4.2修改type类型

                         
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  |char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| sex   | char(2)     | YES |     | NULL    |      |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
5 rows 
in
set
(0.00 sec)
 
mysql> alter tablestudent_test modify sex varchar(4);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0  Warnings: 0
 
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  |char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| sex   | varchar(4)  | YES |     | NULL    |      |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
5 rows 
in
set
(0.00 sec)
        
      

2.4.3删除部分字段

                         
mysql> alter tablestudent_test drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0  Warnings: 0
 
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  |char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
4 rows 
in
set
(0.00 sec)
        
      

2.4.4改变表的字符集

                         
mysql> show create table student_test\G
*************************** 1. row***************************
       
Table:student_test
Create Table: CREATE TABLE `student_test` (
  
`
id
` int(4)NOT NULL,
  
`name`char(20) NOT NULL,
  
`age`tinyint(2) NOT NULL DEFAULT 
'0'
,
  
`dept`varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row 
in
set
(0.00 sec)
 
mysql> alter tablestudent_test CONVERT TO CHARACTER SET latin1;
mysql> show create table student_test\G         
*************************** 1. row***************************
       
Table:student_test
Create Table: CREATE TABLE `student_test` (
  
`
id
` int(4)NOT NULL,
  
`name`char(20) NOT NULL,
  
`age`tinyint(2) NOT NULL DEFAULT 
'0'
,
  
`dept`varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row 
in
set
(0.00 sec)
        
      

2.4.5修改字段名字已经type类型

把name字段改成xingming，类型从char改成varchar

                         
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| name  | char(20)    | NO   |    | NULL    |       |
| age   |tinyint(2)  | NO   |    | 0       |       |
| dept  |varchar(16) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
4 rows 
in
set
(0.01 sec)
 
mysql> alter table student_test change columnname xingming varchar(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0  Warnings: 0
 
mysql> desc student_test;
+----------+-------------+------+-----+---------+-------+
| Field    |Type        | Null | Key | Default |Extra |
+----------+-------------+------+-----+---------+-------+
| 
id
|int(4)      | NO   |    | NULL    |       |
| xingming | varchar(40) | YES |     | NULL    |      |
| age      |tinyint(2)  | NO   |    | 0       |       |
| dept     |varchar(16) | YES  |     | NULL   |       |
+----------+-------------+------+-----+---------+-------+
4 rows 
in
set
(0.00 sec)
        
      

2.4.6表的重命名

把student_test表重命名为stu_test

                         
ysql> rename table student_test to stu_test;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| stu_test        |
| student         |
| 
test
|
| test2           |
+------------------+
4 rows 
in
set
(0.00 sec)
        
      

第3章表数据的操作

3.1insert命令插入数据

数据的表的插入使用命令insert，语法insert into 表名

                         
mysql> insert into stu_testvalues(1,
'kirk'
,28,
'male'
);
Query OK, 1 row affected (0.00 sec)
 
mysql> 
select
* from stu_test;                        
+----+----------+-----+------+
| 
id
| xingming | age | dept |
+----+----------+-----+------+
|  1 |kirk     |  28 | male |
+----+----------+-----+------+
1 row 
in
set
(0.00 sec)
        
      

3.1.1插入数据遇到的问题

ERROR 1300 (HY000): Invalid utf8 character string: '\xA1\xAFaaa\xA1\xAF'

解决办法：引号问题’ ’这种是GB2312 , ' '这种是utf-8

ERROR 1366 (HY000): Incorrect stringvalue: '\xC4\xD0' for column 'sex' at row 1

解决办法：show variables like 'chara%';

setcharacter_set_client=gb2312;

该处查看会显示乱码

解决方法：set character_set_results=gb2312;

3.2修改表数据update

                         
mysql> update stu_test setxingming=
'laowang'
where 
id
=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1  Warnings: 0
 
mysql> 
select
* from stu_test;                          
+----+----------+-----+------+
| 
id
| xingming | age | dept |
+----+----------+-----+------+
|  1 |laowang  |  28 | male |
|  2 |huasheng |  23 | male |
|  3 |dadou    |  20 | nv  |
+----+----------+-----+------+
3 rows 
in
set
(0.00 sec)
        
      

第4章表的查询

4.1条件查询

                         
mysql> 
select
* from stu_test where xingming=
'dadou'
;
+----+----------+-----+------+
| 
id
| xingming | age | dept |
+----+----------+-----+------+
|  3 |dadou    |  20 | nv  |
+----+----------+-----+------+
1 row 
in
set
(0.00 sec)
 
mysql> 
select
xingming,age from stu_test;
+----------+-----+
| xingming | age |
+----------+-----+
| laowang |  28 |
| huasheng | 23 |
| dadou   |  20 |
+----------+-----+
3 rows 
in
set
(0.00 sec)
 
+----+----------+-----+------+
| 
id
| xingming | age | dept |
+----+----------+-----+------+
|  1 |laowang  |  28 | male |
|  2 |huasheng |  23 | male |
|  3 |dadou    |  20 | nv  |
|  4 |dadou    |  20 | male |
+----+----------+-----+------+
4 rows 
in
set
(0.00 sec)
 
mysql> 
select
20 from stu_test;
+----+
| 20 |
+----+
| 20 |
| 20 |
| 20 |
| 20 |
+----+
4 rows 
in
set
(0.00 sec)
 
mysql> 
select
distinct20 from stu_test;
+----+
| 20 |
+----+
| 20 |
+----+
1 row 
in
set
(0.00 sec)
 
mysql> 
select
xingming,(age+dept) from stu_test;
+----------+------------+
| xingming | (age+dept) |
+----------+------------+
| laowang |         28 |
| huasheng |        23 |
| dadou   |         20 |
| dadou   |         20 |
+----------+------------+
4 rows 
in
set
, 3 warnings (0.01 sec)
 
mysql> selectxingming,(age+dept)+10 from stu_test;   
#在上面的基础上加10
+----------+---------------+
| xingming | (age+dept)+10 |
+----------+---------------+
| laowang |            38 |
| huasheng |            33 |
| dadou   |            30 |
| dadou   |            30 |
+----------+---------------+
4 rows 
in
set
, 3 warnings (0.00 sec)
小结：
select
* from student where name=
'王五'
;
select
name,english from student;
select
distinct english from student;
 
select
name,(chinese+english+math) fromstudent;
select
name,(chinese+english+math)+10 fromstudent;
 
select
name as 姓名,(chinese+english+math)+10 as 总分 from student;  
//
别名
select
name 姓名,(chinese+english+math)+10  总分 from student;
 
select
* from student whereenglish>
'90'
;
select
name from student where(chinese+english+math)>200;
select
name from student where(chinese+english+math)> 
'200'
//
这里用单引号也是可以的
 
select
name from student whereenglish>80 and english<90;
select
name from student where Englishbetween 80 and 90;  
//
这句跟上面那句是一样的
 
select
* from student where mathin(89,90,91);
        
      

4.2模糊查询

使用like 加上百分号来匹配

                         
select
* from student where name like 
'李_'
; （2个字符）
select
* from student where name like 
'李%'
;  (2个字符以上)
        
      

4.3排序查询

order by 排序查询

                         
select
name,math from student order by math;  
#默认是升序排序
select
name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc; 
#desc参数是降序
select
name 姓名,(chinese+english+math) 总分 from student order by 总分 desc;
select
* from student where name like 
'李%'
order by (chinese+english+math) desc;
        
      

4.4统计数据

我们有时候要看一个表里面有多少条数据，需要统计

                         
select
count(name) from student; 
##查看name字段有多少条记录
select
count(*) from student;
select
count(*) from student where(chinese+english+math)>250;
        
      

4.5数据求和

求和

                         
select
sum
(chinese),
sum
(english),
sum
(math) fromstudent;
 
select
sum
(chinese+english+math) from student;
        
      

4.6求平均

                         
select
avg(chinese) from student;
select
avg(chinese+math+english) from student;

本文转自 kesungang 51CTO博客，原文链接：http://blog.51cto.com/sgk2011/1844109，如需转载请自行联系原作者

第1章 表的介绍

第2章 表的操作