MySQL数据库学习笔记2

最新推荐文章于 2022-05-30 01:31:07 发布

小bye菜

最新推荐文章于 2022-05-30 01:31:07 发布

阅读量526

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/m0_60477996/article/details/119851501

版权

笔记专栏收录该内容

7 篇文章 1 订阅

订阅专栏

SQL（结构化查询语言）
MySQL（数据管理系统）
create database lianxiku1 default character set utf8;#建库语句
show character set;#查看MySQL支持的字符集命令：
show collation;#查看MySQL字符集支持的校对集命令：
show databases;#查看有哪些库
show create database lianxiku1;#查看指定数据库的信息
CREATE DATABASE lianxiku1 /*!40100 DEFAULT CHARACTER SET utf8 */
create database lianxiku2 default character set utf8;
#切换库：
use lianxiku1;
use lianxiku2;
#建表语法：
create table 表名(
属性1 类型1 约束1,
属性2 类型2 约束2,
属性3 类型3 约束3,
…
);

约束：
主键约束（primary key）、外键约束（foreign key……references……）、唯一约束（unique）、
非空约束（not null）、默认值约束（default xx）、
自增（auto_increment）#一般用于比如id、编号等键的列自增
comment 用于备注，解释属性
数据类型：
整型 int 最多存10位数
浮点型 float(m,n) m–有效位数；n–小数位数
字符串
char(n) 定长计算机按指定长度存储数据，存储效率高
varchar(n) 可变长度计算机按实际的字符长度存储和数据，可节约存储空间
日期时间型
datetime – 年月日时分秒

练习,建表,一张学生表
use lianxiku2;#–切换库，此处为到数据库lianxi2中创建一张表格
create table student1(
stu_no int comment “学号”,
stu_name char(4) comment “姓名”,
stu_sex enum(“男”,“女”,“秘密”) default “男” comment “性别”,
stu_age int comment “年龄”,
stu_tel char(11) comment “电话号码”
);
show create table student1;
CREATE TABLE student1 (
stu_no int(11) DEFAULT NULL COMMENT ‘学号’,
stu_name char(4) DEFAULT NULL COMMENT ‘姓名’,
stu_sex enum(‘男’,‘女’,‘秘密’) DEFAULT ‘男’ COMMENT ‘性别’,
stu_age int(11) DEFAULT NULL COMMENT ‘年龄’,
stu_tel char(11) DEFAULT NULL COMMENT ‘电话号码’
) ENGINE=InnoDB DEFAULT CHARSET=utf8

特殊类型
enum 枚举单选
set 集合多选
use lianxiku1;
create table student2(
id int,
name char(5),
sex enum(“男”,“女”,“秘密”),
hobby set(“学习”,“睡觉”,“运动”,“追剧”,“玩游戏”,“唱歌”)
);
desc student2;#查看表结构，即属性
show create database lianxiku2;#查看建库语句
CREATE DATABASE lianxiku2 /*!40100 DEFAULT CHARACTER SET utf8 */
show create table student2;#查看建表语句
CREATE TABLE student2 (
id int(11) DEFAULT NULL,
name char(5) DEFAULT NULL,
sex enum(‘男’,‘女’,‘秘密’) DEFAULT NULL,
hobby set(‘学习’,‘睡觉’,‘运动’,‘追剧’,‘玩游戏’,‘唱歌’) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

use lianxiku1;
create table salgrade(
grade int(2) primary key,
losal float(7,2),
hisal float(7,2)
);

create table dept(
deptno int(2) primary key,
dname varchar(20) not null,
loc varchar(20) not null,
index dept_index(deptno)#表示在deptno列上创建索引dept_index
);

create table emp(
empno int(4) primary key,
ename varchar(20) not null,
job varchar(10) not null,
mgr int(4),
hiredate date not null,
sal float(7,2) not null,
comm float(7,2),
deptno int(2),
index emp_index(deptno),
foreign key(deptno) references dept(deptno) on delete cascade on update cascade
#在deptno列上创建外键约束，引用dept表的deptno列，具有极联删除和修改
);

外键的使用：1.首先要在被引用的表（主表）中需要的元素那里创建索引（语法为：index (元素) ps:主键和唯一键会自动生成索引，无需额外创建索引）
2.在引用表（从表）中需要引用元素后面创建外键（外键语法为：foreign key (需要引用的元素) references 被引用表(被引用元素)）
综上，创建外键则主表中被引用的元素必须要加索引
create table depar(
id int primary key,
name char(10),
no int(4),
index (no),
age char(3) unique
);
create table stuu1(
id int primary key,
name char(4),
deptno int,
foreign key(deptno) references depar(no),
sex enum(“男”,“女”),
hobby set(“篮球”,“高尔夫”,“马球”,“极限运动”),
depar_no int,
foreign key (depar_no) references depar(id),
age char(4),
foreign key stuu1(age) references depar(age)
);

use lianxiku1;
desc depar;
desc stuu1;

主键和唯一键的区别:
主键,一张表只有1个,唯一键允许多个
主键不能为空,唯一键允许为空
都是不允许重复的
主键和唯一键都会生成强制索引,唯一索引
create table student3(
id int primary key,
name char(5) unique,
sex enum(“男”,“女”,“秘密”),
hobby set(“学习”,“睡觉”,“运动”,“追剧”,“玩游戏”,“唱歌”)
);
desc student3;

DDL语句：alter table 语句对字段进行操作
alter 在已有的表中添加、修改、或删除列（元素）
alter table 表名 add 元素（数据类型） --（PS：增加元素）
alter table 表名 modify 元素（数据类型） --（PS：修改元素数据类型）
alter table 表名 change 旧元素新元素（新元素数据类型） --（ps：修改元素）
alter table 表名 drop 元素 --（PS：删除元素）
use lianxiku2;
create table student4(
no int,
name char(4)
);
create table course4(
c_no int,
c_name char(20)
);
desc student4;
alter table student4 add primary key(no);
desc student4;
alter table student4 add age char(3);
desc student4;
alter table student4 modify name char(10);
desc student4;
alter table student4 change age addr char(20);
desc student4;
alter table student4 drop addr;
desc student4;

drop:删除数据库和表 --！！！！！！！慎用
删库：drop database 库名
删表：drop table 表名
create database lianxiku3 default character set utf8;
use lianxiku3;
create table student5(
id int,
name char(5)
);
drop table student5;
drop database lianxiku3;

DML语句：对数据进行操作（增删改—写）
insert：插入
insert into 表名（属性1，属性2，属性3……） values(values1,values2,values3……)
PS：如果是向表的所有属性插入值，表名后的属性可以不写
create database lianxiku3 default character set utf8;
use lianxiku3;
create table student5(
no int auto_increment primary key comment “编号”,
name char(5) comment “姓名”,
sex enum(“男”,“女”,“保密”) default “女” comment “性别”
);
insert into student5(no,name) values(01,“张三”);

create table student6(
no int auto_increment primary key comment “编号”,
name char(5) comment “姓名”,
age char(2) comment “年龄”
);
insert into student6 values(002,“李四”,20);

update:更新数据
update 表名 set 列=values（where 条件）
PS：如果不加where条件则表示更新整张表 --！！！！！！
update 表名 set 要改的字段=要改的值 where 条件
将表student6中编号为1的人的姓名改为王麻子
update student6 set name=“王麻子” where no=“1”;
将表student6中编号为3的人的姓名改为张三，年龄改为18
update student6 set name=“张三”,age=18 where no=“3”;

delete：删除数据
delete from 表名 where 条件
将表student6中姓名为张三的人的信息删除
delete from student6 where name=“张三”;
PS：如果不加where条件则表示删除整张表的数据 --！！！！！！

删除汇总：
delete from where 删除一条数据，一条一条地删除，速度慢
delete from 删除整张表的数据
drop 删除表结构，连表一起删除
truncate table 清空数据,速度非常快,不容易恢复
use lianxiku2;
delete from student1 = truncate table student1;

作业：
创建数据库
修改数据库
删除数据库
create database lianxiku4 default character set utf8;
alter database lianxiku4 character set gb2312;
drop database lianxiku4;
创建一张你家庭成员表
里面有你家庭成员的属性(数据类型)
id
名字
工作地点
手机号
爱好
use lianxiku3;
create table family (
id int,
name char(4),
addr char(20),
tel char(11),
hobby set(“唱歌”,“玩游戏”,“打麻将”,“跳舞”,“rap篮球”)
);

添加家庭成员的属性
修改家庭成员的属性
删除家庭成员的属性
查询家庭成员的属性
alter table family add age char(3);
alter table family change id no char(5);
alter table family drop age;

添加表数据 1 蔡徐坤中国韩国 1XXXXXXX 唱歌、跳舞、rap篮球
修改表数据
删除表数据
查询表数据
show create table family;
CREATE TABLE family (
no char(5) DEFAULT NULL,
name char(4) DEFAULT NULL,
addr char(20) DEFAULT NULL,
tel char(11) DEFAULT NULL,
hobby set(‘唱歌’,‘玩游戏’,‘打麻将’) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into family
values(2,“蔡徐坤”,“中国韩国”,13381263594,“唱歌,跳舞,rap篮球”);
update family set addr = “中国” where name = “蔡徐坤”;
delete from family where no = 2;

#创建员工信息表
CREATE TABLE emp (
id int(4) UNIQUE AUTO_INCREMENT COMMENT ‘表id’,
empno int(4) NOT NULL COMMENT ‘员工编号’,
ename varchar(20) NOT NULL COMMENT ‘员工姓名’,
job varchar(10) COMMENT ‘职位’,
mgr int(4) COMMENT ‘经理编号’,
hiredate date NOT NULL COMMENT ‘入职日期’,
sal float(7, 2) NOT NULL COMMENT ‘工资’,
comm float(7, 2) COMMENT ‘奖金’,
deptno int(2) COMMENT ‘部门号’,
ctime datetime COMMENT ‘创建时间’,
mtime datetime COMMENT ‘修改时间’,
UNIQUE (id),
PRIMARY KEY (empno)
) ENGINE = InnoDB CHARSET = utf8 comment ‘员工信息表’;

CREATE TABLE dept (
id int(4) UNIQUE AUTO_INCREMENT COMMENT ‘表id’,
deptno int(2) COMMENT ‘部门号’,
dname varchar(20) NOT NULL COMMENT ‘部门名称’,
loc varchar(20) NOT NULL COMMENT ‘部门所在地’,
ctime datetime COMMENT ‘创建时间’,
mtime datetime COMMENT ‘修改时间’,
UNIQUE (id),
PRIMARY KEY (deptno)
) ENGINE = InnoDB CHARSET = utf8 comment ‘部门信息表’;

CREATE TABLE salgrade (
id int(4) UNIQUE AUTO_INCREMENT COMMENT ‘表id’,
grade int(2) COMMENT ‘工资等级’,
losal float(7, 2) COMMENT ‘相应等级最低工资’,
hisal float(7, 2) COMMENT ‘相应等级最高工资’,
ctime datetime COMMENT ‘创建时间’,
mtime datetime COMMENT ‘修改时间’,
UNIQUE (id),
PRIMARY KEY (grade)
) ENGINE = InnoDB CHARSET = utf8 comment ‘工资信息表’;

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000001,‘张三’,‘董事长’,10001,‘2017-02-01’,22300.43,7456.34,1001,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000002,‘张四’,‘职位’,10002,‘2017-03-01’,18300.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000003,‘张五’,‘职位’,10003,‘2017-04-02’,12300.43,3456.34,1003,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000004,‘张五’,‘职位’,10004,‘2018-04-01’,9300.43,3456.34,1004,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000005,‘李四’,‘职位’,10005,‘2018-05-02’,8800.43,3456.34,1005,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000006,‘李武’,‘职位’,10001,‘2018-02-01’,7300.43,3456.34,1001,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000007,‘王五’,‘职位’,10002,‘2018-05-01’,6300.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000008,‘刘武’,‘职位’,10002,‘2018-12-01’,7300.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000009,‘李伟’,‘职位’,10003,‘2019-01-22’,9300.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000010,‘刘伟’,‘职位’,10003,‘2019-01-26’,4300.43,3456.34,1003,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000011,‘梁文’,‘职位’,10003,‘2019-02-04’,5300.43,3456.34,1004,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000012,‘王树’,‘职位’,10004,‘2019-02-09’,6300.43,3456.34,1005,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000013,‘马路’,‘职位’,10004,‘2019-02-11’,3300.43,3456.34,1004,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000014,‘请求’,‘职位’,10005,‘2019-02-22’,3600.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000015,‘事上’,‘职位’,10002,‘2019-02-23’,8300.43,3456.34,1003,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000016,‘我名’,‘职位’,10003,‘2019-03-09’,6900.43,3456.34,1004,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000017,‘莫名’,‘职位’,10004,‘2019-03-10’,4300.43,3456.34,1003,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000018,‘好好’,‘职位’,10003,‘2019-03-11’,3500.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000019,‘整治’,‘职位’,10002,‘2019-04-21’,5300.43,3456.34,1002,now(),now());
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,ctime,mtime) value (10000020,‘淘汰’,‘职位’,10004,‘2019-05-01’,6400.43,3456.34,1004,now(),now());

insert into dept(deptno,dname,loc,ctime,mtime) value (1001,‘董事会’,‘贵州省贵阳市花果园’,now(),now());
insert into dept(deptno,dname,loc,ctime,mtime) value (1002,‘技术部’,‘北京市朝阳区’,now(),now());
insert into dept(deptno,dname,loc,ctime,mtime) value (1003,‘产品部’,‘广东省深圳市’,now(),now());
insert into dept(deptno,dname,loc,ctime,mtime) value (1004,‘财务部’,‘贵州省贵阳市花果园’,now(),now());
insert into dept(deptno,dname,loc,ctime,mtime) value (1005,‘市场部’,‘北京市海淀区’,now(),now());

insert into salgrade(grade,losal,hisal,ctime,mtime) value (1,1500,3000,now(),now());
insert into salgrade(grade,losal,hisal,ctime,mtime) value (2,3001,6000,now(),now());
insert into salgrade(grade,losal,hisal,ctime,mtime) value (3,6001,10000,now(),now());
insert into salgrade(grade,losal,hisal,ctime,mtime) value (4,10001,15000,now(),now());
insert into salgrade(grade,losal,hisal,ctime,mtime) value (5,15001,50000,now(),now());

DQL数据查询语言:select — 读 ,查询
语法: 单表
select 要显示的字段1,字段2,字段3,…
from 表 where 限制条件;

表示要显示所有字段
select * from student6 where no = 2;
算数运算:
select 3+2 “和”;
select 0-10 “差”;
select 0*10 “积”;
select 10/1 “商”;
select no + 1 “no+1的和” from student6;

比较运算：> < >= != <> --一般用!=表示不等于
sal —工资
查询出工资低于6000 的人员信息?
desc emp;
select * from emp where sal > 6000;
查询出职位不是董事长的人员信息
select * from emp where job != “董事长”;
特殊的比较:between and , in , like , is null ,is not null
两者之间包含等于的关系 between …… and ……
查询出工资在 6000 - 10000 的人员信息
select * from emp where sal between 6000 and 10000;# between …… and ……只能按从小到大的排序来写
select * from emp where sal <= 10000 and sal >= 6000;

in 在XX里边,必须加()
查询出张三和李四的信息
select * from emp where ename in(“张三”,“李四”)
模糊匹配 like ,单独用like 相当于= 没有什么意义
一般和 _ 或者 % 一起用
_ 表示一个字占位 1个
% 任意字符 0-无数个
查询出姓张的人员信息?
select * from emp where ename like “张%”;
查询出姓名包含四的人员信息
select * from emp where ename like “%四%”;

匹配空值 is null ; is not null
null 只能用 is
select empno,ename,job,mgr,sal,comm from emp where comm is null;
select empno,ename,job,mgr,sal,comm from emp where comm is not null;

逻辑运算符 and or not
and/&& 都满足
or/|| 或者
not/! 非 PS:逻辑运算符!在使用的时候一定要注意不能省略后面括号
SELECT * FROM emp where not ( ename = “张三” or ename = “李四”);
SELECT * FROM emp where !( ename = “张三” or ename = “李四”);

运算符优先级:
数学运算符>比较运算符>逻辑运算符
注意：在WHERE子句中
字符串和日期要用引号括起来
对字符串不区分大小写
日期值是格式敏感的

查询出年薪超过20W,且姓张的人员信息;按照13薪算
select *,(sal+comm)*13 from emp where (sal+comm)*13 > 200000 and ename like “张%”;

去重 distinct 将这个关键字放到第一位
select distinct job from emp;
select distinct(job) from emp;

别名 as 或者空格
select *,(sal+comm)*13 “年薪” from emp where (sal+comm)*13 > 200000 and ename like “张%”;

排序 order by
降序–desc 升序 asc–默认
select ename,sal13 年薪 from emp order by 年薪 desc;
select ename,sal13 年薪 from emp order by 年薪;
计算出人员的年薪,并按照降序排序,如果薪资一样则按照工号的升序排序
select empno “工号”,ename “姓名”,(sal+comm)13 “年薪” from emp order by “年薪” desc, “工号” asc;
select empno 工号,ename 姓名,sal13 年薪 from emp order by 年薪 desc,empno;

分组聚合函数 having的使用
聚合函数:
count() 统计有几条数据
avg() 平均值
max() 最大
min() 最小
sum() 求和
group_concat() 拼接显示
查询有多少个人?
select count() from emp;
select count(),count(comm),count(2) from emp;
count()—统计条数,统计null
count(常量) --=count()
count(字段) — 忽略null值

查询出姓张的人数?
select count(*) from emp where ename like “张%”;
select count(1),avg(sal),max(sal),min(sal),sum(sal),GROUP_CONCAT(ename) from emp;

分组–group by
查询出每个部门的部门人数
select deptno,count() 部门人数 from emp group by deptno;
查询出每个部门的人数,最高工资,最低工资,平均工资,总工资,人员信息,部门号
select deptno 部门号,count() 部门人数,max(sal),min(sal),avg(sal),sum(sal),group_concat(ename) from emp group by deptno;

having函数是针对聚合函数的判断;聚合函数和常量对比
查询出部门人数超过5个的部门
select deptno,count(2),group_concat(ename) from emp group by deptno having count(2) > 5;

limit 分页 ,显示哪些行
select * from emp limit 1;
select * from emp limit 5;
select * from emp limit 1,5;#–1是偏移量，即偏移1行，从第一行起，返回5行数据
select * from emp limit 3,5;#–3是偏移量，即偏移3行，从第二行起，返回5行数据
select * from emp limit 5 offset 1;
PS.数据库数据计算是从0开始的
select * from emp limit 1,5; == select * from emp limit 5 offset 1;

练习1：参考案例2-DQL语句1
1、查询emp表中所有员工的员工姓名
select ename from emp;
2、查询emp表中年薪大于4W的职位
select ename,job,(sal+comm)13 from emp where (sal+comm)13 > 40000;
3、查询emp表中姓张的员工信息
select * from emp where ename like “张%”;
4、查询emp表中1004部门员工的人数
select count(),group_concat(ename) from emp where deptno = 1004;
5、查询emp表中同一天入职的员工人数
select count(),group_concat(ename) from emp group by hiredate having count(*) > 1;
6、查询emp表中最近刚入职的员工姓名
select ename,hiredate from emp order by hiredate desc limit 1;
7、查询emp表中2016年6月前入职的员工姓名
select ename,hiredate from emp where hiredate < “2016-06-01”;