SQL基础语法

基本增,删,查,改操作

假设存在数据库为WebApplication 表名User 如下所示

ID	Username	Password	Access
1	admin	admin@nimda	true
2	Alice	alice@ecila	false
3	Bob	bob@bob	false
4	Cindy	cindy@ydnic	false

其中ID为自增序列，且拥有主键（PRIMARY KEY）约束，以及唯一(UNIQUE)约束;并设置自动增量(auto-increment)
扩展:1.自增

Database	SQL
MySQL	AUTO_INCREMENT
MS SQLserver	IDENTITY(1,1)
MS Access	AUTOINCREMENT
Oracle	SEQUENCE对象

扩展:2.主键约束

Database	SQL
MySQL	PRIMARY KEY(ID)
Oracle MS Access MS SQLserver	PRIMARY KEY

扩展:3.唯一标识约束

Database	SQL
MySQL	UNIQUE (ID)
Oracle MS Access MS SQLserver	UNIQUE

语法使用

---

查 要求：查到有权限的用户名以及密码

select Username,Password from User where Access;
select Username,Password from User where Access = true;

ID	Username	Password	Access
1	admin	admin@nimda	true

增 要求：用户名Denis 密码 1234 没有权限

insert into User (ID,Username,Password,Access) set values(5,"Denis","1234",false);

ID	Username	Password	Access
1	admin	admin@nimda	true
2	Alice	alice@ecila	false
3	Bob	bob@bob	false
4	Cindy	cindy@ydnic	false
5	Denis	1234	false

删 要求：删除密码为1234 并且没有权限的用户

delete from User where Password = "1234"  and Access <> true;
delete from User where Password = "1234"  and Access != true; --Access无!运算符

ID	Username	Password	Access
1	admin	admin@nimda	true
2	Alice	alice@ecila	false
3	Bob	bob@bob	false
4	Cindy	cindy@ydnic	false

改 要求：用户名为Bob改为有权限的用户

update User set Access = true where Username = "Bob";

ID	Username	Password	Access
1	admin	admin@nimda	true
2	Alice	alice@ecila	false
3	Bob	bob@bob	true
4	Cindy	cindy@ydnic	false