常用sql语句整理

yifei_

发布于 2022-11-14 14:03:57

2810

发布于 2022-11-14 14:03:57

文章被收录于专栏：yifei的专栏

什么是SQL?

SQL是一种结构化查询语言(structured query language)
我们用它来访问数据库

others

RDBMS 关系型数据库管理系统(relational database management system)

大小写不敏感

可分为两部分：

数据操作语言(DML)：SELECT、UPDATE、DELETE、INSERT、INTO

数据定义语言(DDL)：CREATE DATABASE、ALTER DATABASE、

CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE INDEX、DROP INDEX

select语句

SELECT * FROM tablename
SELECT columnname FROM tablename
SELECT name1,name2 FROM tablename

DISTINCT

SELECT DISTINCT columname FROM tablename -从表里面返回 不重复 的columnam值

WHERE

SELECT columnname from tablename WHERE columname = value
- "="可以换为 <>(不等于),>,<,>=,<=,between(某个范围内)
- value 如果是文本，需要加单引号，如果是数值，直接写

AND OR

有多个限定条件时可以用and或or连接，分别是&&和||的作用。
SELECT * FROM tablename where a=1 AND b=2

ORDER BY DESC:descend降序；ASC：ascend升序；默认升序

SELECT * FROM tablename ORDER BY columname DESC
SELECT columname1,columname2 FROM tablename ORDER BY columname 
SELECT columname1,columname2 FROM tablename ORDER BY columname1,columname2

INSERT INTO

INSERT INTO tablename VALUES(v1,v2,v3)
INSERT INTO tablename(c1,c2,c3) VALUES(v1,v2,v3)

UPDATE

UPDATE tablename SET columnname=value WHERE columnname=value
UPDATE tablename SET columnname1=value1,columnname2=value2 WHERE columnname=value

DELETE

DELETE FROM tablename WHERE columname=value
DELETE * FROM tablename 不删除表的情况下删除所有行

TOP

SELECT TOP 3 * FROM tablename  返回前三条数据

TOP PERCENT

SELECT TOP 50 PERCENT * FROM tablename 返回前50%的数据

LIKE

SELECT * FROM tablename WHERE col LIKE 'N%' 返回col的值以N开头的记录
SELECT * FROM tablename WHERE col LIKE '%ooo%'
% 代表模式中缺少的字母

SELECT * FROM tablename WHERE col NOT LIKE '%ooo%

%:代替一个或多个字符
_:替代一个字符

IN

Id LastName FirstName Address City 1 Adams John Oxford Street London 2 Bush George Fifth Avenue New York 3 Carter Thomas Changan Street Beijing

SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

Id LastName FirstName Address City 1 Adams John Oxford Street London 3 Carter Thomas Changan Street Beijing

Alias/AS 取别名

SELECT LastName AS Family, FirstName AS Name
FROM Persons

Family Name Adams John Bush George Carter Thomas

JOIN

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

UNION 连接结果集

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

CREATE TABLE

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE(Id_P),
PRIMARY KEY (Id_P)
)

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
FOREION KEY (Id_P) REFFERENCES Persons(Id_P)
)

CHECK约束

CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

欢迎与我分享你的看法。转载请注明出处：http://taowusheng.cn/ 微博：寒枫–0-0– 知乎：https://www.zhihu.com/people/tao-wu-sheng 豆瓣：YIFEI

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2018-01-22，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

数据库

登录后参与评论

0 条评论

热度