MySQL增删查改基础操作
# 1、初识MySQL数据库
# 1.1 连接数据库
- 命令行连接
mysql -u root -p795200 -- 连接数据库
- 修改数据库
update mysql.user set anthentication_string=password('795200') where user='root'and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
show databases; -- 查询所有的数据库
show tables; -- 查看数据库中所有的表
describe student; -- 查看数据库表中所有的信息 describe {表名}
use school; -- 切换数据库 use {数据库名} Database changed
create database westos; -- 创建一个数据库 create database {数据库名}
exit; -- 退出mysql连接
-- MySQL数据库注释
-- 单行注释
/*
多行注释
sfad
dsadas
dsadas
*/
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
数据库语言(CRUD增删查改)
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
# 2、操作数据库
操作数据库>操作数据库中的表>操作数据库表的数据
mysql关键字不区分大小写
# 2.1 操作数据库
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] singer; -- 创建数据库singer
- 删除数据库
DROP DATABASE [IF EXISTS] singer -- 删除一个数据库singer
- 使用数据库
USE `singer` -- 使用数据库
-- tab键上面,如果表名或字段名是一个特殊符号,就需要带 ` `
2
- 查看数据库
SHOW DATABASES -- 查看所有的数据库
- 对照sqlyog可视化的历史记录查看sql命令!
- 固定的语法或关键字必须要牢记!
# 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类型
- tinytext 微型文本 2^8-1 博客之类
- text 文本串 2^16-1 保持大文本
日期
- data YYYY-MM-DD 日期格式
- time HH: MM: SS 时间格式
- datatime YYY-MM-DD HH: mm: ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数! 也较为常用
Null
- 没有值,未知
- -注意,不要使用NULL进行运算,运算结果为NULL
# 2.3 数据库字段属性
**Unsigned **
- 无符号的整数
- 声明强调该列不能声明为负数
zerofill
- 0填充
- 不足的位数,使用0来填充 例: int(3) 5 005
自增
- 自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~index,且必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空
- 假设设置为not null ,如果不给它赋值,会报错!
- NULL,如果不填写值,默认就是null.
默认
- 设置默认的值
- sex,默认值为 男,如果不指定该列的值,则会有默认值
# 2.4 创建数据库表
格式:
create table [if exists] `表名`(
`字段名` 列类型 [属性][索引][注释],
`字段名` 列类型 [属性][索引][注释],
...
...
`字段名` 列类型 [属性][索引][注释]
[PRIMARY KEY(`字段名`)]
)[表类型][字符集设置][注释]
2
3
4
5
6
7
8
常用命令:
SHOW CREATE DATABASE `数据库名` -- 查看创建数据库的语句
SHOW CREATE TABLE `表名` -- 查看创建表的语句
DESC `表名` -- 查看表的结构
2
3
【示例】创建数据库表
-- 目标:创建一个school数据库
-- 创建学生表(列,字段)使用SQL语句创建
-- 学号 int
-- 登陆密码 varchar(20)
-- 姓名
-- 性别 varchar(2)
-- 出生日期 datatime
-- 家庭住址
-- email
-- 注意点:使用英文(),表的名称和字段尽量用``括起来
CREATE TABLE IF NOT EXISTS `student`(
`student_id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`password` VARCHAR(30) NOT NULL COMMENT '密码',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' 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 (`student_id`)
)ENGINE = INNODB DEFAULT CHARSET = UTF8
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
NOT NULL -- 非空
AUTO_INCREMENT -- 自增
DEFAULT '男' -- 初始化值
DEFAULT NULL -- 初始化值为空
COMMENT -- 注释
PRIMARY KEY (`student_id`) -- 主键
ENGINE = INNODB DEFAULT CHARSET = UTF8 --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
创建 表 如果 不 存在 `表名`(
`列名` 数据类型(长度) 不 为空 自增 注释'学号',
`列名` 数据类型(长度) 不 为空 默认值 '匿名' 注释 '姓名',
...
...
`列名` 数据类型(长度) 默认 为空 注释 '出生日期',
...
...
主 键(`列名`)
)引擎=INNODB 默认 编码=utf8mb4
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 2.5 修改或删除表
【示例】修改表
-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段:ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE tacher1 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)
2
3
4
5
6
7
8
9
10
11
【示例】删除
-- 删除表:OROP [TABLE IF EXISTS] `表名`
OROP TABLE IF EXISTS teacher1 --
2
注意点
- `` 字段名,使用这个符号包裹
- 注释 -- /**/
- sql官架子大小写不敏感,建议大家写小写
- 所有的符号必须全部用英文!
# 3、DML数据库操纵语句
实现增删查改,需要重点记忆
实现增删查改,需要重点记忆
实现增删查改,需要重点记忆
# 3.1 添加插入语句
# 1、语句语法
insert into `表名`(列字段1,列字段2,列字段3) values(列字段对应值1,列字段对应值1,列字段对应值1)
语法要求:
1、数值类型直接填写,字符串或字符类型使用' '
(引号)括起来;
2、插入值与列约束一致,插入的列字段的个数、数据类型和值的个数、数据类型必须一致,依次匹配;
3、使用英文符号,不能使用中文字符
# 2、SQL插入数据实例
INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);
-- 分号表示语句的结束
INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);
INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);
2
3
4
- 注:使用以下这种语句时,主键设置自动递增,列字段可以不写,但是值列表中的主键要用Null填充
INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23); -- 分号表示语句的结束
INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23);
INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23);
2
3
INSERT INTO `singerw`.`student` (`student_id`, `password`, `birthday`, `address`) VALUES (12323, 'asdas', '2021-06-07 22:50:05', 'sdasd')
# 3.2 删除语句
# 1、语句语法
delete from `表名`; -- 删除表中所有数据
delete from `表名` where 条件(指定的记录); -- 删除表中指定的数据
2
# 2、SQL删除语句实例
delete from `singerw`; -- 删除表中所有数据,但自动递增不会清零
delete from `singerw` where student_id = 20171649; -- 删除表中学号为20171649的这条数据
delete from `singerw` where name = `张三`; -- 删除表中姓名为张三的这条数据
2
3
- 删除表中所有数据,且把自动递增清零:
truncate table `singerw`;
# 3.3 更新修改语句
# 1、语句语法
update `表名` set 列名 = 新的值 -- 修改表中所有数据
update `表名` set 列名 = 新的值 where 更新条件; -- 修改表中指定的数据
2
# 2、SQL修改语句实例
update `student` set student_id = 20171549,sag = 25 where student_id = 20171649;
update `student` set student_id = 20171549,sag = 80 where student_id = 20171649 and name = ‘张欣’; --where添加用 and 连接即可。
2
# 3.4 查询语句
# 1、语句语法
select 查询的字段1,查询的字段2,......form `表名` where 查询条件
where 可以省略,如果省略,查询的是表中所有的数据
2
# 2、SQL查询语句实例
select id,student_id,name from `student`;
-- 查询指定的行记录
select student_id from `student` where = 20171649;
-- *代表所有的列字段,不建议用
select * from `student`;
2
3
4
5
6
AS
-显示类的别名
-- 显示类的别名 AS
select student_id as 学号,name as 学生姓名,age as 年龄,sex as 性别 from student;
-- 显示类的别名 AS可以省略
select id 编号,student_id 学号,name 姓名,sex 性别,age 年龄 from student;
2
3
4
- 单条件查询
-- 单个条件查询 使用比较运算符 > < >= <= !=(不等于) <>(不等于)
select student_id,name,age from student where age>23; --筛选出年龄大于23岁的
select student_id,name,age from student where age!=23; --筛选出年龄不等于23岁的
2
3
多条件查询 -LIKE 模糊查询
查询关键字有:
%
_
or
in
not in
between and
is null
is not null
%
-- % 匹配多个字符
select sid,sname,sage from student where sname like '%欣%' -- 名字中带有欣的都可以查询出来
select sid,sname,sage from student where sname like '张%' -- 名字中带有张的都可以查询出来
2
3
_
-- _ 匹配一个字符(下划线)
select sid,sname,sage from student where sname like '_欣' -- 查询出X欣,名字只能有两个字符
select sid,sname,sage from student where sname like '张_' -- 查询出张X,名字只能有两个字符
select sid,sname,sage from student where sname like '张__' -- 查询出张XX,名字只能有三个字符
2
3
4
or
-- or 或者 输出编号1或者编号20的学生,如果没有20,就不会显示
select sid,sname,sage from student where
2
in
-- in 可以用来批量删除、修改、查询
select sid,sname,sage from student where sid in(1,3,5,7,8,10);
2
not in
-- not in 可以用来批量删除、修改、查询。排除专用
select sid,sname,sage from student where sid not in(1,3,5,7,8,10);
2
between and
-- between and 一个范围
select sid,sname,sage from student where sage between 18 and 25; 18-25岁的人
2
is null
-- is null
select sid,sname,sage from student where sage is null; -- 查询出年龄为空的
2
is not null
-- is not null
select sid,sname,sage from student where sage is not null; --查询出年龄不为空的
2
# 3、基础查询小练习
- 创建数据库 T 创建表:t_class、t_score、t_student、t_teacher
-- ----------------------------
-- Table structure for t_class
-- ----------------------------
DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class` (
`cid` int NOT NULL AUTO_INCREMENT COMMENT '班级编号',
`cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级名称',
`teacherid` int NOT NULL COMMENT '教师编号',
PRIMARY KEY (`cid`)
)
-- ----------------------------
-- Table structure for t_score
-- ----------------------------
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
`scid` int NOT NULL COMMENT '考试编号',
`sid` int NULL DEFAULT NULL COMMENT '考试编号',
`subject` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试科目',
`score` float(4, 1) NULL DEFAULT NULL COMMENT '考试成绩',
PRIMARY KEY (`scid`) USING BTREE
)
-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`sid` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
`sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
`gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
`age` int NULL DEFAULT NULL COMMENT '年龄',
`classid` int NULL DEFAULT NULL COMMENT '班级编号',
PRIMARY KEY (`sid`) USING BTREE
)
-- ----------------------------
-- Table structure for t_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
`tid` int NOT NULL AUTO_INCREMENT COMMENT '教师编号',
`tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师姓名',
`gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
PRIMARY KEY (`tid`) USING BTREE
)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
- 添加数据
-- 添加语法:insert into 表名(字段列表用逗号隔开) values (对应的值,用逗号隔开,字符串用单引号包含)
-- ------------------------------------------------------------------------------------
-- eg:插入编号3,姓名王五,性别男,班级1
INSERT INTO `t_student`(sid,sname,gender,age,classid) VALUES (3,'王五','男',NULL,'1');
INSERT INTO `t_student` VALUES (1,'张欣','男',NULL,'1'),(4,'大大','男',NULL,'2'),(2,'朱帅','男',NULL,'3');
2
3
4
5
- 修改数据
-- 修改语法:update 表名 set 列名=值 where 列名=值
-- ------------------------------------------------------------------------------------
-- eg:修改表中所有学生的班级号为2
UPDATE t_student SET classid = 2;
-- eg:修改表中王五的班级号为1
UPDATE t_student SET classid = 1 WHERE sname='王五';
2
3
4
5
6
查询语法:select 列名 from 表名 where 条件 order by 排序的列名【asc/desc】 group by 分组的列名 having 分组后的条件 简单查询语句
select 查询的字段1,查询的字段2,......form
表名
where 查询条件 where 可以省略,如果省略,查询的是表中所有的数据
-- eg:查询学生表所有信息
SELECT sid,sname,gender,age,classid FROM `t_student`;
-- eg:查询所有学生的姓名,性别
SELECT sname,gender FROM `t_student`;
-- eg:查询姓名为王五的学生姓名,性别
SELECT sname,gender FROM `t_student` WHERE sname = '王五';
2
3
4
5
6
- 查询语法:select
列名
from表名
where条件
order by排序的列名
【asc/desc】 group by分组的列名
having分组后的条件
模糊查询语句:like
,in
,not in
,between...and
,is null
,is not null
-- eg:查询所有姓带张的学生信息:where 列名 like 'X%'
SELECT sid,sname,gender,age,classid FROM t_student WHERE sname LIKE '张%';
-- eg:查询所有名字中带三的学生信息:where 列名 like '%x%'
SELECT sid,sname,gender,age,classid FROM t_student WHERE sname LIKE '%三%';
-- eg:查询所有老师编号是1或者2所在班级信息:可以使用or,也可以使用where 列名 in(1,2)
SELECT cid,cname,teacherid FROM t_class WHERE teacherid = 1 or teacherid = 2;
SELECT cid,cname,teacherid FROM t_class WHERE teacherid in(1,2);
-- 如果只知道老师的名字是老张,老林查询他们的班级信息
-- 先通过教师表查询Tid
-- 在班级表通过Tid查询班级信息
-- 然后串表嵌套查询
SELECT cid,cname,teacherid FROM t_class WHERE teacherid in(SELECT tid FROM t_teacher WHERE tname = '老张' or tname = '老林');
-- 查询老师编号不是1的班级信息,where 列名 not in(1)
SELECT cid,cname,teacherid FROM t_class WHERE teacherid NOT IN(1); --where
SELECT cid,cname,teacherid FROM t_class WHERE teacherid !=1; --not in
-- 查询学生年龄在20-23岁之间的学生信息,可以使用and也可以采用where age between 20 and 25
SELECT sid,sname,gender,age,classid FROM t_student WHERE age BETWEEN 20 AND 25;
SELECT sid,sname,gender,age,classid FROM t_student WHERE age>=20 AND age<=25;
-- 查询班级名称不为空的数据 where cname is not null
SELECT cid,cname,teacherid FROM t_class WHERE cname is not null;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26