mysql

最新推荐文章于 2025-05-24 14:25:39 发布

leader321

最新推荐文章于 2025-05-24 14:25:39 发布

阅读量152

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/leader321/article/details/109518713

版权

mysql 专栏收录该内容

1 篇文章

订阅专栏

数据库

关系型数据库 MySql，Oracle，SqlServer
非关系型数据库 Redis，Kafka,RabbitMQ

mysql基本操作

1.Mysql 基本命令
1.登陆mysql
mysql -u 【username】-p
password
2.如何查看数据库
show databases;
3.切换数据库
use 库名;
4.查看所有表
show 库名;
5.查看表中的字段
desc 表名;
6.查看表里的数据
select * from 表名;
7.退出mysql
exit;

数据库结构

对象

1.数据库服务器：
1.数据库
2.数据表

2.数据表：
1.表结构（字段）
2.表数据（记录）

3.视图
视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询	定义。视图不直接存储数据，不知真正的表。
同上

4.列（字段）column（field）

表

1.索引

#索引本质：是一种辅助检索数据的数据结构
#劣势
	占表空间
	增删数据的开销大
	索引使用不当会失效
		select ... from student where p=v / p=v and c=v2/p=v and c=v2 and d=v3; #左置前缀
		select ...  where ix_field like '...'; #模糊查询
		select ...  where ix_field%5=0 #索引列参与计算
		select ... where ix_field not in (....) #排除查询
		....
#适合建索引的字段
#经常作为条件查询的字段
#数据量不能太小

#分类
单列索引
	聚簇索引	：通常为主键字段，只有一个，决定表的物理存储
	唯一索引	：
	全文索引
	普通索引
	创建：	create index ix_stuid on score(student_id);
			show index from score;
组合索引	
	create [unque/fulltext/...]index group ix pcd on student(province,city,district)
		create index ix_stusub on score(course_id,student_id);
		show index from score;
	mysql> explain select * from score;
	+----+-------------+-------+------+---------------+------+---------+------+------+-------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------+
	|  1 |SIMPLE(简单查询)score| ALL  | NULL          | NULL | NULL    | NULL |   60 | NULL  |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------+
	1 row in set
#锁
#分类：共享锁，排他锁
select 默认不加锁 lock in share mode/for update
insert/update/delete#默认添加排它锁
select * from score set

2.事务
(ACID 原子性，一致性，隔离性，持久性)

		try{
			...
			....error
			...
			...
		}catch(Exception e){
				e.printStacktract();
			}
			set auto_commit=0;
			insert/delect/update/select
			...
			if(error){
				rollback;//回滚
			}else{
				commit;
			}

3.函数

1. 自定义函数

--变量
	--局部变量：作用域方法内
	declare val data_type default df_value;
	--全局变量：跨函数访问
	set @var=1;
--<> and or not
--逻辑结构
	--分支
	if exper1 then
		...
	else exper2 then
		...
	else
		...
	else if ;
	--循环
	while exper do
		...
	end while;
--函数
	--调换结束符号
	delimitor //
	--自定义函数
	create function FUNC_NAME(param...) return RETURN_TYPE
	begin
		...
	end //
	
	delimitor;
	substring_index(varchar,int,varchar)

--去除分隔符字符长度	
create function size_index(sep char(1),src varchar(100)) returns int
	begin 
		declare t int default 0;
		declare pos int default 1;
		while 0<>locate(sep,src,pos) do
			set t=t+1;
			set pos=locate(sep,src,pos)+1;
		end while;
		return t+1;
	end //
--分隔符分割字符串	
create function split(src varchar(100),sep varchar(20)) returns varchar(100)
	begin 
		declare t varchar(100) default '';
		declare pos int default 1;
		declare L int default char_length(sep);
		while 0<>locate(sep,src,pos) do
			if pos<>1 then set t=concat(t,',');end if;
			set t=concat(t,substr(src,pos,locate
			(sep,src,pos)-pos));
			set pos=locate(sep,src,pos)+L;
		end while;
		set t=concat(t,',',substr(src,pos));
		return t;
	end //

4.存储函数


#存储过程
	#创建存储过程
	create procedure PRO_NAME(in/out/inout VAR_NAME DATA_TYPE)
	begin
		....
	end //
	#查看存储过程
	show procedure status like ...

	#删除存储过程	
	drop procedure PRO_NAME;

		
	#成绩表分页查询
	create procedure  pro_selscore(in pageNo int,in pageSize int,out total int)
	begin
		declare _begin int default (pageNo-1)*pageSize;
		select ceil(count(1)/pageSize) into total from score;
		select * from score limit _begin, pageSize;
	end // 
	#银行转账
	delimitor //
	create table acmount(
	user bigint  not null,
	balance decimal(10,2) not null
);	

	insert into TABLE_NAME(FIELD_NAME,...) values(VALUE1,...);
insert into acmount (user,balance) values(1001,2000),(1002,500);
	

create procedure pro_carryover (
	in fromUser int ,
	in toUser int,
	in amount decimal (10,2),
	out rst int
)
begin
	declare accnum int default 0;
	declare _balance decimal (10,2) default 0;
	declare continue handler for sqlexception set rst=-1;
	set rst=0 ;
	select count(userid) into accnum from account where userid in(fromUser,toUser);
	if accnum<2 then
		set rst=-3 ;
	else
		select balance into _balance from account where userid=fromUser;
		if _balance<amount then
			set rst=-2;
		else
			start transaction ;
			update account set balance=balance-amount where userid=fromUser;
			update account set balance=balance+amount where userid=toUser;
			if rst<>-l then
				set rst=1;
				commit;
			else
				set rst=0;
				rollback;
			end if;
		end if;
	end if ;
end //

2. 系统函数

字符串函数

char_length(str)字符串长度
length(str)字符串字节长度，汉字三字节
concat(s1,s2,...)字符串拼接
concat_ws(x,s1,s2,...)以x为连字符拼接字符串
insert(s1,x,len,s2)把s1中第x位置开始长度len的字符替换成s2
lower(str)字符转小写
upper(str)字符转大写
left(str,len)字符串左起长度len字符串输出
right(str,len)字符串右起长度len字符串输出
mid(str,p,len)字符串左起p位置长度len字符串输出
lpad(str,n,x)左起以'x'填充字符串str满足字符串长度为n
rpad()右起以'x'填充字符串str满足字符串长度为n
ltrim(s)，rtrim(s)删除左/右空格
trim(s)删除左右空格
trim(s1 from s)字符串s删除左右子字符串s1,没有删除左右空格
repeat(str,n)让字符串重复n次
space(n)返回n个空格字符串
replace(s,s1,s2)s中含有子字符串s1，用s2替换s1
mid(s,n,len)返回字符串s第n位长度为len的子字符串
instr(s1,s)返回s中子字符串s1 的位置
reverse(s)反转s
elt(n,s1,s2,s3,s4)返回第n个字符串
field(dest,s1,s2,s3)根据参数一指定的字符串参数，从之后列表查找参数1的位置，从1 开始，不存在返回0
find_in_set(s,set)集合set为'xx,xx1,xx2,...'返回set集合中与s相同的字符串的位置，不存在返回0
					make_set(b,s1,s2,s3,s4...sN)参数1转化为二进制,取低n位,左位低,右位高，为1输出

2.数学函数

abs(...)
floor(...)去尾
ceil(...)去尾进一
truncate(...,n),保留小数后n位，截断时不进行四舍五入
round(...,n)保留小数后n位，截断时进行四舍五入
rand()随机数
sign(...)返回值的正负[-1,0,1]
pi()圆周率
pow(x,y)x^y[支持小数]
sqrt(x)x开平方根
exp(x)e^x
mod(x,y)x%y
log(x)lnx
log10(x)lgx
radians(x)x有角度转弧度
degrees(x)x有弧度转角度插入代码片

3.日期函数

date()年月日
time()时分秒
now()年月日-时分秒
unix_timestamp('yyyy-MM-dd  HH:MM:SS')
from_unixtime(ms)#毫秒数转换成时间
year(date)/month(date)/day(date)/hour(date)/minute(date)/second(date)/
weekofyear(date)当年第几周  1~53/weekofday(date)星期？1为星期日/dayofyear(date)
quarter(date)第几个季度 1~4
datediff(d1,d2)
adddate(date,INTERVAL '±n[ m]' YEAR|QUARTER|YEAR_MONTH|MONTH|DAY...)
date_format(date,'%Y/%m/%d %H:%i:%s')

4.系统级函数

if(expr,v1,v2)
ifnull(fieldV,defieldV)
case when expr1 then v1 when expr2 then v2...   delimiter;
case @a when a1 then v1 when a2 then v2....
select database();
select @@identity;
md5(concat('src_str','pass_str','src_str'));
select encode('src_str','pass_str');
select decode('crypt_str','pass_str');

5.聚合函数

	min(NUMBER_FIEID_NAME)
	max(NUMBER_FIEID_NAME)
	sum(NUMBER_FIEID_NAME)
	avg()
	count（1/distinct FIEID_NAME）

3. 高阶函数：存储过程

4.触发器

操作

1.数据库操作：

1.创建数据库

create
	create database [if not exists]DATABASE_NAME;
	#mysql数据类型   	java       				mysql
	#字符串		  		String					char(n)/varchar(n)/text/long text
	#字符串		  		char 					没有/char((1)
	#整数				byte/short/int/long		smallint/【int/bigint(n)】
	#小数				float/double/BigDecial  float/double/money/real/【numerie(m,n)/decimal(m,n)】
	#布尔				boolean                 bit
	#日期				java.util.Date 			date/datetime/timestamp

2.删除数据库

	drop database [if not exists]DATABASE_NAME;

3.展示数据库

	show databases;

2.表操作:

	1.创建表

create table TABLE_NAME(	
	FIELD_NAME DATA_TABLE(N) 
		/zerofill 				#int(4)-zerofill高位不足则补零
		/auto_increment 		#auto_increment 自增列
		/primary key 			#primary key 主键			不可重复，不可为null，单表唯一
		/not null				#[not] null 非空
		/default VALUE			#default VALUE 默认值
		/unique key				#unique key 唯一值			不可重复，不可为null,可以多字段

2.删除表

drop table TABLE_NAME;

3.展示表

	use DATABASE_NAME;
	show tables;

4.修改表内容

alter table TABLE_NAME ADD(FIELD_NAME DATA_TABLE(N));

3.表数据操作：
1.插入数据

insert
	insert into TABLE_NAME(FIELD_NAME,....) values(value1,...)
	insert into TABLE_NAME(FIELD_NAME,....) values
	(value1,...),
	(value1,...),
	(value1,...);
	insert into TABLE_NAME(FIELD_NAME1,....) select FIELD_NAME1,...from ANOTHER_TABLE;

create table TABLE_NAME(select FIELD_NAME1,FIELD_NAME2,...FIELD_NAMEn from ANOTHER_TABLE);

2.删除数据

delete from TABLE_NAME where ...;

3.修改数据

update table NEW_TABLE set FIELD_NAME=VALUE1,...where...;

4.查看数据

	(1)基础查询

select 
	（distinct）FIEID_NAME as ALLAS_NAME,...
from 
	TABLE_NAME
where
	CONDITION
group by 
	GROUP_FILED_NAME,...
having
	GROUP_RESULT...
order by 
	ORDER_FIELD,...(ASC|DESC)
limit
	(pageNo-1)*pageSize, pageSize

（2）复杂查询
 - 合并查询(合并的是列)

	select
		S.*,C.*
	from
		table S,table C 
	where 
		S.ref_id=C.pid;

 - 联合查询（合并行）

select ...
union
select ...
列要一致

 - 子查询

(distinct)FIEID_NAME as ALLAS_NAME,...
from 
		TABLE_NAME
where
		CONDITION
group by 
		GROUP_FILED_NAME,...
having
		GROUP_RESULT...
order by 
		ORDER_FIELD,...(ASC|DESC)
limit
		(pageNo-1)*pageSize, pageSize```
	 - 连接查询
	 

```sql
#内连接
		select
			FIEID_NAME,...
		from
			table A
		where
			CONDITION
		inner join
			table B
		on 
			A.xxxId=b.xxxId
		...
#外连接
		#左外连接
select
	FIEID_NAME,...
from
	table A
where
	CONDITION
left [outer] join
	table B
on 
	A.xxxId=b.xxxId
...
	#右外连接
select
	FIEID_NAME,...
from
	table A
where
	CONDITION
right [outer] join
	table B
on 
	A.xxxId=b.xxxId
...

 - 行转列

max(case...when...then...else 0 end)group by 
max(case C.cname when '生物' then S.score else 0 end)'生物',
max(case C.cname when '体育' then S.score else 0 end)'体育'

 - 列转行

group_concat(distinct course_id order by course_id)

条件语法

where|having
		#单条件
			FIEID_NAME = | <> | > | >= | < | <= VALUE
			FIEID_NAME between VALUE_SMALL and VALUE_BIG 
				<=>FIEID_NAME>=VALUE_SMALL and FIEID_NAME<=
				#限数值
			FIEID_NAME is [not] NULL	#空或非空
			FIEID_NAME in (VALUE1,...) #列举
			FIEID_NAME like ''#字符串#模糊条件  %任意长度任意内容  _ 一个长度，内容任意 []一个长度，指定内容
		#多条件
			and
			or
			not