1、数据库基础知识
idea连接数据库步骤：https://www.cnblogs.com/Ran-Chen/articles/9646187.html
1.1数据库概述
什么是数据库:
数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
什么是数据库管理系统：DBMS
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
常见的数据库管理系统
MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中。
SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。SyBase ：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。SQLite : 嵌入式的小型数据库，应用在手机端。
Java相关的数据库：MYSQL，Oracle．
这里使用MySQL数据库。MySQL中可以有多个数据库，数据库是真正存储数据的地方。
1.2数据库表 student.sql
数据库中以表为组织单位存储数据。
表类似我们的Java类，每个字段都有对应的数据类型。
那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。
类----------表，类中属性----------表中字段，对象----------记录
1.3表数据
根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的一行一行的信息我们称之为记录。
表记录与java类对象的对应关系：
如果mysql服务已经停止，在登录时会出现以下的错误；
解决方案：计算机--》右击--》管理--》服务--》启动mysql服务就可以了
1.DBMS：数据库管理系统
DBMS= 管理程序+多个数据库 mysql:c626：部门表，学生表，学生信息表...
DataBase = 多张表（table），table = 表结构+表记录
根据表结构定义对应的类：实体类；student:
name,age,sex----->表结构：定义对应的列名；表记录：张三，18，男
2. 2.1. 什么是SQL：
结构化查询语言(Structured Query Language)。
2. 2SQL的作用：
客户端使用SQL来操作服务器。
3.sql的分类
1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等； > 创建、删除、修改：库、表结构！！！
2. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；> 增、删、改：表记录
3. DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。
4. DCL（Data Control Language）：数据控制语言，用来进行数据库权限的控制。
4.登录mysql服务端
--打开dos窗口的方式：从附件中选择运行；window键加R
-- 注释格式：--加空格
-- 连接mysql数据库
mysql -uroot -proot
mysql -uroot -p 加回车 -- 密码是星号，保证密码安全
5.-- 属性与数据库的DDL，对数据库，建库，删库，建表，删表，修改数据库字符集，该表名，修改字段
-- 查询mysql数据库已有的数据库，show databases;展示所有数据库
-- 创建数据库，create database c1706a;创建数据库c1706A;
-- 删除数据库，drop database c1706a;
-- 查看数据库新创建的信息或者默认字符集，show create database c1701a;
-- 创建数据库是没有设置字符集，set names gbk;
-- 修改数据库，alter database c1701a default character set utf8;
-- 选择要使用的数据库，use c1701a;
-- 查看当前数据库中的表，show tables;
-- 创建表：创建一个学生表，存储学生信息：id,学生姓名，年龄
java:int String double mysql:int varchar(长度可变的字符串)， char(长度不可变)，double
create table t_student(id int(11) primary key auto_increment,sname varchar(50),age int(6));
-- 查看表结构describe t_student;desc t_student;
-- 删除表drop table t_student;
修改表：前缀：ALTER TABLE 表名
表名修改之-添加列： ALTER TABLE 表名 ADD (列名 列类型, 列名 列类型,... );
修改之-修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据)：ALTER TABLE 表名 MODIFY 列名 列类型;
修改之-修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
修改之-删除列：ALTER TABLE 表名 DROP 列名;
修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;
如果在黑窗口执行命令时，发生了乱码，:set names gbk;
默认设置的是utf8,黑窗口是gbk;
-- 以下操作数据DML,只对表中的数据进行增加，修改，删除，查询
-- 查看表中的数据(查询表中的数据)select * from t_student;
-- 添加数据库到学生表中
insert into t_student value(null,"张三",20);
insert into t_student(sname,age) value("李四",21);
insert into t_student(sname,age) values("王五",22);
insert into t_student(sname,age) value("赵六",23),("田七",24),("马八",25);
insert into t_student(age,sname) values(1,"鲁迅");
-- 删除数据
运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR(||)、AND(&&)、between... and: >= and <=
in(..):只要被包含，就是符合条件的
is null:只要为空，就是符合条件的not:
is not null不为空就是符合条件的,如果写成not is null则错误；
or(||),and(&&)
-- 指定id值删除数据delete from t_student where id=7;
-- 指定学生名删除delete from t_student where sname='田七';
-- 删除多个数据,把年龄小于23岁的学生删除delete from t_student where age <23;
-- 删除表中的所有数据delete from t_student;
-- 删除id为14,15delete from t_student where id=14 or id=15;
-- 删除id为9,11,12,1delete from t_student where id in(9,11,12,1);
--删除表中性别不为null的数据delete from t_student where sex is not null;
truncate table t_student:先删除表，然后再创建表达到删除数据的目的；不可回滚；
-- 修改数据:update 表名 set
update t_student set sname="李四" ,age=21 where sname="张三";
update t_student set sname="张三" ,age=100 where id=10;
—————————————————————————————————————————
-- 数据类型
-- int 整型 ,int(m)，这里边的m宽度，没有实际意义
-- 注意：建表时，不能使用关键字作为列名
-- 创建表 t_person
create table t_person(pid int(11) primary key auto_increment,age int(1),pname varchar(2),pdate date,ptime time,tp timestamp,dt datetime);
insert into t_person(age) value(1);
insert into t_person(age) value(123456);
-- 字符串 varchar(m)
insert into t_person(pname) value("aa");
insert into t_person(pname) value("aaa");-- ERROR 1406 (22001): Data too long for column'pname' at row 1
insert into t_person(pname) value("你好");-- 汉字在数据库中与字符大小一样
insert into t_person(pname) value("你好吗");
-- char ,double ,text,longtext,
-- 字符串最大宽度为255
-- date yyyy-mm-dd，time hh:mm:ss timestamp yyyy-mm-dd hh:mm:ss datetime
-- mysql 数据库中时间支持字符串：pdate:date
ptime:time
tp:时间戳 dt:datatime
insert into t_person(pdate,ptime,tp,dt) value("2017-03-03","14:01:01","2017-03-03
14:01:01","2017-03-03 14:01:01");
-- TRUNCATE TABLE 表名
表结构不变，删除时保留表头，id从新自增
truncate table t_student; --DDL操作
-- DELETE FROM 表名 [WHERE 条件];
只是清空表中的数据，id还是继续自增
-- 查询操作
-- 查询学生表中的所有数据
select * from t_student;-- 效率低
select id,sname,age from t_student;-- 是开发时建议使用这种查询
-- 查询学生的姓名和id
select id,sname from t_student;
-- 加条件
-- 查询年龄小于23的学生
select * from t_student where age<23;
-- 查询学生年龄在21到24之间的学生信息
select * from t_student where age<24 and age >21;
-- 查询学生年龄在21到24之间的学生信息,包括21和24岁select * from t_student where age<=24 and age >=21;
select * from t_student where age between 21 and 24;
-- 查询年龄不大于23岁的学生
select * from t_student where age !> 23;-- mysql数据库不支持!> 和!<
select * from t_student where age<=23;
-- 查询年龄不是23岁的学生信息
select * from t_student s where s.age !=23;
select * from t_student s where s.age <> 23;
-- and && 连接的条件同时满足，or ||，满足一个查一个
-- 查询年龄小于21岁,或者年龄大于24
select * from t_student s where s.age<21 or s.age>24;
-- 查询id小于8并且姓名为李四的学生
select * from t_student s where s.id<8 and s.sname='李四';
-- 可以给表取别名 表名（列名，聚合函数，子查询）as 别名
-- 查询学生的姓名
select s.sname from t_student s;
select t_student.sname from t_student;
-- t_person(pid,name);
-- t_student(sid,name);
select p.name,s.name from t_person p,t_student s;
-- 查询,别名只在查询时使用
-- 查询赵六的学生信息
select * from t_student s where s.sname='赵六';
-- 别名在删除语句中不能使用
delete from t_student s where s.sname='赵六';
-- 给列取别名
select sname as '姓名',age '年龄' from t_student;
-- 和并列
-- 把主键值和年龄相加
select (id+age) as '和' from t_student;
-- 查询结果中不显示重复数据select distinct sname ,age from t_student;
-- in(), 括号中的值，匹配上哪个就查询出来那条数据
-- in 条件查询，数据有量的限制
-- 查询符合条件的学生信息,张三，李四，赵六
select * from t_student s where s.sname in("张三","李四","赵六");
-- 查询学生姓名不为空的数据
insert into t_student(age) values(30),(10);
-- 判空条件
select * from t_student s where s.sname !=null;-- 不支持null的不等于判断
select * from t_student s where s.sname is not null;
not 列名 is null;--->不建议使用
select * from t_student s where s.sname is null;
-- 模糊查询
insert into t_student(sname) values("鲁四迅"),("四五六"),("张三四");
-- like _ ： _代表的是一个字符
select * from t_student s where s.sname like '_四';
select * from t_student s where s.sname like '四__';
select * from t_student s where s.sname like '_四_';
-- like %： %代表0到多个字符
select * from t_student s where s.sname like '%四';
select * from t_student s where s.sname like '四%';
select * from t_student s where s.sname like '%四%';
-- 聚合查询
-- sum()，求和
-- 查询所有学生的年龄总和
select sum(s.age) as cc from t_student s;
-- avg(),求平均数
-- 查询所有学生的平均年龄
select avg(s.age) avgage from t_student s;
-- 查询最大，最小年龄
select max(s.age) x,min(s.age) n from t_student s;-- count()统计总记录数
select count(1) from t_student;
select count(*) from t_student;
select count(id) from t_student;
select count(sname) from t_student;
-- 查询系统当前时间
-- dual 表 ，伪表，虚表
select sysdate() from dual;
select now() from dual;
select curdate() from dual;
select current_date();，Thread.currentThread().getName();获取正在执行的线程的名称；
select curtime() ;
select 3*3;
select 3*3 from dual;
select 3-3;
-- 向人表中添加时间
insert into t_person(pdate,ptime,tp,dt)
value(curdate(),curtime(),sysdate(),now());
-- 拼接字符串
select concat("00","aa","bb","cc");
-- 分页查询(limit 起始行，查询几行)
-- limit m :m 代表查询的记录的前m条
select * from t_student limit 5;
-- limit m,n:一般用于分页m = (page-1)*n -->一页两条数据：n=2; 第三页 ：m = (3-1)*2;
-- m:先从第m条记录开始，不包括该条记录，n代表查询出n条记录
select * from t_student limit 4,3;
-- 查询数据每页显示3条记录
第一页 0,3(page-1)*n
select * from t_student limit 0,3;
第二页 3,3(m-1)*n
select * from t_student limit 3,3;
第三页 6,3(m-1)*nselect * from t_student limit 6,3;
第四页9,3(m-1)*n
select * from t_student limit 9,3;
第五页12,3(m-1)*n
select * from t_student limit 12,3;
-- null是最小的数字
-- 排序,一般只对数字类型列排序
-- -order by
-- 语法 ：order by 字段 asc/desc
-- asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
-- 查询所有学生，年龄按升序排序
select * from t_student order by age ;
select * from t_student order by age asc ;
-- desc: 倒序，反序，逆序。数值：递减，字母：自然反序(z-a)
-- 查询所有学生，年龄按降序排序
select * from t_student order by age desc ;
-- 分组 group by 列名
alter table t_student add sex varchar(20);-- 增加性别列
-- 统计男女同学数量
select count(*) from t_student group by sex;
-- having，从分组后的数据中过滤数据
select count(*) from t_student group by sex having sex is not null;
select * from t_student where sex is not null;
select * from t_student having sex is not null;
-- 数据约束
-- 默认值 default
create table t_user(uid int(11) primary key auto_increment,uname varchar(20),sex char(3) default '男');
insert into t_user(uname) value("李四");
insert into t_user(uname,sex) value("张三",'女');
-- 非空 not null
create table t_user1(uid int(11) primary key auto_increment,uname varchar(20) not null,sex char(3) not null default '男');
insert into t_user1(uname) value("张三");
-- Unique :使用唯一约束的字段，插入的值不能重复。insert into t_user1(uname) value("张三");
delete from t_user1 where uid=2;
create table t_user1(uid int(11) primary key auto_increment,uname varchar(20) not null unique,sex char(3) not null default '男');
insert into t_user1(uname) value("张三");
insert into t_user1(uname) value("张三");
-- 主键约束：primary key 对一个字段加上主键约束，控制字段的值不能为null，而且必须唯一，不能重复。
-- primary key :包括两个约束：unique，not null
-- 设置成主键的字段，一般与业务无关（代理主键）（推荐使用）
-- 与业务相关的列作为主键列（自然主键）
-- 组合主键
--自增长： auto_increment
-- 设置自增长之后的字段，可以不赋值，默认的自动增长1
默认约束：default
唯一约束：unique
非空约束：not null
主键约束：primary key
自增：auto_increment
-- DDL操作，对表结构，数据库进行的操作
-- 建库create database 库名
-- 删库drop 库名
-- 建表 create table 表名(列名 列类型（宽度），其他列...);
-- 删表drop table 表名
-- 改表名alter table t_user1 rename to t_user2;
rename table t_user2 to t_user1;
-- 修改字段名称 column(列) column添加，删除，修改列的语法中可以省略
alter table t_user1 change column uname uname1 varchar(30);
alter table t_user1 change uname1 uname varchar(20);
-- 添加列
alter table t_user1 add column age int(3) default 20;
alter table t_user1 add column tel varchar(30) first;-- 在第一列位置添加新列
alter table t_user1 add column address varchar(50) after uname ;-- 在指定列后添加一列
-- 删除一列
alter table t_user1 drop tel;
-- 设计的三大范式
-- 范式:建表的规则
-- 建立学生表
班级t_class(cid,cname)， t_stu(sid,sname,cid外键);
-- 数据库第一范式
-- 1）业务数据不能都写在表中的一列里边；
-- 2）列名不能重复
-- 数据库第二范式
-- 每张表要有一个其他列依赖的唯一列（主键）
-- 数据库第三范式
-- 如果表与表之间存在关联关系，建立外键
-- 把主键做为另一张表的外键的表成为，主表或父表
-- 把持有另一张表的主键作为外键列的表成为，从表或子表
主表
create table t_class(cid int(11) primary key auto_increment,cname varchar(200));
在外部添加主键：
alter table t_class add primary key (cid);
insert into t_class(cname) value("c1701a"),("c1701b");
从表
create table t_stu(sid int(11) primary key auto_increment,sname varchar(200),cid int(11)：外键);
insert into t_stu(sname,cid) value("张三",1),("李四",1),("王五",2),("赵六",2);
-- 查询所有班级及班级下的学生
select * fromt_class c ,t_stu swhere c.cid=s.cid;
-- 外键设置
create table t_school(scid int(11) primary key auto_increment,name varchar(50),tel varchar(14));
-- 给班级表添加外键列
alter table t_class add scid int(11);
-- 添加外键约束
alter table t_class add foreign key(scid) references t_school(scid) on delete cascade on update cascade;
-- 建表时直接加外键约束
create table t_class(cid int(11) primary key auto_increment,cname varchar(200),scid int(11),foreign key(scid) references t_school(scid) on delete cascade on update cascade);
create table t_stu(sid int(11) primary key auto_increment,sname varchar(200),cid int(11),foreign key(cid) references t_class(cid) on delete cascade on update cascade);
-- 查询学校下的所有班级，班级下的所有学生
select * from t_school sc ,t_class c ,t_stu s where sc.scid=c.scid and c.cid=s.cid;
-- 班级，学校是主表，学生是子表
t_school(scid,scname);
t_class (cid,cname);
t_stu(sid,sname,scid外键,cid外键);
insert into t_school(name) value("积云"),("吉利");
insert into t_class(cname,scid) value("c1701a",1),("c1701b",1),("c1701ajl",2);
insert into t_stu(sname,cid) value("张三",4),("李四",4),("王五",5);
-- 删除吉利级联删除吉利的班级和学生
delete from t_school where scid=1;
-- 在子表中只是持有主表的主键值作为列，该列成为形式外键；
-- 内连接查询-- 创建部门，员工表
create table t_dept(did int(11) primary key auto_increment,dname varchar(50));
create table t_emp(eid int(11) primary key auto_increment,ename varchar(50),did int(11)-- 形式外键);
添加主外键以后的数据操作注意事项：
1.先添加主表再从表
2.先删除从表再主表
如何解决直接删除主表即可？设置级联删除：on delete cascade;
更新：on update cascade;
练习：
1.创建一个school表：scid,scname
2班级表：cid,cname,scid(需要知道班级是哪个学校的)
3.学生表：sid,sname.cid需要知道学生的所属班级)
添加删除数据操作；
alter table t_emp add foreign key(did) references t_dept(did) on delete cascade;
--增加外键约束
insert into t_dept(dname) value("技术部"),("市场部"),("人事部");
insert into t_emp(ename,did) value("张三",1),("李四",2),("王五",3),("王五",4);
insert into t_emp(ename,did) value("王五11",3);
部门表：主表
did 主键，dname
员工表：从表
eid，ename，salary
did：外键
-- 1）等值连接：方言---------------》查询出的数据都是符合条件的，不符合条件的将不
会显示；
-- 查询部门及部门下的所有员工
select * from t_dept d,t_emp e where d.did = e.did;
-- 2)inner join 连接表 on 官方
select * from t_dept d inner join t_emp e on d.did = e.did;
-- 外连接：左外连接 left outer join on
左外：会展示左表中的所有数据，不符合条件部分，以null值顶替
右外：会展示右表中的所有数据，不符合部分，null值顶替
insert into t_dept(dname) value("UI部");
-- 查询所有部门，及部门下的员工
select * from t_dept d left outer join t_emp e on d.did = e.did;
select * from t_dept d left join t_emp e on d.did = e.did;
-- 右外连接right outer join on
-- 查询所有部门，及部门下的员工
select * from t_emp e right join t_dept d on d.did=e.did;
-- 添加员工表，不属于任何部门数据
insert into t_emp(ename) value("鲁迅");
select * from t_emp e left join t_dept d on d.did=e.did;
-- mysql不支持全连接查询 full outer join内连接和外连接的区别？
内连接：只展示符合条件的数据；
外连接：左外：展示左表中的全部数据，右外：展示右表中的全部数据；
不符合条件的数据：null代替
-- 子查询:查询语句的结果是另一个查询的条件，前边的查询语句成为主查询；
-- 查询年龄最大的学生信息从t_student表中
select max(age) from t_student;
select * from t_student where age=(select max(age) from t_student);
-- 查询员工张三所属部门的信息
select * from t_dept d where d.did=(select did from t_emp where ename='张三');
-- （了解）
-- 存储过程
delimiter $ -- 声明存储过程的结束符
create procedure pro_test(in stuid int) begin select * from t_student where id=stuid; end $;
call pro_test(1);-- 调用存储过程
-- 带参数的存储过程 输出参数
create procedure pro_test3(out num1 int)
begin
set num1=3;
end $
call pro_test3(@num2);
select @num2; -- 查询会话参数值
-- 触发器
-- 当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！
-- 创建触发器
create trigger tri_comment_add after insert on tbl_comment for each rowupdate tbl_info set commentNum=commentNum+1 where id=new.infoId;
-- DCL操作
-- 创建新用户
CREATE USER 'a'@'localhost' IDENTIFIED BY '1';
-- 给用户付权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON *.* TO 'a'@'localhost';
-- 修改用户密码
SET PASSWORD FOR 'a'@'localhost' = PASSWORD('11');
-- 删除用户
DROP USER 'a'@'localhost';
--备份数据库
mysqldump -uroot -proot c1706a>d:aaaa.sql
-- 还原数据库
mysql -uroot -proot c1701a<d:aaaa.sql
-- 重点掌握：简单的ddl操作(建库，删库，建表，删除表)
dml（增删改查，多表（等值查询，左-右外连接））
linux:
练习：
2.2.1公司表t_company(cid,cname,tel,address);
部门表t_dept(did,dname,dno部门编号,cid外键);
员工表t_employee(eid,ename,age,eno员工编号,did外键);
1） 手工添加添加测试数据；
2） 查询公司及下属部门，以及部门有员工的部门下的所有员工；
3） Select * from t_company c, t_dept d,t_employee e where c.cid=d.cid and
d.did=e.did;
2.2.2公司表t_com (cid,cname,tel,address);
部门表t_dep (did,dname,dno部门编号);员工表t_emp (eid,ename,age,eno员工编号,did外键,cid外键);
4） 手工添加添加测试数据；
5） 查询公司及下属部门，以及部门有员工的部门下的所有员工；
Select * from t_company c, t_dept d,t_employee e where c.cid=e.cid and d.did=e.did;
2.2.3商品类型表t_type(tid,tname);
商品表t_goods(gid,gname,price价格为double双精度类型,tid外键);
CREATE TABLE `t_type` (`tid` int(11) PRIMARY KEY (`tid`) AUTO_INCREMENT ,`tname` varchar(50) ,);
CREATE TABLE `t_goods` (`gid` int(11) NOT NULL AUTO_INCREMENT,`gname` varchar(50) DEFAULT NULL,`price` double(10,2) DEFAULT NULL,`tid` int(11) DEFAULT NULL,PRIMARY KEY (`gid`))
1） 外键设置约束，通过后添加外键约束语句完成；
alter table t_goods add foreign key(tid) references t_type(tid) on delete cascade on update cascade;
2） 测试数据数据手工添加；
3） 查询商品类型下的所有商品；
select * from t_goods g,t_type t where g.tid=t.tid;
4） 计算各个商品类型下商品的总价格，并查询商品类型名称；
select t.tname ,aa.sm from t_type t, (select SUM(g.price) sm,g.tid from t_goods g GROUP BY g.tid) aa where t.tid=aa.tid; select SUM(g.price),t.tname from t_goods g,t_type t where t.tid=g.tid group by t.tname;
5） 查询任意商品类型的所有商品，按价格降序排序；
6） select * from t_goods g where g.tid=1 ORDER BY price DESC;
7） 查询商品价格最贵的商品信息；3、 select * from t_goods g ,t_type t where g.tid=t.tid and g.price=(select max(price) from t_goods);