MYSQL

MYSQL

一.基本的命令行操作

show databases;--查看所有的数据库
use school;--切换数据库
show tables;--查看数据库中所有的表
describe student;--显示数据库中表的信息；查看表结构
create database test;--创建数据库    
exit;--退出连接
--单行注释
/*
多行注释
*/

DDL定义 DML操作 DQL查询 DCL控制

二.操作数据库

操作数据库->操作数据库中的表->操作数据库中的数据

操作数据库

mysql关键字不区分大小写

1）.创建数据库

create database [if not exists] shool;

2）.删除数据库

drop database [if exists] shool;

3）.使用数据库

use shool;

4）.查看数据库

show databases;

数据库的列类型

1）数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的书库 3个字节
  • int 标准的整数 4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节（精度问题）
  • decimal 字符串形式的浮点数 金融计算一般使用decimal

2）字符串

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

3）时间日期

  • date YYYY-MM-DD 日期格式
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳 1970.1.1到现在的毫秒数
  • year 年份

4）null 

  • 没有值，未知

创建表

create table [if not exists] `teacher`(
   `id` int(4) not null auto_increment comment '学号',
   `name` varchar(30) not null default '匿名' comment '姓名',
   primary key (`id`)
)engine=innodb default charset=utf8

格式：

create table [if not exists] `表名`(
   `字段名` 列类型 [属性] [索引] [注释],
   `字段名` 列类型 [属性] [索引] [注释],
   `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集][注释]
show create database school;--查看创建数据库的语句
show create table student;--查看创建表的语句
desc student;--查看表的结构

数据表的类型

数据库引擎：

  MYISAM INNODB（现在默认）
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约为2倍
优点 节约空间，速度较快 安全性高，事务处理，多表多用户操作

 所有的数据库文件都存在data目录下，本质还是文件存储

  • innodb：在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • myisam对应文件：*.frm文件：表结构定义文件；*.MYD：数据文件；*.MYI索引文件

 修改删除表

1）修改表名

alter table student rename as student1;

2）增加字段

alter table student add age int(10);

3）修改表字段

alter table student modify age varchar(10);--修改字段约束，无法重命名
alter table student change age age1 int(1);--重命名字段，无法修改约束

4）删除表字段

alter table student drop age1;

5）删除表

drop table if exists student;

所有的创建于删除操作加上判断；

三.数据管理

1）外键

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

2）DML语言

数据操作语言

  • insert 
    INSERT INTO teacher (id,NAME) VALUES(1,'dengwenxiong');--插入多个字段
INSERT INTO teacher(name) VALUES('hxd'),('gdx');--同一字段插入多个值

    必须一一对应

  • update 
    UPDATE teacher SET NAME="dyw" WHERE id=1;--更改一个属性
UPDATE teacher SET NAME="dyw",email='1780403059@qq.com' WHERE id=1;--更改多个属性
  • delete 
    delete from student where id=1;
  • truncate 
    truncate student;--完全清空一个表

delete与truncate的区别：

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

    使用delete删除后，重启数据库，若使用innodb引擎，则自增列会从1开始（存在内存中的，断电就没了），而使用Myisam引擎，则会继续从上一个自增量开始（存在文件中的，不会丢失）

四.DQL查询数据

数据查询语言

SELECT * FROM student
-- 起别名
SELECT `studentno` AS 学号 FROM student
-- 函数 Concat(a,b)字符串拼接
SELECT CONCAT('学号：',studentno) AS 新学号 FROM student
-- 去重
SELECT DISTINCT studentno FROM result
-- 查询系统版本
SELECT VERSION()

模糊查询

运算符

betweeen a between b and c

a在b，c之间结果为真
like a like b 如果a匹配b，结果为真
in a in(a1,a2,..) a为其中一个值，结果为真 
-- 查询姓邓的
-- %代表0到任意个字符，_代表一个字符
SELECT studentnum FROM student WHERE studentnum LIKE '邓%'
SELECT studentnum FROM student WHERE studentnum LIKE '邓_'
-- 查询学号为110,111,112的学生姓名,in跟着的是具体的值
SELECT studentnum FROM student WHERE studentno IN(110,111,112)

联表查询

七种连接

-- 内联inner join；左联left join；右联right join
SELECT s.studentno,studentnum,subjectno,subjectresult FROM student AS s INNER JOIN result AS r ON s.studentnum=r.studentnum

自连接

把一张表拆为两张一样表

SELECT a.categroyname AS '父'，b.categoryname AS '子' FROM category AS a,category AS b WHERE a.categoryid=b.category.pid

分页limit和排序order by

升序asc，降序desc

limit 起始值，页面大小 ；limit 0,5 显示1-5条数据，第n页：limit (n-1)*pagesize,pagesize ;n为当前页，pagesize为页面大小

子查询

在where查询条件里嵌套使用select（先查里面的查询，再查询外面的查询）

五.常用函数

-- 数学运算
SELECT ABS(-9) -- 绝对值
SELECT CEILING(9.6) -- 向上取整
SELECT FLOOR(9.6) -- 向下取整
SELECT RAND() -- 返回一个0-1的随机数
SELECT SIGN(-10) -- 判断一个数的符号，0返回0，负数返回-1，正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('abcd') -- 字符串长度
SELECT CONCAT('w','b','d') -- 拼接字符串
SELECT INSERT('abcd',1,3,'789') -- 替换字符串
SELECT LOWER() -- 小写
SELECT UPPER() -- 大写
SELECT REPLACE('abcd','b','e') -- 替换指定的字符串
SELECT SUBSTR('abcdef',3,2) -- 截取字符串
SELECT REVERSE('abcdf') -- 反转字符串
-- 时间日期函数
SELECT NOW() -- 当前时间
SELECT LOCALTIME()-- 本地时间

六.聚合函数

count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值 
SELECT COUNT(studentname) FROM student -- 会忽略null值
SELECT COUNT(*) FROM student -- 不会忽略null值
SELECT COUNT(1) FROM student -- 不会忽略null值

group by 分组 having 分组的条件

加密MD5('密码')

七.事务

要么都成功要么都失败

事务四个要素：acid 

  • Atomicity原子性：要么都成功要么都失败
  • Consistency一致性：事务前后数据保存一致性
  • Durability持久性：事务一旦提交不可逆，会持久化到数据库中
  • Isolation隔离性：数据库为每个用户开启事务不能被其他事务干扰
set autocommit=0 -- 关闭事务自动提交
set sutocommit=1 -- 开启事务自动提交（默认）
-- 事务开启
start transaction -- 标记一个事务的开始，这之后的sql都在一个事务中
-- 提交：持久化
commit
-- 回滚：回到原来的样子
rollback
-- 事务结束
set autocommit=1 -- 开启自动提交
-- 设置保存点
savepoint 保存点
-- 回滚到保存点
rollback to savepoint 保存点名 
-- 撤销保存点
release savepoint 保存点名

八.索引

索引是帮助Mysql高效获取数据的数据结构

索引分类

  • 主键索引（primary key）：唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引（unique key）：避免出现重复的列，唯一索引可以重复，多个列都可以标识
  • 常规索引（key/index）：磨人的
  • 全文索引（fulltext）：特定的数据库引擎才有，myisam，快速定位数据

创建索引的方式

Btree是innodb的默认数据结构

九.权限管理

-- 创建用户
create user hhh identified by '12345'
-- 修改密码(当前用户)
set password =password('24536')
-- 修改指定用户密码
set password for hhh =password('1273')
-- 重命名
rename user hhh to xxx 
-- 授权(库.表)
grant all privileges on *.* to hhh
-- 查询权限
show grants for hhh
-- 撤销权限
revoke all privileges on *.* from hhh

十.三大范式

第一范式(1NF):

原子性：保证每一列不可再分

第二范式(2NF):

前提：满足第一范式

每张表描述一个事情

第三范式(3NF):

前提：满足第一范式和第二范式

确保数据表中的没列数据都和主键直接相关，而不能间接相关

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/python-road/p/13235227.html