欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql在linux系统下的应用(一)

程序员文章站 2022-03-09 21:52:33
[root@localhost ~]# yum list | grep -i mariadb-server 提供服务的包 mariadb-server.x86_64 1:5.5.56-2.el7 v...

[root@localhost ~]# yum list | grep -i mariadb-server 提供服务的包

mariadb-server.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

[root@localhost ~]# yum list | grep -i mariadb

mariadb-libs.x86_64 1:5.5.56-2.el7 @anaconda/7.4

mariadb.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-bench.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-devel.i686 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-devel.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-embedded.i686 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-embedded.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-embedded-devel.i686 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-embedded-devel.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-libs.i686 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-server.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

mariadb-test.x86_64 1:5.5.56-2.el7 var_www_html_rhel7

[root@localhost ~]# rpm -q mariadb-server mariadb

未安装软件包 mariadb-server

未安装软件包 mariadb

如果装了则使用把相应的依赖包后把以上的两个包给卸载

rm -rf /etc/my.cnf

rm -rf /var/lib/mysql

[root@localhost /]# tar -xf mysql-5.7.17.tar

[root@localhost /]# ls *.rpm

mysql-community-client-5.7.17-1.el7.x86_64.rpm

mysql-community-common-5.7.17-1.el7.x86_64.rpm

mysql-community-devel-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm

mysql-community-libs-5.7.17-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm

mysql-community-server-5.7.17-1.el7.x86_64.rpm

mysql-community-test-5.7.17-1.el7.x86_64.rpm

[root@localhost /]# rpm -qa | grep -i mariadb

mariadb-libs-5.5.56-2.el7.x86_64

[root@localhost /]# rpm -uvh mysql-community-*.rpm

警告:mysql-community-client-5.7.17-1.el7.x86_64.rpm: 头v3 dsa/sha1 signature, 密钥 id 5072e1f5: nokey

错误:依赖检测失败:

perl(json) 被 mysql-community-test-5.7.17-1.el7.x86_64 需要

[root@localhost /]# yum list | grep -i perl-json

perl-json.noarch 2.59-2.el7 var_www_html_rhel7

perl-json-pp.noarch 2.27202-2.el7 var_www_html_rhel7

perl-json-tests.noarch 2.59-2.el7 var_www_html_rhel7

[root@localhost /]# rpm -q perl-json

未安装软件包 perl-json

[root@localhost /]# yum -y install perl-json

[root@localhost /]# rpm -q perl-json

perl-json-2.59-2.el7.noarch

[root@localhost /]# rpm -uvh mysql-community-*rpm

[root@localhost /]#rpm -qa | grep -i mysql

[root@localhost /]# rpm -qa | grep -i mysql

mysql-community-client-5.7.17-1.el7.x86_64

mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64

mysql-community-test-5.7.17-1.el7.x86_64

mysql-community-common-5.7.17-1.el7.x86_64

mysql-community-devel-5.7.17-1.el7.x86_64

mysql-community-libs-compat-5.7.17-1.el7.x86_64

mysql-community-libs-5.7.17-1.el7.x86_64

mysql-community-embedded-5.7.17-1.el7.x86_64

mysql-community-embedded-compat-5.7.17-1.el7.x86_64

mysql-community-embedded-devel-5.7.17-1.el7.x86_64

mysql-community-server-5.7.17-1.el7.x86_64

[root@localhost /]# ls /etc/my.cnf

/etc/my.cnf

[root@localhost /]# ls /var/lib/mysql

[刚开始的时候,以上的两个软件都没有数据,系数要进行初始化]

[root@localhost /]# systemctl status mysqld

● mysqld.service - mysql server

loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

active: inactive (dead)

docs: man:mysqld(8)

https://dev.mysql.com/doc/refman/en/using-systemd.html

[root@localhost /]# ls /var/lib/mysql

auto.cnf ib_buffer_pool mysql public_key.pem

ca-key.pem ibdata1 mysql.sock server-cert.pem

ca.pem ib_logfile0 mysql.sock.lock server-key.pem

client-cert.pem ib_logfile1 performance_schema sys

client-key.pem ibtmp1 private_key.pem

[root@localhost /]# ls /var/lib/mysql -l

总用量 122952

-rw-r-----. 1 mysql mysql 56 6月 25 22:49 auto.cnf

-rw-------. 1 mysql mysql 1679 6月 25 22:49 ca-key.pem

-rw-r--r--. 1 mysql mysql 1074 6月 25 22:49 ca.pem

-rw-r--r--. 1 mysql mysql 1078 6月 25 22:49 client-cert.pem

-rw-------. 1 mysql mysql 1675 6月 25 22:49 client-key.pem

-rw-r-----. 1 mysql mysql 413 6月 25 22:49 ib_buffer_pool

-rw-r-----. 1 mysql mysql 12582912 6月 25 22:49 ibdata1

-rw-r-----. 1 mysql mysql 50331648 6月 25 22:49 ib_logfile0

-rw-r-----. 1 mysql mysql 50331648 6月 25 22:49 ib_logfile1

-rw-r-----. 1 mysql mysql 12582912 6月 25 22:49 ibtmp1

drwxr-x---. 2 mysql mysql 4096 6月 25 22:49 mysql

srwxrwxrwx. 1 mysql mysql 0 6月 25 22:49 mysql.sock

-rw-------. 1 mysql mysql 5 6月 25 22:49 mysql.sock.lock

drwxr-x---. 2 mysql mysql 8192 6月 25 22:49 performance_schema

-rw-------. 1 mysql mysql 1679 6月 25 22:49 private_key.pem

-rw-r--r--. 1 mysql mysql 451 6月 25 22:49 public_key.pem

-rw-r--r--. 1 mysql mysql 1078 6月 25 22:49 server-cert.pem

-rw-------. 1 mysql mysql 1679 6月 25 22:49 server-key.pem

drwxr-x---. 2 mysql mysql 8192 6月 25 22:49 sys

[root@localhost /]# grep mysql /etc/group

mysql:x:27:

[root@localhost /]# grep mysql /etc/passwd

mysql:x:27:27:mysql server:/var/lib/mysql:/bin/false

[root@localhost /]# systemctl enable mysqld

[root@localhost /]# ps -c mysqld

pid tty time cmd

4613 00:00:00 mysqld

[root@localhost /]# netstat -utnlp | grep :3306

tcp6 0 0 :::3306 :::* listen 4613/mysqld

[root@localhost /]# rpm -qa | grep -i mysql

mysql-community-client-5.7.17-1.el7.x86_64

mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64

mysql-community-test-5.7.17-1.el7.x86_64

mysql-community-common-5.7.17-1.el7.x86_64

mysql-community-devel-5.7.17-1.el7.x86_64

mysql-community-libs-compat-5.7.17-1.el7.x86_64

mysql-community-libs-5.7.17-1.el7.x86_64

mysql-community-embedded-5.7.17-1.el7.x86_64

mysql-community-embedded-compat-5.7.17-1.el7.x86_64

mysql-community-embedded-devel-5.7.17-1.el7.x86_64

mysql-community-server-5.7.17-1.el7.x86_64

初始密码连接:

[root@localhost /]# grep password /var/log/mysqld.log [查看原始的密码]

2018-06-25t14:49:28.937599z 1 [note] a temporary password is generated for root@localhost: 3xzm(tq(jyd)

2018-06-25t14:59:05.551210z 3 [note] access denied for user 'root'@'localhost' (using password: no)

[root@localhost /]# mysql -hlocalhost -uroot -p'3xzm(tq(jyd)'

二、重制本机登陆密码

[root@localhost ~]# mysql -uroot -p'3xzm(tq(jyd)'

mysql: [warning] using a password on the command line interface can be insecure.

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 3

server version: 5.7.17

copyright (c) 2000, 2016, 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.

mysql> show databases;

error 1820 (hy000): you must reset your password using alter user statement before executing this statement.

密码验证策率为以下的三行,mysql中的策率有三种。

mysql> set global validate_password_policy=0;

query ok, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;

query ok, 0 rows affected (0.00 sec)

mysql> alter user root@"localhost" identified by "123456";

query ok, 0 rows affected (0.00 sec)

mysql> quit

bye

[root@localhost ~]# mysql -uroot -p'123456'

mysql: [warning] using a password on the command line interface can be insecure.

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 4

server version: 5.7.17 mysql community server (gpl)

copyright (c) 2000, 2016, 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.

mysql> show databases;

+--------------------+

| database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

只用是命令行都是临时的

三、配置密码设置永久生效

vim /etc/my.cnf

[mysqld]

validate_password_policy=0;

validate_password_length=6;

验证的办法

systemctl stop mysqld

systemctl start mysqld

四、相关概念

表 文件夹

库 文件

记录 行

字段 列

sql命令 结构化查询语言

sql命令语法规则?

mysql指令:环境切换、看状态、退出等控制

sql指令:定义/查询/操作/授权/操作/授权语句

基本注意事项

-操作指令不区分大小写(密码、变量值除外)

每条sql指令以;结束或分隔

不支持tab建自动补齐

\c可废弃当前编写错的操作指令

五、客户端把数据库的过程

连接数据库

[root@localhost ~]# mysql -uroot -p'123456'

mysql: [warning] using a password on the command line interface can be insecure.

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 3

server version: 5.7.17 mysql community server (gpl)

copyright (c) 2000, 2016, 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.

mysql> create database db1;

query ok, 1 row affected (0.00 sec)

mysql> use db1;

database changed

mysql> create table t1(name char(1),age int (1));

query ok, 0 rows affected (0.43 sec)

mysql> insert into t1 values("bob", 91);

error 1406 (22001): data too long for column 'name' at row 1

mysql> insert into t1 values("bob",91);

error 1406 (22001): data too long for column 'name' at row 1

mysql> insert into t1 values("bob",9);

error 1406 (22001): data too long for column 'name' at row 1

mysql> insert into t1 values("a",9);

query ok, 1 row affected (0.05 sec)

mysql> select * from t1;

+------+------+

| name | age |

+------+------+

| a | 9 |

+------+------+

1 row in set (0.00 sec)

mysql> ^dbye

库管理命令

show database;

create database db1;

insert into t1 values("bob", 91);

select * from t1;

select batsbase(); 显示当前所在的数据库下

show tables;

drop database 库名;

mkdir /var/lib/mysql/zhydb ---这杨的数据库所有者不是mysql而不是root

数据库的命令规则

可以使用数字/字母/下划线,但不能纯数字

区分大小写,唯一性

不能使用指令关键字、特殊字符

数据库管理

查看表结构

mysql> desc t1;

+-------+----------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+-------+----------+------+-----+---------+-------+

| name | char(10) | yes | | null | |

+-------+----------+------+-----+---------+-------+

1 row in set (0.01 sec)

mysql> create database db11;

query ok, 1 row affected (0.01 sec)

mysql> use db11;

database changed

mysql> show tables;

empty set (0.00 sec)

mysql> create table t1(name char(10));

query ok, 0 rows affected (0.90 sec)

mysql> insert into t1 values("as"),("aa");

query ok, 2 rows affected (0.06 sec)

records: 2 duplicates: 0 warnings: 0

mysql> select * from t1;

+------+d

| name |

+------+

| as |

| aa |

+------+

2 rows in set (0.00 sec)

mysql> update t1 set name='xx';

query ok, 2 rows affected (0.06 sec)

rows matched: 2 changed: 2 warnings: 0

mysql> select * from t1;

+------+

| name |

+------+

| xx |

| xx |

+------+

2 rows in set (0.00 sec)

mysql> delete * from t1;

error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near '* from t1' at line 1

mysql> delete from t1;

query ok, 2 rows affected (0.31 sec)

mysql> select * from t1;

empty set (0.00 sec)

mysql> show tables;

+----------------+

| tables_in_db11 |

+----------------+

| t1 |

+----------------+

1 row in set (0.00 sec)

mysql> drop table t1;

query ok, 0 rows affected (0.11 sec)

mysql> show tables;

empty set (0.00 sec)

mysql> select databases();

error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'databases()' at line 1

mysql> select database();

+------------+

| database() |

+------------+

| db11 |

+------------+

1 row in set (0.00 sec)

mysql> drop database db11;

query ok, 0 rows affected (0.00 sec)

指定编码格式

create table t2(name char()) default charset=utf8;

alter table 学生表 default charset=utf8;

mysql> create database 学生库;

query ok, 1 row affected (0.00 sec)

mysql> use 学生库;

database changed

mysql> create table 学生表(姓名 char(15));

query ok, 0 rows affected (0.18 sec)

mysql> show tables;

+---------------------+

| tables_in_学生库 |

+---------------------+

| 学生表 |

+---------------------+

1 row in set (0.00 sec)

mysql> desc 学生表;

+--------+----------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+--------+----------+------+-----+---------+-------+

| 姓名 | char(15) | yes | | null | |

+--------+----------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into 学生表 values("张三非");

-> ;

-> ;

error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near ';

;' at line 1

mysql> insert into 学生表 values("张三非");

error 1366 (hy000): incorrect string value: '\xe5\xbc\xa0\xe4\xb8\x89...' for column '姓名' at row 1

mysql> show create table 学生表;

+-----------+----------------------------------------------------------------------------------------------------+

| table | create table |

+-----------+----------------------------------------------------------------------------------------------------+

| 学生表 | create table `学生表` (

`姓名` char(15) default null

) engine=innodb default charset=latin1 |

+-----------+----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table 学生表 default charset=utf8;

-> ;

error 1115 (42000): unknown character set: 'utf8;'

mysql> alter table 学生表 default charset=utf8;

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

mysql> show create table 学生表;

+-----------+-----------------------------------------------------------------------------------------------------------------------+

| table | create table |

+-----------+-----------------------------------------------------------------------------------------------------------------------+

| 学生表 | create table `学生表` (

`姓名` char(15) character set latin1 default null

) engine=innodb default charset=utf8 |

+-----------+-----------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> insert into 学生表 values("张三非");

error 1366 (hy000): incorrect string value: '\xe5\xbc\xa0\xe4\xb8\x89...' for column '姓名' at row 1

mysql> alter table 学生表 default charset=utf-8;

error 1115 (42000): unknown character set: 'utf'

mysql> alter table 学生表 default charset=utf8;

query ok, 0 rows affected (0.05 sec)

records: 0 duplicates: 0 warnings: 0

mysql>

mysql> create database studb;

query ok, 1 row affected (0.00 sec)

mysql> use studb;

database changed

mysql> create table stuinfo(name char(15),address char(30));

query ok, 0 rows affected (0.17 sec)

mysql> desc stuinfo;

+---------+----------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+---------+----------+------+-----+---------+-------+

| name | char(15) | yes | | null | |

| address | char(30) | yes | | null | |

+---------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> insert into stuinfo values("zhansan","beijing");

query ok, 1 row affected (0.06 sec)

mysql> select * from stuinfo;

+---------+---------+

| name | address |

+---------+---------+

| zhansan | beijing |

+---------+---------+

1 row in set (0.00 sec)

mysql> show databases;

+--------------------+

| database |

+--------------------+

| information_schema |

| 学生库 |

| db1 |

| mysql |

| performance_schema |

| studb |

| sys |

+--------------------+

7 rows in set (0.00 sec)

mysql> use db1;

reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -a

database changed

mysql> show tables;

+---------------+

| tables_in_db1 |

+---------------+

| t1 |

+---------------+

1 row in set (0.00 sec)

mysql> desc t1;

+-------+---------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+-------+---------+------+-----+---------+-------+

| name | char(1) | yes | | null | |

| age | int(1) | yes | | null | |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> drop table t1;

query ok, 0 rows affected (0.12 sec)

mysql> create table t1(level tinyint

-> );

query ok, 0 rows affected (0.19 sec)

mysql> select * form t1;

error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'form t1' at line 1

mysql> select * from t1;

+-------+

| level |

+-------+

| -128 |

+-------+

1 row in set (0.00 sec)

mysql> create table t2(level tinyint unsigned)

mysql> create table t2(level tinyint unsigned)

-> ;

query ok, 0 rows affected (0.23 sec)

mysql> insert into t2 values(21.23)

-> ;

query ok, 1 row affected (0.05 sec)

mysql> select * ftom t2;

error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'ftom t2' at line 1

mysql> select * from t2;

+-------+

| level |

+-------+

| 21 |

+-------+

1 row in set (0.00 sec)

整数型

关于整数型字段

使用unsigned修饰时,对应的字段只能保证正数

数值不够指定宽度是,在左边填空格补位

宽度仅是显示宽度,存数值的大小由类型决定

使用关键字zerofill时,填0代替空格补位

数值超出范围时,报错。

字符类型

char 255字符 固定宽度,在右边补齐

vachar 65535

text/blob 大于65535

太耗时间了如果在数据库存图片1和视频

在见表的时候一定要先指定字符集

字符类型的宽度和数值的宽度时不一样的

数值的宽度是显示宽度

mysql> create table tt2( id int(5) zerofill,

-> age int (4) zerofill,

-> level int zerofill

-> );

query ok, 0 rows affected (0.47 sec)

mysql> desc tt2;

+-------+---------------------------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+-------+---------------------------+------+-----+---------+-------+

| id | int(5) unsigned zerofill | yes | | null | |

| age | int(4) unsigned zerofill | yes | | null | |

| level | int(10) unsigned zerofill | yes | | null | |

+-------+---------------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into tt2 values(9,9,9);

query ok, 1 row affected (0.07 sec)

mysql> select * from tt2;

+-------+------+------------+

| id | age | level |

+-------+------+------------+

| 00009 | 0009 | 0000000009 |

+-------+------+------------+

1 row in set (0.00 sec)

设置宽度的目的是为了节省存储空间

日期时间类型

data 4字节 0001-01-01~9999-12-31

yesr 1字节 1901~2155

time 3字节 hh:mm:ss

datatime 8字节 1000-01-01 00:00:00:000000~9999-12-31 23:59:59.999999

日期时间,timestamp 4字节 1970-01-01 00:00:00:000000~2038-01-19 03:14:07.999999

mysql> create table time(

-> name char(15),

-> age tinyint(2) unsigned,

-> birthday date,

-> up_time time,

-> y_star year,

-> party datetime

-> );

query ok, 0 rows affected (0.23 sec)

mysql> desc time;

+----------+---------------------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+----------+---------------------+------+-----+---------+-------+

| name | char(15) | yes | | null | |

| age | tinyint(2) unsigned | yes | | null | |

| birthday | date | yes | | null | |

| up_time | time | yes | | null | |

| y_star | year(4) | yes | | null | |

| party | datetime | yes | | null | |

+----------+---------------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> insert into time values("zhu",11,19920808,083000,1990,201806203028);

error 1292 (22007): incorrect datetime value: '201806203028' for column 'party' at row 1

mysql> insert into time values("zhu",11,19920808,083000,1990,20180628203028);

query ok, 1 row affected (0.05 sec)

mysql> select * from time;

+------+------+------------+----------+--------+---------------------+

| name | age | birthday | up_time | y_star | party |

+------+------+------------+----------+--------+---------------------+

| zhu | 11 | 1992-08-08 | 08:30:00 | 1990 | 2018-06-28 20:30:28 |

+------+------+------------+----------+--------+---------------------+

1 row in set (0.00 sec)

使用时间函数给日期时间类型赋值?

mysql> insert into time values("t",21,date(now()),083000,year(19901220),now());

query ok, 1 row affected (0.05 sec)

select now();

select year(20170321);

select date(now());

select time(now());

select month(now());

使用2位数字给year类型自动赋值?

枚举类型

在定值集合中选择单个值 enum

选择多个是set

mysql> create table m1(name char(12),sex enum("boy","girl","no"), likes set ("game","read","film"));

query ok, 0 rows affected (0.20 sec)

mysql> desc m1;

+-------+---------------------------+------+-----+---------+-------+

| field | type | null | key | default | extra |

+-------+---------------------------+------+-----+---------+-------+

| name | char(12) | yes | | null | |

| sex | enum('boy','girl','no') | yes | | null | |

| likes | set('game','read','film') | yes | | null | |

+-------+---------------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into m1 values("zhu","boy","game")

-> ;

query ok, 1 row affected (0.04 sec)

mysql> select * from m1;

+------+------+-------+

| name | sex | likes |

+------+------+-------+

| zhu | boy | game |

+------+------+-------+

1 row in set (0.00 sec)