mysql基本操作

来源：互联网发布：java poi word 编辑：程序博客网时间：2024/06/08 07:20

//进入数据库：打开cms->输入mysql -uroot -p->输入密码（root）

//查看数据库：showdatabases;

//打开某一数据库：use 数据库名；eg:usedangdang;

//显示数据库里的表：show tables;

//创建数据库：CREATE DATABASE 数据库名 CHARACTER SETgbk(或utf8);

//删除数据库：DROP DATABASE 数据库名；

//查看数据库：SHOW CREATE DATABASE数据库名；

//新建表

DROP TABLEIF EXISTS t_students;

CREATETABLE t_students(

id int(12)NOT NULL auto_increment,

namevarchar(100) NOT NULL,

ageTINYINT,

sexENUM('男','女'),

dbatedate,

PRIMARYKEY (id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERTINTO t_students VALUES(1,'TOM',19,'男','2012-12-25'); //加入表内容（行的内容）

INSERTINTO t_students VALUES(2,'TOM2',20,'男','2013-12-25');

INSERTINTO t_students VALUES(3,'TOM3',21,'男','2014-12-25');

SELECT *FROM t_students; //查询显示表内容

ALTERTABLE t_students ADD email VARCHAR(50);//添加列

ALTERTABLE t_students DROP email; //删除列

DESCt_students; //查看表结构

SHOWCREATE TABLE t_students; //查看表结构

ALTERTABLE t_students MODIFY name varchar(25); //修改类型varchar 大小

ALTERTABLE t_students CHANGE name sname varchar(35); //修改名称与类型varchar 大小

UPDATEt_students SET age=age+10; //修改表中内容记录；

UPDATEt_students SET dbate='2000-10-10',sex='女' WHERE sname ='TOM'; //修改表中记录的内容

1\SELECT *FROM emp WHERE ename = 'james'; //跟据特定项查找

2\SELECT *FROM emp WHERE ename REGEXP '^a'; //查找name以a开头的

3\SELECT *FROM emp WHERE (deptno = 20&&job ='clerk'||deptno = 10&&job ='manager');//多项内容查找

4\SELECTename,hiredate FROM emp; // 选取特定列的查找

String url= "jdbc:mysql://127.0.0.1/cms";

//jdbc:mysql://协议

// 127.0.0.1/ ：服务器地址

//cms ：数据库的名称

Class.forName("com.mysql.jdbc.Driver"); //

例：

实现控制台/jdbc版注册登陆数据库的建立

1\用户信息注册表

CREATETABLE bbs_user(

id intprimary key auto_increment,

nameVARCHAR(50) not null default '',

emailVARCHAR(50) UNIQUE,

pwdVARCHAR(50) not null default '',

hobbyVARCHAR(50),

qqVARCHAR(50) UNIQUE

)engine=innodb;

2\板块

CREATETABLE bbs_user(

id intprimary key auto_increment,

nameVARCHAR(50) not null default '',

emailVARCHAR(50) UNIQUE,

pwdVARCHAR(50) not null default '',

hobbyVARCHAR(50),

qqVARCHAR(50) UNIQUE

)engine=innodb;

3.主题帖，回复

CREATETABLE project(

id INTauto_increment,

pid intnot null default 0,

titlevarchar(25) not null default'',

destvarchar(25) not null default'',

cuservarchar(25) not null default'',

ctimevarchar(25) not null default '0000-00-00',

recountint not null default 0,

primarykey(id)

)engine=innodb;

建一个bbs的数据库

createdatabase bbs default character set gbk;

usebbs;

createtable bbs_user(

id intauto_increment,

namevarchar(25) not null default '',

pwdvarchar(25) not null default '',

emailvarchar(80) not null default '',

lastipvarchar(15) not null default '',

logincountint not null default 0,

statenum('0','1') not null default '1' comment '0禁言',

ctimedatetime not null default '0000-00-00',

primarykey(id)

)engine=innodb;

createtable bbs_page(

id intauto_increment,

titlevarchar(25) not null default '',

desctvarchar(255) not null default '',

adminvarchar(25) not null default '' comment '版主',

ctimedatetime not null default '0000-00-00',

primarykey(id)

)engine=innodb;

createtable bbs_topic(

id intauto_increment,

pid intnot null default 0,

titlevarchar(25) not null default '',

destvarchar(25) not null default '',

cuservarchar(25) not null default '',

ctimedatetime not null default '0000-00-00',

rcount intnot null default 0,

primarykey(id)

)engine=innodb;

insertinto bbs_uservalues(null,"tom","tom!","email@1","hh-hh",67,1,"2012-12-25");

insertinto bbs_topic values(null,0,"java特点","","kaka",now(),3);

insertinto bbs_topic values(null,1,"跨平台","","tom",now(),0);

insertinto bbs_topic values(null,1,"手机开发","","jerry",now(),0);

insertinto bbs_topic values(null,1,"游戏开发","","maray",now(),0);

createtable bbs_admin(

id intprimary key auto_increment,

namevarchar(50) not null default '',

pwdvarchar(50) not null default ''

);

insertinto bbs_admin values(1,'dog','dog');

//分页显示一页10行

SELECT id,title,desct,admin,ctime FROM bbs_page limit 0,10;

SELECT id,title,desct,admin,ctime FROM bbs_page limit 10,10;

SELECT id,title,desct,admin,ctime FROM bbs_page limit 20,10;