1.DISTINCT:去重复,distinct必须写在前面,否则报错
例句:select DISTINCT name from guest
2.where
例如:查找性别为女的所有人
select * from user where sex="女"
查找工资在2000到3000之间的所有员工
Select * from emp where sal > 2000 and sal < 3000; 例如查找姓名中有‘明的人’ select * from user where name like '%明'选取 name 以 "G" 开始,然后是一个任意字符,然后是 "o",然后是一个任意字符,然后是 "le" 的所有网站:select * from user where name like '明%'选取表中前两条数据select * from user where name like '%明%' is null
查找某一列为空的所有人Select * from emp where comm is null;between and 在什么之间
not between and 不在什么之间
例如:Select * from emp where sal between 1500 and 3000;in
例如:查找工资为5000,3000,1500的员工Select * from emp where sal in (5000,3000,1500);and:表示两个条年间都成立
select * from guest where name like '%明' and gender='男'
or:表示满足两个条件中的任意一个
select * from guest where name like '%明'or gender='男'
and 和 or 结合使用
select * from guest where age>20 and (gender='男'OR gender='女')
order by按升序排序
select grade from restaurant ORDER BY grade
desc按降序排序
select grade from restaurant ORDER BY grade desc
insert into 插入,有两种方式:
方式一,给所有的字段插值
INSERT INTO table_name
VALUES (value1,value2,value3,...);
方式二,给部分字段插值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
update更新已有的数据:
update guest set name='李四'where name='张三'
delete删除
例如删除所有数据:
DELETE FROM table_name;
或
DELETE * FROM table_name;
删除一行:
DELETE FROM table_name
WHERE some_column=some_value;
通配符SELECT * FROM Websites LIMIT 2 ;
SELECT * FROM Websites WHERE name LIKE 'G_o_le';
选取 name 以 "G"、"F" 或 "s" 开始的所有网站:
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';
选取 name 以 A 到 H 字母开头的网站
SELECT * FROM Websites WHERE name REGEXP '^[A-H]';
选取 name 不以 A 到 H 字母开头的网站:
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';
别名
select g.name,g.gender,r.info from guest g,restaurant_judge r where g.id=r.guest_id
JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段
select g.name,g.gender,r.info from guest g JOIN restaurant_judge r ON g.id=r.guest_id
UNION:合并两个或多个select语句的结果集
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL:允许有重复值
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据(也有重复的值):
SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
SELECT INTO 语句从一个表复制数据,然后把数据插入到另一个新表中。
CREATE TABLE 新表 SELECT * FROM 旧表 复制所有的列插入到新表中: SELECT * INTO newtable [IN externaldb] FROM table1;只复制希望的列插入到新表中
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
创建 Websites 的备份复件:
SELECT * INTO WebsitesBackup2016 FROM Websites;
复制多个表中的数据插入到新表中:
SELECT Websites.name, access_log.count, access_log.date INTO WebsitesBackup2016 FROM Websites LEFT JOIN access_log ON Websites.id=access_log.site_id;
SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可
SELECT * INTO newtable FROM table1 WHERE 1=0;
约束
在某列增加unique约束
ALTER TABLE Persons ADD UNIQUE (P_Id)
撤销某列的unique约束
ALTER TABLE Persons DROP INDEX uc_PersonID
撤销主键
ALTER TABLE Persons DROP PRIMARY KEY
外键
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
表已创建添加外键
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
撤销外键:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
check约束:用来限定列的范围
添加check约束:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
ALTER TABLE Persons ADD CHECK (P_Id>0)
撤销check约束:
ALTER TABLE Persons DROP CHECK chk_Person
CREATE INDEX:创建索引允许使用重复值
CREATE INDEX index_name ON table_name (column_name)CREATE UNIQUE INDEX:在表上创建一个唯一的索引。不允许使用重复的值:唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name ON table_name (column_name)
创建多个索引:
删除表:
DROP TABLE table_name
删除表中的数据,不删除表本身:
TRUNCATE TABLE table_name
删除表中的索引:
Auto Increment:表中插入值时,自动生成一个唯一的数字
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
) sql函数:
1.平均分:
SELECT AVG(column_name) FROM table_name
2.查看高于平均分
SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);
3.COUNT() 函数返回匹配指定条件的行数。
SELECT COUNT(column_name) FROM table_name;
4.count(*)返回表中的记录数
SELECT COUNT(*) FROM table_name;
5.COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目: