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
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
