当前位置:   article > 正文

MYSQL文档

mysql文档

MYSQL命令

Navicat快捷键

打开查询窗口 Ctrl+Q

关闭当前查询窗口 Ctrl+W

打开一个新的查询窗口 Ctrl+N

打开命令行窗口 F6

运行查询窗口全部SQL语句 Ctrl+R

运行查询窗口选中的SQL语句 Ctrl+Shift+R

删除一行SQL语句 Ctrl+Shift+L

在查询窗口(写SQL的界面)复制当前行 Ctrl+D

查询窗口取消补全 Esc

表内容快速切换到表设计页面 Ctrl+D

在表设计页面切换到表内容显示界面 Ctrl+O

跳转到整篇语句开头 Ctrl+Home

跳转到整篇语句结尾 Ctrl+End

注释 Ctrl+/

解除注释 Ctrl+Shift+/


函数

函数:是指一段可以直接被另一段程序调用的程序或代码

常用函数

字符串函数
函数名作用
concat(s1,s2,sn…)字符串拼接,依次拼接s1,s2…
substring(str,start,len)字符串截取,从start开始截取len个长度的字符串
trim(str)去前后空格
lower(str)str全部转小写
upper(str)str全部转大写
lpad(str,n,pad)左填充,用pad填充str的左边,知道达到n个字符
rpad(str,n,pad)右填充,用pad填充str的左边,知道达到n个字符
数值型函数
函数名作用
ceil(x)向上取整
floor(x)向下取整
mod(x,y)x%y
rand()返回0~1内的随机数,不包括0,1
round(x,y)四舍五入,保留y位小数
日期型函数
函数名作用格式
curdate()返回当前日期YYYY-MM-DD
curtime()返回当前时间HH-MM-SS
now()返回当前日期和时间YYYY-MM-DD HH-MM-SS
year(date)获取指定日期的年份YYYY
month(date)获取指定日期的月份MM
day(date)获取指定日期的日期DD
date_add(date,interval expr lype)返回一个日期|时间值加上一个时间间隔后的间隔YYYY-MM-DD HH-MM-SS
datediff(date1,date2)返回date1和date2之间的天数number|NULL
流程控制函数
函数名作用
if(value,t,f)如果value为true,则返回t,否则f
ifnull(value1,value2)若value不为空,返回value1,否则value2
case when [[val1] then [res1]]…else[default] end如val1为true,返回res1,…否则返回default默认值
case [expr] when [val1] then [res1]…else [default] end若expr的值等于val1,返回res1,…否则返回default默认值

管理用户

mysql的用户信息存在mysql数据库下的user数据表

查询用户

use mysql

select * from user

创建用户 create user ‘用户名’@‘主机名’ identified by ‘密码’

主机名 localnost什么都不能做,%:任意主机都可以访问mysql服务器s

修改用户密码 alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’

删除用户 drop user ‘用户名’@‘主机名’

授予查询权限 show grants for ‘用户名’@‘主机名’

授予权限 grants 权限列表 on 数据库名.表名 to ‘用户名’ @‘主机名’ 授予全部就写*.*

撤销权限 revoke 权限列表 on 数据库名.表名 from ’用户名’@‘主机名’

注意:多个权限用逗号分隔

权限列表:all,select,alter,delete

mysql报错代码

常见报错代码

1366 - Incorrect string value: ‘\xE7\x8E\x8B\xE7\xBA\xA2…’ for column ‘姓名’ at row 1

数据库编码或数据表编码错误

修改数据库编码 alter database <数据库名> character set <编码>

修改数据表编码 alter table <数据表名> convert to character set <编码>

1146 - Table ‘数据库.商品表’ doesn’t exist

数据库名或数据表名输入错误

mysql相关信息

mysql是关系型数据库

RDBMS 关系型数据库

DB 数据库

DBMS数据库管理系统

使用SQl语句控制DBMS创建维护DB

启动服务器

net start mysql57

停止服务器

net stop mysql57

客户端连接

mysql [-h 127.0.0.0] [-p 3306] -u root -p -h:连接ip -p:连接端口

单行注释 –

多行注释 /**/

show engines 查看可用存储引擎

myisam 要求查询速度更快

memory 临时表存储

innoDB 表与表进行连接

SQL分类

分类全称说明
DDLData Definition Language数据定义语言,用于定义数据库对象(数据库,表,字段)
DMLData Manipulation Language数据操作语言,用于对数据库表中的数据增删改
DQLData Query Language数据查询语言,用来查询数据库中表的记录
DCLData Control Language数据控制语言,用来创建数据库用户,控制数据库的访问权限

字符序

ci 大小写不敏感

cs 大小写敏感

bin 按二进制编码值比较


约束

约束:作用与表中字段上的规则,用于限制存储在表中的数据

作用:保证数据的正确性、有效性、完整性

注意:约束是作用于表中字段上的,可以在创建/修改表的时候添加约束

主键约束 Primary key

是一行数据的唯一标识,要求非空且唯一

设计一张表必须要有主键

多字段联合主键需要在最后写:primary key(字段1,字段2)

外键约束 foreign key

用于让两张表之间的数据建立连接,保证数据的一致性和完整性

注意:字段类型长度父表子表必须一样,引擎必须是innoDB,外键名是唯一的,如果没有值,可以直接创建,如果有值,必须再父表的主键中设置

删除/更新行为
删除/更新行为作用
no action在父表删除/更新记录时候,首先检查该记录是否有对应外键,有就不允许删除。更新。(与restrict一致)
restrict在父表删除/更新记录时候,首先检查该记录是否有对应外键,有就不允许删除。更新。(与no action一致)
cascade在父表删除/更新记录时候,首先检查该记录是否有对应外键,有就删除/更新在子表中的记录
set null在父表删除/更新记录时候,首先检查该记录是否有对应外键,若有则设置子表中改外键值为NULL(要求该外键允许取NULL)
set default父表有变更时,子表将外键设置为一个默认的值(inNodb不支持)
唯一约束 unique

保证改字段是唯一的,不重复的,只能有一个null值

检查约束(8.0.16版本之后) check

非空约束 not null

限制该字段不能为null

默认值约束 default

若未指定该字段的值,则采用默认值


数据类型

数值型
类型大小范围(有符号)范围(无符号)用途
tinyint1字节(-128,127)小整数值
smallint2字节(-32768,32767)大整数值
mediumint3字节(-8388608,8388607)大整数值
int4字节()大整数值
bigint8字节极大整数值
float4字节单精度
浮点数值
double8字节算个精度浮点数值
decimal(m,n)如果m>n,m+2。否则n+2小数值固定

字符串型
类型大小用途备注
char0~255字节定长字符串性能好
varchar0~65535字节可变字符串性能差
tinyblob0~255字节
tinytext0~255字节
blob0~65535字节区分大小写
text0~65535字节不区分大小写
mediumblob0~1677215字节
mediumtext0~1677215字节
longblob0~4294967295字节
longtext0~4294967295字节
enum限制为一种选择
set允许一列有多个条目
日期时间型
类型大小范围格式用途
date3字节YYYY-MM-DD日期值
time3字节HH:MM:SS时间值或持续时间
year1字节YYYY年份值
datetime8字节YYYY-MMM-DD HH:MM:SS混合日期和时间值
timestamp4字节YYYYMMDDHHMMSS混合日期和时间值,时间戳

数据库相关命令

查看当前MYSQL服务器支持的字符集 Show character set

查看已有数据库 Show Databases

查看当前使用的数据库 select 数据库名()

查看当前数据库中的表 show tables

新建数据库 Create Database <数据库名>

新建数据库并指定默认字符集和排序规则 Create database [if not exists]<数据库名> default charset 字符集 collate 排序规则

修改数据库字符集和排序规则 Alter database <数据库名> character set 字符集 collate 排序规则

选择数据库 use <数据库名>

查看指定数据库建库信息 show create database <数据库名>

删除数据库 Drop Database [if exists] <数据库名>


表相关命令

查看可用存储引擎 show engines

创建表的时候修改存储引擎 alter table <表名> engine = 更改后的引擎名(写在最后)

查看数据表结构 describe 表名 或可缩写为desc 表名 或 show columns from <表名>

查看表详细结构 show create table <表名>

创建表 create table <表名> (字段名 类型 约束,字段2)[comment 表注释]

删除表 drop table [if exists] <表名>;

删除表后再创建表 truncate table <表名> 保留结构,删除数据

主键约束 primary key [默认值] 多个字段联合主键(复合主键),primary key (字段1,字段2,…)

唯一约束 unique

非空约束 not null

自增长 Auto_Increment

数值类型前自动补0 zerofill

无符号 unsigned

修改表名 alter table <旧表名> rename to <新表名>

修改字段数据类型 alter table <表名> modify <字段名> <数据类型> (也可添加非空约束)

修改字段排列位置 alter table <表名> modify <字段1> <数据类型> first | after <字段2> (first,修改到第一位,after,修改到指定字段后)

修改字段名 alter table <表名> change <旧字段名> <新字段名> <新字段类型>(数据类型可和原来一直,但类型不能为空) 也可以添加not null

添加字段 alter table <表名> add <新字段名> <数据类型> [comment 注释] [约束条件] [first | after 已存在字段名] (first为可选参数,如果写上则将新添加的字段设置为表的第一个字段. After,将新添加的字段添加到指定的”已存在字段名后面,默认为最后)

删除字段 alter table <表名> drop <字段名>[,drop <字段2>]

添加表主约束 alter table <表名> add primary key (字段名1,字段名2)

删除主键约束 alter table <表名> drop primary key(没有外键,和其他表没有关联)

添加外键约束 alter table <表名> add constraint <外键约束名> foreign key(外键字段) references 关联表名/父表(关联字段) on update [更新时行为] on delete [删除时行为]

删除外键约束 alter table <表名> drop foreign key <外键约束名>

添加唯一约束 alter table <表名> add constraint <唯一约束名(一般unique_字段名)> unique (字段名)

删除唯一约束 alter table <表名> drop index <唯一约束名>

删除若干个表 drop table [if exists] <表1,表2,表n> ( 与其他表没联系的表)

插入数据 insert into <表名> [(字段1,字段2,字段n)] values (数据1,数据2,数据n)[,(数据1,数据二,数据三)]; (字段和值一一对应,字符型用双单引号)

插入select查询结果 insert into 插入数据表名 (字段列表) select (查询字段列表) from 查询数据库名 where 条件

更新数据 update 表名 set 字段1=值1,字段2=值2,字段n=值n [where 条件] (忽略where则修改全部记录)

删除记录 delete from 表名 [where 条件] (忽略条件则删除全部记录)

索引

SELECT查询语句

查询语句 select [distinct] 字段列表 from <数据表> limit 偏移量,行数[where <查询条件> [group by <字段列表>] [order by <字段列表>] [having] [limit [<offset] <限制行数>] [Asce] |[Desc]

查询语句 select 内容 from 位置 [字句]

字句说明是否必须使用
where 条件按条件查找,分组之前使用
in(值1,值2,…)满足其中任意一个即可
between 值1 and 值2值1-值2,包括值1值2,前面加not表明范围外
like包含,%:任意字符,_:单一字符
is null空值
is not null非空值
and/some条件1 and 条件2…,连接多个条件
or条件1 and 条件2…,连接多个条件
distinct不显示重复值
limit[位置偏移量n,] 行数m从n+1行开始,显示m行,mysql从0行开始检索
order by 排序字段按排序字段排序
group by 分组字段按分组字段分组,必须在where字句后
having 条件分组之后使用
asce查询结果升序,默认
desc查询结果降序
avg(字段名)指定字段平均值
count(字段名)指定字段行数
max(字段名)指定字段最大值
min(字段名)指定字段最小值
sum(字段名)指定字段总和

多表查询

格式:select 字段 from 表1,表2 where 条件

概述:从多张表中查询数据

多表关系

一对一

实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(inique)

一对多

实现:在多的一方建立外键,指向一的一方的主键

多对多

实现:建立一张中间表,中间表至少包含两个外键,分别关联两方主键

连接分类
内连接

查询A、B交集部分数据(共同含有)

隐式内连接

格式:select 字段列表 from 表1,表2 where 条件

显式内连接

格式:select 字段列表 from 表1 [inner] join 表2 on 连接条件

外连接

注意:通常使用左外连接

左外连接

查询左表所有数据,以及两张表交集部分数据

格式:select 字段列表 from 表1 left [outer] join 表2 on 条件

右外连接

查询右表所有数据,以及两张表交集部分数据

格式:select 字段列表 from 表2 right [outer] join 表2 on 条件

自连接

注意:必须写别名

当前表与自身的连接查询,自连接必须使用表别名

根据实际情况选中内外连接

联合查询

把多次查询的结果合并起来,形成一个新的查询结果集

格式:select 字段列表 from 表A union[all] select 字段列表 from 表B

注意:

1.写了all就是直接和合并,没写就会去除重复值再合并;

2.联合查询的列数必须相同,类型也必须相同

字查询(嵌套查询)

概念:SQL语句中嵌套select语句,称为嵌套查询,又叫子查询

注意:子查询外部的语句可以是insert/update/delete/select中的任意一个

select * from 表1 where column1 = (select column1 from 表2)

分类(按结果)

1.标量子查询(结果为单个值)

2.列子查询(结果为一列)

3.行子查询(结果为一行)

4.表子查询(结果为多行多列)

分类(按位置)

1.where之后

2.from之后

3.select之后

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Monodyee/article/detail/475647
推荐阅读
相关标签
  

闽ICP备14008679号