Mysql数据库基础

最新推荐文章于 2024-10-16 11:14:53 发布

✘155

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量614

点赞数 27

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_69907434/article/details/140607472

版权

1.数据库

1.什么是数据库

数据库是一种组织、存储和管理数据的电子系统，它允许用户高效地访问、更新和管理信息。通过使用结构化查询语言（SQL）或特定的数据访问方法，数据库能够确保数据的一致性、完整性和安全性。它支持多用户共享数据，并控制对数据的并发访问和恢复操作，以防止数据丢失或损坏。数据库可以是关系型的，如MySQL或Oracle，也可以是非关系型的，如MongoDB或Cassandra，每种类型都有其特定的优势和适用场景。在现代信息技术中，数据库是几乎所有应用程序的核心组成部分，从简单的个人应用到复杂的企业级解决方案，都依赖于数据库来存储和处理关键数据。

2.常见的数据库

Oracle（神喻）：甲骨文（最高！）；
DB2：IBM；
SQL Server：微软；
Sybase：赛尔斯；
MySQL：甲骨文；
PostgreSQL: 开源

3.理解数据库

RDBMS(关系型数据库管理系统;) = 管理员（manager）+仓库（database）
database = N个table
table：
- 表结构：定义表的列名和列类型！
- 表记录：一行一行的记录！

我们现在所说的数据库泛指关系型数据库管理系统（RDBMS - Relational database management system)，即数据库服务器。在这里插入图片描述当我们安装了数据库服务器后，就可以在数据库服务器中创建数据库，每个数据库中还可以包含多张表。

数据库表就是一个多行多列的表格。在创建表时，需要指定表的列数，以及列名称，列类型等信息。而不用指定表格的行数，行数是没有上限的。下面是tab_student表的结构：
在这里插入图片描述
当把表格创建好了之后，就可以向表格中添加数据了。向表格添加数据是以行为单位的！下面是s_student表的记录：

s_id	s_name	s_age	s_sex
S_1001	zhangSan	23	male
S_1002	liSi	32	female
S_1003	wangWu	44	male

大家要学会区分什么是表结构，什么是表记录。

2.SQL语句

1.什么是SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQ标准（ANSI/ISO）有：

SQL-92：1992年发布的SQL语言标准；
SQL:1999：1999年发布的SQL语言标签；
SQL:2003：2003年发布的SQL语言标签；

这些标准就与JDK的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

虽然SQL可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言，其它数据库都不支持！当然，Oracle或SQL Server都有自己的方言。

2.基本操作

#语法:
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集 COLLATE 排序规则字符集]；
创建数据库：
CREATE DATABASE [IF NOT EXISTS] mydb1；

创建数据库，例如：CREATE DATABASE mydb1，创建一个名为mydb1的数据库。如果这个数据已经存在，那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1，在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

DEFAULT CHARACTER: 默认的字符集, 如果没有设置,默认是utf8

COLLATE: 排序规则, 如果没有设置,默认是: utf8_general_ci

CREATE DATABASE IF NOT EXISTS mydb2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

注意:

字符集:

utf8: utf8 编码最⼤字符长度为 3 字节，如果遇到 4 字节的宽字符就会插⼊异常了,比如Emoji表情,就无法使用utf8存储,推荐使用utf8mb4

- utf8mb4: mb4即most bytes 4，专门用来兼容四个字节即以上的unicode, 在Mysql在5.53版本之后
--删除数据库：
DROP DATABASE [IF EXISTS] mydb1;

删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

--修改数据库编码：
ALTER DATABASE mydb1 CHARACTER SET utf8;
-- 查看排序编码
show variables like 'collation_%';
-- 查看字符集
show variables like 'character_set_%';

修改数据库mydb1的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。

3.操作表

创建表：

CREATE TABLE 表名(

 列名 列类型,

 列名 列类型,

 ......

);

例如：

CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR (20),
age INT,
gender VARCHAR (10)
) ;

再例如：

CREATE TABLE emp (
eid CHAR(6) COMMENT '编号',
ename VARCHAR (50) COMMENT '姓名',
age INT COMMENT '年龄',
gender VARCHAR (6) COMMENT '性别',
birthday DATE  COMMENT '出生日期',
hiredate DATE  COMMENT '入职日期',
salary DECIMAL (7, 2) COMMENT '薪水',
RESUME VARCHAR (1000)  COMMENT '简介'
)  COMMENT '员工表' ;

再例如: 设置表的使用的数据库引擎, 默认字符集,默认排序规则:

CREATE TABLE emp (
eid CHAR(6) COMMENT '编号',
ename VARCHAR (50) COMMENT '姓名',
age INT COMMENT '年龄',
gender VARCHAR (6) COMMENT '性别',
birthday DATE  COMMENT '出生日期',
hiredate DATE  COMMENT '入职日期',
salary DECIMAL (7, 2) COMMENT '薪水',
RESUME VARCHAR (1000)  COMMENT '简介'
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT '员工表' ;
``

3.DML

1.插入数据

插入指定列语法：

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stu (sid, sname, age, gender) 
VALUES
('s_1001', 'zhangSan', 23, 'male');

INSERT INTO stu (sid, sname) 
VALUES
('s_1001', 'zhangan') ;

插入全部列语法：

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');
注意：所有字符串数据必须使用单引用！

2.修改数据

语法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stu SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001';

UPDATE stu SET sname='wangWu', age='30' WHERE age>60 OR gender='female';

UPDATE stu SET sname='liSi', age='20' WHERE age>50 AND gender='male';

UPDATE stu SET gender='female' WHERE gender IS NULL;

UPDATE stu SET age=age+1 WHERE sname='zhaoLiu';

3.删除数据

语法：

DELETE FROM 表名 [WHERE 条件]

DELETE FROM stu WHERE sid='s_1001';

DELETE FROM stu WHERE sname='chenQi' OR age > 30;

--删除所有  慎用
DELETE FROM stu;

语法：

TRUNCATE TABLE 表名

TRUNCATE TABLE stu;

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

4.DCL

1.查询用户

select * from mysql.user;

在这里插入图片描述
其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

2.创建用户

CREATE USER 用户名@地址 IDENTIFIED BY '密码';

–user1用户只能在localhost这个IP登录mysql服务器
CREATE USER user1@localhost IDENTIFIED BY ‘123’;

–user2用户可以在任何电脑上登录mysql服务器
CREATE USER user2@‘%’ IDENTIFIED BY ‘123’;

3.查看用户权限

语法：

SHOW GRANTS FOR 用户名;
SHOW GRANTS FOR user1@localhost;

4.删除用户

语法：

DROP USER 用户名;
DROP USER user1@localhost;

5.修改用户密码

DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

语法：

SELECT selection_list /*要查询的列名称*/

 FROM table_list /*要查询的表名称*/

 WHERE condition /*行条件*/

 GROUP BY grouping_columns /*对结果分组*/

 HAVING condition /*分组后的行条件*/

 ORDER BY sorting_columns /*对结果分组*/

 LIMIT offset_start, row_count /*结果限定*/

6.基础查询

1.查询所有列

SELECT * FROM stu;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

2.查询指定列

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT sid, sname, age FROM stu;

7.条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

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

BETWEEN…AND

IN(set)

IS NULL

AND

OR

NOT

逻辑运算符:

逻辑运算符

AND 或 && 并且 (多个条件同时成立)

OR 或 || 或者 (多个条件任意一个成立)

NOT 或 ! 非 , 不是

8.模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

-- 查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';

-- 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';

-- 查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';

-- 查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';

9.排序

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ,...;

排序方式

ASC : 升序(默认值)
DESC: 降序

注意事项：

• 如果是升序, 可以不指定排序方式ASC ;

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

10.聚合函数

聚合函数是用来做纵向运算的函数：

将一列数据作为一个整体，进行纵向计算

函数	描述
COUNT()	统计指定列不为NULL的记录行数
MAX()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
MIN()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
SUM()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
AVG()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

语法

SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的。

11.count

当需要纵向统计时可以使用COUNT()。

查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

查询emp表中有奖金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal > 2500;

统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

查询有佣金的人数，以及有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

12.SUM和AVG

当需要纵向求和时使用sum()函数。

查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

查询所有雇员月薪和，以及所有雇员奖金和：

SELECT SUM(sal), SUM(comm) FROM emp;

查询所有雇员月薪+奖金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

13.MAX和MIN

查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

14.分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

语法:

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组

之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

执行顺序: where > 聚合函数 > having 。

支持多字段分组, 具体语法为 : group by columnA,columnB

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;