MYSQL基础
references
https://www.cnblogs.com/vuciao/p/10586773.html
https://blog.csdn.net/lk142500/article/details/83316876
https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
1. 数据表的创建及操作
1.1创建数据表
数据库引擎有两种InnoDB(默认),MyISAM
create table 表名(
列名 类型 是否可以为空,
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
/*example*/
-- 自增列(auto_increment)必须为索引列,包含主键;
create table tb(
id int(4) auto_increment ,
name varchar(5),
dept varchar(5) ,
primary key(id) -- 声明主键
)ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8 ;
1.2删除与清空表
/*删除表*/
drop table 表名
/*清空表*/
delete from 表名
truncate table 表名
1.3修改表
添加列: alter table 表名 add 列名 类型
删除列: alter table 表名 drop column 列名
修改列:
alter table 表名 modify column 列名 类型; -- 类型
添加主键:
alter table 表名 add primary key(列名);
删除主键:
alter table 表名 drop primary key;
添加外键: alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外 键字段) references 主表(主键字段);
删除外键: alter table 表名 drop foreign key 外键名称
修改默认值: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
2. 数据表内容的操作(增删改查)
2.1增
insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
2.2删
delete from 表
/*example*/
delete from 表 where id=1 and name='alex'
2.3改
update 表 set name = 'alex' where id>1
2.4查
2.4.1普通查询
select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1
2.4.2数据排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
2.4.3聚合函数
2.4.3.1 count()
select COUNT(*) from 表名
/*example*/
select count(*) as records from student;/*起一个别名records*/
2.4.3.2 sum()
select sum(字段名) from 表名
/*example*/
select sum(grade) as 成绩总和 from student
2.4.3.3AVG()
select avg(字段名) from student;
2.4.3.4MAX/MIN
select max(grade) from student;
2.5分组查询(group by)
特别的: group by 必须在 where之后, order by之前
Example:准备数据,员工信息表结构和数据
某个员工信息表结构和数据如下:
id name dept salary edlevel hiredate
1 张三 开发部 2000 3 2009-10-11
2 李四 开发部 2500 3 2009-10-01
3 王五 设计部 2600 5 2010-10-02
4 王六 设计部 2300 4 2010-10-03
5 马七 设计部 2100 4 2010-10-06
6 赵八 销售部 3000 5 2010-10-05
7 钱九 销售部 3100 7 2010-10-07
8 孙十 销售部 3500 7 2010-10-06
Question1:列出每个部门的最高薪水(GROUP BY)
SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
/*result*/
DEPT MAXIMUM
开发部 2500
设计部 2600
销售部 3500
Question2:查询公司2010年入职的各个部门每个级别里的最高薪水(GROUP BY+WHERE)
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL
/*result*/
DEPT EDLEVEL MAXIMUM
设计部 4 2300
设计部 5 2600
销售部 5 3000
销售部 7 3500
Question3:寻找雇员数超过2个的部门的最高和最低薪水(GROUP BY+HAVING)
SELECT DEPT, MAX(SALARY) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT(*) >2
ORDER BY DEPT
/*result*/
DEPT MAXIMUM MINIMUM
设计部 2600 2100
销售部 3500 3000
Question4:寻找雇员平均工资大于3000的部门的最高和最低薪水(GROUP BY+HAVING)
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING AVG( SALARY ) >3000
ORDER BY DEPT
/*result*/
DEPT MAXIMUM MINIMUM
销售部 3500 3000
3. 多表联查+SQL JOINS
一张著名的SQL JOINS 的图,生动形象。
准备数据表table1和table2
3.1 INNER JOIN
/*用法*/
select column_name(s)
from table 1
INNER JOIN table 2
ON
table 1.column_name=table 2.column_name
/*example*/
select * from Table A inner join Table B
on Table A.id=Table B.id
3.2 LEFT JOIN
LEFT JOIN返回左表的全部行和右表满足ON条件的行,如果左表的行在右表中没有匹配,那么这一行右表中对应数据用NULL代替。
/*用法*/
select column_name(s)
from table 1
LEFT JOIN table 2
ON table 1.column_name=table 2.column_name
/*example*/
select * from Table A left join Table B
on Table A.id=Table B.id
3.3 RIGHT JOIN
RIGHT JOIN返回右表的全部行和左表满足ON条件的行,如果右表的行在左表中没有匹配,那么这一行左表中对应数据用NULL代替。
/*用法*/
select column_name(s)
from table 1
RIGHT JOIN table 2
ON table 1.column_name=table 2.column_name
/*example*/
select * from Table A right join Table B
on Table A.id=Table B.id
3.4 FULL OUTER JOIN
FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行,那么对面的数据用NULL代替
/*用法*/
select column_name(s)
from table 1
FULL OUTER JOIN table 2
ON table 1.column_name=table 2.column_name
/*example*/
select * from Table A full outer join Table B
on Table A.id=Table B.id