MySQL入门

最新推荐文章于 2022-12-01 19:26:24 发布

努力学习Java的鱼

最新推荐文章于 2022-12-01 19:26:24 发布

阅读量463

点赞数

分类专栏： Java 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_53551331/article/details/126966130

版权

Java 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

结构化查询语言SQL

SQL的概念

什么是SQL

结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。

SQL作用

通过SQL语句我们可以方便的操作数据库中的数据、表、数据库。

SQL是数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

SQL语句分类

DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等
DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update等
DQL(Data Query Language) 数据查询语言 (掌握)
- DQL语言并不是属于MYSQL官方的分类，但是对数据库的操作最多就是查询，所以把查询语句的语句称作为DQL语言
DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE等
TCL(Transaction Control Language) 事务控制语言或TPL事务处理语言
- 用于控制数据库的事务操作，关键字; COMMIT，SAVEPOINT，ROLLBACK等
CCL（Cursor Control Language）指针控制语言（了解）

像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。多用于存储过程中对结果集的处理。

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。SELECT * FROM student;
3种注释
1. 单行注释: -- 注释内容或 # 注释内容(mysql特有)多
2. 行注释: /* 注释 */

延展阅读

SQL标准简介

　　SQL是Structured Query Language的缩写，它的前身是著名的关系数据库原型系统System R所采用的SEQUEL语言。作为一种访问关系型数据库的标准语言，SQL自问世以来得到了广泛的应用，不仅是著名的大型商用数据库产品Oracle、DB2、Sybase、SQL Server支持它，很多开源的数据库产品如PostgreSQL、MySQL也支持它，甚至一些小型的产品如Access也支持SQL。近些年蓬勃发展的NoSQL系统最初是宣称不再需要SQL的，后来也不得不修正为Not Only SQL，来拥抱SQL。

　　蓝色巨人IBM对关系数据库以及SQL语言的形成和规范化产生了重大的影响，第一个版本的SQL标准SQL86就是基于System R的手册而来的。Oracle在1979年率先推出了支持SQL的商用产品。随着数据库技术和应用的发展，为不同RDBMS提供一致的语言成了一种现实需要。

　　对SQL标准影响最大的机构自然是那些著名的数据库产商，而具体的制订者则是一些非营利机构，例如国际标准化组织ISO、美国国家标准委员会ANSI等。各国通常会按照 ISO标准和ANSI标准（这两个机构的很多标准是差不多等同的）制定自己的国家标准。中国是ISO标准委员会的成员国，也经常翻译一些国际标准对应的中文版。标准为了避免采用具体产品的术语，往往会抽象出很多名词，从而增加了阅读和理解的难度，翻译成中文之后更容易词不达意。对于数据库系统实现者和用户而言，很多时候还不如直接读英文版本为好。虽然正式的标准不像RFC那样可以从网络上免费获得，标准草案还是比较容易找到的（例如：ISO/IEC JTC1 SC32 Document Registry）。待批准的标准草案和最终的标准也没有什么实质上的区别，能够满足日常工作的需要。

　　下面是SQL发展的简要历史：1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-861989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-891992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）2003年，ISO/IEC 9075:2003，SQL:20032008年，ISO/IEC 9075:2008，SQL:20082011年，ISO/IEC 9075:2011，SQL:2011

　　细心的读者能发现，从SQL:1999开始，标准简称中的短横线（-）被换成了冒号（:），而且标准制定的年份也改用四位数字了。前一个修改的原因是ISO标准习惯上采用冒号，ANSI标准则一直采用短横线。后一个修改的原因是标准的命名也遇到了2000年问题。

　　SQL86大概只有几十页，SQL92正文大约有500页，而SQL99则超过了1000页。可以看出，从SQL99开始，SQL标准的个头就非常庞大了，内容包罗万象，已经没有人能够掌握标准的所有内容了。以SQL:2003为例，它包括以下9个部分（中间编号空缺是曾经被占用，之后被废弃的标准造成的）：

ISO/IEC9075-1: Framework (SQL/Framework)ISO/IEC 9075-2: Foundation (SQL/Foundation)ISO/IEC 9075-3: Call Level Interface (SQL/CLI)ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM)ISO/IEC 9075-9: Management of External Data (SQL/MED)ISO/IEC 9075-10: Object Language Bindings (SQL/OLB)ISO/IEC 9075-11: Information and Definition Schemas (SQL/Schemata)ISO/IEC 9075-13: Java Routines and Types Using the Java Programming Language(SQL/JRT)ISO/IEC 9075-14: XML-Related Specifications (SQL/XML)

负责具体制定工作的是ISO和IEC联合成立的一个技术委员会JTC1/SC32。正因为关系数据库市场非常成熟，竞争不够激烈，标准本身变得过于庞大等原因，SQL3制定的周期很长，制定期间也存在很多争议。例如文章《Is SQL a Real Standard Anymore?》对SQL标准化进程提出了置疑。

　　如果要了解标准的内容，比较推荐的方法是泛读SQL92（因为它涉及了SQL最基础和最核心的一些内容），然后增量式的阅读其他标准。标准在每次更新的时候，委员会的成员们都为大家提供比较好的介绍文档。例如针对最新的SQL:2011，SIGMODRecord上就有很不错的介绍：http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf

　　最后再简要介绍一下SQL标准的符合程度。绝大多数人提起SQL标准，涉及的内容其实是SQL92里头最基本或者说最核心的一部分。SQL92本身是分级的，包括入门级、过度级、中间级和完全级。为了验证具体的产品对标准的遵从程度，NIST还曾经专门发起了一个项目，来做标准符合程度的测试集合：SQL Test Suite。不过，SQL标准包含的内容实在太多了，而且有很多特性对新的SQL产品而言也越来越不重要了。从SQL99之后，标准中符合程度的定义就不再分级，而是改成了核心兼容性和特性兼容性；也没有机构来推出权威的SQL标准符合程度的测试认证了。

用户管理与权限管理

用户是我们使用数据库的基础，任何连入数据库的操作，都依托于一个用户，并受到这个用户所具有权限的限制。

用户管理

查看用户

select host,user,authentication_string from mysql.user;

mysql是MySQL数据库中的系统库，所有用户、权限、密码、时区等信息都存放在这个库里。

库名.表名的方式可以让我们跨库来引用某个特定库的表。

创建用户

语法：create user "username"@"host" identified by "password";

create user 'test'@'localhost' identified by '123';
create user 'test'@'192.168.7.22' identified by '123';
create user 'test'@'%' identified by '123';

host的含义是限制访问的客户端的主机IP，%表示无限制。

语句中的单引号表示字符串，在这里也可以使用双引号，用户名可以不用引号。

密码设置串可以不带，但是带了密码，就必须带引号。

设置用户密码

方法1：用alter修改用户密码 首先需要以root身份登录MySQL。

ALTER USER 'bbb'@'localhost' IDENTIFIED BY '456';
flush privileges;

方法2：用mysqladmin 格式：mysqladmin -u用户名 -p旧密码 password 新密码

此语句需用在命令行下

mysqladmin -uroot -p123456 password 123

方法3：忘记root密码，需要重置root密码

1、首先停止mysql服务

net stop mysql

2、以管理员身份启动命令行，执行

mysqld --skip-grant-tables --shared-memory

这时以忽略密码模式启动了mysql，不要关闭此cmd窗口。

3、再开一屏cmd窗口

用mysql直接登录，不输入用户和密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_psd_123';
FLUSH PRIVILEGES;

完成后此屏可以退出。前一屏，先ctrl+C强制退出mysqld，再用net start mysql最后尝试一下root的新密码登录

mysql -uroot -pnew_psd_123

删除用户

语法：drop user 用户名@主机名；

drop user zhangsan@'%';

drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

权限管理

给用户授权

初始创建的用户，还不能访问数据库，因为还没有给用户赋予相应权限，需要执行用户授权操作。

命令格式：grant 权限码 on 数据库名.表名 to 用户名@主机名 identified by "password";

grant all on dbname.* to zhrt@localhost identified by '123456';
FLUSH PRIVILEGES;

设置密码的子串可写可不写

权限码表示授予的权限类型，常用的有以下几种类型：

all ：所有权限。
select：读取权限。
delete：删除权限。
update：更新权限。
create：创建权限。
drop：删除数据库、数据表权限。

查看用户权限

语法：show grants for 用户名@主机名;

show grants for 'bbb'@'localhost';

取消用户授权

语法：REVOKE 权限码 ON 库名.表名 FROM 用户名@主机名;

REVOKE all ON power.* FROM bbb@localhost;

数据定义语言（DDL）

操作数据库

创建数据库

1.直接创建数据库

CREATE DATABASE 数据库名;

2.判断是否存在并创建数据库(了解)

CREATE DATABASE IF NOT EXISTS 数据库名;

3.创建数据库并指定字符集(了解)

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

4.具体操作：

直接创建数据库db1

CREATE DATABASE db1;

判断是否存在并创建数据库db2

CREATE DATABASE IF NOT EXISTS db2;

创建数据库并指定字符集为gbk

CREATE DATABASE db2 CHARACTER SET gbk;

查看数据库

1.查看所有的数据库

SHOW databases;

2.查看某个数据库的定义信息

SHOW CREATE DATABASE 数据库名;

修改数据库(了解)

修改数据库字符集格式

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

具体操作：

将db3数据库的字符集改成utf8

ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;

删除数据库

DROP DATABASE 数据库名;

具体操作：

删除db2数据库

DROP DATABASE db2;

使用数据库

1.查看正在使用的数据库

SELECT DATABASE();

2.使用/切换数据库

USE 数据库名;

具体操作：

查看正在使用的数据库

SELECT DATABASE();

使用db1数据库

USE db1;

操作表

如果已执行 use 数据库名; 可以省略数据库名。

创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名 (
   字段名 字段类型 [ primary key | unique | not null | auto_increment |
      | binary | default 缺省值 | comment 注释语句],
   ......
) [ ENGINE=InnoDB | DEFAULT CHARSET=utf8 ];

表可选项：

if not exists：如果不存在就执行建表

engine：设置数据引擎，默认为innodb

default charset：设置默认字符编码

字段可选项：

primary key：主键，值唯一，不可为null

unique：唯一键，值唯一，可以为null

not null：不可为null，不写这一项，则默认为可以接受null值

auto_increment：自动增长，只能与整数类型搭配，默认每次增长1

binary：与字符类型搭配，在字段比较时大小写敏感

default：设置缺省值，若插入值为null时，则使用默认值填充

comment：设置字段注释

示例：

MySQL的数值数据类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

整数类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127) FF 1111 1111	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2,147,483,648，2,147,483,647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807)	(0，18 446 744 073 709 551 615)	极大整数值

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

TINYINT，1字节，8位，16进制最大表示为FF，2进制表示最大为1111 1111，有符号，第一位为符号为（整数为0，负数为1），所以整数最大值为0111 1111（127），负数最大值为1111 1111（-128），无符号的最大值是255。

可以用unsigned控制是否有符号位
可以使用zerofill控制是否有前导零
也存在布尔类型。首先mysql是不支持布尔类型的,当把一个数据设置成布尔类型的时候,数据库会自动转换成tinyint(1)的数据类型,其实这个就是变相的布尔。默认值也就是1,0两种,分别对应了布尔类型的true和false。
类型后面(1)，代表的显示长度，只有跟zerofill配合起来才能用。简单地说，没有(1)，会显示成00x数字，具体连数字带前导零总共几位，有(n)来限制。

我们在这里测试一下。

create table test1 (
c1 TINYINT,
c2 tinyint UNSIGNED,
c3 tinyint ZEROFILL );

然后执行sql，分别看看执行结果，分析报错信息。

insert into test1 values(1, 2, 3);
insert into test1 values(-1, 2, 3);
insert into test1 values(1, -2, 3);
insert into test1 values(1, 2, -3);
insert into test1 values(127, 2, -3);
insert into test1 values(-128, 128, 3);
insert into test1 values(-128, 256, 255);

总结：zerofill具备unsigned的隐藏属性，不能存负值。

SMALLINT，2字节，16位，16进制最大表示为FFFF。

MEDIUMINT，3字节，24位，16进制最大表示为FF FFFF。

INT或INTEGER ，4字节，32位，16进制最大表示为FFFF FFFF。

BIGINT，8字节，64位，16进制最大表示为FFFF FFFF FFFF FFFF。

这些类型都可以用unsigned和zerofill修饰。

浮点数类型

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

科学计数法，0.123 * 10^1。

创建该类型字段时，可以设置（M，D）模式控制数值范围

create table test4 (
  f1 float,
  f2 float(5, 2),
  f3 float(16，4)
);

insert into test4 values (123.456789, 123.456789, 123.456789);
insert into test4 values (1234.56789, 1234.56789, 1234.56789);
insert into test4 values (12345.6789, 12345.6789, 12345.6789);
insert into test4 values (123456.789, 123456.789, 123456.789);
insert into test4 values (1234567.89, 1234567.89, 1234567.89);
insert into test4 values (12345678.9, 12345678.9, 12345678.9);
insert into test4 values (123456789, 123456789, 123456789);
select * from test4;

从上面看出：默认的float类型都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示（id=4），小数位超出则需要四舍五入。

float(m,d)：小数点后位数为d，即整数位数为（m-d），整数位超出则整数为(m-d)个，小数点后位数为(d)个9999，不用科学计算了；若小数位超出，则需要四舍五入。

单精度浮点数的精度是不高的，我们可以试试double。

drop table test5;
create table test5 (
  d1 double,
  d2 double(5, 2),
  d3 double(16, 4)
);

insert into test5 values (123.456789, 123.456789, 123.456789);
insert into test5 values (1234.56789, 1234.56789, 1234.56789);
insert into test5 values (12345.6789, 12345.6789, 12345.6789);
insert into test5 values (123456.789, 123456.789, 123456.789);
insert into test5 values (1234567.89, 1234567.89, 1234567.89);
insert into test5 values (12345678.9, 12345678.9, 12345678.9);
insert into test5 values (123456789, 123456789, 123456789);

双精度浮点型的有效精度更高（16位左右），可以容纳更多的值。

定点小数类型

类型	大小	范围（有符号）	范围（无符号）	用途
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

我们创建一个包含decimal类型字段的表

drop table test6;
create table test6 (
  d1 decimal,
  d2 decimal(6, 2),
  d3 decimal(32, 4)
);
insert into test6 values (123.456789, 123.456789, 123.456789);
insert into test6 values (1234.56789, 1234.56789, 1234.56789);
insert into test6 values (12345.6789, 12345.6789, 12345.6789);
insert into test6 values (123456.789, 123456.789, 123456.789);
insert into test6 values (1234567.89, 1234567.89, 1234567.89);
insert into test6 values (12345678.9, 12345678.9, 12345678.9);

decimal型的默认整数位为10，小数位为0，即默认为整数。

在MySQL中，定点数以字符串形式存储，因此，其精度比浮点数要高，而且浮点数会出现误差，这是浮点数一直存在的缺陷。如果要对数据的精度要求比较高，还是选择定点数decimal比较安全。

MySQL的日期时间数据类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00	结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

我们来创建一个表试试。

drop table dt01;
create table dt01(
  d1 year,
  d2 date,
  d3 time,
  d4 datetime,
  d5 timestamp
);

insert into dt01 values ('2020', '2020-3-4', '3:4:5','2020-3-4 3:4:5', null);
insert into dt01 values ('2020', '20200304', '131415','20200304131415', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5','2020-3-4 3:4:5', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020:3:4', '03:04:05','2020:3:4 3:4:5', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019-12-5 7:8:9', '%Y-%m-%d %H:%i:%s'), CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019*12*5 7&8&9', '%Y*%m*%d %H&%i&%s'), CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019年12月12日 7时8分9秒', '%Y年%m月%d日 %H时%i分%s秒'), CURRENT_TIMESTAMP);

日期时间类型转换成格式字符串，可以使用这种方法。

select date_format(now(), ‘%Y-%m-%d %H:%i:%s’);

格式字符串转换成日期时间类型，可以使用这个函数

STR_TO_DATE('2019-12-5 7:8:9', '%Y-%m-%d %H:%i:%s')

format可以使用的值为：

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59) %s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

MySQL的字符串类型

类型	大小	用途
CHAR(n)	0-255字符（*字符集字节数）	定长字符串
VARCHAR(2000)	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换，CHAR删除尾部的空格，VARCHAR则保留尾部的空格。

因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度

如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节。

最大长度根据字符集换算，GBK每个字符2个字节，UTF8每个字符3个字节，utf8mb4每个字符4字节，那么

在GBK字符集下，varchar最大长度32766，UTF8最大长度21844，UTF8MB4最大长度16384。

我们来验证一下

create table test7 (
  test_char varchar(21845)
) character set utf8;
create table test7 (
  test_char varchar(32766)
) character set gbk;
create table test7 (
  test_char varchar(16383)
) character set utf8mb4;

还有一个有趣的现象，mysql表的每行记录字节总和不能超过65535。

create table test7 (
  c1 char(100),
  c2 varchar(21845)
) character set utf8;

create table test7 (
  c1 char(100),
  c2 varchar(21744)
) character set utf8;

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

1.BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。 2.BLOB存储的数据只能整体读出。 3.TEXT可以指定字符集，BLOB不用指定字符集。

查看表

查看某个数据库中的所有表 SHOW TABLES;
查看表结构 DESC 表名;
查看创建表的SQL语句 SHOW CREATE TABLE 表名;

具体操作：

查看mysql数据库中的所有表

SHOW TABLES;

查看student表的结构

DESC student;

查看student的创建表SQL语句
```
SHOW CREATE TABLE student;
```
快速创建一个表结构相同的表

CREATE TABLE 新表名 LIKE 旧表名;

具体操作：
创建s1表，s1表结构和student表结构相同
```
CREATE TABLE s1 LIKE student;
```

删除表

直接删除表 DROP TABLE 表名;
判断表是否存在并删除表(了解) DROP TABLE IF EXISTS 表名;

具体操作：

直接删除表s1表
```
DROP TABLE s1;
```

判断表是否存在并删除s1表
```
DROP TABLE IF EXISTS s1;
```
修改表结构

修改表结构使用不是很频繁，只需要知道下，等需要使用的时候再回来查即可

1.添加表列

ALTER TABLE 表名 ADD 列名类型;

具体操作：

为学生表添加一个新的字段remark,类型为varchar(20)
```
ALTER TABLE student ADD remark VARCHAR(20);
```

2.修改列类型

ALTER TABLE 表名 MODIFY 列名新的类型;

具体操作：

将student表中的remark字段的改成varchar(100)

ALTER TABLE student MODIFY remark VARCHAR(100);

3.修改列名

ALTER TABLE 表名 CHANGE 旧列名新列名类型;

具体操作：

将student表中的remark字段名改成intro，类型varchar(30)

ALTER TABLE student CHANGE remark intro varchar(30);

4.删除列

ALTER TABLE 表名 DROP 列名;

具体操作：

删除student表中的字段intro

ALTER TABLE student DROP intro;

5.修改表名

RENAME TABLE 表名 TO 新表名;

具体操作：

将学生表student改名成student2

RENAME TABLE student TO student2;

6.修改字符集

ALTER TABLE 表名 character set 字符集;

具体操作：

将sutden2表的编码修改成gbk

ALTER TABLE student2 character set gbk;

单表查询语句（Select）

查询不会对数据库中的数据进行修改.只是一种显示数据的方式准备数据

CREATE TABLE student3 (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

简单查询

查询表所有数据

1.使用*表示所有列

SELECT * FROM 表名;

具体操作：

SELECT * FROM student3;

2.写出查询每列的名称

SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;

具体操作：

SELECT id, NAME ,age, sex, address, math, english FROM student3;

查询指定列

查询指定列的数据,多个列之间以逗号分隔

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

具体操作：

查询student3表中的id , name , age , sex , address 列

SELECT id, NAME ,age, sex, address FROM student3;

别名查询

1、“*”，表示按照create table的顺序排列的所有列。

2、表名.*，表示取回一个指定表中的所有列，适用于多表关联时，存在同名字段。列表中使用非限定列名，可能会产生解析错误。

2、按照用户所需顺序排列的列名的清单。

3、可以使用别名取代列名，形式如下：

column name as column_heading

mysql还支持不带as，直接空格跟别名的方式来指定别名。

4、表达式（列名、常量、函数，或以算术或逐位运算符连接的列名、常量和函数的任何组合）。

5、内部函数或集合函数。

6、上述各项的任何一种组合。

SQL的运算符

MySql中，数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过 MySQL 运算符进行运算，就可以获取到表结构以外的另一种数据。

例如，学生表中存在一个 birth 字段，这个字段表示学生的出生年份。而运用 MySQL 的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

算术运算符

算术运算符是 SQL 中最基本的运算符，MySQL 中的算术运算符如下表所示。

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/	除法运算，返回商
%	求余运算，返回余数

注意：在除法运算和模运算中，如果除数为0，将是非法除法，返回结果为NULL。

加法减法没什么可以说的，我们先来说说乘法。

整数的乘法结果是整数。小数的乘法呢？

mysql> select 2*3 as t;
+---+
| t |
+---+
| 6 |
+---+
1 row in set (0.00 sec)

mysql> select 2.0*3.0 as t;
+------+
| t    |
+------+
| 6.00 |
+------+
1 row in set (0.00 sec)

可以看到小数乘以小数，结果仍然是小数，我们需要关注精度，保留了乘数和被乘数的小数之和，系统并不会将小数点后的零自动去除。如果需要去除，则采用强制转换函数。

mysql> select 1.234 * 5.678 as t;
+----------+
| t        |
+----------+
| 7.006652 |
+----------+
1 row in set (0.00 sec)
mysql> select convert(1.234 * 5.678, decimal(10,2)) as t;
+------+
| t    |
+------+
| 7.01 |
+------+
1 row in set (0.00 sec)

MySQL 的CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。具体的语法如下：

CONVERT(value, type);

直接在sql中操作乘法的精度没问题，我们来看看字段操作怎么样。

create table ta (
aaa double,
bbb double,
ccc float,
ddd float,
eee decimal(10,2),
fff decimal(10,2)
);
insert into ta values (1.23, 2.34, 1.23, 2.34, 1.23, 2.34);
mysql> select aaa, bbb, aaa*bbb, ccc, ddd, ccc*ddd, eee, fff ,eee*fff from ta;
+------+------+--------------------+------+------+--------------------+------+------+---------+
| aaa  | bbb  | aaa*bbb            | ccc  | ddd  | ccc*ddd            | eee  | fff  | eee*fff |
+------+------+--------------------+------+------+--------------------+------+------+---------+
| 1.23 | 2.34 | 2.8781999999999996 | 1.23 | 2.34 | 2.8781999390602095 | 1.23 | 2.34 |  2.8782 |
+------+------+--------------------+------+------+--------------------+------+------+---------+
1 row in set (0.00 sec)

可以发现除了decimal，float和double的乘法操作都存在精度问题，需要强转。

可以发现：

sql里面直接数相乘，与字段中decimal相乘一致，结果小数位数保留被乘数乘数之和。

double类型数相乘，结果的精度高于float数相乘。

提示：能用decimal，就不要用float和double。

除法操作，我们也来看看精度问题。跟乘法类似，

进一步尝试

select 1.22345 / 2.3456;
insert into ta values (1.2345, 2.3456, 1.2345, 2.3456, 1.2345, 2.3456);
select aaa, bbb, aaa/bbb, ccc, ddd, ccc/ddd, eee, fff ,eee/fff from ta where aaa=1.2345;

可以发现：

sql里面直接数相除，与字段中decimal相除一致，结果小数位数保留被除数除数之和。

double类型数相除，结果的精度高于float数相除。

比较运算符

select语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录时符合条件的，比较结果为真，则返回1，为假则返回0，比较结果不确定则返回NULL。

select 1<2;

等于（==）与严格等于运算符（）

严格等于和等于运算符（=）的作用一致，只不过多了一个功能，就是可以判断NULL值，如下：

select 1=0,'2'=2,(1+3)=(2+2),NULL=NULL;
select 1<=>0,'2'<=>2,(1+3)<=>(2+2),NULL<=>NULL;

不等于运算符（<>或!=）

不等于运算符用于判断数字、字符串、表达式是否不相等，如果不相等则返回 1，否则返回 0 ，但是不能判断 NULL 值。

IS NULL 、IS NOT NULL

IS NULL是检验一个值是否为 NULL ，如果为 NULL ，返回值为 1，否则返回值为 0；IS NOT NULL 检验一个值是否不为 NULL ，如果不为 NULL ，返回值为 1，否则返回值为 0。

BETWEEN AND

用于判断一个值是否落在两个值之间。

select 4 between 1 and 5,4 between 4 and 6,12 between 9 and 10;

between ... and 操作符是包含两边端点的。

IN、NOT IN

IN ：判断一个值是否是 IN 列表中的任意一个值；NOT IN ：判断一个值是否不是 IN 列表中的任意一个值。

select 2 in(3,5,8,2), 2 not in(1,3,5);

LIKE

LIKE 运算符用来匹配字符串（正则表达式），如果匹配则返回 1，如果不匹配则返回 0；LIKE 使用两种通配符：'%' 用于匹配任何数目的字符，包括零字符； '_' 只能匹配一个字符。

mysql> select 'stud' like 'stu_','stud' like 's___','stud' like '%d';

REGEXP

1）REGEXP运算符用来匹配字符串，如果匹配则返回1，如果不匹配则返回0；

2）REGEXP 使用以下几种通配符：

'^'：用于匹配以什么开头的字符串；

'$'：用以匹配以什么结尾的字符串；

'.'：用于匹配任何一个单字符串；

'[...]'：用于匹配在方括号内的任何字符；

'*'用于匹配零个或多个在它前面的字符；

select 'ssky' regexp '^s', 'ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]' ;

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

逻辑非（NOT 或！）

当操作数为 0 时，所得值为 1；
当操作数为非 0 时，所得值为 0；
当操作数为 NULL 时，所得值为 NULL。

mysql> select not  10,!10,not(1-1),!(1-1),not 1+1,not null;

逻辑与（AND或&&）

当所有操作数均为非零值、并且不为 NULL 时，所得值为 1；
当一个或多个操作数为 0 时，所得值为 0 ；
其余情况所得值为 NULL。

mysql> select 1 and -1,1 && 0,0 and null,1 && null;

逻辑或（OR 或 || ）

当两个操作数均为非 NULL 值，且任意一个操作数为非零值时，结果为 1 ，否则为 0；
当有一个操作数为 NULL ，且另一个操作数为非零值时，则结果为 1 ，否则结果为 NULL；
当两个操作数均为 NULL 时，则所得结果为 NULL。

mysql> select 1 or -1 or 0,1 || 2,0 or null,null|| null;

逻辑异或（XOR）

a XOR b 的计算等同于 ( a AND (NOT b) ) 或 ( (NOT a) AND b )；
当任意一个操作数为 NULL 时，返回值为 NULL；
对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回结果为 0；
如果一个为 0 值，另一个为非 0 值，返回结果为 1。

mysql> select 1 xor 1,0 xor 0,1 xor 0,1 xor null,1 xor 1 xor 1;

运算符的优先级

最低优先级为： :=。

最高优先级为： !、BINARY、 COLLATE。

用（），千万记得用括号。

where子句

where子句设置了搜索条件。

它在insert，update，delete语句中的应用方法也与在select语句中的应用方法完全相同。搜索条件紧跟在关键词where的后面。如果用户要在语句中使用多个搜索条件，则可用and或or连接。

搜索条件的基本语法是

select * from test1 where aaa = '呵呵';
select* from test1 where not aaa = '呵呵';
select* from test1 where aaa != '呵呵';

distinct关键字的用法

在mysql中，distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤，只返回其中的一条数据给用户，distinct只可以在select中使用。

distinct的原理：

distinct进行去重的主要原理是通过先对要进行去重的数据进行分组操作，然后从分组后的每组数据中去一条返回给客户端，在这个分组的过程可能会出现两种不同的情况：

distinct 依赖的字段全部包含索引：

该情况mysql直接通过操作索引对满足条件的数据进行分组，然后从分组后的每组数据中去一条数据。

distinct 依赖的字段未全部包含索引：

该情况由于索引不能满足整个去重分组的过程，所以需要用到临时表，mysql首先需要将满足条件的数据放到临时表中，然后在临时表中对该部分数据进行分组，然后从临时表中每个分组的数据中去一条数据，在临时表中进行分组的过程中不会对数据进行排序。

GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

select bbb from test1 group by bbb;
--这其实跟distinct返回的结果一致
select distinct bbb from test1;

聚合函数

聚合函数aggregation function又称为组函数。默认情况下聚合函数会对当前所在表当做一个组进行统计，MySQL提供了许多聚合函数，包括AVG，COUNT，SUM，MIN，MAX等。除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值。

聚合函数的特点

1.每个组函数接收一个参数（字段名或者表达式），统计结果中默认忽略字段为NULL的记录 2.要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。　

3.不允许出现嵌套比如sum(max(xx))

AVG函数

AVG()函数计算一组值的平均值。它计算过程中是忽略NULL值的。

select avg(bbb) from test1;
select aaa, avg(bbb) from test1 group by aaa;

如果select的字段列表除了聚合函数以外，没有其他字段，可以不用group by分组子句。否则必须搭配group by使用。

MAX()函数

MAX()函数返回一组值中的最大值，其语法如下所示 -

select aaa, max(bbb) from test1 group by aaa;

MIN()函数

MIN()函数返回一组值中的最小值，其语法如下所示 -

select aaa, min(bbb) from test1 group by aaa;

现在我们设想一个应用场景，计算公司部门的员工最高工资和最低工资，先创建表。

drop table emp;
create table emp(
    emp_id int primary key auto_increment,
    emp_name varchar(20) comment '员工姓名',
    emp_dept varchar(20) comment '部门名称',
    salary decimal(10, 2) comment '工资',
    hiredate datetime comment '入职时间'
);

insert into emp(emp_name, emp_dept, salary, hiredate) values('赵大', '开发部', 4500, '2016-3-1');
insert into emp(emp_name, emp_dept, salary, hiredate) values('陈二', '开发部', 5000, '2015-5-6');
insert into emp(emp_name, emp_dept, salary, hiredate) values('张三', '开发部', 7000, '2012-7-4');
insert into emp(emp_name, emp_dept, salary, hiredate) values('李四', '测试部', 5500, '2015-3-5');
insert into emp(emp_name, emp_dept, salary, hiredate) values('王五', '测试部', 3500, '20180407');
insert into emp(emp_name, emp_dept, salary, hiredate) values('钱六', '销售部', 6000, '20170909');
insert into emp(emp_name, emp_dept, salary, hiredate) values('周七', '财务部', 5200, '20170709');
select * from emp;

我们要如何统计各个部门的最高工资和最低工资呢？

select emp_dept, max(salary) from emp group by emp_dept;
select emp_dept, min(salary) from emp group by emp_dept;

解释一下这个结果：

1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY DEPT中包含的列DEPT。

2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。

3、分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。

select emp_dept, max(salary) from emp where hiredate between '20150101' and '2016-12-31' group by emp_dept;

COUNT()函数

COUNT()函数返回结果集中的行数。

select count(*) from emp;
select count(1) from emp;
select count(emp_id) from emp;
select emp_dept, count(emp_id) from emp group by emp_dept;

注意：count() 在统计时，会计入null值。

SUM()函数

SUM()函数返回一组值的总和，SUM()函数忽略NULL值。如果找不到匹配行，则SUM()函数返回NULL值。

select emp_dept, sum(salary) from emp group by emp_dept;
select emp_dept, sum(salary) from emp  where emp_dept='aaa' group by emp_dept;

Order by 子句

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。

我们来看几个例子

select * from emp order by salary;
--默认情况按升序排列
select * from emp order by salary desc;
--指定desc后按降序排列
select emp_dept, sum(salary) from emp group by emp_dept order by emp_dept;
--字符串也可以排序，排序依据为字符编码的二进制值
select emp_dept, sum(salary) from emp group by emp_dept order by emp_dept desc;

SELECT后被选择的列，可以在ORDER by和GROUP BY中，通过列名、列别名或者代表列位置的整数（从1开始）来引用。

select emp_dept, sum(salary) from emp group by emp_dept order by 1 desc;

用 union/union all来连接结果集

如果想选择其他几个表中的行或从一个单一的表作为一个单独的结果集行的几个集会，那么可以使用的UNION。

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。如果允许重复的值，请使用 UNION ALL。我们来创建一个场景，学生表和教师表

create table teacher(
    id int primary key auto_increment,
    teacher_name varchar(20),
    teacher_city varchar(20)
);
create table student(
    id int primary key auto_increment,
    student_name varchar(20),
    student_city varchar(20)
);
insert into teacher(teacher_name, teacher_city) values('赵大', '武汉');
insert into teacher(teacher_name, teacher_city) values('陈二', '鄂州');
insert into teacher(teacher_name, teacher_city) values('张三', '襄阳');

insert into student(student_name, student_city) values('李四', '宜昌');
insert into student(student_name, student_city) values('王五', '恩施');
insert into student(student_name, student_city) values('钱六', '黄石');
insert into student(student_name, student_city) values('周七', '孝感');

我们用两个查询分别取得两个表的结果集，然后连接。

select * from teacher
union
select * from student;

如果要获取老师和学生来自哪些城市，则

select teacher_city from teacher
union
select student_city from student;

注意：

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。union只关注数据类型，数据业务含义是否相同不管。

我们再插入3条记录

insert into student(student_name, student_city) values('周七1', '武汉');
insert into student(student_name, student_city) values('周七2', '武汉');
insert into student(student_name, student_city) values('周七3', '武汉');

比较一下3句sql

select * from teacher
union
select * from student;
select teacher_city from teacher
union
select student_city from student;
select teacher_city from teacher
union all
select student_city from student;

union会将结果集去重，它比较结果集中的全部字段，所有字段都相同的将被去除。union all 不去重。

union的用法及注意事项

union:联合的意思，即把两次或多次查询结果合并起来。

要求：两次查询的列数必须一致

推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

select emp_dept, sum(salary) from emp group by emp_dept
union
select emp_dept, sum(salary) from emp group by emp_dept
order by emp_dept desc;
--或者这样
(select emp_dept, sum(salary) from emp group by emp_dept)
union all
(select emp_dept, sum(salary) from emp group by emp_dept)
order by emp_dept desc;