大二学mysql的时候记录了一部分命令,想着还没发过文章,那就把这个作为第一篇吧!希望大家都能有收获!

有点潦草,过后有时间修改吧! ——220825

进入cmd

windows+R

输入cmd

显示帮助

\h

\?

显示警告

\W

show warnings;

隐藏警告

\w

进入数据库的步骤

net start mysql

mysql -u root -p 或者 mysql -h localhost -u root -p

退出数据库

\q

exit

quit

刷新权限

flush privileges;

创建数据库

create database 库名;

查看创建的数据库

show create database 库名;

show databases;

修改编码

alter database 库名 default character set 编码 collate 编码_bin;

删除数据库

drop database 库名;

drop database if exists 库名;

显示当前使用的数据库

select database();

创建表

use 所属数据库;

create table 表名

(

字段名1 数据类型 [约束],

字段名2 数据类型 [约束],

……,

字段名n 数据类型 [约束]

);

查看表(分号改为\G格式更好点)

show create table 表名; 

show tables;

describe 表名;

desc 表名;

此处显示的参数值:

FIELD:该表字段名

TYPE:对应字段的数据类型

NULL:表示该列是否可以存储NULL值

KEY:表示该列是否已经编制索引

DEFAULT:表示该列是否有默认值

EXTRA:表示获取到的与给定列相关的附加信息

修改表名称

alter table 旧表名 rename [to] 新表名;

修改表中参数的字段名

alter table 表名 change 旧字段名 新字段名 原数据类型;  (直接改成新数据类型也可以)

修改表中参数的数据类型(同时可以添加约束)

alter table 表名 modify 字段名 新数据类型 [约束类型];

表的约束

主键约束 primary key

非空约束 not null

唯一性约束 unique

默认约束 default 数值(用于设置字段的默认值)

值自动增加 auto_increment

外键约束 foreign key(涉及多表操作,两个及以上)

创建表时加外键格式:(最后一句)

constraint 外键名 foreign key(外键字段名) references 主表名(主表的主键字段名)

创建表后添加外键

alter table add constraint 外键名 foreign key(从表的外键字段名) references 主表名(主表的主键字段名)

删除外键约束

alter table 从表名 drop foreign key 外键名;

添加新参数

alter table 表名 add 新字段名 新数据类型;

删除字段名

alter table 表名 drop 字段名;

修改字段位置

alter table表名 modify 字段名 数据类型 [first/after 字段名];

删除表

drop table 表名;

插入记录

insert into 表名[(字段名列表)]

values (default/null/值1)[, (default/null/值2),…(default/null/值n)];

修改记录

update表名 set 字段名=表达式/值 [where 条件表达式];

删除部分或所有数据

delete [from] 表名 [where 条件表达式];

delete语句和drop语句的区别

delete语句只删除表中数据,drop语句删除表结构

清空所有数据

truncate [table] 表名;

delete语句和truncate语句的区别

(1)delete语句是DML语句;truncate语句是DDL语句

(2)delete语句可以通过指定后面的where子句来删除满足条件的部分记录;truncate语句只能删除表中所有记录,不能加where子句

(3)delete语句清空表后,auto_increment不会恢复为初始值;truncate语句清空表后,auto_increment会被重置为初始值

(4)因为用delete语句删除的内容仍会在日志中保留,而truncate语句删除的则不会,所以truncate语句的执行效率比delete语句高

查询表内容(全内容)

use 所属数据库;

select [all/distinct] */字段名 [ [as] 别名]                         (*表示全部)

from 表名

[where 条件表达式]

[group by 字段名列表 [having 逻辑表达式] ]                    (分组)

[order by 字段名 [asc|desc]]

(asc升序(一般为默认,但一定要加上),desc降序)(可多个字段)

[limit [offset,] n];                                              (限制查询结果的数量)

group by子句和order by子句的区别

group by子句用于对查询结果按指定字段的值进行分组,字段值相同的放在一起,order by子句是对查询结果集中的数据行按照指定字段的值重新排列

where子句的条件表达式中的列表运算符in的使用

select [all/distinct] */字段名 [ [as] 别名]                         (*表示全部)

from 表名 where 字段名 (not) in (‘值1’,’值2’,…,’值n’);

统计查询

select 集合函数 [[as] 别名] from 表名 [where子句];

集合函数

count()、sum()、avg()、max()、min()

where子句和having子句的区别

(1)where子句设置的查询筛选条件在group by子句之前发生作用,并且条件中不可以使用集合函数

(2)having子句设置的查询筛选条件在group by子句之后发生作用,并且条件中可以使用集合函数

交叉连接查询

select 字段名列表 from 表名1 cross join 表名2;

内连接查询(两表连接)

select 字段名列表 from 表名1 [inner] join 表名2 on 表名1.字段名 比较运算符 表名2.字段名;

或者

select 字段名列表 from 表名1,表名2 where 表名1.字段名 比较运算符 表名2.字段名;

内连接查询(三表连接)

select 字段名列表 from 表名1 join 表名2 on 表名1.字段名 比较运算符 表名2.字段名 join 表名3 on 表名3.字段名 比较运算符 表名1或表名2.字段名;

或者

select 字段名列表 from 表名1,表名2, 表名3 where 表名1.字段名 比较运算符 表名2.字段名 and 表名3.字段名 比较运算符 表名1或表名2.字段名;

外连接查询(仅适用于两个表)

select 字段名列表 from 表名1 [left/right/full] join 表名2 on 表名1.字段名 比较运算符 表名2.字段名;

左外连接   左表为主表,右表填充null

右外连接   右表为主表,左表填充null

全外连接   互相填充

自连接查询(注意是一个表出现两次,只是别名不同)

select 字段名列表 from 表名 as 别名1 join 表名 as 别名2 on 别名1.字段名=别名2. 字段名 [where 子句];

子查询---可多层嵌套,不止一层

比较子查询(一般来说,用到all的情况时前面为不等号)

select 字段名 [[as] 别名] from 表名 where 字段名=[any/all](select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

in子查询

select 字段名 [[as] 别名] from 表名 where 字段名 (not) in (select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

exists子查询

select 字段名 [[as] 别名] from 表名 where (not) exists (select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

insert语句中使用子查询(两组字段需一一对应)

insert 表名1 [(字段名列表1)] select 字段名列表2 from 表名2 [where 条件表达式];

update语句中使用子查询

update 表名1 set 字段名=值 where 字段名 (not) in (select 字段名 from 表名2 [where 条件表达式]);

delete语句中使用子查询

delete from 表名1 where 字段名=(select 字段名 from 表名2 [where 条件表达式]);

合并结果集(两组字段需一一对应,并且只能在最后一个select语句有order by子句)

select 字段名列表1 from 表名1 union select字段名列表2 from 表名2;

定义局部变量(没有default为null)

declare 变量名 数据类型 [default 值];

赋值局部变量

set变量名=值;

用户自定义变量

set @变量名[=值];

调用用户自定义变量

select @变量名[1, @变量名2,…, @变量名n];

流程控制语句

if语句

if 逻辑判断语句 then mysql 语句

[else if 逻辑判断语句 then mysql 语句]

[else mysql 语句]

end if;

case语句

case 表达式 when 表达式可能匹配值 then mysql 语句

[when 表达式可能匹配值 then mysql 语句]

[else mysql 语句]

end case;

loop语句

[循环标记名称:] loop mysql 语句 end loop [循环标记名称];

leave语句(执行该语句时会无条件跳出到该标记名称所标识的语句块,即跳出循环体)

leave 循环标记名称:

iterate语句(结束本次循环,转到循环开始语句,进行下一次循环)

iterate循环标记名称:

repeat语句(先执行一次循环语句块再进行条件表达式判断,如果条件表达式结果为true循环结束,否则再重复执行一次循环语句块)

[循环标记名称:] repeat mysql 语句 until 逻辑判断语句 end repeat [循环标记名称];

while语句(首先判断条件表达式,结果为true则继续执行一次循环语句块,执行完再次判断条件表达式,如果条件表达式结果为false直接退出循环)

[循环标记名称:] while 逻辑判断语句 do mysql 语句 end while [循环标记名称];

光标使用步骤:声明→打开→使用

声明光标

declare 光标名 cursor for select 字段名[列表] from 表名:

打开光标

open光标名;

使用光标

fetch光标名 into 变量名[列表]:

关闭光标

close光标名;

创建和调用存储过程(创建时首先要修改结束符,创建完也要把结束符修改回来)

创建无参存储过程

use 所属数据库;

delimiter //

create procedure 存储过程名() [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储过程

call 存储过程名();

创建有参存储过程

delimiter //

create procedure 存储过程名([in/out/inout] 参数名 数据类型) [存储特性]

begin mysql语句 end;//

delimiter ;

调用带输入参数in的有参存储过程

set @变量名=’字段名’;

call 存储过程名(变量名);

或者

call 存储过程名(‘字段名’);

调用带输入参数in和输出参数out的有参存储过程

set @变量名=0;

call 存储过程名(‘字段名’,变量名);

显示数据库内存储过程和存储函数列表

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema=’数据库名’;

显示数据库内所有存储过程和存储函数的名称和详细信息

show procedure status where db=’数据库名’;

查看存储过程的定义语句等信息

show create procedure 数据库.存储过程名;

修改存储过程的特性

alter procedure 存储过程名 [language sql/(not) deterministic/{contains sql/no sql/reads sql data/modifies sql data}/sql security{definer/invoker}];

存储过程特性

contains sql:存储程序不包含读或写数据的语句(如果没指明则为默认)

no sql:存储程序不包含mysql语句

reads sql data:存储程序含读数据的语句

modifies sql data:存储程序含写数据的语句

deterministic:输入不变的情况下输出是否确定

sql security{definer/invoker}:指明谁有权限执行存储过程

definer:只有定义者能执行存储过程

invoker:调用者可以执行存储过程

删除存储过程

drop procedure [if exists] 存储过程名;

存储过程和存储函数的联系和区别

(1)联系:结构上相似,都是由sql语句和过程式语句组成的代码段,都可以被别的应用程序或sql语句所调用

(2)区别:存储过程需要输出参数处理结果和call语句进行调用,存储函数必须使用return语句返回结果

创建和调用存储函数(创建时首先要修改结束符,创建完也要把结束符修改回来)

创建无参存储函数

use 所属数据库;

delimiter //

create function 存储函数名() returns 数据类型 [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储函数

set @变量名=存储函数名();

select @变量名;

创建有参存储函数

delimiter //

create function 存储函数名(参数 数据类型) returns 数据类型 [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储函数

set @变量名1=值;

set @变量名2=存储函数名(@变量名1);

select @变量名2;

查看存储函数的定义语句等信息

show create function 数据库.存储函数名;

删除存储函数

drop function [if exists] 存储函数名;

启动事务

start transaction;

提交事务

commit;

完成事务全过程

use 所属数据库;

start transaction;

mysql 语句;

commit;

回滚事务

rollback;

设置隔离级别

set [session/global] transaction isolation level {read uncommitted/read committed/repeatable read/serializable};

隔离级别

read uncommitted:读未提交

read committed:读提交

repeatable read:可重复读

serializable:可串行化

创建表时创建索引

use 所属数据库;

create table 表名

(

字段名1 数据类型 [约束],

字段名2 数据类型 [约束],

……,

字段名n 数据类型 [unique/fulltext/spatial] index/key [别名](字段名[(长度)]) [asc/desc]

)[engine=myisam];                                              (长度指检索出的量)

索引类型

index:普通索引,基本索引类型,允许在定义索引的字段插入重复值或空值,该索引可创建在任何数据类型中

unique:唯一索引,定义索引的字段的值必须唯一,但允许有空值,如果是在多个字段上建立的组合索引,则字段值的组合必须唯一,在创建主键和唯一约束的字段上会自动创建唯一索引,主键索引是特殊的唯一索引,不允许有空值

fulltext:全文索引,允许在定义索引的字段上插入重复值和空值,只能创建在char,varchar和text类型的字段上

spatial:空间索引,只能在空间数据类型(geometry,point,linestring,polygon)上创建,且创建索引的字段必须声明为not null

单列索引:在表中单个字段上创建的索引,可以是任何索引,只要保证该索引只对应表中的一个字段即可

多列索引:在表中多个字段上创建的索引,只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用

查看索引的字段信息

explain select * from 表名 [where 条件表达式];

查看索引的定义语句等信息

show create table 表名\G

在已经存在的表上创建索引

create [unique/fulltext/spatial] index 索引名 on 表名(字段名[(长度)]) [asc/desc];

或者

alter table 表名 add [unique/fulltext/spatial] index 索引名(字段名[(长度)]) [asc/desc];

删除索引

drop index 索引名 on 表名;

或者

alter table 表名 drop index 索引名;

定义视图

create [or replace] [algorithm={undefined/merge/temptable}] view 视图名 [(字段名列表)] as select 语句 [with [cascaded/local] check option];

相关参数

or place:当具有同名视图时,将用新创建的视图覆盖原始图

algorithm:为视图选择的算法

undefined:默认算法,倾向于merge

merge:将引用视图的语句和视图的定义合并起来,使得视图定义的某一部分取代语句的对应部分

temptable:视图的结果将被置于临时表中,然后使用它执行语句

with check option:对于可更新视图,给定该子句来防止插入或更新行,除非select语句中的where子句为真

cascaded:默认,对该视图相关的所有视图和基表进行检查

local:限制check option仅作用在定义的视图上

查看视图的字段信息

describe 视图名;

或者

desc 视图名;

查看视图的定义信息

show table status like ’视图名’;

查看视图的定义语句、字符编码以及视图中记录的行数

show create view 视图名;

修改视图          (对于create和replace,视图存在时修改,视图不存在时会创建视图)

alter/replace/create [algorithm={undefined/merge/temptable}] view 视图名 [(字段名列表)] as select 语句 [with [cascaded/local] check option];

删除视图

drop view [if exists] 视图名[列表];

向视图插入记录

insert into 视图名[(字段名列表)]

values (default/null/值1)[, (default/null/值2),…(default/null/值n)];

修改视图的记录

update视图名 set 字段名=表达式/值 [where 条件表达式];

删除视图的部分或所有数据

delete [from] 视图名 [where 条件表达式];

创建触发器

use 所属数据库;

delimiter //

create trigger 触发器名 after/before insert/update/delete on 表名 for each row

begin sql语句; 触发程序 end;//

delimiter ;

删除触发器

drop trigger 触发器名;