java学习笔记(MySQL)

1.数据库添加/删除/查询:

    1)展示数据库的内容:

show databases ;

     2)创建数据库:

create database + 库名;

    3)删除库:

drop database +库名;

      4)查询创建数据的默认字符集格式:

show create database+库名;

     5)默认查询当前数据库中所有的带字符集的字符集格式.

show variables like '%%character%%';   

2.数据库表添加/删除/修改:

      1) 创建表的前提:

use +库名; 创建表的前提,先选择库,进入到库中.

       2)查询这个库中的所有的表:

show  tables;

      3)查询表的结构:

desc 表名;(可以看到表的字段以及字段的类型).

       4)创建表:

create table 表名(
    -> 字段    字段类型 ,
    -> ;字段   字段类型.
    -> );

     5)修改表的字段名称:

alter table 表名  change 旧字段名称 新字段名称 数据类型(旧字段的);

     6)修改字段类型:

alter table 表名 modify 字段名称 新的字段类型;

    7)添加一个新的列(新的字段):

alter table 表名 add 新的字段名称 类型(alter table student add socre double(3,1););

    8)删除某一个字段:

alter table 表名 drop 字段名称;

    9)复制一张新的表跟以前的表结构相同:

create table 新的表名 like 以前的表名;

     10)修改的表的名称:

alter table 以前的表名 rename to 新的表名;

      11)删除表:

   drop table 表名 ;
   drop table if exists 表名;

3.数据库的DML语句:

      1)一次性插入全部字段值:

 insert into 表名 values(字段名称1的值,字段名称2的值,....) ;
     一次性插入全部字段值以及多行信息(注意按照顺序)

      2)一次性插入部分字段值:

insert into 表名(字段名称1,字段名称2...) values(值1,值2,值3....) ;
                           注意:按照顺序.

      3)修改字段:

update 表名 set 字段名称1 = 值1,  ......  等:后期都是where id = 值 ;

      4)批量修改字段值:

update 表名 set 字段名称 = 值;

       5)删除语句整行:

delete  from 表名 where 带条件删除:DELETE FROM student WHERE id =  7 ;

        6)删除的是全表记录:

delete  from 表名:删除的是全表记录(注意:仅仅只是删除表的记录, 
      不会删除这个表的结构,那么针对自增长约束的字段不影响)
 
   truncate table 表名;删除的全部表记录,
         自增长的主键约束(数据库约束)者这个语法它会将影响

      7)创建一张带一个自增长主键约束表:

创建表 create table 表名(   
-> id int primary key auto_increment, ---id自增长住建唯一    
-> 字段   字段类型  
-> );

      8)查询全表(或指定的字段)内容:

select * from 表名:
 
select  全部字段名称(或指定的字段) from 表名:

       9)给表起了个别名:

SELECT
   s.`id` '编号',
   s.`name` '姓名',
   s.`age` '年龄',
   s.`address` '地址'
   
FROM
 student s; -- 给表起了个别名 s (as省略可以不写)

      10)数据库的DISTINCT(字段名称),可以实现字段去重:

SELECT 
 DISTINCT(address)  -- 数据库的DISTINCT(字段名称),可以实现字段去重
FROM student ;

       11)求和(字段内有空值null):

FROM student ;
-- 两个数据类型一致:int,求和 举例:英语和数学字段;
-- 如果有一个字段的值位null(空值),没有意义,数学+NULL = NULL
-- 数据库的函数 ifnull(字段名称,预期值) ;
SELECT 
     id '学号', 
     NAME '姓名', 
     math+IFNULL(english,0) '总分' 
FROM 
     student ;

4.数据库的DML语句:

      1)关系运算符/逻辑运算符( > ,= ,!=):

第一种大于:
SELECT 
	id '编号',
	NAME '姓名',
	age '年龄',
	sex '性别',
	address '地址'
 
FROM
	student 
	
WHERE 
	age >20 ;   比较运算符 大于 等于 小于 不等于(!=20 /<>)

      2)逻辑或运算:

第一种:
-- 查询年龄在20-30岁之间的学生信息 
-- (Java中逻辑符号呢?&& 可以,数据库and ,使用between 值1 and 值2)
SELECT
    id,
    NAME,
    age,
    sex,
    address,
    math,
    english
FROM 
    student 
WHERE
     age>=20 && age<=30 ;   Java中的逻辑符号 && 或add
 
第二种:
 SELECT
    id,
    NAME,
    age,
    sex,
    address,
    math,
    english
FROM 
    student 
WHERE 
     age BETWEEN 20 AND 30 ;    

     3)查询某字段值为空或不为空的:

要查询英语成绩为null的学生信息
SELECT
	id,
	NAME,
	age ,
	address,
	english
FROM 
	student
WHERE 
     english IS NULL ;
 
要查询英语成绩不为null的学生信息  
SELECT
	id,
	NAME,
	age,
	address,
	english
FROM
	student
WHERE 
	english IS NOT NULL ;

      5)查询某字段的或值:

-- 要查询年龄是18或者20,或者30的学生信息
SELECT 
	id ,
	NAME,
	age,
	sex,
	address
FROM
	student
WHERE 
	age =18 OR age = 20 OR age = 30 ;  -- || 或者 or

5.DQL语句:

      1)使用逻辑运算符查询

where 条件后面跟表达式(关系运算符/逻辑运算符 ||(or)  &&(and) between...and...  )
SELECT
     id ,
     NAME,
     sex,
     age,
     score
FROM
     students
WHERE 
        age >= 20;    
        age !=或(<>) 20 ; 
        age>=20 && age<=30 ;  
        age>=20 AND age<=30 ;
        age BETWEEN 20 AND 30 ; -- Java中的逻辑符号  
        age =18 OR age = 20 OR age = 30 ; age IN(18,20,30) ;  -- || 或者 or 

       2)数据库中:某个字段名称 is null   /某个字段名称 is not null

SELECT
	id,
	NAME,
	age ,
	address,
	english
FROM 
	student
WHERE 
      english IS NULL ;空
      english IS NOT NULL ;非空

6.模糊查询:

      1)查询某字段中包含特殊字:

SELECT
     id,
     NAME,
     age,
     sex,
     address
FROM 
     student
WHERE 
-- 查询学生姓名中包含马的学生
	NAME LIKE '%%马%%' ; 
-- 姓名中第二个字符是化的学生新'%%_化%%'
    NAME LIKE '%%_化%%' ;
-- 姓名包含三个字符的人的信息
    NAME LIKE '___'  ;

7.聚合函数:

    1)单行单列的数据

 count(字段名称) 查询总记录数
 count(字段名称:一般情况使用的非业务字段
 max(字段名称)  求某个列中的最大值
 min(字段名称)  ...最小值
 sum(字段名称) 针对某个字段列表进行求和
 avg(字段):针对某个字段列求平均分
SELECT
	COUNT(IFNULL(english,0))  '总记录数' -- ifnull(字段名称,预期值) 
    COUNT(id) '总人数'
    MAX(math) 
    MIN(IFNULL(english,0)) '最低分'
    AVG(math) '数学平均分' 
FROM
	student ;

     2)排序查询order by:

格式:
select 
    字段列表 
from 
    表名 
order by  
   字段名称 排序方式;
SELECT
	*
FROM 
	student
ORDER BY  
	math ; 不带排序方式:默认 asc
将英语成绩按照降序排序
SELECT
	*
FROM
	student
ORDER BY 
	english DESC ;

       3)分组查询 group by:

SELECT
	sex '性别', 可以查询分组字段
	COUNT(id) '总人数',
	AVG(math) '数学平均分'
FROM
	student
GROUP BY 
	sex  ;  性别分组
 
1):数学成绩不大于70分的人不参与分组 
 
SELECT
	sex '性别', -- 可以查询分组的字段
	COUNT(id) '总人数',
	AVG(math) '数学平均分'
FROM
	student  
where 
	math > 70 
GROUP BY 
	sex;
   注意: 上面语法错误:正常是要先满足条件,在分组:如果sql语句中存在where语句以及group by
语句,先进行条件,然后再分组,group by 的后面不能where 语句,where 必须在group by之前;
 筛选(过滤) having	
 按照性别分组,查询他们的数学的平均分以及总人数
 1):数学成绩不大于70分的人不参与分组 
 2):筛选出总人数大于2的一组
 
	having和group by 的区别
	having后面可以使用聚合函数,而group by 的后面不能使用聚合函数查;
	如果存在where 语句,group by,having,先where条件,然后分组group by ,
	然后筛选having;
 
SELECT
	sex '性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM
	student
WHERE 
	math > 70   条件
GROUP BY 
	sex   分组
 筛选出总人数大于2的一组
HAVING 
    COUNT(id) > 2 ;

       4)分页查询 limit:

select 
   字段列表 
from 表名 limit 起始行数(起始索引),每页显示的条数;
 起始行数(起始索引):从0开始计算 = (当前页码-1)*每页显示的条数
 查询当前学生表中第一页数据,每页显示2条数据;
SELECT
	*
FROM
	student
LIMIT 0,2 ;
 
 查询第二页的数据
 起始行数= (2-1)*2 
      .........

       5)数据库的约束:

约束:约束用户操作表时候的行为,防止无意义的数据在表中存在!
1. 默认约束 default
 
CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20),
	gender VARCHAR(5)  DEFAULT '女' -- 创建的时候给性别添加默认约束,default
);  
    1)举例: 如果插入部分字段的时候,没有插入的字段就是NULL,没有意义 ,需要加入约束这种非法行为
INSERT INTO stu_test(id,NAME) VALUES(3,'高圆圆') ; -- 加入约束,不插入默认约束就起作用
 
 
 2.非空约束 not null
    一些业务字段的必须存在值,设置非空约束
CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20)  NOT NULL -- 非空约束
)  ;
     举例:insert into stu_test values(3,null) ;-- Column 'name' cannot be null :非空约束起作用
      1)通过修改表:删除非空约束
             ALTER TABLE stu_test MODIFY NAME VARCHAR(20) ;
      2)通过修改表:添加非空约束
             ALTER TABLE stu_test MODIFY NAME VARCHAR(20) NOT NULL  ;
 
 3.唯一约束 unique
   字段值不能重复
   1)创建一张表
CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20),
	
	phone VARCHAR(11) UNIQUE  -- 添加唯一约束
) ;
 
  举例:
    1)删除唯一约束 (drop index 唯一约束的字段名称)
      alter table stu_test modify phone varchar(11) ;
      ALTER TABLE stu_test DROP INDEX phone ;
    2)添加唯一约束
      ALTER TABLE stu_test MODIFY phone VARCHAR(11) UNIQUE ;
 
 4.主键约束 primary key 非空且唯一
     注意:一般开发中,一张表都会有也非业务字段id,自增长的主键
  业务字段不会将它设置为主键,因为业务在的数据在不断变化!
     id 给定值,下次它会随着之前的值继续自增
 
CREATE TABLE stu_test(
	id INT  PRIMARY KEY, -- 编号 添加主键约束
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	address VARCHAR(50)
);
   1)通过修改表:删除主键 的语法
ALTER TABLE stu_test DROP PRIMARY KEY ;
   2)通过修改表:添加主键
ALTER TABLE stu_test MODIFY id INT PRIMARY KEY ;
   
 
 5.自增长约束 auto_incrment
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
	NAME VARCHAR(20), -- 员工名称
	age INT, -- 年龄
	gender VARCHAR(5) ,-- 性别
	dept_name VARCHAR(10) -- 部门名称
) ;
   1)查询最后一次自增长主键的值(后期mybatis框架中使用:获取最后一次自增长主键的id值)
SELECT LAST_INSERT_ID() ;
 6.外键约束 foreign key
 创建一个部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
	dept_name VARCHAR(10)
);
-- 插入三个部门
INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;
创建员工表
-- id 编号,name 员工名称,age 年龄,gender 性别,dept_id 部门编号
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	gender VARCHAR(5), -- 性别
	dept_id INT   -- 部门编号
    CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id) -- 关联与主表的主键id
);
INSERT INTO employee(NAME,age,gender,dept_id) VALUES
('高圆圆',42,'女',1),
('文章',35,'男',2) ,
('赵又廷',45,'男',1),
('张三丰',50,'男',3),
('王宝强',44,'男',2),
('王聪',25,'男',3);
  1)通过sql语句:修改表语句将外键约束删除
-- alter table 表名 drop foreign key 外键的名称
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
  2)通过修改表的语句添加外键约束
ALTER TABLE employee ADD 
	CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id); -- 关联与主表的主键id
 
 
 7.外键约束基础,可以进行级联操作(级联修改和级联删除)CASCADE
用户在操作主表的时候,跟主表相关的从表的数据也随之更改;
 如果不使用操作:针对修改或者删除,都应该先执行从表,然后主表;
 级联删除和级联修改 on delete cascade  /on update cascade
  1)通过sql语句:外键删除
 
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
 
2) 添加外键约束的同时,添加级联删除和级联修改
ALTER TABLE employee ADD 
	CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id) -- 关联与主表的主键id
	ON DELETE CASCADE 
	ON UPDATE CASCADE ;
  3)直接部门表的4号部门,那么在4号部门的员工也随着 删除了
DELETE FROM dept WHERE id = 4 ;
 

8)数据库的备份和还原:

 1)还原过程

- 方式1:命令行方式
-- mysql8---进入mysql自动控制台或者dos登录mysql
 
 
方式2:图形界面化
-- 在sqlYog或者navicat里面都可以用,直接将库选中, 删除,删除之后,
-- 在创建的新库右键---选择 import---> Execute Sql Scripts:执行sql脚本
-- 选择备份的地址,直接执行sql文件!