mysql最常用法

一.数据库  
1.创建数据库：
    create database Student
    on
    (
     name=Student_Data,
     filename='f:\data\student_Data.mdf',
     size=10,
     maxsize=20,
     filegrowth=5
    )
    log on
    (
     name=Student_Log,
     filename='f:\data\student_Log.ldf',
     size=10,
     maxsize=20,
     filegrowth=5
    )
2.修改数据库：
   1>添加数据文件：
         alter database student
         add file
         (
         name=Student_Data2,
         filename='f:\data\student_Data2.ndf',
         size=10,
         maxsize=20,
         filegrowth=5
         )
   2>修改数据文件：
         alter database student
         modify file
         (
         name=Student_Data,
         size=15
         )
3.删除数据库:
         drop database student
4.设置数据库选项:
         sp_dboption student,'single user',true
5.修改数据库名:
         sp_renamedb 'student','students'
6.查看服务器上的数据库：
         sp_databases
7.查看数据库上的文件:
         sp_helpdb
         sp_helpdb students
8.压缩数据库:
          sp_dboption student,'single user',true
          go
          DBCC shrinkdatabase(students,50)
9.断开与连接数据库:
      1>断开:    sp_detach_db 'students'
      2>连接:    sp_attach_db 'students','f:\students_data.mdf'
10.备份和恢复数据库
      1>备份:    backup database students to disk='h:\students_back'
      2>恢复:    restore database students from disk='h:\students_back'

二.表
1.创建表：(先建主键表,再建外键表)
               create table xsxxb
            (        
             xh char(10) primary key,
             xm char(8),
             xb char(2),
              csrq datetime,
             dh char(20)
            )
        go
        create table kmxxb
        (
         kmbh char(10),
         kmmc char(20),
          primary key(kmbh)
        )
        go
        create table xscjb
        (
        xh char(10),
          kmbh char(10),
          fs int,
          foreign key(xh)references xsxxb,
        foreign key(kmbh)references kmxxb
        )
2.修改表:
   1>增加字段
         alter table xsxxb
         add bz char(50) null
   2>删除字段
         alter table xsxxb
         drop column bz
3.删除表：(先删外键表,再删主键表)
      drop table xscjb
      drop table xsxxb
      drop table kmxxb
4.复制一个表:
   select * into xsxxb2 from xsxxb
5.创建临时表#,##)
       create table #xsxxb
            (        
             xh char(10) primary key,
             xm char(8),
             xb char(2),
              csrq datetime,
             dh char(20)
            )
  
       select * from #xsxxb
6.创建用户定义数据类型:
        use students
        go
        sp_addtype sts,'varchar(20)','not null','dbo'
   
        sp_addtype sts,datatime,'null','dbo'

7.删除用户定义数据类型:
        sp_droptype sts
三.操作表中的数据
   1>使用 INSERT 语句向表中插入数据:
            insert into xsxxb values('008','','','')
   2>使用 UPDATE 语句修改表中的数据:
            update xsxxb set xm='不' where xh='001'
   3>使用 DELETE 语句删除表中的数据:
            delete from xsxxb where xh='001'
            delete from xsxxb where xh in('002','004')
            delete from xsxxb
四.系统内置函数的使用
1.聚合函数:
   1>AVG（表达式）     返回表达式中所有值的平均值。仅用于数字列并自动忽略 null 值。
   2>COUNT（表达式）   返回表达式中非 null 值的数量。可用于数字和字符列。
   3>COUNT(*)          返回表中的行数（包括有 null 值的行）。
   4>MAX（表达式）     返回表达式中的最大值，忽略 null 值。可用于数字、字符和日期时间列。
   5>MIN（表达式）     返回表达式中最小值，忽略 null 值。可用于数字、字符和日期时间列。
   6>SUM（表达式）     返回表达式中所有值的总和，忽略 null 值。仅用于数字列。
2.转型函数:
   CONVERT(datatype[(length)], expression [,style])
       select convert(char(20),getdate(),101)
3.日期函数:
   1>GETDATE()         当前的系统日期。
   2>DATEADD(datepart, number, date)   返回带有指定数字 (number) 的日期 (date)，
                                         该数字添加到指定的日期部分 (date part)  。

       select dateadd(yy,2,getdate())  (yy,mm,dd,ww,hh,mi,ss)

   3>DATEDIFF(datepart, date1, date2)   返回两个日期中指定的日期部分之间的不同.

       select datediff(yy,getdate(),'2008/09/09')

   4>DATENAME(datepart, date)          返回日期中日期部分的字符串形式。
   5>DATEPART(datepart, date)           返回日期中指定的日期部分的整数形式。
4.数学函数:
5.字符串函数:
     rtrim()
     ltrim()
     ltrim(rtrim())
     space(2)
     substring(列名,开始位置,长度)
     right(列名,长度)
     left(列名,长度)
     stuff(列名,开始位置,长度,字符串)
五.表的连接
1.内部连接:
        select xsxxb.xh,xm,xscjb.fs from xsxxb inner join xscjb
         on xsxxb.xh=xscjb.xh
2.多表连接:
        select xsxxb.xh,xm,kmmc,fs from xsxxb inner join xscjb
        on xsxxb.xh=xscjb.xh
        join  kmxxb
        on xscjb.kmbh=kmxxb.kmbh
3.左外部连接:
       select xsxxb.xh,xm,fs from xsxxb left outer join xscjb
       on xsxxb.xh=xscjb.xh
4.右外部连接:
       (与左外部连接相反)
5.完全外部连接:
       select xsxxb.xh,xm,fs from xsxxb full join xscjb
       on xsxxb.xh=xscjb.xh
6.交叉连接:
      select xm,fs from xsxxb cross join xscjb
7.自连接:
       select A.xh,A.fs,B.xh from xscjb A join xscjb B
       on A.kmbh=B.kmbh
       where A.xh>B.xh
8.联合运算符(union):
       select xh,xm from xsxxb
       union   
       select xh,xm from xsxxb2
六.数据汇总
1.排序:   (Asc升,desc降)
       select * from xscjb order by fs Asc
2.分组group by all 包含不符合指定的where条件的组,但将它们设为null)
        
        select xh,sum(fs) as tol from xscjb
        where xh='004'
        group by all xh

3.指定分组后,组的条件(having):
          select xh,sum(fs) as tol from xscjb
          group by xh
          having sum(fs)>80
4.compute汇总:
          select xh,fs from xscjb
          order by xh
          compute sum(fs)
5.compute by汇总:   
          select xh,fs from xscjb
          order by xh
          compute sum(fs) by xh
          compute sum(fs)
6.rollup和cube函数:
   rollup()  对group by子句中的列按不同的等级进行分组.
         
           select xh,sum(fs) as tol from xscjb
            group by xh with rollup

   cube()    是rollup的扩展.

七.数据的查询
1.SELECT 语句的数据检索形式
    1>显示所有列和行:
         SELECT * FROM authors
    2>显示所有行和特定的列:
         SELECT pub_name, city FROM publishers
    3>显示限定范围的行:
         SELECT stor_id, qty, title_id FROM sales
             WHERE qty BETWEEN 20 AND 50
    4>显示与值列表相匹配的行:
         SELECT * FROM publishers
             WHERE state IN ('CA', 'TX', 'NY')
    5>根据未知值显示行:
         SELECT price, title FROM titles
             WHERE price IS NULL
    6>显示/隐藏重复的值:
         SELECT DISTINCT city FROM authors
    7>显示根据多个搜索条件查询的结果:
         SELECT title FROM titles
             WHERE pub_id = 0736 AND type = 'business'
2.SELECT 语句中使用的关键字:
    BETWEEN 测试值是否在两值之间
    EXISTS 测试值是否存在
    IN 测试在一定范围内的值
    LIKE 测试类似这个值的值
    IS NULL 测试是否为 null 值
3.查询通配符的使用:
     (%,_,[],^)
   1> select * from xsxxb where xm like '张%'
   2> select  * from xsxxb where xm like '_花%'
   3> select * from xsxxb where xm like '_[花娇]%'
   4> select * from xsxxb where xm like '_[^花娇]%'
4.简单子查询:
   1>使用返回单个值的子查询:
         select xm,xb,csrq,dh from xsxxb
          where xh=(select xh from xscjb where fs=70)
5.相关子查询:
   1>使用返回多行单列的子查询:
         select xm,xb,csrq,dh from xsxxb
          where xh in(select xh from xscjb where fs>70)
   2>使用exists关键字验证存在性的子查询:
           select xm,xb,csrq,dh from xsxxb
             where exists (select xh from xscjb where kmbh='3' and fs>70
                                          and xh=xsxxb.xh )
   3>使用not exists关键字验证存在性的子查询:
            select xm,xb,csrq,dh from xsxxb
             where not exists (select xh from xscjb where kmbh='3' and fs>70
                                          and xh=xsxxb.xh )

八.流程控制语句
   1>声明和使用变量:
      declare @i int
      set @i=3
      select @i=(select fs from xscjb where xh='001')

   2>begin...end 语句块:
      begin
       print'dfdfdfd'
      end
   3>条件语句:
      if (select fs from xscjb where xh='002') >70
        begin
         print'dfdfedfd'
        end
      else if (select fs from xscjb where xh='002') <60
        begin
         print'888888'
        end
      else
        begin
         print'99999999'
        end
   4>分支语句:
        select gg=case fs   'gg是别名
          when 60 then 'df'
          when 70 then 'xdf'
          when 80 then 'yb'
          when 90 then 'xgf'
         else 'mf'
         end
        from xscjb
   5>循环语句:
      declare @i int
      declare @sum int
          set @i=0
          set @sum=0
       while @i<10
        begin
        set @sum=@sum+@i
        set @i=@i+1
        end
       print @sum
   6>标签:
       select * from xsxxb
       goto A
       select * from kmxxb
       A:
        select * from xscjb
九.视图的使用
1.创建视图:
   1>创建基于表中指定列的视图:
            create view GetFs
            as
            select xh,fs from xscjb
   2>创建基于表中指定行的视图:
            create view GetFs2
            as
            select xh,fs from xscjb where fs=80
   3>创建基于表中汇总信息的视图:
            create view GetFs3
            as
            select xh,sum(fs) as tol from xscjb
            group by xh
   4>创建基于多个表的视图:
            create view GetFs4
            as
            select xsxxb.xh,xm,kmxxb.kmmc,xscjb.fs from xsxxb,kmxxb,xscjb
            where xsxxb.xh=xscjb.xh and xscjb.kmbh=kmxxb.kmbh
   5>创建基于视图的视图:
            create view GetFs5
            as
            select * from GetFs4
             where fs>75
2.更改视图:
           (把create换为alter)
3.删除视图:
   1>删除视图中的数据:
         delete GetFs2
   2>删除视图:
         drop view GetFs2
4.通过视图修改数据:
     create view GetFs6
     as
     select xh,xm from xsxxb
   1>插入数据:
       insert into GetFs6 values('005','黄三')
   2>更新数据:
       update GetFs6 set xh='006' where xh='005'
   3>删除数据
       delete GetFs6 where xh='006'
十.存储过程与触发器
1.创建存储过程与执行存储过程:
   1>创建一个不带参数的存储过程:
        create proc Display_orders
        as
        select * from orders
   2>创建一个带输入参数的存储过程:
        create proc Display_orders
        @cusid char(20)
        as
        select * from orders where customerid=@cusid
   3>创建一个带输入，输出参数的存储过程:
        create proc Display_Name
        @Name char(20) output
        as
        select @Name=(select xm from xsxxb,kmxxb,xscjb where xsxxb.xh=xscjb.xh and
                       fs=(select max(fs) from xscjb where kmbh=(select kmbh from kmxxb
                             where kmmc=@Name))
                      and kmxxb.kmbh=(select kmbh from kmxxb where kmmc=@Name))
                  from xsxxb,kmxxb,xscjb
        print @Name
2.更改存储过程:
       (把create换为alter)
3.删除存储过程:
      drop proc Display_Name
4.创建触发器:
   1>创建INSERT 触发器:
        create trigger checkFs
        on  xscjb
        for insert
        as
         if(select fs from inserted)<50
         begin
         print'bu neng cha!'
         rollback tran
         end
   2>创建UPDATE 触发器:
        create trigger NoUPdateXm
        on xsxxb
        for update
        as
        if update(xm)
         begin
         print'bu neng geng xing xm!'
         rollback tran
         end
   3>创建DELETE 触发器:
        create trigger NoDelete002
        on xsxxb
        for delete
        as
        if (select xh from deleted)='002'
         begin
         print'bu neng shang chu xh wei 002!'
         rollback tran
         end
5.更改触发器:
          (把create换为alter)
6.删除触发器:
        drop trigger NoDelete002
7.禁用和启用触发器:
    1> 禁用:
        alter table xsxxb
        disable trigger NoDelete002
   2> 启用:
        alter table xsxxb
        enable trigger NoDelete002

十一.用户自定义函数
1.创建用户自定义函数:
   1>创建数量型用户自定义函数：（返回一个简单的数值,如：int,char等）
      create function NumAdd
      (@num1 int,@num2 int)
      returns int
      as
      begin
      return(@num1+@num2)
      end

   调用：select dbo.NumAdd(4.6)


   2>创建表值型用户自定义函数:（返回一个Table型数据）

      use northwind
      go
      create function DisplayXsxx
      (@xh char(20))
      returns table
      as
      return(select * from xsxxb where xh=@xh)

   调用：select * from DisplayXsxx('002')

2.更改用户自定义函数:
     (把create换为alter)
3.删除用户自定义函数:
      drop function DisplayXsxx
十二.游标
1.创建游标:
declare Fs_level cursor
static
for select xm,sum(fs) from xsxxb,xscjb where xsxxb.xh=xscjb.xh group by xm
declare
     @fs int,
     @Name varchar(20)
2.打开游标:
open Fs_level
3.提取游标:
   fetch next from Fs_level into @Name,@fs
   while(@@Fetch_status=0)
       begin
           if @fs<150
               print'总分太低！'
           else
              print'高分！'
           fetch next from Fs_level into @Name,@fs
       end
4.关闭游标:
close  Fs_level
5.销毁游标:
deallocate Fs_level
十三.数据完整性
1.缺省
  1>创建缺省:
      create default dd
      as 'MN'
  2>绑定缺省:
      sp_bindefault dd,'xsxxb.xh'
  3>取消缺省:
      sp_unbindefault 'xsxxb.xh'
  4>删除缺省：
      drop default dd
2.规则
  1>创建规则:
      create rule rr
      as @scode in('MN','ND','SD')
  2>绑定规则:
      sp_binderule rr, 'xsxxb.xh'
  3>取消规则:
      sp_unbindrule 'xsxxb.xh'
  4>删除规则：
      drop rule rr
3.约束
  1>主键约束:
      primary key
  2>外键约束:
      foreign key(列名) references 表名
  3>唯一约束:
      unique
  4>检查约束:
      check(xb='男' or xb='女')
十四.数据库的安全性
1.帐户
   1>创建一个帐户:
    sp_addlogin 'zj','0822','pubs'
   2>查询帐户的相关信息:
    select convert(varbinary(32),password) from syslogins where name='zj'
   3>更改，删除帐户:
     sp_password '0822','888','zj'
2.数据库用户
   1>添加数据库用户
     use northwind
     go
     sp_grantdbaccess zj
   2>删除数据库用户
     use northwind
     go
     sp_revokedbaccess [zj]

3.角色
   1>浏览服务器角色的内容：
      sp_helpsrvrole
   2>角色分配给帐户：
       sp_addsrvrolemember zj,'sysadmin'
4.权限
   1>授予权限:
       use northwind
       go
       grant insert,update on xsxxb to zj
   2>撤消权限：
       revoke create table,create view from zj
   3>拒绝访问:
       use northwind
       go
       deny select,insert,update ,delete on xsxxb to zj
十五.事务与锁
1.事务
  1>一个标准事务:
    begin tran
      select * from xsxxb
    commit tran

   或
    begin tran insert xscjb
      insert into xscjb values('002','2',70)
    commit tran
  2>返回几个事务在运行:
     begin tran
        select * from xsxxb
        select * from kmxxb
        select @@trancount  --执行第一次时返回值为1，每执行一次事务数量就加1。
     commit tran
        select @@trancount  --返回值为0。
  3>复杂可回滚事务:
      declare @i int
    set @i=0
    print ltrim(str(@i))

  begin tran
      print ltrim(str(@i))
      select @i=(select count(*) from xsxxb)
   if @i>4
       begin
          rollback tran
          return      --停止过程中当前批的执行，并在下一批的开始处恢复执行。
       end
   else
       print ltrim(str(@i))
       select * from xsxxb
       select @@trancount

      begin tran      --嵌套事务
          select * from xscjb
          select @@trancount
      commit tran
  commit tran
  4>嵌套事务:
     declare @i int
    set @i=0
    print ltrim(str(@i))

  begin tran
      print ltrim(str(@i))
      select @i=(select count(*) from xsxxb)
   if @i>4  --改为3试一试
       begin
          rollback tran
          return      --停止过程中当前批的执行，并在下一批的开始处恢复执行。
       end
   else
       print ltrim(str(@i))
       select * from xsxxb
       select @@trancount

      begin tran      --嵌套事务
          select * from xscjb
          select @@trancount
      commit tran
  commit tran
  5>与表相联系的事务:
    declare @i int
       set @i=0
         begin tran
           update xscjb set fs=85 where fs=70
            set @i=2
           if @i>1  --改为3试一试
           begin
             rollback tran
             return
           end
           else
         commit tran
     go
   select * from xscjb
  6>设置保存点:
    declare @i int
       set @i=0
         begin tran
           update xscjb set fs=120 where fs=90
            save tran s1
            set @i=2
           if @i>1  
           begin
             rollback tran s1
             return
           end
           else
         commit tran
     go
   select * from xscjb
  7>含子查询的事务:
    begin tran
          declare @fs int
          update xscjb set fs=95 where fs=90
          select @fs=(select max(fs) from xscjb)
        if @fs<100  --改为90试一试
         begin
           rollback tran
           return
         end
        else
       commit tran
        go
           select * from xscjb
  8>隐式事务:
    [im'plisit]暗示的  
      set implicit_transactions on  --打开
         update xscjb set fs=95 where fs=90
         select @@trancount
         go
          select * from xscjb

2.锁
  事务的隔离级别：
    1>读提交：
      第一个窗口：
       begin tran
           update xscjb set fs=95 where xh='002'
      第二个窗口:
       set transaction isolation level read committed
            go
             select * from xscjb where xh='002'
    2>读未提交:
       第一个窗口：
      begin tran
            update xscjb set fs=80 where xh='002'
       第二个窗口:
      set transaction isolation level read uncommitted
         go
           select * from xscjb
    3>可重复读:
      第一个窗口：
      set transaction isolation level repeatable read
  go
   begin tran
      select * from xscjb
      update xscjb set fs=100 where xh='002'
      select * from xscjb
      rollback
    第二个窗口:
        set transaction isolation level read committed
go
   begin tran
       insert into xscjb values('002','2',120)
       select * from xscjb where fs=120
       rollback
    4>顺序读:
      第一个窗口：
      set transaction isolation level serializable
  go
   begin tran
      select * from xscjb
      update xscjb set fs=100 where xh='002'
      select * from xscjb
  
      第二个窗口:
        set transaction isolation level read committed
go
   begin tran
       insert into xscjb values('002','2',120)
       select * from xscjb where fs=120
   
      









1. STATUS;
2. mysql> SELECT @@global.time_zone, @@session.time_zone;

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

| @@global.time_zone | @@session.time_zone |

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

| SYSTEM             | SYSTEM              |

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

1 row in set (0.00 sec)

3.
mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`i` int(11) default NULL,
`j` int(11) NOT NULL,  `k` int(11) default '-1'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4. SHOW DATABASES LIKE 'm%';
5.SHOW WARNINGS\G显示错误
复制创建表技巧
6. CREATE TABLE CityCopy1 SELECT * FROM City;复制表
7. CREATE TABLE CityCopy2 SELECT * FROM City where id=5;
按条件复制表:将city 表格的结构复制同时复制id=5的记录
8. CREATE TABLE CityCopy3 SELECT title FROM City where id=5;
   title(是字段)指定city表中的title字段复制创建成CityCopy3表
9.重命名
(1)        ALTER TABLE t1 RENAME TO t2;
  (2)Rename tabae t1 to t2;
(2)        RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; 批量命名
10.删表
(1)DROP TABLE IF EXISTS t1;或者DROP TABLE t1
  (2)DROP TABLE t1, t2, t3; 批量删表
11.清空表记录
(1)        DELETE FROM t;或者TRUNCATE TABLE t;
(2)        DELETE FROM t WHERE id=5;指定条件删除
删除表
DELETE FROM table_name;
TRUNCATE TABLE table_name;

12.select 另类用法
(1) mysql> SELECT 1 AS One, 4*3 'Four Times Three';

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

| One | Four Times Three |

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

|   1 |               12 |

+-----+------------------+
(2)SELECT last_name, first_name FROM t ORDER BY 1, 2;
排序ORDER BY 1, 2升序;2,1降序
(3) mysql> SELECT description, IF('abc' LIKE pattern,'YES','NO') FROM patlist;

  在对pattern字段中别条件
+--------------------------------+-----------------------------------+

| description                    | IF('abc' LIKE pattern,'YES','NO') |

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

| empty string                   | NO                                |

| non-empty string               | YES                               |

| string of exactly 3 characters | YES                               |

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

(4) SELECT ABS(-14.7), ABS(14.7);
(5) select * from tablename order by data desc limit 0,20返回20条数据(同微软数据库中select top 20 * from tablename 一样)
select * from tablename limit 0,20
说明：limit 0,20 （0是从表的第一行开始，是可以指定的，20是查询返回20条记录）
13.数据库加密
SELECT MD5('a');
mysql> SELECT MD5('a');

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

| MD5('a')                         |

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

| 0cc175b9c0f1b6a831c399e269772661 |

+----------------------------------+
14.随机数
SELECT RAND();

15. INSERT插入值的技术
(1)        INSERT INTO people (name,age)VALUES('William',25),('Bart',15),('Mary',12);多重插入
(2)INSERT INTO people VALUES(25,'William');不用中间的字段名字
(3) INSERT INTO t (i) VALUES(1),(2),(3),(4),(5);
16.数据替换
(1)        REPLACE INTO people (id,name,age) VALUES(12,'William',25);
(2)        多重替换
REPLACE INTO people (id,name,age)VALUES(12,'William',25),(13,'Bart',15),(14,'Mary',12);
(3)确定条件替换
REPLACE INTO people SET id = 12, name = 'William', age = 25;