MySQL数据库基本操作整理

1.mysql基本操作

net start 服务名//启动mysql服务

net stop 服务名//停止mysql服务

mysql -h主机地址 -P端口 -u用户名 -p密码//登录mysql

\q ,Quit,Exit; //退出mysql

 

2.数据库操作

create database 数据库名字 [charset 库选项];//创建数据库

show databases [like 匹配模式];//显示数据库

show create database 数据库名字;//查看数据库创建语句

use 数据库名字;//选择数据库名字

alter database 数据库名字 charset=字符集;//修改数据库字符集

drop database 数据库名字;//删除数据库

 

3.表操作

; , \g, \G,//sql语句结束符

create table 表名(字段名 字段类型[字段属性],字段名 字段类型[字段属性],....) [charset=字符集]；//创建表

create table 新表名 like 表名;//复制已有表结构

 

show tables [like 匹配模式];//显示表

desc 表名;//显示表结构

show columns from 表名;//显示表结构

describe 表名;//显示表结构

show create talbe 表名;//显示表创建语句

 

rename table 旧表名 to 新表名;//修改表名

alter table 表名 表选项=新值;//修改表选项

alter table 表名 add [column] 新增字段 列类型[列属性] [first/after 字段名];//新增字段

alter table 表名 change 旧字段名 新字段名 字段类型[列属性];//修改字段名

alter table 表名 modify 字段名 类型 [新属性];//修改字段属性

alter table 表名 drop 字段名;//删除字段

drop table [表名1，表名2...]；//删除表

 

4.表数据操作

insert into 表名 [(字段列表)] values (对应字段值列表);//插入数据

insert into 表名 [(字段列表)] values(值列表),(值列表)...;//插入多条记录

insert into 表名[(字段列表)] values(值列表) on duplicate key update 字段=新值//主键冲突更新方式插入数据

replace into 表名 [(字段列表)] values(值列表)；//主键冲突替换方式插入数据

//蠕虫复制：一分为二，成倍增加。从已有数据中获取数据，并且将获取到的数据插入到数据表中

insert into 表名[(字段列表)] select */字段列表 from 表；

 

select *from 表名;//查询表中全部数据

select 字段列表 from 表名;//查询表中部分字段数据

select 字段列表/* from 表名 where 字段名=值；//简单按条件查询数据

select 语句 Union[union选项] select语句;//联合查询，

select *from 表1 join[inner] 表2 on 匹配条件；//内连接查询

select *from 主表 left join 从表 on 连接条件;//左连接查询

select *from 从表 right join 主表 on 连接条件;//右连接查询

select *from 表1[inner,left,right] join 表2 using 连接条件；//自然连接查询

select *from 数据源 where 条件判断 =/<>(select 字段名 from 数据源 where 条件判断)；//标量子查询，一行一列

select *from 表1 where 条件判断 in(select 查询语句);//列子查询，一列多行

select *from 表1 where 条件判断(构造一个行元素)=(select 查询语句);//行子查询，一行多列

select 字段列表 from (表子查询)[where][group by][having][limit]；//表子查询，多行多列

select *from 表名 where exists(select 查询语句);//exists子查询

//完整的select查询语句

select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制;

（注：select 选项： all, distinct 。

select 数据源：frome 表1，表2...，或者是从表中查询出来的二维结果：from （select 字段列表 from表）as 别名

group by 字段1[desc/asc],字段2[desc/asc];//按字段分组并排序：

group by 字段 [asc/desc] with rollup;//回溯统计

order by 字段 [asc/desc],字段2 [asc/desc]；//按字段排序

limit 数量//限制数量 limit offset ,lenth;//分页）

 

delete from 表名 [where 条件];//删除数据

//更新数据，如果没有条件，是全表更新数据，可以使用limit来限制更新数量

update 表名 set 字段名=新值 [where 判断条件] [limit 数量]；

 

show variables like 'character_set%';//查看系统保存的关系字符集

set names 字符集名;//修改系统保存的关系字符集

set 变量名=值;//一个个修改系统保存的关系字符集

 

5.列属性

null//空

default//默认值

comment//列描述

auto_increment //自增长

primary key //主键

unique key//唯一键

 

//在创建表时在需要当做主键的字段之后增加primary key 属性来确定主键

create table 表名(字段名 字段类型[列属性] primary key) [charset=字符集];

//在创建表时在所有字段之后增加primary key选项确定主键

create table 表名(字段名 字段类型[列属性,字段名2 字段类型[列属性] ,primary key(字段名)) [charset=字符集];

alter table 表名 add primary key(字段名);//在创建表后增加主键

desc 表名；show create table 表名； //查看主键信息

alter table 表名 drop primary key;//删除主键

 

//在创建表时直接在表字段之后增加唯一键标识符unique[key] 来确定唯一键

create table 表名(字段名 字段类型[列属性] unique key) [charset=字符集];

//在创建表时在所有字段之后使用 unique key(字段名);

create table 表名(字段名 字段类型[列属性,字段名2 字段类型[列属性] unique key(字段名)) [charset=字符集];

alter table 表名 add unique key(字段名);//在创建完表之后增加唯一键

desc 表名；show create table 表名； //查看唯一键信息

alter table 表名 drop index 唯一键名字;//删除唯一键

 

6.数据库备份与还原

mysqldump/mysqldump.exe -hPup 数据库名字 [表1 [表2...]] > 备份文件地址//SQL备份

mysql.exe -hPup 数据库 <文件位置 //数据还原

 

7.用户权限管理

create user 用户名 indentified by '明文密码'; //创建用户，用户格式：用户名@主机地址

drop user 用户名@host;//删除用户

set password for 用户=password('新明文密码')；//修改用户密码

update mysql.user set password=password('新明文密码') where user='' and host='';//修改用户密码

grant 权限1，权限2../all privileages on 数据库.表名/数据库.*/*.* to用户;

revoke 权限1,权限2.../all privileages on 数据库.表名/数据库.*/*.* from 用户;

flush privileges;//刷新权限

 

8.外键

create table 从表(字段1 类型[属性]，字段2 类型[属性]....，[constraint '外键名'] foreign key(外键字段) references 主表(主键)) charset 字符集;//在创建表时增加外键

alter table 从表 add [constraint '外键名'] foreign key(外键字段) references 主表(主键)；//在创建表后增加外键

alter table 从表 drop foreign key 外键名字;//删除外键

alter table 表名 drop index 索引名字;//删除对应的索引

 

9.视图

create view 视图名字 as select 指令;//创建视图

select 字段列表 from 视图名字 [子句];//使用视图

alter view 视图名字 as 新select指令；//修改视图

drop view 视图名字;//删除视图

 

10.事务

start transaction;//开启事务

commit rollback;提交事务

savepoint 回滚点名字;//增加回滚点

rollback to 回滚点名字；//回到回滚点

 

11.变量

show variables [like 'pattern'];//查看系统变量

set 变量名=新值;//局部修改系统变量

set global 变量名=值; set@@global.变量名=新值//全局修改，只针对新客户端有效

 

set @变量名=值； set@变量名：=值；//用户变量，只对当前用户使用的客户端生效

select @变量1:=字段1，@变量2:=字段2 from 数据表 where 条件；//给用户变量赋值且查看赋值过程

select 字段1，字段2...from 数据源 where 条件 into 变量1，变量2...;//给用户变量赋值不看过程

select @变量名;//查看自定义变量

 

declare 变量名 数据类型[属性];//局部变量,使用declare关键字声明，declare语句的位置在begin和end语句块之间

 

12.流程结构

if(条件,为真结果，为假结果)//if分支，用在select查询当中

if 条件表达式 then 满足条件要执行的语句; else 不满足条件要执行的语句; end if; //用在复杂的语句块中

 

while 条件 do 要循环执行的代码, end while;//while循环

标识名字:while 条件 do 循环体 end while[标识名字];//结构标识符,为某些特定结构进行命名

 

13.函数

(delimiter 新符号//修改临时语句结束符

create function 函数名(形参) returns 返回值类型

begin

//函数体

return 返回值数据;//数据必须与结构中定义的返回值类型一致

end

语句结束符

delimiter ;//修改回语句结束符)----------//自字义函数

 

show function status [like pattern];//查看函数状态

show create function 函数名字;//查看函数创建语句

drop function 函数名;//删除函数

 

14.存储过程

(delimiter 新符号//修改临时语句结束符

create procedure 过程名字([参数列表])

begin

过程体

end

语句结束符

delimiter ;//修改回语句结束符)----------//自字义过程

 

show procedure status [like pattern];//查看过程

show create procedure 过程名字；//查看过程

call 过程名([实参列表]);//调用过程

drop procedure 过程名；//删除过程

 

15.触发器

(delimiter 新符号//修改临时语句结束符

create trigger 触发器名字 触发时机 触发事件 on 表 for each row

begin

触发体

end

语句结束符

delimiter ;//修改回语句结束符)----------//创建触发器

 

show triggers\G//查看触发器

show create trigger 触发器名字;//查看触发器

drop trigger 触发器名字;//删除触发器