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分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用于定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用于对数据库表中的数据增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data 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
若未指定该字段的值，则采用默认值
数据类型
数值型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| tinyint | 1字节 | (-128,127) | 小整数值 | |
| smallint | 2字节 | (-32768,32767) | 大整数值 | |
| mediumint | 3字节 | (-8388608,8388607) | 大整数值 | |
| int | 4字节 | () | 大整数值 | |
| bigint | 8字节 | 极大整数值 | ||
| float | 4字节 | 单精度 浮点数值 | ||
| double | 8字节 | 算个精度浮点数值 | ||
| decimal(m,n) | 如果m>n,m+2。否则n+2 | 小数值固定 |
字符串型
| 类型 | 大小 | 用途 | 备注 |
|---|---|---|---|
| char | 0~255字节 | 定长字符串 | 性能好 |
| varchar | 0~65535字节 | 可变字符串 | 性能差 |
| tinyblob | 0~255字节 | ||
| tinytext | 0~255字节 | ||
| blob | 0~65535字节 | 区分大小写 | |
| text | 0~65535字节 | 不区分大小写 | |
| mediumblob | 0~1677215字节 | ||
| mediumtext | 0~1677215字节 | ||
| longblob | 0~4294967295字节 | ||
| longtext | 0~4294967295字节 | ||
| enum | 限制为一种选择 | ||
| set | 允许一列有多个条目 |
日期时间型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| date | 3字节 | YYYY-MM-DD | 日期值 | |
| time | 3字节 | HH:MM:SS | 时间值或持续时间 | |
| year | 1字节 | YYYY | 年份值 | |
| datetime | 8字节 | YYYY-MMM-DD HH:MM:SS | 混合日期和时间值 | |
| timestamp | 4字节 | 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之后
扫一扫
535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
