数据库

1、mysql 介绍

一数据库管理软件的由来

程序的所有组件不可能只在一个机子上，性能问题和单点故障，

程序分散了，还需要解决数据共享问题，

基于网络访问这台共享的机器，用socket。

还需要解决并发、安全，锁的问题、认证等。

这一套套接字服务端、客户端软件就是数据库管理软件。

我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），

于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据

共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。

数据库相关概念

1、数据库服务器：计算机

2、数据库管理软件：MySQL

3、数据库/库：文件夹

4、表:文件，表的标题，叫做字段

5、记录:一个事物的一系列典型的特征：egon,male,18,oldgirl

6、数据：事物的特征，sex='male'

mysql是什么

mysql就是一个基于socket编写的C/S架构的软件

客户端软件

mysql自带：如mysql命令，mysqldump命令等

python模块：如pymysql

数据库管理软件分类

分两大类：

	关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用

	非关系型：mongodb，redis，memcache

可以简单的理解为：

    关系型数据库需要有表结构

    非关系型数据库是key-value存储的，没有表结构

2、mysql 基本管理

mysql被oracle收购了，出了一个mariadb，免费的。

下载

选择社区版本，找之前版本，下载5.6

客户端：

mysql -uroot -p123 -h 服务端ip -P 3306

tasklist | findstr mysql

taskkill /F /PID 6316

做成系统服务

	mysqld --install; --remove

	net start mysql; stop

管理员设置密码

mysqladmin -uroot -p password "123"  但是这样在终端中暴露了密码

破解密码

启动不加载了授权表，没有认证机制了相当于，登进去就是管理员，把数据文件里写的密码重置，重启服务。

net stop MySQL

mysqld --skip-grant-tables

mysql -uroot -p

    update mysql.user set password=password("") where user='root' and host="localhost";

    flush privileges;

C:\Users\Administrator>tasklist |findstr mysql

mysqld.exe                    6316 Console                    1    454,544 K

C:\Users\Administrator>taskkill /F /PID 6316

成功: 已终止 PID 为 6316 的进程。

C:\Users\Administrator>net start MySQL

MySQL 服务正在启动 .

MySQL 服务已经启动成功。

登进去 select user();

统一字符编码

\s  没有分号

mysql 默认的配置名字叫my.ini

ini 模式configparse 模块的类型

	标题代表着命令名，

	如果我这台服务器只运行服务端，没必要配置client的配置；

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

3、基本sql语句

三种操作对象

操作文件夹（库）

    增

        create database db1 charset utf8;

    查

       show databases;

	   show create database db1; 查看db1的详细信息。

    改

        alter database db1 charset gbk; 数据库没有改名字一说。

    删

        drop database db1；

操作文件（表）

    查看当前所在的文件夹:select database();

    切换文件夹：use db1;

    增

        create table t1(id int,name char) engine=innodb;

    查

       show tables;

	   show create table t1;

	   desc t1;

    改

       alter table t1 add age int;

	   alter table t1 drop age;

	   alter table t1 modift name char(16);

	   alter table t1 change name NAME char(13);

    删

       drop table t1;

操作文件的内容（记录）

    增

       insert into(into 可有可无) db1.t1(id,name) values

		(1,'egon'),

		(2,'alex');

    查

        select name from db1.t1;

		select * from t1;

		先找到文件，先from，后select；

    改

        update db1.t1 set name='SB' where id=2;

    删

       delete from t1 where id=2;  删了的数据的自增id不会往回退；

	   truncate t1; 可以用这个

4、数值类型

语法：

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

注意：

1. 在同一张表中，字段名是不能相同

2. 宽度和约束条件可选

3. 字段名和类型是必须的

语法中遇到【】，表示可选的是。最后一个字段后没有逗号

修改表 alter table

语法：

	1. 修改表名

	      ALTER TABLE 表名

	                          RENAME 新表名;

	2. 增加字段

	      ALTER TABLE 表名

	                          ADD 字段名  数据类型 [完整性约束条件…],

	                          ADD 字段名  数据类型 [完整性约束条件…];

	      ALTER TABLE 表名

	                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;

	      ALTER TABLE 表名

	                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

	3. 删除字段

	      ALTER TABLE 表名

	                          DROP 字段名;

	4. 修改字段

	      ALTER TABLE 表名

	                          MODIFY  字段名 数据类型 [完整性约束条件…];

	      ALTER TABLE 表名

	                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

	      ALTER TABLE 表名

	                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）

mysql> create table new_service select * from service;

只复制表结构

mysql> select * from service where 1=2;        //条件为假，查不到任何记录

Empty set (0.00 sec)

mysql> create table new1_service select * from service where 1=2;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

整数类型

1、整型(默认有符号)

	create table t8(n tinyint);

	insert into t8 values(-1);

	insert into t8 values(128);  有符号范围是（-128,127）

	insert into t8 values(-129);

	create table t9(n tinyint unsigned);  unsigned表示无符号，范围是0-255

	insert into t9 values(-1),(256);

只要是int类型，就用四个字节去存，不管宽度，tinyint就是一个字节去存。

所有的整形类型都不用指定宽度，宽度不影响存储大小，只是显示的大小。

整型的宽度代表显示宽度

create table t11(n int(3) unsigned zerofill);

create table t12(n int unsigned zerofill);

create table t13(

    id int

);

浮点型

======================================

#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

定义：

        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号：

           -3.402823466E+38 to -1.175494351E-38,

           1.175494351E-38 to 3.402823466E+38

无符号：

           1.175494351E-38 to 3.402823466E+38

精确度：

           **** 随着小数的增多，精度变得不准确 ****

======================================

#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义：

           双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号：

           -1.7976931348623157E+308 to -2.2250738585072014E-308

           2.2250738585072014E-308 to 1.7976931348623157E+308

无符号：

           2.2250738585072014E-308 to 1.7976931348623157E+308

精确度：

           ****随着小数的增多，精度比float要高，但也会变得不准确 ****

======================================

decimal[(m[,d])] [unsigned] [zerofill]

定义：

          准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

精确度：

           **** 随着小数的增多，精度始终准确 ****

           对于精确数值计算时需要用此类型

           decaimal能够存储精确值的原因在于其内部按照字符串存储。

5、库操作

有一些默认存在的库。

information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等

performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象

mysql：	授权库，主要存储系统用户的权限信息

test：	MySQL数据库系统自动创建的测试数据库

6、存储引擎

存储引擎就是表的类型

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方

法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和

操作此表的类型）

不同的文件对应不同的处理方式；

show engines; 查看支持哪些存储引擎；

innodb 但凡建表，就应该指定主键，主键就是相当于它的索引。

7、字符类型

补充：

mysql5.6的sql模式默认是非严格模式，超了4个字符也能存，把报错处理掉了。

mysql -V

设置sql模式：

show variables like 'sql_mode';

set sql_mode='strict_trans_tables'; 数据长了就报错了；指定的宽度开始生效。

永久添加：配置文件里mysqld的字段下添加sql_mode

存储和选取不一样

取消后边的空格值适用于等号操作；但是不能取消前边和中间的空格。

like 查询，两个符号 % 任意多个字符； 下划线——，代表任意就一个字符。

区别

char:定长

varchar：变长

	#宽度代表的是字符的个数

create table t16(name char(5));

create table t17(name varchar(5));

insert into t16 values('李杰 '); #'李杰   '

insert into t17 values('李杰 '); #'李杰 '

select char_length(name) from t16; #5

select char_length(name) from t17; #3

mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';

select * from t16 where name='李杰';

select * from t17 where name='李杰';

select * from t16 where name like '李杰';

name char(5)

egon |alex |wxx  |

name varchar(5)

1bytes+egon|1bytes+alex|1bytes+wxx|

测试前了解两个函数

length：查看字节数

char_length:查看字符数

8、日期类型

create table student(

    id int,

    name char(16),

    born_year year,

    birth_date date,

    class_time time,

    reg_time datetime

);

insert into student values

(1,'egon',now(),now(),now(),now())  会根据表中的字段类型，year还是date等自动匹配now（）函数。

;

insert into student values

(2,'alex','1999','1999-11-11','11:11:11',"1999-11-11 11:11:11")

;

9、枚举与集合类型

create table employee(

    id int,

    name char(10),

    sex enum('male','female','other'),

    hobbies set('play','eat','music','read')

);

insert into employee values

(1,'egon','male','music,read');

insert into employee values

(2,'alex','xxxx','music,read');  插入空值；要不报错，要不插入不知道什么、或者最大最小值什么的，只有这两种情况。

10、上节课复习

先命令行改了sql模式，看下次链接还生效吗？

set global sql_mode='strict_trans_tables'; 不用重启服务，以后的链接也会生效；重启服务失效；

设置完global之后要退出链接，重新登录才能生效。

改了配置文件，需要重启服务，以后也永久生效；

show variables like '%sql_mode%'; 查看变量。

11、约束条件

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

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

NOT NULL    标识该字段不能为空

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

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

DEFAULT    为该字段设置默认值

UNSIGNED 无符号

ZEROFILL 使用0填充

not null 和default

非严格模式下， NO_ENGINE_SUBSTITUTION，设置了default，插空，插入的是什么？

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

insert t1(id,name) values(1,'houbinglei');

   1 | houbinglei | male

create table student(

name varchar(20) not null,

age int(3) unsigned not null default 18,

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

hobby set('play','study','read','music') default 'play,music'

);

create table t1(id int); #id字段默认可以插入空

mysql> insert into t2 values(); #不能插入空

ERROR 1364 (HY000): Field 'id' doesn't have a default value

设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

unique

create table department1(

id int,

name varchar(20) unique,

comment varchar(100)

);  设置唯一约束

设置联合唯一：

create table service(

id int primary key auto_increment,

name varchar(20),

host varchar(15) not null,

port int not null,

unique(host,port) #联合唯一

);

mysql> insert into service values

    -> (1,'nginx','192.168.0.10',80),

    -> (2,'haproxy','192.168.0.20',80),

    -> (3,'mysql','192.168.0.30',3306)

    -> ;

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);

ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

primary key

从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

在某一个字段后用primary key

create table department2(

id int primary key, #主键

name varchar(20),

comment varchar(100)

);

#方法三：在所有字段后单独定义primary key

create table department3(

id int,

name varchar(20),

comment varchar(100),

constraint pk_name primary key(id); #创建主键并为其命名pk_name

auto-increment

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

不指定id，则自动增长;

对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

mysql> truncate student;

Query OK, 0 rows affected (0.01 sec)

12、外键

foreign key

表的设计思路

如果所有数据再一张表里：浪费空间+ 管理难

(1)先要创建被关联表（主表）

		被关联的字段必须唯一

		create table department(

			id int primary key auto_increment,

			name char(20) not null,

			des varchar(20) not null

		);

(2) 创建关联表(从表)

		create table employee(

			id int primary key auto_increment,

			name char(20) not null,

			age int not null,

			dep_id int not null,

			test_id int not null,

			constraint fk_dep foreign key(dep_id) references department(id)

			on delete cascade

			on update cascade,

			constraint fk_dep2 foreign key(test_id) references test(id)

			on delete cascade

			on update cascade

		);

		insert into department values

		(1,'IT','IT技术有限部门'),

		(2,'销售部','销售部门'),

		(3,'财务部','花钱太多部门');

		insert into employee values

		(1,'zhangsan',18,1),

		(2,'lisi',19,1),

		(3,'egon',20,2),

		(4,'yuanhao',40,3),

		(5,'alex',18,2);

删除

删除的时候要先删从表，再删主表；

13、表之间的关系

多对一

站在两边分别找能否自己的多对应对面的一

多对多

通过第三张表联系起来

一对一

foreign key 添加unique约束

14、修改表、拷贝表

CREATE TABLE `t2_sys` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` char(30) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\G 查看记录

\c 取消执行

语法：

1. 修改表名

      ALTER TABLE 表名

                          RENAME 新表名;

2. 增加字段

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…],

                          ADD 字段名  数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

3. 删除字段

      ALTER TABLE 表名

                          DROP 字段名;

4. 修改字段

      ALTER TABLE 表名

                          MODIFY  字段名 数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

15、单表查询之简单查询

select

	distinct

	字段1，

	字段2，。。。

from

	表明

where 约束条件

group by 分组条件

having 过滤条件

order by 排序字段

limit n;

distinct

distinct后边的字段的查询结果，结果（记录）去重。

比如查询部门，

select distinct post from emp;

#简单查询

    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id

    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

#避免重复DISTINCT

    SELECT DISTINCT post FROM employee;    

#通过四则运算查询

    SELECT name, salary*12 FROM employee;

    SELECT name, salary*12 AS Annual_salary FROM employee;

    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式

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

   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary

   FROM employee;

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

   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary

   FROM employee;

   结合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;

16、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

17、group by分组

先from，for循环表里的每条记录，然后执行where判断符合条件不，符合条件，执行select，输出select的字段。

分组：就是把记录当中相同的字段归类。

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

是想要：以组为单位进行一些统计操作。分完后就不去在意每一条记录。 每后边的字段就是分组依据，比如每歌部门等。

分组之后只能查到分组字段，或者聚合的结果。不能查具体的每一条结果。

set global sql_mode='strict_trans_tables,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;#按照岗位分组，并查看每个组有多少人

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：

    SELECT COUNT(*) FROM employee;

    SELECT COUNT(*) FROM employee WHERE depart_id=1;

    SELECT MAX(salary) FROM employee;

    SELECT MIN(salary) FROM employee;

    SELECT AVG(salary) FROM employee;

    SELECT SUM(salary) FROM employee;

    SELECT SUM(salary) FROM employee WHERE depart_id=3;

18、having过滤

having 分组后，可以使用聚合函数；

mysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段

因为分组后无法取到具体的某一个值。

ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;

19、order by与limit

默认从小到大，升序  asc

desc 降序

这些都是select的关键字，流程： having运行完运行select后的字段，才运行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 post,avg(salary) from emp group by post order by avg(salart);

limit

limit n 显示前n条

分页：

	select * from emp limit 0,5;

	select * from emp limit 5,5;

	select * from emp limit 10,5;

20、正则匹配

select * from emp where name regexp '正则匹配式'

21、连表操作

select * from emp,dep;

数据是一个笛卡尔积，谁在前边，就是它的第一个数据依次匹配后边表的每一个数据。

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

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

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

左链接：在内连接的基础上保留左表的记录。

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

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

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

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; 

先from找到两张表，做笛卡尔积，再on条件，只取共同部分的内容，left join，左表的内容留下来，后边在where等。

(7)     SELECT

(8)     DISTINCT <select_list>

(1)     FROM <left_table>

(3)     <join_type> JOIN <right_table>

(2)     ON <join_condition>

(4)     WHERE <where_condition>

(5)     GROUP BY <group_by_list>

(6)     HAVING <having_condition>

(9)     ORDER BY <order_by_condition>

(10)    LIMIT <limit_number>

22、子查询

一个字，练。

23、索引原理

读写比例10:1，极少出现写的性能问题，查询来说，才会出现效率低的问题。

优化查询是重点。

索引，主键、unique、index等三种key。mysql提供的一种数据结构，帮你加速查询的。

通过不断的缩小范围，减少查询次数。

b+树性质

1.索引字段要尽量的小

2.索引的最左匹配特性

没有主键，会用一个隐藏字段做索引，不能起到加速查询的效果。

聚集索引： 按照主键指定的字段做索引，叶子节点存放的id对应的一整条完整的记录。

覆盖索引：

在辅助索引中已经有了完整想要的内容。

select name from emp where name='alex';

都要先创建索引。

24、pymysql基本使用

# 输入用户名和密码 如果 有 打印 登陆成功 如果没有登陆失败

import pymysql

user = input('请输入用户名').strip()

pwd = input('请输入密码').strip()

# 连接服务端

conn = pymysql.connect(

    host='127.0.0.1',

    user='root',

    password="",

    database='db10',

    port=3306,

    charset='utf8'

)

# -- ddadad

# 创建游标对象

cur = conn.cursor()

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

print(sql)

resultNum = cur.execute(sql)

print(resultNum)

cur.close()

conn.close()

if resultNum:

    print('登陆成功')

else:

    print('登陆失败')

25、pymysql模块之注入问题

sql语句中--表示注释。

# 输入用户名和密码 如果 有 打印 登陆成功 如果没有登陆失败

import pymysql

user = input('请输入用户名').strip()

pwd = input('请输入密码').strip()

# 连接服务端

conn = pymysql.connect(

    host='127.0.0.1',

    user='root',

    password="",

    database='db10',

    port=3306,

    charset='utf8'

)

# -- ddadad

# 创建游标对象

cur = conn.cursor()

sql = "select * from user where name = %(name)s and password = %(password)s"

print(sql)

# resultNum = cur.execute(sql,[user,pwd])

resultNum = cur.execute(sql,{"name":user,"password":pwd})

print(resultNum)

cur.close()

conn.close()

if resultNum:

    print('登陆成功')

else:

    print('登陆失败')

26、pymysql之增删改查

一定要在close之前做conn.commit()

sql='insert into userinfo(name,password) values(%s,%s);'

res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数

conn.commit() #提交后才发现表中插入记录成功

cursor.close()

conn.close()

查

# 创建游标对象

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

row = cur.fetchone()

print(row)

rows = cur.fetchmany(5)

print(rows)

rows = cur.fetchall()

print(rows)

cur.scroll(1,mode='relative') scroll操作光标移动

27、视图

重用查询结果。

视图就是虚拟表，内存里的表，

select @sql_mode;

视图只是用来查看的。

28、触发器

增删改三种行为绑定一个触发器，没有查

#准备表

CREATE TABLE cmd (

    id INT PRIMARY KEY auto_increment,

    USER CHAR (32),

    priv CHAR (10),

    cmd CHAR (64),

    sub_time datetime, #提交时间

    success enum ('yes', 'no') #0代表执行失败

);

CREATE TABLE errlog (

    id INT PRIMARY KEY auto_increment,

    err_cmd CHAR (64),

    err_time datetime

);

#创建触发器

delimiter //

CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW

BEGIN

    IF NEW.success = 'no' THEN #等值判断只有一个等号

            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号

      END IF ; #必须加分号

END//

delimiter ;

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (

    USER,

    priv,

    cmd,

    sub_time,

    success

)

VALUES

    ('egon','0755','ls -l /etc',NOW(),'yes'),

    ('egon','0755','cat /etc/passwd',NOW(),'no'),

    ('egon','0755','useradd xxx',NOW(),'no'),

    ('egon','0755','ps aux',NOW(),'yes');

#查询错误日志，发现有两条

mysql> select * from errlog;

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

| id | err_cmd         | err_time            |

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

|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |

|  2 | useradd xxx     | 2017-09-14 22:18:48 |

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

rows in set (0.00 sec)

插入后触发触发器

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

29、事物

事物包含一堆sql语句，一个整体，一般跟钱相关，要不同时成功，要不同时失败。

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;

没有commit，就是相当于没有操作。

30、存储过程

存储过程： mysql给程序提供一系列接口，程序直接调用接口用。用基于数据库功能开发，最后封装的就是存储过程，

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

#方式一：

    MySQL：存储过程

    程序：调用存储过程

#方式二：

    MySQL：

    程序：纯SQL语句

#方式三：

    MySQL:

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

存储过程是针对某一个库；

set @res=o; sql语句设置一个变量；

31、函数与流程控制

#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'

#2 准备表和记录

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 |

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

rows in set (0.00 sec)

需要掌握函数：date_format

巴特西

day17_雷神_数据库小全

数据库

1、mysql 介绍

2、mysql 基本管理

3、基本sql语句

4、数值类型

5、库操作

6、存储引擎

7、字符类型

8、日期类型

9、枚举与集合类型

10、上节课复习

11、约束条件

12、外键

13、表之间的关系

14、修改表、拷贝表

15、单表查询之简单查询

16、where关键字

17、group by分组

18、having过滤

19、order by与limit

20、正则匹配

21、连表操作

22、子查询

23、索引原理

24、pymysql基本使用

25、pymysql模块之注入问题

26、pymysql之增删改查

27、视图

28、触发器

29、事物

30、存储过程

31、函数与流程控制

32、navicat工具介绍

最新文章

热门文章

巴特西

day17_雷神_数据库 小全

数据库

1、mysql 介绍

2、mysql 基本管理

3、基本sql语句

4、数值类型

5、库操作

6、存储引擎

7、字符类型

8、日期类型

9、枚举与集合类型

10、上节课复习

11、约束条件

12、外键

13、表之间的关系

14、修改表、拷贝表

15、单表查询之简单查询

16、where关键字

17、group by分组

18、having过滤

19、order by与limit

20、正则匹配

21、连表操作

22、子查询

23、索引原理

24、pymysql基本使用

25、pymysql模块之注入问题

26、pymysql之增删改查

27、视图

28、触发器

29、事物

30、存储过程

31、函数与流程控制

32、navicat工具介绍

最新文章

热门文章

day17_雷神_数据库小全