基本的SQL语句

@基本的SQL语句@

 

建库：create database db_name;

 

查库：show databases;

 

建表(设主键)：create table user(id varchar(10) not null,name varchar(10) not null,age int(5) default null,primary key (id))innodb default charset = gbk;

 

建表(设外键)：create table produce(pid varchar(10) not null,price float(7,2),foreign key(pid) references user(id));

 

查表：show tables;

 

查表结构：describe tb_name;

 

查询：select count(*) as count from tb_name where......

         select * from tb_name group by 字段 having 条件......

         select * from tb_name order by 字段 desc(asc);

         select * from g.g_name,g.g_id,p.p_id from goods as g,produce as p where g.g_id = p.p_id;

 

删表：drop table tb_name;(有外键的先删从表后删主表)

 

创建存储过程：

        delimiter // (标注)

            create procedure p_name()

            begin

                ....语句体....

            end

         //

        call p_name(); //

@带参数的存储过程@

        delimiter $$

            create procedure p_name(out param1 int)

            begin

                ....语句体....

                select count(*) into param1 from tb_name;

            end

        $$

        call p_name(@abc);  $$

        select @abc;  $$

 

创建函数：

        delimiter //

            create function hello(s char(20)) returns char(50)           s:变量，要输入的值  return s:要返回的值

            return concat('hello',s,'!');

        //

        select hello('world');                 结果：helloworld!

 

创建视图：

         create view v_p as select u.uid,u.u_name,p.pid,p.price from user_info as u,produce_info as p,foreignkey as f where f.uid = u.uid and f.pid = p.pid;

         select * from v_p;

 

删除视图：drop view v_p;

 

创建索引：create uniue index q_s on user_info(id);

               show * from q_s;

 

删除索引：drop view q_s;

 

 

http://dev.mysql.com/doc/refman/5.1/zh/index.html

oracle工具：sqldeveloper-2.1.0.63.73-no-jre.zip