初始mysql

2022/3/21 2:28:09

编程Tag： mysql 数据库 null id 初始 -- Student comment

本文主要是介绍初始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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！