mysql 数据库操作--create,alter,drop,show, 数据类型，char,vachar,init,float,date等

关系型数据库的常见组件

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

SQL语言规范

1.在数据库系统中，SQL语句不区分大小写(建议用大写)
2.SQL语句可单行或多行书写，以“;”结尾
3.关键词不能跨多行或简写
4.用空格和缩进来提高语句的可读性
5.子句通常位于独立行，便于编辑，提高可读性
注释：
SQL标准：
/*注释内容*/ 多行注释
-- 注释内容 单行注释，注意有空格
MySQL注释：#

数据库对象

数据库的组件(对象)：
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
1.必须以字母开头
2.可包括数字和三个特殊字符（# _ $）
3.不要使用MySQL的保留字
4.同一database(Schema)下的对象不能同名

SQL语句分类

SQL语句分类：
DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操作语言

INSERT，DELETE，UPDATE

DCL：Data Control Language 数据控制语言（权限）

GRANT，REVOKE，COMMIT，ROLLBACK

DQL：Data Query Language 数据查询语言

SELECT

SQL语句构成

SQL语句构成：
Keyword组成clause（字句）
多条clause组成语句
示例：
SELECT * SELECT子句
FROM products FROM子句
WHERE price>400 WHERE子句
说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

一. 数据库操作

utf8mb4编码比utf8多一个功能（支持字符—表情包等）

创建数据库：

建议-----指定字符集

在创建数据库的时候指定好字符集—防止乱码
在/etc/my.cof 要重启服务
这里改的是服务端的字符集

指定客户端字符集

mysql --character-sets-dir=utf8md4

永久在/etc/my.cnf.d.mysql-clients.cnf
default-character-set=utf8mb4

1.创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; 
CHARACTER SET 'character set name’COLLATE 'collate name'
 //没有指定编发格式
create database test2;           
show create database test2;

//指定编码格式
create database test2  charater set utf8mb4;           
show create database test2;

2.修改数据库：

ALTER DATABASE DB_NAME character set utf8;

3. 删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 

查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;
获取命令使用帮助：

mysql> HELP KEYWORD;

help create database  查看关键字帮助

4.查看数据库列表：

mysql> SHOW DATABASES;

二. 表：二维关系

默认表的字符格式，与数据库的格式一致

设计表：遵循规范
定义：字段，索引
字段：字段名，字段数据类型，修饰符

约束，索引：应该创建在经常用作查询条件的字段上

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
[(create_definition,...)] [table_options] [partition_options] select_statement

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE 
old_tbl_name | (LIKE old_tbl_name) }

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

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2  type2 修饰符, ...)

字段信息
• col type1
• PRIMARY KEY(col1,…)
• INDEX(col1, …)
• UNIQUE KEY(col1, …)
表选项：

• ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
• ROW_FORMAT [=] 
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

获取帮助：mysql> HELP CREATE TABLE;

查看所有的引擎：SHOW ENGINES
查看表：SHOW TABLES [FROM db_name]
查看表结构：DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
删除表：DROP TABLE [IF EXISTS] tb_name
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name’
查看库中所有表状态：SHOW TABLE STATUS FROM db_name

数据类型

MySql支持多种列类型：
数值类型
日期/时间类型
字符串(字符)类型

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

char和vachar的区别：
char（4 ）定长----只存4个字符 （不够补，多余砍掉）
宽度固定，规律
varchar（5 ）变长-----可以变（少了节约空间，多了补充）

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值视为真

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、定点数

在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使 用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

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：
• 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

5.二进制数据：BLOB

• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写

• BLOB存储的数据只能整体读出
• TEXT可以指定字符集，BLOB不用指定字符集

6.日期时间类型

• 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 无符号

MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name           | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset   | 1     |
+--------------------------+-------+ 2 rows in set (0.001 sec)
# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table t1(id int unsigned auto_increment primary key) 
auto_increment = 4294967294;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(null);                                
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
mysql> insert into t1 values(null);                                
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
MariaDB [testdb]> insert t1 value(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1