数据库常用语句

列出所有数据库信息:  show databases;
转到某个数据库:        use database_name;
列出某个数据库的所有表信息: show tables;
创建数据库:              CREATE DATABASE database_name;
创建数据库表:           CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1),
                                                                    birth DATE, birthaddr VARCHAR(20));
显示表结构:              DESCRIBE mytable;
更改表名:                 alter table table_name rename new_t;
添加列:                    alter table table_name add column column_name column_attributes;

                               ALTER TABLE TABLE_AAA ADD (STOCKMAN NUMBER(18))//

给列添加注释:           COMMENT ON COLUMN TABLE_AAA.STOCKMAN IS '管理人员'

列添加外键 :             ALTER TABLE TABLE_AAA ADD CONSTRAINT FK_TABLE_AAA_R_TABLE_BBB_S FOREIGN KEY (STOCKMAN) REFERENCES TABLE_BBB (ID)//
删除列:                    alter table table_name drop column column_name;
创建索引:                 alter table c_table add index (column_name1,column_name2);
                              alter table c_table add unique index_name(column_name);
                              alter table c_table add primary key(sid);
删除索引:                 alter table c_table drop index column_name1;
更改列信息:              alter  table t_table change c_1 c_1 varchar(200);
                              alter table t_table modify 1 c_1 varchar(200);
插入语句:                 insert into table_name (c_1,c_2) values ('x1',1);
更新语句:                 update table_name set c_1 =1 where c_2=3;
删除数据库/表:         drop table table_name;
                             drop database database_name;

 

 

 

 

 

 

 

 

 

查看oracle 连接情况：

select * from v$process --当前的连接

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

alter system set processes = 300 scope = spfile;  --修改最大连接数:

shutdown immediate; --重启数据库:
startup;

--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine  from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;

select * from v$session   --连接

select * from v$session where status='ACTIVE'　-并发连接

show parameter processes  --最大连接

 

 

 

--查询电脑连接数

SELECT   username, machine, program, status, COUNT (machine) AS
连接数量
    FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;