MySQL基本操作

MySQL笔记

前言

MySQL的服务的启动与停止

net start mysql 		启用
net stop mysql  		停用

客户端连接

mysql [-h 127.0.0.1]  [-P 3306] -u root -p

注意:需要将mysql安装路径添加到Path环境变量中

关系型数据库(RDBMS)

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便

一.数据库和表的基本操作

1.1数据库基础知识

1.1.1创建数据库

语法:

CREATE DATABASE [IF NOT EXISTS] 数据库名
	[ [DEFAULT] CHARACTER  SET 字符集名
	| [DEFAULT] COLLATE 校对规则名];

实例:

create database if not exists default character set utf8;

1.1.2查看所有数据库

show databases

1.1.3查看数据的物理路径的方法

show variables like 'datadir';

1.1.4查看数据库的信息

show databases;	# 查看当前服务器中的所有数据库
show create database 数据库名; # 查看创建数据库语句

1.1.5修改数据库

alter database 数据库名 character set 要修改的字符编码；

1.1.6删除数据库

drop database 数据库名;

1.1.7使用数据库

use 数据库名;

1.1.8查看当前选择的数据库

show database();

1.2数据类型

1.2.1整数类型

​ MySQL支持的5种主要整数类型是TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。这些类型只是存储值的大小不相同。

数据类型	字节数	无符号的取值范围	有符号的取值范围
TINYINT	1	0~255	-128~127
SMSLLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT/INTEGER	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775807

1.2.2浮点数类型

​ MySQL支持的3种浮点类型是FLOAT、DOUBLE和DECIMAL类型。其中，FLOAT数值类型用于表示单精度浮点数值，而DOUBLE数值类型用于表示双精度浮点数值。 DECIMAL类型的取值范围与DOUBLE类型相同。

数据类型	字节数	无符号的取值范围	有符号的取值范围
FLOAT	4	略	略
DOUBLE	8	略	略
DECIMSL（M，D）	变长，整数部分和小数部分分开计算	略	略

1.2.3字符串类型

​ MySQL提供了8种基本的字符串类型，分别为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据，常见的字符串类型所对应的字节大小和取值范围如表所示。

数据类型	字节数	类型描述
CHAR	0~255	定长字符串
DOUBLE	0~65535	可变长字符串
TINYBLOB	0~255	不超过 255 个字符的二进制字符串
TINYTEXT	0~255	短文本字符串
BLOB	0~65535	二进制形式的长文本数据
TEXT	0~65535	长文本数据
MEDIUMBLOB	0~16777215	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215	中等长度文本数据
LOGNGBLOB	0~4294967295	二进制形式的极大文本数据
LOGNGTEXT	0~4294967295	极大文本数据
VARBINARY(M)	0~M	允许长度为0~M 个字节的变长字节字符集
BINARY(M)	0~M	允许长度为0~M 个字节的变长字节字符集

1.char和varchar类型

​ char把这个大小视为值的大小,在长度不同的情况下用空格不足,而varchar类型把它视为最大值,并且只有使用存储字符串实际需要的长度(增加一个额外字节来存储字符串本身的长度)来存储值,所以短与指示器场长度的varchar类型不会被空格填补,但是长于指示器的值仍然会被截断.

2.TEXT和BLOB类型

 对于字段长度超过255，MySQL提供了TEXT和BLOB两种类型。用于存储文本块或图像、声音文件等二进制数据类型。
 TEXT类型和BLOB类型的相同点具体如下。

1. 在TEXT或BLOB列的存储或检索过程中不存在大小写转换，当未运行在严格模式下时，如果为BLOB或TEXT列分配一个超过该列类型的最大长度值时，则值会被截取。如果截掉的字符不是空格，将会产生一条警告。
2. BLOB和TEXT列都不能有默认值。
3. BLOB和TEXT列都不能有默认值。
4. 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。

3.BINARY和VARBINARY类型

BINARY和VARBINARY类型表示二进制数据。数据定义如下：
BINARY（M）和VARBINARY(M)
M是指二进制数据的最大字节长度。如果数据的长度不足最大长度，后面用‘\0’补齐。

4. TEXT类型

​ TEXT类型用于存储文本数据。如文章的内容、评论等。
它分为4 种类型：

5. BLOB类型

​ BLOB类型是一种特殊的二进制类型，它用于表示数据量很大的二进制数据，如图片、PDF文档等。它分为4种类型：

6.ENUM类型

ENUM类型又称枚举类型，定义ENUM类型的数据格式如下：
ENUM(’值1’，’值2’，’值3’….’值4’)

7.SET类型

SET类型用于表示字符串对象，它的值可以有零个或多个，SET类型数据的定义格式与ENUM类型类似：
SET（’值1’，’值2’，’值3’….’值4’)

8.BT类型

BT类型用于表示二进制数据。定义的语法格式：
BIT (M)
M表示二制值的位数，范围在1~64之间。如bt(1)只能存储0或1。

2.1.4 日期和时间类型

​ 在处理日期和时间类型的值时，MySQL提供不同的数据类型。它们可以被分成简单的日期和时间类型、混合的日期和时间类型。日期和时间类型同样有对应的字节数和取值范围等。

​ 每种日期和时间类型都有一个有效范围。如果插入的值超过这个范围，系统会报错，并将0值插入到数据库中，不同的日期和时间类型有不同的0值。

1.YEAR–年份

YEAR类型使用1个字节来表示年份，以YYYY的形式来显示值。

1. 使用4位字符串和数字表示：其范围是1901~2155。输入格式为’YYYY’或YYYY。

   例如，输入‘2008’或者2008，可直接保存2008。如果超过了范围，就会插入0000。

2. 使用两位字符串表示。‘00’_{‘69’转换为2000}2069，‘70’_{‘99’转换为1970}1999。

   例如输入‘35’，YEAR值会转换成2035，输入‘90’，YEAR值会转换成1990。

3. 使用两位数字表示。1_{69转换为2001}2069，70_{99转换为1970}1999。

   另外，在对YEAR类型字段进行相关操作的时候，最好使用4位字符串或者数字表示，不要使用两位的字符串和数字。有时可能会插入0或者‘0’，此处要严格区分0和‘0’。如果向YEAR类型的字段插入0，存入该字段的年份是0000；如果向YEAR类型的字段插入‘0’，存入的年份是2000。

2.DATE类型----日期

DATE类型使用4个字节来表示日期。在MySQL中以YYYY-MM-DD的形式显示DATE类型的值，其中，YYYY表示年，MM表示月，DD表示日。DATE类型的字段赋值表示方法如下。

1. 表示‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串。

   例如：输入‘2014-2-8’，DATE类型将转换为2014-02-08；输入‘20140308’，DATE类型将转换为2014-03-08。

2. 在MySQL中还支持一些不严格的语法格式，任何标点都可以用来作间隔符：

   如‘YYYY/MM/DD’、‘YYYY@MM@DD’和‘YYYY.MM.DD’等分隔形式。例如：输入‘2011.3.8’，转换为2011-03-08。

3. 表示‘YY-MM-DD’或者‘YYMMDD’格式的字符串：其中‘YY’的取值‘00’_{‘69’转换为2000}2069，‘70’_{‘99’转换为1970}1999，与YEAR类型类似。

   例如：输入‘35-01-02’，DATE类型将转换为2035-01-02，输入‘800102’，DATE类型将转换为1980-01-02。

4. 使用current_date或current_date ()输入当前系统日期。

3. TIME类型----时间

TIME类型使用3个字节来表示时间。以HH:MM:SS显示TIME类型的值。其中，HH表示时（取值范围为0_{23），MM表示分（取值范围为0}59），SS表示秒（取值范围是0~59）。 TIME类型的范围可以从‘-838：59：59’_{‘838：59：59’。虽然小时的范围是0}23，但为了表示某种特殊需要的时间间隔，将TIME类型的范围扩大了，而且还支持了负值。

1. 表示‘D HH:MM:SS’格式的字符串。其中D表示天数（取值范围是0~34）。在保存时，小时的值等于D×24+HH。例如：输入‘2 11：30：50’，TIME类型会转换为59：30：50。

当然，在输入时可以不严格按照这个格式，可以是‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’、‘SS’等形式。 例如：输入‘30’，TIME类型会自动转换为00：00：30。

2. 表示‘HHMMSS’格式的字符串或HHMMSS格式的数值，例如输入‘123456’，TIME类型会转换成12：34：56。如果输入0或者‘0’，那么TIME类型会转换为0000：00：00。

3. 使用current_time或者current_time()输入当前系统时间。

4.DATETIME类型

DATETIME类型使用8个字节来表示日期和时间。以‘YYYY-MM-DD HH:MM:SS’的形式显示值：

1. 表示‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串：表达的范围是‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’。

   例如：输入‘2008-08-08 08:08:08’，自动转换为2008-08-08 08:08:08，
   输入‘20080808080808’，转换为2008-08-08 08:08:08。

2. DATETIME类型可以使用任何标点作为间隔符。

   例如：输入‘2008@08@08 080808’，DATETIME类型统一转换为2008-08-08 08:08:08。

3. 其中‘YY’的取值为‘00’_{‘69’转换为2000}2069，‘70’_{‘99’转换为1970}1999。

   例如：输入‘69-01-01 11:11:11’，在数据库中插入2069-01-01 11:11:11；
   输入‘70-01-01 11:11:11’，在数据库中插入1970-01-01 11:11:11。

4. 使用now()来输入当前系统日期和时间。

5.TIMESTAMP类型

TIMESTAMP类型使用4个字节来表示日期和时间。
TIMESTAMP类型的范围是1970-01-01 08:00:01~2038-01-19 11:14:07。

1. 使用CURRENT_TIMESTAMP来输入系统当前日期和时间；
2. 输入NULL时，系统会输入系统当前日期和时间；
3. 无任何输入时，系统会输入系统当前日期和时间

1.3数据表的基本操作

1.3.1创建数据表

CREATE TABLE 表名
(
  字段名1   数据类型[完整性约束条件],
  字段名2   数据类型[完整性约束条件],
   ．．．．．．
  字段名n   数据类型[完整性约束条件]
)      

注意:

在操作数据表之前，应该使用“USE 数据库名”指定使用的数据库，否则会抛出“No database selected”错误。

1.3.2查看数据表

显示数据库中所有的表的基本语法：

show tables;

查看创建表时的定义语句、表的字符编码的基本语法：

SHOW CREATE TABLE 表名;

查看表结构基本语法：

DESCRIBE 表名；

1.3.3修改数据表

1. 修改表名

   ALTER TABLE 旧表名 RENAME [TO] 新表名;
   RENAME TABLE 原表名 TO 新表名;
   

2. 修改字段名

   ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新数据类型;
   

   注意 ：新数据类型不能为空。

3. 修改字段的数据类型

   ALTER TABLE 表名 MODIFY 字段名  数据类型;
   

4. 添加字段

   ALTER TABLE 表名 ADD  新字段名  数据类型      [约束条件][ FIRST |AFTER 已存在字段名]
   

   FIRST” 是可选参数，用于将新添加的字段设置为表的第一个字段。

   “AFTER”是可选参数，用于将新添加的字段添加到指定的“已存在字段名”的后面。

5. 删除字段

   ALTER TABLE 表名 DROP 字段名;
   

6. 修改字段的排列位置

   ALTER TABLE表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
   

   字段名1:要修改位置的字段

   FIRST:将字段1修改为表的第一个字段

   AFTER 字段名2:将字段1插入到字段2的后面

1.3.4删除数据表

DROP TABLE 表名;

注意:创建数据表时，表和表之间可能会存在关联，要删除这些被其它表关联的表比较复杂，将在后面的章节进行讲解，本节讲解的是删除没有关联关系的数据表。

1.4数据表的约束

为了防止数据表中插入错误数据，在MySQL中，定义了一些维护数据库完整性的规则，即表的约束。

约束条件	说明
PRIMARY KEY	主键约束，用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值
CHECK	检查约束

1.4.1 主键约束

单字段主键:

单字段主键指的是由一个字段构成的主键

字段名 数据类型  PRIMARY KEY;

多字段主键

多字段主键指的是由多个字段组合而成的主键

PRIMARY KEY(字段名1，.…);

1.4.2非空约束

字段名 数据类型 NOT NULL;

1.4.3 唯一约束

字段名 数据类型 UNIQUE;

1.4.4 默认约束

字段名 数据类型  DEFAULT 默认值;

1.4.5 检查约束

字段名 数据类型  check (条件);

1.5 设置表的字段值自动增加

设置表的字段值自动增加

​ 在数据表中，若想为表中插入的新记录自动生成唯一的ID，可以使用AUTO_INCREMENT约束来实现。AUTO_INCREMENT约束的字段可以是任何整数类型，默认情况下，该字段的值是从1开始自增的。

字段名 数据类型 AUTO_INCREMENT;

二.数据表的基本操作

2.1插入数据

语法1:

INSERT INTO 表名(字段名1,字段名2,......)  VALUES (值1,值2,......);

语法2:

INSERT INTO 表名  VALUES (值1,值2,......);

语法3:

INSERT INTO 表名 SET 字段名1=值1 [,字段名2=值2,......];

语法4:

INSERT INTO 表名[(字段1,字段2,.....)]  VALUES(值1,值2,....),(值1,值2,....),....,(值1,值2,....);

2.2修改数据/更新数据

UPDATE 表名 SET 字段名1=值1 [,字段名2=值2,......] [WHERE 条件表达式];

2.3删除表中的数据

 DELETE FROM 表名 [WHERE 条件表达式]

删除数据表所有内容

DELETE FROM 表名;
# 注意:此种删除方式,不会使自动增长的内容回到初始值

清空数据表的所有数据

TRUNCATE TABLE 表名;
# 彻底清除,使下一次自动增长的内容从0开始

2.4查询数据表

查看数据表整个内容

select * from 表名;

三.单表查询

SELECT  [DISTINCT] *|{字段名1, 字段名2, 字段名3,……}
 FROM 表名
 [WHERE 条件表达式1]
 [GROUP BY 字段名 [HAVING 条件表达式2]]
 [ORDER BY 字段名 [ASC|DESC]]
 [LIMIT [OFFSET] 记录数]

3.1简单查询

3.1.1查询所有字段

指定所有字段

SELECT 字段名1, 字段名2,…… FROM  表名;

使用星号(“*”)通配符代替所有字段

SELECT  * FROM  表名;

3.1.2查询指定字段

SELECT 字段名1,字段名2,…… FROM  表名;

3.1.3给指定字段取别名

select 字段名1 as 别名1[,字段名2 as 别名2,.......] from 表名;

3.2条件查询

3.2.1基本语法

 SELECT 字段名1,字段名2,……  FROM 表名  WHERE 条件表达式;

3.2.2where子句常用的条件

查询条件	谓词
比较	=、>、<、>=、<=、!=、!>、！<、not+比较运算符
确定范围	between and、not between and
确定集合	in、not in
字符匹配	like、not like
空值	is null,is not null
多重条件	and、or

3.2.3 聚合函数实现对某些数据进行统计

max(最大值）、min（最小值）、avg（平均值）、sum （和）、count（数量:记录的条数。）

例如:

# 查询学生表中年龄最大的学生
select max(sage) as 最大的年龄  from stuInfo ;
# 查询学生表中年龄最小的学生
select min(sage) as 最小的年龄  from stuInfo  ;
# 计算平均年龄
select avg(sage) from stuInfo ;

3.3高级查询

3.3.1查询结果排序

使用__order by__关键字

# 升序
select * from 表名 order by 字段名 asc		# (默认)
# 降序
select * from 表名 order by 字段名 desc