学习数据库（MYSQL）随笔（入门级涉及一部分查询）

一.数据库（databese）
显示数据库
show databases;
创建数据库
方法一.create database(库名);
方法二.create schema(库名);
创建字符集数据库的方法
create database（库名）character set=gbk;
删除数据库
drop database(库名);
检查是否有重名数据库
create database if not exists （库名）;
使用数据库的方法
use 数据库名;

修改数据库的方法

ALTER {DATABASE | SCHEMA} [数据库名]
[DEFAULT] CHARACTER SET [=] 字符集
|[DEFAULT] COLLATER [=] 较对规则名称
实例：修改前面视频中创建的数据库db_library，设置默认字符集和较对规则。

alter database db_library
default character set gbk 修改字符集
default collate gbk_chinese_ci; 修改校对规则
显示表
DESC

 

二.表（table）

表的常用字符类型
数字型
int
double（double后面不加“（）”）
字符型
char
varchar
时间
date
创建表的前提首先创建并且打开库
例子
create table tb_student(
id int(10),
name varchar(20),
time date);
显示创建好的表
desc（表名);

使用搜索引擎
在表末尾;号前面）后面输入engine=搜索引擎。例如：engine=myisam
给字符添加非空
在（）后面加上not null
给字符添加主键
在（）后面加上primary key
给字符添加自增（自增:例如输入1后面自动添加2.3.4.5...）只可以给整型(Int)附加自增
在（）后面加上auto_increment
添加字符集在表末尾添加
default charset=字符集
例如：default charset=gbk;
删除表单
drop table 表单名;
单次删除多张表
drop tables 表单名1，表单名2，表单名3.......;
例如：drop table lx3;
复制表
create table 新建表单名 like 已有表单名（需要复制的表单名）
例如：create table lx4 like lx3;（lx4为新建的表单名lx3为库内已有的表单名）

修改表

添加字段
alter table 表名 add 字段名 类型；
例如：alter table tb_lx add name varchar(20);
删除字段
alter table 表名 drop 字段名；
例如：alter table tb_lx drop name；
修改字段类型
alter table 表名 modify 字段名 类型；
例如：alter table tb_lx modify time datetime；
增加条件约束
alter table lx add primary key（id）;
删除条件约束
alter table lx drop primary key;
修改字段
alter table 表名 change 旧名字 新名字 字符类型；

例如：alter table lx change name mingzi varchar（20）；
修改表名
rename table 旧表名 to 新表名
例如：rename table sutdent to newstudent;
索引
添加索引
index(需要添加的字段)
删除索引
drop index 字段名 on 表名
例如：drop index id on tb_lx;
显示表结构
show create table 表名
例如：show create table tb_lx;

index(索引)
add(增加)
alter（更改）
change（改变）
rename（重命名）
添加信息
插入信息
方法一：
insert into 表名 values(10,'研发部','北京');
方法二：
insert into 表名(字段名) values(信息);
查询显示信息
select * from 表名
删除信息
delete from 表名 where 字段名=需要删除的数据；
修改信息
update 表名 set 修改后的信息（ deptno=''）where 搜索条件；
设置字段默认值
并且录入信息时调用默认值输入default,其他值输入'女'；
在字段末尾添加default '',
例：Sex char(2) not null default'男',
设置字段无符号
在字段末尾添加default,
例:gradeld int(4) unsigned,
查询

查询多条（全部）语句
select * from 表名;
查询多个语句
select 条件，条件1 from 表名
查询多个条件之间连接用and
select * from student where Sex='女'and gradeld=1;
在查寻时显示表内没有的新创建的字段

select ename,salary,salary*12 新字段名字 from emp;

null值的处理
select ename,salary,bonus,salary+ifnull(bonus,0) moth from emp;数值为空

select ename,ifnull(job,'没工作') '职位' from emp;字段为空

select ename,ifnull(hiredate,'2019-11-01') '入职时间' from emp;日期为空

distinct显示字段重复的只显示一个可以与NULL值的处理一起用（去除重复值）
select distinct 字段名 from 表名;（例：普通用法）

select distinct ifnull(bonus,0) '奖金' from emp;（例：与非空处理同时用）
同时查询多个字段
例如：查询每个部门不同的职位（注意：先职位再部门）
select distinct job，deptno from emp;

all（全部）
select salary from emp where salary>all(select salary from emp where ename= '张无忌');
between and (大于....小于...)

例如:工资大于等于5000小于等于10000
select * from emp where salary between 5000 and 10000;(注意between后面是最小值，and后面是最大值,并且包含该数字)

例如：入职年月在2011年之内的员工信息
select * from emp where hiredate between '2011-01-01'and'2011-12-31'；
显示职位是Manager 或者 Analys；
select * from emp where job='Manager'or job='Analys';

in 的用法
例如：显示职位是Manager 或者 Analys
select * from emp where job in('Manager','Analys');

模糊查询
语法：select * from 表名 where 字段名 like '查询内容'；
1.%表示任意多个字符‘%查询内容%’
2.—表示一个字符‘—查询内容—’

is null
查询奖金为空的员工
select * from emp where bonus is null;

not的用法

is not null
查询奖金不为空的员工
select * from emp where bonus is not null;
查询奖金不在5000-8000范围的员工
select * from emp where salary not between 5000 and 8000;
查询不在10 20 部门的员工
select * from emp where deptno not in(10,20);

函数
round
例子：select ename,salary*0.1234567 s1,round(salary*0.1234567,2) s2,round(salary*0.1234567) s3 from emp;（s1原样输出，s2保留小数点后两位，s3四舍五入）
truncate
用于截取 不做四舍五入计算 （用于计算金额）

日期
显示当前时间:current_date
计算入职时间
select ename,salary,datediff(current_date,hiredate)days from emp;
(注意括号内是，前的时间减去后面的时间)days=新字段名
显示月份
select period_diff('201911','199906')字段名；
（注意：数值大的在，前面数值小的在，后面）
date_fromat
字符串类型转换为日期型
select date_format(hiredate,'%Y')'年',date_format(hiredate,'%m')'月',date_format(hiredate,'%d')'日',date_format(hiredate,'%Y-%m-%d')hiredate from emp;
日期醒转换为字符串型
select str_to_date('2019-11-11','%Y-%m-%d')date;

18. 其他函数
(1) 函数coalesce()
coalesce( 参数列表 )函数的作用：返回参数列表中第一个非空参数，参数列表中最后一个值通常为常量。
【案例】计算员工的年终奖金。
要求：
如果bonus不是null，发年终奖金额为bonus；
如果bonus是null，发年终奖金额为salary * 0.5；
如果bonus和salary都是null，发100元安慰一下。
mysql> select ename , bonus , salary ,
coalesce( bonus , salary*0.5 , 100 ) bonus
from emp;

练习：计算员工的年终薪金。
要求：
如果salary不是null，发年终薪金额为salary；
如果bonus和salary都是null，发500元安慰一下。

(2) case语句
case语句是数据中的分支语句，相当于Java中的switch-case语句。
【案例】根据员工的职位，计算加薪后的薪水数据。
要求：
如果职位是Analyst：加薪10%；
如果职位是Programmer：加薪5%；
如果职位是Clerk：加薪2% ；
其他职位：薪水不变。
mysq> select ename , salary , job ,
case job when 'Analyst' then salary * 1.1 --注意这里没有“ , ”
when 'Programmer' then salary * 1.05
when 'Clerk' then salary * 1.02
else salary --else相当于Java中case语句的default
end new_salary --end是case语句的结束标识
from emp;
; --new_salary是从case开始到end结束这部分的别名
练习：根据员工的部门，计算加薪后的薪水数据。
要求：
如果部门是10：加薪20%；
如果职位是20：加薪15%；
如果职位是30：加薪12% ；
其他部门：薪水不变。

19. 查询结果排序order by
【案例】薪水由低到高排序( 升序排列 )。
mysql> select ename , salary from emp order by salary asc ;
--正序排列，asc可以省略。
空值被看做最小。

练习：1、奖金由低到高排序
2、部门编号由低到高排序
3、经理按由低到高顺序排序
【案例】薪水由高到低排序( 降序排列 )。
mysql> select ename, salary from emp order by salary desc ;
--desc( descend )降序排列，不可省略。
练习：1、奖金由高到低排序
2、部门编号由高到低排序
3、经理按由高到低顺序排序

【案例】按入职时间排序，入职时间越早排在前面。
mysql> select ename, hiredate from emp order by hiredate ;
练习：按部门编号，从低到高排序

【案例】按部门排序，同一部门按薪水由高到低排序。
mysql> select ename , deptno , salary from emp order by deptno , salary desc ;
注意：排序语句放在查询语句的最后。

组函数

count(查询该表有多少条语句)
例：select count(*) from emp;

sum（求和）
例：select sum(字段名) from emp;

avg（求平均值）
例:select avg(字段名) from emp;
假设有null
例： select avg(ifnull(bonus,0)) from emp;

max(最大值)不计算null值
min(最小值)不计算null值

select *,min(hiredate) from emp;（可以显示该条完整语句）

group by
例：显示同部门最高工资和最低工资
select deptno,max(salary)gz,min(salary)zx from emp group by deptno;
例：查询男女人数
select sex,count(*) from student group by sex;

多列分组查询
显示每个年级男女人数
例：select gradeId,sex,count(*)sex from student group by gradeId,sex order by gradeId;

having
筛选 where是在表内筛选 having是在分组后进行筛选
例：计算平均工资大于5000的部门
select deptno,avg(salary)avg_xs from emp group by deptno having avg_xs>5000;

子查询（查询套查询）
例：查询工资最高的人的姓名和工资（一条语句实现）
select ename,salary from emp where salary=(select max(salary) from emp);
例：查询工资最少的人的信息
select * from emp where salary=(select min(salary) from emp);
例：查询工资大于张无忌的人（只可以使用张无忌三个字来实现）
select ename,salary from emp where salary>(select salary from emp where ename='张无忌');
all
例： select ename from emp where salary>all(select salary from emp where ename='张无忌');以最大的值为计算标准
any
例： select ename from emp where salary>any(select salary from emp where ename='张无忌');以最小的值为计算标准

in
例： select ename from emp where deptno in (select deptno from emp where ename='刘苍松') and ename<>'刘苍松';

字段名<>'字段'；（除这个ID）

查询每个部门最高工资的人
select ename,deptno from emp where (deptno,salary) in(select deptno,max(salary) from emp group by deptno);

加入having那个部门的人数比30部门的多

select count(*),deptno from emp group by deptno having count(*)>(select count(*) from emp where deptno=30);

那个部门的平均工资比部门20的平均工资高

select avg(salary),deptno from emp group by deptno having avg(salary)>(select avg(salary) from emp where deptno=20);

in显示平均工资大于5000的部门的姓名职位

select ename,job from emp where deptno in(select deptno from emp group by deptno having avg(salary)>5000);

join on
例：显示上海地区的员工姓名，职位，部门
方法一（使用子查询）: select ename,job,mgr,deptno from emp where deptno=(select deptno from dept where location='上海');
方法二（使用连接）: select emp.ename,emp.job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno and location='上海';
方法三（使用join on连接）： select t.ename,t.job,t.deptno,w.dname from emp t join dept w on t.deptno=w.deptno where location='上海';

 

例：显示员工姓名以及上司
select t2.ename,t1.ename from emp t1 join emp t2 on t1.empno=t2.mgr;

 

复习

复制表
1.单纯复制结构不复制数据
create table 新表名 like 旧表名；
2.复制表结构又复制表内数据
create table 新表名 as select * from 旧表名；
3.使用as语句单纯复制表结构
create table newemp3 as select * from emp where 1<>1;
4.使用as语句复制表内部分数据
create table newemp4 as select empno,ename,salary*12 salary from emp where deptno=10;
5.复制别的表的部分数据到已创好的新表
insert into newemp5(select * from emp where deptno=30);

使用limit插入数据
insert into newemp5(select * from emp limit 2,5);

修改（数据内容字段名）
1.修改多个
update emp set salary=3500,job='Programmer' where empno=1007;

 

删除
1.drop table 表名 （同时删除结构和数据无法找回）
2.truncate 表名 （删除表内数据但保留表的结构）
3.delete from 表名 （删除特定行 可与where连用）

重命名
rename table 旧表（必须存在） to新表（一定不存在）；

修改
增加列：alter table emp add (phtno varchar(20));
修改字段： alter table emp change column phtno dianhua varchar(10);
修改字段类型： alter table emp modify column dianhua int(10);
删除字段： alter table emp drop column dianhua;

约束

PK：主键primary key（设置为主键后，该字段会自动添加非空约束）
语句：id int primary key,

NN：非空not null
语句：name varchar(20) not null,

FK：外键foreign key 在desc中显示MUL就证明设置外键成功
语句：constraint foreign key(需要添加外键的本表字段名) references 另一张表名（字段名（该字段为该表主键））;

constraint foreign key(本表字段) references 表1(表1字段));

UK：唯一性约束unique
语句：email varchar(120) unique,

CK：检查check key 多用于字段在指定范围的输入如：男女

视图(view)
创建一张虚拟的表
create view 新表名 as select * from 旧表名;（后面可以加where)