MySQL学习笔记

扬光
83 阅读6分钟

基础命令

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接

数据库的操作

  • 数据库不存在，创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
  • 删除数据库
DROP DATABASE [if EXISTS] 数据库名;
  • 进入数据库并使用
use 数据库名;
  • 查看数据库
SHOW DATABASES;

MySQL的数据类型

数值

数据类型描述大小
tinyint十分小的数据1个字节
smallint较小的数据2个字节
mediumint中等大小的数据3个字节
int标准的整数4个字节
bigint较大的数据8个字节
float浮点数4个字节
double浮点数8个字节
decimal字符串形式的浮点数，一般用于金融计算

字符

数据类型描述大小
char字符串固定大小0~255
varchar可变字符串0~65535
tinytext微型文本2^8-1
text文本串2^16-1

时间日期

数据类型描述格式
date日期格式YYYY-MM-DD
time时间格式HH：mm：ss
datetime最常用的时间格式YYYY-MM-DD HH：mm：ss
timestamp时间戳，1970.1.1到现在的毫秒数
year年份表示

表字段的设计规范，一般以下五个字段是必要的 |名称 | 描述 | | ---------- | ---- | | id | 主键 | | version | 乐观锁 | | is_delete | 伪删除 | | gmt_create | 创建时间 | | gmt_update | 修改时间

sql脚本规范

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 '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
  • 表和字段名最好用''括起来
  • 每一个语句后都要使用,隔开
  • 主键声明一般放在最后或者最前，以便于查看

数据库引擎

MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为2倍

外键

A表中的主键是B表的某一字段存在某种关系，但是不一定要同名，建立联系，类型要相同，字段宽度也要一致。例如班级表class中的主键classid和学生表student中的classid就是一种外键关系，其中班级表class被称作主表。
因此主表删除时必须保证所有从表都被删除，否则主表无法被删除。

数据库操作

DML操作

Data Manipulation Luaguge：数据操作语言

  • insert操作

注意省略字段的这种用法，必须要保证插入的数据和数据表的字段一一对应

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');

-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');

-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1);
  • update操作
-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;

-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';

-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
  • delete操作
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

总结
insert语法:INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
update语法:UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];
delete语法:DELETE FROM 表名 [WHERE 条件]

DQL查询数据

Data QueryLanguage： 数据查询语言

  • 查询SQL模板
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

虚表：查询结果是虚表。

  • 普通查询
-- 查询全部学生
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;
  • 条件查询
-- 单一条件查询
select * from A where condition = ?

-- 多条件查询
select * from A where condition1 = ? and condition2 = ?

-- 范围条件查询
select * from A where condition in ('value1','value2',...)

-- 模糊查询，含有某个字符d
select * from A where condition like '%d%'

-- 模糊查询，倒数第2个字符为d
select * from A where condition like '%d_'
  • 分组查询
select 分组函数，分组后的字段
from 表
[where 筛选条件]
group by 分组的字段
[having 分组后的筛选]
[order by 排序列表]
使用关键字筛选的表位置
分组前筛选where原始表group by的前面
分组后筛选having分组后的结果group by 的后面

相当于说where筛选的是原始的表，但是having筛选的是经过where和group by处理后的虚表

  • 连接查询
-- 左外连接
select * from A left (outer) join B on A.key = B.key

-- 右外连接
select * from A right (outer) join B on A.key = B.key

-- 内连接
select * from A inner join B on A.key = B.key

-- 左连接
select * from A left (outer) join B on A.key = B.key where B.key is null

-- 右连接
select * from A right (outer) join B on A.key = B.key where A.key is null

-- 全外连接
select * from A full (outer) join B on A.key = B.key

-- 两表不重复部分
select * from A full (outer) join B on A.key = B.key where A.key is null or B.key is null

image.png 摘自blog.csdn.net/qq_45173404…

  • 分页查询
select * from A limit offset,pageSize

这里的offset表示开始索引，默认从0开始，pageSize表示每页的条数。现在已经很少自己计算开始索引等参数，基本都是通过各种第三方分页插件实现。

  • 子查询

部分情况下可以替代连接查询，连接查询是通过某一关联字段相等作为条件进行查询的，那么可以直接使用where A.关联字段 = (select 关联字段 from B where condition = ?)

-- 查询'课程设计'的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;
-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='课程设计'
)



参考博文：blog.csdn.net/qq_45173404…

作者：Baret-H

avatar
文章
阅读
粉丝