SQL入门基础

建立表

CREATE TABLE Persons
(
    Id_p int,
    Last_name varchar(255),
    Frisr_name varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT – 插入数据

向表中插入行数据 :

INSERT INTO Persons VALUES (1, 'KUANG', 'YANG', 'WUXIUniversity', 'WuXi');

在指定的列中插入数据:

INSERT INTO Persons (Address, City) VALUES ('北京路32号', '上海市');

SELECT – 查询数据

查询表中数据, *表示查询所有字段:

SELECT * FROM Persons;

按照条件查询数据 ：

SELECT City FROM Persons;
SELECT Last_name, Frisr_name FROM Persons;

DISTINCT筛选字段中唯一值:

SELECT City from Persons;
SELECT DISTINCT City FROM Persons;

后续内容需要：

INSERT INTO Persons VALUES(2, '小米', '冯', '1栋333号', '北京');
INSERT INTO Persons VALUES(2, '小李', '李', '4栋01号', '上海');
INSERT INTO Persons VALUES(2, '小成', '张', '33栋33号', '北京');
INSERT INTO Persons VALUES(2, '小同', '康', '43栋9号', '深圳'); 

根据条件查询 :

SELECT * FROM Persons WHERE City='北京';
SELECT * FROM Persons WHERE Id_p>1;
SELECT * FROM Persons WHERE Id_p=1;

and 和 or逻辑条件查询:

SELECT * FROM Persons WHERE City='北京'AND Id_p>1;
SELECT * FROM Persons WHERE City='上海'OR Id_p>10;

SELECT * FROM Persons WHERE (City='北京' AND Id_p>1) OR Last_name='小成';

ORDER BY排序 默认为升序，可以使用 DESC 关键字降序:

SELECT * FROM Persons ORDER BY City;
SELECT * FROM Persons order BY Last_name;
SELECT * FROM Persons ORDER BY City DESC;

UPDATE – 更新数据

更新 字段City=’上海市’，的其他指定字段例如：Id_p=1, Last_name=’小旷’, Frisr_name=’杨’:

UPDATE Persons SET Id_p=1, Last_name='小旷', Frisr_name='杨' WHERE City='上海市';

DELETE – 删除数据

删除字段City=’WuXi’的行：

DELETE FROM Persons WHERE City='WuXi';

不删除表，删除所有行:

DELETE FROM Persons;
SELECT * FROM Persons;

DROP TABLE – 删除表:

DROP TABLE Persons;```

本作品采用《CC 协议》，转载必须注明作者和本文链接