SQL语句

SELECT LastName,FirstName FROM Persons
SELECT * FROM 表名称


SELECT DISTINCT 列名称 FROM 表名称 //DISTINCT 用于返回唯一不同的值
SELECT DISTINCT Company FROM Orders


SELECT * FROM Persons WHERE City='Beijing'


SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'


INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)


UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'


DELETE FROM Person WHERE LastName = 'Wilson'
delete FROM table_name


SELECT column_name(s) FROM table_name LIMIT number
SELECT * FROM Persons LIMIT 5


SELECT * FROM Persons WHERE City LIKE 'N%'
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'


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


SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'


SELECT column_name(s) FROM table_name AS alias_name //表Alias 语法
SELECT column_name AS alias_name FROM table_name //列Alias 语法


SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //存在至少一个匹配时
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName //只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。


SELECT E_Name FROM Employees_China union SELECT E_Name FROM Employees_USA
SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA


SELECT * INTO Persons_backup FROM Persons
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P //会创建一个名为 "Persons_Order_Backup" 的新表


CREATE DATABASE my_db


CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


UNIQUE (Id_P)
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons ADD UNIQUE (Id_P)
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons DROP INDEX uc_PersonID


PRIMARY KEY (Id_P)
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons DROP PRIMARY KEY


FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders


CHECK (Id_P>0)
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
ALTER TABLE Persons ADD CHECK (Id_P>0)
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
ALTER TABLE Persons DROP CHECK chk_Person


City varchar(255) DEFAULT 'Sandnes'
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
ALTER TABLE Persons ALTER City DROP DEFAULT


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


ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name


P_Id int NOT NULL AUTO_INCREMENT,
ALTER TABLE Persons AUTO_INCREMENT=100 //其他的值起始


SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL


SELECT AVG(OrderPrice) AS OrderAverage FROM Orders


SELECT COUNT(*) FROM table_name //返回表中的记录数：
SELECT COUNT(DISTINCT column_name) FROM table_name//返回指定列的不同值的数目：
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'


SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders //返回指定的字段中第一个记录的值。


SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders //返回指定的字段中最后一个记录的值。


SELECT MAX(column_name) FROM table_name //返回一列中的最大值。NULL 值不包括在计算中。


SELECT MIN(column_name) FROM table_name //MIN 函数返回一列中的最小值。NULL 值不包括在计算中。


SELECT SUM(column_name) FROM table_name


SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer


SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000 //增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。


SELECT UCASE(LastName) as LastName,FirstName FROM Persons//把字段的值转换为大写。
SELECT LCASE(LastName) as LastName,FirstName FROM Persons


SELECT MID(City,1,3) as SmallCity FROM Persons //从文本字段中提取字符。


SELECT LEN(City) as LengthOfCity FROM Persons //返回文本字段中值的长度。


SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products //用于把数值字段舍入为指定的小数位数。


SELECT ProductName, UnitPrice, Now() as PerDate FROM Products //返回当前的日期和时间。12/29/2008 11:36:05 AM


SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products //用于对字段的显示进行格式化,12/29/2008


if exists(select * from students where 学号='1005')
begin
set @message='下列人员符合条件:'
print @message
set @name=(select 姓名 from students where 学号='1005')
print @name
end
else
    begin
        set @message='没有人符合条件'
        print @message
    end
go


drop table if exists xxx_book

//=====================================================
UPDATE table1 set age = REPLACE(age,22,17); //查找替换
UPDATE table1 set age = 18 WHERE age =24;
UPDATE table1 set sex = CONCAT("我是",sex,"的");//字段内容增加

UPDATE menu_static SET main_picture = CONCAT('menuPics/instantPot/mainPics/',main_picture) WHERE CHAR_LENGTH(main_picture)<10

UPDATE menu_static SET main_picture = CONCAT('menuPics/instantPot/mainPics/',main_picture) WHERE CHAR_LENGTH(main_picture)<10

UPDATE step_disciption_static SET pic_url = CONCAT('menuPics/instantPot/stepPics/',pic_url) WHERE CHAR_LENGTH(pic_url)<12 AND pic_url!=''


//----------------------
delete from A，B   //相当于内连接

update a left join b on a.id=b.a_id set a.title='aaaaa',b.body='bbbb' where a.id=1

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
或：
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;