mysql基础

关系型数据库介绍:

数据结构模型主要有：

层次关系

网状结构

关系模型 

常见的关系型数据库管理系统

  • MySQL：MySQL，MariaDB，Percona-Server
  • PostgreSQL：简称为pgsql
  • Oracle
  • MSSQL

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

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

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

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

关系型数据库的常见组件

关系型数据库的常见组件有：

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

SQL语句有三种类型

  • DDL：Data Defination Language，数据定义语言
  • DML：Data Manipulation Language，数据操纵语言
  • DCL：Data Control Language，数据控制语言

 mysql安装

mysql安装方式有三种：

  • 源代码：编译安装
  • 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
  • 程序包管理器管理的程序包：
    • rpm：有两种
      • OS Vendor：操作系统发行商提供的
      • 项目官方提供的

安装Mariadb，设置开机自启，查看端口号

 

[root@localhost ~]# yum -y install mariadb*
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:01:26 前，执行于 2020年12月28日 星期一 05时44分29秒。
[root@localhost ~]# echo $?
0
root@localhost ~]# systemctl  enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# ss -antl
State   Recv-Q   Send-Q      Local Address:Port     Peer Address:Port  
LISTEN  0        128               0.0.0.0:22            0.0.0.0:*     
LISTEN  0        80                0.0.0.0:3306          0.0.0.0:*     
LISTEN  0        128                  [::]:22               [::]:*     
[root@localhost ~]#

修改数据库密码设成123

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY '123' ;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]>

 

 

mysql的程序组成

  • 客户端
    • mysql：CLI交互式客户端程序
    • mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
    • mysqldump：mysql备份工具
    • mysqladmin
  • 服务器端
    • mysqld

查看数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.343 sec)

MariaDB [(none)]>

删除数据库school

MariaDB [(none)]> drop database if exists school;
Query OK, 1 row affected (0.410 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

MariaDB [(none)]>

创建表

MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use school;
Database changed
MariaDB [school]> show tables;
Empty set (0.000 sec)

MariaDB [school]> create table student(id int not null,name varchar(50) null,age tinyint);
Query OK, 0 rows affected (0.349 sec)

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.000 sec)

MariaDB [school]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.335 sec)

MariaDB [school]>

权限类型(priv_type)

权限类型代表什么？
ALL 所有权限
SELECT 读取内容的权限
INSERT 插入内容的权限
UPDATE 更新内容的权限
DELETE 删除内容的权限

DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

INSERT语句

MariaDB [school]> INSERT INTO student (id,name,age) value (1,'tom',18); 
Query OK, 1 row affected (0.001 sec)

MariaDB [school]> INSERT INTO student (id,name,age) value (2,'harry',20),(3,'zhangshan',23),(4,'lisi',25);
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]>

SELECT语句

表示符代表什么？
* 所有字段
as 字段别名，如col1 AS alias1
当表名很长时用别名代替

条件判断语句WHERE

操作类型常用操作符
操作符 >，<，>=，<=，=，!=
BETWEEN column# AND column#
LIKE：模糊匹配
RLIKE：基于正则表达式进行模式匹配
IS NOT NULL：非空
IS NULL：空
条件逻辑操作 AND
OR
NOT

ORDER BY：排序，默认为升序（ASC）

 

ORDER BY语句意义
ORDER BY ‘column_name' 根据column_name进行升序排序
ORDER BY 'column_name' DESC 根据column_name进行降序排序
ORDER BY ’column_name' LIMIT 2 根据column_name进行升序排序
并只取前2个结果
ORDER BY ‘column_name' LIMIT 1,2 根据column_name进行升序排序
并且略过第1个结果取后面的2个结果		 
MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> select name from student;
+-----------+
| name      |
+-----------+
| tom       |
| harry     |
| zhangshan |
| lisi      |
+-----------+
4 rows in set (0.000 sec)

MariaDB [school]> select * from student order by age;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> select * from student order by age desc;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | lisi      |   25 |
|  3 | zhangshan |   23 |
|  2 | harry     |   20 |
|  1 | tom       |   18 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> select * from student order by age limit 2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | harry |   20 |
+----+-------+------+
2 rows in set (0.000 sec)

MariaDB [school]>

update语句

MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> update student set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.354 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [school]> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   30 |
+----+------+------+
1 row in set (0.000 sec)

MariaDB [school]>

delete语句

MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   25 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> update student set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.354 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [school]> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   30 |
+----+------+------+
1 row in set (0.000 sec)

MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   30 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> delete from student where id = 7;
Query OK, 0 rows affected (0.000 sec)

MariaDB [school]> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   18 |
|  2 | harry     |   20 |
|  3 | zhangshan |   23 |
|  4 | lisi      |   30 |
+----+-----------+------+
4 rows in set (0.000 sec)

MariaDB [school]> delete from student;
Query OK, 4 rows affected (0.001 sec)

MariaDB [school]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [school]>

truncate语句

 

语句类型特点
delete DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间
truncate 删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表
posted @ 2020-12-29 14:18  cbcbage  阅读(86)  评论(1)    收藏  举报