Mysql 进阶操作

将已经存在表设置自动增长属性
alter table student change id id int not null auto_increment primary key;
(注：这个地方一定是原来就是自动增长的或者原来就一条记录，并且id值为1)

mysql查询其中分数最高的人名，我摸索出来一个方法，如下：
mysql> select name from student a where a.english>=(select max(english) from student b);
参照：http://zhidao.baidu.com/question/100646012.html

在qq群里面讨论，一个网友提出了这个：

select top 1 name from student order by english desc;但是mysql中不知道top，不知道在哪个数据库中可以运行。

 

下面是一些mysql的进阶操作方法：

在select中使用排序
1.跟列名
select * from student order by chinese;
select * from student order by chinese desc;

2. 别名
举例：按照学生的总分进行排序
select name, chinese+english+math 总分 from student order by 总分 desc;

3.序号
举例：按照学生的总分进行排序
select name, chinese+english+math 总分 from student order by 2 desc;

练习:
对数学成绩排序后输出。
select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出
select name, chinese+english+math 总分 from student order by 2 desc;

对姓李的学生总成绩排序输出
select * from student where name like '李%' order by (chinese+english+math);

合计函数－count(Oracle中叫组函数)
统计一个班级共有多少学生？
select count(*) from student;
select count(id) from student;
扩展：对于null值：count(列名)会自动滤空

统计数学成绩大于90的学生有多少个？
select count(*) from student where math>=90;

统计总分大于250的人数有多少？
select count(*) from student where chinese+english+math>250;

sum函数:
统计一个班级数学总成绩？
select sum(math) from student;

统计一个班级语文、英语、数学各科的总成绩
select sum(chinese) 语文总成绩,sum(english) 英语总成绩,sum(math) 数学总成绩 from student;

统计一个班级语文、英语、数学的成绩总和
select sum(chinese)+sum(english)+sum(math) 全班总分 from student;

统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
select avg(chinese) from student;

avg函数:
求一个班级数学平均分？
select avg(math) from student;

求一个班级总分平均分
select avg(chinese+english+math) 平均分 from student;

max和min函数
求班级总分最高分和最低分（数值范围在统计中特别有用）
select max(chinese+english+math) 最高分, min(chinese+english+math) 最低分 from student;

分组函数： group by
按系统计学生的总成绩
select deptname,chinese+english+math from student group by deptName;

过程：先根据系名将同一系的学生分到一起，然后再对同组进行计算

扩展：按系统计学生的平均成绩
select avg(chinese+english+math) from student group by deptname;

例如:
select a,b,avg(c) from table group by a,b

语法要求：如果在select语句中，使用了合计函数，则select中未包含在合计函数中的列，都必须出现在group by后.

按系统计学生的总成绩,并且查询出总成绩大于250分的记录
问题：要对分组后的结果，再进行筛选
select deptname,sum(chinese+english+math) from student group by deptName having sum(chinese+english+math)>250;

如果group by作用于多列：
group by a,b ?? group by增强： group by rollup(a,b)

时间和日期函数：
select now() 当前时间, addtime(now(),'01:00:00') 一小时后
当前日期和当前的时间：
select current_date(), current_time();

一年后：
select now(), date_add(now(), INTERVAL 1 year) 一年后;
一年前:
select date_sub(now(), INTERVAL 1 year) 一年前 ;
返回两个时间相差的天数:
select datediff('2011-08-02','2010-09-12');
返回时间的年，月，日
select year(now()) 年, month(now()) 月, day(now()) 日;

字符串函数:
charset:返回字符集
select charset('hello world');

concat: 字符串连接函数
select concat('Hello',' World');
扩展:oracle中：连接符 ||: select 'Hello'||' World' from dual;

instr:在主串中查询子串的位置
select instr('Hello World','ll');

UCASE,LCASE
select ucase('hello world'),lcase('HELLO WORLD');

扩展：oracle,initcap:每个单词的首字母大写

left函数:
select left('Hello World',3);

length:求字符串的长度
select length('Hello World');
对于中文：字符数和字节数? "中国" length和lengthb

replace：替换函数
select replace('hello world','l','a');

对字符串进行比较
select strcmp('abc','abc'), strcmp('abcd','abc'),strcmp('abc','abcd');

求子串:
select substring('hello world',3),substring('hello world',3,3);

数值函数:
abs:绝对值
select abs(1),abs(-1);

bin:求二进制:
select bin(15);

向上取整和向下取整
select ceiling(10.12),floor(10.12);
扩展：四舍五入: round
截断?: trunc

hex: 十进制转十六进制
select hex(1125);

least:求参数列表中的最小值
select least(10,4,1,90);

表的约束:
1. 主键: 如果某列的值可以唯一标识该行，这列就为该标的主键
*. 不能为null
*. 不能重复
举例: 创建一个person1表
create table person1(
id varchar(18) primary key,
name varchar(10)
);

2. 定义主键自动增长:auto_increment
create table person2(
id int primary key auto_increment,
name varchar(10)
);

扩展：Oracle中使用序列,完成类似的功能

3. 定义唯一约束:unique
create table person3(
id int primary key auto_increment,
name varchar(20),
email varchar(20) unique
);

4. 定义非空约束;not null
create table person4(
id int primary key auto_increment,
name varchar(20) not null,
email varchar(20) not null unique
);

5. 外键：两张表的关系:
父表：
子表：外键指向了父表中的主键

部门表：父表
create table department(
deptno int primary key auto_increment,
dname varchar(20) not null unique,
loc varchar(20) not null
);

员工表：子表:
create table employees(
empno int primary key auto_increment,
ename varchar(20) not null,
email varchar(20) not null unique,
salary double not null,
deptno int,
constraint emp_dept_FK foreign key(deptno) references department(deptno)
);

插入数据:必须现有父记录，后有子记录
父记录：
insert into department(dname,loc) values('开发部','北京');
insert into department(dname,loc) values('行政部','北京');
子记录
insert into employees(ename,email,salary,deptno) values('Tom','tom@126.com',1000,1); 对

insert into employees(ename,email,salary,deptno) values('Mike','mike@126.com',1000,3); 错

更新操作：
update employees set deptno=2 where empno=1; 对

update employees set deptno=3 where empno=1; 错

删除:
delete from department where deptno=2;
办法：1. 先删除子表中的记录,在删除父表记录
2. 将子表中的外键列置为null，再删除父表记录(前提：该列没有定义not null)

6. check约束

多表操作：
1. 笛卡尔集
2. 多表查询:显示员工信息（包含部门名称）
select e.empno,e.ename,e.salary,d.dname from department d, employees e where d.deptno=e.deptno;

连接操作:
1. 等值连接
2. 不等值连接
3. 外连接
4. 自连接

3.java和数据库中多表的存储

使用mysql中的mysqldump命令(window 命令):
闪回:Oracle特有的技术