数据库一-CSDN博客

本文链接：https://blog.csdn.net/qq_53706119/article/details/124082252

数据库（DB , DataBase）

概念：安装在操作系统（window，Linux，mac ...）之上的数据仓库软件

作用：存储数据，管理数据

数据库分类

关系型数据库：（SQL）

MySQL,Oracle，SqlServer，

通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库: (No SQL) Not Only SQL

Redis,MongDB

以对象进行存储，通过对象的自身属性来决定

DBMS(数据库管理系统)

数据库的管理软件，科学有效的管理数据。维护和获取数据

基本的命令行操作

mysql -uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and host='localhost'; -- 修改用户密码

flush privileges; -- 刷新权限--

----所有的语句都使用;结尾

show databases; -- 查看所有的数据库

mysql> use school; -- 切换数据库 use 数据库名

Database changedmysql> show tables; -- 显示数据库中所有表的信息

mysql> describe student; -- 显示表的详细信息

create database zyy; -- 创建一个数据库

exit -- 退出连接--

单行注释/**

多行注释*/

DDL 数据库定义语言

用来定义数据库对象,库、表、列等;创建、删除、修改:库,表结构;

DML 数据库操作语言

用来定义数据库记录(数据);增、删、改:表记录

DQL 数据库查询语言

所有的查询操作都用它 select
简单的查询，复杂的查询它都能做
数据库中最核心的语言，最重要的语句
使用频率最高的语句

DCL 数据库控制语言

设置或更改数据库用户或角色权限

由 GRANT 和 REVOKE 两个指令组成

操作数据库语句

操作数据库 > 操作数据库中表 >操作数据库中表的数据

==mysql关键字不区分大小写==

操作数据库

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] student;

2.删除数据库

DROP DATABASE [IF EXISTS] zyy;

3.使用数据库

-- 如果你的表名或者字段名是一个特殊字符，就需要带上``

`USER `student`;

4.查看数据库

SHOW DATABASES; -- 查看所有的数据库

数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数（金融计算的时候，一般是使用decimal）

字符串

char 字符串固定大小的 0~255
varchar 可变字符串 0~65535 （常量的变量 String）
tinytext 微型文本 2^8 -1
text 文件串 2^16 -1 (保存大文本)

时间日期

date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳，1970.1.1到现在的毫秒数！较为常用！
year 年份表示

null

没有值，未知
==注意：不要使用NULL进行运算，结果为NULL==

数据库的字段属性

Unsigned:

无符号的整数
声明了该列不能声明为负数

Zerofill:

0填充的
不足的位数，使用0来填充 int(3) 5 --- 005

自增：

通用理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键，index，必须是整数类型
可以自定义设计主键自增的起始值和步长

非空 null/not null：

not null，如果不给他赋值，就会报错
null，如果不给他赋值，默认就是null

默认：

设置默认的值
如果不赋值，就会存默认值

拓展：

/*每个表，都必须存在以下五个字段未来做项目用的，表示一个记录存在的意义

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间*/

创建数据库表

-- 目标：创建一个school数据库

-- 创建student学生表，使用sql创建

-- 学号姓名性别出生日期家庭地址 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 '姓名',

`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',

`birthday` DATETIME NOT NULL COMMENT '出生日期',

`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',

`email` VARCHAR(30) 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`; -- 查看表的结构

修改和删除数据表字段

修改

-- 修改表名 ALTER TABLE `原表名` RENAME AS `新表名`;

ALTER TABLE `teacher` RENAME AS `teacher1`;

-- 新增表的字段 ALTER TABLE `表名` ADD 字段名列属性

ALTER TABLE `teacher1` ADD age INT(3);

-- 修改表的字段（重命名，修改约束！）

-- ALTER TABLE `表名` MODIFY 字段名列属性;

-- ALTER TABLE `表名` CHANGE 原字段名现字段名列属性;

ALTER TABLE `teacher1` MODIFY age VARCHAR(3); -- 修改约束

ALTER TABLE `teacher1` CHANGE age age1 INT(3);-- 字段重命名

-- 删除表的字段ALTER TABLE `teacher1` DROP age1;

删除

-- 删除表（如果存在再删除）

DROP TABLE IF EXISTS teacher1;

注意点：

sql关键字大小写不敏感，建议大写写小写
所有的符号全部用英文

Insert

-- 插入语言

-- INSERT INTO `表名`(`字段1`,`字段2`,`字段3`...) VALUES('值1','值2','值3'...);

INSERT INTO `grade`(`name`) VALUES('大四');INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('张三','1995-11-20','1');

-- 一般写插入语句，我们一定要数据和字段一一对应。

-- 插入多条-- INSERT INTO `表名`(`字段1`,`字段2`...) VALUES('值1','值2'...),('值1','值2'...)...;

INSERT INTO `grade`(`name`) VALUES('大一'),('大二');

INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('李四','1994-11-20','2'),('王五','1995-11-20','1');

注意事项：

字段和字段之间使用逗号隔开
字段是可以省略，但是后面的值必须要一一对应，不能少
可以同时插入多条数据，VALUES后面的值，需要使用逗号分开VALUES(),()...

Update

-- 修改学员名字

UPDATE `student` SET NAME='zyy' WHERE id='1';

-- 通过多个条件定位数据

UPDATE `student` SET NAME='hehe' WHERE NAME='zyy' AND id_grade='2';

-- 不指定条件的情况，会改动所有的表UPDATE `student` SET NAME='all';

-- 语法--

UPDATE 表名 SET 列名=值[,列名=值,列名=值,列名=值...] [WHERE 条件]

条件：where 字句运算符 id等于某个值，大于某个值，在某个区间内修改

操作符	含义	范围	结果
=	等于	5=6	false
<> 或者 !=	不等于	5!=6	true
>
<
>=
<=
BETWEEN ... AND ...	某个范围内	BETWEEN 1 AND 3	[1,3]
AND	和 &&	5>1 and 1>2	false
OR	或 \|\|	5>1 or 1>2	true

注意事项：

列尽量带上``
条件，筛选的条件，如果没有指定，则会修改所有的列

value，可以是一个具体的值，也可以是一个变量

UPDATE `student` SET birthday=CURRENT_TIME WHERE NAME='hehe' AND id_grade='2';

多个设置的属性之间，使用英文逗号隔开（后面trim,可以干掉多余的逗号）

delete

语法： delete from 表名 [where 条件]

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

truncate

作用：完全清空一个数据库表，标的结构和索引约束不会变！

-- 清空表
TRUNCATE `student`;

delete 和 truncate 区别

相同点：都能删除数据，都不会删除表结构
不同：
- truncate 重新设置自增列，计数器会归零
- truncate 不会影响事务

select 语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

注意：[]括号代表可选的，{}括号代表必选的

指定查询字段

 -- 查询全部的学生   SELECT 字段 FROM 表名;
  SELECT * FROM student;
  
  -- 查询指定字段
  SELECT student_name, student_no FROM student;
  
  -- 别名，给结果起一个名字 AS  可以给字段起别名，也可以给表起别名
  
  SELECT student_name AS '学号', student_no AS '姓名' FROM student;
  
  -- 函数 concat(a,b)
  SELECT CONCAT('姓名：', student_no)  AS '新姓名' FROM student;

有的时候，列表名不是那么见名知意，我们可以起别名

去重 distinct

  -- 查询全部的考试成绩
  SELECT * FROM result;
  -- 查询有哪些同学参加了考试
  SELECT `student_no` FROM result;
  -- 发现重复数据，去重
  SELECT DISTINCT `student_no` FROM result;

数据库的列（表达式）

 -- 查询系统版本（函数）
  SELECT VERSION();
  -- 用来计算（表达式）
  SELECT 100*3 -1 ;
  -- 查询自增的步长（变量）
  SELECT @@auto_increment_increment;
  
  -- 学员考试成绩 +1 查看
  SELECT `student_no`,`student_result` + 1 AS '提分后' FROM result;

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量

select 表达式 from 表名

where子句逻辑运算符

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成，结果布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or \|\|	a or b a\|\|b	逻辑或
not !	not a !a	逻辑非

==尽量适应英文字母==

-- 查询考试成绩在 95 ~ 100分之间

SELECT student_no,student_result FROM result;

-- and
SELECT student_no,student_result FROM result
WHERE student_result>95 AND student_result<=100;

-- && 
SELECT student_no,student_result FROM result
WHERE student_result>95 && student_result<=100;

-- 模糊查询（区间）
SELECT student_no,student_result FROM result
WHERE student_result BETWEEN 95 AND 100;

-- 除了1000号学生之外的学生的成绩
-- !=
SELECT student_no,student_result FROM result
WHERE student_no != 1000;

-- not
SELECT student_no,student_result FROM result
WHERE NOT student_no = 1000;

模糊查询操作符

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
BWTWEEN...AND...	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到任意个字符）
--   _（代表1）

-- 查询姓刘的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘%';

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘_';

-- 查询姓刘的同学，名字后面有两个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘__';

-- 查询名字中间有嘉字的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '%%嘉%';

-- in (具体的一个或者多个值)
-- 查询学号1001,1002,1003号学号
SELECT `student_no`,`student_name` FROM `student`
WHERE student_no IN ('1001','1002','1003');

-- 查询在北京的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address IN ('北京');

-- null
-- 查询地址为空的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address = '' OR address IS NULL;

-- not null
-- 查询有出生日期的同学 不为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NOT NULL;

-- 查询没有出生日期的同学 为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NULL;