常用 sql 语句整理

创建用户及授权

CREATE USER `用户名`@`主机地址` IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON `库名`.`表名` TO `用户名`@`主机地址`;
注:
01 主机地址决定该用户可以在在哪个主机上可以登陆,如"%",则可以从任意地址登陆数据库,这在需要远程连接数据库时很有用
02 当需要授权具体权限的时候,可以把"ALL PRIVILEGES"换成"select/delete/update"

查看 MySQL 用户权限

show grants for "用户名"@"主机地址";

删除用户

DROP USER '用户名'@'主机地址';

创建/删除数据库

CREATE DATABASE `库名` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
DROP DATABASE `库名`;
注:此处创建数据库时还指定了字符集为"utf8mb4",字符序为"utf8mb4_general_ci",实际使用中,具体需求具体分析

插入单行

insert [into] <表名> (列名) values (列值);
例:

insert into Strdents (姓名,性别,出生日期) values ('张三','男','1980/6/15');

将现有表数据添加到一个已有表

insert into <已有的新表> (列名) select <原表列名> from <原表名>;
例:

insert into tongxunlu ('姓名','地址','电子邮件') select name,address,email from Strdents;

直接拿现有表数据创建一个新表并填充

select <新建表列名> into <新建表名> from <源表名>;
例:

select name,address,email into tongxunlu from strdents;

删除满足条件的行

delete from <表名> [where <删除条件>];
例:

delete from Strdents where name='张三';

删除整个表

truncate table <表名>;

truncate table tongxunlu;
注意:删除表的所有行,但表的结构、列、约束、索引等不会被删除;不能用于有外建约束引用的表

update <表名> set <列名=更新值> [where <更新条件>];
例:

update Strdents set 年龄=18 where 姓名='张三';

查询所有数据行和列

例:select * from Strdents;
说明:查询 Strdents 表中所有行和列

查询部分行列 -- 条件查询

例:select i,j,k from a where f=5;
说明:查询表 a 中 f=5 的所有行,并显示 i,j,k 3列

在查询中使用 as 更改列名

例:select name as 姓名 from a where xingbie='男';
说明:查询 a 表中 性别 为 男 的所有行,显示 name 列,并将 name 列改名为(姓名)显示

查询空行

例:select name from a where email is null;
说明:查询表 a 中 email 为空的所有行,并显示 name 列;SQL语句中用 is null 或者 is not null 来判断是否为空行

在查询中使用常量

例:select name, '唐山' as 地址 from Student;
说明:查询表 Student,显示 name 列,并添加 地址 列,其列值都为 '唐山'

查询返回限制行数(关键字:top percent)

例1:select top 6 name from a;
说明:查询表 a,显示列 name 的前6行,top 为关键字
例2:select top 60 percent name from a;
说明:查询表 a,显示列 name 的 60%,percent 为关键字

查询排序(关键字:order by , asc , desc)

例:select name from a where chengji>=60 order by desc;
说明:查询 a 表中 chengji 大于等于 60 的所有行,并按 降序 显示 name 列;默认为 asc升序

使用 like 进行模糊查询

注意:like 运算符只用于字符串,所以仅与 char 和 varchar 数据类型联合使用

例:select * from a where name like '赵%';
说明:查询显示表 a 中,name 字段第一个字为 赵 的记录

使用 between 在某个范围内进行查询

例:select * from a where nianling between 18 and 20;
说明:查询显示表 a 中 nianling 在 18 到 20 之间的记录

使用 in 在列举值内进行查询

例:select name from a where address in ('北京','上海','唐山');
说明:查询表 a 中 address 值为 北京 或者 上海 或者 唐山 的记录,显示 name 字段

使用 group by 进行分组查询

例:select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名) from score (注释:这里的score是表名) group by studentID;
说明:在表 score 中查询,按 strdentID 字段分组,显示 strdentID 字段和 score 字段的平均值;select 语句中只允许被分组的列和为每个分组返回的一个值的表达式,例如用一个列名作为参数的聚合函数

使用 having 子句进行分组筛选

例:select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)
from score (注释:这里的score是表名) group by studentID having count(score)>1;
说明:接上面例子,显示分组后 count(score)>1 的行,由于 where 只能在没有分组时使用,分组后只能使用 having 来限制条件

在 where 子句中指定联接条件

例:select a.name,b.chengji from a,b where a.name=b.name;
说明:查询表 a 和表 b 中 name 字段相等的记录,并显示表 a 中的 name 字段和表 b 中的 chengji 字段

在 from 子句中使用 join…on

例:select a.name,b.chengji from a inner join b on (a.name=b.name);
说明:同上

数据库状态查询

show status like '%max_connections%';
//查询 mysql 最大连接数
set global max_connections=1000
//重新设置 mysql 最大连接数
show variables like '%max_connections%';
//查询数据库当前设置的最大连接数
show global status like 'Max_used_connections';
//服务器响应的最大连接数
show status like 'Threads%';
//查询线程池状态
Variable_name Value
Threads_cached 0
//mysql 管理的线程池中还有多少可以被复用的资源
Threads_connected 152
//打开的连接数
Threads_created 550
//表示创建过的线程数,如果发现 Threads_created 值过大的话,表明 MySQL 服务器一直在创建线程,这也是比较耗资源,可以适当增加配置文件中 thread_cache_size 值
Threads_running 1
//激活的连接数,这个数值一般远低于 connected 数值,准确的来说,Threads_running 是代表当前并发数
show variables like 'thread_cache_size';
//查询线程池缓存大小
set global thread_cache_size=60;
//设置线程池缓存大小

查询 mysql 主从状态

mysql -u用户名 -p密码 -e "show slave status\G;"
Slave_IO_Running: Yes
//与主机的 io 通信状态
Slave_SQL_Running: Yes
//自己的 slave mysql 进程状态
Seconds_Behind_Master: 0
//和主库比同步延迟的秒数
注:重点关注以上三项

mysqllinuxsql增删改查主从复制

我来吐槽

*

*