四，数据类型和操作符
1.数据类型简介
数值数据类型：TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、FLOAT 、DOUBLE 、DECIMAL 日期/时间类型：YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP 字符串类型：CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET
2.数据类型
1.整数类型
2.浮点型和定点型
3.日期/时间类型
4.字符串类型
5.二进制字符串
3.操作符
1.算数运算符
2.其他运算符
3.逻辑运算符
4.位运算符
五，数据库用户权限（DCL）
一，基本用户权限命令
1.创建用户并赋予权限
mysql> grant all on test.* to zhangsan@'%' identified by '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show grants for zhangsan@'%'; +----------------------------------------------------+ | Grants for zhangsan@% | +----------------------------------------------------+ | GRANT USAGE ON *.* TO 'zhangsan'@'%' | | GRANT ALL PRIVILEGES ON `test`.* TO 'zhangsan'@'%' | +----------------------------------------------------+ 2 rows in set (0.00 sec)
2.撤销删除权限
mysql> revoke drop,delete on test.* from zhangsan@'%'; Query OK, 0 rows affected (0.00 sec)
3.查看权限
mysql> show grants for zhangsan@'%'; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for zhangsan@% | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'zhangsan'@'%' | | GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'zhangsan'@'%' | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
六，数据库查询（DQL）
1.select单独使用
mysql> select @@basedir; #mysql安装目录 mysql> select @@port; #mysql端口号 mysql> select @@innodb_flush_log_at_trx_commit; #日志刷新策略 mysql> show variables like 'innodb%'; #模糊查看innodb开头的配置 mysql> select database(); #查看当前库名 mysql> select now(); #查看当前系统时间 mysql> select @@server_id; #查看本实例id号，群集中不能重复
2.select通用语法
select {字段列表} from <表1>,<表2> [where 表达式] [group by] [having] [order by] [limit <>]
通用符：
七，数据库操作语言（DML）
1.添加数据
给指定字段添加数据
insert into 表名 (字段名1,字段名2) values (值1，值2);
给全部字段添加数据
insert into 表名 values(值1,值2);
批量添加数据
insert into 表名 (字段名1,字段名2) values (值1,值2),(值1,值2),(值1,值2); insert into 表名 values (值1,值2),(值1,值2),(值1,值2);
注意
插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内
2.修改数据
修改数据
update 表名 set 字段=值1,字段2=值2 [where 条件];
3.删除数据
删除数据
delete from 表名 [where 条件]
注意
delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
delete语句不能删除某一个字段的值（可以使用update）
八，MySQL函数
一.数学函数
(1) 绝对值函数：ABS(x) 用于返回 x 的绝对值 (2) 返回圆周率的函数：PI() PI() 返回圆周率值 (3) 平方根函数：SQRT(x) SQRT(x) 返回非负数 x 的二次方根 (4) 求余函数：MOD(x,y) MOD(x,y) 返回 x 被 y 除后的余数，MOD(x,y) 对于带有小数部分的数值也起作用，它 返回除法运算后的精确余数 (5) 获取整数的函数：CEIL(x) 、CEILING(x) 、FLOOR(x) CEIL(x) 用于返回不小于 x 的最小整数值 CEILING(x) 用于返回不小于 x 的最小整数 FLOOR(x) 返回不大于 x 的最大整数值 (6) 获取随机数的函数：RAND() 、RAND(x) RAND() 用于返回一个随机浮点值，范围在 0 ~ 1 之间 RAND(x) 用于返回一个随机浮点值，范围在 0~1 之间，x 被用作种子值，用来产生重复序列 (7) 四舍五入的函数：ROUND(x) 、ROUND(x,y) ROUND(x) 用于对 x 进行四舍五入 ROUND(x,y) 用于对 x 进行四舍五入，并保留小数点后 y 位 (8) 截取数值的函数：TRUNCATE(x,y) TRUNCATE(x,y) 用于对 x 进行截取，结果保留小数点后 y 位 (9) 符号函数：SIGN(x) SIGN(x) 用于返回参数 x 的符号，当 x 为负数时返回 -1 ，当 x 为正数时返回 1 ， 当 x 为 零时返回 0 (10) 幂运算函数：POW(x,y) 、POWER(x,y) 、EXP(x) POW(x,y) 用于返回 x 的 y 次方的结果 (11) 对数运算函数：LOG(x) 、LOG10(x) LOG(x) 返回 x 的自然对数 使用 LOG10(x) 计算以 10 为基数的对数 (12) 角度与弧度相互转换的函数：RADIANS(x) 、DEGREES(x) RADIANS(x) 用于将参数 x 由角度转化为弧度 DEGREES(x) 用于将参数 x 由弧度转化为角度 (13) 正弦函数和反正弦函数：SIN(x) 、ASIN(x) SIN(x) 用于返回 x 的正弦值，其中 x 为弧度值 ASIN(x) 用于返回 x 的反正弦，即正弦为 x 的值 (14) 余弦函数和反余弦函数：COS(x) 、ACOS(x) COS(x) 用于返回 x 的余弦，其中 x 为弧度值 ACOS(x) 用于返回 x 的反余弦，即余弦是 x 的值 (15) 正切函数、反正切函数、余切函数：TAN(x) 、ATAN(x) 、COT(x) TAN(x) 返回 x 的正切，其中 x 为给定的弧 度值 ATAN(x) 用于返回 x 的反正切，即正切为 x 的值 COT(x) 用于返回 x 的余切
二，字符串函数
(1) 计算字符串长度的函数：CHAR_LENGTH(str) 、LENGTH(str) CHAR_LENGTH(str) 用于统计 str 的字符个数 LENGTH(str) 用于统计 str 的字节长度，使用 utf8 编码字符集时，一个汉字是3个字节，一个数字或字母是1个 字节 (2) 合并字符串的函数：CONCAT(s1,s2,...) 、CONCAT_WS(x,s1,s2,...) CONCAT(s1,s2,...) 用于合并字符串 s1, s2, ..... ，如果有一个参数为 NULL，则结果为 NULL CONCAT_WS(x,s1,s2,...) 以 x 作为分隔符，将 s1, s2, .... 合并，如果 分隔符为 NULL ，则结果为 NULL (3) 替换字符串的函数：INSERT(s1,x,len,s2) (1) INSERT(s1,x,len,s2) 用于返回字符串 s1，其子字符串起始于 x 位 置和被字符串 s2 取代的 len 字符 (2) 如果 x 超过字符串长度，则返回值为原始字符串，如果 len 的长度大于其 他字符串的长度则从位置 x 开始替换 (3) 如果任何一个参数为 NULL ，则返回值为 NULL (4) 转换大小写的函数：LOWER(str) 、LCASE(str) 、UPPER(str) 、UCASE(str) LOWER(str) 用于将字符串 str 中的 字母字符全部转换成小写字母 LCASE(str) 用于将字符串 str 中的字母字符全部转换成小写字母 UPPER(str) 用于将 str 中的小写字母转换成大写字母 UCASE(str) 用于将 str 中的小写字母转换成大写字母 (5) 获取指定长度的字符串的函数：LEFT(s,n) 、RIGHT(s,n) LEFT(s,n) 用于返回字符串 s 开始的最左边 n 个字符 RIGHT(s,n) 用于返回字符串 s 中右边 n 个字符 (6) 填充字符串的函数：LPAD(s1,len,s2) 、RPAD(s1,len,s2) LPAD(s1,len,s2) 返回字符串 s1 ，其左边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度 RPAD(s1,len,s2) 返回字符串 s1 ，其右边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值 被缩短至 len 长度 (7) 删除空格的函数：LTRIM(s) 、RTRIM(s) 、TRIM(s) LTRIM(s) 用于删除字符串 s 左侧的空格 RTRIM(s) 用于删除字符串 s 右侧的空格 TRIM(s) 用于删除字符串 s 两侧的空格 (8) 删除指定字符串的函数：TRIM(s1 FROM s) TRIM(s1 FROM s) 用于删除字符串 s 中两端所有的子字符串 s1 ， 如果没有指定 s1 ，则默认删除字符串 s 两侧的空格 (9) 重复生成字符串的函数：REPEAT(s,n) REPEAT(s,n) 用于重复字符串 s ，n 表示重复多少次 (10) 空格函数：SPACE(n) SPACE(n) 用于返回 n 个空格 (11) 替换函数：REPLACE(s,s1,s2) REPLACE(s,s1,s2) 表示使用字符串 s2 替换字符串 s 中所有的字符串 s1 (12) 比较字符串大小的函数：STRCMP(s1,s2) STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的大小，若所有字符串相 同则返回 0 ，若第一个字符串大于第二个字符串则返回 1 ，若第一个字符串小于第二个字符串则返回 -1 (13) 获取子字符串的函数：SUBSTRING(s,n,len) 、MID(s,n,len) SUBSTRING(s,n,len) 用于获取指定位置的子字符 串 MID(s,n,len) 用于获取指定位置的子字符串 (14) 匹配子字符串开始位置的函数：LOCATE(str1,str) 、POSITION(str1 IN str) 、INSTR(str, str1) LOCATE(str1,str) 用于返回字符串 str1 在字符串 str 中的开始位置 POSITION(str1 IN str) 用于返回字符串 str1 在字符串 str 中的开始位置 INSTR(str, str1) 用于返回子字符串 str1 在字符串 str 中的开始位置 (15) 反转字符串的函数：REVERSE(s) REVERSE(s) 用于将字符串 s 反转 (16) 返回指定位置的字符串的函数：ELT(n, s1, s2, s3, .....) ELT(n, s1, s2, s3, .....) 用于返回第 n 个字符串，如果 n 超出范围则返回 NULL (17) 返回指定字符串位置的函数：FIELD(s, s1, s2, .....) FIELD(s, s1, s2, .....) 用于返回字符串 s 在列表 s1, s2, .... 中 的位置，如果不存在字符串 s 则返回 0 ，如果字符串 s 是 NULL 也返回 0 (18) 返回子字符串位置的函数：FIND_IN_SET(s1, s2) FIND_IN_SET(s1, s2) 用于返回字符串 s1 在字符串列表 s2 中的位置
三，日期和时间函数
(1) 获取当前日期的函数：CURDATE() 、CURRENT_DATE() CURDATE() 用于获取系统当前日期 CURRENT_DATE() 用于系统获取当前日期 (2) 获取当前时间的函数：CURTIME() 、CURRENT_TIME() CURTIME() 用于获取系统当前时间 (3) 获取当前日期和时间的函数：CURRENT_TIMESTAMP() 、LOCALTIME() 、NOW() 、SYSDATE() CURRENT_TIMESTAMP() 用于获取系统当前日期和时间 LOCALTIME() 用于获取系统当前日期和时间 NOW() 用于获取系统当前日期和时间 SYSDATE() 用于获取系统当前日期和时间 (4) 获取时间戳的函数：UNIX_TIMESTAMP() UNIX_TIMESTAMP() 用于获取 UNIX 格式的时间戳 (5) 转换时间戳的函数：FROM_UNIXTIME() FROM_UNIXTIME() 用于将 UNIX 格式的时间戳转换为普通格式的时 间 (6) 获取 UTC 日期的函数：UTC_DATE() UTC_DATE() 用于获取当前 UTC (世界标准时间) 日期值 (7) 获取 UTC 时间的函数：UTC_TIME() UTC_TIME() 用于获取当前 UTC (世界标准时间) 时间值 (8) 获取月份的函数：MONTH(date) 、MONTHNAME(date) MONTH(date) 用于返回 date 对应的月份 MONTHNAME(date) 用于返回 date 对应月份的英文全名 (9) 获取星期的函数：DAYNAME(date) 、DAYOFWEEK(date) 、WEEKDAY(date) 、WEEK(date) 、 WEEKOFYEAR(date) DAYNAME(date) 用于返回 date 对应的工作日的英文名称 DAYOFWEEK(date) 用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，...... ，7 表示周六 WEEKDAY(date) 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，...... ，6 表示周日 WEEK(date) 用于计算 date 是一年中的第几周，一年有 53 周 WEEKOFYEAR(date) 用于计算日期 date 是一年中的第几周，一年有 53 周 (10) 获取天数的函数：DAYOFYEAR(date) 、DAYOFMONTH(date) DAYOFYEAR(date) 用于返回 date 是一年中的 第几天，一年有 365 天 DAYOFMONTH(date) 用于计算 date 是一个月中的第几天 (11) 获取年份的函数：YEAR(date) YEAR(date) 返回 date 对应的年份 (12) 获取季度的函数：QUARTER(date) QUARTER(date) 返回 date 对应的一年中的季度值 (13) 获取分钟的函数：MINUTE(time) MINUTE(time) 返回 time 对应的分钟值 (14) 获取秒钟的函数：SECOND(time) SECOND(time) 返回 time 对应的秒数 (15) 获取日期的指定值的函数：EXTRACT(type FROM date) EXTRACT(type FROM date) 用于获取指定的日期值 (16) 时间和秒钟转换的函数：TIME_TO_SEC(time) 、SEC_TO_TIME(time) TIME_TO_SEC(time) 用于将 time 转换 为秒钟，公式为 " 小时3600 + 分钟60 + 秒 " SEC_TO_TIME(time) 用于将秒值转换为时间格式 (17) 计算日期和时间的函数：DATE_ADD() 、ADDDATE() 、DATE_SUB() 、SUBDATE() 、ADDTIME() 、 SUBTIME() 、DATE_DIFF() DATE_ADD() 用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type)，expr 与 type 的关系 DATE_SUB() 用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) ，expr 与 type 的关系 SUBDATE() 用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ，expr 与 type 的关系 ADDTIME() 用于对日期进行加运算，格式为 ADDTIME(date, expr) SUBTIME() 用于对日期进行减运算，格式为 SUBTIME(date, expr) DATE_DIFF() 用于计算两个日期之间的间隔天数 (18) 将日期和时间格式化的函数：DATE_FORMAT(date, format) 、TIME_FORMAT(time, format) 、 GET_FORMAT(val_type, format_type) DATE_FORMAT(date, format) 用于格式化日期，即根据 format 指定的格式 显示 date 值，format 格式 TIME_FORMAT(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值，format 格式 GET_FORMAT() ，我们指定值类型和格式化类型，然后会显示成格式字符串
四.条件判断函数
(1) IF() IF(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2 (2) IFNULL() IFNULL(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2 (3) CASE 语法：CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END 含义：如果 expr 等于某个 vn ，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn
五.系统信息函数
(1) 获取 MySQL 版本号的函数：VERSION() VERSION() 用于获取 MySQL 版本号 (2) 查看当前用户的连接数的ID函数：CONNECTION_ID() CONNECTION_ID() 用于查看当前用户的连接数 1. Id ：用户登录 MySQL 时，系统分配的连接 id 2. User ：当前连接的用户 3. Host ：显示这个语句是从哪个 IP 的哪个端口上发出的，可以用来追踪出现问题语句的用户 3. db ：显示这个进程目前连接的是哪个数据库 4. Command ：显示当前连接执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect) 5. Time ：显示这个状态持续的时间，单位是秒 6. State ：显示使用当前连接的 SQL 语句的状态 7. info ：显示这个 SQL 语句 (3) 查看当前使用的数据库的函数：DATABASE() 、SCHEMA() DATABASE() 用于查看当前使用的数据库 SCHEMA() 用于查看当前使用的数据库 (4) 查看当前登录的用户名的函数：USER() 、CURRENT_USER() 、SYSTEM_USER() USER() 返回当前登录的用户 及主机名 CURRENT_USER() 用于返回当前登录的用户及主机名 SYSTEM_USER() 用于返回当前登录的用户及主机名 (5) 查看指定字符串的字符集的函数：CHARSET(str) CHARSET(str) 用于查看字符串 str 的字符集 (6) 查看指定字符串的排列方式的函数：COLLATION(str) COLLATION(str) 用于查看字符串 str 的字符排列方式 (7) 获取最后一个自动生成的 ID 值得函数：LAST_INSERT_ID() LAST_INSERT_ID() 用于获取最后一个自动生成的 ID 值
六.加密/解密函数
(1) 加密函数：PASSWORD(str) 、MD5(str) 、ENCODE(str, pswd_str) PASSWORD(str) 从明文密码 str 计算并返 回加密后的密码字符串，当参数为 NULL 时，返回 NULL MD5(str) 为字符串 str 算出一个 MD5 128 比特校验和 ENCODE(str, pswd_str) 使用 pswd_str 作为密码，加密 str (2) 解密函数：DECODE(crypt_str, pswd_str) DECODE(crypt_str, pswd_str) 使用 pswd_str 作为密码，解密加密字 符串 crypt_str
七.其他函数
(1) 格式化函数：FORMAT(x, n) FORMAT(x, n) 将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果 以字符串的形式返回 (2) 不同进制的数字进行转换的函数：CONV() CONV() 用于不同进制数之间的转换 (3) IP 地址与数字互相转换的函数：INET_ATON(expr) 、INET_NTOA(expr) INET_ATON(expr) 用于将网络地址转 换为一个代表该地址数值的整数 (4) 加锁函数和解锁函数：GET_LOCK(str, timeout) 、RELEASE_LOCAK(str) 、 IS_FREE_LOCK(str) 、 IS_USED_LOCK(str) GET_LOCK(str, timeout) 使用字符串 str 来得到一个锁，持续时间 timeout 秒 1. 若成功得到锁，则返回 1 2. 若操作超时，则返回 0 3. 若发生错误，则返回 NULL RELEASE_LOCAK(str) 用于解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁 1. 若锁被解开，则返回 1 2. 若该线程尚未创建锁，则返回 0 3. 若命名的锁不存在，则返回 NULL 4. 若该锁从未被 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在 IS_FREE_LOCK(str) 检查名为 str 的锁是否可以使用 1. 若锁可以使用，则返回 1 2. 若锁正在被使用，则返回 0 3. 若出现错误，则返回 NULL IS_USED_LOCK(str) 用于检查名为 str 的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识 符，否则返回 NULL (5) 重复执行指定操作的函数：BENCHMARK(count, expr) BENCHMARK(count, expr) 用于重复 count 次执行表 达式 expr 1. 可以用于计算 MySQL 处理表达式的速度 2. 可以在 MySQL 客户端内部报告语句执行的时间 (6) 改变字符集的函数：CONVERT(... USING ...) CONVERT(... USING ...) 用于改变字符串的默认字符集 (7) 改变数据类型的函数：CAST(x, AS type) 、CONVERT(x, type) CAST(x, AS type) 用于将一个数据类型的值转换 为另一个数据类型的值 CONVERT(x, type) 用于将一个数据类型的值转换为另一个数据类型的值
MySQL常见报错
1.MySQL忘记密码
解决1：
systemctl stop mysqld mysqld_safe --skip-grant-tables & #跳过权限表 mysql grant all on *.* to root@localhost identified by '123456'; #会报错 flush privileges; grant all on *.* to root@localhost identified by '123456'; exit killall -9 mysqld_safe systemctl start mysqld
解决2：
1.在[mysqld]下添加skip-grant-tables，保存即可。
mysql stop vi /etc/my.cnf skip-grant-tables
2.登录，修改密码
mysql -u root -p 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功） update user set authentication_string=password(‘新密码’) where user=‘root’; flush privileges;
3.重启mysql解决
mysql start
