sql常用语句

1.创建部门表
create table dept(
deptno int primary key,
dname varchar(9),
loc varchar(10)
)

2.创建员工表
create table employees(
empno int primary key,
name char(10) not null,
deptno int,
manager int,
hiredate date,
salary numeric(7,2)
)

3.创建经理表
create table managers(
empno int primary key,
title
)

4.insert插入
insert into 表名 values( );
向员工表插入记录，包括所有字段的值。
insert into emoloyees values(1,'张三'.......)

插入和修改数据不能违反主键约束

5.select查询
查询员工表所有信息
select * from employees;

查询员工表某一字段的信息
select salary,name from employees

6.distinct去掉重复的值
select distinct manager from employees

7.where用法
select name,salary from employees where deptno=3;

8.and or 运算符
select * from employees where (deptno=3 or deptno) and salary>=5000;

9.like的用法
select * from employees where name like 'li%'

10.in匹配多个值。

11.between指定范围
select name from employees where hiredate between '2013-01-01' and '2013-12-31';

12,order by 排序(降序）
select name,salary from employees order by salary desc;

13.update更新数据
update employees set deptno=2,manager=4 where empno=4;

14.delete 删除数据
delete from employees where empno=9;

15.index索引
create index in_name on employees(name);

16.view视图
create view view_name as select name,salary from employees where hiredate<'2015-01-01'
select * from view_name

更新视图view_name
update view_name

17.null的用法
select * from employees where manager is not null.

18.别名。
select name 姓名，salary 工资 from emploees e;

19.join连接。内连接inner join 左连接left join 右连接