2021-07-30mysql

mysql进入方式:mysql -uroot -p123456
mysql版本查询：mysql -V
SQL分类：
DDL：数据定义语言(关键词create,alter,drop)
-数据库
-创建数据库：create database if not exists bigdata_61;
-查看有哪些数据库：show databases;
-使用数据库：use bigdata_61;
-查看正在使用的数据库：select database();
-删除数据库：drop database bigdata_61;

-表（进行表操作前需要先使用一个数据库）
-创建表：create table student(id int,name varchar(20),tel int,hobby varchar(20));
-查看有哪些表：show tables;
-查看表的结构：desc student;
-删除表：drop table student;

-表结构名称(表头）
-添加：alter table stuednt add age int;
-更改：alter table student change name sname varchar(20);
-删除：alter table student drop age;
-重命名：rename table student to stu;

DML：数据操作语言（关键词insert,delete,update）
-插入数据：insert into stu values(1,‘zhuangsan’,123456,‘wan’);
insert into stu(sname,hobby) values(‘lisi’,‘da’);
-更新数据：update stu set sname=‘wangwu’ where id=1 and hobby=‘wan’;
-删除数据：delete from stu where id!=1;
truncate stu
约束

DQL：数据查询语言（关键词select,from,where）
简单查询：select * from stu;#(*中内容可单独列出，显示的表则会列出输入的内容.*可以是固定值也可以是四则运算。）
-取别名：select hobby as likey,id as sid,sname name from stu student;#（as可省，表也可以取别名，但之后使用表就要输入别名）
-去重：select distinct price,sex from product;#distinct要放在所有字段前

条件查询：where
-select *from product where price >=200;
-select name from product where price >=1 and price <=100;
找到category_id是c001，c002，c003的数据
-select * from product where category_id=‘c001’ or category_id=‘c002’ or category_id=‘c003’;
-select * from product where category_id !=‘c004’
-select * from product where not category_id =‘c004’ and not category_id =‘c005’
-select * from product where category_id in (‘c001’,‘c002’,‘c003’);

模糊查询：like
-select * from product where name like ‘_想%’;

null的判断
-select * from product where price is null;
-select * from product where price is not null;
-select * from product where not price is null;

排序：order by (desc降序，asc升序默认）
-select * from product where price is not null order by price;
-select * from product where price is not null order by price desc,category_id asc;

聚合函数：
-select count(1) as cn from product;
-select max(price) as p,min(price) as p1,sum(price) as p3,avg(price) as p4,sum(price)/count(1) as avg2 from product where price is not null;

分组查询：group by
-select category_id,count(1),sum(price) from product where price is not null group by category_id;
二次过滤：having
-select category_id,count(1) as cn,sum(price) from product where price is not null group by category_id having cn>=3;

limit
1. 求TOPN
#求分类里面产品个数最多的N个（降序，最多的一个）
select category_id ,count