MySQL学习（第一周7.8-7.12）-CSDN博客

本文链接：https://blog.csdn.net/MeiSama_w/article/details/140377444

数据库基础

首先，什么是数据库，什么是数据库管理系统

数据库：是指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统：一般是指操作和管理数据库的大型软件，用于建立，使用和维护数据库，对数据库进行管理和控制，保证数据库完整性和安全性的软件

常见的数据库管理系统有：ORACLE(甲骨文)，MySQL，sql server，达梦等等

在了解完数据库的基本概念后，接下来学习安装和一些基本使用命令

一，安装mysql for windows（Linux）这里

1，访问官网，下载对应版本的MySQL版本（这里使用8.0.33）

解压后，记住存储目录

2，配置系统环境变量

右键此电脑-属性-高级属性-配置环境变量-系统环境变量-点击path-新建-将MySQL解压路径复制进去

3，创建或打开my.ini文件，配置内容如下

[mysqld]

# 设置 3306 端口

port=3306

# 设置 mysql 的安装目录（记得改成自己的安装目录

basedir=D: \\software\\mysql8.0

# 设置 mysql 数据库的数据的存放目录（同上

datadir=D: \\software\\mysql8.0 \\data

# 允许最大连接数

max_connections=200

# 允许连接失败的次数。

max_connect_errors=10

# 服务端使用的字符集默认为 utf8mb4

character-set-server=utf8mb4

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

# 默认使用 “mysql_native_password” 插件认证

#mysql_native_password

default_authentication_plugin=mysql_native_password

[mysql]

# 设置 mysql 客户端默认字符集

default-character-set=utf8mb4

[client]

# 设置 mysql 客户端连接服务端时默认使用的端口

port=3306

default-character-set=utf8mb4

配置完在安装路径下创建data文件夹

4，安装

使用管理员权限打开cmd. 进入安装文件/bin

初始化数据库

mysqld --initialize --console

执行结束后会在最后生成数据库初始化密码，记得保存

ps：如果没记住，删除datadir目录中的所有内容，重新初始化即可

5，安装服务，启动服务

mysqld--install mysql（根据需要，此项可改）

安装完成后

net start mysql（你的服务名)

6，链接数据库

使用cmd. 进入你的安装目录下的/bin

mysql -u root -p

enter password: 上文安装中的初始密码

进入后先修改密码

alter user 'root'@'localhost' identied with mysql_native_password BY '新密码 ';

ps：新密码在学习中尽量简单，例如:123456

退出数据库命令：quit；

至此数据库安装以及初始化结束

二，SQL语言

1，操作数据库基本语句

(1) 查看当前数据库命令：

show databases;

(2) 查看当前数据库定义信息：

show create database [库名];l

(3) 修改数据库编码方式：

alter database 数据库名 character set 编码方式;

(4) 删除数据库

drop [库名]

(5) 切换/使用数据库名

use [库名]

2，操作表的基本语句

(1)创建新表

CREATE TABLE 表名 (

列名1 数据类型 [ 约束 ],

列名2 数据类型 [ 约束 ],

列名n 数据类型 [ 约束 ]

);

ps:

常用的数据类型有

int ：整型

double ：浮点型

char ：固定长度字符串类型

varchar ：可变长度字符串类型

text ：字符串类型

blob ：字节类型

time：时间类型

date ：日期类型

timestamp ：时间戳类型

datetime: 日期时间类型

（2）删除表

drop table [表名];

（3）查看当前目录下的所有表

show tables;

（4）扫描字段信息

desc [表名];

（5）增加列

alter table [表名] add [添加的列名] [数据类型];

（6）修改列

alter table [ 表名] change [ 旧列名] [新列名] [新的数据类型];

（7）删除列

alter table [ 表名] drop [ 列名];

ps：一次只能删除一列

（8）修改表名

alter table [ 旧表名] rename [ 新表名] ;

（9）删除表中内容

delete from [表名];

（10）删除表

drop table [表名]；

（11）向表中插入数据

INSERT INTO [表名] (列名1，列名2) VALUES(列名1对应数据,列名2对应数据);

（12）插入多条数据

INSERT INTO [表名] (列名1，列名2) VALUES(列名1对应数据,列名2对应数据),

(列名1对应数据,列名2对应数据),

(.......);

例如：

INSERT INTO mytb(id,name,age,sex,image) VALUES(3,'对的','24','女',1),
   (4,'弱智','24','女',1),
   (5,'哈哈','40','女',1),
   (6,'有病','50','女',1);

（13）更新表数据

UPDATE FROM [表名] SET 列名=修改项 WHERE 限定条件;

3，数据库查询语言（创建了stu，emp，dept三张虚拟表）

（1）简单查询

-- 查询所有列
select * FROM stu;

ps：在考虑sql优化的时候不建议使用*，直接写字段

-- 查询指定的列各字段之间用逗号隔开
SELECT sname,age,gender FROM stu;

（2）条件查询

什么是条件查询：条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

案例：

-- 查询性别为女，并且年龄在50岁以下的
SELECT * FROM stu WHERE gender='female' AND age < 50;

-- 查询学号为1，2，3的前三位同学
SELECT * FROM stu WHERE sid in('S_1001','S_1002','S_1003');

-- 查询年龄为空的记录
SELECT * FROM stu WHERE age is NULL;

-- 查询年龄在20-40之间的记录
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
SELECT * FROM stu WHERE age > 20 and age < 40;

-- 查询性别非男的记录
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE not gender='male';

-- 查询年龄不为空的记录
SELECT * FROM stu WHERE not age is null;

条件查询就是对where语句的更改来达成查询目的

（3）模糊查询

模糊查询的关键字是like，看到查询语句中带like一般就是模糊查询

模糊查询的通配符有 _:任意一个字符

%:任意0~n个字符

案例：

-- 查询姓名是三个字组成的

SELECT * FROM stu WHERE sname LIKE'___';

-- 查询姓名中以z开头的记录
SELECT * FROM stu WHERE sname LIKE'z%';
SELECT * FROM stu WHERE sname LIKE'z_';(仅适用于两个字符的记录)

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

-- 查询字母中带a的记录
SELECT * FROM stu WHERE sname LIKE '%a%';

（4）字段控制查询

①去重查询（查询不更改表数据，只是查询结果不同）

去掉emp表中sal列的重复，并显示出来

SELECT DISTINCT sal FROM emp;

②混合计算

查看雇员的月薪与佣金之和

SELECT *,sal+comm FROM emp;

ps：进行计算的列必须都为数值类型，否则会报错

拓展：

有些列数据为null 直接进行运算会算不出来，所以要用ifnull参数为空值赋值（为查询结果列命名直接在后面加 as [命名],as可省略）

修改后（两种写法）：

SELECT *,sal+IFNULL(comm,0) AS 总和 FROM emp;

SELECT *,sal+IFNULL(comm,0) 总和 FROM emp;（不加as命名）

（5）排序查询

语法：
ORDER BY ASC升序（默认可以不写） DESC降序

多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

案例：

-- 查询所有雇员，按月薪降序排序，如月薪相同，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC;

（6）聚合函数查询

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

常用的聚合函数：

COUNT( 列名 ) ：统计指定列不为 NULL 的记录行数；

MAX( 列名 ) ：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN( 列名 ) ：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM( 列名 ) ：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0 ；

AVG( 列名 ) ：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0 ；

案例：

-- 统计一共有多少记录
SELECT COUNT(*) as 总和 FROM emp;

-- 统计有佣金的的人数
SELECT count(*) as 共有 FROM emp WHERE sal > 2500;

-- 统计有佣金以及有领导的人数
SELECT count(comm),count(mgr) from emp;

-- 查询月薪和
SELECT sum(sal) as 月薪和 FROM emp;

-- 查询所有员工月薪和以及佣金和
SELECT sum(sal) as 月薪和 , sum(IFNULL(comm,0)) as 佣金和 FROM emp;

-- 查询员工总工资
SELECT sum(sal+IFNULL(comm,0)) FROM emp;

-- 查询月薪最高和最低
SELECT max(sal),min(sal) from emp;

-- 查询平均月薪
SELECT AVG(sal) FROM emp;

（7）分组查询：

当需要分组查询时需要使用 GROUP BY 子句

ps：如果查询语句中有分组操作，则 select 后面能添加的只能是聚合函数和被分组的列名

案例：

-- 查询每个部门编号和每个部门的工资和
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;

如果要对查询结果进行筛选，则要用到having子句

案例：

-- 查询工资总和大于8000的部门编号
SELECT deptno,sum(sal) from emp GROUP BY deptno HAVING sum(sal) > 8000;

（8）limit查询

limit查询就是限定查询结果的起始行，以及总行数

案例：

-- 查询emp表的第五5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0,5;

数据库高级

1，数据库的完整性

定义：用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

完整性的分类：

- 实体完整性 ( 行完整性 ):

- 域完整性 ( 列完整性 ):

- 引用完整性 ( 关联表完整性 )

一般约束有：

主键约束： primary key

唯一约束： unique [key]

非空约束： not null

默认约束： default

自动增长： auto_increment

外键约束 : foreign key

案例：

create table student(

studentno int primary key auto_increment,

loginPwd varchar ( 20 ) not null default '123456' ,

studentname varchar ( 50 ) not null ,

sex char ( 2 ) not null ,

gradeid int not null ,

phone varchar ( 255 ) not null ,

address varchar ( 255 ) default ' 学生宿舍 ' ,

borndate datetime ,

email varchar ( 50 )

);

2，多表查询（创建了emp1和dept1）

（1） 合并结果集

-- 去除笛卡尔积 SELECT * from emp1,dept1;
SELECT * from emp1,dept1 where emp1.deptno=dept1.deptno;

-- 获取部分信息
SELECT dept1.dname,emp1.ename,emp1.sal from emp1,dept1 where emp1.deptno=dept1.deptno;

（2）内链接

-- 内链接修改 SELECT * from emp1,dept1 where emp1.deptno=dept1.deptno;
-- 表名1 INNER JOIN 表名2 on 条件
SELECT * from emp1 as e INNER JOIN dept1 as d on e.deptno = d.deptno;

（3）左，右外链接

-- insert into emp1 values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);（额外测试数据）
-- 左外链接以左表为主右表中不满足条件的显示为空
SELECT * from emp1 as e left JOIN dept1 as d on e.deptno = d.deptno;

-- 右外链接以右表为主左表中不满足条件的显示为空
-- insert into dept1 values(60,'颜值部','成都');（额外测试数据）
SELECT * from emp1 as e right JOIN dept1 as d on e.deptno = d.deptno;

（4）子查询

一个 select 语句中包含另一个完整的 select 语句。

子查询就是嵌套查询，即 SELECT 中包含 SELECT ，如果一条语句中存在两个，或两个以上 SELECT ，那么就是子查询语句了

子查询出现的位置：

a. where 后，作为条为被查询的一条件的一部分；

b. from 后，作表；

l 当子查询出现在 where 后作为条件时，还可以使用如下关键字：

a. any

b. all

案例：

-- 工资高于jones工资的员工
SELECT * from emp where sal > (SELECT sal FROM emp where ename='JONES');

-- 查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename='SCOTT');

-- 工资高于30号部门所有人的员工信息
SELECT * FROM emp1 WHERE sal > (SELECT MAX(sal) FROM emp1 WHERE deptno=30);

附件1：stu，emp，dept的表

# 创建表 stu

CREATE TABLE stu (

sid CHAR(6),

sname VARCHAR(50),

age INT,

gender VARCHAR(50)

);

# 添加数据

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');

INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');

INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');

INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');

INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');

INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');

INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');

INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');

INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');

INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');

INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

# 创建雇员表

CREATE TABLE emp(

empno INT,

ename VARCHAR(50),

job VARCHAR(50),

mgr INT,

hiredate DATE,

sal DECIMAL(7,2),

comm decimal(7,2),

deptno INT

) ;

# 添加数据

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);

INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);

INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);

INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);

INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);

INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);

INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);

INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);

CREATE TABLE dept(

deptno INT,

dname varchar(14),

loc varchar(13)

);

# 添加数据

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');

INSERT INTO dept values(30, 'SALES', 'CHICAGO');

INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

附件2：emp1，dept1

CREATE TABLE dept1(

deptno int primary key,

dname varchar ( 14 ),

loc varchar ( 13 )

);

insert into dept1 values ( 10 , ' 服务部 ' , ' 北京 ' );

insert into dept1 values ( 20 , ' 研发部 ' , ' 北京 ' );

insert into dept1 values ( 30 , ' 销售部 ' , ' 北京 ' );

insert into dept1 values ( 40 , ' 主管部 ' , ' 北京 ' );

CREATE TABLE emp1(

empno int ,

ename varchar ( 50 ),

job varchar ( 50 ),

mgr int ,

hiredate date ,

sal double ,

comm double ,

deptno int

);

insert into emp1 values ( 1001 , ' 张三 ' , ' 文员 ' , 1006 , '2019-1-1' , 1000 , 2010 , 10 );

insert into emp1 values ( 1002 , ' 李四 ' , ' 程序员 ' , 1006 , '2019-2-1' , 1100 , 2000 , 20 );

insert into emp1 values ( 1003 , ' 王五 ' , ' 程序员 ' , 1006 , '2019-3-1' , 1020 , 2011 , 20 );

insert into emp1 values ( 1004 , ' 赵六 ' , ' 销售 ' , 1006 , '2019-4-1' , 1010 , 2002 , 30 );

insert into emp1 values ( 1005 , ' 张猛 ' , ' 销售 ' , 1006 , '2019-5-1' , 1001 , 2003 , 30 );

insert into emp1 values ( 1006 , ' 谢娜 ' , ' 主管 ' , 1006 , '2019-6-1' , 1011 , 2004 , 40 );

MySQL学习 （第一周7.8-7.12）

MySQL学习（第一周7.8-7.12）