SQL语句

select 选择

select * from table order by intime asc;---升序

select * from table order by intime desc;---降序

select * from table order by intime desc,modtime asc;

 

select top 20 * from table order by id;

select top 50 percent * from table;-----表中选取 50% 的记录

select * from table limit 20;

 

select distinct name from table;

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。

select * from table where name='李明';

select * from table where age>16;

select * from table where intime>='2018-5-30 18:26:50' and intime<='2019-5-30 18:26:50';

select * from table where firstname='Thomas' and lastname='Carter';

select * from table where firstname='Thomas' or lastname='Carter';

select * from table where (firstname='Thomas' or firstname='William')  and  lastname='Carter';

 

select * from table where name like '李%';

select * from table where name like '%明';

select * from table where name like '%明%';

select * from table where name not like '%明%';

select * from table where firstname like '_eorge';----选取名字的第一个字符之后是 "eorge" 的人

select * from table where name in('王刚','李宝');

select * from table where age between 15 and 20;

select * from table where age not between 15 and 20;

select name from name_table as table;

select first_name as name from table;

select n.id,n.name,s.score,s.time from name_table as n,score_table as s where n.id=s.id;

select n.id,n.name,s.score,s.time from name_table n inner join score_table s on n.id=s.id order by s.time;

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

select id from name_table left join score_table on name_table.id=score_table.id;

select id from name_table right join score_table on name_table.id=score_table.id;

select name_table.id,name_table.name,score_table.score,score_table.time from name_table full join score_table on name_table.id=score_table.id;

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2   请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

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

SELECT * INTO Persons_backup FROM Persons;

SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing';

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P;创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息.

insert 插入

insert into table values ('李明',15,'数学')；

insert into table ('姓名','年龄','学科') values ('李明',15,'数学')；

update 更新

update table set name='小红' where id='1001';

update table set name=‘小雨’,age=15 where id='1002';

delete 删除

delete from table where name='李华';

delete from table;----可以在不删除表的情况下删除所有的行(或delete * from table)

create 创建

CREATE DATABASE my_db;

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

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

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)
)

ALTER TABLE Persons
ADD UNIQUE (Id_P)