SQL 常用关键字

//create database(创建数据库)
create database my_db

//创建表
create table user( id int(11) not null auto_increment, name varchar(50) default null, password varchar(50) default null, primary key(id) )

//约束 not null (不为null值) unique (唯一) primary key(主键) foreign key(外键) check(查询约束) default(默认值约束)

//create index (创建索引)
create index ff on user (name,Address)

//distinct 关键字查询字段的唯一值记录，重复的字段值只会显示一条查询记录
SELECT distinct name FROM user

//and(并且) or(或)查询where语句的条件
SELECT * FROM user where City=”上海” and name=”admian3”
SELECT * FROM user where City=”上海” or name=”admian3”

//如果and 和or 结合来用的时候则要用到()处理
SELECT * FROM user where (City=”上海” or name = “admian3”) and name=”xxx”

//order by(关键字排序)(按照名字的顺序来做升序排序)
select * from user order by name
//order by 后面可以接多个排序列表中间用,隔开
select * from user order by name,City,….
//desc (降序的关键字)
select * from user order by name desc
//asc (升序的关键字 order by 后面不跟有降序的关键字那么就是默认为升序)
select * from user order by name desc,City asc

//添加数据
insert into user(name,password,Address,City,OrderNumber)values(“ddsds”,”ssss”,”广东”,”广州”,1234656)
//更新数据
upate user set name=”ffff” where id =6 //删除数据 delete from user where id = 6

//limit (关键字是查询数据库中多少条数据 再mysql里面用，如果是sql server用则用top )
select * form user limit 2

//in (关键字允许我们在where语句中存在多个条件)
select * from user where name in(“xxxxs”,”zdasdsa”)

//between ….and..(关键字是处理在什么之间到什么之间的操作)
select * from user where name between “fffff” and “ssss”

//as(关键字是用于给表或数据库起一个别名)
select * name as n,password as p from user

//inner join (关键字使用了内连接来查询两个表的数据,其中还有 left join(左连接) right join(右连接) full join(左右连接))
select u.name,u.Address,n.number from user as u “inner join” number as n on u.id = n.user_id order by u.Address

//union(命令将两条sql语句拼接起来)
select * from user union select * from number

//select into(关键字是为了把一个表的数据插入到另一个表当中)
select * into user from number