MYSQL日常命令

1、分组查询与分组函数(聚合函数)有的时候，我们可能需要查询表中的记录总数，
或者查询表中每个部门的总工资,平均工资，总人数。
这种情况需要对表中的数据进行分组统计,需要group by子句。

位置:
select…from tName [where 条件] [group by子句] [order by子句]
用法:
group by Field1[,Field2]

注意:在分组查询时，select子句中的值如果含有组标志或组函数，就尽量
不要含有非组标志或组函数，如果在select后边既有组函数，又有非组函数(普通字段)，
那么在oracle数据库中，该语句出现错误，而在mysql数据库中，该语句没有错误，
但是查询结果不正确，所以我们要避免在select后面既有组函数，又有普通字段

聚合函数:
count(Filed)：统计指定字段的记录数。
*：统计指定表中有多少条记录
字段名：统计指定表中该字段不为空的个数
sum(Filed):统计指定字段的和。
avg(Filed):统计指定字段的平均值
max(Filed):返回指定字段中的最大值。
min(Filed):返回指定字段中的最小值。
PS:聚合函数会忽略null值。

因此有时候需要使用函数：ifnull()
函数ifnull(field,value)
逻辑：如果field字段对应的值不是null,就使用
field的值，如果是null,就使用value.

多字段分组时:

   field1,  field2
   10        500
   20        600
   30        700

注意:多字段分组时，最多分组的数目为Filed1*Field2[*Filed3…]
2、having子句：
在分组查询时，有的时候可能需要再次使用条件进行过滤，这个时候不能where子句，
应该使用having子句，having子句后可以使用聚合函数。
位置：位于group by子句后

面试题：where和having的区别？
1)where后面放的是字段名，having后面可以放聚合函数
(where属于一次过滤，having是对分组结果二次过滤)
2)书写位置：where放在group by之前，having group by之后
3)执行顺序：where先执行，having后执行

3、分页查询：
需求:当每次查询的记录数比较大，通常一页显示不下，此时我们可以进行分页查询。
关键字limit
用法: limit begin,size;
begin：记录的开始行数. 偏移量
size：每页的最大记录数。

注意:limit后可以是一个参数，
	一个参数时，是指每次最大的查询记录数，limit size;
	默认记录数从0开始，而不是1.

第p页，每页显示n条数据的分页写法：
select * from tablename limit (p-1)*n,n
SQL分类：
DDL(create,drop,alter,truncate)
DML(insert,delete,update)
DQL(select)  ——————————现在学习
TCL(commit,rollback,savepoint)
DCL(grant,revoke)

4、完整的DQL
书写顺序：select,from,where,group by,having,order by,limit
执行顺序：from,where,group by,having,select,order by,limit

5、关联查询的基础
1)、概念：
当在查询时，我们所需要的数据不在一张表中，可能在两张表或多张表中。此时我们需要同时操作这些表。
即关联查询。
2)、等值连接:
在做多张表查询时，这些表中应该存在着有关联的两个字段。
我们使用某一张表中的一条记录与另外一张表通过相关联
的两个字段进行匹配，组合成一条记录。
用法:
from 表A ，表B where 关联条件
3)、笛卡尔积：
在做多张表查询时，我们使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。
比如表A有x条，表B有y条件，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。
4)、内连接
只要使用了join on。就是内连接。查询效果与等值连接一样。
用法:
from 表A [inner] join 表B on 关联条件
5)、外连接:
在做多张表查询时，我们所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。
此时需要使用外连接。会涉及到两个概念：
驱动表(主表):(就是要全部查询的表)
除了显示满足条件的数据，还需要显示不满足条件的数据的表
从表(匹配表)(副表):只显示满足关联条件的数据的表

外连接分为三种:
左外连接:(左边的表做驱动表)
表A left [outer] join 表B on 关联条件
表A是驱动表，表B是从表

右外连接：(右边的表做驱动表)
表A right [outer] join 表B on 关联条件
表B是驱动表，表A是从表

全外连接：
两张表的数据不管满不满足条件，都做显示
表A full [outer] join 表B on 关联条件
PS:mysql 不支持全外连接

外连接的结果集 = 内连接的结果集+驱动表中不匹配的结果集

6)、自连接:
在多张表进行关联查询时，这些表的表名是同一个。
即自连接。

高级关联查询：
有的时候，我们要查询的数据， 一个简单的查询语句满足不了，并且我们使用的数据，
表中不能直观体现出来。而是预先经过一次查询才会有所体现。那么先执行的查询，我们称之子查询。
被子查询嵌入的查询语句称之为父查询。

1)子查询可以在where子句中
2)子查询可以在from子句中
3)子查询可以在having子句中
4)子查询可以在select字句中，相当于外连接的另外一种写法。
举例：
创建student表，
sno int 主键 自动增长，
sname varchar(20) 唯一的，
spwd varchar(20) 非空的，
sage int，
ssex (男/女)
*/
DROP TABLE student
CREATE TABLE student(
sno INT PRIMARY KEY auto_increment,
sname varchar(20) UNIQUE,
spwd varchar(20) NOT NULL,
sage INT,
ssex enum(‘男’,‘女’)
)

/15.向表中插入数据/
INSERT INTO student(sname,spwd) VALUES(‘bb’,‘cc’)
INSERT INTO student VALUES(NULL,‘cc’,‘dd’,23,‘m’) #INSERT INTO student VALUES(NULL,‘cc’,‘dd’,23,‘女’)
INSERT INTO student VALUES(default,‘dd’,‘ee’,23) #INSERT INTO student VALUES(NULL,‘dd’,‘ee’,23,NULL)

/获取最后一次自增长的值/
select last_insert_id();

UPDATE student SET sname=‘bbb’,spwd=‘a’ WHERE sno=4

UPDATE student SET sname=‘bbb’,spwd=‘a’,ssex=‘男’ WHERE sno=3
select * from student;

外键约束： foreign key
要求:有外键约束的字段A必须依赖于另外一个字段B，字段B要有主键约束。
字段A的值，要么是null,要么必须是字段B里的值。
create table t1(
tno int,
foreign key (tno) references t(tno);
)
主表：t
从表：t1
如果想从t表中删除数据时，怎么办？
要看主表的记录是否被从表引用，如果引用，则需要先删除从表引用的记录，
然后在删除主表，如果没有引用，则可以直接删除

/*16.创建三张表
stu (sno 主键，sname，sage)
course(cno主键，cname)
sc(sno外键，cno外键，score)
*/
DROP TABLE stu;
DROP TABLE course;
CREATE TABLE stu(
sno INT PRIMARY KEY auto_increment,
sname VARCHAR(20),
sage int
)

CREATE TABLE course(
cno INT PRIMARY KEY auto_increment,
cname VARCHAR(20)
)

CREATE TABLE sc(
sno INT,
cno INT,
score INT,
FOREIGN KEY (sno) REFERENCES stu(sno),// 一一对应
FOREIGN KEY (cno) REFERENCES course(cno)
)
分别插入数据
INSERT INTO stu VALUES(null,‘张三’,23)
INSERT INTO stu VALUES(null,‘李四’,20)
INSERT INTO stu VALUES(null,‘王五’,26)

INSERT INTO course VALUES(null,‘数据结构’)
INSERT INTO course VALUES(null,‘组成原理’)
INSERT INTO course VALUES(null,‘java’)

select * from stu;

插入引用表数据 sno,cno
INSERT INTO sc VALUES(2,3,89)

/将sno为2的学生删除/
delete from stu where sno=2

/将sno为1的学生删除/
delete from stu where sno=1

/删除sc表中sno为2的引用表中的数据 外键/
delete from sc where sno=2

/将sno为2的学生删除 主键/
delete from stu where sno=2