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;