Mysql 核心语句

2022-07-01

SQL 语句分类

1、DDL 语句数据库定义语言：数据库、表、视图、索引、存储过程，例如 CREATE DROP ALTER 

2、DML 语句数据库操纵语言：插入数据 INSERT、删除除数据 DELETE、更新数据 UPDATE、查询数据 SELECT 

3、DCL 语句数据库控制语言：例如控制用户的访问权限 GRANT、REVOKE

4、DQL	查询语句：查询语句 SELECT

DDL 语句

创建、删除数据库；添加表字段，表索引

# 数据库里，大小写不敏感（不是不区分大小写）
# 语法
# 内置的SQL关键字全部大写，然后自定义的数据，可以进行大小写管理

### 创建数据库
# 完善写法
CREATE DATABASE IF NOT EXISTS test1;
# 最完善的写法，包含了数据库的字符集指定，让你的 mysg1 能够支持 UTF-8 编码，支持中文 且 不敏感大小写
CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;

# 查看 数据库 创建语句
SHOW CREATE DATABASE test1;

DQL 语句

查询sql语句

# 

# 查看当前在哪个库中
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

# 查看数据表
mysql> show tables;

# 查看数据表结构
mysql> desc user;

# 查看user表所有信息
mysql> select * from user;

# 指定字段查询
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.01 sec)

#

DML 语句

DML，全称为Data Manipulation Language，中文为数据操作语句，其语句的关键字为INSERT、UPDATE和DELETE。
它们分别用于添加、修改和删除表中的行（数据）

insert

delete

update

# 
# 删除；删除前可以使用 se lect 来查看，以免误删
delete  from 数据库.数据表 where 条件；

delete from mysql.user where user='root' and host='tech\_master01';

select user,host,password from mysql.user where user='root';

delete from mysql.user where user='root' and host='::1';

DCL 语句

DCL 授权控制
全称为 Data Control Language，这类语句通过GRANT或REVOKE授权用户许可，确定单个用户和用户组对数据库对象的访问

# 创建用户
create user 名字@'允许登录的主机网段' identified by '密码';

create user test1@'%' identified by 'password';

mysql> show grants for test1@'%';
+-----------------------------------+
| Grants for test1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
# USAGE 只有登录权限； 没有其他权限；


###########
# 授权 ###
#允许 test1 用户使用 grant 命令，授权语句最后加 with grant option;
grant 给与的权限 on 数据库.数据表 t0 用户@'允许登录的主机';

# 授予 test1 用户 select 权限； 
grant select on mysql.user to test1@'%';
# 让授权立即生效
flush privileges；

mysql 命令帮助

# 首先是登录 mysql
mysql> ?			# List of all MySQL commands:

查看 DDL sql 语句指令

mysql> ? Data Definition			# 查看 DDL 帮助信息；
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER INSTANCE
   ALTER LOGFILE GROUP
   .....

mysql> ? CREATE DATABASE			# 查看 创建数据库 语句的使用
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
    ......

查看 DCL sql 语句帮助信息

mysql> ? Account Management					# 查看 DCL 命令提示信息
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics: 
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

查看 DML 语句帮助

mysql> ? Data Manipulation			# 查看 DML 语句帮助
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

MySQL SQL 解析原理（面试用）

主要用于性能优化相关；

这里附上一张图，帮助大家理解；解析流程和原理已经在图上写明了；

王者荣耀和 sql 不得不说的故事

先看 DDL 和 DCL

DDL 数据定义：创建修改相关；1.创建

DCL 权限控制：2.修改，权限控制

mysql 字符集的了解

读写 mysql 的中文数据，得保证

客户端的编码，mysql 的连接客户端，如 mysqli 命令，如 navicat 的编码，如 xshell 的编码
服务端的编码，linux 服务器本身的编码，mysqld 服务端的编码
统一

查询当前数据库的字符集信息

字符集的作用就是，计算机是如何处理、以及展示全世界各种语言的一种编码表格

# 查看包含 char 字符的变量；
mysql>  show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

# 这里的字符集是不统一的，插入中文数据，就会有乱码存在了，因此需要 ut8 字符编码统一

创建数据表

# 查看创建数据表的 帮助
mysql> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
    ....


# 创建数据表的语法
create table tb_name (
    字段名 字段数据类型[(宽度) 约束条件], 
    字段名2 字段数据类型2[(宽度) 约束条件]
    ) 数据表的额外参数;
    
    ⚠️：同一张表中，
    	字段名不能相同；
    	宽度和约束条件可选；
    	字段名和类型是必须的；

# 完整创建语句
# 注意，mysql 默认关键字大小写不敏感，数据表是严格区分大小写的
# 字段名 数据类型 [额外的选项，可选]
# 引擎理解，好比汽车有不同种类的发动机，各种发动机有各种优缺点 
CREATE TABLE IF NOT EXISTS `tanks`(
   `id` INT,
   `name` VARCHAR(100) NOT NULL,
   `skills` VARCHAR(255) NOT NULL,
   `price` int not null,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 查看表
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

# 查看创建表的信息
mysql> show create table tanks\G;
*************************** 1. row ***************************
       Table: tanks
Create Table: CREATE TABLE `tanks` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

# 插入信息
mysql> insert into tanks (id, name, skills, price) values(1, '程咬金'，'大招回血，血量越低，伤害越高'，8888),(2，'大鱼庄周'，'免疫所有人友的负面控制·，2888);

DDL 删除管理

# 查看 drop 帮助
mysql> ? drop database
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

#

DCL 用户管理

# 创建用户的语法
create user 用户名'@'允许登录的网段' identified by '允许该用户登录的密码';

#创建普通用户
# 限制客户端登录的命令 
# 允许用户test1 本地 登录
create user test1@'127.0.0.1' idenitified by 'password';


#########
# grant 添加权限
grant 允许执行的权限1,权限2,权限3 on 数据库.数据表 to 用户@'主机' identified by '密码'；

# 允许 chaochao 用户访问 tanks 表
mysql> grant select on kings.tanks to chaochao@'127.0.0.1' identified by 'cc888';
Query OK,0 rows affected  (0.00 sec)

# 给一个大权限
mysql> grant all privileges on *.* To chaochao@'%' identified by 'cc888';


# 删除用户操作 
mysqL> drop user chaochao@'127.0.0.1';

mysqL> drop user chaochao@'%';

MySQL 核心语句二

通过多种客户端读写 mysql
- mysq1 命令
- navicat 工具的使用
- php
- python
不同业务、给与不同权限 ·
又见王者荣耀数据库
- 数据表设计，创建
- 修改数据表
- 索引创建
- 删除数据条目
- 数据插入
- 数据查询
- 修改表数据

grant 语句

开发、测试需要连接数据库；

# 给开发、测试开通一个连接数据库的账号，且只能够进行读写操作的用户
# create user 
# grant 设置权限


# Grant 语句可以直接创建，且赋值权限，给一个用户
grant select,insert,update,delete on 数据库.数据表 to 用炉名@'%' identified by'密码';

# 创建用户并授权
mysql> grant select,insert,update,delete on kings.tanks to 'yuchao'@'%' identified by 'yuchao668';

# 刷新权限
# 一定记住，修改了 use 表，创建，修改用户后，立即刷新权限 
mysql> flush privileges;

授权的几大特性：

权限，轻而易举，不要使用 all privileges；尽量指定一个、某几个权限
主机范围，尽量别用%，尽量给一个局域网的网段，或者限制某一个IP地址，即使是需要在公网环境中连接，使用%号，也得通过防火墙做一定的安全措施，以及更换 mysql 默认端口等等
Grant 授权对数据库的选择，也尽量别用*，而指定数据库以及数据表
=

连接 mysql

## php 部分
# 安装 php，依赖
# centos7 平台先安装 php，以及 php 读写 mysql 的一个驱动
yum install php-mysqlnd php -y

# 关于php 连接 mysql 脚本，可以网上搜索一下；

# python 部分
# 安装依赖
yum install -y python3 python3-devel

# 安装连接插件驱动
pip3 install pymysql

数据表的修改

语法：
1, 修改表名
    		ALTER TABLE 表名
    											RENAME 新表名；

2, 增加字段
    		ALTER TABLE 表名
    											ADD 字段名数据类型【完整性约束条件】，
    											ADD 字段名数据类型【完整性约束条件】；
    		ALTER TABLE 表名
    											ADD 字段名数据类型【完整性约束条件】FIRST;
    		ALTER TABLE 表名
    											ADD 字段名数据类型【完整性约束条件】AFTER 字段名；

3, 删除字段
    		ALTER TABLE 表名
    											DROP 字段名；

4, 修改字段
    		ALTER TABLE 表名
    											MODIFY 字段名数据类型【完整性约束条件】；
    		ALTER TABLE 表名
    											CHANGE 旧字段名 新字段名 旧数据类型【完整性约束条件】；
    		ALTER TABLE 表名
    											CHANGE 旧字段名 新字段名 新数据类型【完整性约束条件】；

alter 修改数据表

添加数据

# 指定的字段，必须和 value 对应上，不能多，也不能少

INSERT INTO Tanks (name, skills) VALUES ('关于','骑着一匹吗，跑的特别快，特别肉');

# 添加多条数据
INSERT INTO Tanks (name, skills) VALUES ('鲁班','二技能全图，被动扫射'),('甄姬','技能会冰冻敌人');

修改数据表名字

rename 修改

alter 修改

rename
# 语法
rename table 旧的表名 to 新的表名;

# 修该表明
mysql> rename tables tanks to Tanks;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| Tanks           |
+-----------------+
1 row in set (0.00 sec)


alter 
# 语法
#语法
alter table 当前表名 rename to 新表名；

mysql> alter table Tanks rename to TANKS;


##############
#### 修改表字段

添加字段

alter 指令

alter 指令 
#语法
alter table 表名 add 字段名字段的数据类型（长度）额外的字段属性；

# 添加字段
Mysql> alter table Tanks add introduction varchar (255) not null;

# 添加枚举类型；枚举类型 二选一

# 插入到数据表中
alter table Tanks add summoner_skills ENUM ('flush', 'fire') not null default 'flush';

# 添加字段 summoner_skills 在 skills 之后
alter table Tanks add summoner_skills ENUM ('flush', 'fire') not null default 'flush' after skills;

mysql> desc Tanks;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| name         | varchar(100) | NO   |     | NULL    |       |
| skills       | varchar(255) | NO   |     | NULL    |       |
| price        | int(11)      | NO   |     | NULL    |       |
| introduction | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table Tanks add summoner_skills ENUM ('flush', 'fire') not null default 'flush' after skills;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Tanks;
+-----------------+----------------------+------+-----+---------+-------+
| Field           | Type                 | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+---------+-------+
| id              | int(11)              | NO   | PRI | NULL    |       |
| name            | varchar(100)         | NO   |     | NULL    |       |
| skills          | varchar(255)         | NO   |     | NULL    |       |
| summoner_skills | enum('flush','fire') | NO   |     | flush   |       |
| price           | int(11)              | NO   |     | NULL    |       |
| introduction    | varchar(255)         | NO   |     | NULL    |       |
+-----------------+----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段

alter 指令
alter table Tanks drop summoner_skills;

添加多个字段

#英雄的阵营、图片头像
camp
pic

#修改表字段的语句是
alter table Tanks add camp varchar (50), add pic varchar (255);

# 添加到指定字段后边;
alter table Tanks add camp varchar (50) after price, add pic varchar (255) after camp;

修改表字段数据类型

mysql> alter table Tanks change picpic_url char (200);

索引

创建

Mysql 的索引功能，是提高 Mysql 查询速度的一大重点，这里先了解用法索引功能，就像一本书的目录，知道目录，可以非常快速找到我们想要的内容在哪

通过给字段添加索引，能够加快该列字段的查询速度。

# 查看表默认索引
# PRIMARY key 是主键，也是属于索引 inex 的作用；
mysql> show index from Tanks\G;
*************************** 1. row ***************************
        Table: Tanks
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.01 sec)

ERROR:
No query specified


# 创建索引
# 语法：alter table tanks add index 索引的名（哪一个字段)
# 给姓名字段添加索引
Mysql> alter table Tanks add index name_index (name);

删除索引

# 先查看索引
mysql> show index from Tanks\G;
*************************** 1. row ***************************
        Table: Tanks
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: Tanks
   Non_unique: 1
     Key_name: name_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

# 然后删除指定索引
Mysql> alter table Tanks drop index name_index;

数据表的删除

删除基础语法

# 删除数据库语法
drop database 数据库名；

# 删除数据表语法
drop table 表名；

delete 语句

# 语法
delete from 删除数据表的内容

delete from 可以结合 where 条件语句，进行数据的指定删除，判断删除 

delete 是一行一行的删除数据，对于大容量的数据表，删除效率很低

delete 对于有自增 id 的列，数据删除后，会保留其 id 的位置（如果删除、清空表之后，再添加数据，id列不会自增，如果不指定id会直接覆盖前边插入的数据）

# 删除表中所有数据
delete from Tanks;

自增属性

给坦克表的 id 添加自增属性 modify

给 id 列，添加自增属性，再来查看，数据的删除，插入，结果是怎样的

# 修改指令，修改字段的数据类型，额外属性用 modify #自增的意思是，每一个数据插入后，自动的 id+1 操作 
# 自增：每条数据添加后，id自动增加1
# 设置 主键并配置自增
mysql> alter table tanks modify id int(11) primary key auto_increment;

# 只配置自增  
mysql> alter table tanks modify id int(11) auto_increment;


# 配置有自增属性的id键值，当删除数据后，id从上次删除的数值继续增加；而不是回收删除数据的id值

删除之 truncate 语法

truncate 直接清空表数据，重新建立一个表

不会出现自增id的问题，重新计算id

# 清空表数据
truncate table Tanks;

# truncate 清空之后，再插入数据，自增id会从头开始；

数据查询 select

# 查询所有
select * from user;

#  查询 匹配 where  条件
select skills,price from tanks where names='鲁班'；

####### limit 
# limit 语法：limit 起点,条数;
# limit 限制条数 
select from Tanks where price > 3000 limit 2;
# 从第二行开始，向下找三条数据(不包括第二条数据)
select from Tanks where price > 3000 limit 2,3;



######### where
# where 指定条件限制
select from Tanks where id=5;

# 查找 id 大于4 且 价格 大于 8000 的
select from Tanks where id > 4 and price > 8000;

# 
select * from Tanks where price > 3000 and camp='野兽'；



######## 排序
# 对查找结果进行排序
# 默认是生序，asc 排序规则，从小到大
# desc 降序，从大到小

# 默认会根据 id 排序，这里 指定 price 排序
select id,name,skills,price,camp from Tanks where price > 4000 order by price;

# 降序排序 desc
select id,name,skills,price,camp from Tanks where price > 4000 order by price desc ;

修改表数据 update

# 设置多个字段；
update kings.Tanks set price=19999,summoner_skills='fire' where name='猪八戒';

注意不要踩这个坑 – 很坑很坑

#修改所有的数据，这是修改表中所有数据，如果不加 where 就会踩坑；切记切记； 
mysql> update kings.Tanks set ski1ls='二技能的宽度，放大 5gg%，并且大招范围扩大500%', price=99999;
⚠️：这个坑很大，出现这个问题，只能手动修改回去，或者启用备份； 

# 添加where 
mysql> update kings.Tanks set ski1ls='二技能的宽度，放大 5gg%，并且大招范围扩大500%', price=99999 where price > 88888;

MySQL 基础

MySQL 库操作

海天 fei 酱油

Mysql 核心语句

SQL 语句分类

DDL 语句

DQL 语句

DML 语句

DCL 语句

mysql 命令帮助

查看 DDL sql 语句指令

查看 DCL sql 语句帮助信息

查看 DML 语句帮助

MySQL SQL 解析原理（面试用）

王者荣耀 和 sql 不得不说的故事

先看 DDL 和 DCL

mysql 字符集的了解

查询当前数据库的字符集信息

创建数据表

DDL 删除管理

DCL 用户管理

MySQL 核心语句 二

grant 语句

连接 mysql

数据表的修改

添加数据

修改数据表名字

添加字段

删除字段

添加多个字段

修改表字段数据类型

索引

创建

删除索引

数据表的删除

删除基础语法

delete 语句

自增属性

删除之 truncate 语法

数据查询 select

修改表数据 update

注意不要踩这个坑 – 很坑很坑

王者荣耀和 sql 不得不说的故事

MySQL 核心语句二