MySQL資料庫增刪改查常用陳述句詳解

一 MySQL資料庫表結構
- 1.1 常見資料型別
- 1.2 常用約束型別
- 1.3 MySQL存盤引擎
二 MySQL前置基本操作
- 2.1 修改資料庫密碼
- - 2.1.1 創建登錄用戶
  - 2.1.2 給用戶授權登錄
  - 2.1.3 測驗用戶登錄
  - 2.1.4 修改用戶自身密碼
  - 2.1.5 root用戶更改其他用戶密碼
  - 2.1.6 root找回密碼及修改
- 2.2 查看資料庫結構
- 2.3 創建和洗掉資料庫
- 2.4 使用資料庫
- 2.5 查看表
- 2.6 查看資料表的結構
三 DML陳述句
- 3.1 INSERT插入新資料
- 3.2 UPDATE更新表中原有資料
- 3.3 DELETE洗掉不需要的資料
四 DQL陳述句：資料查詢陳述句
- 4.1 基礎查詢陳述句
- 4.2 進階查詢陳述句
- - 4.2.1準備作業：創建資料庫player，表player
  - 4.2.2 按等級降序查詢level大于45的記錄
  - 4.2.3 ORDER BY 陳述句多欄位排序
  - 4.2.4 GROUP BY陳述句
  - 4.2.5 限制結果條目
  - 4.2.6 設定別名
  - 4.2.7 通配符
  - 4.2.8 子查詢
五 DCL陳述句：設定用戶權限
- 用戶不存在時新建用戶
- 撤銷用戶的權限
- 清空表資料
- 在已有表中添加欄位
- 遠程登錄其他用戶的資料庫
六臨時表
七克隆表
- 7.1 方法一：通過like方法，復制ky表生成test表
- 7.2 方法二：通過創建表的方法克隆表

一 MySQL資料庫表結構

資料以表格的形式出現，每行為單獨的一條記錄，每列為一個單獨的欄位，許多的記錄和欄位組成一張表單（table）若干的表單組成（database），

1.1 常見資料型別

字串型別（CHAR（O-255固定長度）
VARCHAR（O-255可變長度））
數值型別（INT（整數型）、FLOAT（浮點型）)
日期和時間型別（DATE（年月日）、TIME（時分秒））

1.2 常用約束型別

約束是一種限制，它通過對表的行或到的資料做出限制，來確保表的資料的完整性、唯一性，

主鍵約束 primary key:
主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也
不允許出現空值，每個表最多只允許一個主鍵，建立主鍵約束可以在列級別創建，也可以在表級別創建，當創建主鍵的約束時，系統默認會在所在的列和列組合上建立對應的唯一索引，

外鍵約束foreign key:
外鍵約束是保證一個或兩個表之間的參照完整性，外鍵是構建于一個表的
兩個欄位或是兩個表的兩個欄位之間的參照關系，

唯一約束unique:
唯一約束是指定table的列或列組合不能重復，保證資料的唯一性，唯一約束不允許出現重復的值，但是可以為多個null，同一個表可以有多個唯一約束，多個列組合的約束，在創建唯一約束時，如果不給唯一約束名稱，就默認和列名相同，唯一約束不僅可以在一個表內創建，而且可以同時多表創建組合唯一約束，

非空約束not null與默認值default:
非空約束用于確保當前列的值不為空值，非空約束只能出現在表物件的列上，Null型別特征:所有的型別的值都可以是null，包括int、float等資料型別，

1.3 MySQL存盤引擎

存盤引擎就是存盤資料，建立索引，更新查詢資料等等技術的實作方式，存盤引擎是基于表的，而不是基于庫的，所以存盤引擎也可被稱為表型別，Oracle，SqlServer等資料庫只有一種存盤引擎，MySQL提供了插件式的存盤引擎架構，所以MySQL存在多種存盤引擎，可以根據需要使用相應引擎，或者撰寫存盤引擎，

MYISAM: 默認引擎、插入和查詢速度較快，支持全文索引，不支持事務、行級鎖和外鍵約束等功能，
INNODB: 支持事務、行級鎖和外鍵約束等功能，
MEMORY: 作業在記憶體中，通過散列欄位保存資料，速度快、不能永久保存資料，

二 MySQL前置基本操作

2.1 修改資料庫密碼

MySQL登錄及退出命令:
設定密碼: mysqladmin -uroot password’123’

登錄:mysql -u用戶名 -p密碼 -P埠 -S套接字檔案
-p 用戶密碼.
-h 登陸位置（主機名或ip地址）
-P 埠號(3306改了就不是了）
-S 套接字檔案（/var/lib/mysql/mysql.sock)
退出命令:exit或ctrl+d

2.1.1 創建登錄用戶

mysql>create user zhangsan@'%'identified by'123';
%:指任意的遠程終端

2.1.2 給用戶授權登錄

GRANT all ON *.* TO 'zhangsan'@'192.168.1.149' IDENTIFIED BY '123';

2.1.3 測驗用戶登錄

mysql -uzhangsan -p123 -h 192.168.1.149

2.1.4 修改用戶自身密碼

mysql>set password=password ('123456') ;

2.1.5 root用戶更改其他用戶密碼

mysql>set password for zhangsan'%'=password('123123');

2.1.6 root找回密碼及修改

關閉資料庫，修改主組態檔(/etc/my.cnf）添加: skip-grant-tables
systemctl stop mysqld 
#vim /etc/my.cnf
skip-grant-tables

啟動資料庫，空密碼登錄并修改密碼
systemctl start mysqld
update mysql.user set password=password(‘新密碼’) where user='root';
或者 update mysql.user set authentication_string=password('123') where user='root';  ###centos7
洗掉skip-grant-tables,重啟資料庫驗證新密碼

2.2 查看資料庫結構

show databases;

2.3 創建和洗掉資料庫

create database auth；
drop database auth;

2.4 使用資料庫

use auth;

2.5 查看表

mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| users          |
+----------------+

2.6 查看資料表的結構

mysql> describe users;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name   | char(16) | NO   | PRI | NULL    |       |
| user_passwd | char(48) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+

三 DML陳述句

對表中資料進行管理

3.1 INSERT插入新資料

insert into users(user_name,user_passwd) values('peiqi',password('250'));
insert into users(user_name,user_passwd) values('qiaozhi','250');
insert into users values('hapi',password('360'));

3.2 UPDATE更新表中原有資料

update users set user_passwd=password('250') where user_name='qiaozhi';
update users set user_name='peihua' where user_name='qiaozhi';

直接修改資料庫來實作資料庫管理員密碼的修改

update mysql.user set authentication_string=password('123') where user_name='root';

3.3 DELETE洗掉不需要的資料

DELETE洗掉不需要的資料
delete from users where user_name='hapi';
洗掉系統用戶
delete from mysql.user where user='yiqi';
洗掉表中所有記錄
delete from auth.users;

四 DQL陳述句：資料查詢陳述句

4.1 基礎查詢陳述句

select * from auth.users where user_name='peiqi';		#查詢用戶名為佩奇的所有記錄資訊
select user_passwd from auth.users where user_name='peiqi';	#查詢用戶名為佩奇的密碼資訊

4.2 進階查詢陳述句

4.2.1準備作業：創建資料庫player，表player

create database player;
use player;
create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','搶寶真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白胡子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
insert into player (id,name,level) values ('199','D 丶狙擊王',46);
insert into player (id,name,level) values ('298','唐三',46);
insert into player (id,name,level) values ('51','新五皇·暴雪',45);
insert into player (id,name,level) values ('272','D 丶搶人頭輔助',45);

4.2.2 按等級降序查詢level大于45的記錄

mysql> select id,name,level from player where level>=45 order by level desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀             |    47 |
|  15 | 新五皇·白胡子          |    46 |
|  63 | 新五皇–敬神            |    46 |
| 199 | D 丶狙擊王            |    46 |
| 298 | 唐三                 |    46 |
|  51 | 新五皇·暴雪           |    45 |
| 272 | D 丶搶人頭輔助        |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

4.2.3 ORDER BY 陳述句多欄位排序

查詢等級在 45 級及以上的用戶，并以 level 降序排列和 id 降序排列，

mysql> select id,name,level from player where level>=45 order by level desc,id desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀           |    47 |
| 298 | 唐三                 |    46 |
| 199 | D 丶狙擊王           |    46 |
|  63 | 新五皇–敬神          |    46 |
|  15 | 新五皇·白胡子        |    46 |
| 272 | D 丶搶人頭輔助       |    45 |
|  51 | 新五皇·暴雪          |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

注意！
ORDER BY 后面跟多個欄位時，欄位之間使用英文逗號隔開，
優先級是按先后順序而定，下面以A和B分別表示兩個欄位，
ORDER BY A,B desc 指A用升序，B用降序；
ORDER BY A asc,B desc 指A用升序，B用降序；
ORDER BY A desc,B desc 指A用降序，B用降序；

4.2.4 GROUP BY陳述句

通過 SQL 查詢出來的結果，還可以對其進行分組，使用 GROUP BY 陳述句來實作，
GROUP BY 從字面上看，是以 BY 后面的內容對查詢出的資料進行分組，就是將一個“資料集”
劃分成若干個“小區域”，然后針對這些個“小區域”進行資料處理，
GROUP BY通常都是結合聚合函式一起使用的，常用的聚合函式包括：
計數（COUNT）、求和（SUM）、求平均數（AVG）、最大值（MAX）、最小值（MIN），
這些聚合函式的用法在后面函式小節會有更詳細的講解，GROUP BY 分組的時候可以按一個或多個欄位對結果進行分組處理，

mysql> select count(name),level from player where level>=45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           2 |    45 |
|           4 |    46 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

mysql> select count(name),level from player where level>=45 group by name;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           1 |    45 |
|           1 |    46 |
|           1 |    46 |
|           1 |    47 |
|           1 |    45 |
|           1 |    46 |
|           1 |    46 |
+-------------+-------+
7 rows in set (0.00 sec)

4.2.5 限制結果條目

LIMIT 的第一個引數是位置偏移量（可選引數），是設定 MySQL 從哪一行開始顯示，
如果不設定第一個引數，將會從表中的第一條記錄開始顯示，需要注意的是，
第一條記錄的位置偏移量是 0，第二條是 1，以此類推，第二個引數是設定回傳記錄行的最大數目，
插入新的欄位

insert into player (id,name,level) values ('1','修歐拉卡',10);
insert into player (id,name,level) values ('2','起風了',10);
insert into player (id,name,level) values ('3','吊打低V',15);
insert into player (id,name,level) values ('4','小花',14);
insert into player (id,name,level) values ('5','小舞',35);

mysql> select id,name,level from player limit 3;
+----+--------------+-------+
| id | name         | level |
+----+--------------+-------+
|  1 | 修歐拉卡     |    10 |
|  2 | 起風了       |    10 |
|  3 | 吊打低V      |    15 |
+----+--------------+-------+
3 rows in set (0.00 sec)

LIMIT 子句的使用也可以結合 ORDER BY：先進行排序，然后再LIMIT限制固定的記錄，
也就是說LIMIT是放在最后的，將處理好的結果集按要求選出幾行來，
例如，將查詢記錄按等級 level 降序排列，只取前三條記錄，

mysql> select id,name,level from player order by level desc limit 3;
+----+----------------------+-------+
| id | name                 | level |
+----+----------------------+-------+
| 30 | 搶寶真多呀           |    47 |
| 15 | 新五皇·白胡子        |    46 |
| 63 | 新五皇–敬神          |    46 |
+----+----------------------+-------+
3 rows in set (0.00 sec)

在顯示結果的時候也可以不從第一行開始，引入 offset 引數，
例如，執行以下操作即可從第 3 條記錄開始顯示之后的 3 條資料，

mysql> select id,name,level from player limit 2,3;
+----+------------+-------+
| id | name       | level |
+----+------------+-------+
|  3 | 吊打低V    |    15 |
|  4 | 小花       |    14 |
|  5 | 小舞       |    35 |
+----+------------+-------+
3 rows in set (0.00 sec)

4.2.6 設定別名

在MySQL查詢時，當表的名字比較長或者表內某些欄位比較長時，為了方便書寫或者多次使用相同的表，可以給欄位列或表設定別名，使用的時候直接使用別名，簡潔明了，增強可讀性，設定別名使用AS陳述句，
在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 陳述句是可選的，AS之后的別名，主要是為表內的列或者表提供臨時的名稱，在查詢程序中使用，庫內實際的表名或欄位名是不會被改變的，
例如，在統計表內所有記錄共有多少條時，使用 count（*），這么寫不便于識別，可以將其別名設定為 number，

mysql> select count(*) as number from player;
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) number from player;		#省略as是一樣的結果
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

此外，AS還可以作為連接陳述句的運算子，例如，執行以下操作即可實作用一條 SQL陳述句完成在創建表tmp的時候將player表內的資料寫入 tmp 表，

mysql> create table tmp as select * from player;
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

4.2.7 通配符

通配符主要用于替換字串中的部分字符，通過部分字符的匹配將相關結果查詢出來，
通常通配符都是跟 LIKE 一起使用的，并協同WHERE子句共同來完成查詢任務，
常用的通配符有兩個，分別是：
%：百分號表示零個、一個或多個字符
_：下劃線表示單個字符

mysql> select id,name,level from player where name like 's%';		##name 欄位以 s 開頭的記錄
+------+--------------+-------+
| id   | name         | level |
+------+--------------+-------+
|  238 | sagou 轟總   |     7 |
|  795 | senoku       |    15 |
| 2460 | shirley      |     1 |
+------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,level from player where name like '_uess';	#替換開頭的一個字符
+-----+-------+-------+
| id  | name  | level |
+-----+-------+-------+
| 713 | guess |    25 |
+-----+-------+-------+
1 row in set (0.00 sec)

mysql> select id,name,level from player where name like 'use____';		#替換結尾的四個字符
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
+-----+---------+-------+
1 row in set (0.00 sec)

4.2.8 子查詢

子查詢也被稱作內查詢或者嵌套查詢，是指在一個查詢陳述句里面還嵌套著另一個查詢陳述句子查詢陳述句是先于主查詢陳述句被執行的，其結果作為外層的條件回傳給主查詢進行下一步的查詢過濾，子查詢不僅可以在 SELECT 陳述句中使用，在 INERT、UPDATE、DELETE中也同樣適用，在嵌套的時候，子查詢內部還可以再次嵌套新的子查詢，也就是說可以多層嵌套，

mysql>  select name,level from player where id in (select id from player where level>=45);
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白胡子        |    46 |
| 搶寶真多呀           |    47 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙擊王           |    46 |
| D 丶搶人頭輔助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+

五 DCL陳述句：設定用戶權限

GRANT 權限串列 ON 資料庫名.表名 TO 用戶名@來源地址 IDENTIFIED BY ‘密碼’

mysql> GRANT all ON *.* TO 'root'@'%' IDENTIFIED BY '123';

mysql> GRANT all ON *.* TO 'root'@'localhost' IDENTIFIED BY '123';

flush privileges;		#重繪

用戶不存在時新建用戶

grant select on auth.* to 'yiqi'@'localhost' identified by '123';	#只授予查詢權限

撤銷用戶的權限

revoke all on auth.* from 'yiqi'@'20.0.0.1';

查看用戶權限

show grants for 'yiqi'@'20.0.0.1';

清空表資料

truncate table tmp;

在已有表中添加欄位

alter table users add wocao  varchar(50) not null;

遠程登錄其他用戶的資料庫

mysql -ulph -p123 -h192.168.1.60

六臨時表

CREATE TEMPORARY TABLE `mytmp` (`id` int(10) NOT NULL AUTO_INCREMENT,`NAME` varchar(32) CHARACTER SET utf8 COLLATE utF8_bin NOT
NULL, `level` int(10) NOT NULL,PRIMARY KEY (id) ) ENGINE InnoDB DEFAULT CHARSET=utf8;

七克隆表

7.1 方法一：通過like方法，復制ky表生成test表

方法一：
mysql> create table test like ky;             
Query OK, 0 rows affected (0.01 sec)

mysql> show create table ky\G
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from ky;           ##like方法復制表結構，不復制資料
Empty set (0.00 sec)

mysql> insert into test select * from users;      ##將ky表的資料寫入test表
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

7.2 方法二：通過創建表的方法克隆表

mysql> show create table ky\G         #獲取源表結構，，索引等資訊
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


CREATE TABLE "test3" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;         #改名后創建與源表一樣的表結構


mysql> insert into test3 select * from ky;                          #匯入源表資料
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from test3;                                                 #查看新創建表的資料
+-----------+-------------------------------------------+-------+
| user_name | user_passwd                               | level |
+-----------+-------------------------------------------+-------+
| lisi      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi1     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi2     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 40    |
| lisi3     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 50    |
| lisi4     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 60    |
| lisi5     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 70    |
| lisi6     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 80    |
+-----------+-------------------------------------------+-------+

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/105576.html

標籤：其他

上一篇：Mysql 與 Oracle的區別

下一篇：PHP--入門（一）

MySQL資料庫增刪改查常用陳述句詳解

MySQL資料庫增刪改查常用陳述句詳解

一 MySQL資料庫表結構

1.1 常見資料型別

1.2 常用約束型別

1.3 MySQL存盤引擎

二 MySQL前置基本操作

2.1 修改資料庫密碼

2.1.1 創建登錄用戶

2.1.2 給用戶授權登錄

2.1.3 測驗用戶登錄

2.1.4 修改用戶自身密碼

2.1.5 root用戶更改其他用戶密碼

2.1.6 root找回密碼及修改

2.2 查看資料庫結構

2.3 創建和洗掉資料庫

2.4 使用資料庫

2.5 查看表

2.6 查看資料表的結構

三 DML陳述句

3.1 INSERT插入新資料

3.2 UPDATE更新表中原有資料

3.3 DELETE洗掉不需要的資料

四 DQL陳述句：資料查詢陳述句

4.1 基礎查詢陳述句

4.2 進階查詢陳述句

4.2.1準備作業：創建資料庫player，表player

4.2.2 按等級降序查詢level大于45的記錄

4.2.3 ORDER BY 陳述句多欄位排序

4.2.4 GROUP BY陳述句

4.2.5 限制結果條目

4.2.6 設定別名

4.2.7 通配符

4.2.8 子查詢

五 DCL陳述句：設定用戶權限

用戶不存在時新建用戶

撤銷用戶的權限

清空表資料

在已有表中添加欄位

遠程登錄其他用戶的資料庫

六 臨時表

七 克隆表

7.1 方法一：通過like方法，復制ky表生成test表

7.2 方法二：通過創建表的方法克隆表

六臨時表

七克隆表