mysql_3 基础语句

标签（空格分隔）： mysql

原博客： https://www.jianshu.com/p/e872bc12f583
原视频： https://www.bilibili.com/video/BV157411K7sf

sql介绍

结构化的查询语言关系型数据中通用的一类语言
SQL标准 89 92 99 03

help contents

help xx xx 查看帮助

DDL : 数据定义语言
DCL : 数据控制语言
DML : 数据操作语言
DQL : 数据查询语言

SQL mode SQL模式

作用规范sql语句的书写方式
select @@sql_mode

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。必须设置，以后各项可能依赖于该项的设置
NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
ONLY_FULL_GROUP_BY：
对于GROUP BY操作，如果在SELECT中出现的单独的列，没有在GROUP BY子句中出现，那么这个SQL是不合法的
NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了
NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零
NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：
禁止GRANT语句创建密码为空的用户
PIPES_AS_CONCAT：
将“||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
也可以在命令行查看和设置sql_mode变量

字符集 chatset 和校验规则 collation

字符集

show collation
utf8
utf8mb4 可以存表情

校对规则

每种字符集有多种校对规则（排序规则）
show collation
作用：
影响到排序的操作

a
b
ab
Ab
aB
ba
A
B
A Ba a aB b
ASCII码的规则

show collation;

数据类型

数据类型存储长度二进制数二进制数范围
整数 tinyint 0-255
整数 smallint 65535

网上有
https://www.w3school.com.cn/sql/sql_datatypes.asp

enum 枚举
('成都','北京','上海')

json{
    id:1,
    name:"sab"
}

约束

PK                  主键  唯一且非空   每张表只能有一个主键 作为聚簇索引
NOT NULL            非空
UNIQUE              唯一
UNSIGNED            针对数字列 非空的数据

其他属性

default 默认值
comment 注释

SQL应用

client
? 帮助
\c 结束上条命令运行
\G 格式化输出
\q 退出mysql会话
source 导入sql脚本
system 调用linux命令

Server

linux 一切皆文件、命令
mysql 一切皆sql、表

DDL

DDL 数据定义语言
库定义库名库属性
创建库
create database 库名 charset utf8mb4;
规范
1.库名大写业务有关不要数字开头库名不要太长不能使用保留字符串
2.必须指定字符集

查库
show databases；
show create database 库名

修改库
alter database 库名 charset 字符集

删除库
drop database 库名;

表操作

查看
show tables
desc tables
show create table

修改表
alter table wx_users add column shouji varchar(11) not null unique key comment '手机号';

alter table wx_users modity shouji varchar(12) not null unique key comment '手机号';

alter table wx_users change shouji varchar(12) not null unique key comment '手机号' ;

alter table wx_users drop shouji;

删除表
drop table wx_users

新增表
CREATE TABLE wx_users
(
Id int primary key,
LastName varchar(255) not null,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
ComeTime datetime not null default NOW()
)ENGINE=INNODB CHARSET=utf8mb4

此类操作会锁表因为修改了表的元数据
类似于linux 中的 inode （磁盘位置、文件属性、）更改属性时inode会变化

DML

select
insert
delete
update

insert

insert into 表名(字段1,字段2) values (字段1,字段2), (字段1,字段2);

insert into 表名 values (字段1,字段2), (字段1,字段2);

update

update table set a = 1 , b = 2 where id = ?

delete

delete from table where id = ?
4.1.3 select 标准用法配合其他子句使用

几种delete的区别

delete from 表逻辑上逐行删除数据行多操作很慢并没从磁盘上删除只是在存储上打了标记并不立即释放

drop table 表表结构元数据物理角度删除

truncate table 表清空表段中的所有数据页磁盘空间立即释放 HWM高水位线会降低

select

select user()
select version()
help contents Functions

select 10*1000

select @@port
show variables ;

show variables like '%trx%';

单表

前提

默认执行顺序

select

1.from 表1,表2

2.where 条件1 条件2

3.group by 条件1 条件2

4.having 过滤条1 过滤条2

5.order by 条件列1 条件列2

6.limit 限制条件

select 列 from table

select * from table 全部

select + from + where 配合使用 --> grep

= > < >= <= != 条件

select * from table where id > 1

select * from table where 条件 like 'ch%'

ch% 别写 %ch% 不走索引

select * from + where + group by

group by 配合聚合函数 max min avg count sum group_concat 使用

聚合函数

max 最大值

min 最小值

avg 平均值

count 数

sum 求和

group_concat 列转行

说明 : 碰到group by 必然会有聚合函数。

驱动表

在多表连接中，承当for循环中外层循坏的角色。
此时，mysql会拿着驱动表的每个满足条件的关联列的值，去依次找到for循环内循环中的关联值一一进行判断和匹配

建议：
关联时使用小表作为驱动表降低next loop次数 left join 可以强制左表为驱动表。

union 和 union all 区别
union 聚集两个结果集会自动进行结果集去重复

union all 聚集两个结果集不会去重复

show 语句

show databases;
show tables;
show tables from world; #查看world库下的表
show processlist; #查看连接情况
show full processlist #全部用户连接情况
show charset #查看字符集
show collation #查看校对规则
show engines #查看引擎
show privileges #查看权限
show grants for #查看用户的权限
show create database #查看建库
show create table #查看建表
show index from # 查看表的索引信息
show engine innodb status #查看innodb引擎状态
show status # 查看数据库状态
show status like '%%' #查询数据状态
show variables # 查看所有数据库参数
show variables like '%%' #某些参数部分
show binary logs #查看二进制日志文件信息
show binlog events in # 查看二进制日志事件
show master status # 查看二进制日志的位置点信息
show slave status # 查询从库状态信息
show relaylog events in #查看中继日志事件

结构逻辑表

数据字典

存放表中列的定义信息

myisam:

t1.frm

innodb:
两份
t1.frm ibdata1 8.0之前

数据行记录

myisam:
t1.MYD

innodb:
t1.ibd

索引

myisam:
t1.MYI

innodb:
t1.ibd

数据库状态

mysql 库 ps sys

权限

mysql 库
user
db
table
column

日志

专门日志文件

information schema

查询部分metadata视图

状态数据字典权限

元数据获取

每次数据库启动会自动在内存中生成I_S 生成查询mysql部分元数据信息试图。

试图？
select语句的执行方法不保存数据本身
I_S中的试图，保存的就是查询元数据的方法

create view v as select * from user;

select * from v;

I_S table

保存了所有的表的数据字典信息

desc tables
//单位都是B
TABLE_SCHEMA #表所在的库
TABLE_NAME #表名
ENGINE #引擎
TABLE_ROWS #多少行
AVG_ROW_LENGTH #平均行长度
DATA_LENGTH #数据大小
INDEX_LENGTH #索引大小
DATA_FREE #表中是否有碎片

case

case when 判断 then 结果 when 判断2 then 结果2 end

posted @ 2021-03-16 09:30 gidos 阅读(58) 评论(0) 编辑收藏举报

huuyann

mysql_3 基础语句

mysql_3 基础语句

sql介绍

help contents

SQL mode SQL模式

字符集 chatset 和校验规则 collation

字符集

校对规则

数据类型

约束

其他属性

SQL应用

Server

DDL

表操作

DML

insert

update

delete

几种delete的区别

select

show 语句

结构逻辑表

数据字典

数据行记录

索引

数据库状态

权限

日志

information schema

元数据获取

I_S table

case

公告

huuyann

mysql_3 基础语句

mysql_3 基础语句

sql介绍

help contents

SQL mode SQL模式

字符集 chatset 和校验规则 collation

字符集

校对规则

数据类型

约束

其他属性

SQL应用

Server

DDL

表操作

DML

insert

update

delete

几种delete的区别

select

show 语句

结构 逻辑表

数据字典

数据行记录

索引

数据库状态

权限

日志

information schema

元数据获取

I_S table

case

公告

结构逻辑表