MySQL学习(完)

安装MySql

1、解压zip

2、把这个包放到电脑环境目录下

3、配置环境变量

4、新建mysql配置文件ini

[mysqlId]
basedir=F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\
datadir=  
port=3306
skip-grant-tables

5、启动管理员模式下的CMD，运行所有的命令

6、安装mysql服务 mysqld -install

7、初始化数据库 mysqld --initialize-insecure --user=mysql

8、启动mysql ，进去修改密码 net start mysql

9、进入mysql，通过命令行

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

10、刷新权限 flush privileges;

11、修改my.ini文件删除最后一句skip-grant-tables

12、重启mysql即可正常使用

net stop mysql

net start mysql

13、进入mysql

mysql -u root -p（p后面不要空格）

步骤流程：

C:\WINDOWS\system32>cd /d F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin

F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>mysqld -install
Service successfully installed.

F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>mysqld --initialize-insecure --user=mysql

F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


F:\environment\mysql-5.7.10-winx64\mysql-5.7.10-winx64\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Navicat

创建数据库

连接数据库

命令行连接

mysql -uroot -p   --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改密码
flush privileges; --刷新权限
-----------------------------------------------
--所有的语句都使用;结尾
show databases; --查看所有的数据库
use school --切换数据库 use 数据库名
show tables; --查看数据库中的所有表
describe student; --显示数据库中表的信息
create database market; --创建一个数据库
exit; --退出连接
/**/ sql的多行注释
-- 单行注释

操作数据库

mysql关键字不区分大小写

创建数据库

CREATE DATABASE IF NOT EXISTS market

删除数据库

DROP DATABASE IF EXISTS market

使用数据库

USE school --如果表名或者字段名是一个特殊字符，就需要带``如`school`

查看数据库

SHOW DATABASES

列的数据类型

数值

• tinyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• midiumint 中等大小的数据 3个字节

• int 标准的整数 4个字节

• bigint 较大的数据 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节

• decimal 字符串形式的浮点数 金融计算的时候一般使用decimal

字符串

• char 字符串固定大小的 0~255

• varchar 可变字符串 0~65535

• tinytext 微型文本 2^8-1

• text 文本串 2^16-1 保存大文本

时间日期

• date YYYY-MM-DD 日期格式

• time HH:mm:ss 时间格式

• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

• timestamp 时间戳 1970.1.1到现在的毫秒数 也较为常用

• year 年份表示

null

• 没有值，未知

• 注意，不要使用NULL进行运算，结果为NULL

数据库的字段属性

Unsigned

• 无符号的整数

• 声明了该列不能声明为负数

zerofill

• 0填充

• 不足的位数，使用0来填充 int(3) 则5--->005

自增

• 通常理解为自增，自动在上一条记录的基础上+1（默认）

• 通常用来设计唯一的逐渐，index，必须是整数类型

• 可以自定义设计逐渐自增的起始值和步长

非空 not null

• 假设设置为not null,如果不给它赋值，就会报错

• NULL，如果不填写值，默认就是null

默认

• 设置默认的值

创建数据库表

--表的名称和字段尽量使用``括起来
--字符串使用单引号括起来
--所有的语句后面加，（英文的），最后一个不加
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL  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

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student  --查看student表的定义语句
DESC student              --显示表的结构

数据表的类型

关于数据库引擎

INNODB 默认使用

MYISAM 早些年使用

修改删除表

ALTER TABLE student RENAME AS student1 --修改表名
ALTER TABLE student1 ADD age INT(11)   --增加表的字段
ALTER TABLE student1 MODIFY age VARCHAR(11)--修改表的字段（重命名，修改约束）
ALTER TABLE student1 CHANGE age age1 INT(1)--字段重命名
ALTER TABLE student1 DROP age1 --删除表的字段
DROP TABLE  IF EXISTS student1  --删除表

所有的创建和删除操作尽量加上判断，以免报错

注意点

• 字段名，使用``包裹

• 注释 -- 或/**/

• sql关键字大小写敏感，建议写小写

• 所有的符号用英文

外键

创建表成功后添加外键约束

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY REFERENCES 哪个表（哪个字段）

以上的操作都是物理外键，数据库级别的外键，不建议使用

DML-添加

insert into 表名([字段名1，字段名2，字段名3])values('值1'),('值2'),('值3')

DML-修改

UPDATE `student` SET `name`='kis'，`email`='45454@qq.com' WHERE id=1;

DML-删除

delete from 表名 where 条件

TRUNCATE 清空表

truncate `student`

delete和truncate都不会删除表的结构

不同：

1. TRUNCATE 重新设置 自增列和计数器会归零

2. TRUNCATE 不会影响事务

 

DQL查询数据

--查询全部的学生 
SELECT * FROM student
--查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
--别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM Student AS s
--函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM Student

去重及数据库的表达式

SELECT DISTINCT `StudentNo` FROM result --发现重复数据，去重
SELECT VERSION() --查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 --用来计算（表达式）
SELECT @@auto_increment_increment --查询自增的步长（变量）
--学员考试成绩+1分后查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

模糊查询

 

 

运算符	语法	描述
BETWEEN	Aa between b and c	若a在b和c之间，则结果为真
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
Like	a like b	SQL匹配，如果a匹配b，则结果为真
In	a in(a1,a2,a3...)	假设a在a1或者a2...其中的某一个值中，结果为真

 

联表查询

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studentNo

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo=r.studentNo

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接

--查父子信息，把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

分页和排序

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 ='数据库结构'
ORDER BY StudentResult ASC
LIMIT 5,5

--第一页 limit 0,5 (1-1)*5
--第二页 limit 5，5 (2-1)*5
--第三页 limit 10,5 (3-1)*5
--第N页  limit 0，5  (n-1)&pageSize,pageSize  【起始值，页面大小】

MySQL常用函数

SELECT ABS(-8) --绝对值
    SELECT CEILING(9.4)--向上取整
    SELECT FLOOR(9.4)  --向下取整
    SELECT RAND() --返回一个0~1之间的随机数
    SELECT SIGN(-10) --判断一个数的符号，负数返回-1，整数返回1
    
    SELECT CHAR_LENGTH('咩咩咩')  --字符串长度
    SELECT CONCAT('咩咩咩','嘎嘎嘎') --拼接字符串
    SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') --查询，从某个位置开始替换某个长度
    SELECT LOWER('KHKJHJK') --小写字母
    SELECT UPPER('gjkghjkg') --大写字母
    SELECT INSTR('kuhjj','k') --返回第一次出现的子串的索引
    SELECT REPLACE('坚持就能成功','坚持','努力') --替换出现的指定字符串
    SELECT SUBSTR('坚持就能成功',4,6) --返回指定的子字符串
    SELECT REVERSE('坚持就能成功') --反转
    --时间和日期函数（记住）
    SELECT CURRENT_DATE --获取当前日期
    SELECT CURDATE() --获取当前日期
    SELECT NOW() --获取当前的时间
    SELECT LOCALTIME() --本地时间
    SELECT SYSDATE() --系统时间
    SELECT YEAR(NOW())
    SELECT MONTH(NOW())
    SELECT DAY(NOW())
    SELECT MINUTE(NOW())
    SELECT SECOND(NOW())
    --系统
    SELECT SYSTEM_USER()
    SELECT USER()
    SELECT VERSION()

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

 

SELECT COUNT(studentname) FROM student; --count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM student; --不会忽略null值
SELECT COUNT(1) FROM result;  --不会忽略null值

数据库级别的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','1313'),(3,'wangwu','43245')
--加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码
--插入时加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

事务

--mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启（默认的）*/
--手动处理事务
--事务开启
START TRANSACTION --标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT XX
--提交：持久化（成功）
COMMIT 
--回滚：回到原来的样子（失败）
ROLLBACK
--事务结束
SET autocommit = 1 --开启自动提交

--了解
SAVEPOINT  保存点名 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 --回滚到保存点
RELEASE SAVEPOINT 保存点名 --撤销保存点

索引

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引（PRIMARY KEY）

唯一的标志，主键不可重复，只能有一个列作为主键

• 唯一索引（UNIQUE KEY）

避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引（KEY/INDEX）

默认的，index 、key关键字来设置

• 全文索引（FULLTEXT）

在特定的数据库引擎下才有，MYISAM

快速定位数据

--显示所有的索引信息
SHOW INDEX FROM stduent
--增加一个全文索引  (索引名)  列名
ALTER TABLE school.student ADD FULLTEXT  INDEX `studentName`(`studentName`);
--EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; --非全文索引

数据库用户管理

--创建用户
CREATE USER miaomiao IDENTIFIED BY'123456'
--修改密码（修改指定用户密码）
SET PASSWORD FOR miaomiao = PASSWORD('2453435')
--重命名
RENAME USER miaomiao TO wangwang 
--用户授权(全部的库，全部的表)
GRANT ALL PRIVILEGES ON *.* TO wangwang

--查询权限
SHOW GRANTS FOR wangwang
SHOW GRANTS FOR root@localhost
--撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM wangwang

三大范式

第一范式（1NF）

• 原子性：保证每一列不可再分

第二范式（2NF）

• 前提：满足第一范式

• 每张表只描述一件事情

第三范式（3NF）

• 前提：满足第一范式和第二范式

• 第三范式需要确保数据表中的每一列数据都和主键直接相关，不能间接相关

规范性和性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本、用户体验）数据库的性能更加重要

• 在规范性能的问题的时候，需要适当考虑一下规范性

• 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）

• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

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
);

 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')

1、创建一个普通项目

2、导入数据库驱动

3、编写测试代码

import java.sql.*;

//我的第一个jdbc程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
          Class.forName("com.mysql.jdbc.Driver");
        //用户信息和url
          String  url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
          String username = "root";
          String password = "123456";
        //连接成功，数据库对象 connection代表数据库
        Connection connection=DriverManager.getConnection(url,username,password);
        //执行SQL的对象
        Statement statement = connection.createStatement();
        //执行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("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
        }
        //释放连接
       resultSet.close();
        statement.close();
        connection.close();
    }
}

statement

提取工具类

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 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");
            //驱动只用加载一次
           Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException 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();
            }
        }
   }
}

//db.properties
driver =com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456

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 users(id,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES('4','miaomiao','13415','4464646@qq.com','2021-06-06')";
             int i=st.executeUpdate(sql);
             if(i>0){
                 System.out.println("插入成功");
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更好

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    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);//预编译SQL,先写sql，然后不执行
            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"wangwang");
            st.setString(3,"1231354");
            st.setString(4,"54545@qq.com");

            st.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i=st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}