新手学习MySQL基础笔记三

# 作业
create table employee(
id int unsigned primary key auto_increment comment '编号',
job_id int unsigned not null comment '工种编号',
name varchar(30) not null comment '名称',
department_id int(3) zerofill not null comment '部门编号',
salary decimal(10,2) not null default 0 comment '薪水',
bonus decimal(10,2) not null default 0 comment '奖金'
)

insert into employee(job_id,name,department_id,salary,bonus) values 
(1,'tom',1,6000,1000),
(1,'jerry',1,7000,1000),
(2,'alice',1,6700,1000),
(3,'tina',1,8000,1000),
(1,'zhangs',2,9000,1000),
(2,'lisi',2,9000,0),
(3,'zhaowu',2,9000,0);
# DDL : alter 修改
# alter table 表名 modify 字段 字段属性 ... [after 字段| first]
# eg1 修改表admin 中 username 的类型 varchar(60)
alter table admin modify username varchar(60) not null after age;
# 练习 修改表admin  sex字段  tinyint unsigned not null 默认值0 字段属性，并且放到salary 后面
alter table admin modify sex tinyint unsigned not null default 0 after salary;

# alter table 表名 change 旧字段 新字段 字段属性 ... [after 字段| first]
# eg2  修改表admin 中 username 字段名  uname
alter table admin change username uname varchar(60) not null;

#  alter table 表名 
#add 字段名 字段属性... [after 字段| first],
add 字段名 字段属性... [after 字段| first]...;
# eg3 添加  n1 n2 字段 varchar(30) not null  放到salary 字段后面
alter table admin 
add n1 varchar(30) not null after salary,
add n2 varchar(30) not null after salary;


#  alter table 表名 drop 字段名,drop 字段名...;
# eg4 删除 n1 n2 字段 
alter table admin 
drop n1,drop n2;
desc admin;
# alter table 表名 rename [to] 新表名
# eg5 修改admin 表名 为 myadmin
alter table admin rename to myadmin;
# eg6  修改表引擎
alter table myadmin engine = InnoDB;
# eg7 手动重置  auto_increment
 alter table myadmin auto_increment = 1;
#注意： primary key , unique, foreign key  单独的语法。 

# DQL 查询语句
# select 字段名|expr,字段名|expr... from 表名
# [where 字段条件] 一次过滤
# [group by 字段名] 分组
# [having  条件] 二次过滤
# [order by 字段]  排序
# [limit 偏移量,长度] 截取

# eg1 select * from 表名 效率低
# eg2 查询myadmin 表中的数据 (列出字段)
select id,uname,password,salary,created,sex from myadmin;
# eg3 select 结合表达式和函数查询
select 3+7,3*7,now();

# eg4 查询myadmin 中编号，用户名，年薪的记录
select id,uname,12*salary from myadmin;
# 给字段起别: [as] 别名
select id,uname,12*salary as ySalary from myadmin;
# 给表起别名 , 表名.字段
select m.id,m.uname,12*m.salary as ySalary from myadmin as m;


# [where 字段条件] 一次过滤
# 比较运算符 : > >= < <= != <> 不等于, 判断null值：<=>， is[not] null 
# 逻辑运算符: and 并且  or 或  !非
# eg5. 查询myadmin 中 age 大于等 25 并且 salary 大于6000 的记录。
select * from myadmin where age >= 25 and  salary >6000; # 两个条件都满足
select * from myadmin where age >= 25 or  salary >6000; # 查询的数据多

# eg6. 查询范围
# 查询myadmin 中 age 大于等 25 并且 小于等于28的记录 [25,28]
select * from myadmin where age >=25  and age<=28;
#  ! 不在[25,28]
select * from myadmin where !(age<25 or age>28);
#  [not] between ...and (包含等于)
select * from myadmin where age between 25 and 28;

# eg7 [not] in(字段值,字段值...)查询指定的多个值
# 查询查询myadmin 中 age 是 22 25 30 的记录
select * from myadmin where age in (22,25,30);
select * from myadmin where age=22 or age=25 or age=30;

select * from myadmin where age not in (22,25,30);
select * from myadmin where age<>22 and age<>25 and age<>30;


# 模糊查询：字段（字符串类型） [not] like string [escape string]
# 关键字：  %:0个1个多个任意字符   _:任意一个字符
# eg1. 查询uname字段中 首字母是't' 的记录
select * from myadmin where uname like 't%'; 
# eg2  查询uname字段中 包含'e' 的记录
select * from myadmin where uname like '%e%'; 
# eg3   查询uname字段中 字符长是5的记录
select * from myadmin where uname like '_____'; 
# eg4  查询name字段中 第二个字母是下划线_的记录。
# '#'后面的是普通字符
select * from myadmin where uname like '_#_%' escape '#'; 
# eg5 查询name字段中 第三个字母是'a' 的记录
select * from myadmin where uname like '__a%';

# 练习
# 1. 查询表 myadmin 中 编号 2-6并且 salary 大于6000 的记录
select * from myadmin where id>=2 and id <=6 and salary>6000;
select * from myadmin where id between 2 and 6 and salary>6000;
# 2. 查询表 myadmin 中 编号 偶数 也可以 年龄 大于25 的记录
select * from myadmin where id % 2 = 0 or age > 25;
# 3. 查询员工中最高工资和最低工资的差，字段别名为diff , max(),min()
select max(salary)-min(salary) as diff from myadmin;

 [group by 字段名] 分组
 # 原理: 将某个字段中相同的值分为一组,每组相同的值只显示一个（显示小编号信息）
 # 一般结合聚合函数做汇总筛查操作, 或显示分组的那个字段
 # 聚合函数： 
    #count(*)包含null值 ,count(字段) 每组人数
	#avg(字段)平均值
	#max(字段) 最大值
	#min(字段) 最小值
	#sum(字段) 求和
	
# 查询myadmin中女男人数,最大年龄，平均年龄，每组的最大工资
select sex,count(*) as count,max(age) as mage,avg(age) as avg,max(salary) as msalary
from myadmin
group by sex