2.数据库操作
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字区分大小写
2.1操作数据库
1、创建数据库
create database if not exists westos
2、删除数据库
DROP DATABASE IF EXISTS westos
3、使用数据库
--如果是你的表名或字段是特殊符号就需要带``
USE `school`
4、查看数据库:
show databases
对比SQL可视化操作
学习思路:
- 对照Sqlog可视化历史记录查看sql
- 固定语法或关键字必须强行记住
2.2数据库的列类型
数值
- 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
时间日期
java.Util.Date
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD:HH:mm:ss 常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数,也常用
- year 年份表示
null
- 没有值 未知
注意, 不要使用它进行运算,结果为null
2.3数据库的字段属性(重点)
Unsigned
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充的
- 不足的位数用0来填充
自增
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键-index,必须是整数类型
- 可以自定义自增的起始值和步长
非空 Null not null
- 假设设置为not null,如果不给它赋值就会报错
- NULL ,如果不填写值,默认就是null
默认
- 设置默认的值
- sex,默认值为男,如果不能指定该列的值,则会有默认的值
扩充
每一个表都必须存在以下五个字段,表示一个记录的意义
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
2.4创建数据库表
-- 目标:创建一个school数据库
-- 创建学生表(列,字段)使用SQL 创建
-- 学号int 登入密码varchar(20) 姓名 varchar(2)性别 出生日期(datatime)家庭地址,email
/*
注意点:使用英文() ,表的名称和字段尽量使用 ` ` 括起来
AUTO_INCREMENT 自增
字符串使用,单引号括起来
所有的语句后面加, (英文的) 最后一个不用加
PRIMARY KEY 主键, 一般一个表只有一个唯一的主键
*/
CREATE TABLE IF NOT EXISTS `Student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pasw` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(3) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(80) DEFAULT 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 -- 显示表的结构
2.5数据表的类型
--关于数据库引擎
INNODB --默认使用
MYISAM -- 早些年使用
MYISAM | INNODB | |
---|---|---|
事物支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文检索 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM :节约空间,速度较快
- INNODB:安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还是文件的存储!
MySQL引擎在物理文件上的区别
- INNODB :在数据表总只有一个 *.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会时mysql默认的字符集编码-(不支持中文)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6修改和删除表中的数据
修改
-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段:alter table 表名 add 字段名 列属性
ALTER TABLE teacher1 ADD id INT(11)
-- 修改表的字段(重命名、修改约束!)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age ag1 INT(11) -- 字段重命名
-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
删除
-- 删除表(如果存在再删除)
Drop table if exists teacher1
所有的创建和操作尽量加上判断,以免报错
注意点:
- ``字段名,使用这个包裹
- 注释:-- /**/
- sql关键字大小写不敏感,建议使用小写
- 所有的符号全部用英文
3.MySQL数据管理
3.1外键(了解即可)
方式一:在创建表的时候,增加约束(麻烦,比较复杂)
create table if not exists `grade`(
`gradeid` int(10) not null auto_increment comment 'grade学号',
`gradename` varchar(30) not null comment 'grade姓名',
primary key(`gradeid`)
)Engine=innodb default charset=utf8
-- 学生表的gradeid 字段 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)reference 引用
create table if not exists `Student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pasw` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(3) not null default '女' comment '性别',
`birthday` datetime default null comment '生日',
`gradeid` int(10) not null comment 'grade学号',
`address` varchar(100) default null comment '地址',
`email` varchar(80) 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 '姓名',
`pasw` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(3) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`gradeid` INT(10) NOT NULL COMMENT 'grade学号',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(80) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)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 外键的表(外键表的哪个字段)
以上的操作都是物理外键,数据库级别的外键,我们不建议使用! (避免数据库过多而造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2DML语言(全部记住)
数据库意义:数据存储、数据管理
DML语言:数据操作语言
- insert
- update
- delete
3.3添加
insert
-- 插入语句(添加)
-- insert into 表名(字段1,字段2,字段3....) values(值1,值2,值3...),(值1,值2,值3...)...
INSERT INTO `grade`(`gradename`) VALUES('大五')
-- 由于主键自增我们可以省略(如果不写表的字段,它就会一一匹配
Insert into `grade` values('大三')
-- 一般插入语句,我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大二'),('大一')
INSERT INTO `student`(`name`,`pasw`,`sex`)
VALUES('李四','3243','女'),('李发的','343243','女'),('发的四','32323243','女')
语法:insert into 表名(字段1,字段2,字段3…) values(值1,值2,值3…),(值1,值2,值3…)…
注意事项
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,values后面的值,需要使用,隔开即可 values(),()…
3.4修改
update 修改谁 (条件) set 原来的值=新值
-- 修改学员的名字
update `student` set `name`='冰凝' where id=1
-- 不指定条件的情况下,会修改所有的数据!
update `student` set `name`='冰凝'
-- 修改多个属性,逗号隔开
update `student` set `name`='仔细',`address`='武汉' where id=3
-- 语法:
-- UPDATE 表名 SET 字段=value,[字段=value,....] where [条件]
条件:where语句 运算符 id等于某个值 ,大于某个值,在某个区间内修改
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 4=3 | false |
> | 大于 | 6>3 | true |
< | 小于 | 4<5 | true |
<= | 小于等于 | 3<=4 | true |
>= | 大于等于 | 3>1 | true |
<>或 != | 不等于 | 3<>4 | true |
BETWEEN A AND B | 在某个范围内 | [A,B] | |
AND | 我和你&& | 1>3and3<2 | flase |
OR | 我或者你 | | | 1>3or3<2 | flase |
-- 通过多个条件定位数据
UPDATE `student` SET `name`='仔细',`address`='武汉' WHERE `name`='冰凝' and sex='女'
语法:UPDATE 表名 SET 字段=value,[字段=value,....] where [条件]
注意
- 字段是数据库的列,尽量带上
- 条件,筛选的条件,如果没有指定,则会修改所有的列
- value,是一个具体的值,也可以是一个变量
- 多个设置属性之间使用英文逗号隔开
UPDATE `student` SET `name`='仔细',`birthday`=CURRENT_TIME WHERE `name`='仔细' AND sex='女'
3.5删除
delete命令
语法:delete from 表名 [where条件]
-- 删除数据(避免这样写,会删除全部)
delete from `student`
-- 删除指定数据
DElete from `student` where `id`=1
TRUNCATE命令
作用:完全清空一个数据库,表的结构和索引约束不会变!
-- 清空 student 表
TRUNCATE `Student`
delete和TRUNCATE区别
- 相同点:都能删除数据,都不好删除表结构
- 不同
- TRUNCATE 重新设置 自增列 计数就会归零
- TRUNCATE 不会影响事物
-- 测试delete和 TRUNCATE区别
create table `test`(
`id` int(4) not null auto_increment,
`coll` varchar(30) not null,
primary key(`id`)
)
Insert into `test`(`coll`)values('1'),('2'),('2')
Delete from `test` -- 不会影响自增
truncate table `test` -- 自增会归零
了解即可:delete删除问题
,重启数据库,现象
- INNODB 自增列会重1开始(存在内存当中,断电即失)
- MYISAM,继续从上一个自增了开始(存在文件中的,不会丢失)