mysql语句学习

mysql -uroot -p
mysql -uroot -p密码

exit/quit/ctrl+C

show Databases;
select version();
select now();
0.注释
--


1.创建数据库
create database python01;
create database python02 charset=utf8;


2.删除数据库
#删除数据库
drop database python01;

撇`xxx-database`

3.使用数据库
use 数据库
show databases;

4.查看当前数据库
show database();

5.查看当前数据库中的表
show tables;

6.创建数据库表
create table xxx(id int, name varchar(30));
create table students(
                id int unsigned primary key not null auto_increment, 
                name varchar(30),
                age tinyint unsigned default 0,
                high decimal(5,2),
                gender enum("男","女","保密") default "保密",
                cls_id int unsigned
                );

7.显示表
desc xxx;

8.插入
insert into students values(0, "王俊杰",18,"男", 0);
select * from students;
insert into students (name, gender) values ("大乔", 12),("小乔", 22);
insert into students values (xx),(xxx),(xxxx);

表修改
9.增加表字段
alter table 表名 add 字段名 字段类型 

10.删除表字段
alter table 表名 drop 字段名

11.修改表字段

只修改字段类型
alter table 表名 modify 字段名 新字段类型
alter table students modify birthday date;
修改字段类型
alter table 表名 change 字段名 新字段名 新字段类型
alter table students change birthday birth date;

12.查询表字段

13.删除数据表
drop table 表名;
drop database 数据库;
alter drop table 表字段名;

14.显示创建表的方式
show create table students;
show create database ss;

15.修改表数值
update students set gender=1,age=12 where id=1;

16.查表数据
select * from students;
select * from students where name="xx";
select name,gender from student;
select name as 姓名,gender as 姓名 students;

17.删除数据
delete from student where xx; 



00.例子
创建classed表
有id、name

create table classes(
    id int unsigned not null auto_increment primary key,
    name varchar(30) not null
);

insert into classes (0, "mysql学习班");

查询

--使用mysql登陆
mysql -uroot -p

--表示注释

--创建一个数据库
create database python_test charset=utf8;

--使用一个数据库
use python_test;

--显示使用的当前数据是哪个
--函数
select database();

--创建一个数据表
--学生表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') defaule '保密',
cls_id int unsigned default 0,
is_delete bit default 0
); 

--班级表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);

--查看当前表
show tables;

--查看如何创建表字段
show create tables students;

--插入字段数据
insert into students values
(0, '小明',18,180.00,2,1,0)
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

insert into classes values
(0,"python_01期"),
(0,"python_02期");

--查询所有字段
select * from student;
select* from classes;

--查询只定字段
select id,name from student;

--别名
select name as 姓名, age as 年龄 from students;
select students.name, students.age from students;
select s.name,s.age from students as s;

--消除重复行
select distinct gender from students;

--条件查询
select name as 名字, age as 年龄 from students where age=18 or age=34;

select name,age from students age in(12, 15, 24); 
select name,age from students age not in(12, 15, 24);

--连续的
select name,age from students age between 18 and 24;
--not between 是一个用法，不能分开not (between)
select name,age from students age not between 18 and 24;

--判断空
--in null
--in not null
select * from students where height is null;
select * from students where height is not null;

--排序
--默认按照id排序
--查找年龄在18-24之间的男性
select * from students where (age between 18 and 34) and gender=1;
--按照age 从小到大排序,默认asc升序，可以不写
select * from students where (age between 18 and 34) and gender=1 order by age;
select * from students where (age between 18 and 34) and gender=1 order by age asc;
--按照age从大到小排序,desc 降序
select * from students where (age between 18 and 34) and gender=1 order by age desc;

--查询年龄在18-24之间的女性，身高从高到矮排序
select name as 名字,age as 年龄 from students where (age between 18 and 24) and gender=2 order by heigth desc; 
--相同情况下，按照id从大到小排
select name as 名字,age as 年龄 from students where (age between 18 and 24) and gender=2 order by height desc,id desc; 
--按照年龄从小到大
select name as 名字,age as 年龄 from students where (age between 18 and 24) and gender=2 order by height, age asc,id desc;
--按照年龄从小到大，身高从高到矮的排序
select * from students order by age asc,height desc;


--聚合函数
--总数
--查询男性有多少人，女性有多少人
select * from students where gender=1;
select count(*) as 男性总数 from students where gnder=1;

--最大值
select max(age) from students;
select max(height) from students where gender=2;

--最小值
--min
select min(age) from students where gender=1;

--求和
--sum
select sum(age) from students;


--平均值
-avg
select avg(age) from students;
select sum(age)/count(*)from students;

--四舍五入
--round
--存小数的时候要*100
--因为小数存储的时候是约等于，会有误差
--取得时候，再除以100
--比如需要存入3.14，存的时候是314
select round(sum(age)/count(*),2) from students;

select round(avg(height),2) from students where gender=1;


--分组，把数据先分组，然后从分组里面的数据取数据
--group by
--按性别分组，实际当中可能是根据部门等分组
select gender from students group by gender;
select gender,count(*) from students group by gender;
select gender,avg(age) from students group by gender;

--group_concat()
--分组中显示需要的字段
select gender,group_concat(name) from students group by gender;

--group by，查询结果过滤
--gender=1,只能写在group前面
select gender,count(*) from students where gender=1 group by gender; 
select gender,count(*),group_concat(name,age,id) from students where gender=1 group by gender; 
select gender,count(*),group_concat(name,"_",age,"",id) from students where gender=1 group by gender; 

--having
--分组结果过滤
--条件一般都写在后面
--查询平均年龄超过30岁的性别，以及姓名 having avg(age)>30;
select gender,group_concat(name) from students group by gender having avg(age)>30;
--where 和 having 的区别
--where是放在group前面，having是放在后面
--where是对原始表的过滤，having是对查询结果临时表的过滤

--查询每种性别中的人数多于2个的信息
--having count(*)>2
select gender,group_concat(name) from students group gender having count(*)>2;


--分页
--限制查询结果的个数
--limit start,count 
--limit 要写在句子最后面
--where group (having) limit
--start起始位置，count总个数
--最多显示两个
select * from students gender=1 limit=2;

--查询前5个,第一组5个数据
select * from students limit 0,5;
select * from students limit 5,5;

--查询每页显示的个数，根据每页显示
--limit (N-1)*每个的个数,每页的个数
--显示第6页的数据，每组2个，(6-1)*2=10;
--根据10可以猜到第几页，(10/2)+1=6,说明是第6页
select * from students order by age asc limit 10,2;

--查询身高从高到矮，每页只显示两个
select * from student where gender=2 order by height desc limit 0,2;


--链接查询
--多表查询
--inner join,多表数据连接,取表的交集
--select ... from tableA inner join tableB;

--将学生中的id和classes中id相等的信息显示出来
select * from students inner join classer on students.cls_id=classed.id;

--按照要求显示名字和班级
select students.*,class.name from students inner join classes on students.cls_id=classes.id;
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

--查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;

--在以上的查询中，将班级姓名显示在第1列
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;

--查询 有能够对对应班级的学生以及班级信息，按照班级进行排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;

--当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;


--外链接
--left join,以后左表为基准，找不到显示为null
--查询每位学生对应班级信息
select * from students as s left join classes as c on s.cls_id=c.id;

--查询没有对应班级信息的学生
--复习，where是对原始的表进行过滤、having是对查询后的哦、临时表进行过滤
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

--right join
--一般用left join代替
--将数据表名字互换位置，用left join完成


--自关联
--同一张表中的字段关联另一个字段
source areas.sql

--省级联动
--查询一张表中某个市、或者省的下级单位
--查询山东省中的下级城市
select * from areas as a  inner join areas as b on a.pid=c.pid having a.name="山东省";

--子查询
--select语句中还有其他select查询，先执行子查询select
--标量子查询

--查询出高于平均值身高的信息
select * from students height = (select max(height) from students);
select * from areas where pid = (select aid from areas where name="河北省");

--查询最高的男生信息

--列级子查询
--查询学生的班级号能够对应的学生信息

 mysql新版修改密码

use mysql；

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’; FLUSH PRIVILEGES;
8.0的详细设置参考 :
https://www.cnblogs.com/xyabk/p/8967990.html