sql语句

第1章 SQL基础应用(SQL语句)

SQL介绍

结构化的查询语言 关系型数据库中一种通用的一种命令

为了让SQL语句更严谨一些 我们规定了一些规则 称之为SQL_MODE

使用SQL_MODE规范SQL语句的语法标准 目前MySQL使用的是SQL92或SQL99语法标准

SQL_MODE是让我们的mysql更加符合现实的逻辑 确保数据更加有意义

SQL_MODE查看方法

mysql> select @@sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode                           |

+-------------------------------------------------------------------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

1.1 常用SQL的分类

分为四类

DDL 数据定义语言

DCL 数据控制语言

DML 数据操作语言

DQL 数据查询语言 (比较特殊拿出来)

第2章 SQL用户管理语句

2.1 MySQL用户管理

用户作用为: 登陆MySQL 管理MySQL

用户定义: 定义用户由两部分组成 用户名@’白名单’

白名单 允许用户登陆的范围 (ip段)

常用

old@’10.0.0.5%’ # 50-59网段

old@’10.0.0.0/255.255.255.0’ # 子网内ip可以访问

old@’10.0.0.%’ #10.0.0.0网段

不常用

old@’localhost’ #本地

old@’10.0.0.1’ #指定ip地址

old@’%’ #所有ip

用户管理SQL语句

１. 查

select user,host from mysql,user;

２. 增

create user old@’10.0.0.%’;

３. 改

alter user old@'10.0.0.%' identified by '123';

４. 删

drop user old@'10.0.0.%';

2.2 MySQL权限管理

MySQL权限定义

在8.0以前 命令即为权限 你要什么命令 就授予什么命令权限

ALL权限 所有权限

包括

ALL
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE,DROP
特殊权限:with grant ,option

在8.0以后 支持将多个权限打包成角色 自由打包

当赋予多个用户 同一样的权限 可以打包权限成组 赋予用户这个组的权限 就不用一个一个写了

权限管理SQL语句

授权

语法: grant 权限 on 权限作用范围 to 用户 identified by ‘密码’;

权限作用范围写法:

*.* # 所有库所有表

user.* # user库的所有表

user.t1# user库的t1表

例: 创建并授权管理员用户root 能通过10.0.0.0网段进行登陆 并可以创建其他管理用户

grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;

with grant授予用户赋予其他用户权限

例2: 创建一个应用程序用户 用户app 库为app

grent select,insert,update,delete on app.* to app@'10.0.0.%' identified by '123';

应用用户只需要增删改查权限即可 不用给的太多

查询用户权限

语法: show grants for 用户;

例: 查询root用户权限等

show grants for root@'10.0.0.%';

回收权限

语法: revoke 权限 on 权限作用范围 from 用户;

例子: 回收app用户的delete权限

revoke delete on app.* from app@'10.0.0.%';

第3章 SQL语句篇

第4章 DDL定义语言

用于定义库和表的内容

4.1 库的定义

4.1.1 增

CREATE DATABASE 库名 CHARSET 字符集;

create database 库名 charset 字符集;
规范:

  1. 必须设置字符集

  2. 库名必须小写不能有中文

  3. 不要用数字开头

  4. 库名要和业务有关

  5. 库名不要太长

  6. 库名不能是关键字

4.1.2 删

DROP DATABASE 库名;

drop database 库名;

一般只有管理员由权限删除 库和表

4.1.3 改

ALTER DATABASE 库名 CHARSET 字符集;

alter database 库名 charset 字符集;

覆盖性的修改库信息 原来存在的 不会收到影响

4.1.4 查 库属性

show create database 库名;

查看库里所有表

SHOW TABLES FROM 库名;

4.2 表的定义

4.2.1 增

CREATE TABLE 表名 (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',

sname VARCHAR(32) NOT NULL COMMENT '姓名',

age TINYINT NOT NULL DEFAULT 18 COMMENT '年龄',

sex ENUM('male','female','others') NOT NULL DEFAULT 'others' COMMENTS '性别',

intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'

)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

括号内为表内 列属性

表属性: 存储引擎

建表语句规范 :

  1. 表名: 不能小写 业务相关 不能以数字开头 不能太长 不能用关键字

  2. 表属性: 字符集,存储引擎,注释都要配置

  3. 列属性:

a) 合适的数据类型(简短,合适,完整的)

b) 必须要有主键(PK)

c) 梅格列要非空 并设定默认值

d) 必须有注释

配合开发审核数据库语句 减少线上数据库的风险

4.2.2 删

删除表内一列

alter table 表 drop 列;

删除数据有两种方式

drop table 表名;

会把表的结构/表数据行 彻底删除 慎用 尽量别用

truncate table 表名;

会把表内数据行(表段)删除 (物理层面删除) 结构会留下

4.2.3 改 重点

只要能定义出来的 都能改

修改表属性

alter table 表名 新属性;

修改后直接覆盖原属性

修改表名

alter table 表名 rename to 新表名;

表内添加一列内容

alter table 表名 add 列名 bigint not null unique comment ‘注释’;

后面是列属性

插入列

alter table 表名 add 列名 carchar(100) not null comment ‘注释’after 列名;

最后的after 指在什么只够添加

在第一列之前添加列

alter table 表名 add 列名 bigint not null unique vomment ‘注释’first;

first 在第一列之前添加

修改列属性 列名

alter table 表名 change 旧列名 新列名 tinyint not null default 1 comment ‘注释’;

注意使用change时 需要将属性都写全了 不然会默认清空属性

删除列

alter table 表名 drop 列名;

4.2.4 查

查看表属性

desc 表名;
查看详细建表属性

show create table 表名;

4.2.5 注意:

所有表结构更改时 都会自动锁表 尽量避免大表 Online DDL(在线繁忙时进行更改)

当有DDL需要紧急上线时 如何诉说

诉说: 对业务影响很大 会长时间锁表

建议: 1 找业务低谷期进行添加

2 使用第三方工具pt-osc(percona-toolkit) 进行添加 风险很大 表结构有更改失败的可能

percona-toolkit工具下载地址: https://www.percona.com/software/database-tools/percona-toolkit

4.3 归档表/日志表

表内数据量过大 进行分隔表内容 进行归档表

rname老的表 修改为新名称 xxx.xx时间

在业务不繁忙时 进行分隔 归档时会锁表(无法使用数据库) 所以要在不忙时归档

分为两步

将表分隔

alter table 表名 rename to 新表名

复制一个表 结构为like后的表

create tabls 源表名 like 时间表名

对于归档表可以使用 pt-archiver 第三方工具

4.4 总结:

建库规范:

  1. 必须设置字符集

  2. 库名必须小写不能有中文

  3. 不要用数字开头

  4. 库名要和业务有关

  5. 库名不要太长

  6. 库名不能是关键字

建表规范

表名: 不能小写 业务相关 不能以数字开头 不能太长 不能用关键字

表属性: 字符集,存储引擎,注释都要配置

列属性:

e) 合适的数据类型(简短,合适,完整的)

f) 必须要有主键(PK)

g) 梅格列要非空 并设定默认值

h) 必须有注释

第5章 DCL(控制语言)

控制语言主要是两个 grant (授权)revoke (回收权限)

查看权限 : show grants for 用户

第6章 DML (操作语言)

主要用于表中数据行进行操作 主要时增删改查

6.1 增insert (数据插入)

向最后一行插入数据

INSERT INTO 表名(列名,列名) VALUES(数据,'数据'); # 向指定列添加数据

SELECT * FROM 表名;

INSERT INTO 表名 VALUES(数据,'数据'); # 逐条写入数据

6.2 删 delete (数据删除)

删除单行或多行数据 小心别乱用

说明 delete语句数据逻辑删除 磁盘空间不会立即释放,长期会产生大量碎片

delete from 表名 where 条件;

6.3 改 update (数据修改)

只修改某一行内容

update 表 set 列 where 条件;

UPDATE lup.biao SET sname='ccc' WHERE sname='zi';

6.4 伪删除

添加一个状态码 将要删除的数据后的状态吗做出改变即可

提前添加状态列 0=删除 1=存在

ALTER TABLE 表名 ADD statu TINYINT NOT NULL DEFAULT 1;

修改状态码

UPDATE 表名 SET statu=0 WHERE 条件;

查看方式 只查看状态为1的

SELECT * FROM 表名 WHERE statu=1;

第7章 DQL (查询语言)

7.1 select 查询语言

用法分为两部分

7.1.1 单独使用 mysql独有

  1. 查询参数

只要是能用的参数就可以使用这种方式查询

select @@datadir; # 查询指定参数

+------------------+

| @@datadir |

+------------------+

| /app/mysql/data/ |

+------------------+

1 row in set (0.00 sec)

show variables ;# 查看所有MySQL所有参数

  1. 简易计算

select 10+10;

  1. 函数查询

select now();# 打印时间

select concat(“信息”); # 打印信息

7.1.2 配合多子句 (标准用法)

当使用多个子句配合时 顺序为

  1. from #必选的

  2. where

  3. group by

  4. having

  5. order by

  6. limit


    image.png

例子:

环境

mysql> desc city;

+-------------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| Name | char(35) | NO | | | |

| CountryCode | char(3) | NO | MUL | | |

| District | char(20) | NO | | | |

| Population | int(11) | NO | | 0       | |

+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

7.1.3 from 选择表

select * from city;

查看指定列

select Name,CountryCode from city;

7.1.4 where 过滤

需求 要过滤出所有中国的地区

等值查询

select * from city where countrycode='CHN';

不等值查询 看人口少于1000的

select * from city where Population<1000;

查看不是中国的信息

select * from city where CountryCode != ‘CHN’; # != 或者<> 代表不等于

where 配合like 语句使用

需求 查询国家代号时CH开头的城市信息 模糊查询

SELECT * FROM city WHERE countrycode LIKE 'CH%';

注意: like语句 百分号不要出现在查询语句的前面 不能用’%xxx’这种方式

where****配合逻辑连接符 (and or)

需求: 中国城市人口大于500w的城市

SELECT * FROM city WHERE countrycode= 'CHN' AND population>5000000;

或者写成

SELECT * FROM city WHERE countrycode= 'CHN' && population>5000000;

需求: 显示中国或美国城市

SELECT * FROM city WHERE countrycode='CHN' or countrycode='USA';

或者写成

SELECT * FROM city WHERE countrycode='CHN' || countrycode='USA';

where 配合in 过滤多个

select * from city where countrycode in (‘USA’ ,‘CHN’);

where 配合between and

从xx 到xx

查找出1000000 到2000000 之间的数值

SELECT * FROM city WHERE population BETWEEN 1000000 AND 20000000

7.1.5 group by(按什么分组) + 聚合函数应用

称之为分组聚合 group by 后面写的是分组条件 过程为排列去重分组

聚合函数有:

max() 最大值

min() 最小值

avg() 平均值

count() 叠加数量

sum() 求和

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,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;

7.1.6 having 过滤 在group by之后判断

要求 统计中国每个省总人数 只显示人口大于1000w的地区

SELECT District,SUM(Population)
FROM city
WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population)>10000000;

7.1.7 order by 按什么列进行排序

要求 查询中国所有的城市信息 并按照人口总数进行从大到小排序输出结果

SELECT District,Population
FROM city
WHERE CountryCode='CHN'
ORDER BY Population DESC; 
其中 desc 倒叙

7.1.8 limit 取范围内的值 n个

要求 查询中国所有的城市信息 并按照人口总数 只显示前五个 或者 2-4名

SELECT District,SUM(Population) FROM city
WHERE countrycode='CHN'
GROUP BY District
ORDER BY SUM(Population) DESC
LIMIT 5; 
# 取前五个

取2-4个

SELECT District,SUM(Population) FROM city
WHERE countrycode='CHN'
GROUP BY District
ORDER BY SUM(Population) DESC
LIMIT 1,3;

7.1.9 赠送项union all和 distinct

union将两条命令一起执行后 显示结果

union和union all 区别是union显示时会自动去重 union all不会去重

显示所有中国和美国城市

SELECT NAME,CountryCode FROM city
WHERE CountryCode='USA'
UNION ALL
SELECT NAME,CountryCode FROM city
WHERE CountryCode='CHN';

distinct 去重

SELECT DISTINCT countrycode FROM city;

7.2 show

常用show命令

show databases: # 查看所有库名

show tables; # 查看当前库下所有表名

show tables from xxx;# 查看指定库下所有表名

show create database xx库; # 查看建库语言

show create tables xx表; # 查看建表语言

show variables like‘参数’; # 查看指定参数

show variables ; # 查看所有MySQL所有参数

show grants for 用户@’白名单’ # 查看用户权限信息

show [full] processlist; # 查看会话连接情况

show processlist;# 查看所有连接状态

show engines; # 查看当前数据库支持的引擎

show charset;# 查看当前数据库使用的数据集

show collation;# 查看当前数据库支持的排序规则

show status \G;# 查看当前数据库状态

show [global] status;# 查看当前数据库状态信息

show master status;# 查看当前使用的二进制日志信息;

show binary logs; # 查看所有二进制日志信息

show binlog evnets in ‘xxx’# 查看二进制日志事件信息

show relaylog events in ‘xxx’ # 查看中继日志事件信息

show slave status\G;# 查看从库复制状态信息

show engine innodb status\G# 查看innodb引擎相关信息数据
:

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,602评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,442评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,878评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,306评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,330评论 5赞 373
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,071评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,382评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,006评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,512评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,965评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,094评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,732评论 4赞 323
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,283评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,286评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,512评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,536评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,828评论 2赞 345

推荐阅读更多精彩内容

  • SQL语句大全
    SQL语句参考，包含Access、MySQL 以及 SQL Server 基础 创建数据库 CREATE DATA...
    清茶了了_阅读 1,570评论 0赞 2
  • SQL语句
    1、说明：创建数据库CREATE DATABASE database-name2、说明：删除数据库drop dat...
    sakura_L阅读 371评论 0赞 6
  • 技术 | SQL语句大全
    语 句 功 能 数据操作 SELECT——从数据库表中检索数据行和列INSERT——向数据库表添加新数据行DELE...
    戰敭阅读 5,077评论 0赞 53
  • sql语句大全
    一、基础 1、创建数据库(create) CREATE DATABASE database-namecreate ...
    YBshone阅读 637评论 0赞 1
  • SQL语句
    基础 创建数据库 CREATE DATABASE database-name 删除数据库 drop databas...
    RoronoaZoro丶阅读 381评论 0赞 0