mysql的使用笔记

、创建数据库：
create database db;
导入数据库
source D:\test.sqls
对表的操作：
创建表前先进入表use mydb1;
create table employee
(
id int,
name varchar(20),
gender varchar(4),
birthday date,
Entry_date date,
job varchar(40),
salary double,
resume text
)character set utf8 collate utf8_general_ci;
alter table employee add image blob;
查看表的创建细节：
show create table employee;
查看表结构：
desc employee;
alter table employee modify job varchar(60);
alter table employee drop gender;
改表名
rename table employee to user;
显示所有表：
show tables;
修改表的字符集：
alter table user character set gb2312;
将列名name修改为username;
alter table user change column name username varchar(20);
alter table employee change column username name varchar(20);
向表中插入数据：
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(1,'caiwanxia','1992-11-06','2014-03-01','computer',6000,'graduate');
查看表的数据：
select * from employee;
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(2,'蔡婉霞','1992-11-06','2014-03-01','computer',6000,'graduate');
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(2,'邓静','1991-10-04','2014-03-01','computer',6000,'graduate');
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(2,'汪加祥','1993-01-06','2014-03-01','computer',6000,'graduate');
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(2,'李羊琴','1991-03-06','2014-03-01','computer',6000,'graduate');
insert into employee(id,name,birthday,entry_date,job,salary,resume)
values(2,'尹倩倩','1993-07-06','2014-03-01','computer',6000,'graduate');


insert into tb_userinfo
(id,login_id,real_name,sex,birthday,email,birth_place,
live_place,regist_time,last_login_time,introduce)
values(1,1,'蔡婉霞','女','1992-11-06','1125575769@qq.com','湖北嘉鱼','湖北嘉鱼','2014-08-11',null,'I am a lively and cheerful girl');


解决中文乱码问题：
show variables like 'chara%';




显示乱码解决方案：
set character_set_client=gb2312;
set character_set_results=gb2312;


update语句：
update employee set salary=5000;
update employee set salary=6000 where name='caiwanxia';
update employee set salary=7000,job='manager' where name='蔡婉霞';
update employee set salary=salary+1000 where name='caiwanxia';


update users set city='武',income=1800,preference='唱歌,跳舞' where username='caicai';


delete语句：
delete from employee where name='caiwanxia';
删除表中所有数据：
delete from employee;(一条一条的删除的)
truncate table employee;(先摧毁整个表，然后再重整整个表的结构)
对于大数据好些,效率高些;
脚本文件导入：
source c:\student.sql;
为了便于维护：
select id,name,birthday,entry_date,job,salary,resume from employee;
过滤重复数据：
select distinct salary from employee;
select name,salary+100 from employee;
select name,(english+chinese+math) from student;
select name as 姓名,(english+chinese+math)as 总分 from student;
select * from student where english>90;
select name 姓名，(english+chinese+math)as 总分 from student where (english+chinese+math)>200;


外键的设置：
create table id_card(
id int primary key auto_increment,
name varchar(20) not null,
foreign key(id) references person(id)
)
create table person(
id int primary key auto_increment,
name varchar(20) not null

)










约束：
定义带有主键约束的表：
create table test1(
   id int primary key,
   name varchar(20),
   password varchar(20)
);
定义一个主键 自动增长的表
create table test2(
   id int primary key auto_increment;
   name varchar(20),
   password varchar(20)
)
唯一性：
create table test2(
   id int primary key auto_increment;
   name varchar(20)unique,
   password varchar(20)
)
create table department(
id int primary key auto_increment,
name varchar(20) not null
)
唯一并且非空：
create table test2(
   id int primary key auto_increment;
   name varchar(20)unique not null,
   password varchar(20)
)
外键约束：
create table wife(
  id int primary_key,
  name varchar(20),
  husband_id int,
  constraint husband_id_FK foreign key(husband_id)references husband(id)
)
update employee set id=3 where name='汪加祥';
update employee set id=4 where name='李羊琴';
update employee set id=5 where name='尹倩倩';




sql攻击：
'or 1==1 or username='用PreparedStatement替换Statement
1.PreparedStatement是Statement的孩子；
2.PreparedStatement可以防止sql注入的问题；
3.2.PreparedStatement它可以对它所代表的sql语句进行预编译，以减轻服务器的负担




分页技术减轻服务器数据库的压力：
select name from customerss limit 0,10;




拷贝工程后记得：该属性-myeclipse的web路径




1.开发环境
  1.1导入开发包
      jstl开发包
      mysql驱动
      beanutils开发包
      log4j开发包
  1.2程序开发包
     cn.itcast.domain
     cn.itcast.dao
     cn.itcast.service
     cn.itcast.service.impl
     cn.itcast.web.UI
     cn.itcast.web.controller
     cn.itcast.utils
     cn.itcast.exception
     web-in/jsp保存网站jsp页面
  1.3创建数据库和表
     create database customer;
     use customer;
     create table customers
     (
      id varchar(40)primary key,
      name varchar(20) not null,
      gender varchar(4) not null,
      birthday date,
      cellphone varchar(20) not null,
      email varchar(40),
      preference varchar(200),
      type varchar(100),
      description varchar(255)
     );


   1.4编写配置文件db.properties
2.设计实体


3.写dao


4.写service


5.写web层
1.改写dao,对service层提供分页的数据服务；
2.设计page对象，封装分页的页面数据；
3.改写service，它对web层提供分页的数据服务






表单校验：
@Override
public ActionErrors validate(ActionMapping mapping,
HttpServletRequest request) {
ActionErrors errors=new ActionErrors();
MessageResources ms=MessageResources.getMessageResources("cn.itcast.resource.MessageResource");
if(isEmpty(username)){
//errors.add("username", new ActionMessage("用户名不能为空", false));
//errors.add("username", new ActionMessage("errors.username.required"));
addMessage(errors, "username", "errors.required", ms.getMessage("prompt.username"));
}else{
if(!this.username.matches("[A-Za-z]{3,9}")){
addMessage(errors, "username", "errors.username.required2", null);
}
user.setUsername(this.username);
}
if(isEmpty(password)){
// ResourceBundle bundle=ResourceBundle.getBundle("cn.itcast.resource.MessageResource");
// errors.add("password", new ActionMessage("errors.required",bundle.getString("prompt.password")));
addMessage(errors, "password", "errors.required", ms.getMessage("prompt.password"));
}else{
if(!this.password.matches("\\w{3,9}")){
addMessage(errors, "password", "errors.password.required2", null);
}
user.setPassword(this.password);
}
if(isEmpty(password2)){
addMessage(errors, "password2", "errors.required", ms.getMessage("prompt.password2"));
}else{
if(!password2.equals(password)){
addMessage(errors, "password2", "errors.password2.required2", null);
}
}
if(isEmpty(gender)){
addMessage(errors, "gender", "errors.required", ms.getMessage("prompt.gender"));
}else{
try {
Gender g=Gender.valueOf(this.gender.toUpperCase());
user.setGender(g);
} catch (Exception e) {
addMessage(errors, "gender", "errors.gender.required2", null);
}
}

if(!isEmpty(birthday)){
try{
DateLocaleConverter convert=new DateLocaleConverter(Locale.CHINA, "yyyy-MM-dd");
Date date=(Date) convert.convert(birthday);
user.setBirthday(date);
}catch (Exception e) {
addMessage(errors, "birthday", "errors.birthday.required2", null);
}
}
//收入要是一个数字
if(!isEmpty(income)){
try {
double i=Double.parseDouble(income);
user.setIncome( i);
} catch (Exception e) {
addMessage(errors, "income", "errors.income.required2", null);
}
}
if(preference!=null&&preference.length>0){
Preference[] pres=new Preference[preference.length];
int pos=0;
for(String pre:preference){
try {
Preference p=Preference.valueOf(pre.toUpperCase());
pres[pos++]=p;
//Preference[] preference=new Preference();
//user.setPreference(preference)
} catch (Exception e) {
addMessage(errors, "preference", "errors.preference.required2", null);
}
}
user.setPreference(pres);
}
if(!isEmpty(email)){
if(!this.email.matches("\\w+@\\w+(\\.\\w+)+")){
addMessage(errors, "email", "errors.email.required2", null);
}
user.setEmail(email);
}
user.setCity(city);
return errors;
}




工程：struts13
动态创建formbean:继承DynaActionForm


struts14
validate框架
struts15
FCKeditor框架


insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);




事务：
start transaction;事务开始
....
....
commit;事务结束
rollback;手动事务回滚
update account set money=money-100 where name='aaa';




相对路径的层级关系是这样的，同级目录用/或者什么也不写，上级目录用../，下级目录就用目录名/。举个例子吧，假如你的index.html在你的web文件夹下。main.css和index1.html在你的web文件夹下的css文件夹中，你的index2.html在你的web文件夹下的files文件夹下搜索，那么你想在index.html中引用css，路径就是css/main.css。而在index1网页中引用css就是main.css，在index2.html中引用css就是../css/main.css。