mysql 常用命令及常用函数整理 （超全）

cls        清屏
数据库基本操作命令

mysqld –install mysql157        需要在mysql安装位置中的bin目录下（如果没有添加环境变量, ）必须以管理员权限打开cmd
mysqld –remove mysql157        需要在mysql安装位置中的bin目录下（如果没有添加环境变量）必须以管理员权限打开cmd
services.mcs    不可以在mysql中打开
mysqld --initialize-insecure        初始化产生data文件(insecure是显示空密码)(一次就行)
mysql -uroot -p        可以产生多个客户端（打开多个cmd文件输入这句话）（输入status        查看connection id会发现不同）
status        查看状态
select version();        查看版本
delimiter: 命令结束符号自定义        \d aaa
quit、exit        退出

一、远程连接
1、（客户端只需要一个mysql.exe文件即可）主机解压 mysql5.7.25.zip /bin/mysql.exe 放到c:根目录下（这个目录可以随便设置）
2、查看虚拟机的ip: cmd ipconfig
3、虚拟机 mysql -uroot -p 
mysql> select user,host,authentication_string from mysql.user;
mysql> rename user root@localhost to root@'%';
mysql> select user,host,authentication_string from mysql.user;
4、虚拟机关闭防火墙， 控制面板-》防火墙-> 打开或关闭防火墙
       5、主机上cmd执行（在mysql.exe的目录下）mysql -uroot -p –h（虚拟机ip地址）（虚拟机的服务要处于开启状态）

 

二、创建桥接
windows 7 x64        设置桥接模式        ipconfig
show processlist        显示当前的进程(删除进程用kill (id) 输入status两次会自动重连并分配不同的id)
dir        显示当前目录下的文件
cd:change directory
show databases;        显示数据库
use db_name;        进入数据库
show tables;        显示表当前数据库下的所有表
desc t_name;        显示表中的列
select * from tb_name ;显示所有的行
select * from tb_name\G        当表中数据太多会分行显示出来
select user,host,authentication_string from mysql.user;        进入表格一般用户存在这里
create user zhang@'%' identified by 'zhang'        创建用户zhang密码也是zhang
set password for user@’host’= ‘xxxxxx’;        修改用户密码（需要是root用户才可以改任意用户的密码）
update mysql.user set password=password(‘password’) where clause;        需要重新将授权表加载到内存中，才能生效(flush privileges;)
skip-grant-tables        (跳过授权，如果忘记密码在my.ini中输入这句话)
set password=’xxxx’        只能改自己的
select password(‘zhang’);        查询zhang的密码
mysql –uzhang –p –h129.168.5.128        在客户端输入回车后并将密码输入进行登录（这是一个普通用户用show databases;无法显示根目录下的文件（因为没有权限））
grant select on blog.* to zhang@’%’;        给zhang添加权限让其能够显示blog的所有
revoke select on blog.* form zhang@’%’;        给zhang删除权限让其不能够显示blog的所有
SQL语句分类
DDL(data definition language)：数据定义语言 create(创建), drop（删除）, alter（改变）        与列相关（针对的组件基本为数据库  数据表...... ）
DML(data manipulation language: 数据操作语言 insert、 delete, update, select        增删改查（与行相关）
DCL (data control language)：数据控制语言 grant, revoke (授权和撤销授权).....
\?          （相当于HELP）
\c          是清除（写错时用（若是三引号则需打个’再加\c））
create database [if not exists] db_name;        创建数据库
show warnings;        显示告警
show charset;        显示支持的字符集
drop database db_name        删除数据库
show engines;        显示引擎没啥用了解一下
drop table t_name;        删除表
ESC        光标删除至最左侧
F7        之前操作过的sql命令列表

insert into t_name(col_name1,col_name2) values(val1,val2);        向表中插入数据
select password(‘xxxx’);        查看密码的编码
prompt xxx>        默认提示符（建议添加一个系统的环境变量：新建一个环境变量变量名：MSQL_PS1变量值：\u@\h[\d]>）
mysql –uroot –p –h192.168.5.128  -e “show databases;use mysql;show tables;”>d:\test.txt        不用进入客户端直接在操作系统中就能对数据库进行操作(>及以后是将输出结果放在test文件中重定向符)
source d:\test.txt        执行test文件中的命令（\.也行）

数据类型：1、字符型
            变长字符        varchar()
            定长字符        char()
            内奸类型        enum(),set()

        2、数值型
            整形        int
            十进制        decimal(10,3)

        3、日期型        一定要带引号
            当前时间        now()
            日期        data: ‘2019-12-02’
            时间        time: ’12-34-23’
            日期和时间    datetime当前服务器时间
            时间戳        timestamp        时区时间
            年份        year(2),year(4):两位或四位前者不长用
            时间区域        set time_zone = “+9:00”;        时间戳用

        4、修饰符
            not null        非空
            null        空
            default        默认值( default ‘unknown’)
            unsigned        无符号位
            auto_increment        自增长（必须加主键）
            primary key        主键（不能不填，不能有重值）
            unique key        (不能不填，可以重值)

DDl:数据定义语言（CREATE  DROP  ALTER）
    create table [if not exists] tb_name (col1 type1,col2 type2)        创建表
    create table t_name as select *from t_name1;        复制表（约束key无法复制过来（通过desc查看））
    create table t_name like t_name1;        结构相同但是数据无法复制过来（用inert into t_name select *from t_name1;把数据复制过来）（like后面只能跟单个表名，不能跟查询结果集）
    show table status\G        显示当前数据库下所有表的状态
    show table status like ‘t_name[%]’        显示具体某个表的状态信息（或者是模糊查询）
    drop table [if exists] t_name;        删除表
    alter table t_name rename new_name;        改表名
    alter table t_name add col_name col_type;        添加字段
    alter table t_name drop col_name;        删除字段
    alter table t_name change col_name new_name col_type;        改变字段名
    alter table t_name modify col_name col_type;        改变字段属性
    alter table t_name add index idx_name(col_name);        添加索引
    show index from t_name;        查看表索引
    alter table t_name drop index idx_name;        删除索引

DMl:数据操作语言（insert、delete、update、select）
    insert into t_name [(col1,col2)] values(v1,v2);        插入数据(可插入多条在后面加,(v3,v4))
    delete from t_name;        删除所有行 
    delete from t_name where clause;        删除对应行    
    update t_name set clo_name=value where clause;        修改某个值
    select col1,col2,col3 from t_name;        查询表中的列
    order by convert(sname using gbk)        汉字通过国标卡排序
    select col1,col2,... from t_name [where clause] [order by ‘col_name’ [desc]] [limit [m,]n];

字段表示法：
*：所有字段
as：字段别名,col1 as alias1        as可以不写
order by
排序查找（默认升序） 降序接desc
limit
字符限制查找（只显示其中部分内容），可以偏移限制查找。)
Limit 3        三列
limit 0,3        0-3列
Clause where 条件表示：
字段后接比较式（除等于，其他仅局限于数值型）
> ,  = ,  < ,  >= ,  <= ,  !=
条件逻辑操作
And            而且
Or           或者
Not           非
between ... and ...(相当于>=和<=之间）
in            在里面
空值比较或匹配
is null               (子句内元素为空)
is not null           (子句内元素为非空)
like        （模糊匹配）
%:        任意长度任意字符
_：        任意单个字符

truncate table t;        （会回收空间）（截短的意思）DDL
delete from x1;(        不会回收空间)DML
drop table x1;        直接表这个文件没了DDL
insert into t_name select * from t_name;        自插

DCl:数据控制语言（GRANT   REVOKE）
grant all on db_name.t_name to user@’host’;        为用户设置权限
show grants        ;查看权限
show grants for user@’host’;        查看指定用户权限（要在root中查看）
revoke all on db_name.t_name from user@’host’;         为用户删除权限
错误日志        在data中god-PC.err
运用虚拟机的快照进行当前状态的保存        虚拟机        快照        拍摄快照        快照管理        点击快照        转到
show variables like ‘log_error’;        显示错误日志的地址
索引：        一般返回数据3%-5%效果最好
create table t_name(index idx_name(col_name));        创建表格时指定
alter table t_name add\drop index idx_name(col_name)        添加删除索引
drop index idx_name on t_name;        删除索引
explain select * from t_name where col_name= col_value;        显示一个表某行的相关信息
show index from t_name;        显示表中的索引
show keys from t_name;        查看索引
show indexes from t_name;        查看索引
模糊查询时必须用左前缀索引：例如like ‘abc%’
索引使用总结
1.优点：
* 加速查询，条件返回记录数占总行数3%-5%
2.缺点：
* 占用额外磁盘空间
* insert变慢
* update delete 可能变慢，也可能变快（依赖索引定位update delete的行）
3.使用条件：
* 根据业务特性，在经常作为查询条件的列上适当加上索引
* 不要每一列都加索引，因为索引会随着表行数据的变化自动更新（占系统资源）
* MySQL官方建议，每个表都要有一个主键
外键：
alter table t_name add foreign key(col_name) reference r_t_name(col_name)        在表中某一列创建一个外键，参照参照表的某一列，必须要在参照表中的那一列中设置一个主键
show create table t_name\G        显示创建这个表的信息（constraint后面写的是外键）

 

视图：
1、简化查询
2、提高安全性
create view v_name as t_name;        创建一个视图
show table status\G        显示表的状态（视图后面全是null）
grant privilege on db_name.t_name to user@’host’;        将视图交给某个用户看（保证安全性）
insert into v_name values(v1,v2、、、);        视图与母表(基表)发生变化时会互相发生变化
show create table v_name;        显示创建视图的步骤
DML高级查询：
select col1,... from clause ---> where clause ---> group by ---> having---> order by ---> limit写法顺序
select distinct col_name from t_name;        去重
select col_name [as] ‘alias’ from t_name;        给列添加别名
select (1+5.3)*15;        支持算术运算
select col_name+col1_name from t_name;        支持列相加
col_name between v1 and v2;        范围（或者用in(v1-v2)）
count(*)        显示数量
sum(*)        求和
avg(*)        求平均值
max(*)        最大值
min(*)        最小值
select group.concat(col_name) from t_name;        将某一列转换成字符串

group by col_name,col_name1,col_name2        按某多列列来分组
having clause        写在group by 后面
select 1 col_name;        给列赋值
select * from t_name1 alias1 join t_name2 alias2;         多表查
询中的交叉连接：笛卡尔积（最消耗资源，效率极低）

select * from t_name1 alias1 [left/right] join t_name2 alias2 on alias1.col_name1=alias2.col_name2;        多表查询中的等值连接
select a.*,b.id id1,b.name name1 from team a join team b on a.id!=b.id order by a.id;        多表查询中的不等值连接（在足球双循环赛中用）
select a.*,b.id id1,b.name name1 from team a join team b on a.id<b.id order by a.id;        多表查询中的不等值连接（在足球单循环赛中用）
select * from t_name where weight>(select col_name from t_name);        子查询
select * from (select * from t_name clause) alias clause;        套表查询（必须给套表起别名）
select col_name1 from t_name1 union select col_name2 from t_name2;        复合查询需要列相同

事物：        myisam引擎不支持事物（钱回不来了）    
begin;/start transaction;
        SQL1
        SQL2…
commit;/rollback;
show variables like ‘autocommit’;        显示自动提交变量的状态
set autocommit=0/off;        关闭autocommit(只对当前会话有用)
set global autocommit=0/off;        关闭autocommit(对所有会话有用（当前会话不会改变）)

mysql数据备份和恢复：
show variables like‘log_bin’’;        显示二进制日志（需要在my.ini写入log_bin=mysql-bin和 server-id=1两句话，并重新启动服务器，会发现在data出现mysql-bin.0000001的两个文件，不能用文本编辑器查看）
cd \mysql-5.7.25-winx64\data        进入data目录
dir        显示目录
mysqlbinlog mysql-bin.0000001        查看日志
mysqldump         –uroot         –p        --databases        db_name db_name1>c:\bak\mybak.txt将数据库拷贝到一个地址（必须在c盘根目录下一定要创建一个文件夹，否则会拒绝访问）(想要远程备份添加-h)
source c:\bak\mybak.txt;如果删除了数据可以用source对备份的数据进行恢复
mysqldump         –uroot        –p        – alll-databases >c:\bak\mybak.txt备份所有的数据库(想要远程备份添加-h)
mysqldump        –uroot        –p        db_name t_name1 t_name2> c:\bak\mybak.txt;        倒入数据库中的某些表(想要远程备份添加-h)

 

存储过程：（建议在txt中编写）
语法：
\d 标识符修改命令结束符是为了防止；产生的错误
      CREATE PROCEDURE proc_name()
      BEGIN
      ....
      ....
      END 标识符
\d ;
调用存储过程call proc_name()

#存储过程，录入班级信息
\d $$
create procedure insert_class(in_cno int, in_cname varchar(50), in_teacher varchar(50))
begin
    insert into class values(in_cno,in_cname,in_teacher);
end$$
\d ;

#存储过程，删除班级信息
\d //
create procedure delete_class(in_cno int)
begin
        delete from class where cno=in_cno;
end//
\d;

#存储过程，查询班级信息
\d //
create procedure show_stu(in_cno int)
begin
   select * from stu where cno=in_cno;
end//
\d;

数值函数：
ABS()        返回数值的绝对值     （案例的数值例如x）
CEIL()        返回不小于x的最小整数
FLOOR()        返回不大于 x 的最大整数(与CEIL的用法刚好相反)
MOD(x,y)         返回数字x除以y后的余数：x mod y   相当于%取模
ROUND(X[,Y])         将数字X四舍五入到指定的小数位数Y
FLOOR(i + RAND() * (j - i))        获得一个从i到j之间的随机整数
字符串函数：

日期函数：
YEAR(date)               获得年份
MONTH(date)              获得月份
DAY(date)                获得天
HOUR(date)              获得小时
MINUTE(date)            获得分钟
SECOND(date)             获得秒
NOW()           当前日期和时间
ADDDATE(date,x)             获得x天后的日期
SUBDATE(date,x)          获得x天前的日期
DATEDIFF(date1,date2)                获得2个日期之间的天数
自定义函数：
\d 标识符
    CREATE FUNCTION function_name         (参数列表)
        RETURNS         返回值类型
        BEGIN
        routine_body         (必须要有return)
END 标识符
\d ;
例1：输入生日返回年龄：
\d $$
create function get_age(in_birth date) returns decimal(8,2)
begin
   return(select round(datediff(now(),in_birth)/365,2));
end$$
\d;
select sname,birth,get_age(birth) age from stu;
select sname,birth,get_age(birth) age from stu where get_age(birth)>25;

例2：输入班号返回班名
\d $$
create function get_cname1(in_cno int) returns varchar(50)
begin
  declare v_cname varchar(50);
  select cname into v_cname from class where cno=in_cno;
  if v_cname is not null then
      return v_cname;
  else
      return '无班';
  end if;
end$$
\d;

触发器：
示例1：删除班级表中1班的数据，级联删除学生表中1班的学生：
\d $$
create trigger t_delete_stu before delete on class for each row
begin
  delete from stu where cno=old.cno;
end$$
\d ;
begin;delete from class where cno=1;
示例2：创建用户和黑名单表，
用户表：用户编号，手机号，余额；
黑名单表：用户编号，手机号。
如果话费余额不足10元，把用户编号和其手机加入到黑名单表中；如果用户充值后，余额超过10元，把其信息从黑名单表中移除。
\d $$
create trigger t_oper_blacklist before update on users for each row
begin
  if new.balance<10 then
     insert into blacklist values(old.id,old.phone);
  end if;
  if new.balance>=10 then 
     delete from blacklist where id=old.id;
  end if;
end$$
\d;

update users set balance=balance-25 where phone='18520151234';
select * from users;
select * from blacklist;

update users set balance=balance+100 where phone='18520151234';
select * from users;
select * from blacklist;