复习SQL语句

1.复习SQL语句(结构化查询语言)

1.SQL语句分类

DDL数据定义语言
DML数据操作语言
DCL数据控制语言
DQL数据查询语言

2.SQL语句

1)库
	create database aaa; //创建数据库
	
	drop database aaa;   //删除数据库
	
	use aaa; 			//切换使用数据库
	
	show databases;		//显示数据库

2)表
	create table abc(		//创建表结构
		name text,
		sex varchar(100),
		age int(10)
	);
	
	drop table abc;		//删除表
	
	desc abc;			//显示表结构
	
3)数据
	insert into abc(name,sex,age) values('abc','nan',20);	//插入数据
		
	update abc set age = 101 【where name = 'abc'】			//更改数据
	
	delete from abc 【where name = 'abc'】					//删除数据
	
	select * from abc 【where age > 18】						//查询数据
	
4）约束
	主键：	primary key
	自增： 	auto_increment
	非空：  not null
	唯一：  unique
	
	create table abc(
		id	int(100) primary key auto_increment,    主键自增
		name varchar(100) not null unique,			非空唯一
		sex varchar(100),
		age int(10)
	);

5）查询
一、基本查询
	select * from emp;
	
	select empno,ename,sal from emp;
	
	select distinct deptno from emp;
	
	select sal*1.5 from emp;
	select concat('$',sal) from emp;
	select concat(sal,'RMB') from emp;
	select ifnull(comm,0)+1000 from emp;
	select sal as 奖金 from emp;

二、条件查询
	select * from emp where deptno = 20;
	select * from emp where deptno != 20;
	select * from emp where sal >=20000;
	select * from emp where sal >=10000 and sal <=20000;
	select * from emp where sal<=10000 or sal >=40000;
	select * from emp where comm is null;
	select * from emp where comm is not null;
	select * from emp where sal between 20000 and 40000;
	select * from emp where deptno in(10,30);

三、模糊查询   _某一个字符    %多个字符
	select * from emp where ename like '张_';
	select * from emp where ename like '张%';
	select * from emp where ename like '_一_';

四、排序
	select * from emp order by sal asc;
	select * from emp order by sal desc;

五、聚合函数
	select max(sal) from emp;
	select min(sal) from emp;
	select count(ename) from emp;
	select sum(sal) from emp;
	select avg(sal) from emp;

六、分组
	select deptno,count(ename) from emp group by deptno;


总结：
	select deptno,count(ename)
	from emp
	where sal >= 10000
	group by deptno
	order by deptno asc;