文章目录
一、数据库基础概念
(一)字符集:
Mariadb默认安装的字符集是latin1
big5 繁体汉字
gb2312 简体中文
utf-8国际语言
(二)字符集/序:
每种字符集都有多种字符序:通常影响查询时的大小写情况;
1.字符序常见结尾如下:
_bin(表示用编码值进行比较)
_ci(cs) 是否区分大小写,ci不区分,cs区分
如下后缀参考自:https://zhidao.baidu.com/question/561235824256928284.html
_stroke 按笔划排序
_ai(as) 是否区分重音,ai不区分,as区分
_ki(ks) 是否区分假名类型,ki不区分,ks区分
_wi(ws) 是否区分全半角,wi不区分,ws区分
示例:
例如 mysql 数据的字符序设置是 utf8_ci 这种 ,在执行查询语句时 会自动过滤大小写
select * from issue_messagewherecontent =‘Yes’
select * from issue_messagewherecontent =‘yes’
这2句SQL 语句的查询结果 完全一样
但是如果 字符序 改为 utf8_cs, 同样执行上述2句SQL 结果不一样
2.查看数据库支持的字符序:
#Defalt =yes 表示数据库默认的字符序
MariaDB [(none)]> show collation;
指定数据库创建使用的默认语言字符集:
MariaDB [(none)]> create database test3 character set utf8mb4;
MariaDB [(none)]> show create database test3;
3.库字符集配置文件路径:
每个库会建立单独的文件夹;
指定字符集创建的库在mysql目录中生成一个db.opt文件,该文件内容很简单,是用来记录该库的默认字符集编码和字符集排序规则用的。如果你创建数据库指定默认字符集和排序规则,那么后续创建的表如果没有指定字符集和排序规则,那么该新建的表将采用db.opt文件中指定的属性。
注意: 修改字符集适合空库,已有数据的库变更字符集可能会出现乱码问题
数据类型:
(三)mysql客户端连接数据库常用选项:
1.连接数据库
[root@CentOS7 ~]#mysql -u root -h localhost -p
mysql客户端可用选项:
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-e “SQL“ 执行SQL命令
--print-defaults 获取程序默认使用的配置
数据库服务器端服务监听方式:
本地方式:unix sock: 监听在sock文件上,仅支持本机连接通信(/var/lib/mysql/mysql.sock)
网络方式:ip socket: 默认监听在tcp的3306端口,支持远程连接通信
2.mysqladmin工具
#查看帮助
[root@CentOS7 ~]#mysqladmin -help
#修改mysql数据库账户密码
[root@CentOS7 ~]#mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password
#查看mysql服务是否正常
[root@CentOS7 ~]#mysqladmin -uroot -pmagedu ping
#创建数据库
[root@CentOS7 ~]#mysqladmin -uroot -p create testdb
#删除数据库
[root@CentOS7 ~]#mysqladmin -uroot -p drop testdb
#关闭数据库
[root@CentOS7 ~]#mysqladmin -uroot -p shutdown
3.mysql配置文件:
/etc/my.cnf #Global选项
/etc/mysql/my.cnf #Global选项
SYSCONFDIR/my.cnf #Global选项
$MYSQL_HOME/my.cnf #Server-specific 选项
--defaults-extra-file=path
~/.my.cnf #User-specific 选项
二、SQL语句
(一)规范:
1.在数据库系统中,SQL语句不区分大小写(建议用大写)
2.SQL语句可单行或多行书写,以“;”结尾
3.关键词不能跨多行或简写
4.用空格和缩进来提高语句的可读性
5.子句通常位于独立行,便于编辑,提高可读性
(二)分类:
- DDL:CREATE,DROP,ALTER
- DML:INSERT,DELETE,UPDATE
- DCL:GRANT,REVOKE,COMMIT,ROLLBACK
- DQL:SELECT
(三)数据库的组件(对象):
- 库
- 表
- 索引
- 视图
- 存储函数
- 存储过程
- 触发器
(四)关系运算
- 选择:挑选行
- 投影:挑选列
- 连接:表连接,主键外键
(五)数据模型:
1.数据抽象:
- 物理层:数据存储格式:RAID、逻辑卷、分区,运维关心的层;与存储引擎相关
- 逻辑层:数据间的关系
- 视图层:展现希望给用户的数据
2.关系模型:
- 关系模型:
- 基于对象关系型:ORM,面向关系与面向对象关系:把面向关系的数据库和面向对象的语言结合,不用写sql语句
- 半结构化关系模型:XML、json
三、库
1.创建库:
MariaDB [(none)]> create database if not exists test2;
2.修改库:
(1).修改库的字符集(注:空库)
MariaDB [(none)]> alter database db_test3 character set utf8mb4;
3.删除库:
MariaDB [(none)]> drop database if exists db_test3;
4.查看库:
#查看库列表
MariaDB [(none)]> show databases;
#查看支持字符集
MariaDB [(none)]> show character set;
#查看排序规则
MariaDB [(none)]> show collation;
5.使用库:
MariaDB [(none)]> use test3
四、 表
1.语法:
1.创建表:
方式一:直接创建新表
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (字段信息1,字段信息2, ... 表选项...)
字段信息
•col1 type1
•PRIMARY KEY(col1,...)
•INDEX(col1, ...)
•UNIQUE KEY(col1, ...)
表选项:
•ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
•ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
方式二:查询现存表创建;新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]
[partition_options] select_statement
方式三:复制现存的表的表结构创建,但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
MariaDB [test3]> help create table
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
col_name column_definition
#设置主键
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
#设置索引
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
#设置唯一键(索引)表示唯一的,不允许重复的索引,该字段信息保证不会重复
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
#FULLTEX:全文索引:检索数据量较大时,先将数据放入一个没有全局索引的表中,然后在用Create Index创建的Full Text索引,比先为一张表建立FullText然后在写入数据要快
#SPATIAL:空间索引:是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
#MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在MYISAM存储引擎的表中创建
#引用自:https://blog.csdn.net/guo_qiangqiang/article/details/88794971
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
#设置外键
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
#CHECK 约束用于限制列中的值的范围。
#如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
#如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制
| CHECK (expr)
column_definition: #列定义
#数据类型 是否允许为空 默认值
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] 列值自增 [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] #表的注释,最大长度为60 个字符
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
data_type: #数据类型
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
index_type: #索引类型
USING {BTREE | HASH} #btree、hash类型
index_option: #索引选项
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
#REFERENCES:对象权限
#ON UPDATE CASCADE 表示当父表更新时,子表也级联更新。
#ON DELETE CASCADE 表示当父表删除时,子表也级联删除
reference_definition: #引用定义
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
#在定义外键关系时,父表的更新(或者删除)动作,可级联子表操作有如下四种定义方式:
#RESTRICT,表示父表列值被保护(限制更改,不能有任何动作,效果和NO ACTION一致,其实就是NO #ACTION的别名)。也是外键级联操作的默认行为。
#CASCADE,表示子表列值随父表一起变化(更新也一起更新,删除也一起删除)。注意,子表上级联更改的列,不会激活触发器。
#SET NULL,表示父表列值被更改时,关联的子表列值置空(前提是子表该列属性可为NULL)。
#NO ACTION,表示父表列值被保护(阻止更改,不能有任何动作)。
#对于非innodb表,还有一个方式是:SET DEFAULT,但是innodb表,该定义不合法。
#引用自:https://blog.csdn.net/q3dxdx/article/details/51005098
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
table_options: #表选项
table_option [[,] table_option] ...
table_option: #表选项
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)
partition_options: #分区选项
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition: #分区定义
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)] #子分区
subpartition_definition: #子分区定义
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
select_statement: #可选项
[IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)
2.删除表
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
3.修改表
MariaDB [test3]> help alter table
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition #添加列字段
[FIRST | AFTER col_name ] #first表示添加到第一列;
| ADD [COLUMN] (col_name column_definition,...) #添加多列
| ADD {INDEX|KEY} [index_name] #添加索引
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY #添加主键
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] #添加唯一键
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name] #添加全文索引
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name] #添加空间索引
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] #添加外键
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} #修改默认值
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name] #修改字段名
| MODIFY [COLUMN] col_name column_definition #修改字段属性
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name #删除列
| DROP PRIMARY KEY #删除主键
| DROP {INDEX|KEY} index_name #删除索引
| DROP FOREIGN KEY fk_symbol #删除外键
| MAX_ROWS = rows #最大行
| DISABLE KEYS #禁用键
| ENABLE KEYS #启用键
| RENAME [TO|AS] new_tbl_name #重命名表名
| ORDER BY col_name [, col_name] ... #分组
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE #放弃表空间
| IMPORT TABLESPACE #导入表空间
| FORCE #强制
| ADD PARTITION (partition_definition) #添加分区
| DROP PARTITION partition_names #删除分区
| TRUNCATE PARTITION {partition_names | ALL} #截断分区
| COALESCE PARTITION number #合并分区
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)] #重组分区
| ANALYZE PARTITION {partition_names | ALL} #分析分区
| CHECK PARTITION {partition_names | ALL} #检测分区
| OPTIMIZE PARTITION {partition_names | ALL} #优化分区
| REBUILD PARTITION {partition_names | ALL} #重建分区
| REPAIR PARTITION {partition_names | ALL} #修复分区
| PARTITION BY partitioning_expression #分区
| REMOVE PARTITIONING #移除分区
index_col_name:
col_name [(length)] [ASC | DESC]
index_type: #修改索引类型
USING {BTREE | HASH}
index_option: #索引选项
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)
partition_options:
(see CREATE TABLE options)
2.创建表:
#方式一创建表
MariaDB [test3]> create table if not exists tb_student (id char(13) primary key,name varchar(20),sex enum('F','M'));
#方式二创建表
MariaDB [test3]> create table if not exists tb_teacher select * from hellodb.classes;
#方式按创建表
MariaDB [test3]> create table if not exists tb_teachers like hellodb.teachers;
3.查看表:
#查看表列表
MariaDB [test3]> show tables;
#查看表创建
MariaDB [test3]> show create table tb_student;
#查看所有引擎
MariaDB [test3]> show engines;
#查看表结构
MariaDB [test3]> desc tb_teachers;
MariaDB [test3]> show columns from tb_teachers;
#查看库中表状态
MariaDB [test3]> show table status from hellodb;
方式二创建表:
方式三创建表:
4.删除表
MariaDB [test3]> drop table if exists tb_teachers;
五、数据类型:
(一)数值型
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)
2.浮点型(float和double),近似值
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
- double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位
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
(二)字符串(char,varchar,_text)
1.定长字符串
- char(n) 固定长度,最多255个字符
2.变长字符串
- varchar(n) 可变长度,最多65535个字符
3.text
- tinytext 可变长度,最多255个字符
- text 可变长度,最多65535个字符
- mediumtext 可变长度,最多2的24次方-1个字符
- longtext 可变长度,最多2的32次方-1个字符
- BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节
- VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节
- 内建类型:ENUM枚举, SET集合
二进制数据:BLOB
- BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob是以二进制方式存储,不分大小写
- BLOB存储的数据只能整体读出
- TEXT可以指定字符集,BLOB不用指定字符集
(三) 日期时间类型
- date 日期 ‘2008-12-2’
- time 时间 ‘12:25:36’
- datetime 日期时间 ‘2008-12-2 22:06:44’
- timestamp 自动存储记录修改时间
- YEAR(2), YEAR(4):年份