mysql varchar 转成bigint_MySQL 指南之 SQL 语句基础

个人所有文章整理在此篇，将陆续更新收录:知无涯，行者之路莫言终(我的编程之路)

---

零、结构化查询语言：SQL(Structured Query Language)

DDL 数据定义语言 管理库，表

DML 数据操作语言 增删改查

DCL 数据控制语言 数据控制，权限访问等

---

准备活动：创建库和表

CREATE DATABASE datatype;

USE datatype;

CREATE TABLE type_number(

type CHAR(12),

byte TINYINT UNSIGNED,

range_singed VARCHAR(20),

range_unsinged VARCHAR(20),

info VARCHAR(40)

);

---

目前状态：

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| datatype |

| information_schema |

| mycode |

| mysql |

| performance_schema |

| seckill |

+--------------------+

mysql> USE datatype;

Database changed

mysql> SHOW TABLES;

+--------------------+

| Tables_in_datatype |

+--------------------+

| type_number |

+--------------------+

mysql> DESC type_number;

+----------------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------+---------------------+------+-----+---------+-------+

| type | char(12) | YES | | NULL | |

| byte | tinyint(3) unsigned | YES | | NULL | |

| range_singed | varchar(20) | YES | | NULL | |

| range_unsinged | varchar(20) | YES | | NULL | |

| info | varchar(40) | YES | | NULL | |

+----------------+---------------------+------+-----+---------+-------+

---

一、DML 数据库记录操作 LEVEL1

LEVEL 1 先简单掌握一下下面的用法

---

1、记录的插入操作

INSERT INTO(属性,...)VALUES(值,...),...;

|-- 插入一条数据 INSERT INTO (属性,...) VALUES (值,...);

INSERT INTO

type_number(type,byte,range_singed,range_unsinged,info)

VALUES

('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整数');

|-- 查询所有 SELECT * FROM ;

mysql> SELECT * FROM type_number;

+---------+------+----------------+----------------+--------------+

| type | byte | range_singed | range_unsinged | info |

+---------+------+----------------+----------------+--------------+

| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整数 |

+---------+------+----------------+----------------+--------------+

|-- 你也可以一次，插入多条数据

INSERT INTO

type_number(type,byte,range_singed,range_unsinged,info)

VALUES

('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整数'),

('SMALLINT',2,'-2¹⁶ ~ 2¹⁶-1','0 ~ 2¹⁶-1','小整数'),

('MEDIUMINT',3,'-2²⁴ ~ 2²⁴-1','0 ~ 2²⁴-1','中等整数'),

('INT',4,'-2³² ~ 2³²-1','0 ~ 2³²-1','标准整数'),

('BIGINT',8,'-2⁶⁴ ~ 2⁶⁴-1','0 ~ 2⁶⁴-1','大整数');

mysql> SELECT * FROM type_number;

+-----------+------+----------------------+----------------+--------------+

| type | byte | range_singed | range_unsinged | info |

+-----------+------+----------------------+----------------+--------------+

| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整数 |

| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整数 |

| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整数 |

| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整数 |

| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 标准整数 |

| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整数 |

+-----------+------+----------------------+----------------+--------------+

2、记录的更新操作

UPDATE SET 属性=值,...WHERE 条件;

UPDATE type_number

SET

info='微型整数'

WHERE type = 'TINYINT';

mysql> SELECT * FROM type_number;

+-----------+------+----------------------+----------------+--------------+

| type | byte | range_singed | range_unsinged | info |

+-----------+------+----------------------+----------------+--------------+

| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 微型整数 |

| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 微型整数 |

| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整数 |

| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整数 |

| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 标准整数 |

| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整数 |

+-----------+------+----------------------+----------------+--------------+

---

3.记录的删除操作

DELETE FROM WHERE 条件;

|--- 删除操作

DELETE FROM type_number

WHERE type = 'TINYINT';

mysql> SELECT * FROM type_number;

+-----------+------+----------------------+----------------+--------------+

| type | byte | range_singed | range_unsinged | info |

+-----------+------+----------------------+----------------+--------------+

| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整数 |

| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整数 |

| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 标准整数 |

| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整数 |

+-----------+------+----------------------+----------------+--------------+

---

4.记录的查询操作

SELECT 属性,...FROM WHERE 条件;

mysql>

SELECT

type,range_unsinged

FROM type_number

WHERE byte>=4;

+--------+----------------+

| type | range_unsinged |

+--------+----------------+

| INT | 0 ~ 2³²-1 |

| BIGINT | 0 ~ 2⁶⁴-1 |

+--------+----------------+

---

二、图片表 pic (LEVER2)

这个是用来记录图片信息的表，数据准备过程详见番外篇：[番外]-练习MySQL没素材？来一波字符串操作

1.建表语句

CREATE TABLE pic(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

pic_path VARCHAR(120) NOT NULL,

pic_length INT UNSIGNED DEFAULT 0,

pic_mime TINYINT UNSIGNED,

pic_width SMALLINT UNSIGNED,

pic_height SMALLINT UNSIGNED

);

|--- id 为主键 自增长

|--- pic_path表示名字，不定长度 ，给个VARCHAR 120 吧，差不多够用吧

|--- 图片文件大小不会非常大，给个INT足够了 ， 给个默认值 0

|--- pic_mime 0 表示 image/png 1表示 image/jpeg 给个最小的

|--- pic_width和pic_height也不会非常大，无符号SMALLINT足够

---

2.查询操作 AS 的作用

|-- 查询高大于1200像素的记录，使用AS 来 临时更改查询输出的属性名(不会改变实际记录)

mysql>

SELECT

pic_path AS 路径 ,

pic_width AS '宽/px',

pic_height AS '高/px'

FROM pic

WHERE pic_height>1200;

+----------------------+--------+--------+

| 路径 | 宽/px | 高/px |

+----------------------+--------+--------+

| 30000X20000.jpg | 30000 | 20000 |

| 3000X2000.jpg | 3000 | 2000 |

| ecNKedygCmSjTWWF.jpg | 700 | 1352 |

| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |

| HXqqASHJETSlvpnc.j