MySQL 的DDL DML DQL DCL细节解析知道这些就够了

Myth丶恋晨 2024-02-19 17:00 17阅读 0赞

SQL语言一共分为4大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL

### 1.数据定义语言DDL(Data Definition Language) ###

对象： 数据库和表

关键词： create alter drop truncate(删除当前表再新建一个一模一样的表结构)

创建数据库：create database school;

删除数据库：drop database school;

切换数据库：use school;

创建表：

create table student(
    
    　　　　　　id int(4) primary key auto_increment,
    
    　　　　　　name varchar(20),
    
    　　　　　　score int(3)
    
    　　　　);

查看数据库里存在的表：show tables;

注意：

varchar类型的长度是可变的，创建表时指定了最大长度，定义时，其最大值可以取0-65535之间的任意值，但记录在这个范围内，使用多少分配多少，

varchar类型实际占用空间为字符串的实际长度加1。这样，可有效节约系统空间。varchar是mysql的特有的数据类型。

char类型的长度是固定的，在创建表时就指定了，其长度可以是0-255之间的任意值。虽然char占用的空间比较大，但它的处理速度快。

修改表：alter table student rename (to) teacher;
    
    　　　　　　alter table student add password varchar(20);
    
    　　　　　　alter table student change password pwd varchar(20);
    
    　　　　　　alter table student modify pwd int;
    
    　　　　　　alter table student drop pwd;
    
    　　删除表：drop table student;
    
    　　查看生成表的sql语句：show create table student;
    
    　　查看表结构：desc student;

### 2.数据操纵语言DML(Data Manipulation Language) ###

对象：纪录(行)

关键词：insert update delete

插入：insert into student values(01,'tonbby',99); (插入所有的字段)
    
    　　　　　insert into student(id,name) values(01,'tonbby'); (插入指定的字段)
    
    　　更新：update student set name = 'tonbby',score = '99' where id = 01;
    
    　　删除：delete from tonbby where id = 01;

注意：

开发中很少使用delete,删除有物理删除和逻辑删除，其中逻辑删除可以通过给表添加一个字段(isDel)，若值为1，代表删除；若值为0，代表没有删除。

此时，对数据的删除操作就变成了update操作了。

truncate和delete的区别：

truncate是删除表，再重新创建这个表。属于DDL，delete是一条一条删除表中的数据，属于DML。

### 3.数据查询语言DQL(Data Query Language) ###

select … from student where 条件 group by 分组字段 having 条件 order by 排序字段

执行顺序：from->where->group by->having->order by->select

注意：group by 通常和聚合函数(avg(),count()…)一起使用 ,经常先使用group by关键字进行分组，然后再进行集合运算。

group by与having 一起使用，可以限制输出的结果，只有满足条件表达式的结果才会显示。

having和where的区别：

两者起作用的地方不一样，where作用于表或视图，是表和视图的查询条件。having作用于分组后的记录，用于选择满足条件的组。

### 4.数据控制语言DCL(Data Control Language) ###

用户，权限，事务。（改天来补充）

## 细节解析和练习，知道这些就够了 ##

mysql数据库的DDL,DML, DCL, TCL  
SQL语句分类:  
a.DDL 数据定义语言 create drop alter  
b.DML 数据操作语言 select insert update delete  
b.DCL 数据控制语言 grant revoke  
c.TCL 事务控制语言 transaction(begin)  
commit rollback  
select 用户查询语句,主要是从服务端获取数据  
查询语法:

select   查询显示的字段
    from  表名
    where  查询条件

select *  //显示表的所有字段
    select name from t1;  ti表中查询 名字
    select id,name from t1;
    select  * from person;
    字段别名
    select  pname a,sex b,age from person;
    select  pname as a,sex as b,age as c  from person;
    表别名
    select p.pname as name,p.age as age,p.tel as tel from person p
    select person.pname as name,person.age as age,person.tel as tel from person
    条件
    select * from t1 where 条件字段  比较符号  值
    select * from t1 where id=10;
    select * from t1 where id<>10;
    select * from t1 where id>=5;
    select * from t1 where id<=10;
    select * from t1 where name>='tt';
    select * from t1 where 1=2;
    多个条件
    a.and 和 所有条件都满足
    b.or  或 只需要满足其中一个条件
    select * from t1 where id>=5 and  name='tt11' and 1=1;
    select * from t1 where id>=5 or name='tt11';
    and>or
    select * from t1 where id>=5 or name='tt11' and name>='tt';
    select * from t1 where (id>=5 or name='tt11') and name>='tt';
    条件语句中使用的函数
    in/not in 字段值是否在集合中存在或不存在
    select * from t1 where name in ('tt','tt11','t1','t2'); in表示名字当中含有的
    select * from t1 where name not in ('tt','tt11','t1','t2');
    select * from student where cid in (select cid  from class);
    select * from student where cid in (select * from class);//错误
    between  and  
    select * from student where cid>=1 and cid<=5;
    select *  from student where cid between 1 and 5;
    is null  /is  not null
    select * from student where sid is not null;
    select * from student where sid is  null;
    like 模糊查找 这里主要注意符号配合使用
    通配符号 :%
    select * from student where score like '7%'
    select * from student where score like '%9'
    select * from student where score like '%9%'
    limit 
    limit n,m //查询输出第n-1笔到n-1+m(总共查询m笔数据)
    select * from student limit 0,2;
    select * from student limit 3,3;  查询第2行到5行的数据
    order by 排序 加 根据什么排序
    排序顺序:升序 asc(默认为升序)默认为升序列化
             降序 desc
    select * from student order by score asc
    select * from student order by score asc
    select * from student order by score desc;
    select * from student order by score desc,sname asc;
    使用在查询显示字段函数
    使用运算符号
    select 3/2
    select sid,sname,score-30 score,cid from student;
    distinct 去掉重复记录
    select distinct score from student;
    select distinct cid from student;
    根据distinct后面所有字段判断记录是否重复
    select distinct score,sname from student
    聚合函数(对多个查询记录处理后只返回一个值)
    a.sum  求和
    b.avg  求平均
    c.max  最大值                 带（加入去求项）
    d.min  最小值
    e.count  记录数
    select sum(score) score,max(score)mscore,min(score) mmscore,avg(score) ascore,count(*) c from student;
    select count(sid) from student;//不统计sid is null 的记录
    select count(*) from student;
    分组统计
    group by   通过相同的项合并分组
    select  sum(score)
    from student
    group by cid
    select sum(score) s1,max(score) s2,min(score) s3,avg(score) s4 from student group by cid;
    聚合函数一般出现在后面,前面可以显示其它字段,一般显示子段与分组字段一样
    select cid, sum(score) s1,max(score) s2,min(score) s3,avg(score) s4 from student group by cid;
    select cid,sname,sum(score) s1,max(score) s2,min(score) s3,avg(score) s4 from student group by cid,sname;
    select cid,sname,sum(score) s1,max(score) s2,min(score) s3,avg(score) s4 from student group by cid;//错误
    分组之后条件筛选
    group by 
    having  条件
    select cid, sum(score) s1,max(score) s2,min(score) s3,avg(score) s4 from student group by cid having s4>=80;
    case when  then  else  end
    根据字段值,增加显示字段
    select case when 1=2 then '=' else '<>' end a;
    练习:
    1.有如下一张表
      sid  sname  object  score
      001   张三   语文    90
      001   张三   数学    80
      001   张三   英语    78
      002   李四   语文    91
      002   李四   数学    87
      002   李四   英语    72
    写以sql执行的结果如下:
      sid    sname   语文    数学  英语
      001     张三    90      80    78
      002     李四    91      87    72
      select sid,sname,
             max(case when object='yuwen' then score else 0 end) yuwen,
    	 max(case when object='shuxue' then
    	 score else 0 end) shuxue,
    	 sum(case when object='yingyu' then
    	 score else 0 end) yingyu
       from student
       group by sid,sname
     
     合并查询结果(每个查询语句显示的字段数一样对应字段类型一样)
     union/union all
     union:去掉重复记录,并按照第一显示字段升序排序
     select * from student
     union 
     select * from student;
     union all:合并
     select * from student
     union all
     select * from student;
    多个表关联查询
    a.内链接(全链接)
       select s.*,c.cname from student s inner join class c on s.cid=c.cid
      
    b.左链接
       select s.*,c.cname from student s left join class c on s.cid=c.cid;
       特点:首先将left左边的表所有数据查询输出,再根据关联字段到left右边的表查找对应记录,如果不存在用NULL输出
       select s.*,c.cname from class c right join student s on s.cid=c.cid;
    c.右链接
       select s.*,c.cname from student s right join class c on s.cid=c.cid;
       特点:首先将right右边的表所有数据查询输出,再根据关联字段到right左边的表查找对应记录,如果不存在用NULL输出
       select s.*,c.cname from class c left join  student s on s.cid=c.cid;
    子查询
    在查询语句中存在另一个查询语句
    a.子查询出现在显示字段
       select  a,(select b from t1) b
       from t2
       select  *,(select 1) a  from student;
    b.子查询出现在条件中
       select * from student where cid in (select cid  from class);
    练习:
    1.存在如下数据
      pid    pname   price
      1      电视机  1000
      3      冰箱    3000
      4      洗衣机  4000
      6      电脑    6000
     写以sql执行结果如下:
      pid    pname   price
      1      电视机  1000
      3      冰箱    4000
      4      洗衣机  8000
      6      电脑    14000
      select pid,pname,
             (select sum(price) from product a where a.pid<=b.pid )price
      from product b
    insert 把数据插入到表中
    语法:
    insert into 表名[(字段名)]
    values(值1,值2,....)
    注意:
    1.如果是给表的所有字段插入数据,字段名不用写
    insert into student values(17,'s10',98,7);
    insert into student values(18,'s18',null,8);
    insert into student values(19,'s19','',9)
    2.如果是给表的部分字段插入数据,必须列出赋值的字段
    insert into student(sid,sname,cid) values(20,'s20',10)
    3.字段赋值法一次只能插入一笔数据
    查询一个表的数据插入到另一个表中
     insert into student2 
     select  sid,sname,cid  from student;
     注意:
     1.目标表接收数据字段要与源表数据字段一致(数据类型 顺序匹配 )
    update 修改表的数据
     语法
    update 表名
    set 字段=新值,字段=新值,....
    [where 条件]
    update student2 set sname='ss1',cid=20;
    //一般主键字段不能修改
    update student2 set sname='ss11',cid=20  where sid in(10,12,14,16,18,20);
    //修改时,要注意条件
    delete 删除数据
    语法
    delete from 表名
    [where 条件]
    delete from student2;//删除表所有数据
    delete  from student where score is null;
    //删除数据时,注意条件\
    DCL  
    grant  授权
    revoke 回收权
    常用的权限:
    select  insert update delete
    create  drop   alter  grant  revoke
    all
    授权语法:
    grant select ,insert,update  privileges
    on 数据.表名 to  用户@服务器
    grant  all privileges  on *.* to user@localhost
    回收权限
    revoke  all  on  *.*  from user@localhost
    mysql增加用户:
    a.增加操作系统用户
      1.[root@localhost ~]# useradd t2
      2.授权并且设置t2用户密码
       mysql>grant  all privileges on *.*  to t2@localhost identified by '123456'
     用户信息及其相关权限都是存储在mysql数据中的user表中
    b.直接插入数据到user表中
      1.mysql> insert into user(host,user,password) values('localhost','user',password('123456'))
      2.mysql> flush privileges;//刷新用户表
      3.授权
        grant select ,insert,update,delete,create,drop,alter  on *.* to t3@localhost
        回收权限
        revoke select ,insert,update,delete,create,drop,alter  from *.* to t3@localhost
    修改用户密码 
    1.mysql> update user set password=password('123456') where user='root' and host='localhost';
    2.flush privileges
       
    删除用户
    1.use mysql
    2.mysql> delete from user where user in ('t3','t2');
    mysql数据库备份还原
    备份:
    a.备份指定的数据库
      mysqldump  //备份命令
      [root@localhost home]# mysqldump -h localhost -u root -p  lkd(数据库名) > lkd.sql(成功备份生成文件名)
    b.备份指定数据库指定的表
      [root@localhost home]# mysqldump -h localhost -r root -p lkd(数据库名) student(表名) student2(表名) >lkd2.sql(成功备份生成文件名)
    还原数据
    a. 1.删除已经存在数据库
         mysql> drop database lkd;
       2.创建一个同名的空数据库
         mysql> create database lkd;
       3.[root@localhost home]# mysql -h localhost -u root -p lkd(指定数据库名) < lkd.sql
    TCL 事务控制语言
    事务是保证数据操作安全,当意外事件发生时,保证数据操作结果一致
    事务 + 锁 =数据操作安全
    开启一个事务 自动加锁
    结束一个事务 自动解锁
    事务四个特点:
    一致性
    持久性
    隔离性
    完整性
    mysql  数据引擎
    查看数据引擎
    show  engines
    修改数据引擎
    alter table 表名 type='InooDB'
    事务使用步骤:
    1.开启一个事务 begin
    2.数据库操作步骤
    3.提交 commit  回滚 rollback 结束事务
    begin
    delete from student2 where sid='3';
    select * from student2;
    rollback;
    select * from student2;
    begin
    delete from student2 where sid='3';
    select * from student2;
    commit;
    select * from student2;
    保存点 savepoing
    只能是先回滚到保存点,最后在提交,不能直接提交到保存点
    savepoint  p1 ;//设置保存点
    rollback to p1 ;//回滚保存点
    commit;//提交剩下修改
    begin
    delete  from student2 where sid=1;//成功
    savepoint p1;
    update student2 set sname='sss' where sid=4;//失败
    rollback  to  p1;//取消该保存点之后的所有修改的数据
    commit; //不能写成 commit  to p1
    索引
    表索引:提高查询的效率(大批量数据),类似于书的目录
    索引是基于字段
    一个表中可以有多个索引,任何一个字段或多个字段都可以成为索引字段
    创建索引语法:
    create index  索引名 on 表名(字段,字段)
    创建主键字段时自动创建一个主键索引
    索引分类:
    1.主键索引
    2.函数索引
    3.组合索引
    4.位图索引
    5.唯一索引 ()
    create unique index  索引名 on 表名(字段)
    视图
    视图是一个虚表,是一段存储在服务器上的查询语句
    视图作用:
    1.共享sql语句
    2.隐藏表真实信息(表名 表字段)
    3.提高查询效率,降低服务器的负荷
    视图创建语法:
    create view  view_name 
    as
    select * from 表名
    视图不能存储数据,所有的数据来自于基表(视图中查询的表名)
    create view  student2_v1 as  select *  from student2;
    select  *  from student2_v1
    create view student_v1 as select s.*,c.cname from student s inner join class c on s.cid=c.cid;
    select * from  student_v1;
    create view student_v2 as select sum(score)s1,avg(score)s2,max(score)s3,min(score)s4 from student;
    select * from student_v2;
    通过视图修改基表的数据:
    1.视图中中使用聚合函数 分组 都不能利用驶入修改基表数据
    2.一般视图可以修改基表的数据
     update student2_v1 set sname='aaa' where sid=3;
     update student_v1 set sname='aaa' where id='10';
     update student_v2 set sname='aaa' where id='10';//student_v2 包含聚合函数
    PL/SQL :procedure language / struct query language 过程/结构化查询语句
    a.存储过程
    b.函数
    函数
    保存在服务器上一段sql语句,可以传参数 ,可以返回 可以调用
    函数作用:
    1.简化sql语句
    2.共享代码
    3.减少网络流量
    函数创建语法:
    create function 函数名([参数名 参数数据类型,参数名 参数数据类型])
    returns 返回值类型
    begin
       //函数体语句
       reutrn value;
    end
    过程语句中数据类型:
    整数:int integer
    浮点数:float  numeric
    字符:char varchar text
    日期:date  datetime
    定义变量
     declare 变量名  变量数据类型 default 0;
    变量赋值
     a.set 变量名=值;
     b.
    修改mysql语句结束符号:
    delimiter $$
    create function fun1 ()
    returns varchar(10)
    begin
      declare vname varchar(10);
      set vname='abcef';
      return vname;
    end;
    调用函数:
    select fun1()$$
    注意:
    1.函数中不能出现查询语句(select * from tablename)
    create function fun2 ()
    returns varchar(10)
    begin
      declare vname varchar(10);
      select sname into vname from student2
      where sid=3;
      return vname;
    end;
    create function fun3 ()
    returns int
    begin
      declare vname varchar(10);
      declare vid  int;
      declare vcid int;
      declare vdid int;
      select sname,sid,cid,did 
      into vname,vid,vcid,vdid from student2
      where sid=3;
      return vcid;
    end;
    变量默认值
    create function fun4()
    returns int
    begin
      declare vage int default 0;
      return vage;
    end;
    变量名注意:
    1.不能与关键字同名
    2.v+字段名(variant)为保存查询字段值的变量
    语句结构
    a.顺序
    b.选择
    c.循环
    选择
    if  条件 then
       //执行语句
    else
       //执行语句
    end if;
    if  条件 then
       //执行语句
    elseif  条件 then
       //执行语句
    else
       //执行语句
    end if;
    条件:比较符号(> < >= <= = != <>)
         判断为空  is null is not null
    create function fun5()
    returns int
    begin
      declare i int default 0;
      set i=10;
      if i<10 then
         return 5;
      else
         return 10;
      end if;
    end;
    create function fun6()
    returns int
    begin
      declare i int default 0;
      declare ret int;
      set i=10;
      if i<10 then
        set ret=5;
      elseif i=10 then
         set ret= 10;
      else
        set ret= 15;
      end if;
      return ret;
    end;
    create function fun8()
    returns int
    begin
      declare i int default 0;
      declare ret int;
      set i=10;
      if (i>=0 and  i<10) then
        set ret=5;
      elseif i=10 then
         set ret= 10;
      else
        set ret= 15;
      end if;
      return ret;
    end;
    case when  then  else end case;
    create function fun9()
    returns int
    begin
      declare i int default 0;
      declare ret int;
      case  i
        when 0  then set ret=0;
        when 1  then set ret=1;
        when 2  then set ret=2;
        else   set ret=3;
      end case;
      return ret;
    end;
    create function fun10()
    returns int
    begin
      declare i int default 2;
      declare ret int;
      case  
        when i=0  then set ret=0;
        when i=1  then set ret=1;
        when i=2  then set ret=2;
        else   set ret=3;
      end case;
      return ret;
    end;
    create function fun11()
    returns int
    begin
      declare vsid int;
      declare vsname varchar(20);
      declare vscore int;
      declare vcid  int;
      declare vdid  int;
      select * into vsid,vsname,vscore,vcid
      from student
      where sid=15;
      if vscore>=60 then
         set vdid=23;
      else
         set vdid=13;
      end if;
      insert into student2 values(vsid,vsname,vcid,vdid);
      return 1;
    end;
    循环
    a. 强制循环 loop
        flags:loop
          //循环体语句
          if 条件 then
             leave flags;//退出循环
          end if
        end loop;
    b. while  do
        //循环体语句
       end while;
    c  repeat  
        //循环体语句
        nutil  条件 //满足条件退出循环
    create function fun12()
    returns int
    begin
      declare vsum int default 0;
      declare i int default 1;
      s1:loop
          set vsum=vsum+i;
          set i=i+1;
          if i=100 then
             leave s1;//退出循环
          end if;
      end loop;
      return vsum;
    end;
    create function fun13()
    returns int
    begin
      declare vsum int default 0;
      declare i int default 1;
      while i<=100 do
          set vsum=vsum+i;
          set i=i+1;     
      end while;
      return vsum;
    end;
    create function fun14()
    returns int
    begin
      declare vsum int default 0;
      declare i int default 1;
      repeat
          set vsum=vsum+i;
          set i=i+1;     
      until i>100
      end repeat;
      return vsum;
    end;
    系统常用函数
    a.数字函数
      select abs(-123);
      select ceiling(34.567)
      select floor(34.567)
      select round(34.567,2)
      select round(34.563)
      select round(34.563,-2)
      select round(64.563,-2)
      select truncate(34.567,2)
      select truncate(34.563,0)
      select truncate(164.563,-2)
      select rand()
    b.字符串
       select ascii('a')
       select char(97)
       select upper('abcdef')   
       select lower('ABCDEF')
       select left('abcdefghij',4)
       select right('abcdefghij',4)$$
       select length('aaaaaa');
       select char_length('aaaaaa');
       select lpad('abcd',10,'*')
       select rpad('abcd',10,'*')
       select ltrim('  abcd   ')a;
       select rtirm('  abcd    ')a;
       select trim('      abcde     ')a
       select instr('abcdefghijk','cd')
       select instr('abcdefghijk','123')
       select locate('cd','abcdefgh')
       select replace('abcdefghicdjk','cd','123')
       select substring('abcdefghijk',2,3)
       select concat('1234','abcde')
       select insert('abcdef',2,4,'123456')
       select repeat('abcd',3)
       select reverse('abcdef')
    c.日期时间
       select sysdate() 
       select now()
       select curdate()
       select curtime()
       select date_format(now(),'%Y-%m-%d')
       select dayofweek(now())
       select dayofyear(now())
       select dayname(now())
       select montname(now())
       select year(now())
       select month(now())
       select day(now())
       select adddate(now(),40)
    d.其它函数
    select greatest('a','b','c');
    select if(1>2,'a','d')
    select ifnull(10,'2'); 
    带有参数函数
    语法:
    create function 函数名(参数列表)
    returns int
    begin
    end;
    参数列表://参数名 参数数据类型,参数名 参数数据类型
    调用
    select 函数名(实参值,实参值);
    create function  fun(str varchar(20))
    returns varchar(20)
    begin
       return str;
    end;
    create function  fun1(str varchar(20),str1 varchar(20))
    returns varchar(20)
    begin
       return concat(str,str1);
    end;
    create function  fun18(vsid int)
    returns varchar(20)
    begin
       declare vsname varchar(20);
       select sname into vsname 
       from student
       where sid=vsid;
       return vsname;
    end;
    练习:
    1.实现系统函数replace
      select replace('abcdefg','cd','1234')
      select replace('abcdefgcd','cd','1234')
      create function  rep(sstr varchar(20),substr varchar(10),newstr varchar(10))
    returns varchar(20)
    begin
       declare i int default 0;
       declare len int;
       declare ret varchar(20) ;
       set ret=sstr;
       set i=instr(sstr,substr);
       while i>0 do
          set len=length(substr);
          set ret=insert(ret,i,len,newstr);
          set i=instr(ret,substr);
       end while;
      return ret;  
    end;
    存储过程
    存储过程把实现某个过程的所有sql语句保存在服务器上,不需要任何返回
    存储过程语法:
    create procedure  存储过程名([参数列表])
    begin
    end;
    注意:
    1.存储过程中可以使用select
    2.存储过程不需要返回值
    create procedure pro1()
    begin
       select * from student;
    end;
    调用存储过程
    call 存过过程名()$$
    select在存储过程中可以当做输出函数使用
    create procedure pro2()
    begin
       declare  ret int;
       set ret=100;
       select ret;
    end;
    create procedure pro3()
    begin
       declare vsid int;
       declare vsname varchar(20);
       select sid,sname into vsid,vsname
       from student
       where sid=15;
       select vsid,vsname;
    end;
    带参数存储过程
    create procedure pro4(vsid  int)
    begin
       declare vcid int;
       declare vsname varchar(20);
       select cid,sname into vcid,vsname
       from student
       where sid=vsid;
       select vcid,vsname;
    end;
    call pro4(15)
    存储过程中调用另一个存储过程
    create procedure pro5(vsid  int)
    begin
         call pro4(vsid);
    end;
    存储过程参数模式
    a.输入模式(in),默认为该模式
    b.输出模式(out)
    c.输入输出模式(inout)
    create procedure pro6(in vsid  int,out vcid int,out vsname varchar(20))
    begin
       select cid,sname into vcid,vsname
       from student
       where sid=vsid;
       select vcid,vsname;
    end;
    create procedure pro7(vsid  int)
    begin
         declare vcid int;
         declare vsname varchar(20);
         call pro6(vsid,vcid,vsname);
    end;
    create procedure pro8(inout vsid  int,out vsname varchar(20))
    begin
       select cid,sname into vsid,vsname//out vsid
       from student
       where sid=vsid;//in vsid
       select vsid,vsname;
    end;
    create procedure pro9(vsid  int)
    begin
         declare vsname varchar(20);
         call pro8(vsid,vsname);
    end;
    函数不支持out inout模式
    存储过程与函数区别?
    1.函数必须又返回值,存储过程不需要
    2.函数不能使用out inout模式,存储过程可以
    3.函数调用使用select ,存储过程调用使用call
    触发器
    触发器是一个特殊存储过程,当满足执行的条件,自动触发执行
    当满足执行的条件:客户端操作的语句类型
    a.insert
    b.update
    c.delete
    触发时刻:
    a.before
    b.after
    触发器语法:
    create  trigger  触发器名
    after|before    insert|update|delete on 
    表名
    for  each row//行级触发
    begin
    end;
    触发器分类:
    a.语句触发(无法获取数据)
    b.行级触发(获取数据)
    获取数据:
    a.insert  : new.字段名
    b.update  : new.字段名  old.字段名
    c.delete  : old.字段名
    触发器注意:
    一个表同一个事件(insert update delete)只能创建一个触发器
    create trigger  tri1
    after insert on A
    for each row
    begin
       insert  into B values(new.id,new.name);
    end;
    insert into A values(1,'a1')
    select * from B
    create trigger  tri2
    after update on A
    for each row
    begin
       update B
       set name=new.name
       where id=old.id;
    end;
    update A set name='12345' where id=1
    select * from B
    create trigger  tri3
    after delete on A
    for each row
    begin
       delete from B
       where id=old.id;
    end;
    delete from A where id=1
    select * from B
    日志记录触发器
    create table log
    (
      lid int auto_increment primary key,
      opttime date,
      opetype varchar(10),
      id int,
      use varchar(20)
    ) 
    create trigger tri4
    before insert on A
    for each row
    begin
       insert into log(opttime,opetype,id,user)
       values(now(),'insert',new.id,user());
    end;
    游标
    是一种变量,指向所有查询结果集,类似于数组或指针,不支持随机查找
    游标定义
    declare 游标名  cursor for  select * from 表名 where//只能是查询语句
    游标的使用:
    1.打开游标执行查询语句  open 游标名
    2.loop 
    3.取数据  fetch 游标名  into 变量
    //根据游标的查询显示字段确定变量的个数与数据类型
    4.判断游标数据是否取完
    5.对数据处理
    6.关闭游标 close 游标名
    如何判断游标中的数据取完?
    1.定义状态变量
      declare flag int default 0;
    2.根据游标异常处理改变状态变量值
      declare continue handler for not fonund set flag=1;
    create procedure pro12()
    begin
    declare flag int default 0;
    declare vsid int;
    declare vsname varchar(10);
    declare vscore int;
    declare vcid int;
    declare s_cur cursor for select * from student;
    declare continue handler for not found set  flag=1;
    open s_cur;
    s1:loop
      fetch s_cur into vsid,vsname,vscore,vcid;
      if flag =1 then
        leave s1;
      end if;
      insert into student2 values(vsid,vsname,vcid,vscore);
    end loop;
    close s_cur;
    end;
    查看函数或存储过程源码
    show  create  procedure  存储过程
    show  create  function   函数名
    show create trigger 触发器名
    查看函数或存储过程名
    show  procedure  status
    show  function  status
    删除存储过程或函数或触发器
    drop procedure 存储过程
    drop function  函数名
    drop trigger   触发器名
    mysql linux 接口函数
    安装开发包
    mysql-connector-c-6.0.2-linux-glibc2.3-x86-32bit.tar.gz
    安装步骤:
    1.tar -xvzf mysql-connector-c-6.0.2-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local
    2.
    mv mysql-connector-c-6.0.2-linux-glibc2.3-x86-32bit/ mysql
    数据库类型
    MYSQL   mysql数据库类型
    数据库连接句柄初始化
    MYSQL * mysql_init(MYSQL *mysql)
    MYSQL *mysql: NULL :分配空间并且初始化
                  NOT NULL:仅仅初始化
    返回值:成功 返回数据库连接句柄
           失败 NULL
    连接数据库函数
    MYSQL* mysql_real_connect(MYSQL* mysql,
                              char* host,
    			  char *username,
    			  char *password,
    			  char * db,
    			  int port,
    			  char * socket,
    			  int client_flags)
    MYSQL* mysql:数据库连接句柄(mysql_init返回值)
    char* host:服务器名
    char *username:用户名
    char *password:用户密码(NULL | "")
    char * db:服务的数据库名
    int port:服务器监听端口号(3306 | 0)
    char * socket:套接字类型一般为NULL
    int client_flags:连接服务器的状态设置一般为0(默认值)
    返回值:NULL:连接数据失败
    返回连接数据错误提示函数
    char* mysql_error(MYSQL* mysql)
    返回错误提示字符串
    关闭数据库连接(释放数据库连接句柄)
    void  mysql_close(MYSQL * mysql)
    #include <stdio.h>
    #include <mysql.h>
    int main(int argc, char *argv[])
    {
        
    	MYSQL *mysql=NULL;
    	mysql=mysql_init(NULL);
    	if(mysql_real_connect(mysql,"localhost","root","123456","lkd",0,NUL,0)==NULL)
    	{
        
    		printf("%s\n",mysql_error(mysql));
    		return;
    	}
    	printf("connect ok!\n");
    	mysql_close(mysql);	
    	return 0;
    }
    如何编译?
    1.gcc -I /usr/local/mysql/include -L /usr/local/mysql/lib -lmysqlclient mysql1.c
    执行文件 可能出现错误:
    1.Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
     解决:
     a.mysql服务是否启动  servie mysqld start
     b.符号链接:ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
    2.error while loading shared libraries: libmysql.so.16: cannot open shared object file: No such file or directory
      解决办法:
      修改环境变量:
      export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib
    简单编译方法:
    1. cp  /usr/local/mysql/include/* /usr/include //系统默认搜索头文件目录
    2. cp /usr/local/mysql/lib/* /usr/lib//系统默认搜索库文件目录
    3.gcc -lmysqlclient   mysql1.c
    执行sql语句函数
    int mysql_query(MYSQL* mysql,char * sql)
    MYSQL* mysql:链接数据库句柄
    char * sql:执行的sql语句(非查询语句)
    返回值  :0 失败
             >0 成功
    执行查询语句
    查询结果集
    MYSQL_RES
    查询结果集中每一行
    MYSQL_ROW
    将查询语句结果保存在内存
    MYSQL_RES* mysql_store_result(MYSQL* mysql)
    查询结果集的行数
    int mysql_num_rows(MYSQL_RES *res)
    查询结果集的列数
    int mysql_num_fields(MYSQL_RES* res)
    取出数据
    MYSQL_ROW mysql_fetch_row(MYSQL_RES* res)
    释放存放查询结果集的内存
    void mysq_free_result(MYSQL_RES* res)
    预处理机制
    直接执行与预处理执行区别?
    select * from a where id=2;
    select * from a where id=?;
    预处理语句
    MYSQL_STMT
    参数结构类型
    MYSQL_BIND
    参数方式:
    1.输入参数(客户端传递给服务端)
    2.输出参数(服务端输出给客户端)
    预处理使用步骤
    a.初始化预处理语句句柄
      MYSQL_STMT * mysql_stmt_init(MYSQL* mysql)
    b.指定预处理语句
      int mysql_stmt_prepare(MYSQL_STMT* st,char * sql,int len)
      char * sql:预处理sql语句类似于:
      select * from a where id=?
      insert int a values(?,?);
      int len:strlen(sql);
    c.输入参数初始化
      根据"?"确定参数的个数
      定义参数数组
      MYSQL_BIND b[N];
      针对每个参数初始化:
      b[0].buffer_type:MYSQL_TYPE_INT,MYSQL_TYPE_LONG,MYSQL_TYPE_STRING
      b[0].buffer=值
      如果参数类型为MYSQL_TYPE_STRING
      b[0].buffer_length=strlen(值)
      将参数与预处理语句绑定
      my_bool mysql_stmr_bind_parm(MYSQL_STMT* st,MYSQL_BIND* bind)
      执行
      int mysql_stmt_execute(MYSQL_STMT* st)
      释放
      void mysql_stmt_close(MYSQL_STMT* st)
    输出入参数处理
      根据要输出字段确定参数的个数
      定义参数数组
      MYSQL_BIND b[N];
      针对每个参数初始化:
      b[0].buffer_type:MYSQL_TYPE_INT,MYSQL_TYPE_LONG,MYSQL_TYPE_STRING
      b[0].buffer=值
      如果参数类型为MYSQL_TYPE_STRING
      b[0].buffer_length=strlen(值)
      将参数与预处理语句绑定
     
      绑定预处理与与参数
      mysql_bool  mysql_stmt_bind_result(MYSQL_RES* st,MYSQL_BIND * bind)
      执行
      int mysql_stmt_execute(MYSQL_STMT* st)
      保存结果
      int mysql_stmt_store_result(MYSQL_STMT * st)
      获取数据
      int mysql_stmt_fetch(MYSQL_STMT * st)
      通过输出变量输出结果
      释放
      void mysql_stmt_close(MYSQL_STMT* st)
    作业:
    1.利用c++ 容器 封装一个mysql数据库操作类
    sqlite
    sqlite 是一个本地存储数据库软件
    文件小存储数据量较大
    sqlite3
    sqlite安装:
    1.tar -xvzf sqlite-3.3.7.tar.gz -C /usr/local
    2. mv sqlite-3.3.7/ sqlite
    3../configure --prefix=/usr/local/sqlite
    4.make
    5.make install
    启动sqlite3
    a.sqlite3  
    b.sqlite3 数据库名 (一个文件标示一个数据库)
    sqlite常用的命令
    1. .database  查看首路径
    2. .tables    查看数据库中存在的表名
    3. .schema    输出所有表的创建语句
       .schema 表名  输出指定表的创建语句
     
    4.  .show  显示对sqlite输出格式控制变量值
    5.  .header on 显示字段名
    6.  .separator "-" 设置分割符
    7.   .output filename 输出到文件中
         .output stdout  输出到屏幕
    8.  .mode  输出模式
            .mode list 
    	.mode csv
    	.mode column (.width n  设置间距)
    	.mode insert
    	.mode line
    	.mode tcl
    	.mode tabs
    将数据库导出到文件脚本
    [root@localhost home] sqlite3 sqlite1.db '.dump' > sqlite1.sql
    将脚本还原成数据库
    [root@localhost home]sqlite3 sqlite2.db < sqlite1.sql
    将上面的两句话合成一句话写
    sqlite3 sqlite2.db '.dump' | sqlite3 sqlite21.db
    将文本中数据一次性导入到数据库中表
    1.创建存放数据的文件
      文件中的数据有一定规律,值与值之间用指定符号分割
    2.sqlite3 sqlite2.db
      .separator '分割符'
      .import  文件名   表名
    linux终端:
    sqlite3  sqlite2.db  'select * from student';
    sqlite 字段数据类型
    sqlite是一个轻量级数据库,对数据类型为弱类型
    create table person(pid,pname,page)
    select typeof(pid),typeof(pname),typeof(page) from person;
    常见的数据类型:
    1.null  空类型
    2.int integer float  
    3.char  varchar text
    4.date datetime
    5.blob
    创建表示字段你的约束
    1.primary key
    2.foreign key
    3.is not null /is null
    4.default
    5.check 
    6.unique
    create table class(cid integer primary key,sname text,num integer check(num>=10 and num<=20));
    sqlite 自动增长字段
    字段为整数类型是主键
    insert into class(sname,num) values('ss',18);
    常用的函数
    abs
    取绝对值
    select abs(-1)
    select max(pid) from tt2;
    select min(pid) from tt2;
    select avg(pid) from tt2;
    select sum(pid) from tt2;
    select count(*)  from tt2;
    select random();
    select round(123.456,2);
    select length('aaaa');
    select substr('abcdefg',2,3);
    select lower('SSSSS');
    select  upper('aaaa');
    select  typeof(id),typeof(name),typeof(pid) from tt2;
    select typeof(1);
    select last_insert_rowid()
    select  sqlite_version();
    时间函数
    select date('now') --获取当前的日期
    select date('2011-07-09');
    select time('now','localtime');--获取本地当前时间
    select datetime('now','localtime');
    select strftime('%Y','now','localtime');--年
    select strftime('%m','now','localtime');--月
    select strftime('%d','now','localtime');--日
    select strftime('%H','now','localtime');--小时
    select strftime('%M','now','localtime');--分钟
    select strftime('%S','now','localtime');--秒
    select julianday('now')-julianday('2001-06-09');