首页 Mysql教程SQL语句

Mysql安装

Mysql备份恢复

运维派隶属马哥教育旗下专业运维社区,是国内成立最早的IT运维技术社区,欢迎关注公众号:yunweipai
领取学习更多免费Linux云计算、Python、Docker、K8s教程关注公众号:马哥linux运维

3 SQL语言

3.1 关系型数据库的常见组件

  • 数据库:database
  • 表:table,行:row 列:column
  • 索引:index
  • 视图:view
  • 用户:user
  • 权限:privilege
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event scheduler,任务计划

3.2 SQL语言的兴起与语法标准

20世纪70年代,IBM开发出SQL,用于DB2 1981年,IBM推出SQL/DS数据库 业内标准微软和Sybase的T-SQL,Oracle的PL/SQL SQL作为关系型数据库所使用的标准语言,最初是基于IBM的实现在1986年被批准的。1987年,“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准 SQL:ANSI SQL,SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003,SQL-2008, SQL-2011

3.2.1 SQL语言规范

在数据库系统中,SQL语句不区分大小写(建议用大写) SQL语句可单行或多行书写,以“;”结尾 关键词不能跨多行或简写 用空格和缩进来提高语句的可读性 子句通常位于独立行,便于编辑,提高可读性

注释:

  • SQL标准:

-- 注释内容 单行注释,注意有空格

/*注释内容*/ 多行注释

  • MySQL注释: #
3.2.2 数据库对象和命名

数据库的组件(对象): 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则:

  • 必须以字母开头,可包括数字和三个特殊字符(# _ $)
  • 不要使用MySQL的保留字
  • 同一database(Schema)下的对象不能同名
3.2.3 SQL语句分类
  • DDL: Data Defination Language 数据定义语言 CREATE,DROP,ALTER
  • DML: Data Manipulation Language 数据操纵语言 INSERT,DELETE,UPDATE
  • DQL:Data Query Language 数据查询语言 SELECT
  • DCL:Data Control Language 数据控制语言 GRANT,REVOKE,COMMIT,ROLLBACK
3.2.4 SQL语句构成

关健字Keyword组成子句clause,多条clause组成语句 示例:

说明:一组SQL语句,由三个子句构成,SELECT,FROM和WHERE是关键字 数据库操作

获取SQL 命令使用帮助

3.2.5 字符集和排序

查看支持所有字符集:

早期MySQL版本默认为latin1,从MySQL8.0开始默认字符集已经为utf8mb4

查看支持所有排序规则:

设置服务器默认的字符集,

设置mysql客户端默认的字符集

查看当前字符集的使用情况

3.3 管理数据库

3.3.1 创建数据库

范例:

3.3.2 修改数据库

范例:

3.3.3 删除数据库

3.3.4 查看数据库列表

范例:

3.4 数据类型

数据类型

  • 数据长什么样
  • 数据需要多少空间来存放

数据类型

  • 系统内置数据类型
  • 用户定义数据类型

MySQL支持多种内置数据类型

  • 数值类型
  • 日期/时间类型
  • 字符串(字符)类型

数据类型参考链接

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

SQL语句插图

选择正确的数据类型对于获得高性能至关重要,三大原则:

  1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型

  2. 简单就好,简单数据类型的操作通常需要更少的CPU周期

  3. 尽量避免NULL,包含为NULL的列,对MySQL更难优化

3.4.1 整数型

tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) bigint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型,如果加修饰符unsigned后,则最大值翻倍

如:tinyint unsigned的取值范围为(0~255) int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和Int(20)是相同的

BOOL,BOOLEAN:布尔型,是TINYINT(1)的同义词。zero值被视为假,非zero值视为真

3.4.2 浮点型(float和double),近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位 double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位 设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位

3.4.3 定点数

在数据库中存放的是精确值,存为十进制 decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位 MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。

例如:

decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:其中,小数点前的9个数字用4个字节,小数点后的9个数字用4个字节,小数点本身占1个字节 浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节 因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal,例如存储财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal

3.4.4 字符串(char,varchar,text)

char(n) 固定长度,最多255个字符 varchar(n) 可变长度,最多65535个字符 tinytext 可变长度,最多255个字符 text 可变长度,最多65535个字符 mediumtext 可变长度,最多2的24次方-1个字符 longtext 可变长度,最多2的32次方-1个字符 BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节 VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节 内建类型:ENUM枚举, SET集合

char和varchar:

参考:https://dev.mysql.com/doc/refman/8.0/en/char.html

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉,所以char类型存储的字符串末尾不能有空格,varchar不限于此 2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n< n>255),所以varchar(4),存入3个字符将占用4个字节 3.char类型的字符串检索速度要比varchar类型的快

varchar和text: 1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n< n>255),text是实际字符数+2个字节。 2.text类型不能有默认值 3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text 数据类型

3.4.5 二进制数据BLOB

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

3.4.6 日期时间类型

date 日期 '2008-12-2' time 时间 '12:25:36' datetime 日期时间 '2008-12-2 22:06:44' timestamp 自动存储记录修改时间 YEAR(2), YEAR(4):年份 timestamp字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间

3.4.7 修饰符

适用所有类型的修饰符: NULL 数据列可包含NULL值 NOT NULL 数据列不允许包含NULL值 DEFAULT 默认值 PRIMARY KEY 主键 UNIQUE KEY 唯一键 CHARACTER SET name 指定一个字符集

适用数值型的修饰符: AUTO_INCREMENT 自动递增,适用于整数类型 UNSIGNED 无符号

范例:关于AUTO_INCREMENT

范例:

3.5 DDL语句

表:二维关系 设计表:遵循规范

定义:字段,索引

  • 字段:字段名,字段数据类型,修饰符
  • 约束,索引:应该创建在经常用作查询条件的字段上
3.5.1 创建表

创建表:

获取帮助:

创建表的方法

(1) 直接创建

注意:

  • Storage Engine是指表类型,也即在表创建时指明其使用的存储引擎
  • 同一库中不同表可以使用不同的存储引擎
  • 同一个库中表建议要使用同一种存储引擎类型

范例:创建表

范例:auto_increment 属性

(2) 通过查询现存表创建;新表会被直接插入查询而来的数据

范例:

(3) 通过复制现存的表的表结构创建,但不复制数据

范例:

3.5.2 表查看

查看支持的engine类型

查看表:

范例:

查看表结构:

范例:

查看表创建命令:

范例:

查看表状态:

范例:

查看库中所有表状态

范例:

3.5.3 修改和删除表

删除表

修改表

查看修改表帮助

修改表范例

3.6 DML语句

DML: INSERT, DELETE, UPDATE

3.6.1 INSERT 语句

功能:一次插入一行或多行数据 语法

简化写法:

3.6.2 UPDATE 语句

语法:

注意:一定要有限制条件,否则将修改所有行的指定字段

可利用mysql 选项避免此错误:

4.6.3 DELETE语句

删除表中数据,但不会自动缩减数据文件的大小。

语法:

注意:一定要有限制条件,否则将清空表中的所有数据

如果想清空表,保留表结构,也可以使用下面语句,此语句会自动缩减数据文件的大小。

缩减表大小

3.7 DQL语句

3.7.1 单表操作

语法:

说明:

  • 字段显示可以使用别名: col1 AS alias1, col2 AS alias2, ...
  • WHERE子句:指明过滤条件以实现“选择”的功能: 过滤条件:布尔型表达式 算术操作符:+, -, *, /, % 比较操作符:=,<=>(相等或都为空), <>, !=(非标准SQL), >, >=, <, <= BETWEEN min_num AND max_num IN (element1, element2, ...) IS NULL IS NOT NULL DISTINCT 去除重复列,范例:SELECT DISTINCT gender FROM students; LIKE: % 任意长度的任意字符 _ 任意单个字符 RLIKE:正则表达式,索引失效,不建议使用 REGEXP:匹配字符串可用正则表达式书写模式,同上 逻辑操作符:NOT,AND,OR,XOR
  • GROUP:根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 常见聚合函数:avg(), max(), min(), count(), sum() HAVING: 对分组聚合运算后的结果指定过滤条件
  • 一旦分组group by ,select语句后只跟分组的字段,聚合函数
  • ORDER BY: 根据指定的字段对查询结果进行排序 升序:ASC 降序:DESC
  • LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制
  • 对查询结果中的数据请求施加“锁” FOR UPDATE: 写锁,独占或排它锁,只有一个读和写操作 LOCK IN SHARE MODE: 读锁,共享锁,同时多个读操作

范例

3.7.2 多表查询

多表查询,即查询结果来自于多张表

SQL语句插图(1)

  • 子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询
  • 联合查询:UNION
  • 交叉连接:笛卡尔乘积
  • 内连接: 等值连接:让表之间的字段以“等值”建立连接关系 不等值连接 自然连接:去掉重复列的等值连接
  • 外连接: 左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col 右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
  • 自连接:本表和本表进行连接查询

子查询

常用在WHERE子句中的子查询

  1. 用于比较表达式中的子查询;子查询仅能返回单个值

  2. 用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表

  3. 用于EXISTS

  4. 用于FROM子句中的子查询 使用格式:

    范例:

范例:子查询

联合查询:UNION

范例:联合查询

范例:交叉连接