Mysql总结

Mysql 常见命令

net start msyql; #启动mysql
net stop mysql; #停止mysql
show databases;   #查看所有库
use 库名; #打开指定的库
show tables; #查看当前库内的所有表
show tables from mysql;
select database(); #查看当前库
create table 表名(列名 列类型,name varchar(20)); #创建表
desc 表名; #查看表结构
select * from 表名; #查看表内数据
insert into 表名(id,name) values(1，'张三'); #向表中插入数据
update 表名 set name='' where id=序号(id); #修改表中数据
delete from 表名 where id=序号; #删除指定数据
select version();  mysql --version  mysql -V #查看当前mysql版本

去重查询

select distinct department_id from 表名 #去重

条件查询

        select 
			字段(id,title,author)
	from
    		表名(ygmx_post)
   	where
   			条件(id=233)

模糊查询

like
between and 
in
is null | is not null
#安全等于
<=>

like用法(%表示任意长度的字符)

            #查询出创建者名称包含【编辑】的文章的标题
            select 
		        title
            from  
		        ygmx_post(数据库名);
            where
		        author like '%编辑%';
		
		
		
            #查询出创建者名称由【编辑】开始的文章的标题
            select 
		        title
            from  
		        ygmx_post(数据库名);
            where
		        author like '编辑%';
		
		#查询出创建者名称由【编辑】结束的文章的标题
            select 
		title
            from  
		ygmx_post(数据库名);
            where
		author like '%编辑';

between end

#查询id介于1000-1100之间的文章
select 
   	*
from
   	ygmx_post;
where
   	id between 1000 and 1100;

排序查询

 select 查询列表
 from  表
【where 筛选条件】
 order by 排序列表 【asc|desc】
# asc代表升序 desc代表降序

函数

函数调用方法

select 函数名（参数列表）【from表】

字符函数

#length 获取参数值的字节个数
select length('');
#concat 拼接字符串
select concat(,'',);
from 表名;
#upper、lower
select upper('');
#substr、substring
select substr('',索引,'');
#instr 返回字串第一次出现的索引，如果找不到返回0
select instr('','');
#trim 去前后空格
select trim('');
#lpad 用指定的字符左填充指定长度
select lpad('',指定长度,'指定的字符');
#replace 替换
select replace('原文本','要替换的文本','替换的目标文本')

数学函数

#round 四舍五入
select round(1.45);
#ceil 向上取整,返回>=该参数的最小数
select ceil(1.00);
#floor 向下取整，返回<=该参数的最大整数
select floor(9.99);
#truncate 截断
select truncate(1.69999,1);
#mod 取余
select mod(-10,-3);

日期函数

#now 返回当前系统日期＋时间
select now();
#curdate 返回当前系统日期，不包括时间
#curtime 返回当前时间，不包括日期
#str_to_date 将字符通过指定的格式转换成日期
#date_format 将日期转换成字符

流程控制函数

# if 函数  if else效果
# 1 case 函数 switch case
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
...
else 要显示的值n或者语句n;
end
# 2
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end

分组函数

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order 不要 排序列表】

#1.都忽略null值
#2.sum，avg一般用于处理数值型，max min count可以处理任何类型
#3.可以和distinct搭配实现去重
#4.一般使用count(*)用作统计行数
#5.和分组函数一同查询的字段要求是group by后的字段

#sum 求和
#avg 平均值
#max 最大值
#min 最小值
#count 计算非空值的个数

增删改查

分组查询

select 分组函数，列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
# 按多个字段分组

表连接

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件

#连接类型：
#内连接： inner

#外连接： left【outer】

			#左外： left【outer】

			#右外： right【outer】

			#全外： full【outer】

#交叉连接：cross

笛卡尔乘积

select name,boyName from beauty,boys;

插入语句

#①
insert into 表名(列名,...)
values(值1,....);
#②
insert into 表名
set 列名=值，列名=值....

修改语句

update 表名
set 列=新值,列=新值...
where 筛选条件

删除语句

#① 单表删除
delete from 表名 where 筛选条件
#② 清空表
truncate table 表名;

#sql92
# 多表删除
delete 表1的别名，表2的别名
from 表1 别名。表2 别名
where 连接条件
and 筛选条件

#sql99
# 多表删除
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

数据库操作

创建修改数据库

create database [if not exists] 库名;

#更改库的字符集
alter database books character set 编码;

删除数据库

drop database [if exists] books;

表的操作

创建表

create table 表名{
		别名 列的类型[(长度) 约束],
		别名 列的类型[(长度) 约束],
		别名 列的类型[(长度) 约束],
		...
		别名 列的类型[(长度) 约束],
}

修改表

#修改列名
alter table book change column 旧列名 新列名 datetime;
#修改列的类型或约束
alter table book modify column 列名 timestamp;
#添加列
alter table author add column 列名 double;
#删除列
alter table author drop column 列名;
#修改表名
alter table author rename to 表名;

删除表

drop table [if exists]表名;

复制表

#仅仅只复制表的结构
create table copy like 表名;
#复制表的结构+数据
create table copy2
select * from 表名;
#只复制部分数据
create table copy3
select id,name 
from 表名
where nation = 中国;
# 仅仅复制某些字段
create table 表名
select id,au_name
from author
where 1=2;

数据类型

#数值型:
#		整数
#		小数:
#				定点数
#				浮点数
#字符型:
#		较短的文本:char、varchar
#		较长的文本:text、blob(较长的二进制数据)
#日期型

整型

浮点型

字符型

日期型

约束

常见约束

create table 表名(
	字段名 字段类型 约束
)

创建表时添加约束

列级约束

create table stuinfo(
		id INT PRIMARY KEY,
    	stuName VARCHAR() NOT NULL,
    	gender CHAR(1) CHECK(gender='男' OR gender='女'),检查
    	seat INT UNIQUE,#唯一
    	age INT DEFAULT 18,#默认约束
)

表级约束

#表级约束加在最后
create table stuinfo(
		id INT ,
    	stuName VARCHAR(),
    	gender CHAR(1),
    	seat INT ,
    	age INT,
        CONSTRAINT A PRIMARY KEY(id), #id约束
);

修改表时添加约束

#添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

修改表时删除约束

#删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;

创建表时设置标识列

CREATE TABLE 表名(
		id INT PRIMARY KEY AUTO_INCREMENT,#添加表示列
		NAME VARCHAR(20)
);

修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT ;

#开启事务
set autocommit=0;
start transaction; #可选的
# 步骤2：编写事务中的SQL语句(select insert update delete)
# 步骤3：结束事务
commit; #提交事务
rollback; #回滚事务

#开启事务的语句：
update 表 set zs的余额=500 where name='zs'
#结束事务的语句：
update 表 set gx的余额=1500 where name='gx'

事务的隔离级别

read uncommitted; # 出现脏读、幻读、不可重复读
read committed; # 避免出现脏读、出现幻读和不可重复读
repeatable read; # 避免脏读、幻读  但会出现幻读
serializable; # 避免出现脏读、幻读、不可重复读

savepoint 节点名; 设置保存点

用法：

set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a; #设置保存点
delete from account where id=28;
rollback a;

MySQL中默认 从第三个隔离级别repeatable read

视图

创建视图

create view 视图名
as
# 查询语句;

修改视图

#方法一
create or replace view 视图名
as
查询语句;
#方法二
alter view 视图名
as
查询语句;

删除视图

drop view 视图名，视图名，...;

查看视图

desc 视图名;
show create view 视图名;

更新视图

create or replace view 视图名;
as
select last_name,email,salary*12*(1+ifnull(commission_pct,0)) "annual salary"
from employees;

变量

系统变量

#查看所有的系统变量
show global| [session] variables;
#查看满足条件的部分系统变量
show global| [session] variables like '%char%';
#查看指定的某个系统变量的值
select @@global| [session].系统变量名;
#为某个系统变量赋值
1. set global| [session] 系统变量名=值;
2. set @@global| [session].系统变量名=值;

会话变量

#查看所有的会话变量
show session variables;
#查看满足条件的部分会话变量
show [session] variables like '%char%';
#查看指定的某个会话变量的值
select @@session.会话变量名;
select @@会话变量名
#为某个会话变量赋值
1. set session 会话变量名=值;
2. set @@session.会话变量名=值;

自定义变量

# 声明并初始化
set @用户变量名=值;或
set @用户变量名:=值;或
set @用户变量名:=值;
# 赋值
1.通过set或select
set @用户变量名=值;或
set @用户变量名:=值;或
select @用户变量名:=值;

2.通过select into
select 字段 into 变量名
from 表;

局部变量

# 声明
declare 变量名 类型;
declare 变量名 类型 default 值;
# 赋值
1.通过set或select
set 用户变量名=值;或
set 用户变量名:=值;或
select @用户变量名:=值;

2.通过select into
select 字段 into 变量名
from 表;
# 使用
select 局部变量名;

存储过程

创建存储过程

#结束标记 $
# 创建语法
create procedure 储存过程名(参数列表)
begin
		存储过程体(一组合法的SQL语句)
end $
# 调用语法
call 存储过程名(实参列表);
# 查看
show create procedure 存储过程名;
#参数模式：in out inout

删除存储过程

# 语法：
drop procedure 存储过程名;

函数

创建函数

create function 函数名(参数列表) returns 返回类型
begin
		函数体
end

调用函数

select 函数名(参数列表)

查看函数

show create function 函数名;

删除函数

drop function 函数名;

分支结构

if函数，实现简单分支

# 语法
if(表达式1，表达式2，表达式3)

#多条件
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...

case结构

case 变量|表达式|字段
when 要判断的值 then 返回的值1
when 要判断的值 then 返回的值2
...
else 要返回的值n
end

循环结构

分类

#while 
#loop 
#repeat

循环控制

#iterate类似于 continue 继续，结束本次循环，继续下一次
#leave类似于 break 跳出，结束当前所在的循环
# while
while 循环条件 do
		循环体;
end while [标签];
#loop
[标签] loop
		循环体;
end loop [标签];
#repeat
[标签]repeat
		循环体;
until 结束循环的条件
end repeat [标签];

微信扫码查看本文