数据库的语句调试可以直接在navicat中调试
可以直接创建语句写,然后运行
删除元素
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
Connection con;
Statement sql;
System.out.print(1);
//下面的为解码
String username = java.net.URLDecoder.decode(request.getParameter("username"),"utf-8");
String filename = java.net.URLDecoder.decode(request.getParameter("filename"),"utf-8");
System.out.println(username);
System.out.println(filename);
try {
String uri = "jdbc:mysql://localhost/MakeFriend?useSSL=false&serverTimezone=UTC";
con = DriverManager.getConnection(uri,"root","gunxueqiu");
String condition = "delete from footid where username = '"+username+"'"+" and filename = '"+filename+"'";
System.out.println(condition);
sql = con.prepareStatement(condition);
sql.execute(condition);
String filepath=request.getSession().getServletContext().getRealPath("/files");
deleteFile(filepath+"/"+username+"/"+filename);
out.println("删除成功!");
} catch (Exception e) {
e.printStackTrace();
}
}
注意是delete from footid where username = '"+username+"'"+" and filename = '"+filename+"'";
不是delete * from footid where username = '"+username+"'"+" and filename = '"+filename+"'";
查找与添加:
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
Connection con;
Statement sql;
String username = java.net.URLDecoder.decode(request.getParameter("username"),"utf-8");
String filename = java.net.URLDecoder.decode(request.getParameter("filename"),"utf-8");
String uri = "jdbc:mysql://localhost/MakeFriend?useSSL=false&serverTimezone=UTC";
try {
con = DriverManager.getConnection(uri,"root","gunxueqiu");
String condition = "select * from footid where username = '"+username+"'"+" and filename = '"+filename+"'";
boolean haveuser=false;
sql = con.prepareStatement(condition);
ResultSet rSet = sql.executeQuery(condition);
if(rSet.next()) {
haveuser=true;
int id=rSet.getInt("id");
out.println(""+id);
con.close();
}
if(haveuser==false) {
con = DriverManager.getConnection(uri,"root","gunxueqiu");
String filepath=request.getSession().getServletContext().getRealPath("/files");
File file=new File(filepath);
if(!file.exists())
file.mkdirs();
filepath=filepath+"/"+username;
file=new File(filepath);
if(!file.exists())
file.mkdirs();
filepath=filepath+"/"+filename;
file=new File(filepath);
if(!file.exists())
file.mkdirs();
movefile(request.getSession().getServletContext().getRealPath("/files"),filepath,filename);
//避免生成重复id
int id;
for(;;) {
id=((int)(Math.random() * 1000000));
System.out.println("123");
String condition1 = "select * from footid where id = "+id;
System.out.println(condition1);
sql = con.prepareStatement(condition1);
ResultSet rSet1 = sql.executeQuery(condition1);
System.out.println("now"+id);
if(rSet1.next()) {
System.out.println("yes");
continue;
}
else {
System.out.println("no");
break;
}
}
//将当前id加入数据库
String sqladd="insert into footid(username,filename,id,url,path)values(?,?,?,?,?)";
PreparedStatement ps=con.prepareStatement(sqladd);
ps.setString(1,username);
ps.setString(2,filename);
ps.setLong(3,id);
ps.setString(4,filepath);
ps.setString(5,"/files"+"/"+username+"/"+filename+"/"+filename);
int row=ps.executeUpdate();
if(row>0){
out.println(""+id);
}
ps.close();
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
数据库加入元素时,如果类型是非空的,则传入数据库必须有值,不然会出错。
如果选了不是null
则会出现下面的错误
所以如果要为空的话,得选成能空的状态
数据库内容复制给其它数据库
先将当前的数据库中的结构和数据全部转储
然后在新的数据库点击运行sql文件
选择刚才转储的文件
数据复制成功
进入mysql
mysql -uroot -p
mysql创建新用户
GRANT ALL PRIVILEGES ON *.* TO'用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
flush privileges;//刷新权限
mysql删除用户
drop user zhangsan@'%';//zhangssan是user,%是该用户的权限
查看用户权限情况
SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
navicat无法删除连接
解决方法:把连接断开以后,再删除,如果还是不行,把navicat重新启动
navicat连接完以后发现始终是自己的本地电脑
解决方法:应该是粗心问题,注意添加的时候连接名只是名字,主机处才是ip地址
System.Data.SqlTypes.SqlNullValueException:“Data is Null. This method or property cannot be called on Null values.”
说明数据库表中拿出了空值,可以看看数据库的具体情况,是否有值没有写入,如果没有写入的话是否有提供默认值
数据库迁移
本质上其实就是把一堆sql语句塞进一个文件(比如txt文件),后缀命名为sql。然后比如拿navicat怎么执行这个文件即可导入相应的数据。
数据库学习 - like
参考:https://blog.csdn.net/linan_pin/article/details/70154416
like(模糊查询)
比如查询姓张的同学,查询张某某等这类型问题,在select语句中通过查询条件中加入运算符like来表示;
含有like运算符的表达式
列名 [not] like “字符串”(“[]”表示其中内容可省略)
找出匹配给定字符串的字符串,其中给定的字符串中可以出现%,等匹配符。
匹配规则:
“%”匹配0个或多个字符
“”匹配任意单个字符
“\”转义字符,用于去掉一些特殊字符的特定含义,使其被作为普通字符看待,如用“%”匹配%,用“_”匹配字符
数据库不能用match为表名
不然会出错,我后来改成了match_table