mysql

mysql

1.mysql常见函数

字符函数

1
2
3
4
5
6
7
8
9
10
select length(str) # 获取长度
select concat(str1,str2)　＃连接字符串
select upper(str) 
select lower(str)
select substring(str,start,len) # 截取字符串　下标从1开始
select instr(pstr,cstr) # 返回子串第一次出现的索引
select trim('a' from 'aaaaaaaaaafaafaaa')# 指定去除两边的字符
select lpad(str,len,s) # 用指定的字符实现左填充　len：总长度　str+s的长度
select rpad(str,len,s) # 用指定的字符实现右填充　len：总长度　str+s的长度
select replace(source,sourcestr,desstr) # 替换掉source中的sourcestr

数学函数

1
2
3
4
5
select round(1.65,1) # 四舍五入
select ceil(1.3) # 2 向上取整　>=1.3的最小整数
select floor(9.9) # 向下取整　<=9.9的最大整数
select truncate(1.6899,2) # 截断
select mod(a,b) # a-a/b*b 被除数为负，结果为负

日期函数

1
2
3
4
5
6
7
8
select NOW() #返回当前系统日期加时间
select curdate()# 返回日期
select curtime() # 返回时间
select year(now())
select month(now())
select day(now())
select str_to_date('2019-07-29','%Y-%c-%d') #将日期格式的字符串转换为日期
select date_format() # 将日期转换为字符串 select date_format(now(),'%y-%m-%d');

其它

1
2
3
4
select version()
select database()
seelct user()
show variables like '%char%'

流程控制函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# if
select if(expr,true.false)
# case函数的使用
#1. switch case
select 
case 要判断的字段或表达式
when 常量1 then 要显示的值或语句
when 常量2 then 要显示的值或语句
else 要显示的值或语句
end
from table
#2.多重if
select 
case 
when expr then 要显示的值或语句
when expr then 要显示的值或语句
else 要显示的值或语句
end
from table

limit:分页

1
limit offset,size # offset 要显示的条目的起始索引，从０开始，size要显示的条目个数

2.DDL

库的管理

1
2
3
4
5
6
7
# 库的创建
create database if not exists dbname ;
# 库的修改
rename database oldname to newname;
alter database dbname character set gbk
# 库的删除
drop database if exists dbname

表的管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 表的创建
create table if not exists tname(
	列名 列的类型 约束
	列名 列的类型 约束
	....
)

# 表的修改
#1. 修改列名
alter table tname change column oldcol newcol dataType
#2.修改列的类型或约束
alter table tname modify column cloname newdataType
#3.添加新列
alter table tname add column newcol dataType;
#4.删除列
alter table tname drop column cloname
#5.修改表名
alter table tname rename to newtname

#表的删除
drop table if exists tname;

#表的复制
# 复制结构
create table copy like tname
# 结构+数据
create table copy2
select * from tname

常见约束

  • NOT NULL:字段不能为空
  • DEFAULT:用于保证该字段有默认值
  • PRIMARY KEY:主键约束，唯一不为空
  • UNIQUE:唯一约束，可以为空
  • CHECK:检查约束 [mysql中不支持]
  • FOREIGN KEY :外键，用于限制两个表的关系

列级约束：

​ 六大约束语法都支持，但外键约束没效果

表级约束：

​ 除了非空，默认，都支持

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 添加表时添加约束
create table stu(
	id int primary key,
	name varchar(20) not null,
	gender char(1) check(gender='男' or gender='女'),
	seat int unique ,
	majorid int foreign key references major(id)
);
create table major(
	id int primary key,
	majorName varchar(20)
);

# 添加表级约束
drop table if exists stu;
create table stu(
	id int ,
	name varchar,
	gender char(1) ,
	seat int  ,
	majorid int,
	constraint pk primary key(id)
	constraint uq unique(seat)
	constraint ck check(gender='男' or gender='女')
	constraint fk_stu_major froegin key(mojorid) references major(id)
);

# 修改表时添加约束 1
alter table tname modify column colname dataType constraint

# 修改表时添加约束 2
alter table tname add [constraint name] 约束类型(字段名)

# 修改表时删除约束
# 非空约束和默认约束
alter table tname modify column colname dataType；
# 主键约束
alter table tname drop primary key;
# 唯一约束
alter table tname drop index colname
# 外键约束
alter table tname drop foreign key colnames

3.视图

一种虚拟存在的表，只保存sql逻辑，不保存查询结果，当多个地方用到同样的查询结果方便使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 视图的创建
create view viewName
as
查询语句
create view ct_view 
as 
select * from ct_call;

# 视图的修改 
# 1
create or replace view viewName
as
查询语句
# 2
alter view viewName
as
查询语句

# 视图的删除
drop view viewName ...

# 查看视图
desc viewName;
show create view viewName

4.变量

系统变量：系统定义

1
2
3
4
5
6
7
8
9
10
11
12
# 查看所有系统变量
show global | [session] variables;
# 查看满足条件的系统变量
show global | [session] variables like '%char%';
# 查看指定的某个系统变量的值
select @@gloable|[session].系统变量名
select @@tx_isolation;
# 为某个系统变量复制
set gloable|[session] 系统变量名 = value
set @@gloable|[session].系统变量名 = value

如果是全局级别需要加global,如果是session，可以不加，默认session

自定义变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 用户变量 （针对当前会话有效）
# 1.声明并初始化 = 或 :=
set @用户变量名=值
set @用户变量名:=值 
select @用户变量名:=值
# 2. 赋值
方式一：
    set @用户变量名 =值
    set @用户变量名:=值 
    select @用户变量名:=值
方式二:
	select 字段 into @用户变量名 from table
# 3.使用（查看)
select @用户变量名; 

#局部变量 （仅在begin end中有效)
# 1.声明
declare 变量名 类型;
declare 变量名 类型 default value;
# 2.赋值
方式一：
    set 局部变量名 =值
    set 局部变量名:=值 
    select @局部变量名:=值
方式二:
    select 字段 into 局部变量名 from table
# 3.使用（查看)
select @用户变量名;

5.存储过程和函数

存储过程

一组预先编译好的sql语句集，经编译后存储在数据库，用户只需要传递给定的参数和存储过程名，就可以调用。减少了编译次数并且减少了数据库服务的连接次数，提高了效率。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 创建语法
create procedure 存储过程名(参数列表)
begin
	一组合法的sql语句
end

参数列表：
    参数模式 参数模式 参数类型
        in stuname varchar(20)
        参数模式：
            in:该参数作为输入
            out ：该参数作为返回值
            inout：既可以作为输入又可以作为输出
如果存储过程体只有一句话，begin end 可以省略,存储过程每天sql必须加;
存储过程的结尾可以使用 delimiter 重新设置
etc. delimiter 结束标志

# 调用
call 存储过程名(实参)

# 删除
drop procedure pname;'

# 查看
show create procedure p1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 例子
delimitet $
# 无参的存储过程
create procedure p1()
begin
select * from ct_call;
end$
call p1()$

#in
create procedure p2(in cid int)
begin
select * from ct_call where id = cid;
end$
call p2(1)$

#out
create procedure p3(in cid int,out sum int)
begin
select sumCall into sum from ct_call where id = cid;
end$
call p3(1,@sum)$
select @sum$

#inout
create procedure p4(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end$
set @a = 10$
set @b = 20$
p4(@a,@b)$
call(@a,@b)
select @a$
select @b$

函数

存储过程没有返回值，或者多个返回值，函数只有一个返回值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 创建
create function 函数名(参数列表) returns 返回值类型
begin
	函数体
end

参数列表：
	参数名 参数类型
函数体:肯定会有return语句，如果没有则会报错
如果return没有放在函数体的最后也不报错，但不建议

# 调用 
select 函数名(参数列表)

# 查看
show create function fname;

# 删除 
drop function fname;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 例子
 create Function f1() returns int
     begin
     declare c int default 0;
    select count(1) into c from ct_call;
     return c;
     end $
 select f1()
 
 create Function f2(cid int) returns int
 begin 
 declare sum int default 0;
 select sumCal into sum from ct_call where id = cid;
 return sum;
 end $
 select f2(1)

流程控制结构

  • 分支结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# if
if (表达式1，表达式2，表达式3) # 如果表达式1成立，返回表达式2的值，否则返回表达式3的值

if 条件1 then 语句1；
elseif 条件2 then 语句2;
...
end if;


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

case 
when 要判断的条件1 then 返回值1或语句；
when  要判断的条件2 then 返回值2或语句;
...
else 返回值n
end case;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 例子
#if
create function f1(score int) returns char
begin
    if score>80 then return 'A';
    elseif score<=80 then return 'B';
    end if;
end$

# case
create procedure p1(in score int)
begin
    case
    when score>80 then select 'A';
    else select 'B';
    end case;
end $
  • 循环结构

三种循环while,loop,repeat， iterate类似continue,leave 类似break 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# while
【标签：】while 循环条件 do
	循环体
end while 【标签】

# loop
【标签：】loop
	循环体
end loop 【标签】

# repeat
【标签：】repeat
	循环体
until 结束循环的条件
end repeat【标签】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 例子
# while
create procedure p_w(in num int)
begin
	declare i int default 1;
	while i<num do
        select * from ct_call where id = i;
        set i = i+1;
	end while;
end $

# loop
create procedure p_l(in num int)
begin
	declare i int default 1;
  l:loop
	  select * from ct_call where id = i;
      set i = i+1;
      if i>10 then leave l;
      end if;
    end loop l;
end$

# repeat
create procedure p_p(in num int)
begin
	declare i int default 1;
	repeat
        select * from ct_call where id = i;
        set i = i+1;
    until i>10
    end repeat;
end$

6.行转列与列转行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE TABLE `TEST_TB_GRADE` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
|  1 | 张三      | 数学   |    34 |
|  2 | 张三      | 语文   |    58 |
|  3 | 张三      | 英语   |    58 |
|  4 | 李四      | 数学   |    45 |
|  5 | 李四      | 语文   |    87 |
|  6 | 李四      | 英语   |    45 |
|  7 | 王五      | 数学   |    76 |
|  8 | 王五      | 语文   |    34 |
|  9 | 王五      | 英语   |    89 |
+----+-----------+--------+-------+

行转列:
select USER_NAME,
max(case course when '语文' then score else 0 end) '语文',
max(case course when '数学' then score else 0 end) '数学',
max(case course when '英语' then score else 0 end) '英语'
from TEST_TB_GRADE
group by USER_NAME;
+-----------+--------+--------+--------+
| USER_NAME | 语文   | 数学   | 英语   |
+-----------+--------+--------+--------+
| 张三      |     58 |     34 |     58 |
| 李四      |     87 |     45 |     45 |
| 王五      |     34 |     76 |     89 |
+-----------+--------+--------+--------+

列转行:
create table test_tb_grade2 as
(
select USER_NAME,
    max(case course when '语文' then score else 0 end) '语文',
    max(case course when '数学' then score else 0 end) '数学',
    max(case course when '英语' then score else 0 end) '英语'
from TEST_TB_GRADE
group by USER_NAME
);

select user_name,'语文'course,语文 as course from test_tb_grade2
union select user_name,'数学'course,数学 as course from test_tb_grade2
union select user_name,'英语'course,英语 as course from test_tb_grade2;

本文标题:mysql

文章作者:HanderH

发布时间:2019-07-29, 19:12:14

最后更新:2019-07-31, 16:39:50

原始链接:http://www.handerh.top/2019/07/29/mysql基础/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 1.mysql常见函数
    1. 1.1. 字符函数
    2. 1.2. 数学函数
    3. 1.3. 日期函数
    4. 1.4. 其它
    5. 1.5. 流程控制函数
    6. 1.6. limit:分页
  2. 2. 2.DDL
    1. 2.1. 库的管理
    2. 2.2. 表的管理
    3. 2.3. 常见约束
  3. 3. 3.视图
  4. 4. 4.变量
  5. 5. 5.存储过程和函数
    1. 5.1. 存储过程
    2. 5.2. 函数
    3. 5.3. 流程控制结构
  6. 6. 6.行转列与列转行
|
载入天数...载入时分秒...