Mysql语句使用全解

星辰泛于海

已于 2022-02-16 15:22:56 修改

阅读量680

点赞数 6

文章标签： mysql 数据库 sql

于 2021-10-06 19:35:13 首次发布

本文链接：https://blog.csdn.net/CSDNzgcxy/article/details/120624817

版权

什么是数据库？

数据库是按照数据结构来组织，存放和管理的仓库。

常见的数据库分为：

关系型数据库。
非关系型数据库。

关系型数据库和非关系型数据库的存储位置：

关系型数据库：存储在磁盘上面（硬盘），磁盘怎么保存数据（IO操作，当遇到性能问题，找到IO，把IO操作减下来，减少运行付出时间成本），怎么读取到的数据，（磁道，数据都会落到一个个扇区上面（寻道和旋转找到你要的数据）每次访问都要进行这样的操作，寻道时间加上旋转时间，访问多了，时间会是一个致命的问题）。

非关系型数据库：非关系型内存数据库，用于做缓存的中间介，保存在缓存里，读取数据非常快，当一个量级达到很大的时候就得用到非关系型数据库，Redis提高用户的访问速度。把一串一串的二进制指令交给CPU处理，CPU以指令的方式去运行（L1，L2，L3）RAM 主存——内存（存放空间大的缓存）

什么是关系型数据库？

顾名思义就是数据元素之间有关联关系的数据库。

RDBMS ——>关系型数据库管理系统。

关系型数据库：

Sql Server Mysql Oracle DB2 等。SQL Server ASP.net windows体系用的比较多

Mysql：开源（免费）的数据库，应用场景十分广泛。

Oracle：需要像Oracle公司付费，传统，适用于安全性要求高的行业。

非关系型数据库：

数据元素独自处理。例如：Redis MongoDB FileSystem 等。

关系型数据库的结构？

存放的是一张一张的表（table）的矩阵。

表里面又存放了行（Row）和列（Column）。

表与表之间使用外键关联。

每一个表用主键标识唯一性。

关系型数据库怎么用？

SQL（Strctured Query Language 结构化查询语言）去操作数据库与数据：

数据查询语言（DQL: Data Query Language）数据检索语句，用于从表中获取数据。通常最常用的为保留字SELECT,并且常与FROM子句、WHERE子句组成查询SQL查询语句。
数据操纵语言（DML：Data Manipulation Language）主要用来对数据库的数据进行一些操作，常用的就是INSERT、UPDATE、DELETE。
事务处理语言（TPL：Data Processing Language）事务处理语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION、COMMIT和ROLLBACK。
数据控制语言（DCL：Data Control Language）通过GRANT、DENY和REVOKE，确定单个用户或用户组对数据库对象的访问权限。
数据定义语言（DDL:Data Definition Language）常用的有CREATE和DROP，用于在数据库中创建新表或删除表，以及为表加入索引等。
指针控制语言（CCL:Cursor Control Language）它的语句，想DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

接下来就步入我们今天的正题..........

Mysql基础语法

1.数据库

1.查看所有数据库：（SHOW DATABASES）
2.创建一个数据库： (CREATE DATABASE 库名)
3.删除一个数据库： (DROP DATABASE 库名)
4.使用这个数据库：（USE 库名）

2.表

1.查看所有表（SHOW TABLES）
2.创建一个表（CREATE TABLE 表名(字段，类型。。。)）
3.直接将查询结果导入或复制到新创建的表：（CREATE TABLE 创建的表名 SELECT * FROM 表名）
4.创建的表结构和存在表结构一样：（CREATE TABLE 新表 LIKE 老表）
5.创建一个临时表临时表在你连接MySQL期间存在。当断开连接时，MySQL将自动删除释放空间，也可以手动删除：（CREATE TEMPORARY TABLE 临时表名(字段，类型)）
6.直接将查询结果导入或复制到新创建的临时表（CREATE TEMPORARY TABLE 临时表名 SELECT * FROM 表）
7.删除一个存在表（DROP TABLE IF EXISTS 表）
8.更改存在表的名称：（ALTER TABLE 旧表 RNAME 新表名）（RENAME TABLE 旧表 TO 新表名）
9.查看表结构（5种推荐使用第一种简单因为效果一样）（DESC 表）（DESCRIBE 表）（SHOW COLUMNS IN 表）（SHOW COLUMNS FROM 表）（EXPLAIN 表）
10.查看表的创建语句：（SHOW CREATE TABLE 表）

3.表的结构

1.添加字段：（ALTER TABLE 表 ADD 字段，属性）
2.删除字段（ALTER TABLE 表 DROP 字段）
3.更改字段属性和字段名（ALTER TABLE 表 CHANGE 老子段新字段类型）
4.只更改字段属性（ALTER TABLE 表 MODIFY 字段类型）

4.表的数据

（基础重点）
1.增加数据（INSERT INTO 表(字段名) VALUES(对应字段的数据)）
2.把复制的数据复制一遍重新插入（INSERT INTO 表 SELECT * FROM 表）
3.删除数据（DELETE FROM 表 WHERE 条件）记住一定要加where条件
4.更改数据 UPTATE 表 SET 字段=什么 WHERE 条件一定要加条件
5.数据查找 (SELECT * FROM 表 WHERE 条件)
6.数据排序（反序）（SELECT * FROM 表 ORDER BY 字段 DESC）

5.键

1. 添加主键
ALTER TABLE n ADD PRIMARY KEY (id);
ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id); 主键只有一个，所以定义键名似乎也没有什么用
2. 删除主键
ALTER TABLE n DROP PRIMARY KEY ;
3.添加外键
ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id); # 自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id); # 使用定义的键名fk_id
4.删除外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`;
5. 修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id); # 删除之后从新建
6.添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
7.添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
8.删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;

6.视图

1.创建视图 (CREATE VIEW 字 AS SELECT 字段 FROM 表)
（CREATE VIEW v(id, name) AS SELECT id, name FROM n;）
2.查看视图（与表操作类似） (SELECT * FROM 视图名)（DESC 视图名）
3.查看创建视图语句（SHOW CREATE VIEW 视图名）
4.更改视图（CREATE OR REPLACE VIEW 视图名 AS SELECT 字段名 FROM 表）（ALTER VIEW 视图名 AS SELECT 字段 FROM 表）
5.删除视图（DROP VIEW IF EXISTS 视图名）

7.联接

1.内联接（SELECT * FROM a INNER JOIN b ON a.id=b.id）
2.左外联接（SELECT * FROM a LEFT JOIN b ON a.id=b.id）
3.右外联接（SELECT * FROM a RIGHT JOIN b ON a.id=b.id）
4.交叉联接（SELECT * FROM a CROSS JOIN b ON a.id=b.id）(SELECT * FROM a, b)
5.类似全连接full join 的联接用法 (UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！)

8.函数函数

（1）聚合函数

关键字：1.总数（count） 2.总和（sum） 3.平均值（avg） 4.最大值（max） 5.最小值（min）
使用语法：select 函数（字段名） as ‘别名’ from 表名条件；

（2）数字函数关键字

语法： SELECT 函数(数值)

关键字：
1.绝对值：（abs）
2.二进制（bin）八进制（oct）十六进制（hex）
3.圆周率（pi）
4.取大于小数的最小整数（ceil）
5.取小于小数的最小整数（floor）
6.返回集合最大的值（greatest）
7.返回集合最小值（least）
8.取余前面的数除后面的数（mod）
9.返回0到1的随机值，每次不一样（rand）可以在里面添加一个值可以让他一直是第一次的值
10.四舍五入（round）可以在数后面添加保留几位小数
11.截短到几位小数（truncate）和round差不多
12.把符号的值变成-1 （sign）里面的值是正数就是正数1
平方根（sqrt）

（3）字符串函数

1.字符串拼接（concat）
# 连接用','分割字符串（concat_ws ）
# 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese（）（insert('chinese', 3, 2, 'IN')）
2.从左开始返回字符串第几个字符（left）
3.从右开始返回字符串第几个字符（right）
4.返回字符串右边第三个或者负几个后面的字符或者第几个后面的多少个字符（substring）
5.切割字符串两边空字符（trim）（ltrim）（rtrim）
6.重复字符多少次（repeat）
7.把字符反过来（倒序）（reverse）
8.返回字符串长度（length）
9.大写（upper）（ucase）小写（lower）（lcase）
10.position('i' IN 'chinese'); # 返回'i'在'chinese'的第一个位置-3
11.比较字符串大小第一个大于第二个返回正数1 小于返回-1（strcmp）

（4）时间函数

1.当前年月（current_date）当前时间时分秒（current_time）当前时间（now（））
2.查看时分秒 (hour),(minute) ,(second) 括号里面放current_time
3.查看年月日（ year(current_date), month(current_date), week(current_date);）
4.四分之一多少刻 SELECT quarter(current_date);
5.几月的星期几的英文（monthname）（dayname）
dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);

（5）控制流函数

1.SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f'); # t f
2.SELECT ifnull(NULL, 't'), ifnull(2, 't'); # t 2
3.SELECT isnull(1), isnull(1/0); # 0 1 是null返回1，不是null返回0
4.SELECT nullif('a', 'a'), nullif('a', 'b'); # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
WHEN 1 THEN 'first'
WHEN 2 THEN 'second'
WHEN 3 THEN 'third'
ELSE 'other' END ;

（6）系统信息函数

1. SELECT database(); # 当前数据库名-test
2.SELECT connection_id(); # 当前用户id-306
3.SELECT user(); # 当前用户-root@localhost
4.SELECT version(); # 当前mysql版本
5.SELECT found_rows(); # 返回上次查询的检索行数

9.用户

1. 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test')); # 在用户表中插入用户信息，直接操作User表不推荐
2. 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
3.更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
4.用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
5. FLUSH PRIVILEGES ; 刷新系统权限表,使授予权限生效
6. 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost'; # 取消该用户的删除权限

10.存储过程

1.创建存储过程
DELIMITER // # 无参数
CREATE PROCEDURE getDates(in(入参) out（出参）inout（出入参）)
BEGIN

END //
DELIMITER ;
2. 删除存储过程
DROP PROCEDURE IF EXISTS getDates;
3.修改存储过程的特性
ALTER PROCEDURE getDates MODIFIES SQL DATA；
4.查看存储过程
SHOW PROCEDURE STATUS LIKE 'getDates'; # 状态
SHOW CREATE PROCEDURE getDates_3; # 语句
5.调用存储过程（call）存储过程有参数就要填参数值

11.其他语句

查看所有的表信息（包括视图）
SHOW TABLE STATUS;

12.其他

1.数据库备份
mysqldump -u root -p db_name > file.sql
mysqldump -u root -p db_name table_name > file.sql
2.数据库还原
mysql -u root -p < C:\file.sql

我们的基础语法差不多应该就这些，如果觉得哪里没写或者写错了的，请纠正，马上改，感谢支持。

条件表达式

1.<> 不等于 <小于 =等于 >大于 <=小于等于 >=大于等于
BETWEEN 几到几的区间
not BETWEEN 不在这个区间的数
in 准确查找后面接括号括号里面可以是一个集合
not in 不是括号值的数都出来
2.and 并且满足两个的条件的时候让他显示出来
or 或把多个条件拼接
xor 亦或
3.排序
升序：asc 小上大下 SELECT * FROM t_sys_user ORDER BY 用户ID ASC;
降序：desc 大上小下 SELECT * FROM t_sys_user ORDER BY 用户ID desc;
4.模糊查询
like like后面接通配符
% 替代0个或多个字符可以写在前中后
_ 替代一个字符

触发器

当数据库内容比较庞大记录大于10000条一定不要用触发器！慎重使用
1.新增
DELIMITER //
CREATE TRIGGER user_role BEFORE INSERT on `t_sys_user` for each ROW
BEGIN
INSERT INTO user_role VALUES(new.`用户id`,new.状态);
INSERT INTO monitor VALUES(new.`用户id`,'新增(BEFORE)',null,new.状态,NOW());
END
//
当对一个表中的数据做了DML操作的时候在执行前或执行后执行触发器语句

Mysql索引

（1）介绍

什么是索引？（在生产环境中，最容易出问题一些复杂的查询语句，为了加速优化查询，就不得不用到索引了）
为什么要有索引？（索引在MySQL中也叫做“键”，是存储引擎用于快速查找的一种数据结构。索引对于良好性能非常关键，尤其是数据越来越大时，索引对性能愈发重要）

（2）索引原理

减少检索数据的扫描量
避免数据排序
可以将磁盘IO 从随机IO到顺序IO

（3）索引管理

1.普通索引（index）：加速查找
   2.主键索引（primary key）：加速查找+约束（不为空且唯一）
   3.唯一索引（unique）：加速查找+约束（唯一）
   4.全文索引（fulltext）：用于搜索很长的文章，效果最好
   5.空间索引（spatial）：了解就好，几乎淘汰
   6.联合索引：primark key（id，name）：联合主键   unique（id，name）：联合唯一索引 index（id，name）：联合普通索引

（4）创建删除方法

创建索引
-在创建表时就创建（需要注意的几点）
create table s1(
id int ,#可以在这加primary key
#id int index #不可以这样加索引，因为index只是索引，没有约束一说，
#不能像主键，还有唯一约束一样，在定义字段的时候加索引
name char(20),
age int,
email varchar(30)
#primary key(id) #也可以在这加
index(id) #可以这样加
);
-在创建表后在创建
create index name on s1(name); #添加普通索引
create unique age on s1(age);添加唯一索引
alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
create index name on s1(id,name); #添加普通联合索引

删除索引
drop index id on s1;
drop index name on s1; #删除普通索引
drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

（5）慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE(查询不使用缓存)
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

MySQL执行计划（EXPLAIN）（重点）

执行语句关键字介绍
   id（按照id大小相同则从上往下）
   select_type（查询的类型和等级）
   table(执行操作的表名)
   partitions（分区）
   type（性能）
   possibis_keys（可能用到的索引）
   key（用到的索引）
   key_len（where条件后面的长度）
   ref
   rows（执行了多少行）
   filtered（百分比）
   Extra

执行计划是干什么的？

用来检查执行语句（DQL DML）的性能，用于优化程序的运行时间和速度

MySQL事务(ACID)

什么是事务？
   （1）在MySQL中只有用了innodb数据库引擎才支持事务
   （2）事务可以用来维护数据库的完整性，保证成批的SQL语句要全部执行，要么就都不执行
   （3）事务用来管理insert，updata，delete语句

事务的四个特征

1.原子性
   一个事务（transaction）中的所有的操作，要么全部完成，要么全部不完成，不会结束在中间的某环节。事务在执行过程发生错误，会被滚回（Rollbback）到事务开始前的状态，就像没执行过一样
2.一致性
   在事务开始之前和事务结束以后，数据库完整性没有被破坏，这表示写入的资料完全符合所有的预设规则，这包含资料的精确度，串联性以后数据库可以自发性的完成预定的工作
3.隔离性
   数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，事务隔离分为四个等级分别是：
       性能从上往下越来越低，安全性从下往上越来越低 MySQL默认是可重复读
（1）读未提交（Read uncommitted）
           一个事务读取另一个未提交的数据，读未提交产生脏读
（2）读已提交（Read committied）
           一个事务只能读取另外事务提交后的数据，产生不可重复度
（3）可重复读（repeatable read）
           确保事务前后查询数据一致产生幻（让你看到一股假象）读数据
（4）串行化（Serializable）
           （可串行化）就如同同步操作，等一个事务操作完，下一个才能操作，这样子能解决幻读，安全性高，性能低，因为一次只能有一个事务进行
4.持久性
   事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失

MySQL事务介绍

在MySQL命令行默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行（CIOMMIT）操作。因此要显式的开启一个事务务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交

事务控制语句

1. 开启事务：（BEGIN）或（STANSACTION）显示的开启一个事务
2. 提交事务：(COMMIT)也可以使用(COMMIT WORK)，不过二者都是等价的。（COMMIT）会提交事务，并使已对数据库进行所有的修改成为永久性的
3.回滚：（ROLLVACK）也可以使用(ROLLBACK WORK),不过二者使等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
4.多个事务：（SAVEPOINT identifer，SAVEPOINT）允许在事务中可以有多个（SAVEPOINT）;
5.删除事务保存点：（RELEASE SAVEPOINT identidier）删除一个事务的保存点，当没有指定的保存点时，执行该语句会报出一个异常
6.回滚事务到标记点（ROLLBACK TO identifier）
（SET TRANSACTION）用来设置事务的隔离级别有（READ UNCOMMITED）(READ COMMITTED) (REPEATABLE READ)(SERLALIZABLE)

MYSQL事务处理主要两种方法

1.用（BEGIN）（ROLLBACK）（COMMIT）来实现
       （BEGIN）开始一个事务
       （ROLLBACK）事务回滚
       （COMMIT）事务确认
2.直接用（SET）来改变（MySQL）的自动提交模式
       （SET AUTOCOMMIT=0）禁止自动提交
       （SET AUTOCOMMIT=1）开启自动提交

游标

游标的创建过程

创建——使用——循环——结束

后续发现还有没补充完的会及时补充，哪里写错了的请纠正指教，感谢大家的支持。

星辰泛于海

关注

6
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
Mysql语句使用全解

什么是数据库？数据库是按照数据结构来组织，存放和管理的仓库。常见的数据库分为：关系型数据库。非关系型数据库。关系型数据库和非关系型数据库的存储位置：关系型数据库：存储在磁盘上面（硬盘），磁盘怎么保存数据（IO操作，当遇到性能问题，找到IO，把IO操作减下来，减少运行付出时间成本），怎么读取到的数据，（磁道，数据都会落到一个个扇区上面（寻道和旋转找到你要的数据）每次访问都要进行这样的操作，寻道时间加上旋转时间，访问多了，时间会是一个致命的问题）。非关系型数据库：非关系型内存数据库
复制链接

扫一扫