SQL常用语句总结

您所在的位置：网站首页 › sql语句计算差值 › SQL常用语句总结

SQL常用语句总结

2023-05-17 20:48| 来源: 网络整理| 查看: 265

一，简介

1.1 数据库是用来存放数据的，对数据库的操作需要用到SQL语句

1.2 数据库种类有也非常多：

关系型数据库： Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL、SQLite

非关系型数据库： NoSql、Cloudant、MongoDb、redis、HBase

1.3 常用的是Oracle、MySQL、SQLite

二常用语句操作，MySQL创建用户表为例

2.1 mysql数据类型五大类

整数类型：BIT、BOOL、TINY INT等。浮点数类型：FLOAT、DOUBLE、DECIMAL。字符串类型：TEXT、MEDIUM TEXT、LONGTEXT等。日期类型：Date、DateTime、TimeStamp、Time、Year。其他数据类型：BINARY、VARBINARY、ENUM、SET等。

2.2 登录MySQL数据库，cmd命令进入MySQL安装目录bin

cd D:\PHPTools\mysql-5.7.25\bin

2.3 登录数据库，执行命令，输入密码

mysql -u root -p

2.4 创建数据库

create database DONG;

2.5 删除数据库

drop database DONG;

2.6 使用数据库

use DONG;

2.7 创建用户表

CREATE TABLE IF NOT EXISTS `user`( `uid` INT UNSIGNED AUTO_INCREMENT, `account` VARCHAR(100) NOT NULL, `password` VARCHAR(100) NOT NULL, `name` VARCHAR(100), `sign` VARCHAR(500), `avatar` VARCHAR(100), `time` DATE, PRIMARY KEY (`uid` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.8 查看全部表

show tables;

2.9 查看表数据结构

desc user;

2.10 删除表

DROP TABLE user;

2.11 插入用户表数据

INSERT INTO user (account,password,name,sign,time) VALUES ("test" , "123456" , "zhangsan" , "hahahahaha" , NOW());

2.12 查询用户表数据

select * from user;

2.13 where 根据用户id查询

select * from user where uid =2;

2.14 更新用户id是2的，用户的名字和签名

UPDATE user SET name="lisi-lisi222" , sign="吼吼吼吼-吼吼吼吼222" where uid=2;

再次查询可以看到用户名和签名已变

select * from user where uid =2;

2.15 删除用户id==1的那条数据

DELETE FROM user WHERE uid=1;

再次查询用户表，可以看到没有用户id==1的数据了

2.16 使用AND（并且）, OR（或者）限制查询

AND查询用户表账户是admin，并且密码是123456的数据

select * from user where account='admin' AND password='123456';

OR查询用户表账户是admin，或者账户是dong的数据

select * from user where account='admin' OR account='dong';

三进阶查询，MySQL创建文章表为例

3.1 创建文章表，并关联用户表，创建用户表外键

CREATE TABLE IF NOT EXISTS `article`( `id` INT UNSIGNED AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `content` TINYTEXT NOT NULL, `time` DATE, PRIMARY KEY (`id` ), uid INT references user(uid) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.2 创建多个文章数据

INSERT INTO article (title,content,time,uid) VALUES ("哈哈" , "哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈" , NOW() , 1);

INSERT INTO article (title,content,time,uid) VALUES ("吼吼" , "吼吼吼吼吼吼吼吼吼吼吼吼吼吼吼吼" , NOW() , 1);

INSERT INTO article (title,content,time,uid) VALUES ("嗷嗷" , "嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷嗷" , NOW() , 2);

INSERT INTO article (title,content,time,uid) VALUES ("呵呵" , "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" , NOW() , 2);

3.3 查询文章表数据，可以看到有4条数据

select * from article;

3.4 LIKE 模糊查询

select * from article where content like "%哈哈";

3.5 查询uid==1的文章

select * from article where uid =1;

3.6 文章表按用户ID降序排序，ASC，DESC

select * from article ORDER BY uid DESC;

3.7 ORDER BY分组，文章表按用户分组，统计每个人有多少文章

SELECT COUNT(*) FROM article GROUP BY uid;

HAVING 配合 GROUP BY使用，用来过滤分组的条件

先看下全部用户数据

如下满足账户数量大于，才显示结果

select count(*) from user group by time HAVING count(account) > 2;

下面如果数量大于4不会显示结果，因为分组后账户数量不大于4

3.8 LIMIT 限制返回条数，如下限制返回2条数据

select * from article LIMIT 2;

3.9 分页

select * from table_name limit [offset，] rows

offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0 rows：返回具体行数

如下，文章表从第2行开始，检索3行记录，即：检索记录行 3-4

select * from article limit 2 ，3;

分页格式，查询每页10条数据，page页码，pageSize每页条数

select * from table limit (page-1)*pageSize,pageSize;

select * from article limit 0,10;

3.10 ALTER修改表

MODIFY修改文章表字段类型，修改title 从 VARCHAR 改为 TEXT

ALTER table article MODIFY title TEXT;

或使用CHANGE 修改文章表字段名字，修改 title 为 name。CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

ALTER table article CHANGE title name TEXT;

查询后可以看到title的名字和类型都变了

3.11 UNION 连接两个以上的表，将结果合并到一个集合中，多个 SELECT 语句会删除重复的数据

SELECT uid FROM user UNION SELECT uid FROM article ORDER BY uid;

用户表和文章表都存在用户id是1，2的数据

3.12 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录

如下查询用户表的time字段，会去除重复的time字段

select DISTINCT time from user;

四多表联合查询

4.1 OIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

4.2 先看下两个表的全部数据

4.2 INNER JOIN 内连接

查询两个表的相同uid的

select a.name , b.name from user a INNER JOIN article b ON a.uid=b.uid;

4.3 LEFT JOIN 左连接

select a.name , b.name from user a LEFT JOIN article b ON a.uid=b.uid;

4.4 RIGHT JOIN 右连接

select a.name , b.name from user a RIGHT JOIN article b ON a.uid=b.uid;

五事务

5.1 事务概念

在数据库中，我们将一条 SQL 语句称为一次基本的操作。将若干条 SQL 语句“打包”在一起，共同执行一个完整的任务，这就是事务。事务（ Transaction）由一次或者多次基本操作构成，或者说，事务由一条或者多条 SQL 语句构成。

5.2 事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

5.3 MYSQL 事务处理主要有两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交SET AUTOCOMMIT=1 开启自动提交

5.4 事务的使用

begin;开始， commit;结束，中间是执行的语句称之为事务

begin;

INSERT INTO user (account,password,name,sign,time) VALUES ("shiwu" , "123456" , "shiwu_name" , "事务签名" , NOW());

INSERT INTO article (name,content,time,uid) VALUES ("事务" , "事务事务事务事务事务事务" , NOW() , 1);

commit;

六临时表

6.1 作用：

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间

6.2 TEMPORARY创建临时表

CREATE TEMPORARY TABLE IF NOT EXISTS `article_te`( `id` INT UNSIGNED AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `content` TINYTEXT NOT NULL, `time` DATE, PRIMARY KEY (`id` ), uid INT references user(uid) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

6.3 使用 SHOW TABLES命令显示数据表列表时，你将无法看到 article_te表

6.4 删除临时表，默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

DROP TABLE article_te;

七视图

7.1 视图的特点：可以被当作是虚拟表或存储查询

视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

7.2 视图的创建

创建一个用户表的视图，查询全部用户信息

create VIEW user_view as select * from user;

7.3 查看视图

show tables;

7.4 查询视图数据

select * from user_view;

7.5 插入修改视图跟正常表一样

INSERT INTO user_view (account,password,name,sign,time) VALUES ("view" , "123456" , "view_name" , "呀呀呀呀" , NOW());

查询可以看到已经有该条数据了

7.6 删除视图

DROP view user_view;

再次查看表，已经没有该视图了

八触发器 TRIGGER

8.1 触发器是数据库的一种回调机制，当达到某种条件时，会自动执行语句。

8.2 创建触发器结构

DELIMITER || CREATE TRIGGER [触发器的名字] [触发器执行时机] [触发器监测的对象] ON [表名] FOR EACH ROW BEGIN[触发器主体代码][触发器主体代码]END

DELIMITER ||：MySQL 默认分隔符是; 但在触发器中，我们使用 || 表示触发器的开始与结束。[触发器的名字]：这里填写触发器的名字[触发器执行时机]：这里设置触发器是在关键动作执行之前触发，还是执行之后触发。[触发器监测的对象]：触发器可以监测 INSERT、UPDATE、DELETE 的操作，当监测的命令对触发器关联的表进行操作时，触发器就被激活了。[表名]：将这个触发器与数据库中的表进行关联，触发器定义在表上，也附着在表上，如果这个表被删除了，那么这个触发器也随之被删除。FOR EACH ROW：这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。[触发器主体代码]：这里是当满足触发条件后，被触发执行的代码主体。这里可以是一句 SQL 语句，也可以是多行命令。如果是多行命令，那么这些命令要写在 BEGIN...END 之间。

8.3 创建触发器

创建如下结构的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END

默认分隔符，结束符是分号，以 ; 作为结束执行语句，但可能会与触发器中需要的分行起冲突，为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||，当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

DELIMITER ||

触发插入数据前，会把对应的账户改为"_new "

CREATE TRIGGER user_demo BEFORE INSERT ON user FOR EACH ROW BEGIN if new.account='trigger_test' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ='禁止用户名是trigger_test'; END IF; END ||