MYSQL入门与安装 SQL语句

最新推荐文章于 2021-03-11 19:05:46 发布
最新推荐文章于 2021-03-11 19:05:46 发布
阅读量272 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ITISJack/article/details/97613381
版权

1、使用rpm安装mysql

  1. 环境 CentOS Linux release 7.6.1810 (Core)
  2. 卸载MariaDB（CentOS7 默认自带）与MySQL
    注：一定要将mariaDB包和自带mysql包卸载干净，否则安装时出现各种错误，导致失败。
[root@centos7 Packages]#rpm -qa | grep -i mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@centos7 Packages]#rpm -qa | grep -i mysql
[root@centos7 Packages]#rpm -e  --nodeps mariadb-libs
[root@centos7 lib64]#find / -iname mysql
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
[root@centos7 lib64]#find / -iname mariadb
[root@centos7 lib64]#rm -rf /usr/lib64/mysql
[root@centos7 lib64]#chkconfig --list #查看是否有相关服务，有就删除

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off
  1. 下载MySQL rpm包，解压，安装选择的包，安装过程中解决依赖问题
    下载地址 https://dev.mysql.com/downloads/mysql/
    注：先装好依赖，再装mysql。即使顺序出错，装完依赖一定要卸载mysql并重新装mysql,否则mysql装上了也会出现各种错误。
[root@centos7 data]#tar -xf mysql-8.0.17-1.el7.x86_64.rpm-bundle.tar
[root@centos7 data]#ls
mysql-8.0.17-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.17-1.el7.x86_64.rpm
mysql-community-common-8.0.17-1.el7.x86_64.rpm
mysql-community-devel-8.0.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.17-1.el7.x86_64.rpm
mysql-community-libs-8.0.17-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm
mysql-community-server-8.0.17-1.el7.x86_64.rpm
mysql-community-test-8.0.17-1.el7.x86_64.rpm
[root@centos7 data]#rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-common-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-8.0.17-1.e################################# [100%]
[root@centos7 data]#rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-libs-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-8.0.17-1.el7################################# [100%]
[root@centos7 data]#rpm -ivh mysql-community-devel-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-devel-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        pkgconfig(openssl) is needed by mysql-community-devel-8.0.17-1.el7.x86_64
[root@centos7 yum.repos.d]#yum install openssl-devel
[root@centos7 data]#rpm -ivh mysql-community-devel-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-devel-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-devel-8.0.17-1.el################################# [100%]
[root@centos7 data]#rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-client-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-8.0.17-1.e################################# [100%]
[root@centos7 data]#rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-server-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-8.0.17-1.e################################# [100%]
  1. 启动服务，找到临时生成的密码登录
[root@centos7 data]#systemctl start mysqld
[root@centos7 data]#ss -ntl #3306端口是否开放监听
[root@centos7 data]#grep password /var/log/mysqld.log #查看生成的临时密码
2019-07-28T06:29:54.935883Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: fp+pdpkd;5:D
[root@centos7 etc]#mysql -p"fp+pdpkd;5:D"
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 14
Server version: 8.0.17

Copyright (c) 2000, 2019, 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>

2、二进制安装mariadb

  1. 官网下载官方制作好的二进制包解压
[root@centos7 ~]#rz
rz waiting to receive.
Starting zmodem transfer.  Press Ctrl+C to cancel.
Transferring mariadb-10.2.25-linux-x86_64.tar.gz...
  100%  447222 KB    9126 KB/sec    00:00:49       0 Errors

[root@centos7 ~]#tar xf mariadb-10.2.25-linux-x86_64.tar.gz -C /usr/local
[root@centos7 ~]#ls /usr/local/
bin  games    lib    libexec                       sbin   src
etc  include  lib64  mariadb-10.2.25-linux-x86_64  share
  1. 制作软链接/usr/local/mysql指向解压出来的目录，使用软链接后期变动升级更改方便，官方二进制指定的位置是/usr/local/mysql，所以不能改位置
[root@centos7 ~]#ln -s /usr/local/mariadb-10.2.25-linux-x86_64/ /usr/local/mysql
[root@centos7 ~]#ll /usr/local/mysql
lrwxrwxrwx 1 root root 40 Jul 25 23:28 /usr/local/mysql -> /usr/local/mariadb-10.2.25-linux-x86_64/
[root@centos7 ~]#ls /usr/local/mysql/
bin      EXCEPTIONS-CLIENT  man           scripts        THIRDPARTY
COPYING  include            mysql-test    share
CREDITS  INSTALL-BINARY     README.md     sql-bench
data     lib                README-wsrep  support-files

[root@centos7 ~]#ll /usr/local/mysql/
total 176
drwxr-xr-x  2 1021 1004  4096 Mar 15 01:07 bin
-rw-r--r--  1 1021 1004 17987 Jun 15 02:29 COPYING
-rw-r--r--  1 1021 1004  2354 Jun 15 02:29 CREDITS
drwxrwxr-x  3 1021 1004    18 Jun 15 23:35 data
-rw-r--r--  1 1021 1004  8245 Jun 15 02:29 EXCEPTIONS-CLIENT
drwxrwxr-x  3 1021 1004    19 Jun 15 23:35 include
-rw-r--r--  1 1021 1004  8694 Jun 15 02:29 INSTALL-BINARY
drwxr-xr-x  5 1021 1004   335 Mar 15 01:07 lib
drwxrwxr-x  4 1021 1004    30 Jun 15 23:35 man
drwxrwxr-x 11 1021 1004  4096 Jun 15 23:35 mysql-test
-rw-r--r--  1 1021 1004  2843 Jun 15 02:29 README.md
-rw-r--r--  1 1021 1004 19477 Jun 15 02:29 README-wsrep
drwxrwxr-x  2 1021 1004    30 Jun 15 23:35 scripts
drwxrwxr-x 32 1021 1004  4096 Jun 15 23:35 share
drwxrwxr-x  4 1021 1004  4096 Jun 15 23:35 sql-bench
drwxrwxr-x  3 1021 1004   275 Jun 15 23:35 support-files
-rw-r--r--  1 1021 1004 86263 Jun 15 02:29 THIRDPARTY
  1. 设置文件的权限
[root@centos7 ~]#chown -R  root.root /usr/local/mysql/
[root@centos7 ~]#ll /usr/local/mysql/                 
total 176
drwxr-xr-x  2 root root  4096 Mar 15 01:07 bin
-rw-r--r--  1 root root 17987 Jun 15 02:29 COPYING
-rw-r--r--  1 root root  2354 Jun 15 02:29 CREDITS
drwxrwxr-x  3 root root    18 Jun 15 23:35 data
-rw-r--r--  1 root root  8245 Jun 15 02:29 EXCEPTIONS-CLIENT
drwxrwxr-x  3 root root    19 Jun 15 23:35 include
-rw-r--r--  1 root root  8694 Jun 15 02:29 INSTALL-BINARY
drwxr-xr-x  5 root root   335 Mar 15 01:07 lib
drwxrwxr-x  4 root root    30 Jun 15 23:35 man
drwxrwxr-x 11 root root  4096 Jun 15 23:35 mysql-test
-rw-r--r--  1 root root  2843 Jun 15 02:29 README.md
-rw-r--r--  1 root root 19477 Jun 15 02:29 README-wsrep
drwxrwxr-x  2 root root    30 Jun 15 23:35 scripts
drwxrwxr-x 32 root root  4096 Jun 15 23:35 share
drwxrwxr-x  4 root root  4096 Jun 15 23:35 sql-bench
drwxrwxr-x  3 root root   275 Jun 15 23:35 support-files
-rw-r--r--  1 root root 86263 Jun 15 02:29 THIRDPARTY
  1. 添加mysql用户组与用户
[root@centos7 ~]#useradd -r -s /sbin/nologin -d /data/mysql -c 'mariadb user' mysql
[root@centos7 ~]#getent passwd mysql
mysql:x:987:981:mariadb user:/data/mysql:/sbin/nologin
参考rpm安装时的
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

[root@centos7 ~]#mkdir /data/mysql
方法二
[root@centos7 ~]#install -d /data/mysql -o mysql -g mysql
  1. 确认数据库存放目录mysql是否有权限
[root@centos7 ~]#ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Jul 25 23:42 /data/mysql/
  1. 开始安装，指定用户与数据库存放目录，注意此操作必须在/usr/local/mysql下进行，安装过程中程序查找文件时相对次路径的
[root@centos7 ~]#cd /usr/local/mysql/scripts/
[root@centos7 scripts]#ls
mysql_install_db
[root@centos7 scripts]#cd ..
[root@centos7 mysql]#pwd
/usr/local/mysql
[root@centos7 mysql]#scripts/mysql_install_db --user=mysql --datadir=/data/mysql 
Installing MariaDB/MySQL system tables in '/data/mysql' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'./bin/mysqladmin' -u root password 'new-password'
'./bin/mysqladmin' -u root -h centos7.localdomain password 'new-password'

Alternatively you can run:
'./bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '.' ; ./bin/mysqld_safe --datadir='/data/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd './mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
  1. 安装成功后查看数据库中已经生成了文件
[root@centos7 mysql]#ls /data/mysql/ -l
total 110620
-rw-rw---- 1 mysql mysql    16384 Jul 25 23:52 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Jul 25 23:52 aria_log_control
-rw-rw---- 1 mysql mysql      938 Jul 25 23:52 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Jul 25 23:52 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jul 25 23:52 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 25 23:52 ib_logfile1
drwx------ 2 mysql mysql     4096 Jul 25 23:52 mysql
drwx------ 2 mysql mysql       20 Jul 25 23:52 performance_schema
drwx------ 2 mysql mysql       20 Jul 25 23:52 test
  1. 准备配置文件目录及配置文件，可以复制官方提供的配置文件进行修改，复制过去时记得改名
[root@centos7 mysql]#mkdir /etc/mysql
[root@centos7 mysql]#ls /etc/mysql/
[root@centos7 mysql]#pwd
/usr/local/mysql
[root@centos7 mysql]#ls support-files/
binary-configure        my-large.cnf         mysql-log-rotate  wsrep_notify
magic                   my-medium.cnf        mysql.server
my-huge.cnf             my-small.cnf         policy
my-innodb-heavy-4G.cnf  mysqld_multi.server  wsrep.cnf
[root@centos7 mysql]#cp support-files/my-huge.cnf  /etc/mysql/my.cnf
[root@centos7 mysql]#vim /etc/mysql/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
#socket         = /tmp/mysql.sock
socket         = /data/mysql/mysql.sock                                        

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
datadir=/data/mysql
port            = 3306
#socket         = /tmp/mysql.sock
socket         = /data/mysql/mysql.sock
[root@centos7 mysql]#ls /etc/mysql/
my.cnf
  1. 准备服务文件，复制官方提供的服务文件到/etc/init.d/下最好改名为mysqld，加入开机启动项
[root@centos7 mysql]#ls
bin      EXCEPTIONS-CLIENT  man           scripts        THIRDPARTY
COPYING  include            mysql-test    share
CREDITS  INSTALL-BINARY     README.md     sql-bench
data     lib                README-wsrep  support-files
[root@centos7 mysql]#ls support-files/
binary-configure        my-large.cnf         mysql-log-rotate  wsrep_notify
magic                   my-medium.cnf        mysql.server
my-huge.cnf             my-small.cnf         policy
my-innodb-heavy-4G.cnf  mysqld_multi.server  wsrep.cnf
[root@centos7 mysql]#ll support-files/mysql.server 
-rwxr-xr-x 1 root root 12242 Jun 15 02:34 support-files/mysql.server
[root@centos7 mysql]#cp support-files/mysql.server /etc/init.d/
[root@centos7 mysql]#cd /etc/init.d/
[root@centos7 init.d]#ls
functions  mysql.server  netconsole  network  README
[root@centos7 init.d]#mv mysql.server mysqld
[root@centos7 init.d]#chkconfig --add mysqld 
[root@centos7 init.d]#chkconfig --list       

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off
  1. 启动服务，查看服务的3306端口是否开放
[root@centos7 init.d]#service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@centos7 init.d]#ss -ntl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128         *:111                     *:*                  
LISTEN      0      128         *:6000                    *:*                  
LISTEN      0      5      192.168.122.1:53                      *:*                  
LISTEN      0      128         *:22                      *:*                  
LISTEN      0      128    127.0.0.1:631                     *:*                  
LISTEN      0      100    127.0.0.1:25                      *:*                  
LISTEN      0      80         :::3306                   :::*                  
LISTEN      0      128        :::111                    :::*                  
LISTEN      0      128        :::6000                   :::*                  
LISTEN      0      128        :::22                     :::*                  
LISTEN      0      128       ::1:631                    :::*                  
LISTEN      0      100       ::1:25                     :::*
  1. 将与服务相关的脚本目录加入到$PATH变量中并生效，方便操作
[root@centos7 bin]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7 bin]#. /etc/profile.d/mysql.sh
[root@centos7 bin]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.25-MariaDB-log 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)]> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select @@datadir;
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> use mysql
Database changed
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N        |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N        |       |
| File_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N        |       |
| References_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N        |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N        |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N        |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N        |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N        |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |          |       |
| ssl_cipher             | blob                              | NO   |     | NULL     |       |
| x509_issuer            | blob                              | NO   |     | NULL     |       |
| x509_subject           | blob                              | NO   |     | NULL     |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0        |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0        |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0        |       |
| max_user_connections   | int(11)                           | NO   |     | 0        |       |
| plugin                 | char(64)                          | NO   |     |          |       |
| authentication_string  | text                              | NO   |     | NULL     |       |
| password_expired       | enum('N','Y')                     | NO   |     | N        |       |
| is_role                | enum('N','Y')                     | NO   |     | N        |       |
| default_role           | char(80)                          | NO   |     |          |       |
| max_statement_time     | decimal(12,6)                     | NO   |     | 0.000000 |       |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)

MariaDB [mysql]> select user,host,password,authentication_string from user;
+------+---------------------+----------+-----------------------+
| user | host                | password | authentication_string |
+------+---------------------+----------+-----------------------+
| root | localhost           |          |                       |
| root | centos7.localdomain |          |                       |
| root | 127.0.0.1           |          |                       |
| root | ::1                 |          |                       |
|      | localhost           |          |                       |
|      | centos7.localdomain |          |                       |
+------+---------------------+----------+-----------------------+
6 rows in set (0.00 sec)
  1. 【特别说明】官方提供的二进制编译很多参数路径设置好了，不可更改，例如socket只能放在/tmp/mysql.sock
[root@centos7 bin]#pwd
/usr/local/mysql/bin
/etc/mysql/my.cnf中的socket不改路径还原，因为官方编译好的二进制含有
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
#socket         = /data/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
datadir=/data/mysql
port            = 3306
socket          = /tmp/mysql.sock
#socket         = /data/mysql/mysql.sock
  1. 重启服务，跑安全脚本进行设置
[root@centos7 mysql]#service mysqld restart 
Restarting mysqld (via systemctl):                         [  OK  ]
[root@centos7 mysql]#ll /tmp/mysql.sock 
srwxrwxrwx 1 mysql mysql 0 Jul 26 00:33 /tmp/mysql.sock
[root@centos7 mysql]#mysql_secure_installation    

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
  1. 测试二进制安装成功
[root@centos7 mysql]#mysql -uroot -pcentos 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.2.25-MariaDB-log 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)]> use mysql
Database changed
MariaDB [mysql]> select user,host,password,authentication_string from user;
+------+-----------+-------------------------------------------+-----------------------+
| user | host      | password                                  | authentication_string |
+------+-----------+-------------------------------------------+-----------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |                       |
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |                       |
| root | ::1       | *128977E278358FF80A246B5046F51043A2B1FCED |                       |
+------+-----------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

3、编译安装mariadb

  1. 准备源码编译的环境安装需要的包

  2. 创建用户，计划好将来的数据库存放的位置并创建好文件夹，设置好权限，解压官网下载的源码

  3. 浏览解压后的目录

  4. 与之前不同的是这里使用的cmake，设置好自己所需的参数

  5. 开始编译安装，这里是同时4线程编译

  6. 安装完成后查看安装目录的文件是否生成

  7. 生成数据库文件，生成完毕后查看目录内是否生成文件

  8. 准备环境变量，方便以后使用命令

  9. 准备配置文件

    编译的时候写好了 文件不用修改 核对下

  10. 准备启动脚本，启动服务

  11. 运行安全设置脚本后需要重新启动服务生效

4、students表的数据，实现下面的需求，写出sql：

MariaDB [db1]> select * from students;
+—-+——–+——-+——-+——+——-+
| id | name | phone | score | swx | class |
+—-+——–+——-+——-+——+——-+
| 1 | adc | 110 | 86 | m | 1 |
| 2 | zhou | NULL | 80 | f | 1 |
| 3 | fei | NULL | 100 | f | 2 |
| 4 | lin | NULL | 88 | m | 2 |
| 5 | wang | 1000 | 99 | m | 2 |
| 6 | 飞哥 | 10000 | 99 | m | 2 |
+—-+——–+——-+——-+——+——-+

题目给出的表中无年龄，附出下表

MariaDB [hellodb]> select * from students; 
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

1）在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> select Name,Age from students where Age>25 and Gender='m';  
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.00 sec)

2）以ClassID为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select classid,avg(age)  from students group by classid;                   
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.01 sec)

3）显示第2题中平均年龄大于30的分组及平均年龄

MariaDB [hellodb]> select classid,avg(age) from students group by classid having avg(age)>30;  
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
3 rows in set (0.01 sec)

4）显示以L开头的名字的同学的信息

MariaDB [hellodb]> select * from students where name like 'l%'; 
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)
方法二
MariaDB [hellodb]> select * from students where name rlike '^l';  
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)

5）显示TeacherID非空的同学的相关信息

MariaDB [hellodb]> select * from students where teacherid is not null;    
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

表制作相关
准备表格

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

MariaDB [(none)]> use db1
Database changed
MariaDB [db1]> create table students;
MariaDB [db1]> create table students;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [db1]> create table students (id int unsigned auto_increment primary key,name varchar(10) not null,phone char(11),score int,swx enum('f','m') default 'm',class int unsigned);
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| phone | char(11)         | YES  |     | NULL    |                |
| score | int(11)          | YES  |     | NULL    |                |
| swx   | enum('f','m')    | YES  |     | m       |                |
| class | int(10) unsigned | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

MariaDB [db1]> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `phone` char(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `swx` enum('f','m') DEFAULT 'm',
  `class` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MariaDB [db1]> insert into students values (1,'adc',110,86,'m',1),(2,'zhou',null,80,'f',1),(3,'fei',null,100,'f',2),(4,'lin',null,88,'m',2),(5,'wang',1000,99,'m',2),(6,'飞哥',10000,99,'m',2);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [db1]> select * from students;
+----+--------+-------+-------+------+-------+
| id | name   | phone | score | swx  | class |
+----+--------+-------+-------+------+-------+
|  1 | adc    | 110   |    86 | m    |     1 |
|  2 | zhou   | NULL  |    80 | f    |     1 |
|  3 | fei    | NULL  |   100 | f    |     2 |
|  4 | lin    | NULL  |    88 | m    |     2 |
|  5 | wang   | 1000  |    99 | m    |     2 |
|  6 | 飞哥   | 10000 |    99 | m    |     2 |
+----+--------+-------+-------+------+-------+
6 rows in set (0.00 sec)
ITISJack
关注
SQL安装细则
zhengalen的博客
07-17 412
安装包链接自取链接：https://pan.baidu.com/s/16SNTmQ4F3Z8LiyYAvF6pAg 提取码：hc4g 安装分解：下载压缩包后+解压+配置环境变量+创建my.ini文件+命令窗口安装。 1、配置环境变量： 将解压后的文件夹中的bin所在的文件地址：D:\az\mysql-8.0.16-winx64\bin放到环境变量中的path中。记得点击确认哦！ 2、...
MySQL入门到进阶sql脚本案例资源合集.zip
最新发布
08-11
这个资源合集是一个从MySQL入门到进阶的SQL脚本案例集合。它涵盖了各种难度级别的案例，帮助读者从初学者逐步提升到进阶水平。 在这个资源合集中，你可以找到许多不同类型的SQL脚本案例，包括创建数据库和表、插入...
mysql 安装及简单SQL语句
wltulongdao001的博客
03-19 374
1.mysql 数据库安装  1.保存安装中的数据库密码，  2>配置环境变量  查看隐藏文件  open.bash_profile   如果没有隐藏文件则需要创建隐藏文件  touch .bash_profile      添加环境变量 export PATH=$PATH:/usr/local/mysql/bin/  保存退出。     注意 重启mysql    打开终端进入mysql命...
mysql 语句安装_mysql的安装及简单sql语句
weixin_39699121的博客
02-03 155
本文简要介绍了mysql的安装和简单的sql语句实验环境Linux Rhel6.0安装：yum install mysql mysql-servermysql_secure_installation 可以进行密码等一系列的设置，都选y即可之后登录的话就要用 mysql -uroot -p 不要有空格 -uroot可以省略简单sql语句mysql>show databases;//看一下...
mySQL安装及部分语句
tewbie的博客
03-21 365
免安装版mysql安装步骤
mysql的安装与语句介绍
qq_43164534的博客
09-18 206
数据库： mysql 关系型 mongoDB 文档型 redis key-value型 安装： sudo apt-get install mysql-server 2.安装过程中会提示输入：root管理员的密码。。。 如果没有密码： xxxx 启动mysql服务器 service mysql start service mysql restart —》只要修改配置文件 service ...
mysql sql语句示例1
12-28
根据提供的文件信息，我们可以归纳出一系列关于MySQL的基本操作与概念，包括如何创建表格、定义不同类型的字段、设置主键、外键以及各种约束等。接下来将详细解释这些知识点。 ### 创建表格 #### 示例1：创建学生...
MySQL基础入门：安装、配置与SQL语句详解
本篇笔记是关于MySQL基础课程的学习资料，主要涵盖了...这篇笔记提供了MySQL初学者必备的基础知识，包括数据库基础知识、MySQL软件的安装、维护和SQL语言的入门介绍，为学习者后续深入学习MySQL打下了坚实的基础。
mysql install语句_mysql安装及使用语句
weixin_31100713的博客
02-07 253
http://www.mysql.com/downloads/ubuntu安装sqlitehttp://www.cnblogs.com/zhuyp1015/p/3561470.htmlhttp://blog.chinaunix.net/uid-20672803-id-3049747.htmlhttp://blog.csdn.net/ws84643557/article/details/693984...
mysql install语句_MySQL入门学习（一）安装与基本操作
weixin_35701203的博客
02-07 510
安装篇PHP+MySQL+Linux目前已逐渐成为小型web服务器的一种经典组合。在Windows环境下构筑和调试MySQL数据库是许多网站开发者的一种首选。本人在Windows98环境下初学MySQL，现将学习过程与经验总结出来供大家参考。1、下载mysql-3.23.35-win.zip并解压；2、运行setup.exe;选择d:mysql,"tyical install"3、启动mysql，...
38 MySQL安装 sql语句
weixin_34342992的博客
05-10 77
数据库概念 一款软件，安装在计算机 | 服务器上， 只要告诉它：创建一个文件 | 新增一个数据 | 删除一个数据，它就能帮我们操作 对应名称关系：文件夹（库）| 文件（表）| 表头（字段）| 数据/记录（表数据） MySQL：用于管理数据的一个软件(两个程序) 只要是基于网络通信，底层都是socket！！！ 　　服务端软件 　　socket服务端 　　本地文件操作 ...
mysql安装及使用语句
weixin_33775582的博客
09-24 73
http://www.mysql.com/downloads/ ubuntu安装sqlite http://www.cnblogs.com/zhuyp1015/p/3561470.html http://blog.chinaunix.net/uid-20672803-id-3049747.html http://blog.csdn.net/ws84643557/article/...
mysql 语句安装_MySQL安装 和 基本SQL语句
weixin_42494902的博客
02-27 134
安装时配置好用户名、密码，用户名一般默认为root,安装完成后打开MySQL Command Line Client.输入密码完成登录。下面是一些简单操作：切记 每一个命令之后都要以分号(;)结尾，MySQL才会执行操作。A.编辑数据库、表1.show databases;显示已有多少个数据库 2.use 数据库名称;use select_test; 进入指定的数据库 3.show table...
Install SQL2005
weixin_30299539的博客
08-13 140
Install SQL2005 1, 第一张盘只有部分安装文件,如果安装涉及第二张盘,需要刻成光盘安装 2, SQL管理工具在第二张盘steup->SqlRun_Tools.msi. 如果安装后发现没有管理工具请直接安装这个文件 posted on 2009-10-2...
MySQL安装 和 基本SQL语句
奚学长的工地
05-28 523
首先安装MySQL，下载地址 ：http://dev.mysql.com/downloads/windows/installer/ 安装时配置好用户名、密码，用户名一般默认为root,安装完成后打开MySQL Command Line Client. 输入密码完成登录。 下面是一些简单操作：切记  每一个命令之后都要以分号(;)结尾，MySQL才会执行操作。 A.编辑数据库、表
Mysql安装及常用语句
weixin_30917015的博客
12-08 210
安装时注意使用”管理员身份运行”的方式来运行CMD命令窗口。 安装数据库，要先配置my.ini文件。 [mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=C:\Program Files\MySQL # 设置mysql数据库的数据的存放目录 datadir=C:\Program Files\MySQL\Data # 允许最大连接...
[SQL]Ubuntu安装mysql并复习SQL语句
weixin_30911451的博客
08-22 104
1.安装MySql： sudo apt update sudo apt upgrade -y sudo apt install mysql-server -y 2.配置MySQL： #配置密码 和 安全策略 可以无脑按Y sudo mysql_secure_installation ---- Securing the MySQL server deployme...
MySql安装
weixin_44646187的博客
03-11 1915
1.安装包下载地址，不建议通过.exe的方式安装，因为会对注册表进行操作，难以卸载。 https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip 2.下载后将压缩包解压。 3.将文件名mysql-5.7.19-win64改为mysql-5.7.19。 4.在D盘新建一个Environment文件夹，将解压后的MySql文件夹剪切至此，我的mysql-5.7.19存放路径是D:\soft\Environment\mysql-5.7
MySQL数据库入门与常用SQL语句详解
"MySQL数据库介绍及常用SQL语句（整理） MySQL是一个开源且免费的关系型数据库管理系统，常用于中小型项目，由Oracle公司维护。它提供了一种高效、可靠的方式来存储和管理数据。相比于非关系型数据库，如Redis，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值