mysql

mysql系列

–输入密码


### 修改用户密码

```shell
update mysql.user set authentication_string=password('xqm@123456') where User='root' and Host='localhost';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION; # 任何主机都能访问数据库

刷新权限

flush privileges;

查询所有数据库

show databases;

使用数据库

mysql> use mysqlstudy  --切换数据库
Database changed

查看使用的数据库的所有表

### show  tables
mysql> show tables;
+----------------------+
| Tables_in_mysqlstudy |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)

查看表里面的数据

# descirbe 表名;
mysql> describe student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

创建一个数据库

create database 数据库名;

单行注释

--
#
/**/

DDL   数据库定义语言
DML   数据库管理语言
DQL   数据库查询语言
DCL   数据库控制语言

5.数据库的列类型和字段属性

列类型

数值

tinyint 十分小的数据占1个字节
smallint 较小的数据 2个字节
mediumint 中等数据 3个字节
int 标准的整数 4个字节
bigint 相当于(long) 8个字节
float 浮点数 4字节
double 8字节
decimal 字符串形式的浮点数(金融计算的时候需要，用于精确浮点数计算)

字符串

char 字符串，固定大小 0-255
varchar 可变字符串，0~65535
tinytext 微型文本 2^8-1
text 文本串 2^16-1

时间日期 java.util.Date

date YYYY-MM-DD
time HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss
timestamp 时间戳 1970.1.1 到现在的毫秒数
year 年份表示

字段属性

无符号(unsigned)：无符号的整数，该列不能声明为负数

零填充(zerofill)：不足的位数使用0来填充，比如int(3) 5-----> 005

自增：上一条记录进行自增长

非空(notNull)：字段的值不能为空

默认：如果不填写值，则全部填充为默认值

6.数据库引擎(INNODB和MYISAM)

	MYISAM	INNODB
事务支持	不支持	支持
数据化锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，事务持久

设置数据库字符集编码

CHARSET=utf8

7.MYSQL数据管理

7.1外键

方式一:在创建表的时候进行约束

/**
定义外键的key
给外键添加约束，(执行引用) references 引用
*/
CREATE TABLE `student`(
  `id` INT(10) NOT NULL AUTO_INCREMENT  COMMENT 'id',
	`name` VARCHAR(50) COMMENT '姓名',
	`age` INT(10) COMMENT '年龄',
	`gradeid` INT(10) NOT NULL  COMMENT '年级id',
	PRIMARY KEY(`id`),
	KEY  `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN  KEY (`gradeid`) REFERENCES `grade` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二:创建完表后进行约束

CREATE TABLE `student`(
  `id` INT(10) NOT NULL AUTO_INCREMENT  COMMENT 'id',
	`name` VARCHAR(50) COMMENT '姓名',
	`age` INT(10) COMMENT '年龄',
	`gradeid` INT(10) NOT NULL  COMMENT '年级id',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE student 
ADD CONSTRAINT `FK_gradeid` FOREIGN  KEY (`gradeid`) REFERENCES `grade` (`id`)

以上的外键都是物理外键，数据库级别的外键，不建议使用，避免数据库过多造成的困扰！！

7.2DML语言(数据库管理语言)

数据库意义：数据存储，数据管理

DML语言：数据操作语言

insert
update
delete

insert

--插入语句
insert into student (id,name,age) values (2,"李四",19),(3,"李四",19).....
INSERT INTO student (`id`,`name`,`age`) VALUES (3,'你好',22)
insert into 表名(字段1,字段2,.....) values (值1,值2....)

update

--修改语句
UPDATE student SET `name`='123' WHERE id=1

where后的操作符

delete

DELETE FROM student  WHERE age=19

truncate命令

作用：完全清空一个数据库表，表的结构、索引和约束不会改变

TRUNCATE student

delete和truncate区别
相同点：都能删除数据，都不会删除表结构
不同点：truncate 重新设置自增列，计数器会归零
       truncate不会影响事务

select

--查询所有
select * from 表名
--查询指定字段名
select id,name,age from student
--别名
select id as '学号',name as '姓名',age as '年龄' from student
--函数
select concat('姓名：',name) as '新姓名' from student
--去重distinct
select distinct name from student,grade where student.id=grade.id
--查询系统版本
select VERSION()
--计算
select 100*3-1 as '计算结果'
--查询自增的步长
SELECT @@Auto_increment_increment

where

SELECT * from student WHERE student.score >= 60 and student.score<=80
SELECT * from student WHERE student.score BETWEEN 60 and 80
SELECT * from student WHERE student.score !=78
SELECT * from student WHERE student.score <>78
SELECT * from student WHERE NOT student.score =78

逻辑运算符

and  &&   逻辑与
or   ||   逻辑或
not  !    逻辑非

--查询姓张的同学
SELECT * from student s where s.name like '张%'
--查询姓张x的同学
SELECT * FROM student s WHERE s.name like '张_'
--查询名字中有张的同学
SELECT * FROM student s WHERE s.name like '%张%'
--in
SELECT * FROM student s WHERE s.name in ('%张%')

模糊查询

is null             为空
is not null         不为空
between             在...之间
like                a like b,a匹配b
in                  a in (a1,a2,a3,....)

联表查询

7种join理论

在这里插入图片描述

1、左连接,查询左表所有数据，即使右表没有匹配的值
select * from a left join b on a.id = b.id
2、右连接，查询右表所有值，即使左表没有匹配的值
select * from a right join b on a.id = b.id
3、左连接之后去除共有部分
select * from a left join b on a.id = b.id where b.id is null
4、内连接，查询左右两表共用的部分
select * from a inner join b on a.id = b.id
5、右连接之后去除共有部分
select * from a right join b on a.id = b.id where a.id is null
6、全连接
select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id
由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
select * from a FULL OUTER JOIN b on a.id = b.id(mysql不支持)
7、全连接之后去除共有部分
select * from a left join b on a.id = b.id where b.id is null union select * from a right join b on a.id = b.id where a.id is null
由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
select * from a FULL OUTER JOIN b on a.id = b.id where a.id is null or b.id is null(mysql不支持)

测试

SELECT * from test1 a LEFT JOIN test2 b ON a.id=b.id

SELECT * from test1 a LEFT JOIN test2 b ON a.id=b.id WHERE b.id IS null

自连接

--一张表当成两张表使用
select b.* from student a ,student b where a.n ame='张三' and a.age<b.age order by b.score

分页和排序

排序order by
排序：升序ASC  降序DESC
SELECT * FROM `test1` ORDER BY id DESC

分页limit 
limit 起始下标页，页面大小
SELECT * FROM `test1` ORDER BY id DESC limit 0,5

子查询和嵌套查询

where 条件里面嵌套一个查询语句
select studentNo ,subjectNo from student
where subjectNo=(select subjectNo from grade where subjectName='数据结构')

函数

SELECT ABS(-9)   --绝对值函数
SELECT CEILING(9.6)  --向上取整
SELECT FLOOR(9.6)    --向下取整
SELECT RAND()   --返回0到1之间随机数
SELECT SIGN(8)  --返回数的符号-1还是1还是0
SELECT CHAR_LENGTH('helloworld')   --返回字符串的长度
SELECT CONCAT('hello','world')   --返回连接的字符串
SELECT INSERT('hello',3,2,'world')  --heworldo，从第三个字符开始替换2个字符为world
SELECT LOWER('HELLOWORLD')    --转换成小写
SELECT UPPER('helloworld')    --转换成大写
SELECT INSTR('helloworld','l')  --3,第一次出现子串的位置
SELECT REPLACE('坚持就能成功','坚持','努力') --替换子串
SELECT REVERSE('123456789')  --反转字符串
--时间和日期函数
SELECT CURRENT_DATE()  --获取当前时间
SELECT CURDATE()       --获取当前时间
SELECT NOW()        --2021-02-22 14:31:38 获取当前时间
SELECT LOCALTIME()  --2021-02-22 14:31:59 获取本地时间
SELECT SYSDATE()    --返回系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
--系统
SELECT SYSTEM_USER()   --当前系统用户

聚合函数

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

-- 聚合函数
SELECT COUNT(*) from student        -- 不会忽略null值
SELECT COUNT(1) from student        -- 不会忽略null值
SELECT COUNT(gradeid) from student  -- 会忽略所有的null值
-- 如果表只有一个字段，则count(*)效率最高
-- 如果有主键，则count(主键)执行效率最高
-- 如果列名不为主键，则count(1)比count(列名)效率高
-- 如果表有多个列，且没有主键，则count(1)比count(*)效率高

分组
group by 
having	
select subject,avg(studentResult) as '平均分',MAX(studentResult) AS '最高分' from result r INNER JOIN subject j ON r.subjectNo=j.subjectNo GROUP BY r.subjectNo  having '平均分'>80

md5加密

-- 测试MD5
CREATE TABLE `testMD5`(
id INT(4) NOT NULL ,
name VARCHAR(20),
pwd VARCHAR(20),
PRIMARY KEY(`id`)
)
-- 明文
INSERT into testMD5(id,name,pwd) VALUES (1,'张三','123456'),
(2,'李四','123456'),(3,'王五','123456'),(4,'赵六','123456'),(5,'楚七','123456'),(6,'沈八','123456'),(7,'钱九','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
-- 加密所有的
UPDATE testmd5 SET pwd=MD5(pwd) 
-- 插入的时候加密
INSERT INTO testmd5 VALUES(8,'加密',MD5('123456'))
-- 校验：将用户输入的密码再次进行md5加密，比较数据库的密码，如果相同，则正确
SELECT * from testmd5 where pwd=MD5('123456')

总结

7.3事务

事务：要么都成功，要么都失败；要么都执行，要么都不执行。

事务原则：ACID 原子性 一致性 隔离性 持久性
原子性（atomicity)
一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性。
一致性（consistency)
事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
隔离性（isolation）
事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。
持久性（durability）
一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态。

7.4索引

官方定义：索引(index)是帮助MYSQL高效获取数据的数据结构。

索引分类：

主键索引（primary key）
- 唯一标识，不可重复
唯一索引（UNIQUE KEY）
- 避免重复的列出现
常规索引（key）
全文索引（FullText）

--显示表的索引信息
show index from student

7.5 关系型数据库的三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

7.6 JDBC

本地存储的地址：C:\Users\asus.m2\repository\mysql\mysql-connector-java\5.1.30

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
//        Class.forName("com.mysql.jdbc.Driver");
        //2.连接用户
        String url="jdbc:mysql://localhost:3306/mysqlstudy?useSSL=true&serverTimezone=UTC&useUnicode=true";
        String useName="root";
        String password="xqm123456";
        Connection connection = DriverManager.getConnection(url, useName, password);
        //3.执行sql的对象 statement   | prepareStatement
        Statement statement = connection.createStatement();
        //4.执行sql
        String sql="SELECT * FROM student";
        // 查询  返回结果集
        ResultSet resultSet = statement.executeQuery(sql);
//        statement.execute(); //执行任何sql语句
//        statement.executeUpdate();   //更新、删除、插入，返回一个受影响的行数
        // 输出
        while(resultSet.next()){
            System.out.println("id:"+resultSet.getObject("id"));
            System.out.println("name:"+resultSet.getObject("name"));
            System.out.println("age:"+resultSet.getObject("age"));
            System.out.println("gradeid:"+resultSet.getObject("gradeid"));
            System.out.println("score:"+resultSet.getObject("score"));
        }

        //5.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

statement对象

jdbc中statement对象用于向数据库发送SQL语句
Statement对象的executeUpdate()方法，用于向数据库发送增、删、改的sql语句，executeUpdate()执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement对象的executeQuery()方法用于向数据库发送查询语句，返回代表查询结果的ResultSet对象。

// 查询
String sql="SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql);
     while(resultSet.next()){
          System.out.println("id:"+resultSet.getLong("id"));
          System.out.println("name:"+resultSet.getString("name"));
          System.out.println("age:"+resultSet.getInt("age"));
          System.out.println("gradeid:"+resultSet.getObject("gradeid"));
          System.out.println("score:"+resultSet.getObject("score"));
          System.out.println("================================");
      }

// 插入
Statement st=connection.createStatement();
String sql_create="insert into student (id,name,age,score) values (8,'rose',16,77)";
int num=st.executeUpdate(sql_create);
  if(num>0){
     System.out.println("插入成功");
  }

// 更新
Statement st=connection.createStatement();
String sql_update="update student set name='rose123' where id =8";
int num=st.executeUpdate(sql_update);
if(num>0){
    System.out.println("更新成功");
}

// 删除
Statement st=connection.createStatement();
String sql_delete="delete from student where id=8";
int num=st.executeUpdate(sql_delete);
        if(num>0){
            System.out.println("删除成功");
        }

如果觉得文章对你有用，请随意赞赏

mysql

http://xkzx.work/archives/mysqlmd

作者

星空之下

发布于

2022-04-07

更新于

2022-09-25

许可协议

CC BY 4.0