第二十章：数据库_数据库 replace 可以用复合组件吗-CSDN博客

本文链接：https://blog.csdn.net/m0_61927991/article/details/127159954

在书写数据库的名字与表的名字的时候喜欢加上 `table` 避免关键字

一、数据库

（1）创建数据库：

CREATE DATABASE hsp_db01 (创建数据库)

CREATE DATABASE hsp_db01 CHARACTER SET utf8 (创建数据库并指定字符集为utf8)

CREATE DATABASE hsp_db01 CHARACTER SET utf8 COLLATE utf_bin (创建数据库并指定字符集为utf8,并设置校验规则为区分大小写)

（2）查询、删除数据库：

SHOW DATABASES (查看所有数据库）

SHOW CREATE DATABASE hsp_db01 (查看hsp数据库的创建语句)

DROP DATABASE IF EXITE hsp_db01 (删除存在的数据)

（3）备份与恢复数据库：

注意：这个指令要在dos命令下面执行

mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql (备份hsp02和hsp03库中数据到D盘的bak.sql里面)

source d:\\bak.sql (将d盘的sql数据备份回去：方法一)

直接打开d:\\bak.sql文件直接复制粘贴到mysql命令行

二、表

（1）创建表：

CREATE TABLE `table_name`

{

field1(字段名) datatype(字段类型),

field2 datatype,

} character set 字符集 collate 校对规则 engine 存储引擎

（2）修改表：

ALTER TABLE hsp_table01 ADD col2 VERCGAR(32) NOT NULL DEFAULT ' ' AFTER col1 (给hsp表添加一个col2列加在col1后面)

ALTER TABLE hsp_table01 MODIFY job VARCHAR(60) NOT NULL DEAFAULT ' ' (修改job列使其长度为60)

ALTER TABLE hsp_table01 DROP sex (删除sex列)

RENAME TABLE hsp_table01 TO hsp_table02 （修改表名为hsp_table02）

ALTER TABLE hsp_table01 CHARACTER SET utf8 (修改字符集为utf8)

ALTER TABLE hsp_table01 CHANGE `name` `username` VARCHAR(40) NOT NULL DEFAULT ' ' (修改列名name为username其余不变)

（3）删除表:

DROP TABLE [IF EXITE] hsp_table (删除表)

三、字段类型或者列类型

红色为常用

（1）整型:

TINYINT(1字节) smallint(2字节) mediumint(3字节) int(4字节) bigint(8字节)

（2）小数类型:

floa(单精度4字节) double(双精度 8字节) decimal[M,D][大小不确定]

（3）文本类型:

char(0-255) verchar(0-65535) text(0-2^16) longtext(0-2^32)

（4）二进制类型:

blob(0-2^16) longblob(0-2^32)

（5）日期类型：

data[日期年月日] time[时间时分秒] datetime[年月日时分秒] timestamp[时间戳] year[年]

四、C[create]R[read]U[update]D[delete]语句

（1）INSERT语句：

INSERT INTO `hsp_table`(id,goods_name,price) VALUES(10,'华为手机',190) (插入数据)

INSERT INTO `hsp_table`(id,goods_name,price) VALUES(10,'华为手机',190),(11,'苹果',290) (插入多条数据)

INSERT INTO `hsp_table` VALUES(10,'华为手机',190) （需要对其列名）

（2）UPDATE语句：

UPDATE employee SET salary = 5000 (所有员工的salary都变成5000)

UPDATE employee SET salary = 5000 where id = 10 (将id为10的员工的salary变成5000)

（3）DELETE语句：

DELETE FROM employee （删除表中所有数据）

DELETE FROM employee WHERE id = 10 (将id为10的数据删除)

（4）SELECT语句：

SELECT 【DISTINCT(去重)】 *|{colum1，colum2，colum3...} FROM table (基本语句)

SELECT * FROM student (查询学生所有信息)

SELECT DISTINCT * FROM student (查询学生所有信息去重)

SELECT (chinese+english+math) FROM student (查询运算之后的结果)

SELECT (chinese+english+math) AS total FROM student (查询运算之后的结果起别名为综合 AS可以用空格的)

SELECT * FROM table WHERE （条件查询）

SELECT * FROM student ORDER BY math ASC(升序：默认) | desc(降序)

SELECT COUNT(*) FROM student 统计一共有多少个

SELECT SUM(*) FROM student 总和

SELECT AVG(*) FROM student 平均值

SELECT MAX(*) FROM student 最大值

SELECT MIN(*) FROM student 最小值

SELECT MAX(sal) FROM student GROUP BY id 按照id来分组查询平均工资

SELECT MAX(sal) FROM student GROUP BY id HAVING AVG(sal) < 2000 按照id来分组查询平均工资，并且平均工资小于2000

条件查询的知识点：

>        <         <=         >=         =         <>         != (大于、小于、大于(小于)等于、等于、不等于)

BETWEEN...AND....        在某个区间的值

IN(set) 显示在in列表中的值例如：in(100,200)

LIKE `张pattern` NOT LIKE `` 模糊查询

IS NULL 判断是否为空

and 多条件同时成立 or或者成立一个即可 not不成立

五、函数

（1）字符串函数：

CHARSET(str) 返回结果字符集

CONCAT(str,[....]) 连接字符串

INSTR(string,substring) 返回substring在string中出现的位置，没有返回0

UCASE(string2) 转换成大写

LCASE(String2) 转换成小写

LEFT(string,length) 从左边取出length个字符

LENGTH(str) 字符串长度

REPLACE(str,search_str,replace_str) 在str字符串中使用search查找，使用replace_str替换

STRCMP(str1,str2) 比较两个字符串大小

SUBSTRING(str,position[,length]) 从str字符串的position开始，取出length个字符串

LTRIM(string2) RTRIM(str2) 去除前后端空格

案例：SELECT CONCAT(*) FROM table

（2）日期函数：

CURRENT_DATE() 当前日期

CURRENT_TIME() 当前时间

CURRENT_TIMESTAMP 当前时间戳

DATE(datetime) 返回datetime的日期部分

DATE_ADD(date2,INTERVAL d_value d_type) 在date2中加上日期或时间

DATE_SUB(date2,INTERVAL d_value d_type) 在date2中减去日期或时间

DATEDIFF(date1,date2) 两个时间差(结果是天)

TIMEDIFF(date1,date2) 两个时间差(结果是多少时多少分多少秒)

NOW() 当前时间

YEAR|Month|DATE(datetime) 年月

案例：SELECT CURRENT_DATE() FROM table

（3）加密函数与系统函数：

SELECT USER() FROM DUAL; 查询用户 -- 用户名@ip地址

SELECT DATABASE(); 查询当前数据库

SELECT MD5("hsp") 将hsp进行MD5加密

SELECT PASSWORD('hsp') 加密函数

（4）流程控制函数：

SELECT IF(TRUE,'北京','上海') FROM DUAL(占位) 返回北京，相当于三元运算符

SELECT IFNULL(NULL,'韩顺平') //返回韩顺平 SELECT IFNULL('罗翔','韩顺平') //返回罗翔前面不为空返回前面，为空返回后面

SELECT CASE WHEN TRUE THEN 'jack' WHEN FALSE THEN 'tom' ELSE 'mary' END 相当于switch那个为真就返回那个

六、增强查询

（1）分页查询：

每页显示3条记录，分别显示1页2页3页

SELECT * FROM user LIMIT start , rows

SELECT * FROM user LIMIT 0 , 3 第一页，显示3条

SELECT * FROM user LIMIT 3 , 3 第二页，显示3条

SELECT * FROM user LIMIT 6 , 3 第三页，显示3条

（2）语句顺序问题：

顺序为：group by，having，order by，limit

七、多表查询

（1）笛卡尔集：

SELECT * FROM user_table,employee_table 默认查询返回的是笛卡尔集，所有的都有匹配拼接

SELECT * FROM user_table,employee_table where user_table.id = employee_table.id 加上过滤条件哦，避免笛卡尔集

（2）自连接：

SELECT * FROM user_table,employee_table where user_table.id = employee_table.id 加上过滤条件哦，避免笛卡尔集

（3）子查询：

SELECT * FROM emp WHERE id = (SELECT id from user) 单行子查询，就是子只返回一个结果

SELECT * FROM emp WHERE job IN (SELECT DINSTINCT jop FROM emp) 多行子查询，子返回多个结果

SELECT * FROM student WHERE (math,chinese,english) = SELECT math,chinese,english FROM student 多列子查询

（4）all和any的使用：

SELECT * FROM user WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30) 查询比30号所有人都大的人

SELECT * FROM user WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30) 查询比30号任何一个大的人

（5）表复制：

INSERT INTO mytable1 (id,`name`,`job`) SELECT no,`name`,`job` FROM emp; //表的复制

INSERT INTO mytable1 SELECT * FROM mytable1; 自我复制

（6）合并查询：

SELECT * FROM user UNION ALL SELECT * FROM emp 合并结果不会去重

SELECT * FROM user UNION SELECT * FROM emp 合并结果会去重

（7）外连接：

左外连接：左边的表没有匹配上也显示右外连接：右边没有匹配上也显示写在左边的是左表，写在右边的是右表

SELECT * FROM stu LEFT JOIN exam ON stu.id = exam.id stu表完全显示哦

SELECT * FROM stu RIGHT JOIN exam ON stu.id = exam.id exam表完全显示哦

八、约束

（1）primary key主键：

primary key不能重复而且不能为null
一个表最多只能有一个主键,但是可以有复合组件例如 primary key(id,name)
主键的指定方式有两种
直接在字段名后指定：字段名 primary key
在表定义最后写 primary key(列名);
使用desc 表名，可以看到primary key的情况

方式一：CREATE TABLE tablename

                                        (id INT,

                                            name VARCHAR(255) PRIMARY KEY,

                                            email VARCHAR(20));

方式二：CREATE TABLE tablename

                                        (id INT,

                                            name VARCHAR(255),

                                            email VARCHAR(20)，

                                            PRIMARY KEY(id,name)); //复合组件

（2）not null 非空：

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

字段名字段类型 NOT NULL

（3）unique 唯一

当定义了唯一约束后，该列值时不能重复的。

字段名字段类型 UNIQUE

（4）foreign key 外键

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null（学生/班级）。注意先创建主表再创建从表

FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或unique字段名)

CREATE TABLE my_stu(

id INT PRIMARY KEY,

name VARCHAR(32) NOT NULL DEFAULt ' ',

class_id INT,

FOREIGN KEY (class_id) REFERENCES my_class(id)) //注意my_class这个表我没有创建

外键指向的表的字段，要求时primary key 或者是unique
表的类型时innodb，这样的表才支持外键
外键字段的类型和主键字段的类型一致(长度可以不同)
外键字段的值，必须再主键字段出现过，或者为null [前提是外键字段允许为null]
一旦简历主外键的关系，数据不能随意删除了

（5）check 检查

了解语法上支持，支持校验，但不会生效了解即可

sex VARCHAR(6) CHECK (sex IN('man','woman')) //mysql没用，其他数据库可以用

九、自增长 auto_increment

一般来说自增长是和primary key配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整型的(虽然小数也可以但是非常非常少这样使用)
自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto_increment = xxx;
你可以自己给值，但建议和警告你不要这样用

字段名整形 primary key auto_increment

十、索引

索引用来优化查询速度，采用二叉树的方法，空间换时间

（1）创建索引

主键索引，主键自动为主键索引（类型primary）
唯一索引（unique）
普通索引（index）
全文索引(FULLTEXT)[适用于MyISAM] 开发中考虑使用：全文搜索Solr 和 ElasticSearch (ES)java开发中常用

SHOW INDEXES FROM table1 查看某个表是否有索引

CREATE UNIQUE INDEX id_index ON table1(id); //创建一个唯一索引，索引名称为 id_index，不会重复列值的时候使用

CREATE INDEX id_index ON table1(id); //创建一个普通索引方式一，索引名称为 id_index，重复列值的时候使用普通索引

ALTER TABLE table1 ADD INDEX id_index(id); //创建一个普通索引方式二，索引名称为 id_index，重复列值的时候使用普通索引

ALTER TABLE table1 ADD PRIMARY KEY (id); //表没有添加主键添加主键命令，主键自带主键索引

（2）删除索引

DROP INDEX id_index ON table1 //id_index是table1里面所有的名字，删除普通索引

ALTER TABLE table1 DROP PRIMARY KEY //这就是删除主键索引

修改的话，先删，后创

（3）索引的创建规则

较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引，及时频繁作为查询条件
更新非常频繁的字段不适合创建索引
不会出现再WHERE子句中字段不该创建索引

十一、事务

保证数据的一致性，要么都成功要么都失败。

START TRANSACTION  //1.开启事务
SAVEPOINT a        //2.设置保存点
INSERT INTO table VALUES(100,"tom"); //执行dml操作
SAVEPOINT b        //2.设置保存点
INSERT INTO table VALUES(200,"jack"); //执行dml操作
ROLLBACK TO b      //回退到b，她下面的dml操作不会执行
ROLLBACK TO a      //回退到a, 这样都没了
ROLLBACK           //这样就表示回到最开始的地方
COMMIT             //提交事务之后就不可以回滚了

（1）事务隔离级别

脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读

不可重复读：当删除或修改，一直返回不同的结果集，此时发送不可重复读

幻读：当查询在同一事物提交插入操作，返回不同结果集，此时发生幻读

SELECT @@tx_isolation; 查询事务隔离级别默认为 Repeatable read

SELECT @@global.tx_isolation; 查看当前系统隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ；设置当前会话隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ；设置当前系统隔离级别

mysql默认级别为REPEATABLE READ,一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

原子性一致性隔离性持久性