day3-sql常用类型
-
-
SQL 使我们有能力访问数据库
-
注：存在许多的版本，他们为了兼容 ANSI 用类似的方式支持主要关键词（比如select、update、delete、insert、where等）但是它们也有自己的扩展
-
-
SQL 可从数据库取回数据
-
SQL 可在数据库中插入新的记录
-
SQL 可更新数据库中的数据
-
SQL 可从数据库删除记录
-
SQL 可创建新数据库
-
SQL 可在数据库中创建新表
-
SQL 可在数据库中创建存储过程
-
SQL 可在数据库中创建视图
-
mysql> help
mysql> help contents
-
-
DCL ： 数据控制语言
-
DML ： 数据操作语言
-
注：快速记忆（ D、C、M、Q、 定义、控制、操作、查询）
mysql> select @@sql_mode; ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
-
-
举个例子： 比如，emoji字符mb4中支持，utf8不支持。emoji表情字符，1个字符占4个字节，utf8存不下。
-
对于非BMP字符，utf8mb4使用4个字节来存储，utf8不能存储非BMP字符
-
innodb中默认最大可对767个字节建立索引
-
使用utf8 的列最多可对255个字符建立索引
-
例如：
show charset;
create database zabbix charset utf8mb4;
show create database zabbix;
- 5.7 版本设置默认字符集
1. 在[mysqld]下添加：
character-set-server=utf8
2. 在[mysql]下添加
default-character-set=utf8
show collation;
| 大小 | 范围(有符号) | 范围(无符号) | 用途 | |
| TINYINT | 1字节 | (-128，127) | (0，255) | 小整数值 |
| INT | 4字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| BINGINT | 8字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) |
create database wei charset utf8mb4; --创建表格式为utf8mb4 use wei; create table t1(id int ,name varchar(64),age tinyint); show tables; +---------------+ | Tables_in_wei | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) 查看表内容 desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(64) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
| 大小 | 用途 | |
| char(长度) | 0 ~ 255字符 | 定长字符串类型 |
| varchar(长度) | 0 ~ 65535字符 |
例如：
补充：
char(10)
但是，不同种类的字符，占用的存储长度空间是不一样的。
对于英文和数学，每个字符占1个字节长度。
对于中文，占用空间大小要考虑字符集。
utf8，utf8mb4，每个中文，占3个字节长度。emoji字符，占4个字节长度。
| 大小 (字节) | 范围 | 格式 | 用途 | |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS |
| 说明 | 存储需求 | |
| BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
| BINARY(M) | 固定长度二进制字符串 | M 字节 |
| VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
| TINYBLOB (M) | 非常小的BLOB | L+1 字节，在此，L<2^8 |
| BLOB (M) | 小 BLOB | L+2 字节，在此，L<2^16 |
| MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节，在此，L<2^24 |
| LONGBLOB (M) | 非常大的BLOB |
提示：默认情况下，MySQL不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定范围内。
列名称 BINARY(M) 或者 VARBINARY(M)
VARBINARY 类型的长度是可变的
| 存储范围 | |
| TINYBLOB | 最大长度为255 (28-1)字节 |
| BLOB | 最大长度为65535 (216-1)字节 |
| MEDIUMBLOB | 最大长度为16777215 (224-1)字节 |
| LONGBLOB |
约束主键类型：
-
-
not null ： 非空约束，作用：必须非空，我们建议每个列都设置非空。
-
unique key ： 唯一约束，作用：必须不重复的值
-
<字段名> <数据类型> PRIMARY KEY [默认值]
例1：在wei数据库中创建t2 数据表，其主键为id输入sql语句和运行结果为：
use wei; create table t2 ( id int(11) primary key, name varchar(25), depid int(11), salary float ); Query OK, 0 rows affected (0.20 sec) desc t2; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | depid | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
在定义完所有列之后，指定主键的语法格式为：
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
例2：在wei数据库中创建t3 数据表，其主键为id，输入sql语句如下：
create table t3 ( id int(11), name varchar(25), deptld int(11), salary float, primary key(id) );
PRIMARY KEY [字段1，字段2，…,字段n]
create table t5 ( name varchar(25), deptld int(11), salary float, primary key(name,deptld) );
ALTER TABLE <数据库名> ADD primary key (<列名>);
创建并查看t6表结构，如下：
create table t6 (id int,name varchar(25),deptld int(11),salary float); desc t6; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | deptld | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
例4：修改数据表t6，将字段id设置为主键，输入的sql语句和运行结果如下：
mysql> alter table t6 add primary key(id); Query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t6; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptld | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
-
-
\G 格式化输出
-
实例：
show databases \c ---直接退出不报错 select * from city\G --格式化输出，但是这个表有点长 desc t1\q ---直接退出 Bye
实例：
t100w.sql包下载地址：https://pan.baidu.com/s/11IQ7fVyxV9eNXVQJJyWDGg
提取码：c5pd
mysql> source t100w.sql mysql> system ls; APlayer.min.css APlayer.min.js ruby-1.9.3-p551.tar.gz t100w.sql
1. 创建数据库
create database wei charset utf8mb4;
规范：
mysql> show databases;
mysql> show create database wei;
3. 修改库
mysql> alter database wei charset utf8mb4;
A ---> B
B是字符集的严格超集。
4.
注：生产数据库中，除了管理员，任何人没有删库权限
mysql> drop database wordpress;
表定义：
1. 创建表
开发工作
CREATE TABLE `oldboy`.`wp_users`( `id` INT NOT NULL AUTO_INCREMENT COMMENT '用户序号', `name` VARCHAR(64) NOT NULL COMMENT '用户名', `age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄', `gender` CHAR(1) NOT NULL DEFAULT 'F' COMMENT '性别', `cometime` DATETIME NOT NULL COMMENT '注册时间', `shengfen` ENUM('北京市','上海市','天津市','深圳市','重庆市') NOT NULL DEFAULT '北京市' COMMENT '省份', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8mb4;
建表规范：
-
-
小写字母
-
不能数字开头
-
表名和业务有关
-
名字不要太长
-
不能使用关键字
-
-
必须设施存储引擎和字符集
-
数据类型：合适、简短、足够
-
必须有主键
-
每个列尽量设施
not null，不知道填啥，设定默认值 -
每个列要有注释
-
列名不要太长
-
-
-
warehouse_id 如果是数字应该换数据类型为数字的
-
station_region_id 同上
-
replenish_type 用 enum 可以节省空间
-
c_t 应该用时间类型
-
u_t 同上
-
is_deleted tinyint(1)
-
2. 查询表
mysql> show tables; mysql> desc wp_users; mysql> show create table wp_users;
3. 修改表
例子： -- 1. 添加手机号列 mysql> alter table wp_users add column shouji bigint not null unique key comment '手机号'; -- 2. 将shouji列数据类型修改为char(11) mysql> alter table wp_users modify shouji char(11) not null unique key comment '手机号'; -- 3. 删除手机号列（危险） mysql> alter table wp_users drop shouji;
4. 删除表
drop table wp_user;
