mysql 结束符报错_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.idselect * 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;