mysql在linux系统下的应用（一）

[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)