安装
- data后去掉\
- 缺少.dll组件？
操作数据库
-- tab键的上面,如果你的表名或者字段名是一个特殊字符，就需要带``，让其变成字段
-- 如 SELECT `user` FROM student 从student表中查询user
数据库的字段属性
/* 每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在意义!
id主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
创建数据库表
-- 目标:创建一个school数据库
-- 创建学生表(列，字段) 使用sQL创建
-- 学号int 登录密码varchar(20）姓名,性别varchar(2),出生日期(datetime) ,家庭住址, email
-- 注意点:使用英文()，表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来!
-- 所有的语句后面加,(英文的)，最后一个不用加
-- PRIMARY KEY主键，一般一个表只有一个唯一的主键!
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
其他操作
SHOW CREATE DATABASE school -- 查看创建数据库的语句
-- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
SHOW CREATE TABLE student -- 查看student数据表的定义语句
/*
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
DESC student -- 显示表的结构
- MySQL基础配置之mysql的默认字符编码的设置（my.ini设置字符编码）
一、Windows系统下面
1、中止MySQL服务
2、在MySQL的安装目录下找到my.ini，如果没有就把my-medium.ini复制为一个my.ini即可
3、打开my.ini以后，在[client]和[mysqld]下面均加上default-character-set=utf8，保存并关闭（mysqld中增加如果出错，可以试character-set-server=utf8）
4、启动MySQL服务
修改和删除数据库表
ALTER TABLE teacher RENAME AS teacher1
ALTER TABLE teacher1 ADD id INT(10)
ALTER TABLE teacher1 MODIFY id VARCHAR(10)
ALTER TABLE teacher1 CHANGE id age INT(10)
ALTER TABLE teacher1 DROP age
ALTER TABLE teacher1 RENAME AS teacher
DROP TABLE IF EXISTS teacher
MySQL数据管理
外键
创建外键方式一：
CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用） references 引用
CREATE TABLE `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
创建外键方式二：
CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
DML语言
数据库意义:数据存储，数据管理
DML语言:数据操作语言
-
lnsert
-
update
-
delete
insert 添加数据
-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)
INSERT INTO `grade`(`gradename`) VALUES('大四')
INSERT INTO `grade` VALUES('2','大三')
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')
INSERT INTO `student`(`name`) VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','987654','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','aaaa','男'),('王五','bbb','男'),('王亮','ccc','男')
INSERT INTO `student`
VALUES(6,'小明','ccc','男','2000-01-01','xian','email',4)
语法: insert into 表名([字段名1,字段2,字段3]) values('值1'),('值2'),('值3'),...)
注意事项:
1.字段和字段之间使用 英文逗号 隔开
2.字段是可以省略的，但是后面的值必须要要一一对应，不能少
3.可以同时插入多条数据，VALUES 后面的值，需要使用,隔开即可VALUES (),()
update 修改数据
UPDATE `student` SET `name`='机器人' WHERE id=11;
UPDATE `student` SET `name`='张三';
UPDATE `student` SET `name`='小明',`email`='123456@qq.com' WHERE id=1
UPDATE `student` SET `name`='小m' WHERE id < 3
UPDATE `student` SET `name`='小q' WHERE id != 2
UPDATE `student` SET `name`='aaa' WHERE id BETWEEN 2 AND 5
UPDATE `student` SET `name`='1111' WHERE `name`='小q' AND sex = '女'
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE id=1
delete 删除数据
DELETE FROM `student` WHERE id=1
TRUNCATE `student`
-- 测试DELETE FROM和TRUNCATE的区别
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`) VALUES('11'),('22'),('33')
DELETE FROM `test`
TRUNCATE TABLE `test`
使用DQL查询数据
DQL语言
指定查询字段
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入成绩数据
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号，允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)
VALUES
(1,'高等数学-1',110,1),(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),(6,'C语言-2',110,2),
(7,'C语言-3',100,3),(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
SELECT * FROM student
SELECT * FROM result
SELECT `studentno`,`studentname` FROM student
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS sss
SELECT CONCAT('姓名：',studentname) AS 新的名字 FROM student
-- DISTINCT关键字的使用
SELECT * FROM result
SELECT `studentno` FROM result
SELECT DISTINCT `studentno` FROM result
-- 使用表达式的列
-- 数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成
SELECT VERSION() -- 函数
SELECT 100*5-2 AS 计算结果
SELECT @@auto_increment_increment -- 变量
SELECT `studentno`,`studentresult`+2 AS '提分后' FROM result
where条件语句
SELECT `studentno`,`studentresult` FROM result
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` >=93 AND `studentresult`<=100
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 90 AND 95
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno`!=1002
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`<80
- 模糊查询 ： 比较操作符
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '赵%'
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '赵_'
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '赵__'
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '%明%'
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (1000,1001,1002)
SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('安徽','河南洛阳')
SELECT `studentno`,`studentname` FROM student
WHERE `address`='' OR `address` IS NULL
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NOT NULL
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NULL
连接查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno`=r.`studentno`
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` IS NULL
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
- 自连接
SELECT a.`categoryname` AS '父类别',b.`categoryname` AS '子类别'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
-- join on练习
SELECT `studentno`,`studentname`,`gradename`
FROM student AS s
INNER JOIN grade AS g
ON s.`gradeid`=g.`gradeid`
SELECT `subjectname`,`gradename`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`gradeid`=g.`gradeid`
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='数据库结构-1'
排序和分页
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname` LIKE '数据库结构%'
ORDER BY `studentresult` ASC
LIMIT 5,5
-- 练习
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='Java程序设计-1' AND `studentresult` >95
ORDER BY `studentresult` DESC
LIMIT 0,2
子查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE subjectno = (
SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1'
)
ORDER BY `studentresult` DESC
-- 1
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
WHERE `studentresult`>=80 AND `subjectname`='高等数学-2'
-- 2
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE `studentresult`>=80 AND `subjectno`= (
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'
)
-- 3
SELECT `studentno`,`studentname`
FROM student
WHERE studentno IN (
SELECT studentno FROM result WHERE `studentresult`>=80 AND `subjectno` = (
SELECT `subjectno` FROM `subject` WHERE subjectname='高等数学-2'
)
)
-- exercise
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE `subjectno`=(
SELECT subjectno FROM `subject` WHERE `subjectname`='C语言-1'
)
ORDER BY studentresult DESC
LIMIT 0,2
分组和过滤
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均分>80
MySQL函数
聚合函数
SELECT COUNT(*) FROM student
SELECT COUNT(`studentno`) FROM student
SELECT COUNT(1) FROM student
SELECT COUNT(`borndate`) FROM student
SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result
- MD5 加密
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `testmd5`
VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd = MD5(pwd)
INSERT INTO `testmd5`
VALUES(4,'xiaoming',MD5('123456'))
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
事务
事务实现
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account`(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)
SET autocommit = 0;
START TRANSACTION
UPDATE account SET `money`=money-500 WHERE `name`='A'
UPDATE account SET money = money+500 WHERE `name`='B'
COMMIT;
ROLLBACK;
SET autocommit = 1;
索引
索引分类
基础语法
SHOW INDEX FROM student
ALTER TABLE student ADD FULLTEXT INDEX `studentname`(`studentname`)
EXPLAIN SELECT * FROM student
SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('张')
EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('张')
测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男;1：女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'123456@qq.com',CONCAT('18',FLOOR(RAND()*999999999) + 100000000),
FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.665 sec
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.582 sec
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.597 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- rows 993145
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.003 sec
SELECT * FROM app_user WHERE `name`='用户9999' -- 0 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- rows 1
权限管理
用户管理
CREATE USER xiaoming IDENTIFIED BY '123456'
SET PASSWORD = PASSWORD('123456')
SET PASSWORD FOR xiaoming = PASSWORD('111111')
RENAME USER xiaoming TO xiaoming2
GRANT ALL PRIVILEGES ON *.* TO xiaoming2
SHOW GRANTS FOR xiaoming2 -- GRANT ALL PRIVILEGES ON *.* TO 'xiaoming2'@'%'
SHOW GRANTS FOR root@localhost
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
REVOKE ALL PRIVILEGES ON *.* FROM xiaoming2
DROP USER xiaoming2
JDBC
编写JDBC程序
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
`birthday` DATE
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
SELECT * FROM `users`
package com.lcj.lesson01;
import java.sql.*;
//我的第一个JDBC程序 创建测试
//编写程序从user表中读取数据，并打印在命令行窗口中。
public class JdbcFirstTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//2.用户信息和urL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3.连接成功，数据库对象 Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象 Statement 执行sql的对象
Statement statement = connection.createStatement();
//5．执行SQL的对象去执行SQL，可能存在结果，查看返回结果
String sql = "SELECT * FROM `users`";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("NAME="+resultSet.getObject("NAME"));
System.out.println("PASSWORD="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("===========================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
statement对象
使用jdbc对数据库增删改查
- 把配置类单独放出来 连接jdbc的信息写在代码里 强耦合了 要解耦 放在文件里
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
package com.lcj.lesson02.utils;
//工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try {
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
package com.lcj.lesson02;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert01 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) \n" +
"VALUES(4,'xiaoming','123456','123456@qq.com','2023-01-06')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.lcj.lesson02;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete02 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "DELETE FROM `users` WHERE id = 4";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.lcj.lesson02;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate03 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "UPDATE `users` SET `NAME`='xiaoming' WHERE id = 1";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.lcj.lesson02;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect04 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM `users` WHERE id=1";
rs = st.executeQuery(sql);
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL 注入问题
package com.lcj.lesson02;
//SQL注入
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLzhuru {
public static void main(String[] args) {
//login("xiaoming","123456");
login(" ' or '1=1"," ' or '1=1");
}
public static void login(String username,String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM `users` WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("=============================================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
PreparedStatement对象
PreparedStatement对象
package com.lcj.lesson03;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestInsert01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);
st.setInt(1,4);
st.setString(2,"zhangsan");
st.setString(3,"123456");
st.setString(4,"123456@qq.com");
st.setDate(5,new Date(new java.util.Date().getTime()));
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
package com.lcj.lesson03;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
String sql = "delete from `users` where id = ?";
st = conn.prepareStatement(sql);
st.setInt(1,4);
int i = st.executeUpdate();
if (i > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
package com.lcj.lesson03;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate03 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update `users` set `NAME`=? where id=?";
st = conn.prepareStatement(sql);
st.setString(1,"zhangsan");
st.setInt(2,1);
int i = st.executeUpdate();
if (i > 0) {
System.out.println("更新成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
package com.lcj.lesson03;
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect04 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from `users` where id=?";
st = conn.prepareStatement(sql);
st.setInt(1,2);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
避免SQL 注入
package com.lcj.lesson03;
//避免SQL注入
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQLzhuru05 {
public static void main(String[] args) {
//login("zhangsan","123456");
//login(" ' or '1=1"," ' or '1=1");
login("' ' or '1=1'","' ' or '1=1'");
}
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//PreparedStatement防止SQL注入的本质:把传递进来的参数当做字符
//假设其中存在转义字符,比如'会被直接转义
String sql = "select * from `users` where `NAME`=? and `PASSWORD`=?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("==========================================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
事务
/*创建账户表*/
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
java程序编写
package com.lcj.lesson04;
//事务
//模拟转账成功时的业务场景
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update `account` set `money` = `money`-100 where `NAME`='A';";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update `account` set `money` = `money`+100 where `NAME`='B';";
st = conn.prepareStatement(sql2);
st.executeUpdate();
conn.commit();
System.out.println("成功");
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.lcj.lesson04;
//模拟转账过程中出现异常导致有一部分SQL执行失败后让数据库自动回滚事务（或手动）
import com.lcj.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update `account` set `money` = `money`-100 where `NAME`='A';";
st = conn.prepareStatement(sql1);
st.executeUpdate();
//int i = 1/0;//报错
String sql2 = "update `account` set `money` = `money`+100 where `NAME`='B';";
st = conn.prepareStatement(sql2);
st.executeUpdate();
conn.commit();
System.out.println("成功");
} catch (SQLException e) {
//如果失败 则默认回滚 也可显式写出
/*try {
conn.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}*/
throw new RuntimeException(e);
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
数据库连接池
DBCP数据源
package com.lcj.lesson05.utils;
//工具类
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static{
try {
InputStream is = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(is);
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
package com.lcj.lesson05;
//测试DBCP
import com.lcj.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_DBCP.getConnection();
String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);
st.setInt(1,4);
st.setString(2,"xiaoming");
st.setString(3,"123456");
st.setString(4,"123456@qq.com");
st.setDate(5,new Date(new java.util.Date().getTime()));
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
C3P0
package com.lcj.lesson05.utils;
//工具类
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static{
try {
/*dataSource = new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setUser();
dataSource.setPassword();
dataSource.setJdbcUrl();
dataSource.setMaxPoolSize();
dataSource.setMinPoolSize();
dataSource.setMaxIdleTime();*/
dataSource = new ComboPooledDataSource("MySQL");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
package com.lcj.lesson05;
//测试C3P0
import com.lcj.lesson05.utils.JdbcUtils_C3P0;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestC3P0 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_C3P0.getConnection();
String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);
st.setInt(1,5);
st.setString(2,"xiaohong");
st.setString(3,"123456");
st.setString(4,"123456@qq.com");
st.setDate(5,new Date(new java.util.Date().getTime()));
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JdbcUtils_C3P0.release(conn,st,null);
}
}
}
- 无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变