数据库常用语句

一、常用语句

1、DQL Data Query Language

select avg(sal) from emp where sal > 1000 group by deptno having avg(sal) > 1500 order by avg(sal) desc;

2、DML Data Manipulation Language

insert into emp (ename,sal) values ('a',3000);

update emp set sal = sal*2 where deptno = 10;

delete ename from emp where deptno = 10;

DDL DCL 自动commit

正常断开连接自动commit

非正常断开连接 rollback

3、DCL Data Control Language

alter user scott account unlock

conn sys/change_on_install as sysdba

grant create table,create view to scott;

create database default character set utf8 collate utf8_general_ci;

4、DDL Data Definition Language

create table student(

id int primary key auto_increment,

name varchar(20) constraint stu_name_nn not null,

sex int,

age int check(age >= 10),

sdate date,

grade int default 1,

class int references class(id),

email varchar(50) constraint stu_email_uni unique

constraint stu_class_fk foreign key(class) reference class(id),

constraint stu_name_email_uni unique(name,email)

)engine=innodb default charset=utf8 auto_increment=1;

alter table student add constraint stu_id_pk primary key(id);

drop table stu;

5、视图：

create view v$_avg_grade_info as

select avg(grade) avg_grade,deptno from (

select ename,grade,deptno from emp e join salgrade s on (e.sal between s.losal and s.hisal)

) group by deptno;

6、三范式：

要有主键，列不可分

不能存在部分依赖

非主键内容不能出现在其他表上

7、常用SQL函数

distinct、lower、substr(ename,2,3)、chr(65)、ascii('A')、round(23.652,2)、to_char(sal,'L/$999,999.999')、

to_char(sal,'L/$000,000.000)、to_char(sysdate,'YYY-MM-DD HH24:MI-SS')、to_data('1981-2-20 12:34:29','YYYY-MM-DD HH24:MI:SS')、

to_number('$1,250.00','$9,999.99')、nvl(comm,0)、dual、min、max、avg、count、sum、rownum、

select * from user order by age limit 3,2;

8、平均薪水等级最低的部门的部门名称

表结构：emp：id、ename、sal，deptno

dept：deptno、dname

select d.dname from dept d where d.deptno = (

select deptno from (

select avg(sal) avg_sal,deptno from emp group by deptno

) t1 join (

select min(avg_sal) min_avg_sal, deptno from (

select avg(sal) avg_sal,deptno from emp group by deptno

) t2 on (t1.avg_sal = t2.min_avg_sal)

);

9、JDBC

try {
	Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}
try {
	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/shopping?user=root&password=root&useSSL=false");
	Statement stmt = conn.createStatement();
	stmt.excuteUpdate(sql);
	stmt.excuteQuery(sql);
	while(rs.next()) {
		System.out.println(rs.getString("ename"));
	}
	PreparedStatement pstmt = conn.prepareStatement(insert into dept2 values (?,?,?)");
	pstmt.setInt(1,no);
	pstmt.setString(2,ename);
	pstmt.setStirng(3,intro);
	pstmt.executeUpdate();
} catch (SQLException e) {
	e.printStackTrace();
}