初始mysql
2022/3/21 2:28:09
本文主要是介绍初始mysql，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.1连接数据库
mysql -u root -p
-- 表示单行注释
/**/ 表示多行注释
show databases; 查看所有的数据库
mysql> use school; 切换数据库 use数据库名
show tables； 查看数据库中的所有表
describe student; 显示数据库中所有的表的信息
creat database westos; 创建一个数据库
exit； 退出
DDL 数据库定义语言
DML 操作
DQL 查询
DCL 控制
2. 操作数据库
创建数据库
CREATE DATABASE IF NOT EXISTS westos;
删除数据库
DROP DATABASE IF EXISTS westos;
使用数据库
USE school
查看数据库
SHOW DATABASES
2.1 数据库的列类型
时间日期
-
data： YYY-MM-DD 日期格式
-
time： HH:MMM:SS 时间格式
-
datatime： YYY-MM-DD HH:MMM:SS 最常用的时间格式
-
timestamp 时间戳， 1970.1.1到现在的毫秒数 也较为常用
-
year ：年份表示
拓展
/*每一个表，都必须存在以下5个字段！未来做项目用的，表示一个记录存在意义*/ id 主键 version 乐观锁 is_delete 伪删除 gmt_create 创建时间 gmt_update 修改时间
/* 注意点：使用英文(),表的名称和字段尽量使用``括起来 AUTO_INCREMENT 自增 字符串使用‘’阔气起来 所有的语句后面加英文的, 最后一个可以不用加 */ 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 '出生日期', `addres` 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 --显示表的结构
3. mysql数据库管理
3.1 外键
方式一。在创建表的时候，增加约束
CREATE TABLE `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 --第二步：给这个外键添加约束（执行引用）reference 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 '出生日期', `gradeid` INT(10) NOT NULL COMMENT '学生的年级', `addres` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址', `email` VARCHAR(50) DEFAULT 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 `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 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 '出生日期', `gradeid` INT(10) NOT NULL COMMENT '学生的年级', `addres` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`); /* 公式： ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(哪个字段); */
以上的操作都是物理外键，数据库级别的外键，我们不建议使用（避免数据库过多造成困扰）
最佳实践
-
数据库就是单纯的表，知用了存数据，只有行（数据）和列（字段）
-
我们使用多张表的数据，想使用外键（程序去实现）
3.2 插入
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`,`addres`) VALUE ('张三','aaaaaaa','女','12','重庆')
INSERT INTO `student` VALUE ('2','李四','1125699','男','1997-08-25','11','重庆','123456789@qq.com')
INSERT INTO `student`(`name`,`sex`,`gradeid`,`addres`) VALUE ('王麻子','男','15','北京'),('张张','女','15','四川')
3.3 修改
UPDATE `student` SET `name`='王五' WHERE `sex`='男' AND `addres`='重庆' UPDATE `student` SET `addres`='日本' WHERE `name`='张张' OR `sex`='男' UPDATE `student` SET `id`='7' WHERE `name`='张张'
3.4 删除
DELETE FROM `student` WHERE `id`='1' --删除数据库 TRUNCATE TABLE`student` --自增会归零 DELETE FROM `student` --不会影响自增
delete和truncate的区别
相同：都能删除数据，都不会删除表结构
不同：TRUNCATE:重新设置自增列，计数器会归零，不会影响事务
了解：delete删除的问题 ，重启数据库，现象
innoDB:自增列会从1开始（存在内存当中，断电即失）
MYISAM：继续从上一个自增量开始（存在文件中，不会丢失）
4. DQL查询数据
4.1 指定查询字段
--选择学生表的所有信息
SELECT * FROM `student`
--查询指定字段
SELECT `studentno`,`sex` FROM `student`
--别名，给结果起一个名字
SELECT `studentno` AS 学号,`sex` AS 性别 FROM `student`
--函数concal（a,b）
SELECT CONCAT('姓名：',`studentname`)AS 新名字 FROM `student`
去重 distinct
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
SELECT DISTINCT `studentNO` FROM result
4.2联表查询
SELECT s.`studentno`,`studentname`,sub.`subjectname`,`studentresult`,g.`gradeid`,g.`gradename` FROM student s INNER JOIN result r ON s.`studentno`=r.`studentno` INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` INNER JOIN `grade` g ON g.`gradeid`=sub.`gradeid`
4.3 内连接
自己的表和自己的表连接，核心：一张表分为两张一样的表即可
CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT 'id', `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', PRIMARY KEY (`categoryid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库');
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发');
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');
SELECT ca.`categoryname`,ab.`categoryname`
FROM category AS ca,category AS ab
WHERE ca.`categoryid`=ab.`pid`
##4.4 分页和排序
分页：LIMIT a,b
a表示查询起始下标，一般从0开始
b表示一页里面有多少个数据
SELECT * FROM `subject` LIMIT 0,5
4.4 分组查询
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分 , MIN(`studentresult`) AS 最高分 FROM result r INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` GROUP BY sub.subjectname HAVING 平均分>=60
5. 事务
事务原则：ACID原则 原子性，一致性，隔离性，持久性
原子性（Astomicity）
要么成功，要么失败
一致性（Consistency）
事务前后的数据完整性要保持一致
持久性（Durability）
事务一旦提交则不可逆，被持久化到数据库中
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同，（这个不一定是错误，只是某些场合不对）
虚读（幻读）：
是指在一个事务内读取到了别人的事务插入的数据，导致前后读取不一致。
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT `account`(`name`,`money`) VALUE ('zhangsan',10000.00),('lisi',20000.00)
--======模拟转账事务==========
SET autocommit = 0 ; -- 关闭自动
START TRANSACTION; -- 开启一个事务
UPDATE account SET money=money-500 WHERE id=1
UPDATE account SET money=money+500 WHERE id=2
COMMIT -- 提交事务
ROLLBACK -- 回滚
SET autoommit = 1; -- 恢复默认值
6. 索引
6.1 索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
-
主键索引（PRIMARY KEY）
-
唯一的标识，主键不可重复，只能有一个列作为主键
-
-
唯一索引（UNIQUE KEY）
-
避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
-
-
常规索引（KEY/INDEX）
-
默认的，index，key关键字来设置
-
-
全文索引（FULLTEXT）
-
在特定的数据库引擎下才有， MYISAM
-
快速定位数据
语法：
-
-- 增加索引的两种方式
-- 1 在创建表的时候添加
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`) ;
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘') -- 全文索引
-- 创建索引的第三种方式
-- id_表名_字段名
CREATE INDEX 索引名 ON 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`)
6.2 索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要索引
-
索引一般加在常用来查询的字段上
7. 权限管理和备份
7.1 用户管理
本质：对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER zzh IDENTIFIED BY '123456'
-- 修改密码（当前用户）
SET PASSWORD = PASSWORD （'123456'）
-- 修改指定用户的密码
SET PASSWORD FROM zzh = PASSWORD('123456')
-- 重命名
RENAME USER zzh TO zzh1
-- 用户授权
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO zzh1
-- 查询权限
SHOW GRANTS FOR zzh
-- 撤消权限
REVOKE ALL PRIVILEGES ON *.* FROM zzh
-- 删除用户
DROP USER zzh
7.2 备份
-
1.直接拷贝data文件
-
2.在mysql yog中，选择需要导出的数据库或者表右键备份
-
3.在命令行中备份
mysqldump -h主机 -u用户名 -p密码 数据库名 表名 > 存放的位置 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql //导多张表 mysqldump -h主机 -u用户名 -p密码 数据库名 表名1 表名2 表名3 > 存放的位置 //导出数据库 mysqldump -h主机 -u用户名 -p密码 数据库名 > 存放的位置 //导入 //1.登录的情况下切换到指定的数据库 source 备份文件 //2.直接登录并导出 mysql -u用户名 -p密码 库名 < 备份文件
8. 三大范式
-
第一范式（1NF）
原子性：保证每一列不可再分
-
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事
-
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
-
（规范数据库的设计）
规范性和性能的问题
根据alibaba员工手册：关联查询的表不得超过三张
-
考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑一下规范性！
-
故意给某些表增加一些冗余的字段。（从多表查询变为单表查询）
-
故意增加一些计算列（从大数据量降低为小数据的查询：索引）
8. JDBC
8.1第一个jdbc程序
1.创建测试数据库
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')
2.创建一个普通项目
3.导入数据库驱动
package com.zzh.test01;
import java.sql.*;
public class JdbcFirstDemo {
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=false";
String username="root";
String password="123456";
//3.连接成功 数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行sql的对象 statement
Statement statement = connection.createStatement();
//5.执行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("birthday="+resultSet.getObject("birthday"));
System.out.println("=================================");
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
}
8.2 sql注入问题
package com.zzh.lesson02;
import com.zzh.lesson02.utils.jdbcutils;
import sun.security.mscapi.CPublicKey;
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) throws SQLException {
register(" ' or '1=1"," ' or '1=1");
}
public static void register(String username,String password) throws SQLException {
Connection co =null;
Statement st=null;
ResultSet re = null;
try {
co = jdbcutils.getconnection();
st = co.createStatement();
String sql="SELECT * FROM `users` WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'";
re = st.executeQuery(sql);
while (re.next()){
System.out.println("id="+re.getObject("id"));
System.out.println("name="+re.getObject("NAME"));
System.out.println("password="+re.getObject("PASSWORD"));
System.out.println("email="+re.getObject("email"));
System.out.println("birthday="+re.getObject("birthday"));
System.out.println("======================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcutils.realse(co,st,re);
}
}
}
8.3preparstatment
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class testpreparstatement {
public static void main(String[] args) throws SQLException {
Connection co=null;
PreparedStatement st=null;
ResultSet re=null;
try {
co = jdbcutils.getconnection();
String sql="SELECT * FROM `users` where name=?";
st = co.prepareStatement(sql);
st.setObject(1,"zzh");
re = st.executeQuery();
while (re.next()){
System.out.println("id="+re.getObject("id"));
System.out.println("name="+re.getObject("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcutils.realse(co,st,re);
}
}
}
9.idea操作事务
su.setAutoCommit(false);//关闭数据库的自动提交，自动会开启事务 su.commit//事务完毕，提交事务 //可以在catach语句中显示定义回滚语句，但默认失败就会
这篇关于初始mysql的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
- 2024-11-01部署MySQL集群学习：新手入门教程
- 2024-11-01部署MySQL集群入门：新手必读指南
- 2024-10-23BinLog入门：新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门：MySQL数据库的日志管理指南
