今天整理了一了一下数据表的基本操作，希望能帮助有需要的人！！！！
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:testCreate 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=utf81 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 tablesmysql> 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_testCreate 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=utf81 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_testCreate 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=latin11 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
m
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;