0..数据库语言分为两个部分:
DML(数据操作语言) | DDL(数据定义语言) |
select:从数据库表中获取数据 update:更新数据库表中的数据 delete:删除数据库表中的数据 insert into:向数据库表中插入数据 | create datbase:创建新的数据库 alter database:修改数据库 create table:创建新表 alter table:变更数据库表 drop table:删除表 create index:创建索引 drop index:删除索引 |
假设有一个叫persons的表,其列名称包括id lastname firstname address city
1.select 语法
1.1.选取所有 select* from persons
1.2选取部分:select lastname,firstname form persons
2.distinct语法:在列中仅选取不重复的值
select distinct lastname from persons
3.where语法:有条件的筛选
例:筛选lastname是jack的人
select* from persons where lastname='jack'(注意是单引号)
筛选id在5到8之间的人
select* from persons where id between 5 and 8(不加引号)
同时筛选frisstname 和/或 lastname
select* from persons where firstname='jack' and lastname ='ma';
select* from persons where firstname='jack' or firstname ='pony';
结合使用:
select* from persons where (firstname='jack' and lastname ='ma') or firstname ='pony';
4.orderby 语法:改变顺序
以城市字母顺序进行排序
select* from persons orderby city;
以逆城市字母顺序显示
select* from persons orderby city desc;
排列组合:
select* from persons orderby city,id(城市一样则按id排序)
select* from persons orderby city desc,id asc;
5.insert into用法
插入一行(里面元素没必要全部写满)
insert into persons values(1,'bill','gates','usa','newyork');
6.update用法:修改表中数据
6.1更新某一行的某一列:如已知lastname,添加一个firstname
update persons set fristname='li' where lastname='goudan';
6.2更新某一行的若干列:
update persons set fristname='li' ,city='njin' where lastname='goudan';
7.delete用法:
删除某行 :delete from persons where lastname='goudan';
删除所有行:delete* from table_name;