目录
数据库(DB,DataBase)
数据库分类
关系型数据库
MySQL , Oracle , Sql Sever ,DB2,SQLlite
通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表,.....
非关系型数据库
Redis,MongDB
对象存储,通过对象的自身属性来决定
DBMS(数据库管理系统)
数据库管理软件。
MySQL
安装mysql
sc delete mysql 清空服务
连接数据库
cmd,管理员运行
mysql -u 用户名 -p 密码
刷新权限
flush privileges;
查看所有的数据库
show databases;
切换数据库
use 数据库名;
显示 DataBase changed 即切换成功
查看数据库中所有的表
show tables;
查看表中所有的信息
describe 表名;
创建一个数据库
create database 数据库名;
create database westos;
退出链接
exit;
SQL的注释信息
单行注释: --
多行注释: /*
*/
数据库语言 CRUD增删改查
DDL定义语言
DML操作语言
DQL查询语言
DCL控制语言
操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不区分大小写
操作数据库
1.创建数据库
create database [if not exists] 数据库名;
-- 设置字符编码和校对
create database 数据库名 character set utf8 collate utf8_general_ci;
2.删除数据库
drop database [if exists] 数据库名;
3.使用数据库
use 数据库名;
--表名或字段名是特殊字符需要加``引起
use `数据库名`;
4.查看数据库
show 数据库名;
--查看所有数据库
show databases;
数据库的列类型
数值
tinyint 1字节数据 -128-127
smallint 2字节数据
mediumint 3个字节数据
int 4字节 常用
bigint 8字节
float 单精度浮点数 4字节
double 双精度浮点数 8字节
decimal 字符串形式的浮点数 (精度高,金融计算时一般使用)
字符串
char 0-255 固定大小
varchar 可变字符串 0-65535
tingtext 微型文本 2^8-1
text 文本串 2^16 -1
时间日期
date 日期类型 YYYY-MM-DD
time 时间类型 HH:MM:SS
datatime yyyy-mm-dd hh:mm:ss
timestamp 时间戳
year 年份
null
没有值,未知
不要使用null进行运算
数据库字段属性
Unsigned
无符号整数
声明该列不能为负数
zerofill
不足的位数用0填充
自增(AUTO_INCREMENT)
自动在上一条记录的基础上+1
通常用来设置唯一的主键~index,必须是整数类型
可以自定义自增的起始值和步长
非空(not NULL)
勾选时,插入数据必须给该字段赋值,否则报错
不勾选时,如果不填值,默认为null
默认(default)
设置默认值
若不指定值则设为默认值
注释(COMMENT)
主键设置
primary key('列名')
拓展
之后的学习需要的每个表都有的字段
id 主键
'version' 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
操作数据表
创建数据库表
格式:
create table [if ont exists] `表名`(
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
例:
CREATE TABLE IF NOT EXISTS `student`(
`student``id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',--AUTO_INCREMENT 自增
`name` VARCHAR(23) NOT NULL DEFAULT '匿名' COMMENT '姓名',--comment 注释
`pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY (`id`) --设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8 --engine 引擎 DEFAULT CHARSET=utf8 默认编码集
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看数据表的定义语句
DESC student -- 显示表的结构
数据表的类型
默认 INNODB引擎
1.INNODB
2.MyISAM (现在不用)
INNDB | MYISAM | |
事务支持 | 支持 | 不支持 |
数据行锁定 | 支持 | 不支持 |
外键约束 | 支持 | 不支持 |
全文索引 | 不支持 | 支持 |
表空间的大小 | 约为MYISAM的两倍 | 较小 |
常规:
MYSIAM:节约空间
INNODB:安全性高,事务处理,多表多用户操作
数据库在物理空间的位置;
所有的数据库文件都在Data目录下
本质还是文件的存储
MySQL引擎在文件上的区别
INNODB:数据库表中.frm文件,以及上级目录下的ibdata文件
MYISAM对应的文件
.frm 表结构定义文件
.MYD 数据文件(data)
.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
MySQL默认编码是Latin1,不支持中文
在my.ini中配置默认的编码(不建议使用,没有通用性)
#character-set-server=utf8
修改删除表
修改表
-- ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE teacher RENAME AS teacher1;-- 表名重命名
-- 增加字段 alter table 表名 add 字段名 列属性;
ALTER TABLE teacher1 ADD age INT(11);
-- 修改表的字段(修改约束)
ALTER TABLE teacher1 MODIFY age VARCHAR(11);
-- 修改表的字段(重命名字段)
ALTER TABLE teacher1 CHANGE age age1 INT(11);
-- 删除表的字段
ALTER TABLE teacher1 DROP age1;
删除表
-- 删除表(所有的操作尽量添加IF EXISTS 判断,以免报错 )
DROP TABLE IF EXISTS teacher1;
注意:
``字段名用此来括住;
注释 -- /**/
MySQL数据管理
外键(了解)
约束
定义外键key
给外键添加约束
references 引用
删除有外键关系的表时,需要先删除被引用的表,再删除原表
方式一:
在创建表的时候增加约束(麻烦,复杂)
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',-- AUTO_INCREMENT 自增
`name` VARCHAR(23) NOT NULL DEFAULT '匿名' COMMENT '姓名',-- comment 注释
`pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL,
PRIMARY KEY (`id`), -- 设置主键
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_grade` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)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 `那个表`(`那个字段`);
以上是数据库级别的物理外键,不建议使用(避免数据库过多造成问题)
最佳实现:
数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
使用多张表的数据,使用外键(程序实现)
DML语言
dml:数据操作语言
添加(insert)
-- insert into `表名`([字段名1,字段名2,字段名3]) values ('值1','值2','值3');
INSERT INTO `student`(`name`,`gradeid`) VALUES ('宋燚','12')
-- 插入多行数据
INSERT INTO `student`(`name`,`gradeid`) VALUES ('宋燚','12'),('好的','22')
修改(update)
-- UPDATE `表名` SET `字段名`='值' WHERE `字段名`=值 (如果没有where,会修改所有的值)
UPDATE `student` SET `name`='sy' WHERE `id`=1
-- 修改多个数据
UPDATE `student` SET `name`='sy',`pwd`=333 WHERE `id`=1 and `sex`='女'
操作符
操作符 | 含义 | 范围 | 结果 |
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
BETWEEN...AND... | 闭区间 | BETWEEN 2 AND 3 | true |
AND | 我和你 && | ||
OR | 我或你 | | |
删除(delete)
-- 删除DELETE FROM `数据表` WHERE `字段名`=值;
DELETE FROM `student` WHERE `id`=1;
TRUNCATE命令:清空一个数据库表(结构索引不变)
TRUNCATE `表名`;
不同点:
TRUNCATE:重新设计,自增列计数器归零,不影响事务
delete:不影响自增列计数器,会接着上一个数据的值增加
DQL查询数据(重重点)
SELECT语句的完整语法为:
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
(Data Query Language)数据查询语言
关键词 Select
简单的查询,复杂的查询都能做
数据库最核心的语言
指定查询字段
-- 查询所有用户
SELECT * FROM `user`
-- 查询所有用户的指定字段
SELECT `username`,`date` FROM `user`
-- 查询所有用户的指定字段,给查出来的结果起个名字 AS (可以给字段起别名,也可以给表起别名)
SELECT `username` AS 学号,`date` AS 姓名 FROM `user` AS s
-- 函数Concat(a,b) 可以给查出的数据前添加内容
SELECT CONCAT('姓名:',`username`) AS 姓名 FROM `user`
concat函数运行结果
去重复 (distinct)
去除结果中多余的重复的数据,
-- 去重复
-- 查询有哪些作者发布了文章
SELECT `title` FROM `news` -- 查询全部的文章名
SELECT `authorname` FROM `news` -- 查询有哪些作者发布了文章
SELECT DISTINCT `authorname` FROM `news` -- 加入了去重关键字
数据库的列(表达式)
-- 查询系统版本
SELECT VERSION()
-- 用来计算
SELECT 100*3 AS 计算结果
-- 查询自增的步长
SELECT @@auto_increment_increment
-- 学员考试成绩加一分
SELECT `student`,`studentresult`+1 AS '加分后' FROM `result`
where条件子句
检索数据中符合条件的值
逻辑运算符
运算符 | 语法 | 描述 |
and && | a and b a&&b | 与 |
or || | a or b a||b | 或 |
not ! | not a !a | 非 |
模糊查询:比较运算符
运算符 | 语法 | 描述 |
IS NULL | A IS NULL | 如果操作符为null则结果为真 |
IS NOT NULL | A IS NOT NULL | 如果操作符不为null则结果为真 |
BETWEEN | A BETWEEN B AND C | 若a在b和c之间,则结果为真 |
Like | a like b | SQL匹配,如果a匹配b则结果为真 |
IN | a in(a1,a2,a3.....) | 若a在A1,A2,A3其中的某个值中,则结果为真 |
-- 模糊查询
-- like %:不限制个数 _:一个字符
SELECT authorname FROM news WHERE authorname LIKE '%光%'
SELECT authorname FROM news WHERE authorname LIKE '阳光之_'
-- in 查询
SELECT authorname FROM news WHERE`authorname` IN ('阳光之星','啊这')
-- 查询生日不为空的用户
SELECT username FROM `user` WHERE `date` IS NOT NULL
联表查询(join on)
-- 分析
-- 确定交叉点
-- 判断条件:lishi表的username = user表的username
-- join 连接的表 on 连接查询
-- where 等值查询
SELECT a.username,`title`
FROM `user` AS a
RIGHT JOIN lishi AS b
ON a.username = b.username
SELECT a.username,`title`
FROM `user` AS a
INNER JOIN lishi AS b
WHERE a.username=b.username
操作 | 描述 |
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
三表查询
先查两张表,再连接第三张表
-- 三表联表查询
SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS b
ON r.studentNo=b.studentNo
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | 美术设计 |
查询父类对应的子类关系
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
SELECT a.`categoryName` AS '父',b.`categpryName` AS '子'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
分页和排序
分页 limit 和排序 order by
排序:
升序 ASC 降序 DESC
语法
-- 分页 limit 和排序 order by
-- 排序: 升序 ASC 降序 DESCSELECT *
FROM `user`
ORDER BY `phone` DESC
分页 limit
缓解数据库压力,给人体验更好,瀑布流
语法: LIMIT 起始值(第一个为0),(一页显示几个);
一般为:
设页面大小为s则
第n页:
LIMIT (n-1)*s,s
子查询
本质:在where语句中嵌套一个子查询语句
分组和过滤
SELECT`username` FROM `user`
GROUP BY `gesture`
HAVING `gesture`='开'