MySQL的基本操作

MySQL的基本操作

SQL 语句分类

1. Data Definition Language (DDL 数据定义语言) 如：建库，建表

2. Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改

3. Data Query Language(DQL 数据查询语言)，如：对表中的查询操作

4. Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

启动\停止数据库

net start mysql
net stop mysql

登录数据库

mysql -u 用户名 -p 密码
mysql -u 用户名 -p
mysql -hip地址 -u用户名 -p密码
mysql --host=ip地址 --user=用户名 --password=密码 

退出MySQL

quit或exit

DDL 操作数据库

创建数据库

create database 数据库名;
create database if not exists 数据库名;
create database 数据库名 character set 字符集;

查看数据库

-- 查看所有的数据库
show databases;

-- 查看某个数据库的定义信息
show create database 数据库名; 

修改数据库

-- 修改数据库默认的字符集
alter database 数据库名 character set 字符集（utf8\gbk）;

删除数据库

drop database 数据库名;

使用数据库

-- 查看正在使用的数据库
select database();-- 使用的一个 mysql 中的全局函数

-- 改变要使用的数据库
use 数据库名;

DDL 操作表结构

前提先使用某个数据库

创建表

CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);

MySQL 数据类型

常使用的数据类型

类型	描述
int	整型
double	浮点型
varchar	字符串型
date	日期格式，格式为yyy-MM-dd，只有年月日没有时分秒

详细的数据类型

分类	类型名称	类型说明
整数	tinyInt	微整型：很小的整数(占 8 位二进制)
	smallint	小整型：小的整数(占 16 位二进制)
	mediumint	中整型：中等长度的整数(占 24 位二进制)
	int(integer)	整型：整数类型(占 32 位二进制)
小数	float	单精度浮点数，占 4 个字节
	double	双精度浮点数，占 8 个字节
日期	time	表示时间类型
	date	表示日期类型
	datetime	同时可以表示日期和时间类型
字符串	char(m)	固定长度的字符串，无论使用几个字符都占满全部，M 为 0~255 之间的整数
	varchar(m)	可变长度的字符串，使用几个字符就占用几个，M 为 0~65535 之间的整数
大二进制	tinyblob Big Large Object	允许长度 0~255 字节
	blob	允许长度 0~65535 字节
	mediumblob	允许长度 0~167772150 字节
	longblob	允许长度 0~4294967295 字节
大文本	tinytext	允许长度 0~255 字节
	text	允许长度 0~65535 字节
	mediumtext	允许长度 0~167772150 字节
	longtext	允许长度 0~4294967295 字节

mysql 4.0版本以下，varchar(50), 指的是50字节，如果存放utf8汉字时，只能存放16个（每个汉字3字节）

mysql 5.0版本以上，varchar(50), 指的是50字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个。

查看表

-- 查看某个数据库中的所有表
show tables;

查看表结构
desc 表名；

查看创建表的 SQL 语句
show create table 表名；

快速创建一个表结构相同的表

create table 新表名 like 旧表名;

删除表

-- 直接删除表
drop table 表名;

-- 判断表是否存在，如果存在则删除表
drop table if exists 表名;

修改表结构

-- 添加表列 ADD
alter table 表名 add 列名 类型;

-- 修改列类型 MODIFY
alter table 表名 modify 列名 新的类型;

-- 修改列名 CHANGE
alter table 表名 change 旧列名 新列名 类型;

-- 删除列 DROP
alter table 表名 drop 列名;

-- 修改表名
rename table 表名 to 新表名;

-- 修改字符集character set
alter table 表名 character set 字符集;

DML 操作表中的数据

插入记录

-- 所有的字段名都写出来
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);

-- 不写字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);

-- 插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);

1. 插入的数据应与字段的数据类型相同

2. 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。

3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。在 mysql 中可以使用 value，但不建议使用，功能与 values 相同。

4. 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。

5. 不指定列或使用 null，表示插入空值。

解决DOS 命令窗口操作数据乱码问题

• 查看包含 character 开头的全局变量

  • show variables like ‘character%’;

• 修改 client、connection、results 的编码为 GBK，保证和 DOS 命令行编码保持一致

  • 单独设置	说明
    set character_set_client=gbk;	修改客户端的字符集为 GBK
    set character_set_connection=gbk;	修改连接的字符集为 GBK
    set character_set_results=gbk;	修改查询的结果字符集为 GBK

  • 或者set names gbk;同时设置三项，退出 DOS 命令行就失效了，需要每次都配置

蠕虫复制

将一张已经存在的表中的数据复制到另一张表中。

-- 将表名2 中的所有的列复制到表名1 中
INSERT INTO 表名1 SELECT * FROM 表名2;

-- 只复制部分列
INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM 表名2;

更新表记录

-- 不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行

-- 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

删除表记录

-- 不带条件删除数据
DELETE FROM 表名; -- MySQL表中的所有记录将被删除。

-- 带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;

-- 使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名;

truncate 相当于删除表的结构，再创建一张表。效率比不带条件的update高。

DQL 查询表中的数据

-- 查询表所有行和列的数据
SELECT * FROM 表名;

-- 查询指定列的数据,多个列之间以逗号分隔
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;

-- 指定列的别名进行查询
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;

-- 查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;

-- 某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;

-- 某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名; -- 参与运算的必须是数值类型

-- 条件查询
SELECT 字段名 FROM 表名 WHERE 条件;
-- 取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

比较运算符

比较运算符	说明
>、<、<=、>=、=、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!=没有==
BETWEEN…AND	在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾
IN(集合)	集合表示多个值，使用逗号分隔
LIKE ‘张%’	模糊查询
IS NULL	查询某一列为 NULL 的值，注：不能写=NULL

逻辑运算符

逻辑运算符	说明
and 或 &&	与，SQL 中建议使用前者，后者并不通用。
or 或 **	或
not 或 !	非

in 关键字

SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
-- in 里面的每个数据都会作为一次条件，只要满足条件的就会显示

范围查询

BETWEEN 值 1 AND 值 2
-- 表示从值 1 到值 2 范围，包头又包尾

like 关键字

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
-- LIKE 表示模糊查询

通配符	说明
%	匹配任意多个字符串
-	匹配一个字符

DQL 查询语句

排序

-- 通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
/*
ASC: 升序，默认值
DESC: 降序
*/

-- 组合排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

聚合函数

SQL中的聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

SELECT 聚合函数(列名) FROM 表名;

-- 查询 id 字段，如果为 null，则使用 0 代替
select IFNULL(列名，默认值) from student; -- 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。

分组

-- 分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

having 与 where 的区别

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2) where 后面不可以使用聚合函数
having 子句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2) having 后面可以使用聚合函数

limit 语句

-- LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];

LIMIT offset,length;

offset：起始行数，从 0 开始计数，如果省略，默认就是 0（length*(页数-1)）

length： 返回的行数