MySQL常用管理SQL陳述句

• 前言
  • 什么是SQL
  • SQL的分類
    • DDL
    • DCL
    • DML
    • 1.DDL陳述句之管理資料庫
      • 1.1.創建資料庫
      • 1.2.顯示資料庫
      • 1.3.切換資料庫
      • 1.4.查看資料庫包含的表資訊
        • 1.4.1.切換到資料庫里面去查看表資訊
        • 1.4.2.在庫外面查看庫里的表資訊
      • 1.5.洗掉資料庫
    • 2.DDL&&DCL陳述句之管理用戶
      • 2.1.查看當前資料庫的用戶串列
      • 2.2.創建資料庫用戶
      • 2.3.洗掉資料庫用戶
      • 2.4.授權資料庫用戶
        • 2.4.1.創建test3用戶，對test庫具備所有權限，允許從localhost主機登錄管理資料庫，密碼是test123，
        • 2.4.2.授權與root同等地位的system用戶權限，
      • 2.5.授權的權限串列
      • 2.6.企業中grant授權權限問題說明
        • 2.6.1.企業里主資料庫用戶的授權問題說明
        • 2.6.2.博客、CMS、BBS等產品的資料庫授權
        • 2.6.3.生產環境針對主庫（寫為主讀為輔）用戶的授權策略
        • 2.6.4.生產環境從庫（只讀）用戶的授權
        • 2.6.5.生產場景下的具體授權
        • 2.6.6.生產場景下，朱從庫讀寫分離授權難點與解決方案
      • 2.7.查看用戶及授權
    • 3.DDL陳述句之管理表
      • 3.1.建立表
      • 3.2.查看表結構
      • 3.3.更改表名
      • 3.4.增、刪、改表的欄位
      • 3.5.創建和洗掉索引
      • 3.6.查看建表陳述句
      • 3.7.洗掉表
    • 4.DML陳述句之管理表中的資料
      • 4.1.往表中插入資料
      • 4.2.查詢表中的資料
      • 4.3.修改表中的資料
      • 4.4.洗掉表中的資料
      • 4.5.清空表中的資料

前言

什么是SQL

SQL是結構化查詢語言，它是一種對關系型資料庫中的資料進行定義和操作的語言，是大多數關系型資料庫管理系統所支持的工業標準語言，

SQL是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系型資料庫系統，SQL常用作MySQL邏輯備份檔案的擴展名，結構化查詢語言是高級的非程序化編程語言，允許用戶在高層資料結構上作業，它不要求用戶指定資料的存放方法，也不需要用戶了解具體的資料存放方式，所以，其完全不同于底層結構，不同的資料庫系統可以使用相同的SQL作為資料輸入與管理的介面，結構化查詢語言陳述句可以嵌套，這使得它具有極大的靈活性和強大的功能，不同的資料庫系統的SQL會有一些差別，

SQL的分類

DDL

DDL，中文為資料定義語言，DDL的特點是對資料庫內部的物件進行create（創建）、alter（修改）、drop（洗掉）等操作，負責管理資料庫的基礎資料，不涉及對表中內容的操作和更改，

DCL

DCL，中文為資料控制語言，DDL的特點是對資料庫內部的物件grant（用戶授權）、revoke（權限回收）、commit（提交）、rollback（回滾），

DML

DML，中文為資料操作語言，DML的特點是對資料庫內部的物件insert（增）、delete（刪）、update（改）、select（查），主要針對資料庫中表內的資料進行操作，

1.DDL陳述句之管理資料庫

1.1.創建資料庫

create database oldboy;  #創建默認字符集資料庫
create database oldboy_gbk character set gbk collate gbk_chinese_ci;  #創建gbk字符集資料庫
create database oldboy_utf8 character set utf8 collate utf8_general_ci;  #創建utf8字符集資料庫
show create database oldboy\G  #查看建庫陳述句

1.2.顯示資料庫

show databases;  #查看所有資料庫
show databases like 'oldboy';  #匹配oldboy字串的資料庫
show databases like 'oldboy%';  #%為通配符，表示匹配以oldboy開頭的所有資料庫

1.3.切換資料庫

use oldboy;  #切換到oldboy庫
select database();  #查看當前管理員所在的庫名

1.4.查看資料庫包含的表資訊

1.4.1.切換到資料庫里面去查看表資訊

use oldboy;
show tables;

1.4.2.在庫外面查看庫里的表資訊

show tables from oldboy;  #查看指定庫oldboy中包含的表
show tables in test_gbk;
show tables from mysql like 'db%';  #匹配包含指定字符開頭的表

1.5.洗掉資料庫

drop database oldboy_gbk;  #洗掉oldboy_gbk資料庫
show databases like 'oldboy_gbk';

2.DDL&&DCL陳述句之管理用戶

2.1.查看當前資料庫的用戶串列

查看資料庫用戶串列屬于DML負責的部分內容，

select user,host from mysql.user;  #這里的select關鍵字表示查詢，是DML陳述句的關鍵字之一，user和host為要查找的MySQL表的欄位，from表示去哪查，mysql.user是MySQL庫里的user表，資料庫的標準用戶由“用戶”@“主機名”共同組成的，兩者加起來是資料庫用戶的唯一標識，

2.2.創建資料庫用戶

create user blog@localhost identified by 'blog123';
select user,host from mysql.user;  #查看所有資料庫用戶
create user bbs@'172.16.1.%' identified by 'bbs123';
select user,host from mysql.user where user='bbs;  #查看指定資料庫用戶
show grants for bbs@'172.16.1.%';
#USAGE表示連接權限

2.3.洗掉資料庫用戶

drop user 'blog'@'localhost';  #洗掉資料庫用戶命令
select user,host from mysql.user where user='blog';  #洗掉后檢查資料庫用戶
flush privileges;  #使得處理用戶后，對資料庫生效，有資料庫改動的情況，執行這個命令

delete from mysql.user where user='bbs' and host='172.16.1.%';
flush privileges;

2.4.授權資料庫用戶

2.4.1.創建test3用戶，對test庫具備所有權限，允許從localhost主機登錄管理資料庫，密碼是test123，

grant all privileges on oldboy.* to 'test'@'localhost' identified by 'test123';
select user,host from mysql.user where user='test';
show grants for 'test'@'localhost';
#ALL PRIVILEGES就是授權的權限

2.4.2.授權與root同等地位的system用戶權限，

show grants for root@localhost;  #查看root用戶的權限
grant all on *.* to 'system'@'localhost' identified by 'system123' with grant option;
grant proxy on ''@'' to 'system'@'localhost' with grant option;  #允許創建代理用戶
show grants for system@localhost;

2.5.授權的權限串列

show grants for 'test'localhost';
#ALL PRIVILEGES就是授權的權限
revoke select on oldboy.* from 'test'@'localhost';
show grants for 'oldboy'@'localhost';
#權限ALL被拆分成了更細的權限

MySQL的ALL PRIVILEGES的權限串列

2.6.企業中grant授權權限問題說明

2.6.1.企業里主資料庫用戶的授權問題說明

在企業生產環境中，如果是以web形式連接資料庫的用戶，那么盡量不要授予all權限，最好是分拆授權，比如，授予select、insert、update、delete等適合web使用的DML陳述句關鍵字權限，

grant select,insert,update,delete on oldboy.* to test@'172.16.1.%' identified by 'test123';

#注意：授權用戶權限時有如下3條安全紅線不要輕易跨過，
1、權限不能用all，要應用select、insert、update、delete等具體權限，
2、庫不能用"*.*"，而應用"oldboy.*"格式具體到庫，
3、主機不能用%，而應用內網IP段，即'172.16.1.%'格式，

PHP程式語言連接MySQL的代碼：

<?php
  //$link_id=mysql_connect('資料庫主機名','用戶','密碼')；
    $link_id=mysql_connect('172.16.1.7')
    if($link_id){
        echo "mysql successful by oldboy !";
    }else{
        echo mysql_error();
    }
?>

2.6.2.博客、CMS、BBS等產品的資料庫授權

采用web形式連接資料庫的用戶應盡量采用最小化原則進行授權，但是很多開源軟體都是通過web界面安裝的，因此，在安裝期間除了select、insert、update、delete這4個權限外，有可能還需要create、drop等比較危險的權限，

grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';

生成資料庫、表后，可以使用revoke命令識訓create、drop授權，

revoke create,drop on blog.* from 'blog'@'172.16.1.%';
show grants for 'blog'@'172.16.1.%';

2.6.3.生產環境針對主庫（寫為主讀為輔）用戶的授權策略

web或應用程式和資料庫在一臺電腦上的資料庫授權，

grant all privileges on `blog`.* to 'blog'@'localhost' identified by 'blog123';

應用程式服務器和資料庫服務器不在一個主機上，

grant all privileges on `blog`.* to 'blog'@'172.16.1.%';

由于作業中異機環境比較多,因此下面都是對命令的嚴格授權，

grant select,insert,update,delete on`blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';

2.6.4.生產環境從庫（只讀）用戶的授權

grant select on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';

#這里表示為172.16.1.0/24的用戶blog授予管理blog資料庫中所有表（*表示所有表）的只讀權限（select），密碼為blog123，

2.6.5.生產場景下的具體授權

主庫授權的命令為：

grant select,insert,update,delete on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';

從庫授權用戶的命令為：

grant select on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';

2.6.6.生產場景下，朱從庫讀寫分離授權難點與解決方案

若主從庫的MySQL庫和表是同步的，則會無法針對同一個用戶授權不同的權限，主庫授權后會自動同步到從庫上，導致從庫的授權只讀失敗，
解決方法有以下幾點，
1、取消資料庫中MySQL庫的同步功能，
2、授權主庫權限后，從庫執行回收增刪改權限，只保留查的權限，
3、不在授權上控制增刪改，而是用read-only引數控制普通用戶更新從庫，注意，read-only引數對超級用戶無效，

2.7.查看用戶及授權

select user,host from mysql.user;
show grants for 'blog'@'172.16.1.%';

3.DDL陳述句之管理表

create database oldboy;
show create database oldboy\G

3.1.建立表

create table student (
    id int(4) not null,
    name char(20) not null,
    age tinyint(2) not null default '0',
    dept varchar(16) default null
);

create table `student` (  #create table是創建表的固定關鍵字，student為表名，
    `id` int(4) not null,  #學號列，數字型別，長度為4.不為空值，
    `name` char(20) not null,  #名字列，定長字符型別，長度為20，不為空值，
    `age` tinyint(2) not null default '0',  #年齡列，很小的數字型別長度為2，不為空，默認為0值，
    `dept` varchar(16) default null  #系別符，變長字符型別，長度為16，默認為空，
) ENGINE=InnoDB default charset=utf8;  #引擎和字符集，引擎默認為InnoDB，字符集，繼承庫的utf8，

欄位型別表的對應列的型別說明

CHAR和VARCHAR之間的差別

char型別是定長，不夠的在右邊用空格補全，這會浪費存盤空間，以此列為查詢條件時，速度更快，多數系統表的欄位都是定長，
varchar型別是變長，節省存盤空間，以此列為查詢條件時速度較慢，

3.2.查看表結構

1、先通過use進入到指定庫，然后再查看，
use oldboy;
desc student;

2、無須進入指定庫，通過如下命令直接查看，
show columns from oldboy.student;
show full columns from student from oldboy;

3.3.更改表名

1、采用rename命令更改表名，
rename table student to test;
show tables;

2、采用alter法修改表名，
alter table test rename to student;
show tables;

3.4.增、刪、改表的欄位

先測驗表資料

create table `test` (
  `id` int(4) not null auto-increment,
  `name` char(20) not null,
  primary key (`id`)
) engine=innodb default charset=utf8;

然后查看表結構

desc test;

在表test中添加欄位sex、age和qq，型別分別為char(4)、int(4)、varchar(15)，

alter table test add sex char(4),add age int(4),add qq varchar(15);

指定在第一行添加qq欄位

alter table test add qq varchar(15) first;

指定在name行后面的位置添加age欄位

alter table test add age int(4) after name;

在表test中洗掉欄位sex、age和qq

alter table test drop sex,drop age,drop qq;

3.5.創建和洗掉索引

資料庫的索引就像書的目錄一樣，如果在欄位上建立了索引，那么以索引列為查詢條件時可以加快查詢資料的速度，

常見的為表內欄位建立索引的方法有兩種

1、建表后利用alter命令增加普通索引，
alter table student add index index_name(name);
desc student;

2、使用create為test表的qq列創建普通索引，
create index index_qq on test(qq);
desc test;

#PRI為主鍵索引的標識，MUL為普通索引的標識，

3.6.查看建表陳述句

show create table test\G

3.7.洗掉表

show tables from oldboy;
drop table student;  #洗掉表名為test的表，
show tables from oldboy;

4.DML陳述句之管理表中的資料

4.1.往表中插入資料

use oldboy;
drop table test;
create table test (
  id int(4) not null auto_increment,
  name char(20) not null,
  primary key (id)
);
desc test;

1、按規則指定所有的列名，并且每列都插入值，
insert into test(id,name) values(1,'oldboy');

2、只在name列插入值，由于id列是自增的，所以可以這般操作，
insert into test(name) values('oldgirl');

3、如果不指定列，就要按規則為每列都插入恰當的值，
insert into test values(3,'inca');

4、批量插入資料的方法，
insert into test values(4,'zuma'),(5,'kaka');  #批量插入2條記錄，
delete from test;
insert into test(id,name) values(1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');

select * from test;  #查看批量插入后的結果，
delete from test; #全部洗掉，

4.2.查詢表中的資料

1、進入指定庫后再查詢，
use oldboy;
select * from test;

2、直接在庫外，使用“庫.表”的方式查詢，
select * from oldboy.test;

根據指定條件查詢表的部分資料

1、查看表test中的前2行資料，
select * from test limit 2;
select * from test limit 0,3;  #從第0行開查，查3行記錄，

2、指定固定條件查詢資料，一般結尾接where關鍵字并指定欄位的條件查詢，
select * from test where id =1;  #查詢數字列無須加引號，但是看起來是數字，實際屬于字符列就需要加引號，
select * from test where name='oldgirl';  #查詢字串務必要加單引號，

3、同時查詢多個條件，則取交集，
select * from test where id=2 and name='oldgirl';  #多個條件，

4、指定固定條件范圍查詢資料，
select id,name from test where id>2 and id<5;  #多個條件，and取交集，
select id,name from test where id>3 or id<2;  #多個條件，or取并集，

5、其他查詢功能，
select id,name from test where id>3 order by id asc;  #正向排序
select id,name from test where id<3 order by id desc;  #反向排序

4.3.修改表中的資料

select * from test;
update test set name='xiaoting' where id=3;  #等號可以改為其他任意符號或增加多條件，
select * from test where id=3;

4.4.洗掉表中的資料

use oldboy;
delete from test where id=1;  #洗掉id為1的行
delete from test where name='oldboy';  #洗掉name等于oldboy的行
select * from test;

alter table test add state tinyint(2) not null default 1;  #通過狀態來判斷頁面內容顯示
desc test;
select * from test where state=1;
update test set state=0 where name='oldgirl';  #通過update實作邏輯洗掉
select * from test where state=1;

4.5.清空表中的資料

select * from test;
truncate table test;  #執行清空命令
select * from test;

truncate和delete的區別：

1、truncate與不帶where子句的delete陳述句功能相同：兩者均洗掉表中的全部行，但truncate比delete速度快，
2、truncate通過釋放存盤表資料所用的資料頁來洗掉資料，并且只在事務日志中記錄頁的釋放，因此使用的系統和事務日志資源更少，
3、delete陳述句每次洗掉一行，便會在事務日志中為所洗掉的每一行記錄一項，

標籤：Linux

上一篇：The user limit for number of open files is too small.

下一篇：虛擬機Centos【橋接模式】下固定ip