mysql基础

最新推荐文章于 2024-11-11 21:02:50 发布

Wahahaaaaaa

最新推荐文章于 2024-11-11 21:02:50 发布

阅读量184

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/Wahahaaaaaa/article/details/89458978

版权

数据库简介

事务

多个操作被当作一个整体对待就称为一个事务
如果一个SQL语句出错，则该批次内所有SQL都将取消执行
MySQL事物处理只支持InnoDB和BDB数据表类型
事务的原则（ACID）
- 原子性（Atomic）；事物整体是不可分的
- 一致性（Consist）；数据在执行前后执行后不能有出入
- 隔离性（Isolated）；多个事务之间互不影响
- 持久性（Durable）；全都执行完毕后的数据必须是永久的

索引

将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储；提高查询速度，确保数据唯一性，加快表和表之间的链接，排序什么的都能得到优化

约束：向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（一个表唯一）
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（一个表可多个）
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束

关系型数据库

关系型数据库的常见组件

数据库：database
表：table，由行（row）和列（column）组成
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler

SQL语句

SQL：Structure Query Language，结构化查询语言

SQL语句类型	对应操作
DDL	CREATE创建；DROP删除；ALTER修改
DML	INSERT增；DELETE删；UPDATE更新；SELECT查
DCL	GRANT授权；REVOKE移除授权

mysql安装及配置

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
yum安装

安装：

//因为这个是rpm包，并且没有依赖，所以可直接rpm安装
[root@134 ~]# rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
...
//安装后，会多出两个mysql相关仓库
[root@134 ~]# ls /etc/yum.repos.d/
mysql-community.repo  mysql-community-source.repo  xxx.repo
//正式安装5.7
[root@134 ~]# yum -y install mysql-community-server
mysql-community-client 
mysql-community-common 
mysql-community-devel
...

配置

[root@134 ~]#systemctl start mysqld
[root@134 ~]# systemctl status mysqld

//在日志文件中找出临时密码
[root@134 ~]#  grep "password" /var/log/mysqld.log | awk '{print $NF}'
//登录，并修改原始密码
[root@134 ~]# mysql -u root -p 密码
...
mysql>set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql>set global validate_password_length=1;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

//为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@134 ~]# yum -y remove mysql57-community-release
[root@134 ~]# ls /etc/yum.repos.d/
xxx.repo

mysql的程序组成

客户端（可安装mariadb，带有下列工具）
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin：mysql备份工具
服务端
- mysqld

mysql的使用

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本
    
 
[root@134 ~]# mysql -uroot -p123456 -h127.0.0.1
//不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码

[root@134 ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

服务器监听的两种socket地址

ip socket：默认监听在tcp的3306端口，支持远程通信

unix sock

监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
仅支持本地通信
server地址只能是：localhost，127.0.0.1
mysql -S /var/lib/mysql/mysql.sock -h 127.0.0.1

mysql具体操作

DDL

数据库操作

//创建数据库
CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';

//删除数据库
DROP DATABASE [IF EXISTS] 'DB_NAME';

表操作

//创建表
use database_name
CREATE TABLE table1 (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);

//删除表
DROP TABLE [ IF EXISTS ] 'table_name';

用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为
- IP地址，如：172.16.12.129
- 通配符；%：匹配任意长度的任意字符，常用于设置允许从任何主机登录；_：匹配任意单个字符

//数据库用户创建，允许aaaa用户在192.168.184.140主机上登录
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
如：CREATE USER 'aaaa'@'192.168.184.140' IDENTIFIED BY '123456';
//刷新权限
flush privileges;
//查看用户权限
 show grants for 'username'@'host;

//删除数据库用户
DROP USER 'username'@'host';
如：DROP USER ''aaaa'@'192.168.184.140';

查看命令

//查看支持的所有字符集
SHOW CHARACTER SET;

//查看当前数据库支持的所有存储引擎
SHOW ENGINES;

//查看数据库信息
SHOW DATABASES; 

//不进入某数据库而列出其包含的所有表
SHOW TABLES FROM database_name;

//查看表结构
DESC db_name.table_name;

//查看表或数据库的创建命令
SHOW CREATE TABLE table_name;
SHOW CREATE DATABASE db_name;

//查看某表的状态
SHOW TABLE STATUS LIKE 'aaaa'\G

获取帮助

//获取创建表的帮助
HELP CREATE TABLE;

DML

INSERT

//一次插入一条完整记录
insert into table_name value (1,'tom',20);
//一次插入多条完整记录
insert into table_name values (1,'tom',20),(2,'jerry',30),(4,'qwer',50)...;

//给指定字段插入一条数据
insert into table_name (name) value ('tom');
//给指定字段插入多条数据
insert into table_name (name) values ('tom','jerry','haha');

SELECT

字段表示方法

*表示所有字段
as，字段别名

条件判断语句WHERE

操作符

>，<，>=，<=，=，!=
BETWEEN
LIKE：模糊匹配
RLIKE：基于正则表达式进行模式匹配
IS NOT NULL：非空
IS NULL：空

条件逻辑操作：and、or、not

排序ORDER BY（必须放在where语句后）

默认升序ESC
DESC，降序
LIMIT 2，进行升序排序并只取前2个结果
LIMIT 1,2，进行升序排序并且略过第1个结果取后面的2个结果

//查询整张表
select * from table_name;
//按字段查询
select column_name from table_name;
//查询完后升序排序
select * from table_name order by age;
//查询完后降序排序
select * from table_name order by age desc;
//查询年龄10到40的
select * from table_name where age between 10 and 40;
//查询年龄10到40的数据，并取第3-5条
select * from table_name where age between 10 and 40 limit 2,3;
//查询名字li开头的数据
select * from table_name where name like 'li%';
//查询名字li开头的数据（正则匹配 ）
select * from table_name where name rlike'li*';

group by：指定结果按照哪几个字段来分组
having：过滤分组的记录必须满足的次要条件
一般和聚合函数一起用

链接查询（多表查询）

内连接（inner join），多张表链接，就一直join…on下去，然后两两找关系

select 字段1,字段2,... from table1 inner join table2 on table1.字段x=table2.字段y;

外链接（left/right join）

select 字段1,字段2,... from table1 left/right  join table2 on table1.字段x=table2.字段y;

常用统计函数

count()
- 返回满足select条件的记录总数，如select conut(*);
sum()
- 返回数字字段或表达式列作统计，返回一列总和
avg()；平均值
max()；最大值
min()；最小值

UPDATE

update table_name set column1 = new_value1[,column2 = new_value2,...] [where clause] [order 'column_name' [DESC]] [limit [m,]n];

//更新条数据
update table_name set age=30 where name='zhangsan';
//更新同一字段所有数据
select * from table_name where age=100;

DELETE

//根据id删除某条记录
delete from table_name where id =1;
//删除整张表内容
delete from table_name;
truncate table table_name;

truncate与delete的区别

delete

DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间

truncate

删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据

delete删除数据能恢复，truncate删了就没了

DCL

创建授权GRANT

权限类型

权限类型	代表什么
ALL	所有权限
SELECT	读取内容权限
INSERT	插入权限
UPDATE	修改权限
DELETE	删除权限

指定要操作的对象

表示方式	意义
.	所有库的所有表
db_name.*	指定库的所有表
db_name.table_name	指定库的指定表

//授权aaa用户在数据库本机上登录访问所有数据库
grant all on *.* to 'aaa'@'localhost' identified by '123456';

//授权aaa用户在172.16.12.129上远程登录访问某个数据库
grant all on db_name.* 'aaa'@'172.16.12.129' identified by '123456';

//授权aaa用户在所有位置上远程登录访问某数据库
grant all on db_name.* 'aaa'@'%' indentifed by '123456';

查看授权

//查看当前登录用户的授权信息
show grants;

//查看指定用户的授权信息
show grants for user_name;
show grants for 'user'@'localhost';

取消授权REVOKE

//取消某用户授权
revoke all on *.* from 'user'@'localhost';
//刷新权限
flush privileges;

grent或revoke等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中；
不能及时重读授权表的，可手动刷新权限