MySQL数据表所有操作命令

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介:

MySQL 数据库表操作

第1章 表的介绍

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

第2章 表的操作

2.1查看表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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来查看帮助

1
2
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来查看表的结构

1
2
3
4
5
6
7
8
9
10
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命令

1
2
3
4
5
6
7
8
9
10
11
12
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

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

查看表

1
2
3
4
5
6
7
8
9
10
11
show tables
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student         |
| student_test    |
test             |
| test2           |
+------------------+
4 rows  in  set  (0.00 sec)
1
2
3
删除表
delete  from  表名:
删除所有记录,表结构还在,写日志,可以恢复的,速度慢

 方法三:truncate

1
2
3
4
5
6
7
8
9
truncate talbe 表名
删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快
小结:
delete from 表名:
删除所有记录,表结构还在,写日志,可以恢复的,速度慢
drop talbe 表名:
删除表的结构和数据
truncate talbe 表名
删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快


使用场合: 
当你不再需要该表时,  drop; 
当你仍要保留该表,但要删除所有记录时,  truncate; 
当你要删除部分记录时(always with a whereclause),  delete. 

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

2.4表的修改

2.4.1添加字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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删除部分字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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改变表的字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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 表名

1
2
3
4
5
6
7
8
9
10
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;

wKiom1fESNGhSgeUAACGxBT1nnU918.jpg-wh_50

                                                       该处查看会显示乱码

解决方法:set character_set_results=gb2312;

3.2修改表数据update

1
2
3
4
5
6
7
8
9
10
11
12
13
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条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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 加上百分号来匹配

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

 

4.3排序查询

order by 排序查询

1
2
3
4
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统计数据

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

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

 

4.5数据求和

求和

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

4.6求平均

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



本文转自 kesungang 51CTO博客,原文链接:http://blog.51cto.com/sgk2011/1844109,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
关系型数据库 MySQL 索引
MySQL数据表添加字段的三种方式
MySQL数据表添加字段的三种方式
177 0
|
23天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之 MySQL数据库中,执行delete命令删除数据后,存储空间通常不会立即释放,该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
23天前
|
NoSQL 关系型数据库 MySQL
linux服务器重启php,nginx,redis,mysql命令
linux服务器重启php,nginx,redis,mysql命令
31 1
|
1月前
|
关系型数据库 MySQL 数据库
MySQL有哪些常用命令?
【6月更文挑战第12天】MySQL有哪些常用命令?
34 3
|
1月前
|
关系型数据库 数据管理 数据库
数据管理DMS产品使用合集之如何极速恢复RDS(关系型数据库服务)中的数据表
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
|
1月前
|
SQL 存储 关系型数据库
Mysql配置及常见命令
mysql 语法规范 SQL不区分大小写,但是建议关键字大写,表名、列名要小写; 每个句子写完,需要以分号结尾;
29 3
|
25天前
|
关系型数据库 MySQL 数据库
Mysql数据表操作CRUD
Mysql数据表操作CRUD
21 0
|
26天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用问题之mysql读取从mc里的每10分钟计算好的结果数据表,如何同步数据过去
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
28天前
|
关系型数据库 MySQL 数据库
Mysql的基础命令有哪些?
Mysql的基础命令有哪些?
15 0
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】DML之操作数据表
【MySQL进阶之路 | 基础篇】DML之操作数据表