文章目录
mysql的学习笔记
这部分笔记主要针对的是基本概念和使用。
基本概念
-
表头(header):每一列的名称
-
列(row):具有相同数据类型的数据的集合;
-
行(col):也叫元组,每一行用来描述某个人/物的具体信息;
-
值(value):行的具体信息, 每个值必须与该列的数据类型相同;
-
键(key):表中用来识别某个特定的人\物的方法, 键的值在当前列中具有唯一性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键:外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
-
冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
-
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
-
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
-
数据库:就是按照数据结构来组织、存储和管理数据的仓库。
-
数据库管理系统:指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
-
mysql:就是关系型数据库(Relational Database Management System)。
安装mysql
- 在官网上下载mysql
我是安在了windows上,十分简单,只要根据自己的系统版本进行下载,任何解压就好。
-
my.ini配置
-
在bin的同级目录下建一个data文件夹(用来放数据)
-
建一个名为my.ini的文件然后按照下面的步骤(安装目录按照自己的来更改)来配置
[mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=D:\mysql-8.0.14-winx64 # 设置mysql数据库的数据的存放目录 datadir=D:\mysql-8.0.14-winx64\data # 允许最大连接数 max_connections=200 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8
-
-
数据库配置
-
通过管理员权限进入cmd(如果不是管理员权限会在安装步骤出现问题),进入mysql安装目录下的bin目录
-
初始化
mysqld --initialize --console
然后记下root@localhost:…这是root用户的初始密码。 -
安装mysql服务
mysqld --install
然后输入net start mysql
启动服务(
mysqld --remove
是卸载MySQL服务,net stop mysql
是停止服务)
-
-
进入数据库配置
-
输入
mysql -u root -p
后会需要输入密码(第一次就是那个初始密码) -
然后会进入下面的界面(表示进入mysql命令模式了)
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.14 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
接着可以输入
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
来更改密码
-
常用的语法
启动和关闭
#启动
net start mysql
#关闭
net stop mysql
#登录
mysql -h 主机名 -u 用户名 -p
#退出
quit
命令分类
-
DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
-
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);
-
DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;
-
DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
DDL
#查看所有数据库
SHOW DATABASES;
#查看某个数据库的定义信息
SHOW CREATE DATABASE mydb1;
#切换数据库
USE mydb1;
#创建数据库
CREATE DATABASE [IF NOT EXISTS] mydb1;
#删除数据库
DROP DATABASE [IF EXISTS] mydb1;
#修改数据库编码
ALTER DATABASE mydb1 CHARACTER SET utf8
# 创建表
CREATE TABLE sort (
sid INT, #分类ID
sname VARCHAR(100) #分类名称
);
CREATE TABLE sort (
sid INT, #分类ID
sname VARCHAR(100) #分类名称
)CHARSET=utf8;
需要注意主键约束
可以在创建表时创建主键(在字段后面加 primary key
)
也可以在表创建的最后来指定主键【primary key(id)
】
by the way:删除主键alter table 表名 drop primary key;
#查看当前数据库中所有表
SHOW TABLES;
#查看指定表的创建语句
SHOW CREATE TABLE emp;
#查看表结构
DESC emp;
#删除表
DROP TABLE emp;
#修改表:
#1.修改之添加列:给stu表添加classname列
ALTER TABLE stu ADD (classname varchar(100));
#2.修改之修改列类型:修改stu表的gender列类型为CHAR(2)
ALTER TABLE stu MODIFY gender CHAR(2);
#3.修改之修改列名:修改stu表的gender列名为sex
ALTER TABLE stu change gender sex CHAR(2);
#4.修改之删除列:删除stu表的classname列
ALTER TABLE stu DROP classname;
#5.修改之修改表名称:修改stu表名称为student
ALTER TABLE stu RENAME TO student;
DML
# 插入数据
INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');
# 修改数据
UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;
# 删除数据
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;
# truncate 是先DROP TABLE,再CREATE TABLE。而且TRUNCATE删除的记录是无 法回滚的,但DELETE删除的记录是可以回滚的
TRUNCATE TABLE stu;
DML是对表中数据进行增删改的操作的。
- INSERT INTO 表名(列名1,列名2 …)VALUES(列值1,列值2…);
- 注意列名与列值的类型、个数、顺序要一一对应
- 可以把列名当做java中的形参,把列值当做实参
- 值不要超出列定义的长度
- 如果插入空值,请使用null
- 插入的日期和字符一样,都使用引号括起来
- UPDATE 表名 SET 列名1=列值1,列名2=列值2… WHERE 列名=值
- DELETE FROM 表名 [WHERE 列名=值]
DCL
# 创建用户: CREATE USER 用户名@地址 IDENTIFIED BY '密码';
CREATE USER user1@localhost IDENTIFIED BY ‘123’;
CREATE USER user2@’%’ IDENTIFIED BY ‘123’;
# 给用户授权: GRANT 权限1, … , 权限n ON 数据库.* TO 用户名
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;
# 撤销授权: REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
# 查看用户权限:SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;
# 删除用户:DROP USER 用户名
DROP USER user1@localhost;
# 修改用户密码
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;
#------------------
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’;
FLUSH PRIVILEGES;
DQL
/*语法:*/
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/
基础查询
- 查询所有列
SELECT * FROM stu;
- 查询制定列
SELECT sid, sname, age FROM stu;
条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
- =、!=、<>、<、<=、>、>=;
- BETWEEN…AND;
- IN(set);
- IS NULL; IS NOT NULL
- AND;
- OR;
- NOT;
SELECT *
FROM stu
WHERE NOT sname IS NULL;
模糊查询
模糊查询需要使用关键字LIKE,在在使用like关键字时,通常和通配符配合使用。
- 通配符: 用来匹配一部分的特殊字符
- _ : 匹配任意一个字符
- % : 任意0~n个字符
SELECT *
FROM stu
WHERE sname LIKE 'z%';
-- 其中“%”匹配0~n个任何字母。
去除重复记录
SELECT DISTINCT sal FROM emp;
使用DISTINCT
排序
排序使用 order by 列名 asc/desc
,默认是asc(升序) 可以指定 desc 降序
聚合函数
聚合函数是用来做纵向运算的函数
- COUNT():统计指定列不为NULL的记录行数;
- MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
- MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
- SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
/*使用方法都类似于下面这样*/
SELECT SUM(sal) FROM emp;
分组查询
当需要分组查询时需要使用GROUP BY子句。
- 注意:
- 凡是和聚合函数同时出现的列名,需要下载group by之后
- 分组的时候是无法体现单个数据的
- group by一般都配合聚合函数使用
/*例一*/
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
/*例二使用having*/
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
- having和where的区别
- having是在分组后对数据进行过滤,而where是在分组前对数据进行过滤
- having后面可以使用聚合函数(统计函数),where后面不可以使用聚合函数
- WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束
LIMIT
LIMIT用来限定查询结果的起始行以及总行数。
SELECT * FROM emp LIMIT 0, 5;
-
注意
-
查询语句的书写顺序
select – from- where- group by- having- order by-limit
-
查询语句的执行顺序
from - where -group by - having - select - order by-limit
-
多表查询
合并结果集
相当于是做笛卡儿积
-
UNION 去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
-
UNION ALL 不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
连接查询
内连接
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
左连接
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
右连接
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
自然连接
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
子查询
即为嵌套查询,即SELECT中包含SELECT。
子查询可以出现在where之后,是作为条件的一部分;也可以出现在from之后,作为被查询的一个表。
完整性约束
主键(primary key)
主键表中每条记录的唯一标识,主键必须包含唯一的值,主键列不能包含null,每个表都应该有一个主键,且只能有一个主键。
-
可以创建表时,在定义列时指定主键
-
可以创建表时,在定义列之后单独指定主键
-
可以修改表时指定主键
ALTER TABLE stu ADD PRIMARY KEY(sid);
-
可以删除主键
ALTER TABLE stu DROP PRIMARY KEY;
主键自增长(auto_increment)
主键必须时整型才可以自增长
-
可以在创建表时,设置主键自增长
CREATE TABLE stu( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20), age INT, gender VARCHAR(10) );
-
可以修改表时设置主键自增长
ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
-
可以修改表时删除主键自增长
ALTER TABLE stu CHANGE sid sid INT;
非空(not null)
唯一(unique)
外键(foreign key)
一个表中的外键指向另一个表中的主键,外键用于预防破坏表之间连接的动作,也能防止非法数据插入外键列。
-
可以在创建表时设置外键
CREATE TABLE t_section( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(30), u_id INT, CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid) );
-
可以修改表时设置外键
ALTER TABLE t_session ADD CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid);
-
可以修改表时删除外键
ALTER TABLE t_section DROP FOREIGN KEY fk_t_user;
检查(CHECK)
用于限制列中的值的范围。
但是在mysql中check子句会被分析,但是会被忽略。不常用到。
mysql数据库备份与还原
- 生成SQL脚本(备份)
mysqldump –u 用户名 –p 密码 数据库名>生成的脚本文件路径
- 执行SQL脚本
mysql –u用户名 –p密码 数据库<要执行脚本文件路径