MySQL学习总结

MySQL学习总结

1.group by:（按照年龄去获取年龄数值有哪些并且顺序排列（每个年龄只获取一次）还有获取各个年龄的人数）
eg：select age,count() as nums from users group by age;
±----±-----+
| age | nums |
±----±-----+
| 20 | 1 |
| 22 | 2 |
| 27 | 1 |
| 28 | 1 |
| 30 | 1 |
±----±-----+(执行结果)
2.having：可以在group by之后再使用，进一步过滤（having是对我们使用group分组后的表再进行进一步过滤）
eg：select age,count() as nums from users group by age having nums >= 2;
±----±-----+
| age | nums |
±----±-----+
| 22 | 2 |
±----±-----+（执行结果）
3.where是对我们的数据行进行过滤的，而having主要是对我们分组后的数据进行过滤的
4.select里面要是有两列 那么前面两列的字段名都要放到group by后面
eg：select class_id,class_name,count() as nums from class group by class_id,class_name;
5.mysql使用cmd窗口导出数据（windows10 cmd）
C:\Users\86136> mysqldump -u root -p chuange > ./Desktop/chaunge.sql（相对路径书写。）
C:\Users\86136> mysqldump -u root -p chuange > C:\Users\86136\Desktop\chaunge.sql（绝对路径书写）
6.mysql数据导入（导入数据之前必须先登录mysql创建好要导入数据的数据库，比如先创建好zzy）
C:\Users\86136> mysql -u root -p zzy < ./Desktop/chaunge.sql
7.创建用户赋给用户某些操作权限
grant select,insert on chuange.class to zhangsan@’%’ identified by ‘123456’;
上述语句的意思是：在你以root用户身份登录以后，使用root用户创建用户zhangsan和其密码123456，但赋予zhangsan用户显示（select）和添加（insert）的权限。
8.删除被赋予了权限的用户
drop user ‘zhangsan’@’%’;（一般用根目录root来执行这条语句，因为一般不会给某个用户赋予注销其他用户的操作权限）
9.创建数据库指令
create database zzy;
10.在数据库中创建表
（1）create table grade_table( ###注释：创建年级表
-> id int unsigned not null primary key auto_increment,
-> grade_name varchar(20)
-> )engine = innodb default charset = utf8mb4;
（2）create table grade( ###注释：成绩表
-> id int unsigned not null primary key auto_increment,
-> student_number varchar(20),
-> object_id int unsigned,
-> score varchar(3),
-> test_time date
-> )engine = innodb default charset = utf8mb4;
10.排序过滤器order by
select * from student_table where grade_id = 1 order by student_birthday;(默认为升序排列)
select * from score where subject_id=3 order by score desc;(加上desc就是降序排列 asc是升序)
11.函数COUNT(),MAX(),MIN(),SUM(),AVG()
select count(name) from class;
select count(id),max(age),min(age),sum(age),avg(sge) from class;
12.转换为嵌套SQL语句，子查询（子句用法）
select * from subject_table where grade_id = 2 and subject_time =(select max(subject_time) from subject_table);
–（1） 查询包含物品TNT2的所有订单编号
select order_num from orderitems where prod_id = ‘TNT2’;
±----------+
| order_num |
±----------+
| 20005 |
| 20007 |
±----------+
– (2) 查询对应订单编号的用户ID
select cust_id from orders where order_num in(20005,20007);
±--------+
| cust_id |
±--------+
| 10001 |
| 10004 |
±--------+
– (3) 查询购买对应物品的用户信息
select cust_id,cust_name from customers where cust_id in(10001,10004);
±--------±---------------+
| cust_id | cust_name |
±--------±---------------+
| 10001 | Coyote Inc. |
| 10004 | Yosemite Place |
±--------±---------------+
– 转换为嵌套SQL，子查询
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
SELECT cust_id
FROM orders
WHERE order_num IN (
SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’ )
);
– 什么是嵌套查询，子查询
就是在一个sql当中，它的where条件来源于另外一个sql，
或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。
（4）作为计算字段使用子查询
select cust_id,cust_name,(select count() from orders where orders.cust_id = customers.cust_id) as orders_num from customers;
±--------±---------------±-----------+
| cust_id | cust_name | orders_num |
±--------±---------------±-----------+
| 10001 | Coyote Inc. | 2 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 1 |
| 10004 | Yosemite Place | 1 |
| 10005 | E Fudd | 1 |
±--------±---------------±-----------+
13.表联结（两张表靠一个信息建立联系，一个表的外键存储的数值和另一个表的主键存储数值一样）
（1）where：
（a）where：书写时，如果展示的表中有两个表中信息，那么先将要展示的信息一次列出，先列主键被关联的表的信息再列出另一个表需要展示的信息；然后信息来自两个表所以要from两个表（eg.from vendors,products）；最后where后面写上主键的被关联主键的表的主键属性等于另一个表的外键属性（eg.where vendors.vend_id = products.vend_id）
完整的语句：select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id;
（b）三表查询（使用where）（不管from后面有几个表，我们都要在where后面把表与表之间的关系讲清楚）
select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where vendors.vend_id = products.vend_id and products.prod_id = orderitems.prod_id and order_num = 20005;
（2）join：
（a）除了使用where进行表的联结查询外，还可以使用另外一种联结方式，join（查询结果跟上面使用where一致）
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
上面这个sql就是使用了 join 的语法，进行了两个表的联结，在 on 后面 去定义了 联结的条件。
（b）三表查询使用（使用join）
select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;
14.自联结
自联结:当前这个表与自己这个表 做联结（join）
假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。 此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
（a）-- 使用 自联结方式查询
select p1.prod_id,p1.prod_name from products as p1 join products as p2 on p1.vend_id = p2.vend_id where p2.prod_id = ‘DTNTR’;（！！！例子一定要理解！）
（b）-- 改成where语句
select p1.prod_id,p1.prod_name from products as p1,products as p2 where p2.prod_id = ‘DTNTR’ and p1.vend_id = p2.vend_id;
深入理解：如果不打出来where后面的限定条件，那么就会出现笛卡尔积那么多行数的表。真正需要的结果包含在笛卡尔积那个表里面！！！通过where里面的限定条件，将笛卡尔积那么多行数的表中一部分表取出，组成我们需要的结果表。！！！
select p1.prod_id,p1.prod_name,p1.vend_id, p2.prod_id,p2.prod_name,p2.vend_id from products as p1,products as p2;

select cust_id,cust_name,count(orders.cust_id) from customers join orders on customers.cust_id = orders.cust_id;
15.对于count函数的理解：count函数作用的顺序是在where或者join后面的限定条件确定完表格以后，count再在经过限定生成的表格中计数！！！
16.联结表时对于group by的理解：两个表联结的时候，group by后面放的属性只能放两个表公共的属性（目前研究出来只能放两个表公共的属性，即外键主键的属性！）
17.外联结

那什么是外部联结呢？

left join ： 是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null
right join ：是以 right join 右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null
以用户表为基准，去关联查询 订单表数据
（left join）select customers.cust_id,orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;
（right join）select customers.cust_id,orders.order_num
from orders right join customers
on customers.cust_id = orders.cust_id;
18.总结：
表联结
内部联结： where， inner join(join)
自联结 : 是在一个sql中，用当前这个表，连接自己这个表进行关联查询
外部联结： left join，right join
19.对于外联结很重要的例子理解！！！（对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；