SQL语言(1)_sql浮点数精度-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_44564366/article/details/104357470

SQL语言就是数据库自己的语言，提供了对数据库的增、删、改、查功能。

SQL语言使用规范：

在数据库系统中，SQL语句不区分大小写(建议用大写)
但字符串经常区分大小写。
SQL语句可单行或多行书写，以";"结尾
关键词不能跨多行或简写。例如：from create drop等等
用空格或者缩进来提高语句的可读性
字句通常位于独立行，便于编辑，提高可读性

数据库操作

增加数据库：CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name'
COLLATE 'collate name'

[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。

MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL
存储字符串的方式，校对规则定义了比较字符串的方式。

示例：


MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS ydong;
Query OK, 1 row affected (0.00 sec)
#如果ydong数据不存在就创建ydong数据库

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| ydong              |
+--------------------+
5 rows in set (0.01 sec)

查看数据库字符集


MariaDB [(none)]> SHOW CREATE DATABASE ydong;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| ydong    | CREATE DATABASE `ydong` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
如果没有指定默认是latin

创建的时候设置字符集

MariaDB [(none)]> CREATE DATABASE utf8base  CHARACTER SET utf8;

删除字符集

MariaDB [(none)]> DROP DATABASE ydong;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| utf8base           |
+--------------------+
5 rows in set (0.00 sec)

查看支持所有字符集：

MariaDB [(none)]> SHOW  CHARACTER SET;

查看支持所有排序规则：

MariaDB [(none)]> SHOW COLLATION;

选择数据库

MariaDB [(none)]> USE utf8base;
Database changed
MariaDB [utf8base]>

命名规则：

必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字就是语法里面的词语，如select，from等等
同一database(Schema)下的对象不能同名也就是数据库里的表名不能重复

表操作

表操作之前需要介绍一下数据类型，也就是定义数据以何种方式进行存储。在创建表的过程中需要用到

每个列当中都应该有相对合适的存储规则，比如电话号码不超过13位，身份证不超过18位。如果设定的范围或多或少都不太符合要求的话，势必会对磁盘空间和性能方面造成一定的影响

数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

整数类型

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

在这里插入图片描述

上面为整数类型的占用字节数以及它的取值范围。

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

例如当INT(4)的时候，你如果输入4的话，那么它会自动补齐0004，但是如果你超过4个宽度但是没有超过取值范围的话仍然会显示全的。

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。也就是最大值翻倍，没有负数，只有正数。

浮点类型

浮点类型有两种表示方式，单精度float和双精度double；定点类型DECIMAL用于保存必须为确切精度的值，例如货币数据。

对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。

FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围)。MySQL还支持可选的只用于确定存储大小的精度规定。0到23的精度对应FLOAT列的4字节单精度。24到53的精度对应DOUBLE列的8字节双精度。

MySQL允许使用非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

浮点类型的存储

类型名称	解释	存储空间
float	单精度	4个字节
double	双精度	8个字节
DECIMAL	单精度	M+2个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

备注：因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

CHAR和VARCHAR
CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

在这里插入图片描述

text

TEXT列被视为非二进制字符串(字符字符串)。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。TEXT字符是可以有变化长度的。和VARCHAR类似

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM

ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。也就是1选多

SET

SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。
SET最多可以有64个不同的成员。
当创建表时，SET成员值的尾部空格将自动

日期和时间

日期时间类型

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

修饰符

所有类型

NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键，不允许为空，且唯一
UNIQUE KEY 唯一键
CHARACTER SET name 指定一个字符集

数值型

AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号

表创建

表创建有三种方法
1）直接创建， CREATE TABLE

MariaDB [ydong]> CREATE TABLE TEST1 (name VARCHAR(10));

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

MariaDB [ydong]> CREATE TABLE IF NOT EXISTS test2 SELECT * FROM mysql.user;
MariaDB [ydong]> SELECT user,host,password FROM test2;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | ansible   |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | ansible   |          |
+------+-----------+----------+

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

MariaDB [ydong]> CREATE TABLE IF NOT EXISTS test3 LIKE ydong.test2;

创建一张完整的表，信息有学生id，学生姓名，联系方式。

MariaDB [ydong]> CREATE TABLE IF NOT EXISTS students (id INT(50) UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10) NOT NULL , phone INT(13));
MariaDB [ydong]> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(50) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| phone | int(13)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

表操作

删除：DROP TABLE [IF EXISTS] 'tbl_name';

MariaDB [ydong]> DROP TABLE TEST1;

修改字段

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]
可以使用HELP ALTER 查看详细使用

1)
添加字段：ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]

MariaDB [ydong]> ALTER TABLE students ADD address VARCHAR(100) AFTER phone;

2)
修改字段：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

MariaDB [ydong]> ALTER TABLE students CHANGE address teachers VARCHAR(10);
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [ydong]> DESC students;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(50) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10)      | NO   |     | NULL    |                |
| phone    | int(13)          | YES  |     | NULL    |                |
| teachers | varchar(10)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

修改表名

MariaDB [ydong]> ALTER TABLE test2 RENAME test4;

4)
删除字段名字


	
MariaDB [ydong]> ALTER TABLE students DROP teachers;
MariaDB [ydong]> DESC students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(50) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| phone | int(13)          | YES  |     | NULL    |                |

数据操作DML

INSERT

可以选择插入的字段值，不指定就是所有的字段全部都插入值。一次插入一行或多行数据

简单写法:INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

MariaDB [ydong]> INSERT students (id,name,phone) VALUES (1,'xiaohong',123),(2,'xiaoming',321);
MariaDB [ydong]> select * from students;
+----+----------+-------+
| id | name     | phone |
+----+----------+-------+
|  1 | xiaohong |   123 |
|  2 | xiaoming |   321 |
+----+----------+-------+

也可以使用其它表内有的信息将数据插入的表中，但是要注意对应关系

MariaDB [ydong]> INSERT students(name,phone) SELECT user,max_questions from mysql.user;

UPDATE

更新数据，但是在更新的时候一定要有限制，否则将对所有的数据都进行更新。

MariaDB [ydong]> UPDATE students SET name='xiaohuang';
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8  Changed: 8  Warnings: 0

MariaDB [ydong]> select * from students;
+----+-----------+-------+
| id | name      | phone |
+----+-----------+-------+
|  1 | xiaohuang |   123 |
|  2 | xiaohuang |   321 |
|  3 | xiaohuang |     0 |
|  4 | xiaohuang |     0 |
|  5 | xiaohuang |     0 |
|  6 | xiaohuang |     0 |
|  7 | xiaohuang |     0 |
|  8 | xiaohuang |     0 |
+----+-----------+-------+
8 rows in set (0.00 sec)

那么需要添加where限制条件来控制某个字段的更新、


MariaDB [ydong]> UPDATE students SET name='xiaoming',phone=666 WHERE id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [ydong]> SELECT * FROM students;
+----+-----------+-------+
| id | name      | phone |
+----+-----------+-------+
|  1 | xiaohuang |   123 |
|  2 | xiaohuang |   321 |
|  3 | xiaoming  |   666 |
|  4 | xiaohuang |     0 |
|  5 | xiaohuang |     0 |
|  6 | xiaohuang |     0 |
|  7 | xiaohuang |     0 |
|  8 | xiaohuang |     0 |
+----+-----------+-------+
8 rows in set (0.01 sec)

像这种不添加where限制条件进行误操作的时候，破坏力巨大。mysql针对这种问题用一个安全选项，--safe-update。或者在配置文件中写入也可以。

[root@ansible ~]# cat /etc/my.cnf.d/client.cnf 
[client]
safe_updates


MariaDB [(none)]> show variables like '%safe_updates%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+


mariaDB [ydong]> UPDATE students SET name='ydong';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

DELETE

删除表中的数据

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]


ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

删除不是在安全模式下，但是生产环境中绝不可以这样
MariaDB [ydong]> DELETE FROM test4;
Query OK, 6 rows affected (0.02 sec)

MariaDB [ydong]> SELECT * FROM test4;
Empty set (0.00 sec)
数据全部清除

MariaDB [ydong]> DELETE FROM students WHERE id<4;
表示ID前三行数据全部删除