MySQL基础知识

MySQL基础知识

数据库基础知识

数据库的常用列类型

数值

• int 标准整数
• decimal 字符串形式 多用于金融计算，不会失去精度（一般不使用double,float)

字符串

• varchar 可变字符串 常用变量String
• text 文本串 常用于保存大文本

时间日期

• datetime yyyy-MM-dd HH:mm:ss
• timestamp 时间戳格式 1970.01.01到现在的毫秒数

拓展：
大型项目建表必须字段：
id	主键
version	乐观锁
id_delete	逻辑删除
gmt_create	创建时间
gmt_update	删除时间

基本语句

show create database 数据库名      --查看建数据库语句
show create table 表名			--查看建表语句
desc 表名							--显示表的结构

模糊查询

• like:
  • % 代表0到任意个字符
  • _ 一个字符

查询地址为空的学生
select * from student where address='' or address is null
查询地址不为空的学生
select * from student where student is not null

limit用法

–语法：limit 起始值，页面的大小

–limit 0,5 1~5

–limit 1,5 2~6

子查询和嵌套查询(由里及外)

子查询
select sutdentNo, subjectNo,studentResult 
from result
where subjectNo= 
	(select subjectNo from student where subjectName = "数据库")
嵌套查询
select studentNo,studentName from student where studentNo in(
	select studentNo from result where studentResult>80 and student =(
	select subjectNo from subject where subjectName = "高等数学")
)

常用函数

--数学运算
SELECT ABS(-8)	--绝对值
SELECT CEILING(9.9)	--向上取整
SELECT FLOOR(9.9)	--向下取整
SELECT RAND()		--返回一个0-1的随机数
SELECT SIGN(-10)	--0返回0 负数返回-1 正数返回1
--字符串运算
SELECT CHAR_LENGTH('字符串')	--字符串长度
SELECT CONCAT('我'，'你')		--拼接字符串
--系统
SELECT SYSTEM_USER()
SELECT VERSION

聚合函数（常用）

常用函数	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
··MAX()	最大值
MIN()	最小值
…	…

SELECT COUNT('name') FROM student 查询name不为空的行数 会忽略null值
SELECT COUNT(*) FROM student 不会忽略null值 本质：计算行数
SELECT COUNT(1) FROM student 不会忽略null值 本质：计算行数

SELECT SUM('result') as 总和 FROM result
SELECT AVG('result') as 总和 FROM result
SELECT MAX('result') as 总和 FROM result
SELECT MIN('result') as 总和 FROM result

#查询不同课程的平均分、最高分、最低分，并且平均分要大于80
SELECT AVG(result) AS 平均分,SUM(result),MAX(result)
GROUP BY subjectNo
HAING 平均分>80   #注意：过滤分组条件只能用having，用where会报错

MD5加密

–明文密码

INSERT INTO testmd5 VALUES(1, ‘zhangsan’, ‘123456’), (2, ‘lisi’, ‘123456’)

–加密

UPDATE testMD5 SET pwd = MD5(pwd)

–插入的时候加密

INSERT INTO testmd5 VALUES(1, ‘zhangsan’, MD5(‘123456’))

–将用户传过来的值进行校验

SELECT * FROM testmd5 WHERE pwd = MD5(‘123456’)

事务

事务：一组操作，要么都成功，要么都失败。

--mysql默认是开启事务的
--手动处理事务
SET autocommit = 0  --关闭自动提交
--事务开启
START TRANSTRACTION 	--标记一个事务的开始，从这个事务之后的sql都在一个事务内

INSERT ***
INSERT ***

--提交：持久化成功
COMMIT
--回滚：回到之前的状态（失败）
ROLLBACK

--事务结束
SET autocommit = 1	--开启自动提交

数据库用户管理

–创建用户

CREATE USER username INDENTIFIED BY ‘123456’

–查看权限

SHOW GRANTS FOR username

数据库备份

Mysql数据库备份的方式

• 直接拷走物理文件（data文件夹下的所有）
• 利用可视化话工具导出成sql
• 使用命令行mysqldump导出

索引

索引的本质就是帮助数据库查询的数据结构。

索引的分类

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

• 主键索引（PRIMARY KEY)

  • 唯一标识

• 唯一索引（UNIQUE KEY)

  • 避免重复列的出现

• 常规索引（KEY/INDEX）

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

• 全文索引（FULLTEXT)

  • 在特定的数据引擎下才有，MyISAM
  • 快速定位数据

基础语法：
--索引的使用：
--1：创建表的时候给字段增加索引
--2：创建完毕后，增加索引
	--id_表名_字段名
	CEREATE INDEX id_app_user_name on app_user('name')

--显示所有的索引信息
SHOW INDEX FROM student

--增加一个全文索引 （索引名、列名）
ALTER TABLE student ADD FULLTEXT INDEX 'studentName'('studentName')

--EXPLAIN 分析sql的执行情况
EXPLAIN	SELECT * FROM student --非全文索引查询
EXPLAIN	SELECT * FROM student WHERE MATCH(studentName) AGAINEST('刘')

索引的原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据的表不需要加索引
• 索引一般加在用于查询的字段上