应届生面试要点总结（16）SQL要看得懂/基本的要会写

客官°小女子只卖身不卖艺 2022-03-24 16:42 120阅读 0赞

SELECT DISTINCT column\_name,column\_name

FROM table\_name;

SELECT column\_name,column\_name

FROM table\_name

ORDER BY column\_name,column\_name ASC|DESC;

INSERT INTO table\_name (column1,column2,column3,...)

VALUES (value1,value2,value3,...);

UPDATE table\_name

SET column1=value1,column2=value2,...

WHERE some\_column=some\_value;

DELETE FROM table\_name

WHERE some\_column=some\_value;

SELECT column\_name(s)

FROM table\_name

LIMIT number;

以下操作在 Microsoft SQL Server 数据库中可执行。

SELECT TOP 50 PERCENT \* FROM Websites;

SELECT \* FROM Websites

WHERE name LIKE '%oo%';

SELECT \* FROM Websites

WHERE name LIKE 'G\_o\_le';

SELECT \* FROM Websites

WHERE name REGEXP '^\[GFs\]';

SELECT \* FROM Websites

WHERE name REGEXP '^\[A-H\]';

SELECT \* FROM Websites

WHERE name REGEXP '^\[^A-H\]';

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;

列的 SQL 别名语法

SELECT column\_name AS alias\_name

FROM table\_name;

表的 SQL 别名语法

SELECT column\_name(s)

FROM table\_name AS alias\_name;

SELECT Websites.id, Websites.name, access\_log.count, access\_log.date

FROM Websites

INNER JOIN access\_log

ON Websites.id=access\_log.site\_id;

SELECT column\_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column\_name=table2.column\_name;

SELECT column\_name(s) FROM table1

UNION

SELECT column\_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

复制多个表中的数据插入到新表中：

SELECT Websites.name, access\_log.count, access\_log.date

INTO WebsitesBackup2016

FROM Websites

LEFT JOIN access\_log

ON Websites.id=access\_log.site\_id;

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO table2

SELECT \* FROM table1;

或者我们可以只复制希望的列插入到另一个已存在的表中：

INSERT INTO table2

(column\_name(s))

SELECT column\_name(s)

FROM table1;

CREATE DATABASE dbname;

CREATE TABLE Persons

(

Id\_P int NOT NULL PRIMARY KEY, //PRIMARY KEY约束

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

ALTER TABLE Persons

ADD UNIQUE (P\_Id);

ALTER TABLE Persons

ADD CONSTRAINT uc\_PersonID UNIQUE (P\_Id,LastName);

ALTER TABLE Persons

DROP INDEX uc\_PersonID;

CREATE TABLE Orders

(

O\_Id int NOT NULL,

OrderNo int NOT NULL,

P\_Id int,

PRIMARY KEY (O\_Id),

CONSTRAINT fk\_PerOrders FOREIGN KEY (P\_Id)

REFERENCES Persons(P\_Id)

);

ALTER TABLE Persons

ADD CHECK (P\_Id>0);

ALTER TABLE Persons

ADD CONSTRAINT chk\_Person CHECK (P\_Id>0 AND City='Sandnes');

ALTER TABLE Persons

ALTER City SET DEFAULT 'SANDNES';

CREATE INDEX index\_name

ON table\_name (column\_name);

CREATE UNIQUE INDEX index\_name

ON table\_name (column\_name);

CREATE INDEX PIndex

ON Persons (LastName, FirstName);

ALTER TABLE table\_name DROP INDEX index\_name;

DROP TABLE table\_name;

DROP DATABASE database\_name;

TRUNCATE TABLE table\_name;

ALTER TABLE table\_name

MODIFY COLUMN column\_name datatype;

ALTER TABLE Persons AUTO\_INCREMENT=100;

CREATE VIEW view\_name AS

SELECT column\_name(s)

FROM table\_name

WHERE condition;

CREATE OR REPLACE VIEW view\_name AS

SELECT column\_name(s)

FROM table\_name

WHERE condition;

SELECT AVG(column\_name) FROM table\_name;

SELECT COUNT(column\_name) FROM table\_name;

SELECT MAX(column\_name) FROM table\_name;

SELECT MIN(alexa) AS min\_alexa FROM Websites;

SELECT SUM(count) AS nums FROM access\_log;

SELECT column\_name, aggregate\_function(column\_name)

FROM table\_name

WHERE column\_name operator value

GROUP BY column\_name;

SELECT site\_id, SUM(access\_log.count) AS nums

FROM access\_log GROUP BY site\_id;

SELECT Websites.name,COUNT(access\_log.aid) AS nums FROM access\_log

LEFT JOIN Websites

ON access\_log.site\_id=Websites.id

GROUP BY Websites.name;

SELECT Websites.name, Websites.url, SUM(access\_log.count) AS nums FROM (access\_log

INNER JOIN Websites

ON access\_log.site\_id=Websites.id)

GROUP BY Websites.name

HAVING SUM(access\_log.count) > 200;

SELECT UCASE(name) AS site\_title, url

FROM Websites;

SELECT LCASE(column\_name) FROM table\_name;

SELECT MID(name,1,4) AS ShortTitle

FROM Websites;

SELECT name, LENGTH(url) as LengthOfURL

FROM Websites;

SELECT ROUND(column\_name,decimals) FROM table\_name;

SELECT name, url, Now() AS date

FROM Websites;

SELECT name, url, DATE\_FORMAT(Now(),'%Y-%m-%d') AS date

FROM Websites;

应届生面试要点总结（16）SQL要看得懂/基本的要会写

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关应届生面试要点总结（10）操作系统相关

相关应届生面试要点总结（8）简单算法

相关应届生面试要点总结（7）网络相关

相关应届生面试要点总结（6）数据库相关

相关应届生面试要点总结（5）Spring相关

相关应届生面试要点总结（3）JVM相关

相关应届生面试要点总结（2）Java IO

相关应届生面试要点总结（1）JAVA基础

相关应届生面试要点总结（16）SQL要看得懂/基本的要会写

相关应届生面试要点总结（15）分布式相关