Mysql
1.基本命令
mysql -u root -p密码 连接数据库
修改用户密码
update mysql.user set authentication_string=password('密码') where user='root' and Host='localhost';
flush privileges; 刷新权限
show databases; 查看所有数据库
use school 切换数据库： use 数据库名
show tables; 查看数据库中所有表
describe student; 显示数据库中所有表的信息
create database westos 创建一个名为westos的数据库
exit; 退出数据库
# 单行注释标识符
/* */ 多行注释
每一个表都必须存在以下五个字段：
- id 主键
version乐观锁- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
修改表
改变目的地 改变的东西是什么
例如：修改表名 ----> 改变的是表 改变的东西是表名
change和modify的区别：
- change用来字段重命名，不能修改字段类型和约束
- modify不用来字段重命名，只能修改字段类型和约束
ALTER TABLE student RENAME AS student1 修改表名
ALTER TABLE student1 ADD age INT(2) 添加表的字段
修改表的字段
ALTER TABLE student1 MODIFY age VARCHAR(4) 修改字段类型---> int（2）变成varchar（4）
ALTER TABLE student1 CHANGE age age1 INT(2) 字段重命名-----> age varchar(4)变成age1 int(2)
ALTER TABLE student1 DROP age1 删除表中字段
DROP TABLE IF NOT EXISTS student1 删除表
2.mysql数据管理
- 数据库外键
//创建表的外键 constraint 约束 references 引用
ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES grade (`gradeid`);
-
DML语言（数据库操作语言）
- 添加
INSERT INTO `teacher` (`name`) VALUES ('ji') 给teacher表中添加字段name的值为ji INSERT INTO teacher (`name`,`pwd`) VALUES ('er','231'),('sd','432') 添加多条值-
修改
操作符 含义 范围 结果 = 等于 2=2 true <>或!= 不等于 2!=2 false > 大于 < 小于 <= 大于等于 >= 小于等于 BETWEND....AND... 什么之间 AND 我和你 5>1and1>2 false OR 我或你 5>1or1>2 true
UPDATE 表名 SET 改变的东西 WHERE 条件 UPDATE teacher SET `name`='wangzi',`sex`='男' WHERE `id`=1-
删除
CREATE TABLE `test`( --创建表 `id` INT(4) NOT NULL AUTO_INCREMENT, `name` VARCHAR(4) NOT NULL DEFAULT '匿名', PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO test (`name`) VALUES ('zhnagsan'),('lisi') --插入数据 DELETE FROM test --删除表 TRUNCATE test --删除表 DELETE FROM test WHERE id=1 --删除id=1的这行数据
delete与truncate区别
相同点：都能删除数据，都不会删除表结构
不同点：truncate 重新设置自增列，计数器归零（下次插入数据从1开始），不会影响事务
-
DQL（数据查询语言）
顺序：
select 去重 要查询的字段 from 表（表和字段可以取别名） xxx join 要连接的表 on 等值判断 xxx--->(inner/left/right) where （具体的值，子查询语句） group by (要通过哪个字段来分组) Having （过滤分组后的信息，条件和where是一样的，位置不同） order by... （通过哪个字段排序） [升序/降序] limit 起始行，页面大小（几条数据）- 指定查询字段
SELECT * FROM student --查询全部学生 SELECT `studentno`,`studentname` FROM student --查询指定字段 SELECT `studentno` AS 学号,`studentname` AS 学生名字 FROM student AS ----别名，给结果起一个名字 AS SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student --函数 concat（a，b）
SELECT DISTINCT studentno FROM result --去重 关键字：distinct
SELECT studentresult+1 AS '提分后成绩' FROM result 学员整体成绩+1
- where条件子句
逻辑运算符：and或者&& or或者|| not或者！
```sql
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>80 AND `studentresult`<90 --查询成绩在80-90之间的学生学号和成绩
SELECT `studentno`,`studentresult` FROM result --查询成绩不等于100的学生学号和成绩
WHERE NOT `studentresult`=100
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult BETWEEN 80 AND 90 --模糊查询（区间）
- 模糊查询
| 运算符 | 语法 | 描述 |
| ----------- | --------------------- | ------------------------------------------------ |
| IS NULL | a IS NULL | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为null，结果为真 |
| BETWEEN | a between b and c | 若在a和b和c之间，结果为真 |
| LIKE | a like b | SQL匹配，如果a匹配b，则结果为真 |
| In | a in （a1,a2,a3....） | 假设a在a1，或者a2.....其中的某一个值中，结果为真 |
SELECT `studentname`,`studentno` FROM student
WHERE `studentname` LIKE '_伟' --通配符：%表示多个字符，_表示一个字符 只有在like的时候才能用
SELECT `studentname`,`studentno` FROM student
WHERE `address` IN ('北京朝阳','广东深圳') --in用法：必须是确定的一个值或者多个值
SELECT `studentname`,`studentno` FROM student
WHERE `borndate` IS NOT NULL --不为空
- 联表查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r -- inner 取并集
ON s.studentno=r.`studentno`
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r -- a right join b 右联接以右表为基准
ON s.studentno=r.`studentno`
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r -- a left join b 左联接以左表为基准
ON s.studentno=r.`studentno`
WHERE studentresult IS NOT NULL
-- 查询参加考试的同学信息：学号，学生姓名，科目名，分数，分数不能为空
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
RIGHT JOIN result r
ON s.studentno=r.`studentno`
LEFT JOIN `subject` AS sub
ON r.`subjectno`=sub.subjectno
WHERE studentresult IS NOT NULL
-
自连接（一张表拆成两张表）
| categoryid | pid | categoryname |
| ---------- | ---- | ------------ |
| 7 | 1 | 信息技术 |
| 6 | 1 | 软件开发 |
| 2 | 3 | 数据库 |
| 8 | 1 | 美术设计 |
| 4 | 3 | web开发 |
| 5 | 5 | ps技术 |
| 3 | 2 | 办公信息 |
父类表：pid=1所对应的categoryid和categoryname
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类表：
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类和子类对应关系（父类表的categoryid=子类表的pid）
| categoryname（父类） | categoryname（子类） |
|---|---|
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
| 信息技术 | 办公信息 |
SELECT a.`categoryname`,b.`categoryname`
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
- 分页和排序
order by 排序 asc 升序 desc 降序
分页：limit 0，5 表示1~5条数据 0起始行数据，5是页面大小（一页有5条数据）
前端公式：pagesize：页面大小，（n-1）*pagesize：第n页起始行数据，n：当前页， 总页数=数据总数/页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
RIGHT JOIN result r
ON s.studentno=r.`studentno`
LEFT JOIN `subject` AS sub
ON r.`subjectno`=sub.subjectno
WHERE studentresult IS NOT NULL AND subjectname='高等数学-1' -- where 条件
ORDER BY studentresult DESC
LIMIT 0,3
练习：查询高等数学-1 课程成绩排名前三的学生，并且分数大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN result r
ON s.studentno=r.`studentno`
INNER JOIN SUBJECT AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='高等数学-1' AND studentresult>=80
ORDER BY studentresult ASC
LIMIT 0,3 -- 排名前三
- 子查询和嵌套查询
SELECT s.`studentno`,`studentname` -- 子查询
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult>=80 AND subjectno=( -- subjectno是两个表合在一起所有的
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1' -- 获得高等数学-1的课程号
)
-- 嵌套查询
SELECT `studentno`,`studentname` --获得学生的学号和姓名
FROM student WHERE studentno IN( --result表里面有学号，学生表里面也有学号
--成绩大于80的学生的学号 课程为高等数学-1的课程号 因为result表里面有课程号
SELECT studentno FROM result WHERE studentresult>80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-1'
)
)
- 聚合函数
统计表中数据，想查询一个表中有多少个记录就用count（）
SELECT COUNT(subjectname) FROM `subject` -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM `subject` -- 不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM `subject` -- 不会忽略null值，本质：计算行数（会更快）
-- 查询不同课程的平均分，最高分，最低分，平均分大于70
-- 核心：根据不同的课程分组
SELECT `subjectname`, -- 平均分 最高分 最低分
AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno` --通过什么字段来分组
HAVING AVG(`studentresult`)>70 --过滤分组必须满足的次要条件
- MD5加密
INSERT INTO textmd5 VALUES (4,'xiaoming',MD5('23123')) -- 插入的时候加密
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM textmd5 WHERE `name`='xiaoming' AND pwd=MD5('23123')
3.事务
原子性：要么都成功，要么都失败。
一致性：事务前后的数据完整性要保持一致
持久性：事务一旦提交则不可逆，被持久化到数据库中。
隔离性：事务的隔离性是多个用户并发访问到数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事物之间要相互隔离
隔离所导致的一些问题：
- 脏读：一个事务读取了另一个事务未提交的数据
- 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，某些场合不对）
- 虚读：在一个事务内读取到了别的事物插入的数据，导致前后读取不一致
测试事务实现转账：
-- 创建shop数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop -- 转换到shop数据库
CREATE TABLE account( -- 创建account表
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 给account表中插入数据
INSERT INTO account (`name`,money) VALUES ('A',2000),('B',10000)
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一个事物（一组事务）
UPDATE account SET money=money-500 WHERE `name`='A' -- A减500
UPDATE account SET money=money+500 WHERE `name`='B' -- B加500
COMMIT; -- 提交事务，就被持久化，就算回滚了也不变
ROLLBACK; -- 回滚,返回到初始状态
SET autocommit=1; -- 恢复默认值(默认)，自动提交
百万数据
DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`, `eamil`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data()
4.三大范式
- 第一范式（1NF）：
- 原子性：保证每一列不可再分
- 第二范式（2NF）：
- 前提：满足第一范式
- 每张表只描述一件事情
- 第三范式（3NF）：
- 前提：满足第一范式，第二范式
- 需要确保数据表中的每一列数据都和主键直接相关，不能简介相关
规范性和性能的问题
关联查询的表不能超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 故意给某些表增加一些冗余字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引、）
5.jdbc程序
package com.zhang.rock;
import java.sql.*;
public class JdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3 mysql：3306
// oralce:1521 jdbc:oracle:thin:@localhost:sid
// 2.用户信息和url student--->数据库
// useUnicode=true&characterEncoding=utf8&useSSL=true";固定不变的
String url = "jdbc:mysql://localhost:3306/student? useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "zy20000229ymy.";
// 3. 连接成功 数据库对象 Connection 代表数据库
// 事务提交：connection.commit();
// 事务回滚：connection.rollback();
Connection connection = DriverManager.getConnection(url,username,password);
// 4. 执行SQL对象 Statement
/* statement.executeQuery(); 查询操作返回ResultSet
statement.execute(); 执行任何SQL
statement.executeUpdate(); 更新，插入，删除 返回一个受影响的行
*/
Statement statement = connection.createStatement();
// 5.执行SQL的对象 去执行SQL（在数据库写语句能查出来复制粘贴）
String sql = "SELECT * FROM teacher";
// 6. 返回的结果集，结果集中封装了我们全部查询出来的结果
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id="+ resultSet.getObject("id"));
System.out.println("pwd="+ resultSet.getObject("pwd"));
System.out.println("name="+ resultSet.getObject("name"));
System.out.println("sex="+ resultSet.getObject("sex"));
System.out.println("=============");
}
// 7. 释放资源
resultSet.close();
statement.close();
connection.close();
}
}
6.数据库写法
- 资源：db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=zy20000229ymy.
- 工具类，获取资源 db.properties：JdbcUtils
package com.zhang.rock01.utils;
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 in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 1.驱动只用加载一次
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
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){
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
- 插入 (删除，更新只需要把SQL语句给换了就行)
package com.zhang.rock01.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); // 获取SQL执行对象
String sql = "INSERT INTO teacher (id,`name`,pwd,sex) VALUES (12,'zhang','12323','女')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
- 查询
package com.zhang.rock01.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SQL
String sql = "select * from teacher where id = 1";
rs = st.executeQuery(sql); //SQL语句和执行SQL对象不一样，剩下的和删除，更新，插入一样
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
