MySQL数据库

小竹节

于 2019-08-13 12:13:48 发布

阅读量224

点赞数

分类专栏：学习笔记文章标签： MySQL数据库

本文链接：https://blog.csdn.net/qq_40913582/article/details/99419528

版权

学习笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

MySQL数据库

数据库

数据库就是一个文件系统，访问数据的时候需要通过标准的SQL语言来完成

关系型的数据库

保存实体于实体之间的关系（用户，商品，订单）

常见的数据库

Oracle	公司Oracle(甲骨文)数据产品，收费的大型数据库
MySQL	开源的，被Oracle收购了。小型数据库。5.x版本免费，6.x收费。
SQLServer	微软的，收费的中型数据库
DB2	IBM公司收费的大型数据库
SyBASE	PowerDigener软件

访问

cmd>输入命令 mysql -u root -p 回车
输入密码 回车

服务器，表，对象之间的关系

mysql是数据库服务器，一个数据库的服务器有多个数据库，一个数据库中有多个表，每个表中有多个字段，每个字段和java中类的属性是对应的

client-->mysql--->db--------------->表
				|				|
				---db--->表  	---表

SQL语句

sql:结构化查询语言

非过程性语言

	过程性语言：下一条语言需要依赖上面的语句
	非过程性语言：写一条语句，就会执行一个结果
	
Oracle开发了PL/SQL,只能在oracle使用
	
SQL Server,Sybase的T-SQL

SQL的分类

DDL(数据库定义语言)
	船舰数据库，创建表

DML(数据操纵语言)
	插入（insert），修改数据，删除数据

DCL(数据控制语言)
	if else

DQL(数据的查询语言)
	查询数据 select

数据库（CURD----增删改查）

创建数据库
	语法
		create database 数据库名称;		创建一个数据库
		create database 数据库名称 character set 编码 collate 校对规则;

	校对规则：和编码成对出现
	

	练习：
		创建一个名称为mydb1的数据库
			create database mydb1;
		创建一个使用utf8字符集的mydb2数据库
			create database mydb2 character set 'utf8';
		创建一个使用utf8字符集带校验规则的mydb3数据库
			create database mydb3 character set 'utf8' collate 'utf8_bin';
	
	查看数据库 ：show databases;
	查询数据库的定义 show create database 数据库;
		show create database mydb2;

	删除数据库 drop database 数据库名称;
		
	练习:
		查看当前服务器所有的数据库
			show databases;
		查看前面创建的mydb3的定义信息
			show create database mydb3;
		删除mydb1数据库
			drop database mydb1;

	修改数据库 
		语法：alter database 数据库名称 character set 编码 collate 校对规则;

		练习：查看服务器中的数据库，并把其中一个库的字符集修改为gbk
			alter database mydb3 character set 'gbk';

	其他的操作
		切换数据库 use db_name;
		查看当前使用的数据库 select database();

表（CURD----增删改查）

语法：
	create table 表名(
		字段 类型(长度) 约束,
		字段 类型(长度) 约束,
		字段 类型(长度) 约束	
	);

	表名小括号，后面有番号。
	每行字段有逗号，最后一行没有
	数据的类型后面有长度，字符串必须加，其他的有默认长度int是(11)

	public class User{
		int id;
		String name;
		String pass;
		String eamil;
	}

	数据的类型：
		字符串
			VARCHER,CHAR
				varchar和char的区别
					varchar(经常使用),长度可变。 name varchar(8) 存入数据hello,如果存入helloworld报错
					char  长度不可变	name char(8)  存入数据hello,如果不够用空格补全
					效率高：char
		大数据类型（一般不用）
			BLOB,TEXT
				BLOB:二进制文件
				TEXT:字符

		数值型
			TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE

		逻辑类型对应boolean
			BIT
	
		日期型
			DATE,TIME,DATETIME,TIMESTAMP
				date  只包含日期
				time  直播啊汗时分秒
				datetime和timestamp包含日期和时分秒区别：
					datetime需要手动录入时间
					timestamp不传入数据，默认选择当前的系统时间


	练习，创建表
		create table employee(
			id int,
			name varchar(20),
			gender varchar(10),
			birthday date,
			entry_date date,
			job varchar(100),
			salary double,
			resume text
		);


约束(单表)
	主键约束
		标识标记该条记录。通过primary key声明主键。（默认是唯一，非空）
		auto_increment	数据库维护主键，自动增长
	唯一约束
		值是唯一的。使用unique声明
	非空约束
		值不能为空	not null

创建表employee2,加上约束
	create table employee2(
		id int primary key auto_increment,
		name varchar(20) unique not null,
		gender varchar(10) not null,
		birthday date not null,
		entry_date date not null,
		job varchar(100) not null,
		salary double not null,
		resume text not null
	);

使用desc表名，查看信息
show tables;查看当前库所有表名
show create table 表名;查看建表语句字符集

删除表
	drop table employee2;

修改表
	alter table 表名 add 字段 类型(长度) 约束;	--添加字段
	alter table 表名 drop 字段;	--删除字段
	alter table 表名 modify 字段 类型(长度) 约束;	--修改类型或者约束
	alter table 表名 change 旧字段 新字段 类型(长度) 约束;	--修改字段的名称

	rename table 表名 to 新表名	--修改表名
	alter table 表名 character set utf8;	--修改字符集

练习
	在上面的员工表的基本上增加一个image列
		alter table employee add image varchar(20);
	修改job列，使其长度为60
		alter table employee modify job varchar(60);
	删除gender列
		alter table employee drop gender;
	表名改为user
		rename table employee to user;
	修改字符集为utf8
		alter table user character set utf8;
	列名name修改给username
		alter table user change name username varchar(20);

数据（CURD----增删改查）

insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...);有几列就插入多少的值
insert into 表名 values (值1,值2,值3...);	插入所有的值

注意:
	数据与字段的类型相同
	字段长度需要控制
	字符串或者日期类型需要使用''

向user表中插入数据
	insert into user values (1,'xiaosi','2000-10-10','2019-08-21','hr',20000,'aaaa','fdfd');
	insert into user values (2,'美美','2000-10-10','2019-08-21','hr',20000,'aaaa','fdfd');

解决插入数据中文乱码问题
	[client]

	port=3306
	
	[mysql]
	
	default-character-set=GBK

修改完需要重启服务


修改语句:
	语法:
		update 表名 set 字段=值,字段=值,字段=值...[where]
		如果没有where条件，默认更新所有的记录。
		有where提交，选择某一条记录。

	将所有员工薪水修改为5000元
		update user set salary=5000;
	将姓名为'xiaosi'的员工薪水修改为3000，job改为kpl
		update user set salary=3000,job='kpl' where username='xiaosi';
	将美美的薪水在原有基础上增加1000
		update user set salary=salary+1000 where username='美美';

删除数据 delete
	delete form 表名 [where];	删除数据
	truncate 表名;	删除所有的数据

	truncate和delete的区别:
		truncate删除数据，先删除整个表，再建立一个新的空表。
		delete删除数据，一条一条删除的。

		事务（支持insert update delete）

	删除表中名称为'美美'的记录
		delete from user where username='美美';

	删除表中所有的记录
		delete from user;
		truncate user;

查询语句
	语法:	select * from 表名;		查询所有
			select 字段1,字段2,字段3 from 表名 查询某些字段
			select DISTINCT 字段名 from 表名	去除重复的数据

	创建一个stu表
		create table stu(
			id int primary key auto_increment,
			name varchar(20),
			math int,
			english int,
			chinese int
		);
		
		insert into stu values (null,'班长',10,20,30);
		insert into stu values (null,'张三',20,30,40);
		insert into stu values (null,'李四',30,40,50);
		insert into stu values (null,'王五',40,50,60);


	练习：
	查询表中所有的信息
	select *from stu;
	查询表中所有学生的姓名和对应的英语成绩
	select name,english from stu;
	过滤表中给重复的数据
	insert into stu values (null,'zhui',40,50,60);
	select distinct english from stu;

	查询的列可以运算
	
	可以使用别名: 使用as 别名     并且as可以省略
		再所有学生分数加上10分特长分
			select name,name,math+10,chinese+10 from stu;
		统计每个学生的总分
			select name,math+english+chinese from stu;
		使用别名表示总分呢
			select name,(math+english+chinese) as sum from stu;
		
	使用where条件过滤
		查询姓名为班长的学生成绩
			select * from stu where name='班长';
		查询英语成绩大于90分的同学
			select name,english from stu where english >90;
		查询总分大于70分的同学
			select name,english+math+chinese from stu where (english+math+chinese)>70;

	一些符号
		> < <= >= = <>(不等于)
		in(范围内取内容)
		like	--模糊查询  写法:like '张_或者%';  _只能占一个 %可以占多个   
			like %张%代表只要有张就行
		is null --判断是否为null
		and	--并且
		or	--或者
		not	--不成立

		查询41-51英语的同学
			select * from stu where english >41 and english<51;
			select * from stu where english between 41 and 51;
		查询数学成绩为10，20，30的同学
			select * from stu where math in (10,20,30);
		查询所有姓李的学生成绩
			select * from stu where name like '李%';


排序   使用order by 升序(asc)/降序（desc）
	出现select的语句末尾.
	
	练习
		对数学的成绩排序后输出
			select name,math from stu order by math desc;
		对学生成绩按照英语进行降序排列，英语相同的同学按照数学降序
			select * from stu order by english,math desc;
		对姓美的学生排序输出
			select * from stu where name like '美%' order by english desc;


聚集函数
	count 获取数量
		练习:
			统计一个班有多少学生
				select count(*) from stu;
			统计数学成绩大于30的学生有多少个
				select count(*) from stu where math>30;
	sum		求和(忽略null的值)  可以同ifnull(xxx,0)
		统计一个班级数学总成绩
			select sum(math) from stu;
		统计一个班级语文，英语，数学的成绩总和
			select sum(ifnull(math,0)+english+chinese) from stu;
			select sum(math)+sum(english)+sum(chinese) form stu;
			
	avg		平均数
		统计一个班语文平均分
			select sum(chinese) / count(*) from stu;
			select avg(chinese) from stu;
	max		最大值
	min		最小值

group by 分组(和聚集函数一起使用)  条件过滤需要用having,不能使用where
	create table orders(
		id int,
		product varchar(20),
		price float
	);
	insert into orders(id,product,price) values(1,'电视',900);
	insert into orders(id,product,price) values(2,'洗衣机',1900);
	insert into orders(id,product,price) values(3,'洗衣粉',90);
	insert into orders(id,product,price) values(4,'橘子',9);
	insert into orders(id,product,price) values(5,'洗衣粉',90);
	insert into orders(id,product,price) values(6,'电视',900);

	对订单表中的商品归类后，显示每一类商品的总价和数量
		select product,count(*),sum(price) from orders group by product;
	查询购买了几类商品，并且每类总价大于100的商品
		select product,sum(price) from orders group by product having sum(price)>100;

	顺序:S-F-W-G-H-O

多表操作

外键约束

有一个部门的表，还有一个员工的表

create database day16;
use day16;
create table dept(
	did int primary key auto_increment,
	dname varchar(30)
);

create table emp(
	eid int primary key auto_increment,
	ename varchar(20),
	salary double,
	dno int
);

insert into dept values(null,'研发部');
insert into dept values(null,'销售部');
insert into dept values(null,'人事部');

insert into emp values(null,'班长',1000,1);
insert into emp values(null,'美美',1000,2);
insert into emp values(null,'李四',1000,3);
insert into emp values(null,'王五',1000,2);
insert into emp values(null,'赵六',1000,1);


把研发部门删除
	研发部下面有人员？操作不合理
	引入外键约束
		作用：保证数据的完整性

添加外键
	alter table emp add foreign key 当前表名(dno) references 关联的表(did);
	删除关联表的字段会报错

数据库的设计

一对一（基本上可以方一个表中）	一个人和身份证号
	建表原则：
		主键对应：
			
		唯一外键对应：
一对多	一个学生对应一个班级，一个班级对应多个学生
	建表原则：再多方的表中添加一个字段，类型必须是相同的，把该字段作为外键，指向一方的主键
多对多	学生和课程的关系
	建表原则：
		拆开两个一对多的关系，中间创建一个中间表，至少有两个字段。作为外键指向两个多对多关系

假如有一个购物的网站

包含哪些实体   用户 订单	商品	分类
	用户(名字，电话，地址)
	订单（下单时间，送货地址，用户名字,类别）
	商品(编号，价格)
	分类（分类名）

 出现多对多关系,加中间表
	中间表（订单号，商品号）

多表的查询

笛卡尔积的概念：
表A					表B
aid	aname			bid	bid
a1	aa1				b1	bb1
a2	aa2				b2	bb2
					b3	bb3

select * from 表A,表B;	结果就是笛卡尔积

内连接(用的比较多)
	普通的内连接
		关键字innr join...on
		select * from dept inner join emp on dept.did=emp.dno;
	隐式的内连接
		select * from dept,emp where dept.did=emp.dno;

外连接
	左外连接(看左表,把左表所有的数据全部查出来,有null就能体现)
		前提条件：需要外键
		语法：	使用关键字 left [outer] join...on
		select * from dept left join emp on dept.did=emp.dno;
	右外连接(看右表,把右表所有的数据全部查出来)
		语法：	使用关键字 right [outer] join...on
		select * from dept right join emp on dept.did=emp.dno;
	![在这里插入图片描述](https://img-blog.csdnimg.cn/2019081312125342.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTEzNTgy,size_16,color_FFFFFF,t_70)

子查询

查询的内容需要另一个查询的结果
select * from emp where ename>(select * from emp where 条件);

any	任意

all	全部

>any   大于结果的最小值

>all   大于结果的最大值

练习：
	查看所有人所属的部门和员工的名称
		select dept.dname,emp.ename from dept inner join emp on dept.did=emp.dno; 
	统计每个部门的人数（按照部门名称统计，分组group by）
		select dept.dname,count(*) from dept,emp where dept.did=emp.dno group by dept.dname; 
	统计部门的平均工资（按部门名称统计，分组）
		select d.dname,avg(e.salary) from dept d,emp e where d.did=e.dno group by d.dname;
	统计部门的平均工资大于公司平均工资的部门（子查询）
		公司的平均工资
			select avg(e.salary) from emp e; 
		部门的平均工资
			select d.dname,avg(e.salary) as sa from dept d,emp e where d.did=e.dno group by d.dname;

		select d.dname,avg(e.salary) as sa from dept d,emp e where d.did=e.dno group by d.dname having sa>(select avg(e.salary) from emp e);