MySQL数据库

基本命令

进入数据库 mysql -u用户名 -p密码
退出数据库 \q

—库级操作

创建数据库 　　create database 数据库名;
查看所有数据库 show databases;
删除数据库　　 drop database 数据库名;
查看连接数据库 select database();
切换数据库　　 use 数据库名;

—表级操作

查看所有表 show tables;
查看表结构 desc 表名;/describe 表名; show create table 表名;
创建表 　　create table 表名(内容);
删除表 　　drop table 表名;
单表的操作
增
　为表的所有列插入数据 　　insert 表名 value(字段值1,字段值2...);
　为表的指定列插入数据　　 insert 表名(字段1,字段2) value(字段值1,字段值2);
　为表的指定列插入多条数据 insert 表名(字段1,字段2) values(字段值1,字段值2),(字段值1,字段值2);
　将查询结果插入表中 　　　insert 表名1 select 字段/* from 表名2 where 条件;
删
　根据条件删除 　　　　　　delete from 表名 where 条件;
　清空表 　　　　　　　　　truncate table 表名;
改
　　　　　　　　　　　　　update 表名 set 字段=值 where 条件;
查
　查询所有列　 　　　　　　select * from 表名;
　查询指定列
　　查询单个列 　　　　　　select 字段 from 表名;
　　查询多个列 　　　　　　select 字段1,字段2...字段n from 表名;
　查询指定记录 　　　　　　select 字段1,字段2...字段n from 表名 where 查询条件;
　查询结果不重复 　　　　　select distinct 字段名 from 表名;
　查询空值　　　　　　　　 select * from 表名 where 字段名 is null;
　查询结果分组 　　　　　　select 字段1,字段2 from 表名 [group by 字段1][having 查询条件];
　对查询结果排序

select 字段1,字段2…字段n from 表名 [order by 字段1 asc(正序)/desc(倒序),字段2
asc(正序)/desc(倒序)];

select 字段1,字段2…字段n from 表名 [order by 字段1,字段2 asc(正序)/desc(倒序)];

limit限制查询数量
　指定位置偏移量
　select 字段1,字段2...字段n from 表名 limit 偏移量 显示数据条数;
　select 字段1,字段2...字段n from 表名 limit 显示数据条数 offset 偏移量;
　不指定位置偏移量
　select 字段1,字段2...字段n from 表名 limit 显示数据条数;
范围查询
　select * from 表名 where id between a and b; a=<value<=b
　select * from 表名 where id in(1,3,5); in(a,b,c) 显示含a,b,c的字段
语法结构

select 字段1…字段n [from 表名 [where 查询条件][group by 字段 asc/desc][having
条件][order 字段 asc/desc][limit 显示数据条数/偏移量 显示数据条数]];

—表关系

一对一　主键和主键相关联 foreign key(id) references tb_name(tb_id);
一对多　主键和外键相关联
多对多　外键和外键相关联 primary key(id1,id2);

—表结构修改

添加字段 　　　alter table 表名 add 字段名 字段类型 添加的位置(first/after 字段名);
　修改字段　　 alter table 表名 change 原字段名 新字段名 新字段类型;
　删除字段　　 alter table 表名 drop 字段名;
　修改字段类型 alter table 表名 modify 字段名 新字段类型;
　修改表名 　　alter table 表名 rename 新表名;

表约束

—非空约束

(not null)：添加时需要添加值
　添加
　alter table 原表名 modify 新表名 varchar(20) not null;
　删除
　alter table 原表名 modify 新表名 varchar(20);

—唯一约束

(unique [key])：不重复出现的值
　添加
　alter table 原表名 modify 新表名 varchar(20) unique;
　alter table 表名 add unique(字段名);
　alter table 表名 add constraint 取键名 unique(字段名);
　删除
　alter table 表名 drop key 键名;

—设置主键

(primary key)：非空且唯一
　添加
　alter table 表名 modify 字段名 int primary key;
　alter table 表名 add primary key(字段名);
　alter table 表名 add constraint 取键名 primary key(字段名);
　删除
　alter table 表名 drop primary key;

—设置默认值

(default 值)：定义默认值 之后可不给添加值
　添加
　alter table 表名 modify 字段名 varchar(20) default '默认值';
　alter table 表名 alter column 字段名 set default '默认值';
　删除
　alter table 表名 modify 字段名 varchar(20);
　alter table 表名 alter column 字段名 drop default;

—设置外键

(constraint 外键名 foreign key() references 表名(字段))
　添加
　alter table 表名 add foreign key(字段名) references 表名(字段名);
　删除
　alter table 表名 drop foreign key fk_id;

—设置自增长

(auto_increment=值)
　添加
　alter table 表名 modify 字段名 int increment=值;
　删除
　alter table 表名 modify 字段名 int;
　修改
　alter table 表名 auto_increment=值;

数据类型

—数值类型

数值类型	说明
tinyint	范围(0,255) 占1B
smallint	范围(0,65535) 占1B
mediumint	范围(0,16777215) 占1B
int	范围(0,4294967295) 占1B
bigint	占8b
float(m,n)	m为总个数 n为小数位 占4B
double(m,n)	m为总个数 n为小数位 占8B

—字符类型

字符类型	说明
char(size)	size默认为1 最多固定储存255个字符
varchar(size)	size默认为1 最多储存255个字符 若超过则转化为text类型
tingtext/tinyblob	最多储存255个字符
text/blob	多储存65535个字符
longtext/longblb	最多储存4294967295个字符
enum(“a”,”b”,”c”)	只能插入已列出的数据
set(‘a’,’b’,’c’)	插入已列出数据中的多个

—数据日期类型

数据日期类型	说明
date	储存格式 2019-09-18
time	储存格式 08:00:00
datetime	储存格式 2019-09-18 08:00:00
timestamp	自动储存记录修改的时间
year	储存格式2019

子查询

子查询格式
select * from table1 where col1=(select col2 from table2);
用any some all修饰子查询
　any some all放在比较运算符后面
　any和some表示满足内层子查询中的任意一个条件
　all表示满足内层子查询中的所有条件
in和not in
in(select col from table where 查询条件) 返回一个数据列 将数据列里的值提供外层查询 not in 功能相反
exists和not exists
exists(select * from table where 查询条件);
子查询提供插入的数据
insert into table2 select * from table1 where 查询条件;

运算符和函数

—运算符

运算符	说明
算数运算符	+ - * / %
大小等于判断	> >= < <= = != <>
判断是否为空	is (not) null
between a and b	a=<value<=b
在n1,n2,n3,中选择	(not) in(n1,n2,n3)
通配符匹配	like "_"表示一个字符 "%"表示多个字符
与或 非	and or not

—数值函数

数值函数	说明
ceil(x)	返回大于x的最小整数值
floor(x)	返回小于x的最大整数值
round(x)	返回最接近x的整数
round(x,y)	四舍五入到小数x的小数点后y位
truncate(x,y)	截断到小数x的小数点后的y位
mod(x,y)	返回x被y取余后的余数

round(x,y) 四舍五入到小数x的小数点后y位
　　　eg：
　　　select round(28.55,1)=28.6
　　　select round(28.55,0)=29
　　　select round(28.55,-1)=30
truncate(x,y) 截断到小数x的小数点后的y位
　　　eg：
　　　select truncate(28.55,1)=28.5
　　　select truncate(28.55,0)=28
　　　select truncate(28.55,-1)=20

—字符函数

字符函数	说明
字符拼接
concat(s1,s2,s3)	将s1和s2连接
concat_ws(x,s1,s2)	用x将s1和s2连接
大小写转换
upper(s)	转大写
lower(s)	转小写
大小写转换
ltrim(s)	去左空白
rtrim(s)	去右空白
trim(s)	去左右空白
字符截取
substring(s,n,len)	从s第n个字符开始往后截取len个字符
left(s,n)	获取s左边n个字符
right(s,n)	获取s右边n个字符
字符替换
replace(str,from_str,to_str)	str为字符串 from_str为需要替换的内容 to_str替换成的内容
format(x,n)	保留x小数点后的n位

—时间函数

时间函数	说明
获取当前日期函数	curdate()或current_date()
获取当前时间函数	curtime()或current_time()
获取当前日期和时间函数	now()或sysdate()
时间的加运算函数	date_add(date,interval n type(year month day week))
计算间隔天数函数	datediff(date1,date2) date格式 2018-10-25
日期格式化	fomat(date,"%Y%m")
根据format指定的格式显示date值	date_format(date,format)

date_format(date,format) 根据format指定的格式显示date值
date_format时间日期格式
　　%b:月份的缩写名称(Jan…Dec)
　　%c:月份,数字形式(0…12)
　　%m:月份,数字形式(00…12)
　　%M:月份名称(January…December)
　　%d:该月日期(00…31)
　　%e:该月日期,数字形式(0…31)
　　%Y:四位数表示年份
　　%y:两位数表示年份

—聚合函数

聚合函数	说明
求最大值	max(x)
求最小值	min(x)
求总和	sum(x)
求平均值	avg(x)
求总数	count(x)
列出字段全部值	group_concat(字段)

—加密函数和信息函数

加密函数和信息函数	说明
系统信息函数
查看版本号	version()
当前连接的次数	connection_id()
当前数据库名	database()或schema()
当前登录的用户名	user()
加密函数
信息摘要函数	md5()
密码算法	password()

多表连接查询

自定义函数

自定义结束符
　delimiter //将//作为结束符
定义函数

delimiter //
create function 函数名(参数)
returns 返回值数据类型
语句
return(语句)
end//
delimiter ;

删除函数
　drop function if exists 函数名;
自变量
　定义declare 变量名 变量类型 [default 默认值];
　赋值set 变量=值; 或select 字段 into 变量名 from 表名 where 查询条件;
流程控制
选择语句
if

if 条件 then
else 条件 then
else 条件 then
else
end if

case

方式一
case 变量
when 值 then 语句
when 值 then 语句
else 语句
end case
方式二
case 
when 条件 then 语句
when 条件 then 语句
else 语句
end case

循环语句
while

[label:]while 条件 do
语句
end while[label]

repeat

[label:]repeat 
语句
until 条件
end repeat[label]

loop

[label:]loop 
语句
leave loop
end loop[label]

存储过程

存储过程的介绍
存储过程是SQL语句和流程控制语句的预编译集合，并以一个名称存储并作为一个单元进行处理。
语法结构
参数
in:表示该参数的值必须在调用存储过程时指定
out:表示该参数的值可以被存储过程改变,并且可以返回
inout:表示该参数的调用时指定,并且可以被改变和返回

create procedure proc_name([参数])#参数格式：[in|out|inout] 参数名 类型
过程体

过程体
　过程体由合法的SQL语句构成；
　过程体可以是任意SQL语句；
　过程体如果为复合结构则使用BEGIN…END；
　复合结构可以包含声明，流程控制语句；
　
调用存储过程
含参数调用时call proc_name(参数)
不含参数调用时call proc_name或call proc_name()

例子:

创建存储过程
delimiter //
create procedure proc2(in cid char(18), out num int)
begin
delete from readerinfo where card_id=cid
select count(card_id) into num from readerinfo;
end//
delimiter ;
调用存储过程
select * from readerinfo;
call proc2('210210199901011111',@num);
select @num;

• 未完待续

mysql事务

开启事务start transaction;
提交事务commit;
回滚　　rollback;

MySQL的管理和维护

—数据库用户管理

MySQL用户分为普通用户和root用户
　root用户实超级管理员，拥有所有权限
　普通用户只拥有被授予的各种权限
权限表
　MySQL服务器托管权限表来控制用户对数据库的访问，放在MySQL数据库中。
　存储账户的权限信息表主要有:user,db,host,tables_priv,columns_priv和procs_priv。

mysql内置表	说明
user	表是MySQL中最重要的一个权限表，记录允许连接到服务器的账户信息，里面的权限是全局级的
db	表中存储了用户对某个数据库的操作权限
host	表中存储了某个主机对数据库的操作权限
tables_priv	表用来对表设置操作权限
columns_priv	表用来对表的某一列设置权限
procs_priv	表可以对存储过程和存储函数设置操作权限

—创建和删除用户

创建用户	语法
使用create创建用户	create user 'user'@'host' identified by [password] '密码';
使用grant创建用户	grant privileges on db.table to 'user'@'host' [identified by '密码'];
删除用户
方式一	delete from mysql.user where user='用户名' and host='主机名';
方式二	delete from mysql.user where user='用户名' and host='主机名';

使用create创建用户
　设置明文密码create user '用户名'@'localhost' identified by '密码';
　设置哈希密码create user '用户名'@'localhost' identified by password '密码';

—用户权限管理

用户权限管理	语法
给用户授权	grant 权限类型(insert/delete/update/select) on db.table to '用户名'@'主机名' [identified by password '密码' ];
查看用户权限	show grants for '用户名'@'主机名';
收回权限	revoke 权限类型(insert/delete/update/select) on db.table from '用户名'@'主机名';

—数据库日志管理

MYSQL的日志记录了 MYSQL数据库的运行情况、用户操作、错误
信息等;可以为 MYSQL管理和优化提供必要的信息。

错误日志:记录 MYSQL服务的启动、运行或停止 MYSQL服务时出
现的问题。
查询日志:记录了 MYSQLE的所有用户操作,包括启动和关闭服务
执行查询和更新语句等。
二进制日志:记录所有更改数据的语句。
慢查询日志:记录所有执行时间超过long query_ time的所有查询
或不使用索引的查询。
错误日志名默认为：主机名.err 位置：在mysql的根目录下
查找命令:show variables like 'log_err';

—数据库的备份与导入

备份
　mysqldump -u用户名 -p 数据库名 > 导入的地址\xxx.sql
导入
　mysql -u用户名 -p 数据库名 < 导入的地址\xxx.sql

---

来源:根据51自学网mysql视频教程整理
链接:https://www.51zxw.net/list.aspx?cid=651