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為基準，比它大的放右邊，比它小的放左邊

數據庫的四種操作語句

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)

以上是我們數據庫的基本操作命令。下一張是數據庫的索引和視圖