狂神说mysql笔记
1、mysql基本操作Windows-->Mysql5.7打开输入用户名和密码查看数据库：showdatabases；查询所有数据库，记住一定要加分号结尾
1、mysql 基本操作 Windows-->Mysql5.7打开 输入用户名和密码 查看数据库 ：show databases；查询所有数据库，记住一定要加分号结尾 这里必须全部为 英文空格 英文符号 选择 day4_6数据库:use day4_6; 查看这个数据库的所有表: show table ...
1、mysql
基本操作
Windows-->Mysql5.7打开
输入用户名和密码
查看数据库 ：show databases；查询所有数据库，记住一定要加分号结尾
这里必须全部为 英文空格 英文符号
选择 day4_6数据库:use day4_6;
查看这个数据库的所有表: show tables;
创建命名为westos的数据库：create databases westos；
再次查询所有的数据库：show databases；
数据库 语言 crud增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
这里在Navicat premium中操作
2.1、创建数据库
创建一个名叫 westos的数据表：create database westos；
删除数据库
这里创建了一个day4_27的数据库
在旁边输入命令 drop databases day4_27 这里多输入了一个s 操作不成功
当命令行输入drop database day4_27；则删除成功
使用数据库
选择 westos数据库 ：use day4_6;
当数据库是关键字时应该用``两个飘将他包含例如
查看表结构sql语句
show create tbale表名;
粘贴的语句为：
查看数据库
只执行第二行语句 show tables 查看数据库中的所有表
2.2、数据库的类型
数值
tinyint 非常小的数据 1个字节smallint 较小的数据 2个字节mediumint 中等大小的数据 3个字节int 标准的整数 4个字节bigint 较大的数据 8个字节float 单精度浮点数 4个字节double 双精度浮点数 8个字节decimal 字符串形式的浮点数 金融计算字符串
char 字符固定的大小 0-255varchar 可变字符串 0-65535 常量的变量 stringtinytext 微型文本 2^8-1text 文本串 2^16-1 保存大文本时间日期
java.util.Date
data YYYY-MM-DD 日期格式time HH:mm:ss 时间格式datetime YY-MM-DD HH：mm：ss 最常用的格式、timestamp 时间戳 1970.1.1到现在的毫秒数！也较为常用year年份的表示null
空值
2.3、数据库的字段属性（重点）
unsigned：
无符号的整数声明该列不能为负数zerofill：
填充0不足位数的，用0来填充 例如 int（3），输入5 输出结果为005自增：
自动在上一条的记录上+1通常设计在唯一的主键~index,必须是整形类型还可以自定义设置自增的起始值和步长非空：null和 not null
假设设置为not null 如果不给它赋值就会报错null，如果不填写，默认值就是null还有可能为空白2.4、创建数据库表
CREATE TABLE`student`(`id`int(10)NOT NULL AUTO_INCREMENT COMMENT学号,`name`varchar(20)DEFAULT匿名COMMENT姓名,`sex`varchar(10)DEFAULT男COMMENT性别,`pwd`varchar(20)NOT NULL COMMENT密码,`birthday`datetime DEFAULT NULL COMMENT出生日期,PRIMARY KEY(`id`))ENGINE=InnoDBDEFAULT CHARSET=utf8CREATE TABLEifnotexists`student`(// 当表不存在时 创建student数据表// 字段名--id 列类型--int(字节) 默认值--DEFAULT 备注--comment AUTO_INCREMENT, //用逗号隔开AUTO_INCREMENT//表自增`birthday`datetime NOT NULL,//这里需要版本足够高否则会报错 结尾后最后一个字段不加点,)常用命令
show create database student --查看创建数据库的语句 这里student表示数据库
show create table student --查看创建数据库表的语句 这里student表示数据库表
desc student --显示数据库表的结构
2.5、数据表的类型
--关于数据库引擎/*INNODB 默认使用MYISAM 早些年使用的*/MYUSAMINNODB事务支持不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间大小较小较大,约为2倍常规使用的操作：
MYUSAM 节约空间，速度较快INNODB 安全性高，事务的处理，多表多用户操作物理空间位置
MySQL 引擎在物理文件上的区别
InnoDB在数据库中只有一个 *.frm 文件，以及上级目录下的ibdata1文件MYSAM 对应文件*.frm 表结构的定义文件*.MYD 数据文件(data)*.MYI 索引文件(index)设置数据库表的字符集编码
CHARSET=utf8不设置的话会是mysql默认的字符集编码(不支持中文！)
mysql的默认编码是Latin1，不支持中文
在my.ini中配置默认的编码
character-set-server=utf82.6、修改删除表
--修改表名：alter table旧表名RENAMEas新表名ALTER TABLE student RENAMEasstudentD--增加表的字段ALTER TABLE表名(student2)添加(ADD)字段名(age)列属性int(11)ALTER TABLE student2 ADD ageint(11)--修改表的字段(重命名，修改约束)--ALTER TABLE表名MODIFY字段名列属性[]ALTER TABLE studentd MODIFY name VARCHAR(11)--修改约束--ALTER TABLE表名CHANGE旧名称新名称列属性[]ALTER TABLE studentd CHANGE name name1int(1)--字段重命名--结论：change用来字段的重命名，不能修改字段类型和约束--modify不能用来字段的重命名，只能修改字段类型和约束--删除表的字段ALTER TABLE表名drop字段名ALTER TABLE studentd drop email删除
--如果存在student7则删除DROP TABLE IF EXISTS student7所有创建和删除尽量加上判断，以免报错~
注意点：
`` 字段名用这个包裹注释用 -- 或者/**/sql 关键字大小写不敏感，建议使用小写所有的符号全部用英文！3、MySQL数据管理
3.1外键
3.2DML语言（全部记住）
数据库意义：数据存储 ，数据管理
3.3添加
--插入语句（添加）--insertinto表名(列名)VALUES（值1）INSERT INTO `grade`(`gradename`) values(大二)-- 由于主键自增我们可以省略（如果不写表的字段，会一一匹配）-- 插入多个字段insert into `index`(id,xb,age) VALUES(1111,男,20)语法：**insertinto`表名`（`字段名1`，`字段名2`，`字段名3`）VALUES（值1）,（值2）,（值3）**注意事项：1.字段和字段之间使用英文逗号隔开2.字段是可以省略的，但是后面的值必须要对应3.可以同事插入多条数据，values后面的值，需要使用，隔开即可values()),(),()3.4修改
update
--指定条件下，修改特定的信息BETWEEN2and3从2-3行UPDATE`index`set`xm`=海儿whereid=1027;--不指定条件的情况下，会改动表中所有的数据UPDATE`index`set`xm`=小海儿;--修改多个属性需要,逗号隔开UPDATE`index`set`xm`=小小小海儿,`xb`=男,`age`=20whereid=1027;--语法：--update表名setcolum_name=value语法：update 表名 set colnum_name=value,[colnum_name =value,...]where[条件]
注意：
colnum_name 是数据库的列，尽量带上``符号条件，筛选的条件，如果没有指定，则会修改所有的列value，是一个具体的值，也可以是一个变量多个设置的属性之间，使用英文逗号隔开3.5删除
delete 命令
语法：delete from 表名[where 条件]
--delete命令--删除数据（避免这样写，会全部删除）DELETE FROM`student`--删除指定数据DELETE FROM`student3`WHERE id=1;truncate 命令
作用：完全清空一个数据表，表的结构和索引约束都不会变
--清空student6表TRUNCATE`student6`4、DQL查询数据（最重点）
4.1、指定查询字段
--查询全部的学生select字段from表select*fromstudent--查询指定字段select`StudentNo`,`StudentName`fromstudent--别名，给结果起另外一个名称as可以给字段起别名，也可以给表起别名SELECT`StudentNo`as学号,`StudentName`as学生姓名FROM studentass--函数concat(a,b)SELECT CONCAT(姓名：,StudentName)as新名字fromstudent去重 distinct
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
SELECT*fromresult--查询全部的考试成绩select`StudentNo`fromresult--查询有哪些同学参加考试，但发现有重复数据SELECT DISTINCT`StudentNo`FROM result--去重数据库的列 （表达式）
SELECT VERSION()--查询系统版本5.7.13函数SELECT100*2-100as计算结果--用于计算（表达式）SELECT@@auto_increment_increment--查询自增的步长--学院考试成绩+1分查看select`StudentNo`,`StudentResult`+1as加一分后结果FROM result数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量
4.2 where条件子句
--=============where==========================SELECTStudentNo,`StudentResult`fromresult--查询列属性,`列属性`from表名--查询成绩在95-100之间SELECTStudentNo,`StudentResult`FROM result WHEREStudentResult>=80andStudentResult<=100--and和&&SELECTStudentNo,`StudentResult`FROM result WHEREStudentResult>=80&&StudentResult<=100--模糊查询(区间)BETWEEN betweenSELECTStudentNo,`StudentResult`FROM result WHEREStudentResultBETWEEN5and100--除了1000号学生之外的同学的成绩selectStudentNo,`StudentResult`fromresultwhereStudentNo!=1000--方法二notselectStudentNo,`StudentResult`fromresultwherenotStudentNo=1000模糊查询：比较运算符
运算符语法描述is nulla is null当操作符为null，结果为真is not nulla is not null当操作符不为null，结果为真betweena between b and c若a在b和c之间，则结果为真likea like bsql匹配,如果a匹配b，则结果为真ina in(a1,a2,a3...)假设a在a1,或者a2...其中的某一个值中，结果为真--=============模糊查询========================--查询姓赵的同学--like结合%(代表0到任意个字符)_(一个字符)SELECT`StudentNo`,`StudentName`from`student`whereStudentNameLIKE赵%--查询姓赵的同学，名字后面只有一个字的SELECT`StudentNo`,`StudentName`from`student`WHEREStudentNamelike赵_--查询姓赵的同学，名字后面只有两个个字的SELECT`StudentNo`,`StudentName`from`student`WHEREStudentNamelike赵__--查询名字中间有摆字的同学%摆%SELECT`StudentNo`,`StudentName`from`student`WHEREStudentNamelike%摆%--=========in（具体的一个或多个值）==================--查询1001.1002.1003号学员SELECT`StudentNo`,`StudentName`from`student`WHEREStudentNoin(1001,1002,1003)--查询在达州宣汉的学员SELECT`StudentNo`,`StudentName`from`student`WHEREAddressin(四川达州,四川宣汉)--查询地址为空的的学员SELECT`StudentNo`,`StudentName`from`student`WHERE`address`=or`address`isnull4.3、联表查询
join
自连接
父类
categoryidcategoryName2信息技术3软件开发5美术设计子类
pidcategoryidcategoryName34数据库28办公信息36web开发57ps技术操作：查询父类对应子类关系
父类子类信息技术办公信息软件开发数据库软件开发web开发美术设计ps技术--查询父子信息selectc.categorynameas父类,d.categorynameas子类FROM categoryasc,categoryasdWHERE c.categoryid=d.pid--查询学院所属的年级（学号，学生的姓名，年级）SELECTStudentNo,StudentName,GradeNameFROM studentassINNER JOIN grade gon s.GradeId=g.GradeId4.4分页和排序
分页
--为什么要分页--缓解数据库压力，给人的体验更好--分页，每页显示五条数据--语法：limit当前页，页面的大小SELECT s.`StudentNo`,`StudentName`,`SubjectName`,StudentResultFROM student sINNER JOIN`result`rON s.`StudentNo`=r.`StudentNo`INNER JOIN`subject`subON r.`subjectNo`=sub.`subjectNo`WHERESubjectName=数据库结构-1ORDER BYStudentResultASCLIMIT0,45、mysql函数
5.1常用函数
--数学运算SELECT ABS(-10)--绝对值SELECT CEILING(10.2)--向上取整selectFLOOR(10.5)--向x下取整selectRAND()--生成0-1之间的随机数selectSIGN(100)--判断一个数的符号0就是0负数返回-1，整数为1--字符串函数selectCHAR_LENGTH(海摆开摆)--返回字符串长度SELECT CONCAT(海儿,还得是海儿)--拼接字符串selectINSERT(小小海儿,1,2,开摆)--从某个位置开始替换长度SELECT UPPER(avc)--小写转换大写selectLOWER(ABC)--大写转小写selectREPLACE(坚持就能成功,坚持,努力)--匹配要换的字体--查询姓赵的同学，改成海儿SELECT REPLACE(StudentName,赵,海)fromstudentwhereStudentNamelike赵%--时间跟日期函数（记住）SELECT CURRENT_DATE--获取今天日期selectCURDATE()--获取今天日期SELECT now()--获取当前时间精确分秒SELECT LOCALTIME()--本地时间--系统SELECT SYSTEM_USER()SELECT USER()SELECT VERSION()5.2聚合函数（常用）
函数名称描述count（）计数sum（）求和avg（）平均值max（）最大值min（）最小值--=======聚合函数=======--都能够统计表中的数据SELECT COUNT(`Email`)FROM student--count(字段)，会忽略所有的nullnull也会计数SELECT COUNT(*)FROM studentSELECT COUNT(1)FROM studentSELECT SUM(`StudentResult`)as总和fromresultSELECT avg(`StudentResult`)as总和fromresultSELECT max(`StudentResult`)as总和fromresultSELECT min(`StudentResult`)as总和fromresult--查询不同课程的平均分，最高分，最低分，平均分大于80--核心：（根据不同的课程分组）SELECT`SubjectName`,avg(StudentResult)as平均分,max(StudentResult)as最高分,min(StudentResult)as最低分FROM result rINNER JOIN`subject`subon r.`SubjectNo`=sub.SubjectNoGROUP BY r.`SubjectNo`HAVING平均分>805.3 数据库级别MD5加密
什么是MD5?
主要增强算法复杂度 不可逆。
MD5不可逆，具体的MD5是一样的
MD5破解原理,背后有一个字典，MD5加密后的值，加密前的值
CREATE TABLE testmd5(`id`INT(4)notNULL,`name`VARCHAR(20)notnull,`pwd`VARCHAR(50)notnull,PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8--铭文密码INSERT INTO testmd5 VALUES(1,张三,123456),(2,李四,123456),(3,王五,123456)--加密UPDATE testmd5setpwd=MD5(pwd)WHERE id=1UPDATE testmd5setpwd=MD5(pwd)WHERE id!=1--插入时加密INSERT INTO testmd5 VALUES(4,小明,MD5(123456))--如何校验，将用户传递过来的密码，进行md5加密SELECT*FROM testmd5 WHERE`name`=小明andpwd=MD5(123456)6、事务
要么都成功要么都失败
1.sql执行 A给B 转账 A1000->200 B 200
2.sql执行 B收到A转账 A800 ->B400
将一组sql放在一个批次中 去执行~
事务原则：ACID原则 原子性，一致，隔离性，持久性
原子性：要么全部完成，要么全部不完成
一致性：事务前后的数据完整性要保证一致，1000
持久性：事务一旦提交则不可逆，被持久化到数据库中!
隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所千扰，多个并发事务之间要相互隔离
CREATE DATABASE shop CHARACTERsetutf8 COLLATE utf8_general_ciuseshopCREATE TABLE`account`(`id`int(3)notnullauto_increment,`name`VARCHAR(30)notnull,`money`DECIMAL(9,2)notnull,PRIMARY key(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO account(`name`,`money`)VALUES(A,2000.00),(B,1000.00)--金额必须保留两位小数--模拟转账：事务setautocommit=0;--关闭自动提交START TRANSACTION--开启一个事务UPDATE accountsetmoney=money-500WHERE`name`=A--A减500UPDATE accountsetmoney=money+500WHERE`name`=B--B加500COMMIT;--提交事务ROLLBACK;--回滚--navicat需要提交事务才能看到结果setautocommit=1;7、索引
mysql 官方对索引的定义为：索引是帮助mysql 高效获取数据的数据结构。提取句子主干，就可以得到索引的本质:索引是数据结构
索引的分类
主键索引（primary key）主键不可重复唯一索引（unique key）- 避免重复的列出现，唯一索引可以重复，多个列都可以标识位常规索引（key/inedex）全文索引 FULLTEXT--索引的使用--1、在创建表的时候给字段增加索引--2、创建完毕后增加索引SHOW INDEX FROM student--显示所有的索引信息--增加一个全文索引（索引名）列名ALTER TABLE school.student add FULLTEXT INDEX`haibai`(`StudentName`)--explain分析sql执行的状况explainselect*fromstudent;--非全文索引5列EXPLAIN SELECT*fromstudentwhereMATCH(StudentName)against(赵);--全文索引8、权限管理和备份
8.1、用户管理
SQL可视化管理
--创建用户CREATE USER haibai IDENTIFIED BY123456--修改密码(修改当前用户密码)SET PASSWORD=PASSWORD(123456)--修改密码（修改指定用户密码）SET PASSWORD FOR haibai=PASSWORD(123456)--错位重命名RENAME USER原用户名TO新用户名RENAME user haibai to bobohai;--版本不一致这里会报错需要添加@%--正确重命名rename userhaibai@localhosttohaibai2@localhost;--用户授权grant all privileges（全部权限）库，表to用户--all privileges除了给别人授权其他都能干GRANT all PRIVILEGES on*.*to haibai2@localhost--查询权限show grantsfor用户show grantsforhaibai2@localhost--查看指定用户的权限show grantsforroot@localhost--查看root用户权限--撤销权限REVOKE权限，在哪个库，哪个表撤销，给谁撤销REVOKE ALL PRIVILEGES ON*.*FROM haibai2@localhost--删除用户drop user haibai2@localhost8.2、mysql备份
为什么要备份：
保证重要的数据不丢失数据转移mysql数据库备份方式
直接拷贝物理文件在可视化工具上导出使用命令行导出mysqldump 命令行cmd才叫命令行--mysqldump-hlocalhost-uroot-p123456 school student>F:/mysqlwenjian/1.sql--mysqldup-h主机—u用户名-p密码数据库表名>到F盘mysqlwenjian物理磁盘重命名为1.sql--导入多张表--mysqldup-h主机—u用户名-p密码数据库表名1表名2表名3>到F盘mysqlwenjian物理磁盘重命名为1.sql--导入数据库--mysqldump-hlocalhost-uroot-p123456 school>F:/mysqlwenjian/10.sql
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候，就需要设计了
糟糕的数据库设计：
数据冗余，浪费空间数据的插入和删除都会麻烦、异常【避免使用物理外键】程序的性能差良好的数据库设计：
节省内存空间保证数据库的完整性方便开发系统软件开发中，关于数据库的设计
分析需求：分析业务和需要处理的数据库的需求概要设计：设计关系图E-R图设计数据库的步骤：（个人博客）
收集信息，分析需求
用户表（用户登录注销，用户的个人信息，写博客，创建分类)分类表(文章分类，谁创建的)文章表(文章的信息)评论表友链表(友链信息)自定义表（系统信息，某个关键的字，或者一些主字段)key : value说说表(发表心情.. id... content....create_time)标识实体（把需求落地到每个字段）
标识实体 之间的关系
写博客：user--> blog创建分类：user -> category关注：user-> user9.2三大范式
为什么需要数据规范化
信息重复更新异常插入异常无法正常显示信息删除异常丢失有效的信息范式
第一范式（1NF）
原子性：保证每个列不可以再分
第二范式（2NF）
前提：满足第一范式
每张表只表示一件事
第三范式（3NF）
前提：满足第一范式
前提：满足第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范性 和 性能的问题
关联查询的表不要超过三张
考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要在规范性能的问题的时候，需要适当考虑一下规范性！故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）故意增加一些计算列（从大数据量降低为小数据量的查询：索引）10、JDBC
10.1数据库驱动
驱动：声卡、显卡、数据库
10.2 JDBC
Sun公司为了简化开发人员对数据库的统一的操作，提供了一个（java操作数据库）规范，JDBC
这些规范的实现有具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC的接口操作即可
10.3、第一个JDBC程序
创建测试数据库
1.创建一个普通项目
2.导入数据库驱动
步骤总结：
1、加载驱动
2、链接数据库DriverManager
3、执行sql的对象
4、获得返回的结果集
5、释放链接
//我的第一个jdbc程序publicclassJdbcFirstDemo{publicstaticvoidmain(String[]args)throwsException{//1.加载驱动Class.forName("com.mysql.jdbc.Driver");//2.用户信息和urlStringurl="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";Stringusername="root";Stringpassword="123456";//3.链接成功，数据库对象Connectionconnection=DriverManager.getConnection(url,username,password);//4执行SQL的对象 statementStatementstatement=connection.createStatement();//5.执行sql的对象去执行sqlStringsql="select *from users";ResultSetresultSet=statement.executeQuery(sql);while(resultSet.next()){System.out.println("id="+resultSet.getObject("id"));System.out.println("NAME="+resultSet.getObject("NAME"));System.out.println("PASSWORD="+resultSet.getObject("PASSWORD"));System.out.println("email="+resultSet.getObject("email"));System.out.println("birthday="+resultSet.getObject("birthday"));}resultSet.close();statement.close();connection.close();}}步骤总结：
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象Statement
4、获得返回的结果集
5、释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver(0));Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动Connectionconnection=DriverManager.getConnection(url,username,password);//connection 代表数据库//数据库设置自动提交//事务提交//事务滚回resultSet.close();statement.close();connection.close();URL
Stringurl="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";//mysql --3306//协议：//主机地址：端口号/数据库名？参数1&参数2&参数3// oralce --1521// jdcbc：oracle：thin：@localhost：1521：sid10.4、statement对象
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
statement对象的executeupdate方法，用于向数据库发送增、删、改的sql语句，executeupdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发送了变化）。
statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询的结果的ResultSet对象
CRUD操作-create
使用executeUpdate（String sql）方法完成数据添加操作，示列操作：
Statementst=com.createStement();Stringsql="insert into user(...) values(...)";intnum=st.executeUpdate(sql);if(num>0){System.out.println("插入成功！！！");}CRUD操作-delete
使用executeUpdate（String sql）方法完成数据删除操作，示列操作：
Statementst=com.createStement();Stringsql="delete from user where id=1";intnum=st.executeUpdate(sql);if(num>0){System.out.println("删除成功"！！！");}CRUD操作-update
使用executeUpdate（String sql）方法完成数据修改操作，示列操作：
Statementst=com.createStement();Stringsql="update user set name= where name= ";intnum=st.executeUpdate(sql);if(num>0){System.out.println("修改成功"！！！");}CRUD操作-read
使用executeQuery（String sql）方法完成数据修改操作，示列操作:
Statementst=com.createStement();Stringsql="select * from user where id=1 ";intnum=st.executeUpdate(sql);while(rs.next()){// 根据获取列数据类型，分别调用rs的相应方法映射到java对象中}10.5、使用idea链接数据库
点击database，选择+号，再点击date source选择mysql
