MySQL基础

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