数据库的初始和单表查询
一、使用工具
可以去终端
我的密码: wmt1998
mysql 和 Mysql Workbench、navicat
二、介绍
使用：
mysql层次：不同项目对应不同的数据库组成，每个数据库有很多表，每个表有很多数据
登录：
mysql -hlocalhost -uroot -p
访问数据库：
show databases; 显示数据库列表
use mysql; 切换当前数据库命令
show tables; 显示当前数据库里所有数据表
退出：
quit / exit / \q
卸载：
1.先在服务里停止mysql，或者输入命令：stop mysql ,2.在应用里卸载软件，3.删除安装文件夹。4.删除数据文件夹。5.环境变量删除
图形化工具：
navicat
注意：MySQL8之前的版本加密规则mysql_native_password，而在8版本以后修改为caching_sha2_password,
解决方法：一种是更新navicat驱动解决，另一种修改加密规则为mysql_native_password
用管理员方式进入mysql，然后设置密码永不过期：alter user 'root'@'localhost' identified by 'root' password expire never;
再设置新加密规则：alter user 'root'@'localhost' identified with mysql_native_password by 'root' ;
三、SQL语言的分类
1.数据查询语言：DQL，主要用于数据的查询，其基本机构就是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或者多条数据
2.数据操作语言：DML，对数据库进行增加、修改、删除的操作，主要包括：
INSERT(增加)、UPDATE(修改)、DELETE(删除)
3.数据定义语言：DDL，针对数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改、和删除
CREATE: 创建数据库对象
ALTER: 修改数据库对象
DROP: 删除数据库对象
4.数据控制欲养：DCL，用来授予或者回收访问数据库的权限，
GRANT: 授予用户某种权限
REVOKE: 回收授予的某种权限
5.事物控制语言：TCL，用于数据库的事务管理
START TRANSACTION: 开启事物
COMMIT: 提交事物
ROLLBACK: 回滚事务
SET TRANSACTION: 设置事物属性
6.注释
#单行注释
/*
多行注释
多行注释
*/
实战：
例子：
#创建学生表 create table t_student1( sid int(3), name varchar(10), sex char(1), age int(3), enterdate date, classname varchar(10), email varchar(15) ); #查看表结构 desc t_student; #展示表的详细信息 #查看表数据 select * from t_student; #查看建表语句 show create table t_student;
四、数据库表列类型
1.整数类型 int(4)
int里面的数字显示宽度，但是MySQL就会自动增加，可以定制不限制
2.浮点数类型；double
浮点数类型不会自动扩充， double(4,1) 代表小数部分为一位，总宽度4位
3.字符串类型；char , varchar，blob，text
char；是固定的字符长度，比如性别的男女，只需要一个字就可，此时就可用char，char(1)
varchar；不固定的，varchar(5) ，此时5个以下的字符都可以存，且存多少只占多少内存
blob；存音频，视频。底层是二进制
text；长文本数据
4.日期和时间类型；date，datetime，timestamp
date；精确到年月日
datetime；精确到年月日时分秒
timestamp；时间戳 从1970-01-01开始计算
五、基本使用
表数据操作
1.INSERT插入数据
#在t_student表里插入数据 #查看表记录 select * from t_student; #插入数据 insert into t_student values (1,'张三','男',18,'2022-5-8','软件一班','123@qq.com'); insert into t_student values (2,'张四','男',18,now(),'软件一班','124@qq.com'); #now()代表插入当前时间，也可写成sysdate(),CURRENT_DATE() insert into t_student (sid,name,enterdate) values (3,'张五','2021-10-10'); #如果不是全字段插入数据，需要加入字段的名字(sid,name,enterdate)
2.UPDATE修改表数据
#修改表数据（一定不要忘记加条件） update t_student set sex='女'; #此时未具体指定哪个性别为女，就会对全表的数据操作，全部改成女 update t_student set sex='女' where sid=2; #where sid=2，指定了条件，就会只对条件的数据操作 update t_student set classnum='python一班' where sid=2;
3.DELETE删除操作
#删除操作（一定不要忘记加条件,且在mysql里面一定不要忘记加from） delete from t_student where sid = 3;
数据库表操作
1.修改、删除数据库表
#查看数据 select * from t_student; #修改表结构 #增加一列 alter table t_student add score double(5,2); #添加名为score这一列，且为浮点数类型，此时对表结构操作，就该用alter了 update t_student set score = 123.5678 where sid = 1; #此时虽然设置最大展示为5为，但超过也能插入，但是数据只能存为5位，且四舍五入，此时存的数为123.57 #增加一列，放在最前面 alter table t_student add score double(5,2) first; #增加一列，放在sex列之后 alter table t_student add score double(5,2) after sex; #删除score一列 alter table t_student drop score;
#修改一列 alter table t_student modify score float(4.1); #modify修改的是列的类型意义，但不会改变列的名字 alter table t_student change score score1 score double(5,1); #change可以修改列名和类型，此时change+老的列名+新的列名+类型 #删除整个表 drop table t_student;
表的完整性约束（非外键约束）：保证数据库中数据的准确性和一致性
列级约束：直接添加在字段后面
primary key : 主键约束，约束字段的值可唯一的标识对应记录
not null : 非空的约束，约束字段不能为空
unique : 唯一约束，约束字段值是唯一的
check : 检查约束，限制某个字段的取值范围
default : 默认值约束，约束字段得默认值
auto_increment : 自动增加约束，约束字段的值自动递增
foreign key : 外键约束，约束表与表之间的关系
#创建学生表 create table t_student1( sid int(3) primary key auto_increment, #学号是主键=不能为空+唯一，可通过学号唯一对应一个值，如果主键是int类型，那么需要自增,可不连号1,2,5,6 name varchar(10) not null, #姓名不能为空 sex char(1) default '男' check(sex='男' || sex='女'), #性别默认为男，但是可以检查设置为男或者女 age int(3) check(age>=18 and age<=50), #限制年龄在18-50岁之间 enterdate date, classname varchar(10), email varchar(15) unique #此时邮箱唯一，但可为空 );
表级约束：需要在字段下面用 constraint 来添加，记住 非空约束、默认值约束 不能加表级约束，只能在字段后面加列级约束
如果在已经建好表里面添加；alter table t_student add constraint pk_stu primary key (sid); #添加外键
alter table t_student modify sid int(3) auto_increment; #添加自增
#创建学生表 create table t_student2( sid int(3) auto_increament, name varchar(10), sex char(1), age int(3), enterdate date, classname varchar(10), email varchar(15) constraint pk_stu primary key (sid), #此时给sid加了主键约束，并且主键名字为pk_stu constraint ck_stu_age check (age>=18 and age<=50) #此时给age添加check约束，且名字为ck_stu_age );
外键约束（删除表时，先删除主表，再删除从表）
外键约束用来实现数据库表的参照完整性，外键约束可以使两张表紧密联系起来，特别是级联操作
若在创建表以后添加外键约束：alter table t_student add constraint fk_stu_classnum foreign key (classnum) references t_class (cid) #添加外键，学生表的classnum对应班级表里面的cid
#创建父表 create table t_class( cid int(4) primary key auto_increment, cname varchar(10) not null, room char(4) ); #添加班级数据 insert into t_class values (null,'java001','room3'); insert into t_class values (null,'java002','room4'); insert into t_class values (null,'大数据001','room5'); #一次性添加多条 insert into t_class values (null,'java001','room3'),(null,'java002','room4'),(null,'大数据001','room5'); #创建子表,学生表，添加外键约束，只有表级约束，没有列级约束 create table t_student( sid int(3) primary key auto_increment, name varchar(10), classnum int(4), #参考取值cid字段，不要求字段名字完全重复，但是类型长度定义尽量相同 constraint fk_stu_classnum foreign key (classnum) references t_class (cid) #添加外键，学生表的classnum对应班级表里面的cid ); #添加学生信息 insert into t_student values (null,'张三',1),(null,'张四',1),(null,'张五',2); #查看 select * from t_student;
外键策略：
1.no action 不允许操做，通过操作sql来操作
先删除学生表2的对应的班级,
再删除班级表里面的2
2.cascade 级联操作，操作主表的同时影响从表外键操作
先删除之前的外键约束
alter table t_student drop foreign key fk_stu_classnum;
再重新添加外键约束
alter table t_student add constraint fk_stu_classnum foreign key (classnum) references t_class (cid) on update cascade on delete cascade;
再去更新数据
update t_class set cid = 5 where cid = 2;
再删除
delete from t_class where cid = 5;
3.set null 置空操作
先删除之前的外键约束
alter table t_student drop foreign key fk_stu_classnum;
再重新添加外键约束
alter table t_student add constraint fk_stu_classnum foreign key (classnum) references t_class (cid) on update set null on delete set null;
再去更新数据
update t_class set cid = 5 where cid = 2;
然后数据里面的学生表里面classnum就全部为空了
######## 注意：2,3这些操作可以混着用，alter table t_student add constraint fk_stu_classnum foreign key (classnum) references t_class (cid) on update set null on delete cascade;
补充：DDL和DML的补充
快速创建一张表和 t_student 一样的结构，和删除表里面的数据
#此时结构和数据都和t_student一样 create table t_student1 as select * from t_student; #添加结构和t_student一致，数据没有 create table t_student2 as select * from t_student where 1=2; #因为1永远不可能等于2 #快速添加只要部分列，部分数据 create table t_student3 as select sid,name from t_student where sid=2; #只要sid,name结构，且sid=2这行数据 #只删除表里面的数据,下面两个都可以，只删除数据，不删除结构 delete from t_student; #可以回滚 truncate table t_student; #删除效率更高，无法回滚
DQL的准备
例子里准备四张表：dept(部门表)、emp(员工表)、salgrade(薪资表)、bonus(奖金表)
一、单表查询
SELECT
select * from emp # *代表所有数据 #显示部分列 select empno,ename,sal from emp; #显示部分行 select * from emp where sal > 200; #显示部分行，部分列 select empno,ename,sal from emp where sal > 200; #起别名(此时给empon起名字为员工编号，后面一样，查询显示出来就是别名 select empon 员工编号, ename 姓名, sal 薪资 from emp; select empon as 员工编号, ename as 姓名, sal as 薪资 from emp; #与上面一样 #可以加算术运算符(sal+1000),也可以给算术加别名 select empno,ename,sal,sal+1000 from emp where sal > 200; #去重操作 select distinct job,deptno from job; #此时distinct是对job和deptno都起作用。工作岗位的种类里面就不会查询出来相同工作岗位了。 #排序(默认情况下由低到高) select * from emp order by sal; select * from emp order by sal desc; #由高到底 select * from emp order by sal desc, deptno asc #组合sal降序，deptno升序
WHERE: 加条件
select * from emp where job = 'clerk'; #此时是不区分大小写的，不管是这个clerk的大写数据和小写数据都会查询出来 #区分大小写 select * from emp where binary job = 'clerk'; #此时只能是clerk #日期 select * from emp where hiredate < '1981-12-25'; #逻辑运算符and / or select * from emp where sal > 1500 and sal < 3000 order by sal; #模糊查询 select * from emp where ename like '%A%' #此时ename里面带A的数据 #关于null的判断 select * from emp where comm is null; #返回是空的数据 select * from emp where comm is not null; #返回不是空的数据 #小括号的使用,优先算括号 select * from emp where job = 'clerk' or (job = 'abc' and sal > 2000);
函数的分类：函数就是封装了一些功能，我们拿过来可以直接使用，可以实现对应功能；
#函数举例 #单行函数 select ename,lower(ename),upper(ename) from emp; 转大小写 #多行函数 select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
多行函数：对一组数据进行运算，针对这一组数据，只返回一个结果，也称为分组函数，除了下面的max/min/sum/count/avg，其余都为单行函数
单行函数：对每一条记录输入值进行计算，并得到相应结果。常见函数为：字符串函数、数值函数、日期与时间函数、流程函数
#字符串函数 select ename,length(ename),substring(ename,2,3) from emp; #substring(ename,2,3)代表重ename字符长度为第二个字母开始取，取得长度为3个。不是按照列表索引一样，第一个就是第一个，不从0开始 #数值函数（除下面的还有/除法, %取余，mod(10,3)取模） select abs(-5), ceil(5.3),floor(5.9),round(3,14) from emp; #abs取绝对值，ceil向上取整此时为6，floor向下取整此时为5，round四舍五入此时取3 #日期与时间函数 select curdate(),curtime(); #curdate()只有年月日，curtime()只有时分秒 select now(),sysdate(),sleep(3),now(),sysdate() from dual; #前后两个now()的时间是一样的，而第二个sysdate()比第一个晚三秒。dual为伪表。 #流程函数 #if相关 select empno,sal,if(sal >= 2500,'高薪','低薪') as '薪资等级' from emp; #if - else双分支结构 select empno,sal,comm,sal + ifnull(comm,0) from emp; #如果comm为空，那么空值就置为0. select nullif(1,1),nullif(1,2) from dual; #nullif(值1,值2)，如果值1等于值2，那就返回空，如果不相等就返回值1 #case相关 select ename,job, case job when 'clerk' then '店员', when 'salesman' then '销售', when'mam' then '经历', else '其他' end, sal from emp;
#clerk显示为销售，后面一样，其余不是里面提到的名字，职位显示为其他，里面的when后面也可为区间如；when sal <= 100 then 'A' , case必须以end结尾 #其他函数 select database(),user(),version() from dual; 查询数据库，管理员，版本
GROUP BY分组
#统计各个部门的平均工资 select deptno,avg(sal) from emp group by deptno; #字段和多行函数不可同时使用，除非属于分组 select deptno,avg(sal) from emp group by deptno order deptno desc;
HAVING分组后筛选
#统计各部门的平均工资，只显示平均工资大于2000的，分组后二次筛选 select deptno,avg(sal) as 平均工资 from emp group by deptno having 平均工资 > 2000; #统计各个岗位的平均工资，除了manager #方法1 select job,avg(sal) from emp where job != 'manager' gorup by job; #方法2 select job,avg(sal) from emp gorup by job having job != 'manager';
