MySQL语句与指令
注：sql语句不区分大小写，多条sql语句用分号( ; )分隔。
以下columnName代表列名，tableName代表表名。
命令
基本
1、mysql -u用户名 -p mysql登录-u为用户名 -p为密码
2、use 数据库名； 使用某个数据库。
3、show table； 展示此数据库中所有的表；
4、desc 表名； 查看此表的表结构；
新建用户
1、查看现有用户
mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.01 sec)
2、新建用户
格式：create user "username"@"host" identified by "password";
样例：create user 'test'@'localhost' identified by '123';
create user 'test'@'192.168.7.22' identified by '123';
create user 'test'@'%' identified by '123';
注：host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录
mysql> create user 'zcq'@'localhost' identified by '15284377'; Query OK, 0 rows affected (0.03 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | | localhost | zcq | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | +-----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
3、删除用户
格式：drop user 'username'@'host';
注：host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录
mysql> drop user 'zcq'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
4、授权
格式：grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';
（1）. GRANT命令说明：
priveleges(权限列表),可以是all priveleges, 表示所有权限，也可以是select、update等权限，多个权限的名词,相互之间用逗号分开。
on用来指定权限针对哪些库和表。
*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。
to 表示将权限赋予某个用户, 如 jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
identified by指定用户的登录密码,该项可以省略。
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。
（2）.授权原则说明：
权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：
a、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
b、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
c、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
d、为每个用户设置满足密码复杂度的密码。
e、定期清理不需要的用户。回收权限或者删除用户。
/*授予用户通过外网IP对于该数据库的全部权限*/ grant all privileges on `test`.* to 'test'@'%' ; /*授予用户在本地服务器对该数据库的全部权限*/ grant all privileges on `test`.* to 'test'@'localhost'; grant select on test.* to 'user1'@'localhost'; /*给予查询权限*/ grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/ grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/ grant update on test.* to 'user1'@'localhost'; /*添加权限*/ flush privileges; /*刷新权限*/
5、查看权限
show grants;
//查看某个用户权限
show grants for 'jack'@'%';
mysql> show grants for 'zcq'@'localhost'; +-----------------------------------------------+ | Grants for zcq@localhost | +-----------------------------------------------+ | GRANT USAGE ON *.* TO `zcq`@`localhost` | | GRANT SELECT ON `test`.* TO `zcq`@`localhost` | +-----------------------------------------------+ 2 rows in set (0.00 sec)
6、删除权限
revoke privileges on databasename.tablename from 'username'@'host';
例：
（1）//删除此用户在test_db表中所有权限
revoke all on test_db.* from 'test_user'@'localhost';
（2）//删除用户指定（select）权限
revoke select on test.* from 'cxn'@'localhost';
mysql> show grants for 'cxn'@'localhost'; +-----------------------------------------------+ | Grants for cxn@localhost | +-----------------------------------------------+ | GRANT USAGE ON *.* TO `cxn`@`localhost` | | GRANT SELECT ON `test`.* TO `cxn`@`localhost` | +-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke select on test.* from 'cxn'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'cxn'@'localhost'; +-----------------------------------------+ | Grants for cxn@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO `cxn`@`localhost` | +-----------------------------------------+ 1 row in set (0.00 sec)
7、更改用户名
rename user 'jack'@'%' to 'jim'@'%';
mysql> rename user 'zcq'@'localhost' to 'cxn'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'zcq'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'zcq' on host 'localhost' mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | cxn | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | +-----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
8、修改密码
（1）.用update直接编辑user表
update mysql.user set authentication_string=password("new password") where User="username" and Host="localhost";
9、查看用户
select * from mysql.user where user='用户名'；
mysql> select * from mysql.user where user='cxn'; +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ | localhost | cxn | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | mysql_native_password | *5CAC74FFF456F77AF6F4B5F45014B01048925F1A | N | 2020-06-16 10:07:17 | NULL | N | N | N | NULL | NULL | NULL | NULL | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ 1 row in set (0.00 sec)
10、刷新权限
flush privileges;
11、导出权限配置
mysql导出数据需要两个方面的权限，系统权限file，即使你是all privieges 权限，也要单独给一个file权限，否则你是导出不了数据的
（1）、file 权限，次权限是全局的，不能针对某个数据库
grant file on *.* to zabbix@'72.7.99.133';
（2）、文件系统安全
show variables like 'secure_file_priv';
secure_file_priv=NULL 没有导出权限
secure_file_priv=/data/tmpfile 只能在制定路径导出数据
secure_file_priv='' 可以在任意路径导出数据
修改配置可修改mysql配置文件，查看是否有
vi my.cnf 添加
secure_file_priv = ''
表示不限制目录，等号一定要有，修改完配置文件后，重启mysql生效
此参数是静态参数，必须重启数据库
语句
查询
5、SELECT * FROM tableName； 查看从此表中的所有数据。
6、SELECT columnName1，columnName2 FROM tableName; 从此表中查看指定列的数据。
7、SELECT DISTINCT columnName FROM tableName； 从此表中查看指定列的不同值的数据。
注：DISTINCT关键字指示数据库返回不同的值。DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。
8、SELECT columnName FROM tableName ORDER BY columnName DESC； 查看此表中指定列数据并按照指定列来进行排序。 其中DESC为降序，ASC为升序，如果不写默认升序。
9、WHERE条件子句
| WHERE子句操作符 | |||
| 操作符 | 说明 | 操作符 | 说明 |
| = | 等于 | > | 大于 |
| != | 不等于 | >= | 大于等于 |
| < | 小于 | <= | 小于等于 |
| BETWEEN | 在两个指定值之间 | IS NULL | 为NULL值 |
| IS NOTNULL | 不为NULL值 | NOT | 否定后边条件 |
例：SELECT * FROM tableName WHERE columnName = “xxxxx”；
SELECT * FROM tableName WHERE columnName > 10；
SELECT * FROM tableName WHERE columnName IS NULL；
SELECT * FROM tableName WHERE columnName BETWEEN 5 AND 10；
SELECT * FROM tableName WHERE columnName NOT BETWEEN 5 AND 10；
注：多个筛选条件可以用AND和OR来进行连接，SQL在处理OR时会优先处理AND，注意用( )来进行限制。
10、IN操作符
IN操作符用来指定条件范围，范围中每个条件都可以进行匹配。
SELECT * FROM tableName WHERE columnName IN (“xxxxx”,”xxxxx”)；
11、通配符
使用LIKE操作符加上通配符可以实现模糊搜索。
%通配符，表示任意字符出现任意次数，%不会匹配到NULL值。
SELECT * FROM tableName WHERE columnName LIKE “%xxx%”；
_通配符，用途和%一样，但是只能匹配到单个字符，并非多个
SELECT * FROM tableName WHERE columnName LIKE “_xxx”；
[ ]通配符，用来指定一个字符集，它必须匹配指定位置的一个字符
SELECT * FROM tableName WHERE columnName LIKE “[XX]”；
12、字符串拼接
SELECT CONCAT( columnName1，“（”，columnName2，“）”) FROM tableName；、
注：CONCAT将方法内的所有字符串进行拼接。
13、算数计算
SQL支持进行加减乘除的计算
SELECT columnName1，columnName2，columnName1*columnName2 AS columnName3 FROM tableName;
注：AS为别名
14、文本处理函数
常用文本处理函数
函数名 说明
LEFT（） 返回字符串左边的字符
LENGTH（） 返回字符串的长度
LOWER（） 将字符串转换为小写
LTRIM（） 去掉字符串左边的空格
RTRIM（） 去掉字符串右边的空格
TRIM（） 去掉字符串左右两边的空格
RIGHT（） 返回字符串右边的字符
UPPER（） 将字符串转换为大写
例：SELECT UPPER(columnName1)，LOWER(columnName2) FROM tableName;
15、日期与时间的处理函数
SELECT * FROM tableName WHERE YEAR( columnDate) = ‘2019’;
16、聚集函数
SQL聚集函数
函数 说明
AVG（） 返回某列的平均值
COUNT（） 返回某列的行数
MAX（） 返回某列的最大值
MIN（） 返回某列的最小值
SUM（） 返回某列之和
例：SELECT AVG（columnName1）AS columnName2 FROM tableName;
注：MAX（）、MIN（）、SUM（）忽略列值为NULL的行。
17、SELECT COUNT（*） FROM tableName； 对表中行的数目进行计数，不管列中包含的是空值NULL还是非空值
SELECT COUNT（columnName） FROM tableName； 对指定列中有值的行进行计数。
18、DISTINCT不能用于COUNT( * )，但是能用于MAX（）和MIN（）。
SELECT MIN（DISTINCT columnName1）AS columnName2 FROM tableName;
19、SELECT * FROM tableName GROUP BY columnName; 创建分组进行查询
20、SELECT * FROM tableName GROUP BY columnName1 HAVING columnName2=‘xx’; 通过HAVING进行分组过滤来查询。WHERE过滤指定的行不是分组，因此需要用HAVING。
21、SELECT子句顺序
SELECT子句及其顺序
子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按钮计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
22、子查询
例：SELECT * FROM tableName1 WHERE columnName = （SELECT columnName FROM tableName2）；
SELECT （SELECT columnName1 FROM tableName1 ）AS columnName2，columnName3 FROM tableName2；
可以进行多层嵌套。
23、联结表查询
SELECT columnName1，columnName2 FROM tableName1，tableName2 WHERE tableName1.columnName3 = tableName2.columnName3;
表可以定义别名
SELECT columnName1，columnName2 FROM tableName1 a，tableName2 b WHERE a.columnName3 = b.columnName3;
24、高级联结
自联结：SELECT columnName1，columnName2 FROM tableName1 WHERE tableName1.columnName3 = （SELECT columnName3 FROM tableName1 WHERE columnName4 = “xxx”）;
内联结：SELECT tableName1.columnName1，tableName2.columnName2 FROM tableName1 INNER JOIN tableName2 ON tableName1.columnName3 = tableName2.columnName3;
外联结：SELECT tableName1.columnName1，tableName2.columnName2 FROM tableName1 RIGHT OUTER JOIN tableName2 ON tableName1.columnName3 = tableName2.columnName3;
注：在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表。
25、组合查询
可以用UNION操作符来组合数条SQL查询。UNION从查询结果集中自动去除了重复行。如果要返回所有匹配行，可使用UNION ALL。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句后。
例：SELECT columnName1，columnName2 FROM tableName WHERE columnName1 IN (“xxxxx”,”xxxxx”)
UNION
SELECT columnName1，columnName2 FROM tableName WHERE columnName2 =“xxxx”
ORDER BY columnName3 desc；
插入
1、插入数据
INSERT INTO tableName（columnName1，columnName2，columnName3）VALUES （“xxxx”，“xxxx”，“xxxx”）；
2、插入检索出的数据
INSERT INTO tableName1（columnName1，columnName2，columnName3）SELECT columnName1，columnName2，columnName3 FROM tableName2；
3、从一个表复制到另一个表
SELECT * INTO tableName FROM tableName2；
更新
UPDATE tableName SET columnName1 = ‘xxx’，columnName2=‘xxx’ WHERE columnName3 = ‘xxx’；
删除列
DELETE FROM tableName WHERE columnName = “xxx”；
创建
1、创建表
CREATE TABLE tableName
(
columnName1 INTEGER NOT NULL DEFAULT ‘1’ COMMENT ‘xxx’,
columnName2 CHAR(20) NOT NULL DEFAULT NULL COMMENT ‘xxx’,
columnName3 DECIMAL(8,2) NOT NULL DEFAULT NULL COMMENT ‘xxx’,
columnName4 TIMESTAMP NOT NULL DEFAULT CURRENT_ TIMESTAMP COMMENT ‘xxx’,
columnName5 TIMESTAMP NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE TIMESTAMP COMMENT ‘xxx’,
PRIMARY KEY(columnName1)
)；
注：DEFAULT为默认值 COMMENT为备注 CURRENT_ TIMESTAMP为当时的时间戳 CURRENT_ TIMESTAMP ON UPDATE TIMESTAMP为每次更新此行数据都默认更改为当时时间。
2、更新表
添加列
ALERT TABLE tableName ADD columnName CHAR(20) NOT NULL DEFAULT NULL COMMENT ‘xxx’；
移除列
ALERT TABLE tableName DROP COLUMN columnName；
删除表
DROP TABLE tableName；
索引
CREATE INDEX indexName ON tableName columnName；
浙公网安备 33010602011771号