Mysql入门使用（慢慢探索）

2019独角兽企业重金招聘Python工程师标准>>>

Mysql 通过source命令可以直接运行*.sql的脚本，在命令行下不需要在进行复制粘贴什么的了：

mysql>source D:\XX*.sql

 

 

学了一段时间数据库和JSP了，

决定用linux+tomcat+JSP+MYSQL给自己做一个记账本，只为把这些主流功能熟悉，最终目标是较好的实现；

其中JSP一定要使用SSH框架，以降低耦合度，为以后扩展做准备；

同时熟悉MYSQL基本的操作，包括存储过程、触发器和视图的使用；

 

首先先设计数据库：

在shell下，进入mysql只需输入>set NAMES utf8;

即可改变编码格式，以输入中文

create database accounts character set 'utf8';

 

 

    create table accounts.records(

        id INT(10) not null auto_increment,

       date INT(10) not null,

       month INT(10) not null,

       week INT(10) not null,

       income FLOAT(20) not null,

       expend FLOAT(20) not null,

       remark VARCHAR(255),

        primary key (id)

    );

 

 

    create table accounts.weeks(

        id INT(10) not null,

       income FLOAT(20) not null,

       expend FLOAT(20) not null,

        primary key (id)

    );

 

create table accounts.months(

        id INT(10) not null,

       income FLOAT(20) not null,

       expend FLOAT(20) not null,

        primary key (id)

    )

 

create table accounts.days(

        id INT(10) not null,

       income FLOAT(20) not null,

       expend FLOAT(20) not null,

        primary key (id)

    )

 

以下单个条数均成功：

create trigger t1 after insert on records for each row update weeks set weeks.income=weeks.income+NEW.income,weeks.expend=weeks.expend+NEW.expend where weeks.id=NEW.week;

 

create trigger t2 after insert on records for each row update months set months.income=months.income+NEW.income,months.expend=months.expend+NEW.expend where months.id=NEW.month;

 

但不能在一个动作下加两个触发器动作：

参照教材改为：

create trigger t1 after insert on records for each row

begin

update weeks set weeks.income=weeks.income+NEW.income,weeks.expend=weeks.expend+NEW.expend where weeks.id=NEW.week;

update months set months.income=months.income+NEW.income,months.expend=months.expend+NEW.expend where months.id=NEW.month;

end

应该是没错的  可是还是失败；

 

使用select week as 周,sum(income) as 周收入,sum(expend) as 周支出  from records group by week;语句可以完美得出周汇总

同理select month as 月,sum(income) as 月收入,sum(expend) as 月支出  from records group by month;也可以得出月汇总

 

因而决定采用VIEW（视图的形式）

CREATE VIEW accounts.week

AS

select week as 周,sum(income) as 周收入,sum(expend) as 周支出  from records group by week;

 

CREATE VIEW accounts.month

AS

select month as 月,sum(income) as 月收入,sum(expend) as 月支出  from records group by month;

 

CREATE VIEW accounts.day

AS

select month as 日期,sum(income) as 日收入,sum(expend) as 日支出  from records group by date;

 

 

在相应的使用中  只需将视图作为一个单独的table即可

即：select * from accounts.week;

   select * from accounts.month;

   select * from accounts.day;

 

 

mysql中(视图有所不同)

看数据库>show databases;

看表 >show tables;

看触发器 >show triggers;

看视图 >show table status where comment='view'; 

转载于:https://my.oschina.net/Chanthon/blog/53173