mysql基础

Mysql基础命令

1.关系型数据库介绍

数据结构模型
数据结构模型主要有：

1.层次模型
层次数据模型是用树状<层次>结构来组织数据的数据模型。

2.网状结构
用有向图表示实体和实体之间的联系的数据结构模型称为网状数据模型。

3.关系模型
使用表格表示实体和实体之间关系的数据模型称之为关系数据模型。

关系模型
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

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

●MySQL: MySQL，MariaDB， Percona-Server
●PostgreSQL: 简称为pgsq|
●Oracle
●MSSQL

SQL: Structure Query Language,结构化查询语言

约束: constraint， 向数据表提供的数据要遵守的限制
丶主键约束:
一个或多个字段的组合，填入的数据必须能在本表中唯一 标识本行。且必须提供数据，不能为空(NOT NULL) 一个表只能存在一个
丶惟一键约束:
一个或多个字段的组合，填入的数据必须能在本表中唯一 标识本行。允许为空(NULL)
丶外键约束:
一个表中的某字段可填入数据取决于另一个表的主键已有的数据
丶检查性约束

关系型数据库的常见组件

关系型数据库的常见组件有:
●数据库: database
●表: table,由行(row) 和列(column) 组成
●索引: index
●视图: view
●用户: user
●权限: privilege
●存储过程: procedure
●存储函数: function
●触发器: trigger
●事件调度器: event scheduler

官方yum
http://kozyatagi.mirror.guzel.net.tr/mariadb/mariadb-10.5.2/yum/centos/7.4/x86_64/

清华大学
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.5.2/yum/rhel/7.0/x86_64/

安装开启mariadb
[root@zyy180 ~]# yum -y install mariadb mariadb-server mariaDB-client mariadbdevel

[root@zyy180 ~]# systemctl restart mariadb
[root@zyy180 ~]# systemctl enable mariadb

[root@zyy180 ~]# netstat -anltup
Active Internet connections (servers and established)
tcp6 0 0 :::3306 :::* LISTEN 42569/mysqld

mysql常见的操作

库操作

登录
mysql -u用户  -p密码  -hip地址


查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.027 sec)


创建数据库
MariaDB [(none)]> create database zyy;
Query OK, 1 row affected (0.000 sec)



删除数据库
MariaDB [(none)]> drop database zyy;
Query OK, 0 rows affected (0.000 sec)



进入数据库
MariaDB [(none)]> use zyy;

表操作

1.创建表
MariaDB [zyy]> create table zyy(id int NOT NULL,name varchar(10) NOT NULL,age
int,sex varchar(4));
Query OK, 0 rows affected (0.002 sec)




MariaDB [zyy]> create table yyz(id int primary key auto_increment not null,name varchar(30),age tinyint);
Query OK, 0 rows affected (0.022 sec)

primary key   主键      只允许有一个该数据
auto_increment    自动增长




2.查看表
MariaDB [zyy]> show tables;
+---------------+
| Tables_in_zyy |
+---------------+
| zyy |
+---------------+
1 row in set (0.000 sec)



3.删除表
MariaDB [zyy]> drop tables zyy;
Query OK, 0 rows affected (0.001 sec)

用户命令

//用户命令
创建用户
create USER zyy@127.0.0.1 IDENTIFIED BY '1';

删除用户
drop user zyy@127.0.0.1;

创建一个任ip都可以登录的用户
create user 'zyy'@'%' IDENTIFIED BY '1';

其他命令

//其他命令

支持的字符集
show character set;


查看支持的所有存储引擎
show engines;


不进入库查看数据表
show tables from zyy;


不进入该表，查看表信息
DESC zyy.zyy;


查看创建该库该表的命令
show create table zyy.zyy;


查看表的状态
show table status like 'zyy'\G;

写入数据

MariaDB [zyy]> insert into yyz values(4,'yyy',23),(3,'zzz',22);



MariaDB [(none)]> insert into zyy.yyz(id,name,age,sex) value(180,'zyy',20,'boy');
Query OK, 1 row affected (0.002 sec)




create table yyz(id int primary key auto_increment not null,name varchar(30),age tinyint);

MariaDB [zyy]> insert into yyz(name,age) value('zyy',20);
Query OK, 1 row affected (0.001 sec)
MariaDB [zyy]> select * from zyy.yyz;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zyy  |   20 |
+----+------+------+
1 row in set (0.000 sec)

MariaDB [zyy]> insert into yyz(id,name,age) value(1,'zyy',20);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
设置了主键，还有自动增长

查看表数据

查看表数据
MariaDB [(none)]> select * from zyy.yyz;
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 180 | zyy  |   20 | boy  |
| 182 | zzz  |   23 | boy  |
| 181 | yyz  |   21 | boy  |
| 181 | zzz  |   25 | 男   |
+-----+------+------+------+
4 rows in set (0.000 sec)


别名设置
MariaDB [zyy]> select name as '姓名' from zyy.yyz;
+--------+
| 姓名   |
+--------+
| zyy    |
| yyz    |
| zzz    |




查看用户
MariaDB [(none)]> select user,password from mysql.user;
+-------+----------+
| User  | Password |
+-------+----------+
| root  | invalid  |
| mysql | invalid  |
|       |          |
|       |          |
+-------+----------+
4 rows in set (0.001 sec)




排序
MariaDB [(none)]> select * from zyy.yyz order by age;
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 180 | zyy  |   20 | boy  |
| 181 | yyz  |   21 | boy  |
| 182 | zzz  |   23 | boy  |
| 181 | zzz  |   25 | 男   |
+-----+------+------+------+
4 rows in set (0.000 sec)




逆序排序
MariaDB [(none)]> select * from zyy.yyz order by age desc;
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 181 | zzz  |   25 | 男   |
| 182 | zzz  |   23 | boy  |
| 181 | yyz  |   21 | boy  |
| 180 | zyy  |   20 | boy  |
+-----+------+------+------+




跳过第一个
MariaDB [(none)]> select * from zyy.yyz order by age limit 1,2;
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 181 | yyz  |   21 | boy  |
| 182 | zzz  |   23 | boy  |
+-----+------+------+------+
2 rows in set (0.000 sec)



判断  （>,<,>=,<=,,=,!=）
MariaDB [(none)]> select * from zyy.yyz where age > 22;
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 182 | zzz  |   23 | boy  |
| 181 | zzz  |   25 | 男   |
+-----+------+------+------+
2 rows in set (0.000 sec)



使用(and ro not)
MariaDB [(none)]> select * from zyy.yyz where age > 20 and age < 22;




查看空数据
MariaDB [zyy]> select * from zyy.yyz where age is null;




查看非空数据
MariaDB [zyy]> select * from zyy.yyz where age is not  null;




与|配合
[root@zyy180 ~]# mysql -uroot -p1 -e 'select name from zyy.yyz where age=20;' | awk 'NR==2,NR==3'
zyy




模糊匹配
MariaDB [(none)]> select * from zyy.yyz where age like '2_';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zyy  |   20 |
|  2 | yyz  |   21 |



精确匹配  扩展正则
MariaDB [(none)]> select * from zyy.yyz where name rlike '^z';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zyy  |   20 |
|  3 | zzz  |   22 |
+----+------+------+

更新数据

MariaDB [(none)]> update zyy.yyz set id = 185 where name = 'zzz';
Query OK, 2 rows affected (0.001 sec)
+-----+------+------+------+
| id  | name | age  | sex  |
+-----+------+------+------+
| 180 | zyy  |   20 | boy  |
| 185 | zzz  |   23 | boy  |
| 181 | yyz  |   21 | boy  |
| 185 | zzz  |   25 | 男   |
+-----+------+------+------+

权限

更改权限
MariaDB [(none)]> grant all on *.* to 'zyy'@'127.0.0.1' identified by '1'; 



查看权限
MariaDB [(none)]> show grants for'zyy'@'127.0.0.1';  
+---------------------------------------------------------------------------------------------------------------------+
| Grants for zyy@127.0.0.1                                                                                            |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `zyy`@`127.0.0.1` IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |



删除权限
MariaDB [(none)]> revoke all on *.* from 'zyy'@'127.0.0.1';

删除表

数据还会保存在日志

MariaDB [(none)]>delete from zyy.yyz;


MariaDB [(none)]>delete from zyy.zzz where id = 185;


永久删除
MariaDB [(none)]>trunacte zyy.zyy ;

更改表类型

MariaDB [(none)]> alter table zyy.yyz convert to character set utf8;