一、操作数据库
B站狂神说mysql教学笔记(视频地址)
可视化软件我用的是navacat
操作数据库>操作数据库中的表>操作数据库中的数据
1.1、操作数据库
(1)创建数据库[大小写没有影响]
CREATE DATABASE [IF NOT EXISTS] westons
(2)删除数据库
DROP DATABASE [IF EXIST] westons
(3)使用数据库
--tab 键上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE `school`
(4)查看数据库
SHOW DATABASES --查看所有数据库
1.2、数据库的列类型
数值
- tinyint 十分小的数据 1个字节 (可以用来设置性别)
- 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
时间日期e
java.util.Date
-
date YYYY-MM-DD ,日期格式
-
time HH: mm: ss 时间格式
-
date YYYY-MM-DD HH: mm: ss
-
timestamp 时间戳,1970.1.1到现在的毫秒数!
-
year 表示年份
null
- 没有值,未知
- 注意,不要使用NULL进行运算,结果为NULL
1.3数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,使用0来填充,int (3), 5–
自增:(AUTO_INCREMENT)
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~ index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为not null,如果不给他赋值,就会报错
- NULL,如果不填写值,默认就是null!
默认
- 设置默认的值!
- sex
拓展
/*每一个表,都必须在以下五个字段!表示一个记录存在意义!
id 主键
`version` 乐观键
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/
1.4创建数据库表
--目标:创建一个school数据库
--创建学生表(列,字段)使用SQL. 创建
--学号int登录密码varchar(20)姓名,性别varchar (2) ,出生日期(datatime),家庭住址, email
--注意点,使用英文(),表的名称和字段尽量使用 `` 括起来
-- AUTO_ INCREMENT 自增
--字符事使用单引号括起来!
--所有的语句后而加,(英文的).,最后一个不用加
--PRIMARYKEY主键,一般一一个表只有一-个唯一-的主键!
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '1234' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` datetime NOT NULL COMMENT '出生日期',
`address` varchar(100) NOT NULL COMMENT '家庭住址',
`email` varchar(50) NOT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
格式
CREATE TABLE [IF NOT EXISTS] '表名'(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
)[表的类型] [字符集设置] [注释];
常用命令
show create database school --查看创建数据库的语句
show create table student --查看student数据表的定义语句
desc student--显示表的结构
1.5数据表的类型
--关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据索引 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | ·支持· | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM的两倍 |
常规使用操作
- MYISAM 节约空间,速度较快
- INNODB 安全性高,支持事物的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,本质还是文件的存储!
MySQL引擎在物理文件上的区别
- InnoDB在数据库表中只有一个*.ibd文件(表的数据文件)
- MYISAM对应的文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
- sdi
设置数据库表的字符集编码
CHARSE=utf8
不设置的话,会是默认的字符集编码(不支持中文!)
MySQL的默认编码是Latin1,不支持中文
在my.ini 中配置默认的编码
character-set-server=utf8
1.6修改、删除表
*1)*修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
alter table teacher1 rename as teacher
-- 增加表的字段 alter table 表名 add 字段 列类型
alter table teacher add age int(4)
desc teacher -- 查看表结构
-- 修改表的字段(重命名,修改约束!)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
alter table teacher modify age varchar(23) -- 修改约束(无法重命名)
-- ALTER TABLE 表名 CHANGE 字段名 新字段名 列属性[]
alter table teacher change age age1 int(2) -- 修改字段名、修改约束
-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
alter table teacher drop age1
*2)*删除
-- 删除表(如果表存在再删除)
drop table if exists 表名
所有的创建和删除操作尽量加上判断,以免报错
注意点
-
`` 使用这个包裹字段名
-
注释-- /**/
-
sql关键字大小不敏感,建议用小写
-
所有的符号全部用英文
二、MySQL数据管理
2.1、外键(了解即可)
方式一:再建表的时候,增加约束(麻烦,比较复杂)
create table `grade`(
`gradeid` int(10) not null auto_increment comment '年级id',
`gradename` varchar(30) not null comment '年纪姓名',
primary key(`gradeid`)
)engine=innodb default charset=utf8
drop table student
-- 学生的gradeid 字段要去引用年纪表的 gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用) references 引用
create table if not exists `learner` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`brithday` datetime default null comment '出生日期',
`gradeid` int(10) not null comment '学生的年级',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '电子邮件',
primary key(`id`),
key `fk_gradeid` (`gradeid`),
constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`)
)engine=innodb default charset=utf8
删除有外键关系的表时,必须要先删除应用别人的表(从表),再删除被引用的表(主表)
方式二:创建表成功后,添加外键约束
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`brithday` datetime default null comment '出生日期',
`gradeid` int(10) not null comment '学生的年级',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '电子邮件',
primary key(`id`),
)engine=innodb default charset=utf8
-- sql语句没有,就不算结束,可以换行
alter table `student` add constraint `fk_gradeid`
foreign key(`gradeid`) references `grade`(`gradeid`)
--alter table 表 add constraint 约束名 foreign key(作为外键的列) references 那个表(字段)
方式一、和方式二都是物理外键,数据库级别的外键,不建议使用!(避免数据库过多造成困扰,使用这种物理外键后删表特别麻烦)
方式三:最佳实现(应用层完成,自己用Java代码写)
- 数据库就是单纯的表,只用来存数据,只有行数据和列字段
- 使用多张表的数据,想使用外键(程序去实现)
2.2、DML语言(全部记住)
- insert
- update
- delete
2.3、添加 insert
-- 插入语句(添加)
-- insert into 表名(字段名1,字段名2 ,字段名3) values('值1'),('值2'),('值3')
insert into `teacher`(`name`) values('陈泽宇')
-- 由于主键自增我们可以省略(如果不写字段,他就会一一匹配)
insert into `teacher` values('天才')
-- 一般写插入语句,我们一定要数据和 字段一一对应!
--插入多个字段
insert into `teacher`(`name`) values('谭腾达'),('詹志文')
insert into `grade`(`gradename`,`gradeid`) values('张三',1)
--省略多个字段,但是值要一一对应
insert into `student` values(1,'张三',3,'神州大陆','110')
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,values后面的值要用逗号隔开value(),()
2.4、修改update
--修改的语法
--update 表名 set 字段名='新值' where 条件
--update 表名 set column_name=value where 条件
-- 修改学员名字,带了简介
update `student` set `name`='李四' where id=1
update `studnet` set `name`='王琦' where id=2
-- 不指定条件的情况下,会改动表的所有值,具体入夏图!如果你在公司干了这种事,准备跑路吧!
update `student` set `name`='李焕英'
--修改多个属性
--语法: update 表名 set column_name=value,[colnum_name=value...]where 条件
update `student` set `gradeid`=4,`address`='地球村',`email`='911' where id =1
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改…
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或 != | 不等于 | 5<>6 | true |
> | 大于 | 5>6 | false |
< | 小于 | 5<6 | true |
>= | 大于等于 | 5>=5 | true |
<= | 小于等于 | 5<=4 | false |
BETWEEN…and… | 在某个范围内 | [2,5] | |
AND | 我和你&& | 5>1 and 1>2 | false |
OR | 我和你|| | 5>1 or 1>2 | true |
--通过多个条件定位数据,无上限
update `student` set `gradeid`=6,address='提瓦特大陆' where id=1 and email='911'
注意:
-
column_name 是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定,则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
2.5删除
delete命令
语法: delete from 表名 [where 条件]
(提一句,真男人玩delete命令从不带where)
--删除表中所有值
DELETE FROM `student`
--删除表中指定数据
DELETE FROM `studnet` whereid=1
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
--清空student表
TRUNCATE `studnet`
DELETE 与 TRUNCATE的区别
-
相同点:都能删除数据,都不会删除表结构
-
不同:
-
TRUNCATE 重新设置 自增列 计数器回归零
-
TRUNCATE 不会影响事务
-
(可以这么理解:truncate就是删表之后重建一个没有数据的新表)
-
detele是dml语言,truncate是ddl语言
insert into `test`(`name`) values('1'),('2'),('3') delete from `test` -- 不影响自增 truncate table `test` -- 自增会归零
了解即可:DELETE 删除问题,重启数据库后
- innodb 自增列会从1开始(存在内存中的,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
-
三、DQL查询语言(最重点)
3.1DQL
(Date Query Langyage)
-
所有的查询操作 Select
-
查询的简单、复杂的查询它都能做
-
数据库最核心的语言,最重要的语句!!!!!
3.2指定查询字段
创建数据库和表代码
create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment '学号',
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment '学生姓名',
`sex` tinyint(1) default null comment '性别,0或1',
`gradeid` int(11) default null comment '年级编号',
`phone` varchar(50) not null comment '联系电话,允许为空',
`address` varchar(255) not null comment '地址,允许为空',
`borndate` datetime default null comment '出生时间',
`email` varchar (50) not null comment '邮箱账号允许为空',
`identitycard` varchar(18) default null comment '身份证号',
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;
FullHousep16开始用到的数据表 2
“
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment '年级编号',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`subjectno`int(11) not null auto_increment comment '课程编号',
`subjectname` varchar(50) default null comment '课程名称',
`classhour` int(4) default null comment '学时',
`gradeid` int(4) default null comment '年级编号',
primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`studentno` int(4) not null comment '学号',
`subjectno` int(4) not null comment '课程编号',
`examdate` datetime not null comment '考试日期',
`studentresult` int (4) not null comment '考试成绩',
key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
对该数据库的查询操作
——语法:SELECT 字段 … FROM 表
-- 查询所有的学生 select 字段 from 表
select * from student
-- 查询指定字段
select `StudentNo`,`StudentName` from student
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
select `studentNo` as 学号 ,`studentName` as 学生姓名 from student
-- 函数 concat(a,b)
select concat('姓名: ',studentName) as 新名字 from student
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有哪些同学参加了考试,成绩
select * from result -- 查询全部的考试成绩
select `StudentNo` from result -- 查询有哪些同学参加了考试
select distinct `StudentNo` from result -- 发现重复数据,去重
数据库的列(表达式)
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量……
语法:select 表达式 from 表
select version()-- 查询系统版本(函数)
select 100*4-1 as 计算结果 -- 用来计算(表达式)
select @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员成绩成绩 + 1分 查看
select `StudentNo`,`StudentResult`+1 as '提分后' from result
3.3where条件字
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成!结果未布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
not ! | not a ! a | 逻辑非,真为假、假为真 |
(尽量使用英文字符)
-- and &&
select `studentNo`,`StudentResult` from result where StudentResult<=100 and studentResult>95
select `studentNo`,`StudentResult` from result where StudentResult<=100 && studentResult>95
-- 模糊查询(区别)
select studentNo,studentResult from result where studentResult between 95 and 100
-- 查询学号不为1000的学生
-- != not
select studentNo,studentName from student where studentNo != 1000
select studentNo,studentName from student where not studentNo = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为NULL结果为真 |
BETWEEN | a beteween b and c | 若a在b和c之间,则结果为真 |
LIke | a like b | SQL匹配,如果a匹配b,则结果为真 |
In | a in (a1,a2,a3……) | 假设a在a1,或者a2……其中的某一个值中,结果为真 |
-- 查询学号不为1000的学生
-- != not
select studentNo,studentName from student where studentNo != 1000
select studentNo,studentName from student where not studentNo = 1000
-- 查询姓赵的同学
-- like结合 %(代表0到任意个字符) _(表示一个字符)
select StudentNO,StudentName from student where studentName like '张%'
select studentNO,studentName from student where studentName like '赵_'
-- 查询姓刘的同学,名字后面只有两个字的 :where studentName like '刘__'
-- 查询名字中有伟字的
select studentNo,studentName from student where studentName like '%伟%'
-- ====== in(具体的一个或者多个值,不能像like一样使用%)
-- 查询1000,1001号成员
select studentNo,studentName from student where studentNo in (1000,1001)
--查询具体地址的学生
select studentNo,studentName from student where Address in ('北京朝阳','广东深圳')
-- ======= null not null=======
-- 查询地址为空的同学
select studentNo,studentName from student where address='' or address is null
--查询出生日期不为空的同学
select studentNO,studentName from student where borndate is not null
3.4联表查询
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 如果右表中没有匹配,也会从左表 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |