SQL对大小写不敏感
基本语句:
SELECT *
FROM table_name
WHERE condition
DISTINCT 关键字:从列中选取唯一不同的值,去掉某一列的重复值。
示例:
SELECT DISTINCT country FROM websites;
WHERE子句中的运算符有like、in、between,where中的条件子句可用and、or、not连接;
用法:
同时满足两个条件
select * from emp where sal>2000 and sal<3000;
满足其中一个条件的值
select * from emp where sal>2000 or sal>500;
满足不包含该条件的值
select * from emp where not sal>2000;
特殊条件判断:
select * from emp where comm is null;查询comm列中的空值 select * from emp where sal between 1500 and 3000;
select * from emp where sal in(5000,3000,1500);
select * from emp where ename like ‘M%’;查询名字以M开头的
select * from emp where ename like ‘%M_’;查询以M在倒数第二位的所有内容,下划线表示一个字符。
select * from emp where enamel not like ‘%M_’;not like 选取不匹配模式的记录
order by对结果进行排序
用法:
select * from websites
order by alexa;按照alexa排序,默认按升序排序
select * from websites
order by alexa desc;按照alexa降序排列,desc表示按降序排列
select * from websites
order by country,alexa;按照多列进行排序
insert into 向表中插入新纪录
用法:
insert into websites(name,url,alexa,country)
values(‘baidu’,‘http://www.baidu.com’,‘4’,‘CN’);指定列名及被插入的值
insert into websites
values(‘baidu’,‘http://www.baidu.com’,‘4’,‘CN’);不指定列名,只提供插入的值
Update 更新表中的记录
用法:
update websites
set alexa=‘5000’,country=‘USA’
where name=‘菜鸟’;
省略where子句时,将更新websites表中所有行的alexa,country
delete 删除表中的记录,用于删除表中的行
用法:
delete from websites
Where name=‘百度’,and country = ‘CN’;删除指定条件的行
delete from websites;删除所有数据