基本操作mysql数据库流程

下文内容主要给大家带来基本操作mysql数据库流程，这里所讲到的知识，与书籍略有不同，都是专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。

mysql数据库的基本操作

首先我们要把mysql装好

mkdir chen/mount.cifs //192.168.100.23/LNMP chen/[root@localhost ~]# yum -y install \gcc \gcc-c++ \ncurses \ncurses-devel \bison \cmake[root@localhost ~]# useradd -s /sbin/nologin mysql[root@localhost ~]# cd chen/[root@localhost chen]# lsmysql-boost-5.7.20.tar.gz  nginx-1.12.2.tar.gz  php-7.1.20.tar.gznginx-1.12.0.tar.gz        php-7.1.10.tar.bz2[root@localhost chen]# tar zxvf mysql-boost-5.7.20.tar.gz -C /opt[root@localhost chen]# cd /opt/[root@localhost opt]# cd mysql-5.7.20/cmake \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \-DSYSCONFDIR=/etc \-DSYSTEMD_PID_DIR=/usr/local/mysql \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_ARCHIVE_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \-DMYSQL_DATADIR=/usr/local/mysql/data \-DWITH_BOOST=boost \-DWITH_SYSTEMD=1[root@localhost mysql-5.7.20]#make[root@localhost mysql-5.7.20]#make install[root@localhost mysql-5.7.20]#cd /usr/local/[root@localhost local]# chown -R mysql:mysql mysql/[root@localhost local]# cd /opt/mysql-5.7.20/[root@localhost mysql-5.7.20]# vim /etc/my.cnf     ##调整配置文件[client]                            ##客户端port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]                           ##客户端     port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysqld]                         ##云服务器        user = mysql                  ##用户basedir = /usr/local/mysql      ##设置mysql的安装目录datadir = /usr/local/mysql/data    ##设置mysql数据库的数据的存放目录port = 3306                    ##设置3306端口character_set_server=utf8            ##中文字符集pid-file = /usr/local/mysql/mysqld.pid     ##pid文件路径socket = /usr/local/mysql/mysql.sock     ##sock文件路径server-id = 1                                           ##主从参数


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES[root@localhost local]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile#设置环境变量，使系统能识别bin和lib下的所有命令[root@localhost local]# echo 'export PATH' >> /etc/profile#全局变量[root@localhost local]# source /etc/profile #重新启动系统环境变量#初始化数据库[root@localhost local]# cd /usr/local/mysql/bin/mysqld \--initialize-insecure \--user=mysql \--basedir=/usr/local/mysql \--datadir=/usr/local/mysql/data [root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /lib/systemd/system/#把mysql启动脚本放到系统中可以systemctl可以管理[root@localhost mysql]# systemctl start mysqld.service[root@localhost ~]# systemctl enable mysqld  #设置开启自启动mysqlCreated symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.serv[root@localhost mysql]# netstat -ntap | grep 3306tcp6       0      0 :::3306                 :::*                    LISTEN      73971/mysqld        #设置mysql密码[root@localhost mysql]# mysqladmin -u root -p password '123123'#一开始是空的密码，可以设置abc123Enter password: New password: Confirm new password: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@localhost mysql]# mysql -u root -pEnter password: 

数据库基本操作命令

[root@localhost ~]# mysql -u root -p #进入数据库Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.20 Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysql   #进入数据库mysql> show tables;  #查看数据库的所有表+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || engine_cost               || event                     || func                      |mysql> desc user; #显示数据表的结构+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field                  | Type                              | Null | Key | Default               | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host                   | char(60)                          | NO   | PRI |                       |       || User                   | char()                          | NO   | PRI |                       |       || 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')      

二分查找
以32为基准，比它大的放右边，比它小的放左边

数据库的四种操作语句

SQL语句概述

SQL语言

是Structured Query Language的缩写，即结构化查询语言
是关系型数据库的标准语言
用于维护管理数据库，如数据查询，数据更新，访问控制，对象管理等功能
> SQL分类
DDL：数据定义语言
DML：数据操纵语言
DQL：数据查询语言
DCL：数据控制语言

DDL语句操作

DDL语句用于创建数据库对象，如库，表，索引等

mysql> create databases test;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 test' at line 1mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> mysql> create table info(    -> id int(3) not null,    -> name varchar(10) not null,    -> age int(5) not null,    -> score decimal default 0,    -> primary key (id));Query OK, 0 rows affected (0.12 sec)mysql> desc info;+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id    | int(3)        | NO   | PRI | NULL    |       || name  | varchar(10)   | NO   |     | NULL    |       || age   | int(5)        | NO   |     | NULL    |       || score | decimal(10,0) | YES  |     | 0       |       |+-------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

DML语句操作

mysql> use test; #进入test数据库Database changedmysql> mysql> create table info(  #创建info数据表    -> id int(3) not null,    -> name varchar(10) not null,    -> age int(5) not null,    -> score decimal default 0,    -> primary key (id));Query OK, 0 rows affected (0.12 sec)mysql> desc info; #查看数据表结构+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id    | int(3)        | NO   | PRI | NULL    |       || name  | varchar(10)   | NO   |     | NULL    |       || age   | int(5)        | NO   |     | NULL    |       || score | decimal(10,0) | YES  |     | 0       |       |+-------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> insert into info values (1,'小陈',20,99); #插入数据Query OK, 1 row affected (0.00 sec)mysql> insert into info values (2,'小王',22,88);Query OK, 1 row affected (0.00 sec)mysql> insert into info values (3,'小高',25,77);Query OK, 1 row affected (0.00 sec)mysql> select * from info;  #查看数据表当中的数据，这是DQL语句+----+--------+-----+-------+| id | name   | age | score |+----+--------+-----+-------+|  1 | 小陈   |  20 |    99 ||  2 | 小王   |  22 |    88 ||  3 | 小高   |  25 |    77 |+----+--------+-----+-------+3 rows in set (0.01 sec)mysql> update info set score='95' where id=1; #删除info表中的score列，来自主键索引id1Query OK, 1 row affected (0.14 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from info;+----+--------+-----+-------+| id | name   | age | score |+----+--------+-----+-------+|  1 | 小陈   |  20 |    95 ||  2 | 小王   |  22 |    88 ||  3 | 小高   |  25 |    77 |+----+--------+-----+-------+3 rows in set (0.00 sec)mysql> delete from info where id =2 #删除id2这行数据    -> ;Query OK, 1 row affected (0.01 sec)mysql> select * from info;+----+--------+-----+-------+| id | name   | age | score |+----+--------+-----+-------+|  1 | 小陈   |  20 |    95 ||  3 | 小高   |  25 |    77 |+----+--------+-----+-------+2 rows in set (0.00 sec)mysql> drop table info;  #删除info数据表Query OK, 0 rows affected (0.05 sec)mysql> show tables;  #查看数据表Empty set (0.00 sec)mysql> drop database test; #删除test数据库Query OK, 0 rows affected (0.04 sec)mysql> show databases; #查看数据库，当中没有test数据库了+--------------------+| Database           |+--------------------+| information_schema || chen               || mysql              || performance_schema || school             || sys                |+--------------------+6 rows in set (0.00 sec)

DQL语句操作

DQL是数据查询语句，只有一条：select
用于从数据表中查找符合条件的数据记录
查询时可不指定条件

mysql> select * from info;+----+--------+-----+-------+| id | name   | age | score |+----+--------+-----+-------+|  1 | 小陈   |  20 |    95 ||  3 | 小高   |  25 |    77 |+----+--------+-----+-------+2 rows in set (0.00 sec) mysql> select name from info where name='小高';   ##查看指定条件+--------+| name   |+--------+| 小高   |+--------+1 row in set (0.00 sec)

DCL语言操作

设置用户权限（用户不存在时，则新建用户）

mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123';Query OK, 0 rows affected, 1 warning (0.00 sec)查看用户的权限mysql> show grants for 'root'@'%';+-------------------------------------------+| Grants for root@%                         |+-------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |+-------------------------------------------+1 row in set (0.00 sec)##撤销用户的权限mysql> revoke all on *.* from 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

对于以上关于基本操作mysql数据库流程，如果大家还有更多需要了解的可以持续关注我们的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。