mysql常用语句

最新推荐文章于 2024-07-24 13:35:03 发布

itoldwei

最新推荐文章于 2024-07-24 13:35:03 发布

阅读量318

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/dhjywe/article/details/107545908

版权

              MySQL的常用语句

1 MySQL基础管理
1.1 用户权限管理
1.1.1 用户的定义
linux：用户名字表示用户，例如：guangtouqiang
MySQL：用户名@‘白名单’ ,例如：root@‘localhost’
白名单：在白名单出现的ip,网段，主机名域名，才能登录数据库
例子：guangtouqiang@‘localhost’ guangtouqiang@’%’ guangtouqiang@‘10.0.1.%’
guangtouqiang@‘10.0.0.51’ guangtouqiang@‘10.0.0.0/255.255.254.0’
guangtouqiang@‘10.0.0.5%’ 表示主机ip为5几的地址50~59
规范： 1.用户名不能以数字开头
2.不能是保留字段（比如：字符）
3.用户名要和业务有关
1.1.2用户的作用
Linux：登陆系统，管理系统中的对象（文件，目录。。。）
MySQL：登录数据库，管理数据库对象（库，表。。。）
1.1.3用户的管理命令

第一个：创建用户
         mysql>create user  guangtouqiang@'10.0.0.%';
         mysql>create user  guangtouqiang@'10.0.0.%' identified  by '123456';
第二个：查询用户
         mysql>select  user,host,authentication_string  from  mysql.user;
第三个：修改用户
         mysql>alter user  guangtouqiang@'10.0.0.%'  identified  by  '56789';
第四个：删除用户
         mysql>drop user guangtouqiang@'10.0.0.%';

1.2权限管理
1.2.1 权限的定义
Linux：rwx
MySQL（8.0版本之前）：增删改查等
MySQL（8.0版本之后）：有了角色功能，可以赋予一类用户某几个权限
1.2.2 权限的作用
控制用户，能够对数据库对象做哪些操作
1.2.3 授权
grant 权限 on 对象 to 用户 identified by ‘密码’；
grant all on . to wordpress@‘%’ identified by ‘123456’；
说明：以上命令只能8.0以前使用，如果用户不存在，会自动创建，并且能够设置密码；
8.0+版本，必须先建用户，再授权，同时创建用户和修改密码修改密码功能取消；
例子：

 （1）创建和授权一个管理员用户，你能够通过10.0.0.%网段管理数据库
    grant all on *.*  to guangtouqiang@'10.0.0.%' identified by '123456' with  grant option;
 （2）授权一个业务用户oldboy，能够通过10.0.0.1%网段访问oldboy库下的所有表。
    grant select  on guangtouqiang.*  to oldboy@'10.0.0.1%' identified by  '123456';

1.2.4 查询权限
show grants for guangtouqiang@‘10.0.0.%’;
1.2.5 回收权限
revoke grant option on . from guangtouqiang@‘10.0.0.%’;
MySQL权限级别介绍
●MySQL权限级别
●全局性的管理权限，作用于整个MySQL实例级别
●数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上
●数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或
者所有的数据库对象上
●权限存储在mysq|库的user, db, tables_ priv, columns_ priv, and
procs_ priv这几个系统表中，待MySQL实例启动后就加载到内存中
user: 存放创建的用户和密码包括全局实例级别管理权限设置
db：存放设置的数据库级别的权限设置
tables_ priv：存放表级别的权限设置
columns_ priv：存放字段级别的权限设置
procs_ priv：存放存储过程中的权限设置
2.SQL-DD类应用
2.1 DDL-针对库的操作
第一个：建库
mysql>create database [if not exixts] guangtouqiang;中括号内容可加可不加；或者mysql> create schema guangtouqiang;同上，俩种方法
第二个：查看设置字符集及校对
mysql>shou charset;
字符集：utf8mb4 utf8
校对规则(排序规则)：mysql>show collation;
utf8mb4_general_ci （大小写不敏感）默认
utf8mb4_bin （大小写敏感）
创建数据库：mysql>create database weizai charset utf8mb4;(一般建库都要加字符集)
第三个：修改库（主要修改字符集和校对规则）
mysql>alter database weizi charset utf8;
注意：修改的字符集必须是修改前的严格超集（意思是修改后的字符集范围超过了修改前的字符集，并且没有交叉集）
第四个：查看库（不属于DDL）
mysql>show create database weizi;

注意：建库规范：
（1）库名是小写（2）不能是数字开头（3）限制在16个字符以内（4）库名和业务有关（5）必须要加字符集（6）不能用预留字符（如tab键）

2.2 DDL针对表的管理
2.2.1表定义
（1）数据类型

数字类型
tinyint:1字节，-128~127，0-255
int:4字节，-2^31~231-1,0-2^32-1
bigint:8字节，0_2^64-1,-2^632^63-1
字符类型
！！！：可存储的最大字符数和字符编码有关，如当采用utf8编码时，一个数字或字母占1个字节，一个汉字占3个字节；一个中文汉字相当于3个字节
char(M):
char(10):最多存储10个字符（无论中英文：即可以存储10个英文字母，也可以存储10个中文），
属于定长字符类型，不管你实际使用多少，只要存入内容，就为他分配10个字符的存储空间
M：M的值最多为255个字符

varchar(M):
varchar(10):最多存储10个字符（无论中英文：即可以存储10个英文字母，也可以存储10个中文）
M值最多为65535字节，若换成中文需要65535/3=21845
属于变长字符类型：按实际存储要求分配磁盘空间
每次存储数据时，都要计算一下数据大小，然后分配空间，并且需要单独存储字符长度

char与varchar怎么选择
小于255，并且固定，长度的列，选择char
大于255，或者边长，长度的列，选择varchar

资料扩展：
1）char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据

2）在使用UTF8字符集的时候，MySQL手册上是这样描述的：
基本拉丁字母、数字和标点符号使用一个字节；
大多数的欧洲和中东手写字母适合两个字节序列：扩展的拉丁字母（包括发音符号、长音符号、重音符号、低音符号和其它音符）、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言；
韩语、中文和日本象形文字使用三个字节序列。

enum（‘m’,‘f’）:枚举类型
时间类型
datatime ：8字节储值范围 1000-01-01 00：00：00~ 9999-12-31 23：59：59
timestamp：4字节 1970- ？？？ 2038？？？受到时区影响
二进制类型

（2）约束
PRIMARY KEY:主键约束，表中只能有一个，非空且唯一
NOT NULL :非空约束，不允许空值
UNIQUE KEY :唯一键约束，不允许重复值
DEFAULT :一般配合 NOT NULL一起使用
UNSIGNED :无符号，一般是配合数字列，非负数
COMMENT:注释说明
（3）表属性
存储引擎
字符集

2.2.2 建表（表名，列名，列属性，表属性）
建表

create table old like student; 只克隆表结构，不复制其中内容
create table user select * from mysql.user; 数据和结构都克隆，索引不会被克隆
建表规范：
表名要和业务有关
表名不能有大写
表名不能有数字开头
表必须设置存储引擎
表必须设置数据类型
选择合适并简短的数据类型
每个表要有主键，一般可以无关列
每个列设置非空约束，设置默认值
每个列和表要有注释
查看表结构
mysql>desc student; (查看表结构)
mysql>show create table student; （查看建表的具体内容）
删除表
mysql>drop table student; 删除表后，其中的内容和结构都被删除2

修改表（一般修改的是其结构）

例子：在student表加一列qq号
alter table student add qq varchar(10) not null default 0 comment qq号；
desc student；
注意：表结构变更，会长时间锁表，会影响线上工作，需要在不繁忙期间或者使用pt-osc工具；
8.0版本以后就解决了这个问题。
例子：在qq号之后加上wechat列
alter table student add wechat varchar(10) not null default 0 comment 微信号 after qq ；
例子：在首列添加idcard列
alter table student add idcard varchar(10) not null default 0 comment 身份证号 first；

删除列（锁表）
alter table student drop qq;

修改列属性（都会锁表）
alter table student modify wechat varchar(20) not null comment 姓名;
ALTER TABLE student change NAME sname VARCHAR(64) NOT NUlL COMMENT 姓名;
注意：change既可以改名字，又可以改列属性；
modify只能改列属性；
如果想要改变列某个属性时，修改前的列属性都会被覆盖，所以想改变某个列属性时，需要在列名后加上所有需要的属性（例如红色的部分，如果不加，则修改后的列只有varchar列属性，其他的没了）。

清空表中的数据（危险）
truncate table student；

3.DCL语句
grant
revoke

4.DML语句（表中的数据行）
(1) insert
语法结构
insert into 表名 valuse(v1,v2,v3…)
规范写法
insert into student(idcard,age,tel)
values
(123，13，110),
(124，24，120);
select * from student; 查看表中的内容

插入json数据类型

（2）update 一般后面都加where条件
语法结构：
update 表 set 列=’’ where 条件；
update student set age=20 where name=‘张三’;
（3）delete （逐行删除）一般后面都加where条件
语法结构：
delete from 表 where 条件
delete from student where name=‘张三’；
（4）伪删除
用状态列标记一个行是否存在
update 替代 delete

添加一个状态列（1代表删除，0代表未删除）
alter table student add state char(1) not dull default 0 comment ‘状态码：1代表删除，0代表未删除’；
删除张三数据行
原语句：delete from student where name=‘张三’；
改写为：update student set state=‘1’ where name=‘张三’；
查询数据
原语句：select * from student;
改写为:select * from student where state=‘0’;

5，DQL应用
5.1 select 查询单表中的数据行
5.1.1 单独使用（MySQL）
（1）select @@xxxx —>用来查询MySQL中设定的参数（例如my.cnf）

select  @@port;
select  @@datadir;
select  @@server_id;
select  @@innodb_flush_log_at_trx_commit;

如果忘了参数可以用下面命令查询
show variables; 显示所有的参数
show variables like ‘%trx%’;
(2) select 查询函数；
如果当前忘了在操作哪个数据库，可以使用select database();
select now(); —>查询当前时间；
select concat(“hello”) —>可以将括号内的内容打在屏幕上；
（3）select 计算功能
select 3+5；

5.1.2 select 配合各种子句使用
各子句的执行顺序
select 列1，列2，列3。。。
from 表1 表2 .。。
where 条件1 条件2
group by
having
order by
limit

（1）select 配合 from 子句的使用
例子：第一个：查询user表中所有的数据
select * from city；（生产中谨慎使用）

例子：第二个：查询user表中 user 和host列所有的值
select user，host from user；

（2）select 配合 where 子句使用
select 列… from 表 where 条件；
条件：where 的< ，>， <= ，>=， != ， <> ， and ， or ， like（模糊查询）

例子：查询city表中，中国所有的城市信息
select   *  from  city  where  countrycode='CHN';
例子：查询人口数小于100的城市
select  *  from  city  where  population<100;
例子：查询中国，人口数量大于500万的城市
select  *  from  city  where   countrycode='CHN' and  population>5000000;
  例子：查询中国和美国的所有城市信息
select  * from  city   where  countrycode='CHN'  or   countrycode='USA';
  例子：查询ch开头的国家的城市信息
select  *  from  city  where  countrycode  like  'CH%';
注意：like语句在使用时，切记不要出现前面带%的模糊查询，不走索引，查询字符串时可用，数字不能用
条件：where 配合 in 子句使用
  例子：查询中国和美国的所有城市信息
select  *  from  city  where  countrycode  in ('CHN','USA');
条件：where 配合 between  and
  例子 ：查询世界上人口数量在100w和200w之jian
select  *  from  city  where  population>=1000000 and  population<=2000000;
select  *  from  city  where  population between  1000000 and  2000000;
  例子：SELECT * FROM city WHERE countrycode NOT IN ( 'CHN'，‘usa’）；

(3) select 配合 group by +聚合函数应用
聚合函数：
平均值 :avg()
最大值 :max()
最小是 :min()
总和 : sum()MySQL的常用语句MySQL的常用语句MySQL的常用语句
计数 : count()
列转行 :group_concat()
概念：一般聚合函数和group by搭配使用分组按照某个相同的元素进行分组
例子：
第一个：统计每个国家的总人口数量
select countrycode，sum(population) from city group by countrycode;
第二个：统计中国每个省的总人口数量
select district，sum(population) from city where countrycode=‘CHN’ group by district;
第三个：统计中国每个省的城市个数和城市名称列表
select district,count(name),group_concat(name) from city where countrycode=‘CHN’ group by district;
select district,count(name),from city where countrycode=‘CHN’ group by district;
（4）select 配合having 应用
例子：统计中国每个省的总人口数量大于10000000的城市
select district ,sum(population) from city where countrycode=‘CHN’ group by district having sum(population)>10000000;

(5) select 配合 order by 排序应用
例子：
第一个：查询中国所有城市，并按人口从小到大排序
select * from city where countrycode=‘CHN’ group by district order by sum(population) desc;
注意：加上desc后就是从大到小排序，不加就是默认从小到大排序
第二个：统计中国每个省的总人口数量，并统计人口大于5000000的信息，加排序
select district，sum（population） from city where countrycode=‘CHN’ group by district having sum(population) >5000000 order by sum(population);
±-------------±----------------+
| district | sum(population) |
±-------------±----------------+
| Anhui | 5141136 |
| Chongqing | 6351600 |
| Guangdong | 9510263 |
| Hebei | 6458553 |
| Heilongjiang | 11628057 |
| Henan | 6899010 |
| Hubei | 8547585 |
| Hunan | 5439275 |
| Jiangsu | 9719860 |
| Jilin | 7826824 |
| Liaoning | 15079174 |
| Peking | 7569168 |
| Shandong | 12114416 |
| Shanghai | 9696300 |
| Sichuan | 7456867 |
| Tianjin | 5286800 |
| Zhejiang | 5807384 |
±-------------±----------------+
说明：where是在group by之前执行的过滤条件，having是在select list 执行成功后的给过滤条件
（6）select 配合 limit应用
例子: 统计中国每个省的总人口数量，并统计人口大于5000000的城市，且统计前五名
select district,sum(population) from city where countrycode=‘chn’ group by district having sum(population)>5000000 limit 5 ;
±-------------±----------------+
| district | sum(population) |
±-------------±----------------+
| Anhui | 5141136 |
| Chongqing | 6351600 |
| Guangdong | 9510263 |
| Hebei | 6458553 |
| Heilongjiang | 11628057 |
±-------------±----------------+

8.0版本新特性：倒序索引

例子：统计中国每个省的总人口数量，并统计人口大于5000000的城市，且统计6到14名城市
mysql> select district,sum(population) from city where countrycode=‘chn’ group by district having sum(population) > 5000000 limit 5,9;（limit 9 offset 5）—>表示跳过前5名，显示后9名
±---------±----------------+
| district | sum(population) |
±---------±----------------+
| Henan | 6899010 |
| Hubei | 8547585 |
| Hunan | 5439275 |
| Jiangsu | 9719860 |
| Jilin | 7826824 |
| Liaoning | 15079174 |
| Peking | 7569168 |
| Shandong | 12114416 |
| Shanghai | 9696300 |
±---------±----------------+

5.2 select 查询多表的数据
思路：多表连接需要有关联关系（比如多个表中有相同的列内容）
关联：需要引入join on语句，
结构：select xxx from +表名 join +表名 on+俩表相同列内容的列名 +条件……
例子1：统计一下人口小于100人的国家的名字，城市的名字，人口，疆土面积
select city.name,country.name,city.population,country.surfacearea from city join country on city.countrycode=country.code where city.population <100;

例子2：统计一下成都城市，人口数，所在国家名，所在的大洲
select city.population,country.continent,city.name,country.name from city join country on city.countrycode=country.code where city.name=‘chengdu’;

例子3：统计一下每个老师教的学生的人数及其具体名字
select teacher.tname,count(student.name),group_concat(student.name) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.name;

扩展：concat用法
作用：可以把显示内容进行拼接
例子：想把显示内容进行‘root@10.0.0.%’显示
比如：select concat(user,"@",host) from mysql.user;
显示：如下

+-------------------------+
| concat(user,"@",host)   |
+-------------------------+
| root@10.0.0.%           |
| huawei@localhost        |
| mysql.session@localhost |
| mysql.sys@localhost     |
| root@localhost          |
+-------------------------+

3.2 distinct 应用
mysql> select distinct(countrycode) from world.city;

3.3 union 和 union all

-- 例子： 查询中国或美国的城市信息
select * from world.city where countrycode='CHN' or countrycode='USA'
select * from world.city where countrycode in ('CHN','USA')

select * from world.city where countrycode='CHN'
union all 
select * from world.city where countrycode='USA';

面试题：
union 和 union all 区别
union : 聚合两个结果集，会自动进行结果集去重复。
union all : 聚合两个结果集，不会去重复。
别名的用法
第一个：表别名 as —>为了书写简单，可以全局调用
select a.tname,count(d.name),group_concat(distinct d.name) from teacher as a join course as b on a.tno=b.tno join sc as c on b.cno=c.cno join student as d on c.sno=d.sno group by a.name;

第二个：列别名 as —>也就是自定义名字，通俗易懂
select a.tname as 教师名,count(d.name) as 学生数,group_concat(d.name) as 学生明细 from teacher as a join course as b on a.tno=b.tno join sc as c on b.cno=c.cno join student as d on c.sno=d.sno group by a.name;

说明：列别名只能在having 或者order by 中调用，distinct是去重复函数

如果想将数据导出到一个文件中，则需要在/etc/my.cnf中的[mysqld]下加入一行参数：secure-file-priv=/tmp,加入后重启数据库服务，则输入命令select * from mydql.user into outfile ‘/tmp/a.txt’;就会输出到相应的目录

tmp目录可以设置为任何目录，* etc opt 。。。。。等

外连接
作用：强制驱动表
算法：next loop
驱动表是什么？
在多表连接当中，承当for循环中外层循环的角色。
此时，MySQL会拿着驱动表的每个满足条件的关联列的值，去依次找到for循环内循环中的关联值一一进行判断和匹配。

建议：将结果集小的表设置为驱动表更加合适。可以降低next loop的次数。
对于内连接来讲，我们是没法控制驱动表是谁，完全由优化器决定。
如果，需要人为干预，需要将内连接写成外连接的方式。
例如：

select city.name ,city.population,country.name,country.surfacearea
from city join country
on city.CountryCode=country.Code
where city.name='wuhan';

改写为：强制驱动表的left join 。

select city.name ,city.population,country.name,country.surfacearea
from city left join country
on city.CountryCode=country.Code
    where city.name='wuhan';

这样做的好处，就是将city。name=wuhan先帅选出来，在与后表进行匹配，效率更高

itoldwei

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql常用语句

MySQL的常用语句1 MySQL基础管理1.1 用户权限管理1.1.1 用户的定义linux：用户名字表示用户，例如：oldguoMySQL：用户名@‘白名单’ ,例如：root@‘localhost’白名单：在白名单出现的ip,网段，主机名域名，才能登录数据库例子：oldboy@‘localhost’ oldboy@’%’ oldboy@‘10.0.1.%’oldboy@‘10.0.0.51’ oldboy@‘1...
复制链接

扫一扫