MySQL基础

最新推荐文章于 2025-07-29 18:00:39 发布

原创最新推荐文章于 2025-07-29 18:00:39 发布 · 149 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

关系型数据库介绍

数据结构模型主要有：

层次模型
网状结构
关系模型（目前最流行的数据库模型）
数据库管理系统（Database Management System）：DBMS

Relational DataBse Management System（RDBMS）的专业名词

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

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语句

SQL语句有三种类型：

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

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据

DELETE：删除表中数据
UPDATE：更新表中数据
SELECT：查询表中数据
DCL GRANT：授权
REVOKE：移除授权

ReHat8/Centos8安装MariaDB:

配置本地仓库(略)
安装以下软件
[root@server ~]# yum -y install mariadb*
启动服务,并设置开机启动
[root@server yum.repos.d]# systemctl enable --now mariadb
登录;默认无密码.需自己设置一个.此版本密码不能为明文
[root@server ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
修改密码
MariaDB [(none)]> MariaDB [(none)]> set password = password('tjp123456789');
MariaDB [(none)]> ALTER USER 'root'@'localhost' identified by 'tjp123456';
用修改后的密码登录的两种方式，第一种不安全容易被其他人看到
[root@server ~]# mysql -uroot -ptjp123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
或者
[root@server ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
为避免mysql自动升级，这里需要卸载最开始安装的yum源
客户机登录失败,因为默认为拒绝.需要在服务端进行授权
[root@client ~]# mysql -uroot -p'tjp123456' -h192.168.163.136
ERROR 1130 (HY000): Host '192.168.163.137' is not allowed to connect to this MariaDB server
创建数据库。注意：如果没有输入“；”  系统会直到你输入为止
MariaDB [(none)]> CREATE DATABASE shuaige
    -> ;
Query OK, 1 row affected (0.001 sec)

删除数据库
 MariaDB [(none)]> DROP DATABASE IF EXISTS meinv;
Query OK, 0 rows affected, 1 warning (0.001 sec)
创建表
MariaDB [(none)]> use shuaige
Database changed
MariaDB [shuaige]> create table student(id int not null,name 
varchar(10),age tinyint);
Query OK, 0 rows affected (0.015 sec)
查看表
MariaDB [shuaige]> show tables;
+-------------------+
| Tables_in_shuaige |
+-------------------+
| student           |
+-------------------+
1 row in set (0.001 sec)
删除表
MariaDB [shuaige]> drop table student;
Query OK, 0 rows affected (0.007 sec)
修改表结构,添加分数
MariaDB [tangjunpeng]> alter table student add score float;
MariaDB [tangjunpeng]> desc student
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
创建账户
MariaDB [tangjunpeng]> create user tom@192.168.163.129 identified by 'tjp123456';
Query OK, 0 rows affected (0.002 sec)
授权tom账户shchool库里面的student表所有权限允许在这个ip上登录,密码为设置的.
MariaDB [(none)]> grant all on school.student to tom@192.168.163.129 identifide by 'tjp123456';
设置好后需要刷新或者重启数据库服务
MariaDB [(none)]> flush privilesges;

练习:

创建一个以本人为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下
MariaDB [(none)]> create database tangjunpeng;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use tangjunpeng;
Database changed
MariaDB [tangjunpeng]> show tables;
Empty set (0.000 sec)
MariaDB [tangjunpeng]> create table student(id int not null primary auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.020 sec)
查看表结构
MariaDB [tangjunpeng]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
# 为表新建数据
insert student(name,age) values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',NULL),('chenshuo',10),('wangqing',3),('qiuyi',15),('qiuxiaotian',20);
# 修改lisi的年龄为50
MariaDB [tangjunpeng]> update student set age =50 where name is 'lisi' 
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0
|  7 | lisi      |   50 |
#以age字段降序排序
MariaDB [tang]> select * from student order by age desc; 
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  9 | wangwu      |  100 |
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
+----+-------------+------+
11 rows in set (0.000 sec)

#查询student表中年龄最小的3位同学跳过前2位
select * from student order by age  limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
|  2 | jerry       |   23 |
+----+-------------+------+
3 rows in set (0.000 sec)

#查询student表中年龄最大的4位同学
select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | wangwu    |  100 |
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+

#查询student表中名字叫zhangshan的记录
MariaDB [tang]> select * from student where name='zhangshan'; 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.001 sec)

#查询student表中名字叫zhangshan且年龄大于20岁的记录
MariaDB [tang]> select * from student where name='zhangshan' and age>=20; 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.001 sec)

#查询student表中年龄在23到30之间的记录
MariaDB [tang]> select * from student where age >=23 and age <=30; 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.001 sec)

#修改wangwu的年龄为100
MariaDB [tangjunpeng]> update student set age = '100' where name = 'wangwu';

#删除student中名字叫zhangshan且年龄小于等于20的记录
MariaDB [tang]> delete from student where name ='zhangshan' and age <=20;
Query OK, 1 row affected (0.002 sec)

MariaDB [tang]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)

内连接、左连接、右连接以及全连接查询

基本定义：

left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

select column_name(s)
from table 1
LEFT JOIN table 2
ON table 1.column_name=table 2.column_name

right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

select column_name(s)
from table 1
RIGHT JOIN table 2
ON table 1.column_name=table 2.column_name

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

select column_name(s)
from table 1
INNER JOIN table 2
ON
table 1.column_name=table 2.column_name

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

注：在sql中l外连接包括左连接（left join ）和右连接（right join），全外连接（full join），等值连接（inner join）又叫内连接。

实验

# 创建两张表分别为AAA,BBB如图:
MariaDB [AAA]> select * from AAA;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangshan |   10 |
|  2 | lisi      |   15 |
|  3 | wangwu    |   20 |
+----+-----------+------+
3 rows in set (0.004 sec)
----------------------------------------------
MariaDB [AAA]> select * from BBB;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  2 | tom   |   5000 |
|  3 | jerry |   6000 |
|  4 | curry |   7000 |
+----+-------+--------+
3 rows in set (0.001 sec)

# 内连接:
语法: select colunm_name(s) form table1 inner join table2 on table
MariaDB [AAA]> select a.id,a.name,a.age,b.salary from AAA a inner join BBB b on a.id = b.id;
+----+--------+------+--------+
| id | name   | age  | salary |
+----+--------+------+--------+
|  2 | lisi   |   15 |   5000 |
|  3 | wangwu |   20 |   6000 |
+----+--------+------+--------+
2 rows in set (0.001 sec)

#左连接
MariaDB [AAA]> select a.id,a.name,a.age,b.salary from AAA a left join BBB b on a.id = b.id;
+----+-----------+------+--------+
| id | name      | age  | salary |
+----+-----------+------+--------+
|  1 | zhangshan |   10 |   NULL |
|  2 | lisi      |   15 |   5000 |
|  3 | wangwu    |   20 |   6000 |
+----+-----------+------+--------+
3 rows in set (0.001 sec)

# 右连接
MariaDB [AAA]> select b.id,b.name,b.salary,a.age from AAA a left join BBB b on b.id = a.id;
+------+-------+--------+------+
| id   | name  | salary | age  |
+------+-------+--------+------+
| NULL | NULL  |   NULL |   10 |
|    2 | tom   |   5000 |   15 |
|    3 | jerry |   6000 |   20 |
+------+-------+--------+------+
3 rows in set (0.001 sec)

GROUP BY

Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理
新建表格

ariaDB [AAA]> create table CCC(type varchar(10) not null,number tinyint,exp varchar(50));
Query OK, 0 rows affected (0.010 sec)

MariaDB [AAA]> desc CCC;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| type   | varchar(10) | NO   |     | NULL    |       |
| number | tinyint(4)  | YES  |     | NULL    |       |
| exp    | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

#插入数据
MariaDB [AAA]> insert CCC values('a',5,'a1005'),('a',2,'1002'),('b',10,'b1010'),('b',6,'b1006'),('b',3,'b1003'),('c',9,'c1009'),('c',9,'c1099'),('c',8,'c1008'),('c',7,'c1007'),('c',4,'c1004'),('a',11,'a1011');
Query OK, 11 rows affected (0.002 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [AAA]> select * from CCC;
+------+--------+-------+
| type | number | exp   |
+------+--------+-------+
| a    |      5 | a1005 |
| a    |      2 | 1002  |
| b    |     10 | b1010 |
| b    |      6 | b1006 |
| b    |      3 | b1003 |
| c    |      9 | c1009 |
| c    |      9 | c1099 |
| c    |      8 | c1008 |
| c    |      7 | c1007 |
| c    |      4 | c1004 |
| a    |     11 | a1011 |
+------+--------+-------+
11 rows in set (0.001 sec)

#语法:
select type '类型',sum(number) '最大值' from groupby_test GROUP BY type ORDER BY sum(number) desc;
MariaDB [AAA]> select type '类型',sum(number)'最大值' from CCC group by type;
+--------+-----------+
| 类型   | 最大值    |
+--------+-----------+
| a      |        18 |
| b      |        19 |
| c      |        37 |
+--------+-----------+
3 rows in set (0.002 sec

#组合GROUP BY
MariaDB [AAA]> select type'类型',sum( number )'最大值',exp'说明' from CCC group by type,CCC.exp order by sum( number );
+--------+-----------+--------+
| 类型   | 最大值    | 说明   |
+--------+-----------+--------+
| a      |         2 | 1002   |
| b      |         3 | b1003  |
| c      |         4 | c1004  |
| a      |         5 | a1005  |
| b      |         6 | b1006  |
| c      |         7 | c1007  |
| c      |         8 | c1008  |
| c      |         9 | c1099  |
| c      |         9 | c1009  |
| b      |        10 | b1010  |
| a      |        11 | a1011  |
+--------+-----------+--------+
11 rows in set (0.000 sec)