MySQL 语句

SQL语句

mysql客户端命令:

mysql> help
\q #退出
\G #格式化查询
\T tee #记录日志 例 mysql> tee /tmp/b.log 基于session,全局记录需加入配置文件[client]中
\c #取消 
\s status #查看状态信息
\. source #导入数据 例如:mysql>source /root/mysql.sql
\u use #切换数据库
system #mysql中调去系统命令

mysqladmin客户端命令:

mysqladmin -uroot -pecho password('new_pasword') #修改密码
mysqladmin -uroot -pecho ping #查看主机是否存活
mysqladmin -uroot -pecho status #查看mysql状态信息,偏向于写脚本监控
mysqladmin -uroot -pecho shutdown #关闭mysqld
mysqladmin -uroot -pecho variables #查看mysql参数(都可以写到配置文件中)
mysqladmin -uroot -pecho drop test1 #直接删库
mysqladmin -uroot -p create test1 #直接创建库
mysqladmin -uroot -p reload #刷新缓存主机 重建授权表
mysqladmin -uroot -p flush-log #刷新日志

SQL语句

什么是sql语句? 结构化查询语句

开发规范:库名小写

DDL:数据定义语言(create drop alter)

ps:需要接(database 库名 或者 table 表名) 例:alter table student

#对库操作
CREATE DATABASE [IF NOT EXISTS] <库名> [charset=8tf8mb4]

create database test1 charset=utf8 default collate= utf8_general_ci;
alter database test default character set utf8 collate utf8_general_ci;#创建数据库
drop database test1;    #删除数据库
show databases;         #查看数据库列表
show create database test1; #查看创建数据库时的SQL语句
create database test1 charset utf8; #创建数据库添加属性
alter database test1 charset utf8;  #修改数据库

#对表操作
use test;
 #创建表
create table [if not exists ] `student4` (
  `id` int(11) not null  auto_increment comment '学号',
  `name` varchar(10) not null comment '姓名',
  `age` tinyint(3) unsigned comment '年龄',
  `sex` enum('f','m','other') not null default 'other' comment '性别',
  `date` datetime not null default NOW() comment '入学时间',
  primary key (`id`)
)engine=InnoDB auto_increment=1 default charset=utf8;
 
#复制表（复制表结构和表数据）
create table <新表名> select * from <表名>；
#复制表结构
create table <新表名> like <表名>；
create table <新表名> select * from <表名> where 1=2；
#删除表
drop table student
#修改表引擎
alter table student engine= Innodb;
#修改表
alter table student4  add  address varchar(50) not null comment '家庭住址'; #添加一个字段默认在最后
alter table student3  add  address varchar(50) not null comment '家庭住址' first ; #在最前面添加一个字段
alter table student3 add address varchar(50) not null comment '家庭住址' after sex ; #在指定字段后插入
alter table student3 drop address; 删除字段
alter table student4  add  address varchar(50) not null comment '家庭住址',add tel int not null comment '电话号码'; #插入多个字段
alter table student4 rename student; #修改表名
alter table student modify tel int unsigned; #修改字段  只修改类型，用modify
alter table student change id uid int;        #修改字段 既更改列名也更改类型，用change 
alter table student discard tablespace; # 删除表空间
alter table student import tablespace; # 导入表空间

DCL:数据控制语言(grant revoke)

#授权用户
grant all on *.* to test@'172.16.1.%' identified by '123' #非超级管理员
grant all on *.* to test@'172.16.1.%' identified by '123' with grant option #授权超级管理员
grant select(所要授权的字段名) on `库名`.`表名` to 用户名@'主机域' identified by '密码'  #单列级别授权: 
# 其他参数
max_queries_per_hour 一个用户每小时可发出的查询数量
max_updates_per_hour 一个用户每小时可发出的更新数量
max_connections_per_hour 一个用户每小时可连接到服务器的次数
max_user_connections 允许同时连接数量
# 只允许test@'172.16.1.%'用户同一时间1人连接
grant all on *.* to test@'172.16.1.%' identified by 'echo' with max_user_connections 1 ;


#撤回权限
revoke select on *.* from test@'172.16.1.%';
revoke update(权限) on hanjy.*(库名.表名) from hanjy@'%'(收回哪个用户的权限); #收回指定用户的指定权限

DML数据操作语言 (insert update delete truncate)

update和delete 一定要接where

#插入数据
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
insert into student(name,age,sex,address,tel) values ('test1','18','m','山东青岛XXXX',12711111111); #插入单条数据
insert into student(name,age,sex,address,tel) values ('test1','18','m','山东青岛XXXX',12711111111),('test2','19','f','山东青岛XXXX',13712345678); #插入多条数据

#更新数据
update student set tel='13712345678' where name='test'; #修改数据

#删除数据
delete from student where id=3; #删除数据

#清空表
truncate student;

#update代替delete做伪删除
alter talbe student add status enum('1','0') default 1; #在表最后添加state列 1代表有,0代表无
update student set status='0' where id=3; #伪删除

DQL数据查询语言(Data Query Languge)

基本结构是由 select 子句， from 子句， where 子句组成的查询块

# select基础用法
select name,district,countrycode from city; #常规用法
select name,countrycode from city; #查询单列
select * from student where status=1; #查询时加上where条件 
  #行级查询
select name,district,countrycode from city  limit 10; #查询前十行
select name,district,countrycode from city  limit 10.2; #从第10行开始查询两行
select name,district,countrycode from city where countrycode ='CHN' #条件查询
select name,district,countrycode from city where countrycode ='CHN' and name='shanghai'; #多条件查询
select name,district,countrycode from city where countrycode like '%H%' limit 10.2; #模糊查询
# select 查询
select name,district,countrycode from city; #常规用法
select name,countrycode from city; #查询单列
  #行级查询
select name,district,countrycode from city  limit 10; #查询前十行
select name,district,countrycode from city  limit 10.2; #从第10行开始查询两行
select name,district,countrycode from city where countrycode ='CHN' #条件查询
select name,district,countrycode from city where countrycode ='CHN' and name='shanghai'; #多条件查询
select name,district,countrycode from city order by countrycode  limit 10; #排序顺序查询
select name,district,countrycode from city order by countrycode  desc limit 10; #排序倒序查询
select name,district,countrycode from city where id<=10; #范围查询(>,<,<=,>=,<> )
select name,district,countrycode from city where id<=10 or countrycode='CHN'; #范围查询OR
select name,district,countrycode from city where countrycode in ('CHN','USA'); #范围查询in
select name,district,countrycode from city where id between 2 and 5; #范围查询between and 从2~5


SELECT Continent,count(name) FROM country GROUP BY Continent; 
SELECT Continent,count(name) FROM country GROUP BY Continent ORDER BY COUNT(name) ;
SELECT Continent,count(name) FROM country WHERE Continent IN ('asia','africa','oceania') GROUP BY Continent HAVING COUNT(Continent)BETWEEN 30 AND 60 ;

注意:
GROUP BY子句可以包含任意数目的列。也就是说可以在组里再分组，为数据分组提供更细致的控制。
如果在GROUP BY子句中指定多个分组，数据将在最后指定的分组上汇总。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除了聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
如果分组列中有NULL值，则NULL将作为一个分组返回。如果有多行NULL值，它们将分为一组。
GROUP BY子句必须在WHERE子句之后，ORDER BY之前。
例:
   select * from 表名 [where] [group by] [having] [order by]
   

#select高级用法 连表查询
select t1.name,t2.score from t1,t2 where t1.id=t2.id and name='bgx' ; #传统连接(笛卡尔积)(只能内连接取交集)

#natural join 自连接(表要有共同的字段名称,且数据要相同)
select t1.name,t2.score from t1 natural join t2 where name='test1';

#join on 内连接 (使用join语句时尽量小表在前,大表在后)
select t1.name,t2.score from t1 join t2 on t1.id=t2.id where t1.name='test1';

#union all 不去重合并 union去重合并 合并查询(相对于in or 条件查询效率高)
select id,name from t1 where id=1 union all select id,name from t1 where id=3;  
select name from t1 where name='test' union  select name from t1 where name='test1'; #去重合并


# 外连接(左连接,右连接)
select t1.id,t1.name,t2.score from t1 left join t2 on t1.id=t2.id and  t2.score<60;   #左连接后的检索结果是显示t1的所有数据和t2中满足条件的数据
select t1.id,t1.name,t2.score from t1 right join t2 on t1.id=t2.id and  t2.score<60;  #右连接后的检索结果是显示t2的所有数据和t1中满足条件的数据

内连接,左连接右连接运算方式

1、内连接：取交集即数据集c
2、左外连接：其运算方式为：t1左连接t2的记录=公共部分记录集C＋t1表记录集A1。
左连接后的检索结果是显示t1的所有数据和t2中满足条件的数据
3、右外连接：其运算方式为：t1右连接t2的记录=公共部分记录集C＋t2表记录集B1。
右连接后的检索结果是显示t2的所有数据和t1中满足条件的数据

show语句

mysql参数和变量查看参考:https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

show databases ; #显示所有数据库名称
show tables; (show tables from database;) #显示某个库内的所有表名称
show columns from table_name;  (desc table_name )  #显示表中列名称 
show engines; #显示所有引擎和默认引擎
show status; show status like 'com_select'; #显示一些系统特定资源的信息
show table status;(show table status from database) #显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
show privileges; #显示MySQL所支持的不同权限
show create database database_name; #显示创建数据库时的语句
show create table table_name; #显示创建表时的语句
show errors; #只显示最后一个执行语句所产生的错误
show warnings; #显示最后一个执行的语句所产生的错误、警告和通知
show grants; #显示当前用户权限语句
show grants from user_name; #显示指定用户权限语句
show index from table_name; #显示指定表索引
show character set; #显示所有字符集和效验规则
show variables; #显示系统变量的名称和值
show variables like '%iso%'; #显示当前隔离级别
show variables like '%engine%'; #显示默认引擎
show variables like 'version'; #显示版本
show variables like 'port'; #显示端口号
show variables like 'transaction_isolation'; #查看事务隔离级别
show variables like 'datadir'; #显示默认存储路径
show variables like 'autocommit'; #显示自动提交状态
show variables like 'max_connections'; #显示最大连接数 set global max_connections=1000 临时设置最大连接数
show variables like '%per_table%'; #查看独立表空间是否开启
show variables like '%path%';#查看共享表空间
show variables like 'character%'; #查看数据库编码格式
show variables like 'collation%'; #查看校验规则
show variables like '%gen%' #查看常规日志状态
show variables like '%log_err%' #查看错误日志状态
show variables like 'binlog_format' #查看binlog模式
show variables like 'log_bin' #查看binlog是否开启
show master logs | show binary logs #显示二进制日志文件
show master status #查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置
show processlist; #查看正在执行的sql语句
show slave status\G #从属服务器线程的关键参数的信息