MySQL基本操作
来源:互联网 发布:csgo怎么看竞技数据 编辑:程序博客网 时间:2024/05/22 00:04
cmd登录命令:
mysql -u root -p密码 (注:-p和密码之间没有空格)
或1.mysql -u root -p 2.输入密码
或1. mysql -h 127.0.0.1 -u root -p 2.输入密码
或1.mysql -h localhost -u root -p 2.输入密码
或1.mysql -D 数据库名 -u root -p 2.输入密码
退出登录:
输入exit 或 quit 即可退出登录
创建数据库:
create database gl;
create database gl character set gbk;
use gl
drop database gl
执行MySQL脚本:
1.新建一个文件createTable.sql,文件内容如下:
create table hello(
id int auto_increment primary key,
name char(8) not null,
sex char(4) not null,
tel char(13) default '-' unique
或 unique(tel)
或 constraint tel_uni (tel)
);
2.新打开一个cmd,输入如下命令:
mysql -D gl -u root -p < createTable.sql
或mysql -D gl -u root -p < Q:\createTable.sql (注:带完整路径)
插入数据:
insert [into ] hello [(column1,column2,column3......)] values(value1,value2,value3......)
其中[]的内容是可选的,可写可不写,
insert into hello values(1,'gl','man','11020200');
insert into hello(name,sex) values('gl','man');
查询表时一般运算符: < <= > >= = !=
扩展运算符: in [not] null in like
还可以对查询条件用 and 或 or 进行组合
更新表数据:
update hello set columu=新值 where 更新条件
删除表数据:
delete from hello where 删除条件
truncate hello (注:用来删除整个表的数据,而且速度更快)
创建表后对表进行修改:
添加列:alter table hello add new_column 数据类型 [ after 位置 ]
修改列:alter table hello change 列名称 列新名称 数据类型
修改列属性:alter table hello modify 列名称 数据类型 [default] [first | after 列名称]
删除列:alter table hello drop 列名称
重命名表:alter table hello rename 新表明
删除表: drop table hello
删除整个数据库: drop database gl
修改MySQL用户名密码:
方式1:mysqladmin -u用户名 -p旧密码 password 新密码 (注:-p和旧密码之间不能有空格)
方式2:set password for root@localhost = password('新密码')
添加约束条件:
alter table hello add unique(name,sex);
alter table hello modify name varchar(20) unique;
删除约束:
alter table hello drop index test_uni
查询条件中下划线( ”_“ )代表的是一个字符。
如果想要匹配下划线_或者百分号%的话,就要使用反斜线\来进行转义,
不用配置文件:
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gl","root","密码");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from hello ");
while(rs.next()){
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)
+"\t"+rs.getString(4)+"\t"+rs.getString(5));
}
或(使用配置文件):
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
Properties prop = new Properties();
prop.load(new FileInputStream(paramFile));
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
pass = prop.getProperty("pass");
}
public void createConnction(String sql) throws Exception{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,pass);
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
}
public static void main(String[] args) throws Exception{
ConnectionByProperties cp = new ConnectionByProperties();
cp.initParam("Q:\\mysql.ini");
cp.createConnction("update hello set tel = '156' where name='gl'");
System.out.println("OK");
}
使用占位符:
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String fileParam) throws Exception{
Properties prop = new Properties();
prop.load(new FileInputStream(fileParam));
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
pass = prop.getProperty("pass");
}
public void testStatement() throws Exception{
long start = System.currentTimeMillis();
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,pass);
Statement stmt = conn.createStatement();
for(int i=0; i<100; i++){
stmt.executeUpdate("insert into hello(name,sex,tel) values('gl"+i+"','man'"+","+i+")");
}
System.out.println("总耗时:" + (System.currentTimeMillis()-start));
}
public void testPreparedStatement() throws Exception{
long start = System.currentTimeMillis();
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,pass);
PreparedStatement pt = conn.prepareStatement("insert into hello(name,sex,tel) values(?,'man',?)");
for(int i=0; i<100; i++){
pt.setString(1, "guolei"+i);
pt.setInt(2, i);
pt.executeUpdate();
}
System.out.println("总耗时 :"+(System.currentTimeMillis()-start));
}
public static void main(String[] args) throws Exception{
PreparedStatementTest pt = new PreparedStatementTest();
pt.initParam("Q:\\mysql.ini");
pt.testStatement();
pt.testPreparedStatement();
}
调用存储过程:
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String fileParam) throws Exception{
Properties prop = new Properties();
prop.load(new FileInputStream(fileParam));
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
pass = prop.getProperty("pass");
}
public void callProcedure() throws Exception{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,pass);
CallableStatement cstmt = conn.prepareCall("{call add_pro(?,?,?)}");
cstmt.setInt(1, 5);
cstmt.setInt(2, 2);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
System.out.println("最后的结果:" + cstmt.getInt(3));
}
public static void main(String[] args) throws Exception {
CallableStatementTest cst = new CallableStatementTest();
cst.initParam("Q:\\mysql.ini");
cst.callProcedure();
}
存储图片,视频等文件到MySQL:
private String driver;
private String url;
private String user;
private String pass;
private Connection conn;
private PreparedStatement ps;
public void initParam(String fileParam) throws Exception{
Properties prop = new Properties();
prop.load(new FileInputStream(fileParam));
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
pass = prop.getProperty("pass");
}
public void createConn() throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pass);
}
public void dealWithImg(String fileName) throws Exception{
File file = new File(fileName);
FileInputStream fileInput = new FileInputStream(file);
ps = conn.prepareStatement("insert into img_table values(?,?,?)");
ps.setInt(1, 1);
ps.setString(2, file.getName());
ps.setBinaryStream(3, fileInput, (int)file.length());
ps.executeUpdate();
ps.close();
fileInput.close();
System.out.println("End !");
ps = conn.prepareStatement("select * from img_table");
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
System.out.println(rs.getBlob(3).length());
}
}
public static void main(String[] args) throws Exception{
BlobTest bt = new BlobTest();
bt.initParam("Q:\\mysql.ini");
bt.createConn();
bt.dealWithImg("Q:\\dog1.jpg");
}
SQL注入:
select * from hello where name = 'gl' and sex = 'man'
此时我们不给name传'gl',而是传: ' or true or '
select * from hello where name = '' or true or '' and sex = 'man'
可以使用preparedStatement来避免这个问题:
PreparedStatement pstmt = conn.prepareStatemnt("select * from hello where name = ? and sex = ?");
pstmt.setString(1,userName);
pstmt.setString(2,userSex);
- MySQL的基本操作
- Mysql 基本操作
- mysql基本操作
- Mysql的基本操作
- Mysql 基本命令操作
- mysql基本操作
- MySQL的基本操作
- MySQL常用操作基本
- MySQL基本操作
- MySQL基本操作
- MySql数据库基本操作
- MySQL 安装 (基本操作)
- MySQL基本操作(控制台)
- mysql 基本操作函数
- MySQL的基本操作
- MySql 基本操作
- mysql操作基本命令
- MySQL 基本操作命令
- ZooKeeper原理及使用
- 使用SQL developer 连接数据库
- 前端代码标准最佳实践:CSS篇
- 安卓帧动画
- 密集匹配之半全局匹配SGBM
- MySQL基本操作
- SQLite入门(一)
- Spring 注解 @ResponseBody
- 【目标识别】深度学习进行目标识别的资源列表
- 简单安全高效的Java计数器
- 前端代码标准最佳实践:javascript篇
- 线程
- pt-query-digest使用
- html5开发入门学习必知的几款主流工具