MySQL的相关知识

最新推荐文章于 2022-12-13 10:44:37 发布

小白收容所

最新推荐文章于 2022-12-13 10:44:37 发布

阅读量854

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Poissoner/article/details/122984579

版权

一、Day01数据库初始
1.数据(Data)
数据就是数值，也就是我们通过观察、实验或计算得出的结果。数据有很多种，最简单的就是数字。数据也可以是文字、图像、声音等。数据可以用于科学研究、设计、查证、数学等。
2.数据库（DataBase）
数据库:顾名思义，就是用来存储数据的仓库；解决了文件跨平台性能差的缺点
3.数据库的分类
在这里插入图片描述
MySQL:它是关系型数据库，它的特点是开源免费，也比较稳定，体积小，速度快，总体拥有成本低
注：关系型数据库的关系主要体现在它自己的关系模型，反应了表与表之间的关系
4.简单的sql命令操作
查看数据库版本号 : select version();
查看本地所有的数据库：show databases;
查什么写什么 : select ‘xxxx’
在这里插入图片描述
切换数据库： use 数据库名;
查看所有的表： show tables;
退出: Exit;
5.用户的创建和授权

#1、创建用户 语法：create user 'username'@'主机地址' identified by 'password'
create user '张三'@'localhost' identified by '123'
#2、授权 语法：grant 权限 on 数据库名.表名 to 用户@登陆主机 identified by '用户密码'  权限这块写 all就是全部权限
#3、撤销授权 语法跟grant差不多，只需要把关键字'to'换成'from'即可
#4、删除用户，会同步把user和db表都删除对应的数据 语法：drop user 用户名@'%'
drop user 张三@'localhost'

6.数据类型

#5、数据类型 
--varchar(长度) ：注意这个括号必须要加，且必须有数值
--char和varchar的区别
--char定长，varchar变长 例：char(11),不管里面是否有值，都会固定占用11个字符对应的存储空间
--decimal(M,D)  : 这是小数类型，这里的M是有效数字个数，D代表了小数点后有效数字个数

7.SQL的分类

#6、SQL的分类
--DCL数据控制语言(Data Control Language) 代表关键字：grant,revoke
--DDL数据定义语言(Data Definition Language) 代表关键字：create，drop，alter
--DML数据操作语言(Data Manipulation Language) 代表关键字:insert,delete,update
--DQL数据查询语言(Data Query Language) 代表关键字:select
--TCL事物控制语言(Transactional Control Language) 代表关键字:commit,rollback

8.SQL语法

#表的创建
create table 表名(
  列名 数据类型,
	列名 数据类型,
	列名 数据类型
)
#insert 语法
Insert into 表名(列名1,列名2)values (值1,值2);
#update 语法
update 表名 set 列名1=值,列名2=值 where 列名=值
#delete语法
delete from 表名 where 列名=值;
#select 语法
select 列限定 from 表限定 where 行限定

补充：alter

# 更改表名  alter table 表名 rename 新表名;
alter table student_info rename student;

#  更改字段名，类型  alter table 表名 change 列名 新列名 数据类型;
alter table student change student_name name VARCHAR(255);

#添加字段 alter table 表名add 新列名  数据类型; 
alter table student add sex char;

# 删除字段 alter table 表名 drop 列名;
alter table student drop sex;

# 更改字段类型(尽量不要更改) alter table 表名 modify 列名 新数据类型; 
alter table student modify student_age VARCHAR(255);


#修改数据库字符集  alter database 数据库名 character set utf8 
alter database day_02_test_01 CHARACTER set utf8;

#修改表字符集  Alter table 表名 character set utf8 collate utf8_general_ci
alter table student CHARACTER set utf8 collate utf8_general_ci;
show variables like 'character%';

二、day02、数据库应用
1.约束
主键约束的两种方式

# 创建表语句时，添加主键约束 PRIMARY key 
 create table person(
   id int,
	 name VARCHAR(100),
	 income decimal(18,2),
	 PRIMARY key (id,name)
 )
 # 如果只有一列主键，也可以直接写在字段后面
 create table person2(
   id int primary key,
	 name VARCHAR(100),
	 income decimal(18,2)
 )
 
 #第二种 创建表完成之后，通过alter添加主键约束  
# 语法 :  alter table 表名 add primary key(列名,列名...);
 create table person3(
    id int,
		name varchar(100),
		income DECIMAL(18,2)
 );
 alter table person3 add primary key(id);

2.主键自增及设置自增起始值
注意：可以设置为自增的列，必须为主键列

#建表时，添加自增
  create table person4(
	  id int auto_increment,
		name varchar(200),
		PRIMARY key(id)
	)
	#测试语句 
	insert into person4(name)VALUES('测试');
	
	#第二种 创建表之后，添加自增 语法 : alter table 表名modify 主键列名 类型 auto_increment;
	create table person5(
	   id int,
		 name VARCHAR(200),
		 PRIMARY KEY(id)
	);
	alter table person5 MODIFY id int auto_increment;
	#测试语句 INSERT INTO person5 (name)VALUES ('测试');
	insert into person5(name) values('测试');

   	#设置自增的起始值 语法:alter table 表名 auto_increment=值；
	create table person6(
	  id int auto_increment,
		name VARCHAR(200),
		PRIMARY key(id)
	);
	alter table person6 auto_increment=1000;
	insert into person6(name)VALUES('测试');

3.外键对应java代码来说，外键就是类的关联关系
设置外键的两种方式

#创建表时添加外键约束
	create table teacher(
	 id int,
	 name VARCHAR(20),
	 primary key(id)
	);
	create table student(
	  id int,
		name varchar(20),
		teacher_id int,
		PRIMARY key(id),
		foreign key(teacher_id) references teacher(id)
	)
	#注意，引用student中添加外键列，指向teacher表，所以必须先创建teacher表才行
	#测试语句 
	#添加一个讲师 
	insert into teacher(id,name) VALUES (1,'张老师');
	#添加一个学生小明，学生通过teacher_id可以指向张老师
	insert into student(id,name,teacher_id)VALUES(1,'小明',1);
	#添加一个学生小红，teacher_id没有设置值
	insert into student(id,name)VALUES (2,'小红');
	#添加一个学生小黑,teacher_id 指向一个不存在的讲师，报错
	insert into student(id,name,teacher_id)VALUES(3,'小黑',2);
	#创建完表之后，添加外键约束 语法 : alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名);
	create table student1(
	   id int,
		 name VARCHAR(20),
		 teacher_id int,
		 primary key(id)
	);
	create table teacher1(
	 id int,
	 name VARCHAR(20),
	 primary key(id)
	);
	alter table student1 add foreign key(teacher_id) references teacher1(id);
	#测试语句 
	#添加一个讲师 
	insert into teacher1(id,name) VALUES (1,'张老师');
	#添加一个学生小明，学生通过teacher_id可以指向张老师
	insert into student1(id,name,teacher_id)VALUES(1,'小明',1);
	#添加一个学生小红，teacher_id没有设置值
	insert into student1(id,name)VALUES (2,'小红');
	#添加一个学生小黑,teacher_id 指向一个不存在的讲师，报错
	insert into student1(id,name,teacher_id)VALUES(3,'小黑',2);

4.唯一约束 unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性唯一约束不允许出现重复的值，但是可以为多个null
两种设置unique的方法

#唯一约束 unique 
 #第一种，创建表时，添加unique约束
  create table temp(
	  id int,
		name varchar(20),
		unique(id)
	);
	#或
	create table temp1(
	  id int unique,
		name varchar(20)
	);
	#添加一条没有id的数据
	insert into temp1(name)values('张三');
	#再添加一条没有id的数据,可以添加（唯一约束，又不是不为空约束）
  insert into temp1(name)values('李四') ;
	#添加一条id为1的数据
	insert into temp1(id,name)values(1,'王五');
	#再添加一条id为1的数据，会报错，因为已经有了id为1的数据了，不可重复
	insert into temp1(id,name)values(1,'赵六');
	
	#第二种，创建表之后，添加unique约束 语句：alter table temp2 add unique(id);
	create table temp2(
	  id int,
		name varchar(20)
	)
	alter table temp2 add unique(id);
	#添加一条没有id的数据
	insert into temp2(name)values('张三');
	#再添加一条没有id的数据,可以添加（唯一约束，又不是不为空约束）
  insert into temp2(name)values('李四') ;
	#添加一条id为1的数据
	insert into temp2(id,name)values(1,'王五');
	#再添加一条id为1的数据，会报错，因为已经有了id为1的数据了，不可重复
	insert into temp2(id,name)values(1,'赵六');

5.非空约束 not null 与默认值 default
所以类型的值都可以是null，包括int、double等数据类型，设置为not null的字段，必须填入数据，经常和default一起使用，当不填写数据的时候，把默认值设置成指定的值
两种方式

#非空约束 not null 与默认值 default
	#第一种，创建表时，添加约束
	create table temp4(
	 id int not null,
	 name varchar (30) default 'abc',
	 sex varchar(10) not null default'男'
	)
	#测试语句
	insert into temp4(id)values(1);
	insert into temp4(id,name,sex)values(2,'张三','女');
	#id设置了不能为空，并且没有默认值，所以报错
	insert into temp4(name,sex)values('李四','女');
	
	#第二种,创建表之后，添加约束  
	#语法:alter table 表名 modify 列名 数据类型 not null  default 默认值;
	create table temp5(
	  id int,
		name varchar(30),
		sex varchar(10)
	);
	alter table temp5 modify id int not null;
	alter table temp5 modify name varchar(30) default 'abc';
	alter table temp5 modify sex varchar(10) not null default'男';
	#测试语句
	insert into temp5(id,name,sex)values(2,'张三','女');
	#没有添加id的值，而id又设置不能为空，并且也没用默认值，所以报错
	insert into temp3(name,sex)values('李四','女');

6.条件判断及关系表达式
and:必须两个条件都符合
or：满足其中一个条件即可
注意：and优先级比or更高
between … and… 语法：select 列限定 from 表限定 where 列名 between 值1 and 值2；
in 在指定数据中 : select 列限定 from 表限定 where 列名 in(值1，值2…)

, >= , < , <= ,<>,=
大于
< : 小于
= : 大于等于
<= : 小于等于
= : 相等
<> : 不等于
判断是否为空，应该使用 is null，判断不为空应该使用 is not null

#and  语法 : select 列限定 from 表限定 where A表达式 and B表达式;
 #例子 查询学生表中,name是张三且成绩大于90分
 select * from student01 where name='张三' and score>90;
#or 语法: select 列限定 from 表限定 where A表达式 or B表达式;
	#例子 查询学生表中,name是张三 或 成绩大于90分
 select * from student01 where name='张三' or score>90;
 #查询学生表中,成绩大于90分的
 select * from student01 where score>90;
 #查询学生中,成绩不为空的学生
 select * from student01 where score is not null;

模糊查询 like 语法：select 列限定 from 表限定 where 列名 like ‘值’；
其中 % 匹配任意个数的任意字符， _匹配单个任意字符

order by 排序语法：select 列限定 from 表限定 order by 列名 asc/desc;
asc:升序 desc：降序

limit：限制条数，通常和order by一起使用，因为我们使用排序之后，再去获取前几条数据比较有价值
语法： select 列限定 from 表限定 limit 条数
select 列限定 from 表限定 limit 开始值（不包含），条数；

7.单表查询（组函数）
MySQL中有一类特殊的函数，用于统计，或者分组统计，分组关键字用 group by
常用组函数有：
count() : 总条数
max(字段名) : 最大值
min(字段名) : 最小值
avg(字段名) : 平均值
sum(字段名) : 总和
语法：
select count(),max(字段名),min(字段名)… from 表名 group by 字段名;
如 : 查看学生表共有多少学生
select count() from student;
如 : 查看学生表中分数大于90分的有多少学生
select count() from student where score > 90;

group by

#group by 
# 查询每个老师分别带了多少学生(显示老师id即可)
  select teacher_id,count(*) from student02 GROUP BY teacher_id;
#查询每个老师带的学生中的最高分数
select name,max(score) from student02 GROUP BY teacher_id;
#查询每个老师所带学生的总成绩与平均分
select teacher_id,sum(score),AVG(score) from student02 GROUP BY teacher_id;

having

#HAVING
# 查询老师所带学生的平均分数大于75分的老师ID和平均分数？
select AVG(score),teacher_id from student02  group by teacher_id HAVING AVG(score)>75;

8.子查询
子查询又叫嵌套查询，当我们查询一个表没有办法实现功能的时候，就需要使用子查询

场景一：select后面
语法： select 字段名，（查询语句） from 表名；
如：查询所有学生的信息并显示老师的名字
select * , (select name from teacher where id=teacher_id) as teacher_name from student
这个 as teacher_name 就是起别名

场景二:from后面
还是学生表，我们要将成绩分级，并且显示汉字的分级与字母的分级。这里可以使用子查询
select *,
case rank
when ‘A’ then ‘优’
when ‘B’ then ‘良’
when ‘C’ then ‘差’
end as rank_ch
from(
select *,
case
when score<60 then ‘C’
when score>=60 and score<80 then ‘B’
when score>=80 then’A’
end as rank
from student
)as a;

场景三：where后面
如 : 在不知道teacher_id 和老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in (
select id from teacher where name=‘张老师’
);
注意 :
当位于WHERE后面时，要注意
1.多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2.如果返回结果为多行一列的话要用 in , 一列是必须的,必须是一列
3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

9.Union与union all
合并查询：合并查询结果
Union 会去除重复项
Union all 不会去除重复项

如 : 查询出学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)
// 用 or 实现
select * from student where teacher_id=1 or score > 60;
// 用 union实现
select * from student where teacher_id=1
union
select * from student where score > 60;

10.常用函数

#常用函数
#显示当前mysql软件的版本
select version();
#显示当前所处数据库是哪个
select database();
#返回字符个数
select char_length('中国');
#返回字符所占字节数，mysql中，一个编码的汉子占三个字节
select length('中国');
#返回'abcd'，字符串拼接函数
select concat('a','b','c','d');
#符号串拼接函数，第一个是拼接间隔符
select concat_ws('a','b','c','d');
#把参数中所有小写转大写
select upper('abcd');
#把参数中所有大写转小写
select lower('ABCD');
#返回 系统信 第二个参数代表从1开始的，第三个参数代表截取字符个数
select substring('系统信息类',1,3);

三、day03多表查询
1.行转列

#1.1 行转列
                           #示例代码
create table test_1(
	id int,
	name varchar(22),
	course varchar(22),
	score decimal(18,2)
);
insert into test_1 (id,name,course,score)values(1,'小王','java',99);
insert into test_1 (id,name,course,score)values(2,'小张','java',89.2);
insert into test_1 (id,name,course,score)values(3,'小李','java',88);
insert into test_1 (id,name,course,score)values(4,'小王','MySQL',92.2);
insert into test_1 (id,name,course,score)values(5,'小张','MySQL',42.2);
insert into test_1 (id,name,course,score)values(6,'小李','MySQL',59.2);
                           #行转列代码_01
select name,
max(case course
    when 'java' then score
		end 
)
as java,
max(case course
    when 'MySQL' then score
		end
)
as MySQL  
from test_1 
GROUP BY name;									
                           #行转列代码_02  两种不同的效果
select name,
group_concat(course,'=', score order by course asc separator'|' ) # separator 修改中间符 
as '各科成绩' 
from test_1
group by name;

1.1.2 行转列

#1.1 行转列
                           #示例代码
create table test_1(
	id int,
	name varchar(22),
	course varchar(22),
	score decimal(18,2)
);
insert into test_1 (id,name,course,score)values(1,'小王','java',99);
insert into test_1 (id,name,course,score)values(2,'小张','java',89.2);
insert into test_1 (id,name,course,score)values(3,'小李','java',88);
insert into test_1 (id,name,course,score)values(4,'小王','MySQL',92.2);
insert into test_1 (id,name,course,score)values(5,'小张','MySQL',42.2);
insert into test_1 (id,name,course,score)values(6,'小李','MySQL',59.2);
                           #行转列代码
select name,
max(case course
    when 'java' then score
		end 
)
as java,
max(case course
    when 'MySQL' then score
		end
)
as MySQL  
from test_1 
GROUP BY name;

2 多表查询

   #多表查询
					#示例数据
create table teacher(
    id int ,
    name varchar(20),
    primary key (id)
);
create table student (
    id int ,
    name varchar(20),
    teacher_id int ,
    primary key (id),
    foreign key (teacher_id) references teacher(id)
);

insert into  teacher (id,name) values(1,'张老师');
insert into  teacher (id,name) values(2,'王老师');
insert into  student (id,name,teacher_id) values(1,'小明',1);
insert into  student (id,name) values(2,'小红');
insert into  student (id,name,teacher_id) values(3,'小黑',2);				

        #语句
select * from teacher tea,student stu where stu.teacher_id=tea.id;				

insert into teacher(id,name)values(3,'孙老师');

select * from teacher tea inner join student stu on tea.id=stu.teacher_id;

select * from teacher t left join student s on s.teacher_id=t.id 

select * from teacher t  left join student s on s.teacher_id=t.id;

insert into  teacher (id,name) values(1,'张老师');
insert into  teacher (id,name) values(2,'王老师');
insert into  student (id,name,teacher_id) values(1,'小明',1);
insert into  student (id,name) values(2,'小红');
insert into  student (id,name,teacher_id) values(3,'小黑',2);				

        #语句
select * from teacher tea,student stu where stu.teacher_id=tea.id;				

insert into teacher(id,name)values(3,'孙老师');

select * from teacher tea inner join student stu on tea.id=stu.teacher_id;

select * from teacher t left join student s on s.teacher_id=t.id 

select * from teacher t  left join student s on s.teacher_id=t.id;

能用inner join的不用left join，能用left join的不用right join
inner join：
使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行
同时左表中有的,右表中没有的数据,都不会生成
右表中有的,左表中没有的也一样不会生成,所以左表和右表就算换了位置,数据行数不会变多
可能会丢失数据
left join：
以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替
生成视图的时候,也是先生成左表的数据
right join：
跟left join功能相似，
以右表为基准,右表中数据都有,左表中不符合条件的就没有,就在指定列上用null代替
但是视图生成的时候,还是会先生成左表数据
以上可以看出，student right join teacher 显示的内容是与teacher left join student相同的。而teacher right join student 显示的内容又与student left join student相同。
所以，我们工作中，right join基本不用。用的最多的是inner join 与left join。

四、day04
1.JDBC（Java DataBase Connectivity）
概念：是让java链接数据库的API
2.JDBC使用步骤
第0步: 导包
第1步：注册驱动 (仅仅做一次)
　　第2步：建立连接(Connection)
　　第3步：创建运行SQL的语句对象(Statement)
　　第4步：运行语句
　　第5步：处理运行结果(ResultSet)
第6步：释放资源
其中如果是添加,删除,更新操作,可以没有第5步,查询肯定会有第五步

第0步导包
创建java项目
创建lib文件夹
把mysql-connector-java-5.1.38-bin.jar复制到lib中
在这里插入图片描述

package Jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * jdbc初始
 *     //第0步: 导包
 *     //第1步：注册驱动 (仅仅做一次)
 *     //第2步：建立连接(Connection)
 *     //第3步：创建运行SQL的语句对象(Statement)
 *     //第4步：运行语句
 *     //第5步：处理运行结果(ResultSet)
 *     //第6步：释放资源
 */
public class Jdbc_01 {
    public static void main(String[]args) throws Exception{
        //第1步：注册驱动 (仅仅做一次)
//        Class.forName("com.mysql.jdbc.Driver");
        Class.forName("com.mysql.jdbc.Driver");
        //第2步：建立连接(Connection)
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/day03_work","root","admin");
        //第3步：创建运行SQL的语句对象(Statement)
        String sql="select * from student ";
        Statement statement=conn.createStatement();
        //第4步：运行语句
        ResultSet resultSet=statement.executeQuery(sql);
        //第5步：处理运行结果(ResultSet)
        while(resultSet.next()){
            System.out.println("索引打印"+resultSet.getString(2));
            System.out.println("标签打印"+resultSet.getString("sname"));;
        }
            //第6步：释放资源
            resultSet.close();
            statement.close();
            conn.close();
    }
}

优化后的代码

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Jdbc_06 {

    public static void main(String[] args) {
        ResultSet resultSet=null;
        Statement statement=null;
        Connection conn=null;
        try {
            conn=JdbcUtil.getConnection();
            //第3步：创建运行SQL的语句对象(Statement)
            String sql="select * from student";
            statement=conn.createStatement();
            //第4步：运行语句,得到结果集
            resultSet = statement.executeQuery(sql);
            //第5步：处理运行结果(ResultSet)
            while(resultSet.next()){
                System.out.println("索引打印"+resultSet.getString(2));
                System.out.println("标签打印"+resultSet.getString("name"));;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //优化的关闭资源
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeResultSet(statement);
            JdbcUtil.closeResultSet(conn);
        }
    }
}

工具类

package main;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {

    public static Connection getConnection() throws Exception {
        //第1步：注册驱动 (仅仅做一次)
        Class.forName("com.mysql.jdbc.Driver");
        //第2步：建立连接(Connection)
        Properties properties=new Properties();
        properties.load(JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        return DriverManager.getConnection(url,username,password);
    }
    
    /**
    　* @Author Mr.Wang
    　* @Date 22-2-17
    　* @Description:关闭资源
    　*/
    public static void closeResultSet(AutoCloseable autoCloseable){
        try {
            if(autoCloseable!=null){
                autoCloseable.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

小白收容所

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的相关知识

一、Day01数据库初始1.数据(Data)数据就是数值，也就是我们通过观察、实验或计算得出的结果。数据有很多种，最简单的就是数字。数据也可以是文字、图像、声音等。数据可以用于科学研究、设计、查证、数学等。2.数据库（DataBase）数据库:顾名思义，就是用来存储数据的仓库；解决了文件跨平台性能差的缺点3.数据库的分类MySQL:它是关系型数据库，它的特点是开源免费，也比较稳定，体积小，速度快，总体拥有成本低注：关系型数据库的关系主要体现在它自己的关系模型，反应了表与表之间的关系4.简单
复制链接

扫一扫