最全MySQL8.0实战教程

内部静态类

已于 2023-06-21 16:04:22 修改

阅读量2.2k

点赞数 3

分类专栏： java 文章标签：数据库 mysql sql

于 2023-04-20 21:54:52 首次发布

本文链接：https://blog.csdn.net/qq_52421292/article/details/130276711

版权

java 专栏收录该内容

27 篇文章 2 订阅

订阅专栏

文章目录

最全MySQL8.0实战教程

最全MySQL8.0实战教程

长路漫漫，键盘为伴；键盘敲烂，月薪过万；SQL写的好，工作随便找

一.前言

1.计算机语言概述

机器语言
汇编语言
高级语言

2.SQL的概述

SQL全称：结构化查询语言，用于访问和处理数据库的标准的计算机语言

2.1 语法特点

SQL对大小写不敏感
SQL可以单行或者多行书写，以分号结束 ;
SQL的注释

1.-- 单行注释 -- 后面需要空格
2.#  单行注释 #后面可不加空格
3./*
    多行注释
    多行注释
    */

2.2 MySQL的安装

2.2.1 方式1：解压配置方式

下载地址： mysql-8.0.28-winx64.zip
解压上述压缩包，并在解压目录下创建my.ini文件并添加内容如下

[mysqld]
# 设置3306端口
port=3306
# 服务端使用的字符集默认为utf-8
character-set-server=utf-8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf-8
[client]
# 设置mysql客户端连接服务端和默认使用的端口
port=3306
default-character-set=utf-8

2.2.2 方式2：步骤安装方式

下载地址：mysql-installer-community-8.0.28.0.msi

二.数据库DDL操作

1.DDL概念

DDL,数据定义语言，不涉及到数据外的结构操作

对数据库常用操作
对表结构的常用操作
修改表结构

2.对数据库常用操作

功能	SQL
查看有哪些数据库	show databases;
创建数据库	create database [if not exists] mybd1 [charset=utf-8];
切换(选择要操作的数据库)	use mydb1;
删除数据库	drop database [if exists] mydb1;
修改数据库编码	alter database mydb1 character set utf8;

-- 一.DDL操作之数据库操作

-- 1.查看所有的数据库
show databases;
-- 创建数据库
create database mydb1;
create database if not exists mydb1;
-- 选择使用哪一个数据库
use mydb1;
-- 删除数据库
drop database mydb1;
drop database if exists mydb1;

-- 修改数据库编码
alter database mydb1 character set utf8;

-- 二.对表结构的常用操作
create table if not exists 表名(
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设计];

-- 1.创建一个数据库 mydb1
create database if not exists mydb1;

-- 2.使用刚才创建的数据库mydb1
use mydb1;

-- 创建一张表 student
create table if not exists  student(
	sid int,
	name varchar(20),
	gender varchar(10),
	age int,
	birth date,
	address varchar(20),
	score double
);

decimal(M,D) M是有效位数，包括整数位和小数位，D是小数位个数

3.对表结构进行操作

功能	SQL
查看当前数据库所有表的名称	show tables;
指定查看某个表的创建语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名;

-- 3.查看当前数据库所有的表
use mydb1;
show tables;
-- 4.查看指定表的创建语句
show create table student;
-- 5.查看表结构
desc student;
-- 6.删除表
drop table student;

-- 修改表结构
-- 1.添加列:alter table 表名 add 列名 类型(长度) [约束]
alter table student add dept varchar(20);

-- 2.修改列名和类型:alter table 表名 change 旧列名 新列名 类型(长度)	[约束]
alter table student change dept department varchar(30);

-- 3.修改表删除列:alter table 表名 drop 列名
alter table student drop department;

-- 4.修改表名：rename table 表名 to 表名
rename table student to stu;

三.数据库DML操作

一.数据插入

-- 语法格式
-- 方式1
insert into 表名 values(值1，值2，值3……)
-- 方式2
insert into 表名(字段1，字段2，字段3……) values(值1，值2，值3……)
INSERT INTO `mydb1`.`stu` (`sid`, `name`, `gender`, `age`, `birth`, `address`, `score`) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL);
-- DML操作
use mydb1;


-- 一.数据的插入

-- 格式1：insert into 表名(字段1，字段2，字段3，……) values(值1，值2，值3，……)

-- 插入单行数据
insert into student(sid,name,gender,age,birth,address,score) 
						values(1001,'张三','男',18,'2001-12-23','北京',85.5);

insert into student(sid) values(1004);
insert into student(sid,name) values(1005,'赵六')

-- 插入多行数据
insert into student(sid,name,gender,age,birth,address,score) 
						values(1002,'李四','男',19,'2002-09-13','上海',78.5),
						      (1003,'王五','女',17,'2003-11-29','深圳',66.5);


-- 格式2: insert into 表名 values(值1，值2，值三，……)
-- 插入单行数据
insert into student values(1006,'张华','女',21,'1999-01-08','广州',79);

-- 插入多行数据
insert into student values(1006,'钱博','男',21,'2005-05-07','北京',98),
													(1007,'李方','男',24,'1998-05-04','武汉',89);

二.更新操作

-- 二.数据修改操作
-- 语法格式
-- 格式1： update 表名 set 字段名=值,字段名=值…… ;
-- 格式2： update 表名 set 字段名=值,字段名=值…… where 条件;



-- 1.将所有学生的地址修改为重庆
update student set address = '重庆';
-- 2.将id为1004的学生的地址修改为北京
update student set address= '北京' where sid = 1004;
-- 将id > 1004的学生地址设置为上海
update student set address= '上海' where sid > 1004;
-- 3.将id为1005的学生的地址修改为北京，成绩修改为100
update student set address = '北京',score = 100 where sid = 1005;

三.删除操作

-- 三.数据删除
-- 语法格式
delete from 表名[where 条件];
truncate table 表名 或者 truncate 表名;

-- 注意：delete和truncate原理不同,delete只删除内容,
-- 而truncate类似于drop table,可以理解为是将整个表删除后再创建该表

-- 1.删除id为1004的学生数据
delete from student where sid = 1004;
-- 2.删除表的所有数据
delete from student;
-- 3.清空表数据
truncate table student;
truncate student;

MySQL的DML操作

练习案例

-- 1.创建表
/*
创建员工表employee,字段如下：
		id(员工编号),name(员工名字),gender(员工性别),salary(员工薪资)
*/
-- 使用mydb1数据库
-- use mydb1;
-- 添加注释
-- comment '' 

create table if not exists  mydb1.employee(
	id int comment  '员工编号',
	name varchar(20) comment '员工名字',
	gender varchar(10) comment '员工性别',
	salary double comment '员工薪资'
);

-- 2.插入数据
/*
	1,'张三','男',2000
	2,'李四','男',1000
	3,'王五','女',4000
*/
insert into mydb1.employee(id,name,gender,salary)
	values(1,'张三','男',2000),
				(2,'李四','男',1000),
				(3,'王五','女',4000);

-- 3.修改数据
-- 3.1 将所有员工薪水修改为5000元
update mydb1.employee set salary = 5000;

-- 3.2 将姓名为'张三'的员工薪水修改为3000元
update mydb1.employee set salary = 3000 where name = '张三';

-- 3.3 将姓名为'李四'的员工薪水修改为4000元,gender改为女
update mydb1.employee set salary = 4000,gender = '女' where name = '李四';

-- 3.4 将王五的薪水在原有的基础上增加1000元
update mydb1.employee set salary = salary + 1000 where name = '王五';

四.MySQL约束

一.主键约束

1.创建主键

第一种：在定义字段的同时指定主键
第二种：定义完字段之后指定主键

-- 一.创建单列主键
-- 方式1: 定义字段的同时创建单列主键
use mydb1;
create table emp1(
	eid int primary key,
	name varchar(20),
	deptId int,
	salary double
);

-- 方式2：定义完字段再创建单列主键
-- 注：constraint pk1 可以省略 直接写 primary key(eid)
create table emp2(
	eid int,
	name varchar(20),
	deptId int,
	salary double,
	constraint pk1 primary key(eid)
);

-- 主键的作用 
-- 一张表只能有一个主键 
-- 主键约束的列非空且唯一
insert into emp2(eid,name,deptId,salary) values(1001,'张三',10,5000);
insert into emp2(eid,name,deptId,salary) values(1002,'李四',20,6000);
insert into emp2(eid,name,deptId,salary) values(null,'王五',20,6000);


-- 二.添加联合主键
-- primary key(字段1，字段2，……)
create table emp3(
	name varchar(20),
	deptId int,
	salary double,
	constraint pk2 primary key(name,deptId)
);

-- 联合主键的各列均不能没空
insert into emp3 values ('张三',10,5000);
insert into emp3 values ('张三',20,5000);
insert into emp3 values ('王五',20,5000);
insert into emp3 values (null,10,5000);
insert into emp3 values ('赵六',null,5000);

-- 三.通过修改表结构添加主键
-- 语法格式 alter table 表名 add primary key (字段);
-- 创建单列主键
create table emp4(
	eid int,
	name varchar(20),
	deptId int,
	salary double
);
alter table emp4 add primary key(eid);


-- 创建联合主键
create table emp5(
	eid int,
	name varchar(20),
	deptId int,
	salary double
);
alter table emp5 add primary key(name,deptId);

2.删除主键

-- 四.删除主键
-- 语法格式：alter table 表名 drop primary key;
-- 1.删除单列主键
alter table emp1 drop primary key;
-- 2.删除联合主键
alter table emp5 drop primary key;

3.自增长主键

在mysql中，当主键设置为自增长之后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键会自动以相同的步长进行增长

-- 五.自增长主键
-- 针对主键设置自增长
-- 语法格式：字段 数据类型 auto_increment 
-- 创建自增长主键
create table t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

-- 插入数据
insert into t_user1 values(null,'张三');
insert into t_user1(name) values('李四');

默认情况下，auto_increment的初始值为1，每增加一条记录，字段值自动加1
一个表只能有一个字段使用auto_increment约束，必须唯一且非空
auto_increment约束的字段只能是整数类（tinyint,smallint,bigint,int）等
auto_increment约束字段的达到最大值，auto_increment失效

3.1 指定自增字段初始值

-- 指定自增长字段的初始值

-- 方式1 创建表的同时指定
create table t_user2(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment=100;

-- 添加数据
insert into t_user2 values(null,'张三');
insert into t_user2 values(null,'张三');

-- 创建表之后指定
create table t_user3(
	id int primary key auto_increment,
	name varchar(20)
);
alter table t_user3  auto_increment = 200;

-- 添加数据
insert into t_user3 values(null,'张三');
insert into t_user3 values(null,'张三');-- 方式1 创建表的时候指定
create table t_user2(
	id int primary key auto_increment,
    name varchar(20)
)auto_increment=100;


-- 方式2 创建完表后指定
create table t_user3(
	id int primary key auto_increment,
    name varchar(20)
);
alter table t_user3  auto_increment = 100;

3.2 delete和truncate在删除后自增列的变化

delete数据之后自动增长从断点开始
truncate数据之后自动增长从默认起始值开始,即从1开始

-- delete删除数据之后，自增长从断点开始
delete from t_user1;
insert into t_user1 values(null,'张三');

-- truncate删除数据库，自增长变为从1开始
truncate t_user1;
insert into t_user1(name) values('李四');

二.非空约束

方式1：字段名数据类型 not null;
方式2：alter table 表名 modify 字段名数据类型 not null;
删除非空约束：alter table 表名 modify 字段名数据类型;

-- 二.非空约束
-- 1.格式1 创建表的时候指定
create table t_user6(
	id int,
	name varchar(20) not null, -- 指定非空约束
	address varchar(20) not null -- 指定非空约束
);


insert into t_user6(id) values(1001); -- 不可以
insert into t_user6(id,name,address) values(1001,null,null); -- 不可以
insert into t_user6(id,name,address) values(1001,'null','null'); -- 可以 相当于字符串null
insert into t_user6(id,name,address) values(1001,'','');  -- 可以 空字符串 

-- 2.格式2：创建完表后指定
create table t_user7(
	id int,
	name varchar(20),
	address varchar(20)
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;

-- 查看表结构
desc t_user7;

-- 3.删除非空约束
alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

三.唯一约束

TODO: 在MySQL中null和任何值都不相同甚至和自己都不相同

-- 三.唯一约束
-- 语法格式
-- 方式1： 字段名 数据类型 unique;
-- 1.创建表时指定
create table t_user8(
	id int,
	name varchar(20),
	phone_number varchar(20) unique  -- 指定唯一约束
);

insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三2',138); -- 不可以
insert into t_user8 values(1003,'张三3',null); -- 可以
insert into t_user8 values(1004,'张三4',null); -- 可以
-- TODO: 在MySQL中null和任何值都不相同 甚至和自己都不相同
-- 方式2： alter table 表名 add constraint 约束名 unique 字段名;

create table t_user9(
	id int,
	name varchar(20),
	phone_number varchar(20)
);

alter table t_user9 add constraint unique_pn  unique(phone_number);
desc t_user9;

-- 删除约束
-- 格式 alter table 表名 drop index  唯一约束名;
alter table t_user9 drop index unique_pn;

注:删除唯一约束时，如无唯一约束名，则使用唯一约束的字段

四.默认约束

-- 四.默认约束
-- 语法格式
-- 格式1：创建表时指定 
-- 字段名 数据类型 default 默认值
create table t_user10(
	id int,
	name varchar(20),
	address varchar(20) default '北京'  -- 指定默认值
);

insert into t_user10(id,name) values(1001,'张三');
insert into t_user10(id,name,address) values(1001,'张三','上海');
insert into t_user10 values(1002,'李四',null);
insert into t_user10 values(1002,'李四'); -- 列数不匹配

-- 格式2：创建完表后指定
--  alter table 表名 modify 列名 类型 default 默认值; 
create table t_user11(
	id int,
	name varchar(20),
	address varchar(20)
);
alter table t_user11 modify address varchar(20) default '深圳';
insert into t_user11(id,name) values(1001,'张三');

-- 删除默认约束
-- alter table 表名 modify  字段名 类型 default null;
alter table t_user11 modify address varchar(20) default null;

五.零填充约束

当插入的字段长度小于定义的长度时，会在该值的前面补上0
zerofill默认为int(10)
当使用zerofill时，默认会自动添加unsigned(无符号)属性，使用unsigned属性后，数值范围是原数值范围的2倍，有符号为-128到127，无符号则为 0-256

-- 五.零填充约束
create table t_user12(
	id int zerofill, -- 零填充约束
	name varchar(20)
);

insert into t_user12 values(123,'张三');
insert into t_user12 values(1,'李四');

-- 删除零填充约束
alter table t_user12 modify id int;

五.数据库DQL操作

1.基本查询

取别名 as
去除重复值 distinct

-- DQL-基本查询

-- 1.创建数据库
create database if not exists mydb2;
-- 2.使用数据库
use mydb2;
-- 3.创建s商品表
create table if not exists product(
	pid int primary key auto_increment,  -- 商品编号
	pname varchar(20) not null,    -- 商品名称
	price double,        -- 商品价格
	category_id varchar(20) -- 商品所属分类
);

-- 4.添加数据
insert into product values(null,'海尔洗衣机',5000,'c001'),
													(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电饭煲',5000,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002');
insert into product values(null,'恒源祥西裤',800,'c002');
insert into product values(null,'花花公子夹克',440,'c002');
insert into product values(null,'劲霸休闲裤',266,'c002');
insert into product values(null,'海澜之家卫衣',180,'c002');
insert into product values(null,'夹克琼斯运动裤',430,'c002');
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅诗兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);




-- 一.简单查询
-- 1.查询所有商品
select pid,pname,price,category_id from product;
select * from product;

-- 2.查询商品名和商品的价格
select pname,price from product;

-- 3.别名查询 使用的关键字是as (as可以省略)
-- 3.1 表别名
select * from product as p;
select * from product p;
-- 适用场景 多表查询
select p.id,u.id from product p,user u;

-- 3.2 列别名
select pname as '商品名',price as '商品价格' from product;
select pname '商品名',price '商品价格' from product;

-- 4.去掉重复值 distinct
select distinct price from product;

-- 5.查询结果是表达式(运算查询):将所有商品都加价10元进行显示
select pname,price + 10 from product;
select pname,price + 10 new_price from product;

2.运算符

2.1 算术运算符

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/ 或 DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

2.2 比较运算符

比较运算符

2.3 逻辑运算符

逻辑运算符	说明
NOT或者!	逻辑非
AND或者&&	逻辑与
OR或者\|\|	逻辑或
XOR	逻辑异或

-- 二.运算符
-- MySQL支持4种运算符
-- 1.算术运算符 2.比较运算符 3.逻辑运算符 4.位运算符
-- 1. 算术运算符
use mydb2;
select 6 + 2 ;
select 6 - 2 ;
select 6 * 2 ;
select 6 / 2 ;
select 6 % 4 ;

-- 1.1 将所有商品的价格加10元
select pname,price + 10 as new_price from product;

-- 1.2 将所有价格的商品上调10%
select pname,price * 1.1 as new_price from product;


-- 2.比较运算符 3.逻辑运算符

-- 2.1 查询商品名称为"海尔洗衣机"的商品所有信息
select * from product where pname = '海尔洗衣机';

-- 2.2 查询价格为800的商品
select * from product where price =  800;

-- 2.3 查询价格不是800的商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not (price = 800);

-- 2.4 查询价格大于等于60元的所有商品信息
select * from product where price >= 60;

-- 2.5 查询商品价格在200到1000之间的所有商品 between and
select * from product where price between 200 and 1000;
select * from product where price >= 200 and price <= 1000;
select * from product where price >= 200 && price <= 1000;

-- 2.6 查询商品价格是200或者800的所有商品
select * from product where price in(200,800);
select * from product where price = 200 or price = 800;
select * from product where price = 200 || price = 800;

-- 2.7 查询含有'裤'的所有商品
select * from product where pname like '%裤%'; -- %用来匹配任意字符

-- 2.8 查询以'海'开头的所有商品
select * from product where pname like '海%';

-- 2.9 查询第二个字为'寇'的所有商品
select * from product where pname like '_蔻%'; -- _下划线匹配单个字符

-- 2.10 查询category_id为null的商品
select * from product where category_id is null;  -- 这里不能用= 

-- 2.11 查询category_id不为null的商品
select * from product where category_id is not null;

-- 2.12 使用least求最小值
select least(10,5,20) as small_number;
-- 如果求最小值时,有一个值为null,则不进行比较,结果直接为null
select least(10,5,null) as small_number; -- null

-- 2.13 使用greatest求最大值
select greatest(10,20,30) as big_number;
-- 如果求最大值时,有一个值为null,则不进行比较,结果直接为null
select greatest(10,null,30) as big_number; -- null

2.4 位运算符（使用少）

位运算符

-- 四.位运算符号
select 3&5; -- 位与 1
3: 0011
5: 0101
-------
1: 0001

select 3|5; -- 位或 7
3: 0011
5: 0101
-------
7: 0111

select 3^5; -- 位异或 6
3: 0011 
5: 0101
-------
6: 0110


select 3>>1; -- 位右移 1
3: 0011 >> 1 ---> 0001 

select 3<<1; -- 位左移 6
3: 0011 << 1 ---> 0110 

select ~3; -- 位取反 18446744073709551612
3: 00000000000000000011 --> 11111111111111111100

3.排序查询

-- 三.排序查询

-- 关键字：order by 
-- asc升序从小到大 desc降序从大到小  默认升序 

-- 3.1 使用价格排序(降序)
select * from product order by price desc;

-- 3.2 在价格排序(降序)的基础上,以分类排序(降序)
select * from product order by price desc,category_id desc;

-- 3.3 显示商品的价格(去重复),并排序(降序)
select distinct price from product order by price desc;

4.聚合查询

聚合函数	作用
count()	统计指定列不为NULL的记录行数
sum()	计算指定列的数值和
max()	计算指定列的最大值
min()	计算指定列的最小值
avg()	计算指定列的平均值

-- 四.聚合查询

--  count() sum() max() min() avg()
-- 1.查询商品的总条数
select count(pid) from product;
select count(*) from product;

-- 2.查询价格大于200商品的总条数
select count(pid) from product where price > 200;

-- 3.查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id = 'c001';

-- 4.查询商品的最大价格
select max(price) from product;

-- 5.查询商品的最小价格
select min(price) from product;

select max(price) max_price,min(price) min_price from product;

-- 6. 查询分类为'c002'的所有商品的平均价格
select avg(price) from product where category_id = 'c002';

-- TODO:聚合查询对null值的处理
-- 1.count函数对null的处理,count(*),统计所有记录的个数,count(某字段)则不统计null
-- 2.sum,avg,max,min忽略null
-- 创建表
create table test_null(
	c1 varchar(20),
	c2 int
);
-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
-- 测试 注:count(1)等价于count(*)
select count(*),count(1),count(c1),count(c2) from test_null;


select sum(c2),max(c2),min(c2),avg(c2) from test_null; -- 12 6 3 4
-- 注 avg(c2)时,null不算个数,固12/3

5.分组查询

-- 五.分组查询
-- 关键字 group by having ……
-- 注 group by 分组之后 查询只能查分组字段和聚合函数
-- where 后面不能加聚合函数

-- 1.统计各个分类商品的个数
select category_id,count(pid) from product group by category_id;

-- 2.统计各个分类商品的个数,且只显示个数大于4的信息
select
 category_id,count(pid) cnt 
from 
	product 
group by 
	category_id having cnt > 4 
order by 
	cnt asc;
-- SQL执行顺序:from --> group by --> count(pid) --> having --> select

6.分页查询

-- 六.分页查询
-- 关键字 limit
-- 方式1 显示前n条 
select 字段 from 表名 limit n;
-- 方式2 分页显示
select 字段 from 表名 limit m,n ;
-- m是从第几条开始显示,第一条为0,m=(当前页数-1)*每页显示条数
-- n表示每页显示几条

select * from product limit 5; -- 显示前5条数据

select * from product limit 3,5; -- 从第4条开始,显示5条
select * from product limit 0,5; -- 第1页 (1-1)*5
select * from product limit 5,5; -- 第2页 (2-1)*5
select * from product limit 10,5; -- 第3页 (3-1)*5

insert into 表名2 select 字段 from 表名1 将一张表的数据导入到另一张表中,可以使用insert into select语句;

use mydb2;
select * from product;
create table product2(
	pname varchar(20),
	price double
);

insert into product2 select pname,price from product;

create table product3(
	category_id varchar(20),
	product_count int
);

insert into product3 select category_id,count(pid) from product group by category_id;

7.案例练习

-- DQL查询练习
use mydb2;
create table student(
	id int,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int
);
insert into student(id,name,gender,chinese,english,math) values(1,'张明','男',89,78,90);
insert into student(id,name,gender,chinese,english,math) values(2,'李进','男',67,53,95);
insert into student(id,name,gender,chinese,english,math) values(3,'王五','女',87,78,77);
insert into student(id,name,gender,chinese,english,math) values(4,'李一','女',88,98,92);
insert into student(id,name,gender,chinese,english,math) values(5,'李财','男',82,84,67);
insert into student(id,name,gender,chinese,english,math) values(6,'张宝','男',55,85,45);
insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);
insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);


-- 1.查询学生表中所有的学生信息 
select * from student;
-- 2.查询表中所有的学生的姓名和英语成绩
select name,english from student;
-- 3.过滤表中重复数据、
select distinct * from student;
-- 4.统计学生的总分
select name,chinese,english,math,chinese+english+math total_score from student;
-- 5.在所有学生的总分数上加10分特长分
select name,(chinese+english+math+10) total_score from student;
-- 6.使用别名表示学生分数
select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student;
-- 7.查询英语成绩大于90的学生
select * from student where english > 90;
-- 8.查询总分大于200的同学
select *,(english+chinese+math) as total_score from student where english+chinese+math > 200 order by total_score desc;
-- 9.查询英语成绩在80到90之间的同学
select * from student where english between 80 and 90;
select * from student where english >= 80 and english <= 90;
-- 10.查询英语成绩不在80-90之间的同学
select * from student where english not between 80 and 90;
select * from student where not english between 80 and 90;
select * from student where not (english >= 80 and english <= 90);
select * from student where english <= 80 or english >= 90;
-- 11.查询数学成绩为89,90,91
select * from student where math in(89,90,91);
-- 11.查询数学成绩不为89,90,91
select * from student where math not in(89,90,91);
select * from student where not math in(89,90,91);
-- 12.查询姓李同学的英语成绩
select name,english from student where name like '李%';
-- 13.查询数学分80并且语文分80的同学
select name,math,chinese from student where math = 80 and chinese = 80;
-- 14.查询英语80或者总分200的同学
select name,english,(english+math+chinese) from student where english = 80 or (chinese+math+english) = 200;
-- 15.对数学成绩降序排序
select name,math from student order by math desc;
-- 16.对总分进行排序输出,按照从大到小的顺序输出
select name,(math+chinese+english) from student order by (math+chinese+english) desc;
-- 17.对姓李的学生按照总分成绩排序输出
select name,(math+chinese+english) from student 
where name like '李%' 
order by (math+chinese+english) desc;
-- 18.查询男生和女生分别有多少人,并将人数降序排序输出,查询出人数大于4的人数信息
select gender,count(1) from student group by gender having count(1) > 4 order by count(1) desc

注：isfull(sal,) 如果sal为null,则sal=0;

-- 练习2
use mydb2;
create table emp(
	empno int,  -- 员工编号
	ename varchar(50), -- 员工名称
	job varchar(50), -- 工作名字
	mgr int, -- 上级领导编号
	hiredate date, -- 入职日期
	sal int, -- 薪资
	comm int, -- 奖金
	deptno int -- 部门编号
);

INSERT INTO emp VALUES(7369,'SMITH', 'CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN', 'SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD' , 'SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES', 'MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN', 'SALESNAN',7698,'1981-09-28 ',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK', 'MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT', 'ANALYST',7566,'1987-04-19', 3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING', 'PRESIDENT',NULL,'1981-11-17' , 5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER', 'SALESNAN' ,7698,'1981-09-08 ',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS' , 'CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES' , 'CLERK',7698,'1981-12-03' ,950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD', 'ANALYST',7566,'1981-12-03' , 3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLERN', 'CLERK',7782,'1982-01-23', 1300,NULL,10);


-- 1.按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno != 10 order by empno asc;

-- 2.查询姓名第二个字母不是A且薪水大于1000元的员工信息,按年薪降序排序
-- 年薪：12*月薪+奖金
-- ifnull(sal,0) 如果sal是null,当作0,不为null,则是sal
select * from emp where ename not like '_A%' and sal > 1000 order by (sal*12+ifnull(comm,0)) desc;

-- 3.求每个部门的平均薪水
select avg(sal+ifnull(comm,0)) avg_sal from emp group by deptno;

-- 4.求每个部门的最高薪水
select deptno,max(sal) max_sal from emp group by deptno;

-- 5.求每个部门每个岗位的最高薪水
select deptno,job,max(sal) max_sal from emp group by deptno,job;

-- 6.求平均薪资大于2000的部门编号
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal >2000;

-- 7.将部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 1500 order by avg_sal desc;

-- 8.查询公司中有奖金的员工姓名,工资
select ename,sal from emp where comm is not null;
select ename,sal from emp where ifnull(comm,0)>0;

-- 9.查询员工最高工资和最低工资的差距
select max(sal)-min(sal) '薪资差距' from emp;