SQL语句用法总结

Posted on 2022-07-18 07:58  quantumLQ  阅读(2640)  评论(0)  编辑  收藏  举报

use quan56_goods; 使用数据库

show tables; 展示数据表

模糊查询

select
	*
from
	tb_brand
where
	name like '%林%';

顺序

书写顺序

SELECT 字段列表

FROM 表名

WHERE 记录筛选条件

GROUP BY 分组字段列表

HAVING 分组筛选条件

ORDER BY 排序字段列表

执行顺序

FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

具体的说：

FROM：组装来自不同数据源的记录

WHERE：根据指定的条件过滤上一步检索出的记录

GROUP BY：对上面过滤后的记录按指定条件分组

SUM/AVG/COUNT：使用聚合函数进行计算

HAVING：对所有分组根据指定条件进行过滤

SELECT：从上一步过滤后的各个分组记录中提取指定查询的字段列表（包括聚合字段、计算字段、表达式字段等）

ORDER BY：对上一步查询得到的结果集按照排序字段列表进行排序，并输出排序结果

去重

select distant *

排序

select XX from XX where XX order by XX DESC(倒顺)/ASC(正序)

范围取值

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

//字符串只有这两个，可以取范围的如数字，日期，则取范围内的
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

系统时间

创建表

CONSTRAINT 限制，建表时上面写了的字段做增加，或重命名主键时使用

CREATE TABLE tb_emp1
        (
        id INT(11) not null identity(1,1) primary key, //非空，自增，主键
        name VARCHAR(25) comment '注释',
        deptId INT(11),
        salary FLOAT,
        CONSTRAINT pk_PersonID PRIMARY KEY (id,name) //重命名主键，以及将多个字段设为主键
        );

外键约束

Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

修改表

ALTER TABLE student RENAME TO tb_students_info;
{ ADD COLUMN <列名> <类型>
 | CHANGE COLUMN <旧列名> <新列名> <新列类型>
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
 | MODIFY COLUMN <列名> <类型>
 | DROP COLUMN <列名>
 | RENAME TO <新表名>
 | CHARACTER SET <字符集名>
 | COLLATE <校对规则名> }

查询表

    SELECT
    {* | <字段列名>}
    [
    FROM <表 1>, <表 2>…
    [WHERE <表达式>
    [GROUP BY <group by definition>
    [HAVING <expression> [{<operator> <expression>}…]]
    [ORDER BY <order by definition>]
    [LIMIT[<offset>,] <row count>]
    ]

SELECT DISTINCT <字段名> FROM <表名>; //去重 distinct

模糊查询

SELECT
	spu.`name`,spu.`caption`
FROM
	tb_spu spu
WHERE
	spu.name LIKE '华为%' OR spu.name LIKE '摩托%';

还有 not like

通配符 描述
% 代表零个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[ ^charlist] 或者 [!charlist] 不在 
查找不以A、L、N开头的
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

联合查询 union

select * from class
union
select * from person;

插入数据

INSERT INTO 表名称 VALUES (值1, 值2,....)  //全字段
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)  //指定字段

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

INSERT INTO <表名>
SET <列名1> = <值1>,
        <列名2> = <值2>;

NSERT INTO tb_courses
    -> (course_name,course_info,course_id,course_grade)
    -> VALUES('Database','MySQL',2,3);

修改数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

mybatis传对象
@Update("UPDATE tb_request_ip t SET count = #{ip.count}, last_time = #{ip.lastTime}, info = #{ip.info} WHERE id = #{ip.id};")
    int updateById(@Param("ip") RequestIp ip);

删除数据

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

DELETE FROM 表名称 WHERE 列名称 = 值

自连接查询

SELECT 
	t1.name, t2.name, t3.name
FROM
	tb_category t1,tb_category t2, tb_category t3
WHERE
	t1.parent_id = t2.id AND t2.parent_id = t3.id;

结果

image-20220409180935991

索引

建立主键或唯一约束时，自动创建索引。

  • 小的数据表不应当使用索引；
  • 需要频繁进行大批量的更新或者插入操作的表；
  • 如果列中包含大数或者 NULL 值，不宜创建索引；
  • 频繁操作的列不宜创建索引。

建立单列索引，可不写列，或写多个列用‘，’分隔

CREATE INDEX index_name ON table_name(column_name);

建立唯一索引

CREATE UNIQUE INDEX index_name
on table_name (column_name);

取消索引

ALTER TABLE table_name DROP INDEX index_name

视图

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

sql函数

时间

NOW() CURDATE() CURTIME()
2008-11-11 12:45:34 2008-11-11 12:45:34

可用于select ，建表语句默认值

CREATE TABLE Orders  
(  
OrderId int NOT NULL,  
ProductName varchar(50) NOT NULL,  
OrderDate datetime NOT NULL DEFAULT NOW(),  
PRIMARY KEY (OrderId)  
)

SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate  
FROM Products;

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

Unit 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH 
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,  
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,  
EXTRACT(DAY FROM OrderDate) AS OrderDay  
FROM Orders  
WHERE OrderId=1

DATE_ADD() 函数向日期添加指定的时间间隔

设置结束时间

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH 
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate  
FROM Orders

DATE_SUB() 函数从日期减去指定的时间间隔

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

DATEDIFF() 函数返回两个日期之间的天数

SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate

GETDATE() 函数 返回当前的日期和时间

CREATE TABLE Orders  
(  
OrderId int NOT NULL PRIMARY KEY,  
ProductName varchar(50) NOT NULL,  
OrderDate datetime NOT NULL DEFAULT GETDATE()  
)

2008-11-11 12:45:34.243

DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

DATEPART(datepart,date)

date 参数是合法的日期表达式。datepart 参数可以是下列的值：

datepart 缩写
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns

条件筛选

SELECT MAX(column_name)
FROM table_name
WHERE condition;

同样用法，还有 MIN最小、 COUNT总个数、 AVG平均、 SUM总合 、FIRST 第一个、LAST最后一个、LOWER(s)转换值小写、 UPPER(s)转换值大写、LCASE(列名)转换列的值小写、UCASE(列名)转换列的值大写

查找

将第一个参数做目标值，在后面的参数中找第一个参数，找到的返回索引（从1开始），没有的话返回0

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

此sql返回2.

分组

第二个 order by 是在 count 得出的各个分组的总个数后，根据总个数对分组进行排序

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

补充条件，having 子句

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

having 和 where 的区别

where是根据数据表筛选，having是根据结果筛选，将聚合函数如count（字段）作为条件的，只能用having，将select所选字段之外字段作为条件的，只能用where。

where:
where是一个约束声明,使用where来约束来自数据库的数据;
where是在结果返回之前起作用的;
where中不能使用聚合函数。
having:
having是一个过滤声明;
在查询返回结果集以后，对查询结果进行的过滤操作;
在having中可以使用聚合函数。

聚合函数就是例如SUM, COUNT, MAX, AVG等对一组(多条)数据操作的函数，需要配合group by 来使用。

where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选。

情景

  1. where和having都可以使用的场景

select goods_price,goods_name from sw_goods where goods_price > 100
select goods_price,goods_name from sw_goods having goods_price > 100

解释：上面的having可以用的前提是我已经筛选出了goods_price字段，在这种情况下和where的效果是等效的，但是如果我没有select goods_price 就会报错！！因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

  1. 只可以用where，不可以用having的情况

select goods_name,goods_number from sw_goods where goods_price > 100
select goods_name,goods_number from sw_goods having goods_price > 100 //报错！！！因为前面并没有筛选出goods_price 字段

  1. 只可以用having，不可以用where情况

查询每种goods_category_id商品的价格平均值，获取平均价格大于1000元的商品信息

select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000
select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category //报错！！因为from sw_goods 这张数据表里面没有ag这个字段

注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(goods_price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

SELECT *, COUNT(course) count
FROM student_course
WHERE course = '数学'
GROUP BY course
HAVING COUNT(course) > 1
ORDER BY COUNT(course) DESC;

mid 提取值的指定部分

SELECT MID(column_name,start[,length]) FROM table_name;
参数 描述
column_name 必需。要提取字符的字段。
start 必需。规定开始位置（起始值是 1）。
length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。 
SELECT MID(City,1,4) AS ShortCity  
FROM Customers;

LEN() 函数返回文本字段中值的长度

SELECT LEN(column_name) FROM table_name;

ROUND() 函数用于把数值字段舍入为指定的小数位数

SELECT ROUND(column_name,decimals) FROM table_name;
SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;
参数 描述
column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。

SQRT 函数用于计算得出任何数值的平方根

SELECT name, SQRT(daily_typing_pages) FROM employee_tbl;

RAND 函数，用于产生 0 至 1 之间的随机数

可以使用 ORDER BY RAND() 来对一组记录进行随机化排列

SELECT * FROM employee_tbl ORDER BY RAND();

CONCAT 函数用于将两个字符串连接为一个字符串

拼接两个字段或添加后缀或前缀

SELECT CONCAT(id, name, work_date) FROM employee_tbl;

ifnull 对字段为null 设置默认值

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))  
FROM Products

REPLACE()字符串替换函数

update `article` set title=replace(title,'CodingDict','hello');

TRIM()函数去除字符串头尾空格

  • MySQL: TRIM( ), RTRIM( ), LTRIM( )
  • Oracle: RTRIM( ), LTRIM( )
  • SQL Server: RTRIM( ), LTRIM( )

触发器

每张表最多设置6个

创建触发器格式：

CREATE  TRIGGER  触发器名 触发时间 触发事件
ON  表名  FOR  EACH  ROW
BEGIN
	触发程序
END

1）触发器名具有唯一性

2）触发时间分为两种：BEFORE、AFTER

3）触发事件分为三种：INSERT、UPDATE、DELETE

4）FOR EACH ROW：表示行级触发器（每一条记录都会执行一次触发器）

5）触发程序：触发事件发生时，系统自动运行的程序

//因为有触发时间和触发事件，一个数据库最多可以设置六种类型的触发器

//目前MySQL仅支持行级触发器，不支持语句级别的触发器

案例

# 删除触发器 
DELIMITER //
CREATE TRIGGER trig_book2 AFTER DELETE
  ON t_book FOR EACH ROW
  BEGIN
    UPDATE t_book_type SET book_num=book_num-1 WHERE old.book_type_id=t_book_type.id;
    INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
    DELETE FROM t_test WHERE old.book_type_id=t_test.id;
  END //
 DELIMITER;
 # 上文中 old 为过渡变量 代指删除的那条数据 插入|更新为 new

取消

DROP TRIGGER 表名.触发器名;

Copyright © 2024 quantumLQ
Powered by .NET 9.0 on Kubernetes