MySQL数据库操作与单表操作（CRUD）

来源：互联网发布：安居客网络经纪人登陆编辑：程序博客网时间：2024/05/14 11:21

声明：以下操作都是基于MySQL5.7.13这个版本的

1 MySQL数据库操作

常见的MySQL数据库管理涉及如下操作：

开启关闭MySQL服务
查询所有数据库
创建数据库
查看数据库信息与修改数据库的默认字符集
删除数据库
等

1.0 开启关闭MySQL服务

1.0.1 通过命令行的方式

在cmd命令行下输入如下命令来开启mysql数据库服务：

net start mysql;

在cmd命令行下输入如下命令来关闭mysql数据库服务：

net stop mysql;

注：cmd命令行须在管理员权限下进行，mysql指创建时的mysql服务的名称。

1.0.2 在系统服务service中配置

在电脑的控制面板搜索‘服务’，在结果中点击‘查看本地服务’即可打开服务页面。在此页面找到我们的mysql服务进行配置
这里写图片描述

1.1 查询所有数据库

使用如下命令进行查询所有的数据库

mysql> show databases;

查询结果如下：

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || yu                 |+--------------------+5 rows in set (0.00 sec)

MySQL5.7默认有这几个数据库：information_schema、mysql、performance_schema、sys。

information_schema

提供了访问MySQL服务器的数据库元数据、信息（诸如数据库名或表名、字段的数据类型或访问权限）的方式。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

mysql

配置数据库,记录用户权限、帮助、日志等信息。

performance_schema

主要用于收集数据库服务器性能参数，性能数据。

sys

一组对象来帮助DBAs和开发人员翻译从 Performance Schema收集的数据。sys schema对象可以用于典型的调优和诊断用例。

1.2 创建数据库

通过如下命令创建数据库

create database db_name ；

创建时还可以指定默认字符集

create database db_name default character set utf8 ；

default character set= utf8 指定默认字符集utf-8创建数据库，等号可以省略。

如果我们使用的是一些数据库软件管理工具，如SQLyog，按如下步骤

数据库 –> 创建数据库 –> 配置创建参数 –> 单击创建

1.3 查看数据库信息与修改数据库的默认字符集

通过如下命令查看数据库的信息

show create database db_name;

或

show create schema db_name;

通过如下命令修改数据库默认的字符集

alter database db_name character set=charset;

如设置数据库db的字符集为utf-8

alter database db character set= utf8;

1.4 删除数据库

通过如下命令删除数据库，此操作不可撤销

drop database db_name;

在 SQLyog中，选中要删除的数据库，然后通过 ‘更多数据库操作’ 进行数据库的删除

2 表的操作

在我们操作一个表之前，我们要先打开它所在的数据库，通过如下命令打开：

use db_name;

2.1 数据库服务器、数据库和表的关系

在操作表之前，先要明白表、数据库、数据库服务器之间的关系

这里写图片描述

2.2 创建表

通过如下命令创建表

create table table_name(字段1 类型，字段2 类型， … ) ;

如下示例创建一个学生表:

mysql> create table student(    -> sid int,    -> sname varchar(20),    -> sage int    -> );

在创建表的时候还可以为字段添加约束（非空、主键、外键、唯一、自增长、默认等），设置默认引擎，默认字符集，如下示例：

CREATE TABLE `db` (  `id` int primary key,  `db` double DEFAULT NULL,  `dm` decimal(10,0) DEFAULT NULL,  `gender` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

通过desc 查看创建的表如下

mysql> desc  db_table;+--------+---------------+------+-----+---------+-------+| Field  | Type          | Null | Key | Default | Extra |+--------+---------------+------+-----+---------+-------+| id     | int(11)       | NO   | PRI | NULL    |       || db     | double        | YES  |     | NULL    |       || dm     | decimal(10,0) | YES  |     | NULL    |       || gender | int(11)       | YES  |     | NULL    |       |+--------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

2.3 查看表结构与查看所有表

查看表结构

在上面我们通过desc table_name查看了表的结构，我们还可以通过如下命令来查看：

DESCRIBE table_name; 或SHOW CREATE TABLE table_name;

诸如我们通过SHOW CREATE TABLE table_name; 来查看 db_table表的结构

mysql> show create table  db_table;+----------+------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                                                                 |+----------+--------------------------------------------------------------------------| db_table | CREATE TABLE `db_table` (              `id` int(11) NOT NULL,              `db` double DEFAULT NULL,              `dm` decimal(10,0) DEFAULT NULL,              `gender` int(11) DEFAULT NULL,              PRIMARY KEY (`id`)            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+------------------------------------------------------------------------1 row in set (0.00 sec)

查看所有表
使用命令 show tables; 来查看所有表。如下所示：

mysql>  show tables;+--------------+| Tables_in_yu |+--------------+| db_table     || human        || jdbc         || number       || person       |+--------------+5 rows in set (0.00 sec)

2.4 删除表

使用如下命令来删除一个表

drop table table_name ;

2.5 修改表

2.5.1 修改表名

修改表名的命令如下， to可省略：

alter table old_table_name rename to new_table_name ;

2.5.2 添加字段

1 新添加的字段默认添加到末尾，命令示例如下，COLUMN可省略：

ALTER TABLE db_name ADD COLUMN field_name VARCHAR(50);

2 添加字段到表的第一个位置，使用 first

ALTER TABLE db_name ADD COLUMN address VARCHAR(50) first;

执行上面命令后再查看表，发现address被添加到第一个位置

    mysql> desc db_table;    +---------+---------------+------+-----+---------+-------+    | Field   | Type          | Null | Key | Default | Extra |    +---------+---------------+------+-----+---------+-------+    | address | varchar(50)   | YES  |     | NULL    |       |    | id      | int(11)       | NO   | PRI | NULL    |       |    | db      | double        | YES  |     | NULL    |       |    | dm      | decimal(10,0) | YES  |     | NULL    |       |    | gender  | int(11)       | YES  |     | NULL    |       |    +---------+---------------+------+-----+---------+-------+    5 rows in set (0.00 sec)

3 添加字段到指定字段后的位置，使用after

alter table db_table add column name varchar(20) after id;

执行上面命令后再查看表:

mysql> desc db_table;+---------+---------------+------+-----+---------+-------+| Field   | Type          | Null | Key | Default | Extra |+---------+---------------+------+-----+---------+-------+| address | varchar(50)   | YES  |     | NULL    |       || id      | int(11)       | NO   | PRI | NULL    |       || name    | varchar(20)   | YES  |     | NULL    |       || db      | double        | YES  |     | NULL    |       || dm      | decimal(10,0) | YES  |     | NULL    |       || gender  | int(11)       | YES  |     | NULL    |       |+---------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

2.5.3 修改字段类型

诸如想将db_table表的name字段的类型修改为text，可执行如下命令：

alter table db_table modify column name text ;

2.5.4 修改字段名称

通过change命令来修改一个字段的名字，诸如想将db_table表的db字段更名为dl，可执行如下sql语句：

alter table db_table change column db dl double ;

2.5.5 删除字段

通过drop命令来删除字段，如下删除db_table表的dm字段：

alter table db_table drop dm;

执行上sql语句后查看表，如下看出name字段的类型被更改为text、db字段被更名为dl，dm字段被删除了。

mysql> desc db_table;+---------+---------------+------+-----+---------+-------+| Field   | Type          | Null | Key | Default | Extra |+---------+---------------+------+-----+---------+-------+| address | varchar(50)   | YES  |     | NULL    |       || id      | int(11)       | NO   | PRI | NULL    |       || name    | text          | YES  |     | NULL    |       || dl      | double        | YES  |     | NULL    |       || gender  | int(11)       | YES  |     | NULL    |       |+---------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)

2.6 增删改查数据

2.6.0 创建一张表

CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `NAME` varchar(20) DEFAULT NULL,  `age` int(4) DEFAULT NULL,  `gender` char(6) DEFAULT NULL,  `salary` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

2.6.1 插入数据

1 插入所有字段。

依次按顺序插入

insert into person values(1,’niko’,20,’male’，20000);

注意插入不能少或多字段值 ，如下错误

INSERT INTO person VALUES(2,’halo’,’female’); // 错误，字段少了，插入不完整

2 插入部分字段

指定插入的数据字段，依次插入

insert into person(name,age,gender) values(‘helen’,20,’female’);

2.6.2 更新数据

1 修改所有字段数据

不推荐使用此方式，一修改全被修改。除非有特殊需求

update person set gender=’male’;

2 带条件的修改，

推荐使用此方式，可以有选择的修改。诸如修改id为2的学生性别为female

update person set gender=’female’ where id=2;

3 修改多个字段,分号隔开。语法: SET 字段名=值,字段名=值 , ….

update person set age=25,name=’kity’ where id=2;

2.6.3 删除数据

1 delete删除所有数据

建议少用，除非特殊需求

delete from person;

2 delete带条件的删除

推荐使用，较为灵活

delete from person WHERE id=2 ;

3 使用TRUNCATE删除

TRUNCATE TABLE person;

TRUNCATE 不可带条件删除，如下错误

truncate table human where id=4; （错误）

4 delete与TRUNCATE的比较

delete TRUNCATE 可以全表删除可以全表删除可以带条件删除不能带条件删除只能删除表的数据,不能删除表的约束既可以删除表的数据，也可以删除表的约束删除的数据可以回滚删除的数据不可以回滚

2.6.4 查询数据

通过select … from 查询数据

2.6.4.1 查询所有字段

语法： select * from table_name;

还可以在选择时列出所有字段名来替代*，这种方式灵活但要做更多的处理。两种方式的使用视情况而定。

2.6.4.2 查询指定字段

语法： select field_x,field_y,field_z, … from table_name;

要想控制显示的结果，只需调整sql语句中字段的位置即可。选择gender,name 两列，示例如下：

mysql> select gender,name from person;+--------+-------+| gender | name  |+--------+-------+| male   | niko  || female | kity  || male   | peter || male   | kity   |+--------+-------+4 rows in set (0.00 sec)

2.6.4.3 查询并添加常量字段

添加常量列，字段名为国籍，值为天朝

select name,age,’天朝’ as ‘国籍’ from person;

如下：

mysql> select name,age,'天朝' as '国籍' from person;+-------+------+------+| name  | age  | 国籍 |+-------+------+------+| niko  |   20 | 天朝 || kity  |   25 | 天朝 || peter |   22 | 天朝 || kiy   |   25 | 天朝 |+-------+------+------+4 rows in set (0.03 sec)

2.6.4.4 查询去重(DISTINCT)

查询非重复的gender

mysql> select distinct gender from person;+--------+| gender |+--------+| male   || female |+--------+

2.6.4.5 查询时的数学四则运算

在执行查询的同时，我们可能会对查询的字段执行一些简单的数学运算，如下列出了所支持的运算：

运算符描述 + 加法 - 减法 * 乘法 / 除法 % (mod) 求余

以加法为例。将列对应的内容求和，如合并age与salary列

select age,salary, (age+salary) as ‘和’ from person;

查询结果如下所示：

mysql> select age,salary, (age+salary) as '和' from person;+------+--------+-------+| age  | salary  | 和    |+------+--------+-------+|   20 |  30000 | 30020 ||   25 |  30000 | 30025 |+------+--------+-------+

注意：只能对数值类型的字段进行运算。

2.6.4.6 条件查询

有如下查询条件:

1. where条件查询
2. 逻辑条件：

运算符说明 and 与 or 或 not 非 xor 逻辑异或

3. 比较条件：

运算符说明 > 大于 < 小于 >= 大于等于 <= 小于等于 = 等于 <>(不等于) 不等于 between and (等价于>= 且 <=) 在… 之间

4. 判空条件 : 包括 null和空字符串：

条件说明 is null 为null is not null 不为null =” 为空字符串 <>” 不为空字符串

注意： null与空字符串的区别：null表示没有值，空字符串是有值的
5. 模糊条件： like
常见占位符：

% : 匹配任意个字符， _ : 匹配一个字符

使用示例如下：

where条件查询、逻辑条件

-- 查询id为4，且姓名为peter的性别mysql> select gender from person where id=4 and name='peter' ;  交集   -- 查询id为5，或姓名为niko的名字mysql> select name from person where id=5 or name='niko';   并集

比较条件

-- Salary大于30000的人mysql> select name from person where salary>30000 ;   -- salary大于20000并且小于40000的人mysql>select name from person where salary>20000 and salary<40000;   -- 选出id不为4的人mysql>select name from person where id<>4;

between and (等价于>= 且 <= , 包前包后 )

salary大于等于20000并且小于等于40000的人mysql> select name from person where salary between 20000 and 40000;

判空条件(null 空字符串)： is null / is not null / =” / <>”

-- 判断nullSELECT * FROM person WHERE salary IS NULL ;-- 判断空字符串SELECT * FROM person WHERE salary ='';   -- 查询salary为空的人 包括null和空字符串 SELECT * FROM person WHERE salary IS NULL OR salary =''; -- 查询有地址的人(不包括null和空字符串)SELECT * FROM person WHERE salary IS NOT NULL AND salary <>'';

模糊条件： like
通常使用以下替换标记：

% : 表示任意个字符
_ : 表示一个字符

-- 查询名字中有o的人select name from person where name like '%o%';   -- 查询名字以k开头且只有三个字母的人mysql> select name from person where name like 'k_ _';

2.6.4.7 排序查询

使用order by来进行字段数据的排序，有升序与降序之分。默认情况下，按照插入时记录顺序排序。既可以使用单字段来排序，也可以使用多个字段来排序（多个字段条件用分号隔开）。

语法： select …. from table_name order by 字段 asc / desc ;

asc: 顺序，正序。数值：递增 ; 字母：自然顺序（a-z）
desc: 降序，反序。数值：递减 ; 字母：自然反序(z-a)

--按照id顺序排序select *from person order by id asc;  -- 默认正序SELECT * FROM student ORDER BY id;-- 反序select *from person order by id desc; -- 多个字段排序条件 ， 按照id正序，按照salary倒序select *from person order by id asc,salary desc;

2.6.4.8 聚合查询

使用聚合函数的查询

语法：select function(field) from table_name where condition;

常用的聚合函数：

函数功能说明 sum(field) 求和忽略null值 avg(field) 平均值忽略null值 max(field) 最大值忽略null值 min(field) 最小值忽略null值 count(field) 统计字段有效数量忽略null值 count(*) 通过所有行不忽略null值

使用示例：

-- 查询salary 的总和(sum() :求和函数)select sum(salary) from person;-- 查询平均salaryselect avg(salary) from person; -- 查询当前最高salary select max(salary) from person;-- 查询最salary select min(salary) from person;-- 统计当前有多少个salary值 (count(字段))select count(salary) from person;

注意：

1）使用count（field）函数统计的数量不包含为null的数据，若使用count统计表的记录数，要使用不包含null值的字段，而使用count(*)时将不会忽略null，统计所有。
2）使用聚合函数查询时，不能和having一起使用。
3）当所操作的表为空表时，count()返回0，其他函数则返回null

2.6.4.9 limit限制查询

使用limit查询指定范围的数据

1 不指定初始查询位置

语法1： select … from table_name limit count；

从第第一条记录开始查询，count表示查询的行数。

mysql> select *from person limit 2;+----+------+------+--------+--------+| id | NAME | age  | gender | salary |+----+------+------+--------+--------+|  1 | niko |   20 | male   |  30000 ||  2 | kity |   25 | female |  30000 |+----+------+------+--------+--------+2 rows in set (0.00 sec)

2 指定初始查询位置（分页查询）

语法2： select … from table_name limit 起始行,查询行数

注意：起始行从0开始

分页：当前页+ 每页显示多少条，分页查询当前页数据的sql语句：

select * from table_name limit (当前页-1)*每页显示多少条,每页显示多少条;

-- 查询第1,2条记录（第1页的数据）select *from person  limit 0,2;-- 查询第3,4条记录（第2页的数据）select *from person  limit 2,2;-- 查询第5,6条记录（第3页的数据）select *from person  limit 4,2;-- 查询第7,8条记录 (没有记录不显示)SELECT * FROM student LIMIT 6,2; Empty set (0.00 sec)

2.6.4.10 分组查询

1 单字段group by对查询结果进行分组

语法： select … from table_name group by 字段；

-- 把人按照性别分组(GROUP BY gender), 并统计每组的人数(COUNT(*))select gender, count(*) from  person group by gender;

2 使用group_concat()函数显示分组字段值

语法： select group_contact(field) from table_name group by field

比如当我们以gender分组后，我们还希望查看每个分组有哪些人

mysql> select gender,group_concat(name), count(*) from  person group by gender;+--------+--------------------+----------+| gender | group_concat(name) | count(*) |+--------+--------------------+----------+| female | kity,ll,helen      |        3 || male   | niko,peter,kiy     |        3 |+--------+--------------------+----------+2 rows in set (0.00 sec)

3 多字段group by对查询结果进行分组

语法： select … from table_name group by 字段1，字段2 ；

在上面的搜索结果中，我们还希望查看每个分组的salary详情，如下：

mysql> select gender,group_concat(name),salary, count(*) from  person group by gender,salary;+--------+--------------------+--------+----------+| gender | group_concat(name) | salary | count(*) |+--------+--------------------+--------+----------+| female | kity,ll,helen      |  30000 |        3 || male   | niko               |  30000 |        1 || male   | peter,kiy          |  40000 |        2 |+--------+--------------------+--------+----------+3 rows in set (0.00 sec)

4 having字句限定分组查询

分组查询后可以对结果进行一次筛选，使用having关键字

语法： select … from table_name group by 字段 having 条件；

例：查询性别总人数大于3的一组

-- 查询男女的人数筛选出人数大于2的记录(having)select gender, count(gender) from  person group by gender having count(*)>=3;

注意：分组之前条件使用where关键字，分组之前条件使用having关键字

2.6.4.11查询并指定格式输出

查询时，有时我们希望以一种自己定义的格式输出，以便于查看。MySQL提供了concat() 函数来连接字符串，以便我们能够设置输出的格式
比如我们想将person表的数据连成一句输出，就可以执行如下sql语句：

select concat(‘这个人的id：’,id,’ , 姓名：’,name,’ , 年龄：’,age,’ , 性别：’,gender, ’ , 工资：’,salary) from person;
如下输出：

+---------------------------------------------------------------------------------------------------+| concat('这个人的id：',id,' , 姓名：',name,' , 年龄：',age,' , 性别：',gender, ' , 工资：',salary) |+---------------------------------------------------------------------------------------------------+| 这个人的id：1 , 姓名：niko , 年龄：20 , 性别：male , 工资：30000                                  || 这个人的id：2 , 姓名：kity , 年龄：25 , 性别：female , 工资：30000                                || 这个人的id：4 , 姓名：peter , 年龄：22 , 性别：male , 工资：40000                                 || 这个人的id：5 , 姓名：kiy , 年龄：25 , 性别：male , 工资：40000                                   || NULL                                                                                              |+---------------------------------------------------------------------------------------------------+-------------------+

2.6.4.12 带in关键字的集合查询

关键字in用来查询某个字段的值是否在指定的一个集合之中。使用in关键字来实现条件查询，语法如下：

select field1 , field2 , field3 , … , fieldn from table_name where fieldx in (value1,value2,…);

如下示例：

mysql> select id, name,age,salary from person where id in (1,2,4);+----+-------+------+--------+| id | name  | age  | salary |+----+-------+------+--------+|  1 | niko  |   20 |  30000 ||  2 | kity  |   25 |  30000 ||  4 | peter |   22 |  40000 |+----+-------+------+--------+3 rows in set (0.02 sec)

同样的，我们可以使用not in来表示某字段不再一个集合之中。具体使用与in类似，就不贴示例了

注意事项：如果使用的是in关键字，当查询的集合有null值时，不会对查询的结果造成影响。如果是使用 not in 关键字，当集合中存在null值时，则查询结果总为空。

如下示例：

--  使用 not in ，当有null值时，对查询结果造成影响mysql> select id, name,age,salary from person where id not in (1,2,null);Empty set (0.00 sec)--  使用in ，当有null值时，对查询结果无影响mysql> select id, name,age,salary from person where id  in (1,2,null);+----+------+------+--------+| id | name | age  | salary |+----+------+------+--------+|  1 | niko |   20 |  30000 ||  2 | kity |   25 |  30000 |+----+------+------+--------+2 rows in set (0.00 sec)

后记：此篇为总结篇，较为详细的记录了单表的操作。本想加多表查询与约束的内容，却发现篇幅已成规模，不便于后面查阅，后续在记吧~

1 1