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文件!
推荐阅读