MySQL的sql浅谈

表操作
1.创建表：

create table table2(
		id int,
		name char(20),
		password varchar(20),
		birthday date
	);

create table employee(
	id int,
	name varchar(20),
	gender char(10),
	birthday date,
	entry_date date,
	job varchar(20),
	salary float,
	resume text
	);	

create table employee(
	id int primary key auto_increment ,
	name varchar(20),
	gender varchar(2) ,
	birthday date,
	entry_date date,
	job varchar(20),
	salary double,
	resume text
	);

2.修改表：
在上面员工表的基本上增加一个image列。

alter table employee add image blob;

修改job列，使其长度为60。

alter table employee modify job varchar(60);

删除gender列。

alter table employee drop gender;

表名改为user。

rename table employee to user;

修改表的字符集为utf8

alter table user character set gbk;

列名name修改为username

alter table user change name username varchar(20);

修改username为 unique

alter table user change username username varchar(20) unique;

alter table user modify birthday varchar(60) unique;

修改 salary 为 not null

alter table user modify salary float not null;

3.删除表：
drop table user;
操作数据：
1.插入数据：
1.1向表中插入数据：
insert into employee(id,name) values(1,‘ls’);
insert into employee values(1,‘ls’,18,‘eat’,‘bj’);
1.2创建employee表：

create table employee(
			id int primary key auto_increment ,
			name varchar(20),
			gender varchar(2) ,
			birthday date,
			entry_date date,
			job varchar(20),
			salary double,
			resume text
			);

向其中插入数据：
insert into employee(id,name,salary,job) values(null,‘ls’,6000.6,null);
insert into employee values(null,‘诸葛亮’,‘男’,‘1970-08-08’,‘1978-09-09’,‘略懂略懂’,5000.0,‘我是法师，需要发育’);
insert into employee values(null,‘关羽’,‘男’,‘1980-01-01’,‘1988-02-02’,‘武财神’,15000.0,‘骑马的’);
insert into employee values(null,‘张飞’,‘男’,‘1971-08-08’,‘1976-09-09’,‘吃豆芽’,3000.0,‘吼声如雷，坦克’);
2.更新语句：
2.1更新表中数据
update employee set name=‘ls’;
update employee set name=‘关羽’ where id = 4;
update employee set name=‘张飞’,salary=10000.0 where id = 3;
注意：更新时，如果要更新其中某一条或几条数据，需要添加where子句。
Update语句练习
• 练习：在上面创建的employee表中修改表中的纪录。
要求:
将所有员工薪水修改为5000元。
update employee set salary = 5000;
将姓名为’张飞’的员工薪水修改为3000元。
update employee set salary = 3000 where name = ‘张飞’;
将姓名为’ls’的员工薪水修改为4000元,job改为ccc。
update employee set salary = 4000,job = ‘ccc’ where name = ‘ls’;
将’关羽’的薪水在原有基础上增加1000元。
update employee set salary = salary+1000 where name = ‘关羽’;
3.删除数据
3.1.在删除数据时，只有指定了where子句才会删除指定条件的数据，如果不指定where子句，则删除全部表中数据。
delete from employee;
delete from employee where id = 1;
3.2.Delete语句练习
删除表中名称为’张飞’的记录。
delete from employee where name = ‘张飞’;
删除表中所有记录。
delete from employee;#删除表数据，不删除表
#drop table employee;#删除表
使用truncate删除表中记录。
truncate employee;
总结：
truncate在删除数据时，是直接将表摧毁，然后再重建表结构。
delete from在删除数据时，仅删除表中数据。

4.查询语句
4.1查询指定表中的数据
select id,name,salary from employee;
select * from employee;
select id,name,gender,birthday,entry_date,job,salary,resume from employee;
注意：查询时，指明字段名称的查询效率要高于select *的查询效率。
4.1.1去重：
select distinct name from employee; #去重
4.2.表达式
导入exam表。
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);

	insert into exam values(null,'关羽',85,76,70);
	insert into exam values(null,'张飞',70,75,70);
	insert into exam values(null,'赵云',90,65,95);

	
	select salary+1000 from employee;
	练习:
		在所有学生分数上加10分特长分显示。
			select math+10,chinese+10,english+10 from exam;
		统计每个学生的总分。
			select math+chinese+english from exam;
		使用别名表示学生总分。
			select math+chinese+english as sum from exam;
			select math+chinese+english sum from exam;
4.3.where子句	
	select * from employee where id = 1;
	查询姓名为'赵云'的学生成绩
		select * from exam where name = '赵云'; 
	查询英语成绩大于90分的同学
		select * from exam where english > 90;
	查询总分大于200分的所有同学
		#select math+chinese+english as sum from exam where sum > 200;#错误,因为where子句在select之前执行

	注意：在sql语句中，关键字具有一定的执行顺序：
		from - where - group by - having - select  - order by
			
		select math+chinese+english from exam where math+chinese+english > 200;
4.4.运算符
	Select语句(４)练习
	查询英语分数在 80－100之间的同学。
		select * from exam where english between 80 and 100;
	查询数学分数为70,76,77的同学。
		select * from exam where math in(70,76,77);
	查询所有姓张的学生成绩。
		select * from exam where name like '张%';
	查询数学分>70，语文分>80的同学。
		select * from exam where math > 70 and chinese > 80;
	插入一条数据：
		insert into exam values(null,'曹操',null,80,90);
	查询所有同学中语文成绩是null的同学。
		select * from exam where chinese is null;
	查询所有同学的成绩，如果缺考(为null)则填写零分。
	select name,ifnull(chinese,0),ifnull(math,0),	ifnull(english,0) from exam;
	查询所有同学的总成绩。
	select ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as sum from exam;
	查询数学成绩大于70分或者英语成绩大于80的同学。
		select * from exam where math > 70 or english > 80;
	查询数学成绩不在70以上的同学。
		select * from exam where not(math>70);
	查询英语成绩不在65,68,70中的同学。
		select * from exam where english not in(65,68,70);
4.5.排序查询
	对查询结构进行排序展示。
	select * from exam    order by math;#默认升序
		asc|desc --  升序|降序
		order by 用户放置在查询语句的最后。
	练习：
		对语文成绩排序后输出。
			select chinese from exam order by chinese;
			insert into exam values(null,'曹洪',0,70,90);
			insert into exam values(null,'曹仁',-1,70,90);
		对总分排序按从高到低的顺序输出
			select ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) from exam order by ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) desc;
			
			select ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as sum from exam order by sum desc;
		对姓李的学生成绩排序输出:
			select ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) from exam where name like '李%' order by ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) desc;
4.6.聚集函数(聚合函数)
	4.6.1计数 -- count()
		根据用户指定的字段，查询表中多少行当前字段。
		count(column_name|*)
		select count(*) from exam;#总共6行
		select count(chinese) from exam;#总共5行，因为有一行内容为null，不计数在内。
			练习：
			统计一个班级共有多少学生？
				select count(*) from exam;
			统计数学成绩大于90的学生有多少个？
				select count(math) from exam where math > 60;
			统计总分大于250的人数有多少？
				select count(*) from exam where ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) > 220;

	4.6.2求和 -- sum()
		对指定字段所有行中的内容进行求和操作。
		select sum(math) from exam;
		练习：
			统计一个班级数学总成绩？
				select sum(math) from exam;
			统计一个班级语文、英语、数学各科的总成绩
				select sum(math),sum(chinese),sum(english) from exam;
			统计一个班级语文、英语、数学的成绩总和
				select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
			统计一个班级语文成绩平均分
				select sum(ifnull(chinese,0))/count(ifnull(chinese,0)) from exam;
			注意：sum仅对数值起作用，否则会报错。
				对于非数值(date类型除外)字段，sum()不报错，提示当前字段计数为0，会有warning提示。
			注意：对多列求和，“，”号不能少。

	4.6.3求平均 -- avg()
		根据指定的字段，进行全部行的求平均数操作。
			select avg(english) from exam;
			select avg(ifnull(chinese,0)) from exam;
		练习：
			求一个班级数学平均分？
				select avg(math) from exam;
			求一个班级总分平均分？
				select avg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;

	4.6.4最大最小值 -- max()  min()
		根据指定的字段，求出所有行中数值最大/最小的数据
		练习：
		求班级最高分和最低分（数值范围在统计中特别有用）
			select max(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as max,min(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as min from exam;

4.7聚集函数(where子句)
	查询班级中id 大于 5的同学的总成绩。
		select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam where id > 5;
4.8.分组操作 -- group by

	根据id进行分组操作：
		select * from table1 group by id;
	根据id和camount进行分组操作：
		select * from table1 group by id,camount;
		
	练习：对订单表中商品归类后，显示每一类商品的总价
		• 使用having 子句 对分组结果进行过滤
		select product,sum(price) from orders group by product;
		
	练习：查询购买了几类商品，并且每类总价大于100的商品.
		select product,sum(price) from orders  group by product having sum(price) > 100;
		
	where和having区别：where在分组前进行条件过滤，having在分组后进行条件过滤。使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。

外键：
在创建表的过程中添加内容如下：
foreign key(dept_id) references dept(id)
表示：在当前表中创建一个dept_id字段的外键，依赖于dept表中的id字段。

外键约束可以维护表与表之间数据的完整性和一致性。
在表与表直接有了外键约束之后，不能随意的对数据进行增删改操作，如果操作产生无效数据，则会报错。
高吞吐量(大量的读写数据)情景下，外键是不适用的，高吞吐应该优先保存数据，然后对数据进行筛选过滤，保留其中有效的数据。如果数据库表中的存在外键，则极有可能影响数据的入库操作，无法优先保证数据入库。

1.多表查询：
笛卡尔积查询：
select * from dept,emp;
在笛卡尔积查询中，如果一张表有m条结果，一张表有n条结果，则笛卡尔积查询会产生m*n条结果。

内连接查询：
在笛卡尔积查询基础之上，展示左边表有且右边表也有的记录。
select * from dept,emp where dept.id = emp.dept_id;

	select dept.name as dept_name,emp.name as emp_name from dept,emp where dept.id = emp.dept_id;
	
	select * from dept d,emp e where d.id = e.dept_id;

	inner join -- 内连接查询

select * from dept 
			inner join emp 
			on dept.id = emp.dept_id;

外连接查询：
(在连接查询中先写的表是左边表，后写的表为右边表。)
左外连接查询：
在内连接查询基础之上，获取左边表有的记录而右边表没有的记录。
left join – 左外连接 连接时的判断条件 – on
select * from dept
left join emp
on dept.id = emp.dept_id;
右外连接查询：
在内连接查询基础之上，获取左边表没有的记录而右边表有的记录。
right join – 右外连接 连接时的判断条件 – on
select * from dept
right join emp
on dept.id = emp.dept_id;

全外连接查询：
	在内连接查询基础之上，获取左边表有的记录而右边表没有的记录，和左边表没有的记录而右边表有的记录。
	在mysql中没有full join操作，所以没有办法直接通过全外连接关键进行查询。
	只能通过union关键字模拟全外连接查询。
	union前和后的查询结果会进行合并，并且将重复的数据去重，只保留一份。

select * from dept
		left join emp
		on dept.id = emp.dept_id
		union
		select * from dept
		right join emp
		on dept.id = emp.dept_id;

select name n ,dept_id from emp group by name having n='赵云';