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]# 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为基准,比它大的放右边,比它小的放左面
mysql数据库的根柢操作

数据库的四种操作句子

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

DML句子操作

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句子操作

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)

以上是咱们数据库的根柢操作指令。下一张是数据库的索引和视图