SQL语句***重点

SQL是MySQL的学习中最为重要的一个部分，进行对数据的增删改查，sql是结构化的查询语句，在学习sql的时候，需要了解sql语句的分类，以及需要掌握语法的使用，熟练使用帮助手册

MySQL执行SQL语句原理

简述底层原理流程

如图所示，所有的app是一个应用程序，可以有非常多不同的应用程序，像手机里面不同的APP程序一样，假设我们在应用程序上进行了商品查询的动作，就是select的语句，应用程序会将查询的动作通过mysqld连接到后端的代码【可以是Python、go Java语言，看后端是使用的什么语言构建的】，这个是连接层，会进入连接池，【如果在这个连接池之前有过连接数据就可以直接复制，没有连接过会将数据同步到连接池里面记录，以方便下次的连接，这样是为了减少数据库的连接，节约资源，最消耗资源的不是进行操作，而是连接和断开的时候】连接层会将申请的SQL语句传递到sql layer，在这里1、会进行检查sql语句是否正确，是否符合规则；2、会针对不同的sql进行分类，发放给不同的模块进行执行，例如这里是查询语句就会先去cache缓存中查询，有就进行响应，没有的会进行sql的解析；3、会由parser进行sql的解析，对用户的sql进行解析是需要查看哪些表的哪些字段数据；4、authorzatom会进行权限的判断，检查是否有对应的权限进行使用，没有就会报错；5、准备sql的执行计划；6、执行sql计划进行查询数据，在对应的模块进行读取数据，内存 磁盘等；7、读取之后的数据会添加到缓存中，方便下一次的查询。

SQL分类（CRUD）

SQL语句中的关键字语法是大小写不敏感的，参数是需要明确大小写的，例如创建一个表或者库的，表名字或库名字

DDL语句

DDL主要学习CREATE、ALTER、DROP，DDL是指的进行对数据结构进行调整的命令

CREATE–创建

#环境准备，检查环境操作系统的MySQL是否有正常启动[root@db01~]# netstat -lnptActive Internet connections(only servers)Proto Recv-Q Send-QLocalAddressForeignAddress State PID/Program name tcp00127.0.0.1:60100.0.0.0:*LISTEN961/sshd: root@pts/tcp000.0.0.0:220.0.0.0:*LISTEN952/sshd tcp600::1:6010:::*LISTEN961/sshd: root@pts/tcp600:::3306:::*LISTEN1981/mysqld tcp600:::22:::*LISTEN952/sshd#创建一个King库语法：CREATEDATABASE库名字 mysql>createdatabaseking;#创建库Query OK,1rowaffected(0.00sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||king||mysql||performance_schema||sys|+--------------------+5rowsinset(0.00sec)#创建jinyin用户语法：CREATEUSER'用户名'mysql>CREATEUSERjinyin->;Query OK,0rowsaffected(0.00sec)#创建wjx的账号并设置密码，允许本地登入语法：CREATEUSER'用户名'@'访问主机'IDENTIFIEDBY'密码';mysql>createuser'wjx'@'127.0.0.1'identifiedby'wjx888';Query OK,0rowsaffected(0.00sec)#在King库里面创建一张表，名字是lol，字段有id、jiage、name语法：CREATEtableifnotexit表名字(字段 类型，字段类型);额外的参数信息 mysql>createtableifnotexistslol(idint,namevarchar(255)notnull,jiageint);Query OK,0rowsaffected(0.01sec)mysql>desclol;#查看表结构+-------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||jiage|int(11)|YES||NULL||+-------+--------------+------+-----+---------+-------+3rowsinset(0.01sec)查看CREATE帮助手册，使用？ 加语法关键字 mysql>?CREATE;Many help itemsforyour request exist.Tomake a more specific request,pleasetype'help <item>',where<item>isoneofthefollowingtopics:CREATEDATABASECREATEEVENTCREATEFUNCTIONCREATEFUNCTIONLOADABLEFUNCTIONCREATEINDEXCREATELOGFILEGROUPCREATEPROCEDURECREATESCHEMACREATESERVERCREATETABLECREATETABLESPACECREATETRIGGERCREATEUSERCREATEVIEWSHOWSHOWCREATEDATABASESHOWCREATEEVENTSHOWCREATEFUNCTIONSHOWCREATEPROCEDURESHOWCREATESCHEMASHOWCREATETABLESHOWCREATEUSERSPATIAL INDEXES

ALTER–修改

主要对表的结构做修改操作，新增、删除、修改表名，字段

#1、修改表的名字，将lol修改为lol2方法一：renametable旧的名字to新的名字 mysql>renametableloltolol2;Query OK,0rowsaffected(0.00sec)mysql>showtables;+----------------+|Tables_in_king|+----------------+|lol2|+----------------+1rowinset(0.00sec)方法二：使用alter语法altertable旧表格renameto新表格 mysql>altertablelol2renametolol;Query OK,0rowsaffected(0.00sec)mysql>showtables;+----------------+|Tables_in_king|+----------------+|lol|+----------------+1rowinset(0.00sec)#2、在表格里面新增字段password语法：altertable表名ADD字段名字 字段类型;mysql>altertablelolADDpasswordint;Query OK,0rowsaffected(0.01sec)mysql>desclol;+----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||jiage|int(11)|YES||NULL|||password|int(11)|YES||NULL||+----------+--------------+------+-----+---------+-------+4rowsinset(0.00sec)#3、指定插入表格字段在哪个位置，添加pic字段在name的后面，添加misc字段在pic的后面mysql>altertablelolADDpicvarchar(200)aftername,ADDmiscvarchar(100)afterpic;Query OK,0rowsaffected(0.00sec)mysql>desclol;+----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||pic|varchar(200)|YES||NULL|||misc|varchar(100)|YES||NULL|||jiage|int(11)|YES||NULL|||password|int(11)|YES||NULL||+----------+--------------+------+-----+---------+-------+6rowsinset(0.01sec)#4、修改已经存在的表结构字段的内容修改password的类型为varchar语法：ALTERTABLE表名MODIFYCOLUMN列名 数据类型[长度]NULL;mysql>altertablelolmodifycolumnpasswordvarchar(100);Query OK,0rowsaffected(0.01sec)mysql>desclol;+----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||pic|varchar(200)|YES||NULL|||misc|varchar(100)|YES||NULL|||jiage|int(11)|YES||NULL|||password|varchar(100)|YES||NULL||+----------+--------------+------+-----+---------+-------+6rowsinset(0.00sec)#5、删除misc对列-- 删除lol表中的misc列语法：ALTERTABLE表名字DROPCOLUMN列名字;mysql>altertableloldropcolumnmisc;Query OK,0rowsaffected(0.00sec)mysql>altertableloldropcolumnpic;Query OK,0rowsaffected(0.01sec)mysql>desclol;+----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||jiage|int(11)|YES||NULL|||password|varchar(100)|YES||NULL||+----------+--------------+------+-----+---------+-------+4rowsinset(0.00sec)-- 同时删除多个列（用逗号分隔）ALTERTABLE表名字DROPCOLUMN列,DROPCOLUMN列;

DROP–删除

#可以删除表 删除库，注意此命令删除数据是无法进行回滚的语法：DROP DATABASE[IF EXISTS]数据库名;DROP TABLE[IF EXISTS]表名1[, 表名2,...];-- 可同时删除多个表

DCL语句

控制数据，主要是进行授权操作

GRANT—授权

SQL安全措施

1、设置的root密码必须是复杂的，定期进行更换密码

2、更改ssh的默认端口

3、禁止root用户远程登入，只允许普通用户登入

4、修改常见的端口数据，例如3306这些
5、所有用户的授权需要精确明确，不使用% *这些通配符

查询目前数据库中用户权限 mysql>SELECT user,host,authentication_string FROM mysql.user WHERE user='root';+------+-----------+-------------------------------------------+|user|host|authentication_string|+------+-----------+-------------------------------------------+|root|localhost|*9A7CFEB6347D7E825827188C11E68E27522E2974|+------+-----------+-------------------------------------------+1rowinset(0.00sec)#授权命令如下：grant 权限 on 数据库.数据表 to 用户名字@‘允许登入的网段’ identified by ‘登入的密码’；#设置最大权限，给用户jinyin运行在任何地方登入对所有数据进行操作，生产环境不要使用mysql>grant all on *.* to jinyin@'%'identified by'jy123.com';mysql>selectuser,host from mysql.user;+---------------+-----------+|user|host|+---------------+-----------+|jinyin|%||wjx|127.0.0.1||mysql.session|localhost||mysql.sys|localhost||root|localhost|+---------------+-----------+5rowsinset(0.00sec)#创建一个用户wujiaxin,只能对king库进行查询，删除，修改，新增操作使用授权命令会自动创建对应的用户并且完成授权 mysql>grant select,drop,alter,insert on king.* to wujiaxin@'10.0.0.%'identified by'jy123.com';Query OK,0rows affected,1warning(0.00sec)mysql>selectuser,host from mysql.user;+---------------+-----------+|user|host|+---------------+-----------+|jinyin|%||wujiaxin|10.0.0.%||wjx|127.0.0.1||mysql.session|localhost||mysql.sys|localhost||root|localhost|+---------------+-----------+6rowsinset(0.00sec)#登入账号验证[root@db01 ~]# mysql -uwujiaxin -h10.0.0.51 -P3306 -pEnter password: Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis7Server version:5.7.38-log MySQL Community Server(GPL)Copyright(c)2000,2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type'help;'or'\h'forhelp. Type'\c'toclearthe current input statement. mysql>show databases;+--------------------+|Database|+--------------------+|information_schema||king|+--------------------+2rowsinset(0.00sec)注意：* 和%不要随便使用，为了数据库安全

REVOKE–删除权限

使用的语法：REVOKE 权限列表 ON 权限范围 FROM'用户名'@'访问主机';要撤销的权限（如SELECT、DROP、ALTER、INSERT，多个权限用逗号分隔，也可用ALL PRIVILEGES表示所有权限）。

COMMIT–永久数据修改

DML语句

DML是对数据内容做处理

insert–插入数据

#使用语法insert into 表(字段，字段，字段)values(数据，数据，数据);#在lol表中新增一条数据mysql>insert into lol(id,name,jiage,password)values(1,'jiax',1800,'jiosffj'),(2,'jiayin',1700,'csdlcsd');Query OK,2rows affected(0.01sec)mysql>select* from lol;+------+--------+-------+----------+|id|name|jiage|password|+------+--------+-------+----------+|1|jiax|1800|jiosffj||2|jiayin|1700|csdlcsd|+------+--------+-------+----------+2rowsinset(0.00sec)注意：字符串类型的数据必须使用引号

update–更新数据

一定要规范的进行使用update，就是必须加where条件进行匹配需要更新的数据内容，不然会导致所有的数据都被修改

语法：update 表名字 SET 修改的字段=修改的值 where 匹配修改内容的字段=匹配的数据值#修改lol表中id为1的name为jymysql>update lolsetname='jy'whereid=1;Query OK,1row affected(0.00sec)mysql>select* from lol;+------+--------+-------+----------+|id|name|jiage|password|+------+--------+-------+----------+|1|jy|1800|jiosffj||2|jiayin|1700|csdlcsd|+------+--------+-------+----------+2rowsinset(0.01sec)#同时修改多个字段使用，隔开#修改lol表中id为2的name为wjx jiage为2000mysql>update lolsetname='wjx',jiage=2000whereid=2;Query OK,1row affected(0.00sec)mysql>select* from lol;+------+------+-------+----------+|id|name|jiage|password|+------+------+-------+----------+|1|jy|1800|jiosffj||2|wjx|2000|csdlcsd|+------+------+-------+----------+2rowsinset(0.00sec)

delete–删除数据

注意：delete是一行一行进行删除数据的，不合适于在海量数据中进行大批量的数据删除，效率会很低，还有一个特点，对于有自增的id数据列删除之后也会保留id位置

进行删除数据 语法：delete from 数据库.表名字 where 接条件#删除jiage是1800的数据mysql>delete from lol wherejiage=1800;Query OK,1row affected(0.00sec)mysql>select* from lol;+------+------+-------+----------+|id|name|jiage|password|+------+------+-------+----------+|2|wjx|2000|csdlcsd|+------+------+-------+----------+1rowinset(0.00sec)

truncate–永久删除数据

这个删除是物理意义上的永久删除，而且是进行大批量删除

语法：truncate table 表名字;

delete truncate drop删除有什么区别

DROP：用于删除整个表，包括数据、索引和约束，一旦执行无法恢复。

DELETE：用于删除表中的特定数据行，但保留表的结构和定义，可以通过WHERE子句进行限制。

TRUNCATE：用于快速删除表中的所有数据，但保留表的结构和定义，不会触发触发器，不会通过WHERE子句进行限制。

字符集

如果你只需要处理大多数的拉丁字符（比如英文、法文、德文等），UTF-8就足够了。但如果你需要处理一些特殊的字符（比如一些表情符号或者一些特殊的语言字符），或者你的应用可能会与一些旧的软件或数据库交互，那么使用UTF-8MB4会是一个更好的选择

DQL语句 *** 【最常使用】

SELECT–查询语句

1、通过*查询所有，生产环境重数据比较庞大，不建议使用，比较合适内容比较少的表格 SELECT * from 表名字 mysql>select* from lol;+------+------+-------+----------+|id|name|jiage|password|+------+------+-------+----------+|2|wjx|2000|csdlcsd|+------+------+-------+----------+1rowinset(0.00sec)2、指定字段进行查询，可以看where结合使用 指定字段 mysql>selectid,name from lol;+------+---------+|id|name|+------+---------+|2|wjx||3|xiat||4|xiuxiu||5|xshasha||1|dage|+------+---------+5rowsinset(0.00sec)结合条件，查询价格大于2000的id和name mysql>selectid,name from lol where jiage>2000;+------+---------+|id|name|+------+---------+|5|xshasha||1|dage|+------+---------+2rowsinset(0.00sec)#多条件查询 合并and查询id=2jiage大于100的name mysql>SELECT name from lol whereid=2and jiage>1000;+------+|name|+------+|wjx|+------+1rowinset(0.00sec)查询数据之后进行排序 order by 字段 默认是从小到大 order by 字段 desc;从大到小#查询jiage大于2000的name，jiage，并且从大到小排序mysql>SELECT name,jiage from lol where jiage>2000order by jiage desc;+---------+-------+|name|jiage|+---------+-------+|xshasha|5900||dage|2900|+---------+-------+2rowsinset(0.00sec)使用limit限制查询的相关数据 语法：limit 起点,条数 从第几条开始，查看几条 mysql>SELECT * from lol;所有数据 +------+---------+-------+-----------+|id|name|jiage|password|+------+---------+-------+-----------+|2|wjx|2000|csdlcsd||3|xiat|1800|jsvr||4|xiuxiu|1900|csdvgresd||5|xshasha|5900|efhioew||1|dage|2900|jinyii|+------+---------+-------+-----------+5rowsinset(0.00sec)mysql>SELECT * from lol limit2,3;+------+---------+-------+-----------+|id|name|jiage|password|+------+---------+-------+-----------+|4|xiuxiu|1900|csdvgresd||5|xshasha|5900|efhioew||1|dage|2900|jinyii|+------+---------+-------+-----------+3rowsinset(0.00sec)

DESC–查询表结构

语法： DESC 表名字 mysql>desc lol;+----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+--------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(255)|NO||NULL|||jiage|int(11)|YES||NULL|||password|varchar(100)|YES||NULL||+----------+--------------+------+-----+---------+-------+4rowsinset(0.00sec)

SHOW–查询语句

#查看数据库show databases;#查看创建数据库详细信息show create database world;#查看表show tables;#查看创建表详细信息show create table city;#查看授权show grants;#查看 字符集show charset；

核心SQL语法

索引操作

查看表的索引，主键也是一种索引 语法 show index from 表 mysql>show index from mysql.user\G ***************************1. row *************************** Table: user Non_unique:0Key_name: PRIMARY Seq_in_index:1Column_name: Host Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: ***************************2. row *************************** Table: user Non_unique:0Key_name: PRIMARY Seq_in_index:2Column_name: User Collation: A Cardinality:6Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:2rowsinset(0.00sec)添加表里面的索引 语法 alter table 表的名字 ADD index 索引的名(添加索引的字段);#给lol表的name列添加普通索引，索引名（idx_lol_name）mysql>alter table lol ADD index idx_lol_name(name);Query OK,0rows affected(0.02sec)mysql>show index from lol\G ***************************1. row *************************** Table: lol Non_unique:1Key_name: idx_lol_name Seq_in_index:1Column_name: name Collation: A Cardinality:5Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:1rowinset(0.00sec)