SQL语句整理

• sql语句对大小写不敏感，以";"隔离语句

select语句

• SELECT name,country FROM Websites;
• SELECT * FROM Websites;
• SELECT DISTINCT country FROM Websites;

where子句的使用

and、or、in、like、between

• SELECT * FROM Websites WHERE country='CN';
• SELECT * FROM Websites WHERE id=1; int和str应区分开
• Select * from emp where sal > 2000 and sal < 3000; =Select * from emp where sal between 2000 and 3000
• Select * from emp where sal > 2000 or comm > 500;
• Select * from emp where comm is null;
• Select * from emp where sal in (5000,3000,1500);
• Select * from emp where ename like 'M%';以M开头， % 表示多个字值，_ 下划线表示一个字符；
• SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');
  -SELECT * FROM Websites ORDER BY alexa DESC/ASC;
• SELECT * FROM Websites ORDER BY country,alexa;

insert into的使用

• INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');其余列默认为0

update的使用

• UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程'; set sql_safe_updates=1 安全模式打开状态

删除的使用

• DELETE FROM Websites WHERE name='Facebook' AND country='USA';
• DELETE FROM test; 仅删除表的内容，表的结构、空间还存在
• DROP test删除表的内容、结构，释放空间
• TRUNCATE test; 删除表的内容、释放空间，表的结构还在

select top/limit/rownum子句

mysql

• SELECT * FROM Websites LIMIT 2; 选出头两条记录
• SELECT * FROM Websites LIMIT 2,2;选出第3，4两条记录
• SELECT TOP 50 PERCENT * FROM Websites;选出前50%的记录
• SELECT TOP 5 * FROM Websites;选出前5条记录

oracle

• SELECT * FROM Persons WHERE ROWNUM <=5;

like操作符

• SELECT * FROM Websites WHERE name LIKE 'G%'; name列中以G开头的记录
• SELECT * FROM Websites WHERE name LIKE '%k';name列中以k结尾的记录
• SELECT * FROM Websites WHERE name LIKE '%oo%';name列中包含oo的记录
• WHERE name NOT LIKE '%oo%';name列中不包含oo的记录
• SELECT * FROM Websites WHERE name LIKE '_oogle';

[charlist]通配符

• 选取name为"G",“F”,"s"开头的记录
  select * from table where name regexp '^[GFs]'
• 选取 name 以 A 到 H 字母开头的记录
  select * from table where name regexp '^[A-H]'
• 选取 name 以不是 A 到 H 字母开头的记录
  select * from table where name regexp '[^A-H]'
• 选取name以AB结尾的记录
• select * from table where name regexp '[AB]$'

in操作符

• 允许在where子句中包含多个条件
• select * from table where city in ('上海','北京')

between操作符

• 选取介于两个值之间的记录
• select * from table where name not between 'A' and 'H'

SQL别名

• 为表名、列名指定别名
• select city_name as N,city_contry as C from table as T

inner join

• 求两个表的并集
• select a.name,b.city from table1 as a inner join table2 as b on a.name=b.name

left join

• 以左表结构为准，右表无匹配记录会显示null，左表记录数可能会增加
• select a.country ,b.city from a left join b on a.country = b.country

right join

• 以右表结构为准，左表无匹配记录会显示null，右表记录数可能会增加
• select a.country,b.city from a right join b where a.city=b.city

inner join

• 返回两张表中都满足条件的记录

full out join

• 取并集，对方没有的字段取null

union

• 合并两个或多个select语句的结果(select的结果列数相同，数据类型相同)
• select name from table1 union select name from table2 不含重复
• select name from table1 union all select name from table2 所有结果

select into

• 将旧表中的内容复制到新表中，新表本来不存在
• select name,city into new_table from table

insert into select

• 将旧表中的内容复制到表中，表本来存在
• iinsert into table1(name,city) select name,city from table

create database my_database 创建数据库

创建新表

create table new_table (name char,age int)

sql约束constraint：规定表中的数据规则

not null

• create table order (id int not null,user char)表建立时添加约束
• alter table order modify user char not null给已有表添加约束
• alter table order modify user char删除约束

unique

• 命名unique约束，并为多个列建立约束
  create table order (id int not null,user char not null,time timestamp) constraint id_user unique(id,user)
• 修改约束
  alter table order add unique(time)
alter table order add constraint c_time unique(time)
• 删除约束
  alter table order drop constraint c_time

primary key

• 主键不能为空，必须唯一，有且仅有一个
• 命名约束，并为多个列建立约束
  create table order(id int,user char not null,time timestamp,constraint primary key(id,user))
• 修改约束
  alter table order add primary key (time)
alter table order add constraint primary_c primary key(id,user)
• 删除约束
  alter table order drop primary_c

foreign key

• 一个表中的外键指向另一个表中的唯一键
• 预防破坏表之间连接的行为
• 防止非法数据插入外键列
• 新建约束
  create table order (id int not null,name str not null,constrain primary key(id),foreign key(name) references user(name))
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
• 删除约束
  ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

check

• 用来限制列中值的范围
  ALTER TABLE order ADD CHECK (id > 0 and name= 'hhhhh')
• 删除check用drop

default

• 用于向列中插入默认值
• ALTER TABLE order ALTER o_iname DEFAULT '奚金霞'
• ALTER TABLE order ALTER o_name DROP DEFAULT

创建索引

• 更加快速高效地查询数据
• 用户无法看到索引，他只被用来加速查找
• 一般在常常被搜索的列或者表上创建索引，因为更新一个有索引的表要比没有索引的表花费更多的时间
  CREATE INDEX new_index ON order (o_id,o_name)创建索引
  ALTER TABLE order DROP INDEX new_index删除索引

删除数据库和表

DROP DATABASE new_database
DROP TABLE order
TRUNCATE TABLE order

修改数据表中的列：alter

ALTER TABLE order ADD product char 增加列
ALTER TABLE order ALTER product varchar修改列
ALTER TABLE order DROP product删除列

视图

• 基于sql语句的结果集可视化的表
• 视图提供了统一访问数据的接口，用户无需接触底层的表结构，方便了用户，也加强了安全性
  CREATE VIEW new_view AS SELECT.......创建视图
• 修改视图可通过重建视图实现
  DROP VIEW new_view删除视图

DATE函数

• 常见数据类型—date、datetime、timestamp

NULL值

• 是不可比较的，代表遗漏的未知数据
• null的查找
  SELECT * FROM order WHERE product IS NULL

NULL函数

• 将null值转化为0,不为null则取原值
  SELECT o_name,price*IFNULL(num) AS total FROM order

average函数

• 返回数值列的平均值
• 选择order中单价price的平均值
  SELECT AVERAGE(price) FROM oder
• 选择单价price高于平均单价的产品名product和id
  SELECT id,product FROM order WHERE price > (SELECT AVERAGE(price) FROM oder )

count函数

• 返回满足条件记录的行数
• order的行数
  SELECT COUNT(*) FROM order
• 返回收货人为奚金霞的产品名的数量
  SELECT COUNT(product) as product_num FROM order WHERE o_name='奚金霞'
• 返回收货人为奚金霞的所有产品（每个产品名只返回一次，不重复）
  SELECT COUNT(DISTINCT product) FROM order WHERE o_name='奚金霞'
• 返回o_name列中不为空的记录数
  SELECT COUNT(o_name) FROM order

first函数

• 返回指定列中第一个记录的值
  SELECT FIRST(o_name) FROM order 仅仅MS Access支持此函数
• mysql可使用limit
  SELECT o_name from order LIMIT 1

last函数

• 返回指定列中最后一个记录的值
• mysql实现
  SELECT o_name FROM order ORDER BY id DESC LIMIT 1

max函数

• 返回指定列的最大值

min函数

• 返回指定列的最小值

sum函数

• 返回数值列的总数

group by语句

• 根据一个列或多个列对结果集进行分组
• 每个产品的购买人数
  SELECT product,count(o_name) FROM order GROUP BY product

having子句

• where关键字无法和聚合函数一起使用(where可在分组前使用，分组后的使用的话会弄乱分组结果)，having可以筛选分组后的各组数据
• 购买人数超过100的产品及其购买人数
  SELECT product,count(o_name) FROM order GROUP BY product HAVING count(o_name)>100

exists运算符

• 用于判断查询子句是否有记录，如果有一条或多条记录则返回True，否则返回false
• 查找访问量大于200的网站名、网站url
  SELECT Websites.name, Websites.url FROM Websites WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

UCASE函数

• 将列中的值转换为大写
  SELECT UCASE(o_name) FROM order 仅限英文

LCASE函数

• 将列中的值转换为小写
  SELECT LCASE(o_name) FROM order

MID函数

• 从文本字段中提取字符
  SELECT MID(o_name,1,4) FROM order)返回列o_name的前4个字符

LENGTH函数

• 返回文本字段中值的长度
  SELECT LENGTH(o_name) AS len_name FROM order

round函数

• 把数值字段四舍五入为指定的小数位数
  SELECT product,ROUND(price,0) AS int_price FROM order 将产品价格取整

now函数

• 返回系统当前的日期和时间
  SELECT id,o_name,NOW() AS date from order

format函数

• 对字段的显示进行格式化
  SELECT id,o_name,data_formate(NOW(),%Y-%M-%D) AS date FROM order

MS sqlserver 使用平台：LINUX、windows

mysql使用平台：windows、linux/unix、mac os

MS access适用平台：windows，小型网站

重点记忆

1、有查询有修改的话需要对查询结果进行表命名

2、update中set赋值使用replace实现

3、修改表名

4、两张表中的不同的类名可以直接使用，相同的列名用表名前缀区分

5、表的创建

6、创建唯一索引

7、创建强制索引

8、创建视图

9、创建触发器

9、创建外键约束

10、update与delete不同，不需要对选择结果进行重命名

11、特殊字符用双引号

12、查询字符串中某字符出现的次数

13、字符串截取长度

14、group_concat的使用

15、刁钻题

16、in的强一致性

17、date_format的使用