文章目录
1、连接数据库(命令行)
- 在控制台(cmd)中输入如下代码:
mysql -u root -proot
1.1常用命令
-- 所有语句必须以;结尾
-- 显示所有的数据库
show databases;
-- 切换数据库
use master;
-- 显示数据库中所有的表
show tables;
-- 显示student表中的数据
describe student;
-- 创建数据库
create datebase databaseName;
-- 退出连接
exit;
-- 单行注释
/*多行注释*/
1.2、数据库语言种类:
- DDL: 数据库定义语言
- DML: 数据库操作管理语言
- DQL: 数据库查询语言
- DCL: 数据库控制语言
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
2.1、操作数据库
-
创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
-
删除数据库
DROP DATABASE [IF EXISTS] westos;
-
使用数据库
USE student;
-
查看数据库
SHOW DATABASES;
2.2、数据库额数据类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 双精度浮点数 8个字节(精度问题!)
- decimal 字符串形式的浮点数 金融计算的时候,一般使用此类型
字符串
- char 字符串固定大小的 0-255
- varchar 可变字符串 0-65535 常用 与Java中的string一样
- tinny 微型文本 2^8-1
- text 文本串 2^16-1
时间类型
java.util.Date
- date YYYY-MM-DD,日期
- time HH:mm:ss,时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数 也较为常用
null
- 没有值,未知
- 注意,不要使用NULL进行运算,结果为NULL
2.3、数据库的字段属性
Unsigned:
- 无符号的整数
- 声明该列不能声明为负数
zerofill:
- 0(零)填充的
- 不足的位数使用0(零)来填充,例:int(3), 5 —> 005
自增(AUTO_INCREMENT):
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的逐渐~ index,必须为整数类型
- 可以自定义设计主键自增的起始值和步长
空(NULL)和非空(NOT NULL):
- 假设设置为not null,如果不给它赋值,就会报错!!!
- null,如果不填写值,默认就是null
默认(DEFAULT):
- 设置默认的值
- 如果不指定设置了此属性的列值,该列的值默认就是指定的默认值
2.4、创建数据库表
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
-- 切换使用school数据库
USE `school`;
-- 创建student表
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
- AUTO_INCREMENT:自增
- NOT NULL:非空
- COMMENT:描述
注意:字段名使用反引号``括起来,而字符串使用 单引号’ '括起来,每一个字段声明结束使用英文状态下的逗号结尾
格式
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释(描述)],
`字段名` 列类型 [属性] [索引] [注释(描述)],
......
`字段名` 列类型 [属性] [索引] [注释(描述)]
)[表类型][字符集设置][注释]
扩展(常用命令):
-- 查看创建数据库的语句
SHOW CREATE DATABASE school;
-- 查看创建数据库表的语句
SHOW CREATE TABLE student;
-- 查看表的结构
DESC student;
2.5、数据表的类型
-- 关于数据库引擎
/*
InnoDb 默认使用
MYISAM 早些年使用
*/
MYISAM | InnoDb | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为MYISAM的2倍数 |
常规使用操作:
- MYISAM:节约空间,速度较快
- INNODB:安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下
本质还是文件的存储
- InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的 ibdata1 文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
2.6、修改和删除数据表
修改表名
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1;
增加表的字段
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11);
修改表的字段
- 修改约束
-- 修改约束 ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age varchar(11); -- 修改约束
- 字段重命名
-- 字段重命名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1); -- 字段重命名
注意:MODIFY 不能重命名字段,重命名字段只能用CHANGE字段
删除表的字段
-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1;
删除表(如果表存在就删除)
-- 删除表
DROP TABLE IF EXISTS teacher1;
所有的创建和删除操作尽量加上是否存在判断,以免报错
注意点:
- ``字段名使用这个反引号包裹
- 注释:
- 多行注释/**/
- 单行注释–
- sql对关键字大小写不敏感,建议使用小写
3、数据库数据管理
3.1、外键
第一种方式(创建表的同时创建外键):
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)
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`)
-- 先定义外键
KEY `FK_gradeid` (`gradeid`),
-- 给这个外键添加约束(执行引用)reference引用
CONSTRAINT `FK_gradeid` 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 CONTRAINT 约束名 FOREIGN KEY(作为外键的列字段名) REFERENCES 被引用的表(引用的字段)*/
以上的操作都是物理外键,数据库级别的外键,不建议使用!
那为什么呢?
因为使用外键会使每次做DELETE或者UPDATE的时候都必须考虑外键约束,增加测试的难度
最佳实践;
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现,即应用层)
3.2、DML语言(需重点记忆)
数据库意义: 数据存储,数据管理
- insert
- update
- delete
3.3.1、添加(insert)
-- 插入语句(添加)
-- insert into 表名([字段名1,字段名2,字段名3,......]) values('值1','值2','值3',......)
INSERT INTO `grade`(`gradename`) VALUES ('大四')
-- 一次插入多行数据
INSERT INTO `grade`(`gradename`) VALUES ('大四'),('大二')
-- 一次插入多个字段
INSERT INTO `grade`(`gradename`,`gradeid`) VALUES ('大四','01')
-- 一次插入多行多个字段
INSERT INTO `grade`(`gradename`,`gradeid`) VALUES ('大四','01'),('大三','02')
字段名参数列表是可以省略的,但是之后的值参数列表必须一一对应数据库表中的字段数量和顺序
3.3.2、修改(update)
-- 修改字段值
-- update 修改对象 set 字段名=字段值 where 另一个字段=查询条件值
-- 例:
update `student` set name='刘宗鑫' where `id`=1;
这里用到了where条件查询,where后面要加上一个运算表达式,其中运算操作符(操作符会返回布尔值)包括如下:
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或 != | 不等于 | 5<>6 | true |
< | 小于 | 5<6 | true |
> | 大于 | 5>6 | false |
<= | 小于或等于 | 5<=6 | true |
>= | 大于或等于 | 5>=6 | false |
BETWEEN… AND… | 在某个区间范围 | BETWEEN 2 AND 5 | [2,5] |
AND | 和操作符 | 5>6 and 5>1 | false |
OR | 或操作符 | 5>6 and 5>1 | true |
3.3.3、删除(delete)
DELETE命令
-- 删除数据(避免这样写,会全部删除)
delete from `student`;
-- 删除指定数据
delete from `student` where `id`=1;
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
TRUNCATE `student`;
DELETE 和 TRUNCATE命令的区别
- 相同点:都能删除数据,都不会删除表结构
- 不同
- TRUNCATE 重新设置自增列,计数器会归零
- TRUNCATE 不会影响事务
DELETE删除的问题
,重启数据库,现象:
- InnoDB 自增列会从1开始(存在内存中的,断电即失)
- MYUSAM 继续从上一个自增量开始(存在文件中的,不会丢失)
4、DQL查询数据(最重点)
4.1、DQL(Data Query Language)
数据查询语言:
- 所有查询操作都用它 select
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
4.2、查询所有的字段
-- 查询全部的学生
select * from student
-- 查询指定字段
select `studentid`,`studentname` from student;
-- 使用as给结果起一个别名,可以给字段和表起别名
select `studentid` as 学号,`studentname` as 学生姓名 from student as s;
-- 函数 concat(a,b),字符串拼接
select concat('姓名:',studentname) as 新名字 from student
去重(DISTINCT关键字)
作用:去除select查询出来的重复的
select distinct `studentid` from result
4.3、WHERE查询字句
where后面跟上一个逻辑表达式,就可以筛选查询到的行
select * from student where id=1
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果a为null,结果为真 |
IS NOT NULL | a is not null | 如果a不为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结合 '%','_'使用,%代表0到任意个字符,_代表一个字符
-- 查询姓刘的同学的学号
select `studentid` from `student`
where studentname like '刘%'
in的用法
-- 查询学号为1001,1002,1003号的学员
select `studentid` from `student`
where studentid in (1001,1002,1003);