MySQL(十一)

1.1 约束条件

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK)    标识该字段为该表的外键

NOT NULL    标识该字段不能为空

UNIQUE KEY (UK)    标识该字段的值是唯一的

AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT    为该字段设置默认值

 

UNSIGNED 无符号

ZEROFILL 使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值

2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'

age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

3. 是否是key

主键 primary key

外键 foreign key

索引 (index,unique...)

1.1.1 not null,default

not null - 不可空

null - 可空

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table t4(x char(4) not null);

create table user(id int,name char(16),sex enum('male','female') not null default 'male');

1.1.2 primary key

    约束字段不为空并且唯一

    依据：主键是innodb的表组织数据的依据

注意：一张表中必须有且只能有一个主键

一个表中可以：

单列做主键

多列做主键（复合主键）

create table t5(

        id int primary key auto_increment,

        name char(20)

    );

    insert into t5(name) values('egon'),('alex');

 

    select name from t5 where id = 1000;

 

    

    了解：联合主键

  create table t6(

        id int,

        name char(20),

        primary key(id,name)

    );

1.1.3 unique key

   

 create table service(

        id int primary key auto_increment,

        name char(20) unique

    );

    not null+unique:会在没有指定主键的情况下被识别为主键

    

create table service1(

        id int,

        name char(20) not null unique

    );

 

    create table service2(

        id int primary key auto_increment,

        name char(20) unique,

        ip char(15),

        port int,

        unique(ip,port)

    );

    insert into service2(name,ip,port) values

    ('nginx','192.168.1.10',80),

    ('haproxy','192.168.1.10',8080),

    ('mysql','192.168.1.10',3306);

 

    insert into service2(name,ip,port) values

    ('nginx1','192.168.1.10',80);

1.1.4 auto_increment

约束字段为自动增长，被约束的字段必须同时被key约束

    

show variables like '%auto%';

    # 结果

    auto_increment_increment    | 1 # 步长

    auto_increment_offset       | 1 # 起始位置

    #强调：起始位置的值必须<=步长

基于全局级别的

   

 set global auto_increment_increment=5;

 set global auto_increment_offset=3;

如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略

   

 create table t7(id int unique auto_increment);

    insert into t7 values();

 

    set global auto_increment_increment=1;

    set global auto_increment_offset=1;

 

1.1.5 foregn key

    创建表：

        1、先创建被关联的表：部门表

        #强调：被关联的字段必须唯一

       

 create table dep(

            id int primary key auto_increment,

            name char(10)

        );

        2、再创建关联表：员工表

 

  create table emp(

            id int primary key auto_increment,

            name char(16),

            age int,

            dep_id int,

            foreign key(dep_id) references dep(id)

            on delete cascade

            on update cascade

        );

插入记录时：

        #1、先往被关联表插入：部门表

      

  insert into dep(name) values

        ('外交部'),

        ('技术哈哈部'),

        ('销售部');

        #2、再往关联表插入：员工表

       

 insert into emp(name,age,dep_id) values

        ('egon',19,1),

        ('alex',74,2),

        ('wxx',38,2),

        ('yxx',22,3);

        #错误操作

        

 insert into emp(name,age,dep_id) values

        ('lhf',29,4);

 

 delete from dep where id=2;

delete from emp where dep_id=2;

1.2 表之间关系

分析步骤：

#1、先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

1.2.1 多对一

三张表：出版社，作者信息，书

书    出版社 

一个出版社可以出版多本书

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

1.2.2 多对多

三张表：出版社，作者信息，书

多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

作者    书

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

1.2.3 一对一：fk+unique

    客户表  学生表

如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

1.3 单表查询

1.3.1 单表语法：

    select

        distinct

        字段1,

        字段2,...

    from

        表名

    where 约束条件

    group by 分组条件

    having 过滤条件

    order by 排序字段

    limit n;

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

1.3.1.1  简单查询

       

select * from emp;

select id,name from emp;

去除重复

      

  select distinct post from emp;

四则运算

       

 select name,salary*12 as annual_salary from emp;

 select name,salary*12 annual_salary from emp;

1.3.1.2    定义显示格式

     

   select

            concat('姓名: ',name) as new_name,

            concat('年薪: ',salary*12) as annual_salary

        from

            emp;

#CONCAT() 函数用于连接字符串

     

select concat(name,'_SB') as new_name from emp;

 

select concat(name,'_SB') as new_name from emp where name != 'egon';

结合CASE语句：

 

 select

            (

                case

                when name = 'egon' then

                    name

                when name = 'alex' then

                    concat(name,'_BIGSB')

                else

                    concat(name,'_SB')

                end

            ) as new_name

        from

            emp;

       

CONCAT_WS() 第一个参数为分隔符

       

 select concat(name,':',salary,':',sex) from emp;

 select concat_ws(':',name,salary,sex) from emp;

1.3.2 where约束

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=

2. between 80 and 100 值在10到20之间

3. in(80,90,100) 值是10或20或30

4. like 'egon%'

    pattern可以是%或_，

    %表示任意多字符

    _表示一个字符

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

    

select id,name,age from emp where age > 20;

    select id,name,age from emp where age >= 20 and age <= 38;

    select id,name,age from emp where age between 20 and 38;

    select id,name,age from emp where age <= 20 or age >= 38;

    select id,name,age from emp where age not between 20 and 38;

 

    select * from emp where age=18 or age=28 or age=38;

    select * from emp where age in (18,28,38);

 

    select * from emp where age=18 or sex='male';

 

    select * from emp where name like 'jin%';

    select * from emp where name like '__';

 

1.3.3 group by分组

    分组依据应该找重复度比较高的字段

分组之后只能查到分组字段，或者聚合的结果

首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

为何要分组呢？

    取每个部门的最高工资

    取每个部门的员工数

    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

大前提：

    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

单独使用GROUP BY关键字分组

    

SELECT post FROM employee GROUP BY post;

    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用

    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名

    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用

select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

强调：

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义

多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

 set global sql_mode='strict_trans_tables,only_full_group_by'; #设置成功后，一定要退出，然后重新登录方可生效

    select post,max(salary) from emp group by post;

    select post,min(salary) from emp group by post;

    select post,sum(salary) from emp group by post;

    select post,avg(salary) from emp group by post;

    select post,count(id) from emp group by post;

    select post,group_concat(name) from emp group by post;

 

    select count(id) from emp;

    select count(id) from emp where max(salary) > 1;

1.3.4 having 过滤条件

HAVING与WHERE不一样的地方在于!!!!!!

#！！！执行优先级从高到低：where > group by > having

#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

   

 select post,avg(salary) from emp group by post having avg(salary) > 20000;

1.3.5 order by排序

按单列排序

   

SELECT * FROM employee ORDER BY salary;

SELECT * FROM employee ORDER BY salary ASC;

SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序

    

SELECT * from employee

        ORDER BY age,

        salary DESC;

 

    select *　from emp order by age asc; # 默认升序，从小到大

    select *　from emp order by age desc; #从大到小

 

    select * from emp order by age asc,id desc;

 

    select post,avg(salary) from emp group by post order by avg(salary);

 

1.3.6 limit n

SELECT * FROM employee ORDER BY salary DESC

        LIMIT 3;                    #默认初始位置为0

   

    SELECT * FROM employee ORDER BY salary DESC

        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

 

    SELECT * FROM employee ORDER BY salary DESC

        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

 

练习：分页显示，每页5条

    

select * from emp limit 3;

    select * from emp limit 0,5;

    select * from emp limit 5,5;

    select * from emp limit 10,5;

    select * from emp limit 15,5;

 

 

def from(db,table):

    f=open(filepath,'r')

    return f

 

def where(f,pattern):

    for line in f:

        if pattern:

            yield line

 

def group():

    pass

 

def having():

    pass

 

def distinct():

    pass

 

def order():

    pass

 

def limit():

    pass

 

 

 

def select():

    f=from(db,table)

    lines=where(f,'id > 1')

    group_res=group(lines)

    having_res=having(group_res)

    distinct_res=distinct(having_res)

    order_res=order(distinct_res)

    res=limit(order_res)

    print(res)

 

 

1.3.7 正则

select * from emp where name regexp '^jin.*$';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}'

小结：对字符串匹配的方式

WHERE name = 'egon';

WHERE name LIKE 'yua%';

WHERE name REGEXP 'on$';

1.4 多表查询

1.4.1 笛卡尔积

select * from emp,dep;

select * from emp,dep where emp.dep_id=dep.id;

1.4.2 内连接inner join：取两张表的共同部分

select * from emp inner join dep on emp.dep_id=dep.id;

 

1.4.3 左连接left join：在内连接的基础上保留左表的记录

select * from emp left join dep on emp.dep_id=dep.id;

1.4.4 右连接right join：在内连接的基础上保留右表的记录

select * from emp right join dep on emp.dep_id=dep.id;

1.4.5 全外连接full join：在内连接的基础上保留左、右表的记录

select * from emp left join dep on emp.dep_id=dep.id

union

select * from emp right join dep on emp.dep_id=dep.id;

 

1.5 子查询

1.5.1 带IN关键字的子查询

#查询平均年龄在25岁以上的部门名

select name from dep where id in

(select dep_id from emp group by dep_id having avg(age) > 25);

#查看技术部员工姓名

select

    emp.name

from emp

inner join dep on emp.dep_id = dep.id

where dep.name = '技术'

;

select name from emp where dep_id =(select id from dep where name='技术');

 

#查看不足1人的部门名

select name from dep where id not in (

select dep_id from emp group by dep_id having count(id) >= 1);

 

select name from dep where id not in (

select distinct dep_id from emp);

 

#查询大于所有人平均年龄的员工名与年龄

select name,age from emp where age > (

select avg(age) from emp);

#查询大于部门内平均年龄的员工名、年龄

select

    t1.*

from emp as t1

inner join

(select dep_id,avg(age) as avg_age from emp group by dep_id) as t2

on t1.dep_id = t2.dep_id

where t1.age > t2.avg_age

;

# 统计每个部门最新入职的员工名，入职日期

select

    t1.name,t1.hire_date

from emp as t1

inner join

(select post,max(hire_date) max_date from emp group by post) as t2

on t1.post = t2.post

where t1.hire_date = t2.max_date

;

select name from emp where name='egon';

select name from emp where id > 1000;

select name from emp where id > 1000 and id < 1005;

select name from emp where id = 1000;

 

select name from emp where salary = 20000/12;

 

 

select * from emp;

1.6 pymysql模块

#安装

pip3 install pymysql

1.6.1 基本使用

'''

create table user(

    id int primary key auto_increment,

    username char(16),

    password char(20)

);

insert into user(username,password) values

('egon','123'),

('alex','456'),

('wxx','456');

'''

import pymysql

 

user=input('user>>: ').strip()

pwd=input('password>>: ').strip()

 

#1、建连接

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    db='db6'

)

#2、拿游标

cursor=conn.cursor()

 

#3、提交sql

# sql='select id from user where username="%s" and password="%s"' %(user,pwd)

# print(sql)

sql='select id from user where username=%s and password=%s'

rows=cursor.execute(sql,(user,pwd))

 

if rows:

    print('登录成功')

else:

    print('用户名或密码错误')

 

conn.commit()

#4、回收资源

cursor.close()

conn.close()

1.6.1.1  execute()之sql注入

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了

#1、sql注入之：用户存在，绕过密码

egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码

xxx' or 1=1 -- 任意字符

解决方法：

# 原来是我们对sql进行字符串拼接

# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)

# print(sql)

# res=cursor.execute(sql)

 

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）

sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上

res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

1.6.2 增删改

import pymysql

 

#1、建连接

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    db='db6'

)

#2、拿游标

cursor=conn.cursor()

 

#3、提交sql

sql='insert into user(username,password) values(%s,%s)'

# rows=cursor.execute(sql,('yxx','123'))

# print(rows)

 

rows=cursor.executemany(sql,[('yxx1','123'),('yxx2','123'),('yxx3','123')])

print(rows)

 

 

conn.commit()

#4、回收资源

cursor.close()

conn.close()

 

1.6.3 查

import pymysql

 

#1、建连接

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    db='db6'

)

#2、拿游标

cursor=conn.cursor(pymysql.cursors.DictCursor)

 

#3、提交sql

sql='select * from user'

rows=cursor.execute(sql)

# print(rows)

 

# print(cursor.fetchall())

# print('===>',cursor.fetchall())

 

print(cursor.fetchone())

print(cursor.fetchone())

print(cursor.fetchmany(3))

 

cursor.scroll(0,mode='absolute') # 相对绝对位置移动

print(cursor.fetchone())

# cursor.scroll(3,mode='relative') # 相对当前位置移动

 

 

#4、回收资源

cursor.close()

conn.close()

1.7 存储过程

1.7.1 视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

1.7.1.1  创建视图

#语法：CREATE VIEW 视图名称 AS  SQL语句

create view teacher_view as select tid from teacher where tname='李平老师';

#于是查询李平老师教授的课程名的sql可以改写为

mysql> select cname from course where teacher_id = (select tid from teacher_view);

+--------+

| cname  |

+--------+

| 物理   |

| 美术   |

+--------+

2 rows in set (0.00 sec)

#！！！注意注意注意：

#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便

1.7.1.2  使用视图

修改视图，原始表也跟着改

select * from course;

 

create view course_view as select * from course; #创建表course的视图

我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的

1.7.2 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

1.7.2.1  创建触发器

# 插入前

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

# 插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

# 删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

# 删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

# 更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

# 更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

'''

1.7.2.2  使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

1.7.2.3  删除触发器

drop trigger tri_after_insert_cmd;

1.7.3 事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

create table user(

id int primary key auto_increment,

name char(32),

balance int

);

 

insert into user(name,balance)

values

('wsb',1000),

('egon',1000),

('ysb',1000);

 

#原子操作

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

update user set balance=1090 where name='ysb'; #卖家拿到90元

commit;

#出现异常，回滚到初始状态

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='egon'; #中介拿走10元

uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到

rollback;

commit;

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  1 | wsb  |    1000 |

|  2 | egon |    1000 |

|  3 | ysb  |    1000 |

+----+------+---------+

3 rows in set (0.00 sec)

1.7.4 存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

使用存储过程的优点：

#1. 用于替代程序写的SQL语句，实现程序与sql解耦

#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

使用存储过程的缺点：

#1. 程序员扩展功能不方便

1.7.4.1  程序与数据库结合使用的三种方式

#方式一：

    MySQL：存储过程

    程序：调用存储过程

 

#方式二：

    MySQL：

    程序：纯SQL语句

 

#方式三：

    MySQL:

    程序：类和对象，即ORM（本质还是纯SQL语句）

1.7.4.2  创建简单存储过程

delimiter //

create procedure p1()

BEGIN

    select user,host from mysql.user;

END //

delimiter ;

#在mysql中调用

call p1()

#在python中基于pymysql调用

cursor.callproc('p1')

print(cursor.fetchall())

1.7.4.3  创建存储过程（有参）

对于存储过程，可以接收参数，其参数有三类：

#in          仅用于传入参数用

#out        仅用于返回值用

#inout     既可以传入又可以当作返回值

delimiter //

create procedure p2(

    in n1 int,

    in n2 int,

    out n3 int

)

BEGIN

    select * from emp where id > n1 and id < n2;

    set n3=1;

END //

delimiter ;

1.7.4.4  执行存储过程

-- 无参数

call proc_name()

-- 有参数，全in

call proc_name(1,2)

-- 有参数，有in，out，inout

set @t1=0;

set @t2=3;

call proc_name(1,2,@t1,@t2)

在python中基于pymysql执行存储过程

import pymysql

 

#1、建连接

conn=pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='123',

    db='db5'

)

#2、拿游标

cursor=conn.cursor(pymysql.cursors.DictCursor)

 

#3、提交sql

# cursor.callproc('p1')

# print(cursor.fetchall())

 

cursor.callproc('p2',(3,5,0)) #@_p2_0=3,@_p2_1=5,@_p2_2=0

print(cursor.fetchall())

 

cursor.execute('select @_p2_2')

print(cursor.fetchone())

 

#4、回收资源

cursor.close()

conn.close()

1.7.4.5  删除存储过程

drop procedure proc_name；

1.8 函数

MySQL中提供了许多内置函数

1.8.1 基本使用

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

        -> 'Sunday October 2009'

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',

    ->                 '%D %y %a %d %m %b %j');

        -> '4th 00 Thu 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

    ->                 '%H %k %I %r %T %S %w');

        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

        -> '00'

准备表和记录

CREATE TABLE blog (

    id INT PRIMARY KEY auto_increment,

    NAME CHAR (32),

    sub_time datetime

);

 

INSERT INTO blog (NAME, sub_time)

VALUES

    ('第1篇','2015-03-01 11:31:21'),

    ('第2篇','2015-03-11 16:31:21'),

    ('第3篇','2016-07-01 10:21:31'),

    ('第4篇','2016-07-22 09:23:21'),

    ('第5篇','2016-07-23 10:11:11'),

    ('第6篇','2016-07-25 11:21:31'),

    ('第7篇','2017-03-01 15:33:21'),

    ('第8篇','2017-03-01 17:32:21'),

    ('第9篇','2017-03-01 18:31:21');

 

#3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组

SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');

 

#结果

+-------------------------------+----------+

| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |

+-------------------------------+----------+

| 2015-03                       |        2 |

| 2016-07                       |        4 |

| 2017-03                       |        3 |

+-------------------------------+----------+

3 rows in set (0.00 sec)

1.8.2 自定义函数

#！！！注意！！！

#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能

#若要想在begin...end...中写sql，请用存储过程

delimiter //

create function f1(

    i1 int,

    i2 int)

returns int

BEGIN

    declare num int;

    set num = i1 + i2;

    return(num);

END //

delimiter ;

1.8.3 执行函数

# 获取返回值

select UPPER('egon') into @res;

SELECT @res;

# 在查询中使用

select f1(11,nid) ,name from tb2;

1.8.4 删除函数

drop function func_name; 

1.9 流程控制

1.9.1 条件语句

1.9.1.1  if条件语句

delimiter //

CREATE PROCEDURE proc_if ()

BEGIN

   

    declare i int default 0;

    if i = 1 THEN

        SELECT 1;

    ELSEIF i = 2 THEN

        SELECT 2;

    ELSE

        SELECT 7;

    END IF;

 

END //

delimiter ;

 

1.9.2 循环语句

1.9.2.1  while循环

delimiter //

CREATE PROCEDURE proc_while ()

BEGIN

 

    DECLARE num INT ;

    SET num = 0 ;

    WHILE num < 10 DO

        SELECT

            num ;

        SET num = num + 1 ;

    END WHILE ;

 

END //

delimiter ;

1.9.2.2  repeat循环

delimiter //

CREATE PROCEDURE proc_repeat ()

BEGIN

 

    DECLARE i INT ;

    SET i = 0 ;

    repeat

        select i;

        set i = i + 1;

        until i >= 5

    end repeat;

 

END //

delimiter ;

 

1.9.2.3  loop

BEGIN

   

    declare i int default 0;

    loop_label: loop

       

        set i=i+1;

        if i<8 then

            iterate loop_label;

        end if;

        if i>=10 then

            leave loop_label;

        end if;

        select i;

    end loop loop_label;

 

END