下文内容主要给大家带来基本操作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]# ls mysql-boost-5.7.20.tar.gz nginx-1.12.2.tar.gz php-7.1.20.tar.gz nginx-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 = 3306 default-character-set=utf8 socket = /usr/local/mysql/mysql.sock [mysql] ##客户端 port = 3306 default-character-set=utf8 socket = /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 #设置开启自启动mysql Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.serv [root@localhost mysql]# netstat -ntap | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 73971/mysqld #设置mysql密码 [root@localhost mysql]# mysqladmin -u root -p password '123123'#一开始是空的密码，可以设置abc123 Enter 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 -p Enter password:
[root@localhost ~]# mysql -u root -p #进入数据库 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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> 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为基准，比它大的放右边，比它小的放左边
是Structured Query Language的缩写，即结构化查询语言
是关系型数据库的标准语言
用于维护管理数据库，如数据查询，数据更新，访问控制，对象管理等功能
> SQL分类
DDL：数据定义语言
DML：数据操纵语言
DQL：数据查询语言
DCL：数据控制语言
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 1 mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> 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)mysql> use test; #进入test数据库 Database changed mysql> 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列，来自主键索引id1 Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> 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是数据查询语句，只有一条：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)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数据库流程，如果大家还有更多需要了解的可以持续关注我们亿速云的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
