数据库的常用操作指令(随时保持更新)

1.创建表

  CREATE table TableName(
  Column_Name1 VARCHAR2(10) PRIMARY KEY,
  Column_Name2 VARCHAR2(10) NUIQUE NOT NULL,
  Column_Name3 int)
  例如:
  CREATE table test(
  ID   VARCHAR2(10) PRIMARY KEY,
  NAME VARCHAR2(10) NOT NULL UNIQUE,
  AGE  int);
S
2.插入数据
 
  INSERT INTO Table_Name VALUES('','',);
  例如:
  INSERT INTO test VALUES('081026','Kaku',24);

3.更改表数据

  UPDATE Table_Name
  SET Column_Name1='CString',
  Where Column_Name2='NUMBER'
  例如:
  update test
  set id='081126'
  where name = 'Kaku';

4.删除表数据
 
  DELETE FROM Table_Name WHERE Column_Name1='CString' AND .......;
  例如:
  delete from test where id = '081126' and name = 'Kaku';

5.清空表中所有数据

  TRUNCATE Table_Name;
  例如:
  truncate test;
6.添加表字段

  ALTER TABLE Table_Name ADD COLUMN Column_name;
  例如:
  ALTER TABLE test ADD COLUMN sex VARCHAR2(3);

7.删除表字段

  ALTER TABLE Table_Name DROP COLUMN Column_name;
  例如:
  ALTER TABLE test DROP COLUMN sex;

8.查询

  SELECT * FROM Table_Name;
  例如:
  查询很灵活,在这暂不举例子,孰能生巧吧！

rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">

9.ALTER TABLE

 

  ALTER TABLE table [ * ]

    ADD [COLUMN] column type [column_constraint [ ... ]]

  ALTER TABLE table [ * ]

    ALTER [COLUMN] column {SET DEFAULT value | DROP DEFAULT}

  ALTER TABLE table [ * ]

    ALTER [COLUMN] column SET STATISTICS integer

  ALTER TABLE table [ * ]

    RENAME [COLUMN] column TO newcolumn

  ALTER TABLE table

    RENAME TO newtable

  ALTER TABLE table

    ADD table constraint definition

  ALTER TABLE [ONLY] table

    DROP CONSTRAINT constraint

{RESTRICT | CASCADE}

ALTER TABLE table

    OWNER TO new owner

10.数据表重命名

   
    RENAME Table_Old TO Table_NEW;
    例如:
    rename  test to test1;
11.模糊查询

     SELECT * FROM Table_Name
     WHERE  COLUMN_Name LIKE '%  ****  %';
     例如:
     select * from test
     where id = '039092'
     and  Name = 'Xuxm'
     and  address like '% China %';