文章目錄
- 1.關系型資料庫介紹
-
- 1.1資料結構模型
- 1.2 RDBMS專業名詞
- 1.3 SQL語句
- 2. mysql安裝與配置
-
- 2.1mysql安裝
- 3示例
1.關系型資料庫介紹
1.1資料結構模型
資料結構模型主要有:
層次模型
網狀結構
關系模型
關系模型:
二維關系:row,column
資料庫管理系統:DBMS
關系:Relational,RDBMS
1.2 RDBMS專業名詞
常見的關系型資料庫管理系統:
MySQL:MySQL,MariaDB,Percona-Server
PostgreSQL:簡稱為pgsql
Oracle
MSSQL
事務:多個操作被當作一個整體對待就稱為一個事務
要看一個關系型資料庫是否支援事務,需要看其是否支援并滿足ACID測試
ACID:ACID是事務的一個基本标準
A:Automicity,原子性
C:Consistency,一緻性
I:Isolation,隔離性
D:Durability,持久性
如果你對ACID感興趣,可以檢視這裡了解詳細說明,ACID将不作為我們講解的重點。
SQL:Structure Query Language,結構化查詢語言
限制:constraint,向資料表提供的資料要遵守的限制
主鍵限制:一個或多個字段的組合,填入的資料必須能在本表中唯一辨別本行。且必須提供資料,不能為空(NOT NULL)。
一個表隻能存在一個
惟一鍵限制:一個或多個字段的組合,填入的資料必須能在本表中唯一辨別本行。允許為空(NULL)
一個表可以存在多個
外鍵限制:一個表中的某字段可填入資料取決于另一個表的主鍵已有的資料
檢查性限制
索引:将表中的一個或多個字段中的資料複制一份另存,并且這些資料需要按特定次序排序存儲
關系運算:
選擇:挑選出符合條件的行(部分行)
投影:挑選出需要的字段
連接配接
資料抽象方式:
實體層:決定資料的存儲格式,即RDBMS在磁盤上如何組織檔案
邏輯層:描述DB存儲什麼資料,以及資料間存在什麼樣的關系
視圖層:描述DB中的部分資料
1.3 SQL語句
SQL語句有三種類型:
DDL:Data Defination Language,資料定義語言
DML:Data Manipulation Language,資料操縱語言
DCL:Data Control Language,資料控制語言
SQL語句類型 | 對應操作 |
---|---|
DDL | CREATE:建立 DROP:删除 ALTER:修改 |
DML | INSERT:向表中插入資料 DELETE:删除表中資料 UPDATE:更新表中資料 SELECT:查詢表中資料 |
DCL | GRANT:授權 REVOKE:移除授權 |
2. mysql安裝與配置
2.1mysql安裝
mysql安裝方式有三種:
源代碼:編譯安裝
二進制格式的程式包:展開至特定路徑,并經過簡單配置後即可使用
程式包管理器管理的程式包:
rpm:有兩種
OS Vendor:作業系統發行商提供的
項目官方提供的
deb
搭建MYSQL
[[email protected] ~]# groupadd -r mysql
[[email protected] ~]# useradd -M -s /sbin/nologin -g mysql mysql
[[email protected] ~]# tar xf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[[email protected] ~]# cd /usr/local/
[[email protected] local]# ln -sv mysql-5.7.23-linux-glibc2.12-x86_64/ mysql
[[email protected] local]# chown -R mysql.mysql /usr/local/mysql
[[email protected] ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH'>/etc/profile.d/mysql.sh
[[email protected] ~]# . /etc/profile.d/mysql.sh
[[email protected] ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[[email protected] ~]# mkdir /opt/data
[[email protected] ~]# chown -R mysql.mysql /opt/data/
[[email protected] ~]#/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
[[email protected] ~]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF
[[email protected] ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
[[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[[email protected] ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[[email protected] ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[[email protected] ~]# /etc/init.d/mysqld start
[[email protected] ~]# ps -ef|grep mysql
3示例
環境準備
主機名 | ip | 環境 |
---|---|---|
psr | 192.168.56.128 | redhat7 |
[[email protected] ~]# systemctl stop firewalld.service
[[email protected] ~]# setenforce 0
[[email protected] ~]# cd /usr/src
[[email protected] src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[[email protected] src]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
[[email protected] src]# cd /etc/yum.repos.d
[[email protected] yum.repos.d]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel
[[email protected] yum.repos.d]# systemctl start mysqld
[[email protected] yum.repos.d]# systemctl status mysqld
[[email protected] yum.repos.d]# systemctl enable mysqld
修改密碼
[[email protected] yum.repos.d]# grep "password" /var/log/mysqld.log
2019-02-19T18:32:33.525415Z 1 [Note] A temporary password is generated for [email protected]: _36b?%Zh8waF _36b?%Zh8waF
[[email protected] yum.repos.d]# mysql -uroot -p輸入密碼
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'pengsuran123!';修改密碼為pengsuran123!
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
示例一
建立一個以你名字為名的資料庫,并建立一張表student,該表包含三個字段(id,name,age),表結構如下:
mysql> desc student;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
±------±-------------±-----±----±--------±------+
3 rows in set (0.00 sec)
[[email protected] yum.repos.d]# mysql -uroot -ppengsuran123!
mysql> create database pengsuran;
mysql> use pengsuran
mysql> create table student(id int(11) NOT NULL,name CHAR(100) NOT NULL,age tinyint NULL);
mysql> DESC pengsuran.student;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(10) | NO | | NULL | |
| name | char(100) | NO | | NULL | |
| age | tinyint(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
示例二
檢視下該建立的表有無内容(用select語句)
mysql> select * from student;
Empty set (0.00 sec)
示例三
往建立的student表中插入資料(用insert語句),結果應如下所示:
±—±------------±-----+
| id | name | age |
±—±------------±-----+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
±—±------------±-----+
mysql> insert into student (id,name,age)values (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
示例四
.修改lisi的年齡為50
mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
示例五
以age字段降序排序
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+c;
示例六
查詢student表中年齡最小的3位同學
mysql> select * from student order by age limit 3;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 9 | wangwu | 3 |
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
+----+----------+------+
示例七
查詢student表中年齡最大的4位同學
mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
示例八
查詢student表中名字叫zhangshan的記錄
mysql> select name from student where name='zhangshan';
+-----------+
| name |
+-----------+
| zhangshan |
| zhangshan |
+-----------+
示例九
查詢student表中名字叫zhangshan且年齡大于20歲的記錄
mysql> select name from student where age>20 and name='zhangshan';
+-----------+
| name |
+-----------+
| zhangshan |
示例十
查詢student表中年齡在23到30之間的記錄
mysql> select name from student where age between 23 and 30 ;
+-----------+
| name |
+-----------+
| jerry |
| wangqing |
| sean |
| zhangshan |
+-----------+
示例十一
修改wangwu的年齡為100
mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
示例十二
删除student中名字叫zhangshan且年齡小于等于20的記錄
mysql> delete from student where name='zhangshan' and age <= 20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+