ps:此随笔基于mysql 5.7.*版本。
准备
- net start mysql
启动MySQL服务 - net stop mysql
关闭MySQL服务 - mysql [-h<IP地址>] [-p<端口>] -u<用户名> -p<密码>
连接MySQL - exit/quit
退出MySQL
注:MySQL的提示符是:mysql> 。MySQL命令结束使用“;”或是“\G”。
已知root账户密码进行登录
格式:mysql [-h地址] [-p端口] -u用户名 -p密码
省略不写地址或端口则自动使用默认。(地址:localhost;端口:3306)
两种方式进行登录。方式1:
方式2:
忘记root账户密码进行登录(修改root密码)
-
以管理员身份打开一个cmd。
-
检查MySQL服务是否启动,若已启动则先将其停止服务,输入命令:net stop mysql,回车。
-
输入命令:E:,回车,再次输入:cd MySQL\MySQL Server 5.7\bin,切换至mysql安装目录的bin目录下。输入命令:mysqld --defaults-file="E:\MySQL\MySQL_Data\my.ini" --console --skip-grant-tables,回车。
该命令通过跳过用户权限安全检查,开启MySQL服务,这样连接MySQL时,可以不用输入密码。此时已经开启MySQL服务,保留这个cmd窗口。
注:这里笔者修改了MySQL配置文件my.ini中basedir和datadir各自的路径。
- 同样使用管理员身份,另起一个cmd窗口,尝试连接MySQL。输入命令:mysql -uroot -p,回车,随后出现:Enter password:,这里不用输入密码,直接回车,出现登录成功的信息。
-
执行命令来重置root账户密码:update mysql.user set authentication_string=password('新密码') where user='root';,回车。
-
刷新权限。输入命令:flush privileges;,回车。
-
然后退出,重新登录。输入命令:quit或exit,回车。
-
此时关闭之前的cmd窗口。执行命令来启动MySQL服务:net start mysql,回车。
-
再次尝试连接mysql。输入命令:mysql -uroot -p,回车。出现输入密码提示,输入新密码即可登录。
- 至此,忘记root账户密码问题解决。
数据库操作
- show databases;
查看所有的数据库,等同于select schema_name from information_schema\G;(注:\G以纵向 报表的形式输出结果,有利于阅读) - create database <数据库名>;
创建数据库 - use <数据库名>;
使用该数据库 - select database();/status;
查看当前使用的数据库 - drop database <数据库名>;
删除数据库 - alter database 数据库名 character set <字符编码>;(注:修改完数据库字符集,需要重启MySQ数据库)
修改数据库字符集
表操作
注:操作表之前应使用“use <数据库名>;”进行连接。
- show tables [from <按数据库名>];
查看指定数据库中包含的表 - desc/describe <表名>;
显示当前数据表的结构 - show create table <表名>;
查看建表时的SQL语句 - create table <表名> (<字段名1> <类型1> [字段约束] [,...<字段名n> <类型n> [字段约束]]);
创建表 - create table <表名> like <已有表名>;
创建与当前表相同结构的新表 - rename table <旧表名> to <新表名>;
重命名数据表 - alter table <表名> add <新字段名1> <字段类型> [default] [默认值] [字段约束] [first|after] [已有字段] [,...add <新字段名n> <字段类型> [default] [默认值] [字段约束] [first|after] [已有字段]];(注:不加[first|after]的位置说明时,默认添加新字段到最后一列。若添加first位置说明,则在第一列添加新字段;若添加after位置说明,则在指定字段的下一列进行添加)
增加一列/多列字段 - alter table <表名> change <旧字段名> <新字段名> <新字段类型> [新约束];
修改字段名称 - alter table <表名> modify <字段名> <新类型> [新约束] [first|after] [已有字段];(注:若不加新约束,则会将建表时的约束清空,主键、外键和唯一约束除外)
修改字段类型及约束 - alter table <表名> engine=innodb|myisam|memory|archivem
修改数据库引擎 - alter table <表名> add primary key(字段名);
增加主键 - alter table <表名> drop primary key;
删除主键 - alter table <表名> add constraint <外键约束名> foreign key(字段名) references <主表>(主键)
增加外键 - alter table <表名> drop foreign key(外键约束名);
删除外键 - alter table <表名> drop index <字段名>;
删除唯一约束 - alter table <表名> drop column <字段名1/列名1> [,...drop column <字段名n/列名n>]
删除单个/多个列 - drop table <表名>;
删除表 - insert into <表名> [(<字段名1> [,...<字段名n>])] values (值1[,...值n]) [,...(值1[,...值n])];
向表中插入一条/多条数据 - delete from <表名> where <表达式>;
删除表中满足条件的数据 - delete from <表名>;/truncate table <表名>;
删除表中所有数据(注:前者会逐条进行删除,速度较慢;后者直接删除表中所有的行,速度快) - update <表名> set <字段名1=新值1> [,...字段名n=新值n] where <表达式> [order by ASC|DESC] [limit ...]
修改表中一条/多条数据 - select * from <表名>
查询表中所有数据 - select [distinct] <字段名1> [,...字段名n] from <表名1> [,...表名n] where <表达式> [group by <字段>] [having <表达式>] [order by ASC|DESC] [limit [偏移量,] <行记录数>]
查询表中满足指定条件的数据
用户和权限管理
- create user '用户名'@'IP地址|域名|%|localhost' [identified by '密码'];(注:%代表通配符,允许客户端以任何IP访问,localhost代表只能通过本机进行登录。如果既有字母,又有数字,则不进行匹配(防止恶意访问))
单独创建用户 - grant <具体权限|all privileges> on <[数据库名.] 表名> to '用户名'@'IP地址|域名|%|localhost' [identified by '密码'] [with grant option];(注:具体权限包括select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file权限中的一种或多种。all privileges代表所有权限。with grant option表示用户可以将自己的权限分配给其他用户)
授权(存在用户则直接授权,否则创建该用户并授权) - select user();
查看当前登录用户 - show grants;
查看当前用户权限 - show grants for '用户名'@'IP地址|域名|%|localhost';
查看某个用户的权限 - rename user '旧用户名'@'IP地址|域名|%|localhost' to '新用户名'@'与前者相同';
重命名用户 - upadte mysql.user set authentication_string=pasword('新密码') where user='用户名' and host='IP地址|域名|%|localhost';
修改用户密码 - flush privileges;
刷新权限 - revoke <具体权限|all privileges> from <[数据库名.] 表名> to '用户名'@'IP地址|域名|%|localhost';
撤销用户权限 - drop user '用户名'@'IP地址|域名|%|localhost';
删除用户
注:每当调整权限之后需要使用flush privileges;命令刷新更改的权限设置
查询数据库运行状态
- show status like '%Aborted_connects%';
查询由于客户端尝试连接,而被MySQL拒绝的连接次数 - show status like '%Threads_connected%';
查询当前打开的连接数量 - show status like 'Qcache%';
查询缓存情况 - show variables like 'log_bin';
查询是否启用binlog日志
备份与恢复
- mysqldump [-h<IP地址>] -u<用户名> -p<密码> <数据库名> > <保存路径/文件名.sql>
备份单个数据库 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> <数据库名> <表名1> [,...表名n] > <保存路径/文件名.sql>
备份单个数据库中的一个/多个数据表 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> --databases <数据库名1> [,...数据库名n] > <保存路径/文件名.sql>
同时备份多个数据库 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> --all-databases > <保存路径/文件名.sql>
备份所有数据库 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> [数据库名] < <保存路径/文件名.sql>(注:如果备份文件中包含创建数据库语句,则不需要指定数据库名;否则需要指定数据库名)
还原单个数据库 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> [数据库名 表名1,...表名n] < <保存路径/文件名.sql>
还原单个数据库中的一个/多个数据表 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> --databases [数据库名1,...数据库名n] < <保存路径/文件名.sql>
还原多个数据库 - mysqldump [-h<IP地址>] -u<用户名> -p<密码> --all-databases < <保存路径/文件名.sql>
还原所有数据库
内置函数
数学函数
- ABS(x):绝对值
- SIGN(x):返回x的符号,x是负数、0、整数分别返回-1、0、1
- CEIL()/CEILING(x):向上取整
- FLOOR(x):向下取整
- ROUND(x):四舍五入取整
- MOD(x,y):求余操作(x mod y等同于x%y)
- LOG(x):返回自然对数(以e为底的对数)
- LOG10(x):返回以10位为底的对数
- PI():获取圆周率
- POW(x,y):乘方操作(x^y)
- SQRT(x):返回x的平方根
- RAND():返回0~1的随机数
- TRUNCATE(x,y):返回数值x保留到小数点后y位的值(与ROUND最大的区别是不会进行四舍五入)
- SIN(x):正弦值(参数是弧度)
- COS(x):余弦值(参数是弧度)
- RADIANS(x):将角度转换为弧度
- DEGRESS(x):将弧度转换为角度
日期时间函数
- NOW()/CURRENT_TIMESTAMP()/LOCALTIME()/SYSDATE()/LOCALTIMESTAMP():返回当前日期和时间
- CURDATE/CURRENT_DATE():返回当前日期
- CURTIME()/CURRENT_TIME():返回当前时间
- TIME_TO_SEC(t):将时间t转换为秒
- SEC_TO_TIME(t):将以秒为单位的时间s转换为时分秒的格式
- DATEDIFF(D1,D2):计算日期d1->d2之间相隔的天数
- ADDDATE(d,n):计算起始日期d加上n天的日期
- SUBDATE(d,n):日期d减去n天后的日期
- ADDDATE(d,INTERVAL expr type):计算起始日期d加上一个时间段后的日期
- SUBDATE(d,INTERVAL expr type):日期d减去一个时间段后的日期
字符串函数
- CHAR_LENGTH(s):返回字符串s的字符数
- CONCAT(s1,s2,...):将字符串s1,s2等多个字符串合并为一个字符串
- CONCAT_WS(x,s1,s2):同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x(x作为分隔符)
- UPPER(s)/UCAASE(s):将字符串s的所有字母变成大写字母
- LOWER(s)/LCASE(s):将字符串s的所有字母变成小写字母
- LEFT(s,n):返回字符串s的前n个字符
- RIGHT(s,n):返回字符串s的后n个字符
- TRIM(s):去掉字符串s开始和结束处的空格
- TRIM(s1 FROM s):去掉字符串开始出和结尾处的字符串s1
- REPEAT(s,n):将字符串s重复n次
- REPLACE(s,s1,s2):将字符串s2替换字符串s中的字符串s1
- STRCMP(s1,s2):比较字符串s1和s2
聚合函数
- COUNT():用来统计记录的条数
- SUM():求和
- AVG():求平均值
- MAX():求最大值
- MIIN():求最小值
条件判断函数
- IF(expr,v1,v2):如果表达式expr成立,返回结果v1;否则,返回结果v2
- IFNULL(v1,v2):如果v1的值不为NULL,则返回v1;否则,返回v2
- CASE
系统信息函数
- VERSION():返回数据库的版本号
- USER()/SYSTEM_USER()/SESSION_USER()/CURRENT_USER()/CURRENT_USER:返回当前用户
- CONNECTION_ID():返回服务器的连接数
其他常用函数
- BIN(x):返回x的二进制编码
- OCT(x):返回x的八进制编码
- HEX(x):返回x的十六进制编码
其他
- show warnings;
查看SQL执行时的警告
文章评论