MYSQL-數據庫-DDL-DML-DQL-DCL-基礎學習

MySql概念：

? ? ? ? 建立在關系模型基礎上，有多張相互連接的二維表組成的數據庫

SQL通用語法：

? ? ? ? 1.SQL語句可以單行或多行書寫，以分號結尾

? ? ? ? 2.SQL語句可以使用空格/縮進來增強語句的可讀性

? ? ? ? 3.MySQL數據庫的SQL語句不區分大小寫，關鍵字建議使用大寫

? ? ? ? 4.注釋

????????單行注釋：-- 注釋內容，或#注釋內容(mysql特有)

? ? ? ? 多行注釋：/*注釋內容*/

mysql定義字符集

設置表字符集
alter table 表名 default character set utf8;修改需要插入中文字段的屬性
alter table 表名 change 列名 列名 varchar(255) character set utf8;修改數據庫字符集
編輯配置文件my.cnf
client下
default-character-set=utf8
mysqld下
character-set-server=utf8
重啟mysql
show variables like '%character%'

SQL分類

DDL數據庫定義語言，用來定義數據庫對象(數據庫，表，字段)DML數據庫操作語言，用來定義對數據庫表中的數據進行增刪改DQL數據庫查詢語言，用來查詢數據庫中表的記錄DCL數據庫控制語言，用來創建數據庫用戶，控制數據庫的訪問權限

DDL-數據庫操作

? ? ? ?查詢所有數據庫

SHOW DATABASES;

查詢當前數據庫

SELECT DATABASE();

創建數據庫

CREATE DATABASE [IF NOT EXISTS]數據庫名 [DEFAULT CHARSET字符集] [COLLATE排序規則];IF NOT EXISTS：數據庫不存在的情況下
DEFAULT CHARSET：字符集，可以省略，默認是UTF-8
COLLATE：排序規則
方括號中的東西都是可選的
CREATE DATABASE 表名 CHARACTER SET utf8;

刪除數據庫

DROP DATABASE 數據庫名

使用

USE [if exists]數據庫名
方括號表示如果存在，則刪除

DDL-表操作-查詢

查詢當前數據庫所有表

SHOW TABLES;

查詢表結構

DESC 表名；

查詢指定表的建表語句

SHOW CREATE TABLE 表名；

DDL-表操作-創建

創建表

mysql> create table test(-> id int comment '編號',-> name varchar(50) comment '姓名',-> age  int comment '年齡',-> xingbie varchar(1) comment '性別'-> ) comment 'user表';
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+---------------+
| Tables_in_zhu |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)mysql>
這里的test是表名，id，name，age，xingbie是列數，int和varchar是類型，varchar(1)是長度，comment是注釋
圓括號外的comment是注釋，說明是一個表

DDL表操作-數據類型

數值類型

類型            大小        有符號SIGNED范圍        無符號UNSIGNED范圍        描述
TINYINT        1byte        -128，127                0，255                小數數值
SMALLINT       2byte        -32768，32767           0，65536               大整數值
MEDIUMINT      3byte        -8388608，8388607       0，16777215            大整數值
INT或INTEGER   4byte        -2147483648，2147483647 0，4294967295          大整數值
BIGINT         8byte        -2^63,2^63-1            0,2^64-1              極大整數1
FLOAT          4byte                                                       單精度浮點值
DOUBLE         8byte                                                         雙精度浮點值
DECTMAL                                                                   小數值

字符串類型

類型                大小                    描述
CHAR                0-255bytes            定長字符串
VARCHAR             0-65535bytes          變長字符串
TINYBLOB            0-255bytes            不超過255個字符的二進制數據
TINYTEXT            0-255bytes            短文本字符串
BLOB                0-65 535bytes         長文本數據
MEDIUMBLOB          0-16 777 215 bytes    二進制形式中等長度文本數據
MEDIUMTEXT          0-16 777 215 bytes    中等長度文本數據
LONGBLOB            0-4 294 967 295bytes  二進制形式的極大文本數據
LONGTEXT            0-4 294 967 295bytes  極大文本數據

日期類型

類型        大小       范圍                                    格式            描述
DATE        3        1000-01-01--9999-12-31                YYYY-MM-DD        日期值
TIME        3        -838:59:59--838:59:59                 HH:MM:SS          時間值持續時間
YEAR        1        1901--2155                            YYYY              年份值
DATETIME    8        1000-01-01 00:00:00--9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期時間
TIMESTAMP   4         1970-01-01 00:00:01--2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和時間值，時間戳

DDL-表操作-修改

1.添加字段

ALTTER TABLE 表名 ADD 字段名 類型(長度) [COMMENT 注釋] [約束];

2.修改數據類型

ALTER TABLE 表名 MODIFY 字段名 新數據類型(長度);修改字段名和字段類型
ALTER TABLE 表名 CHANGE 舊的字段名  新字段名 新類型(長度) [COMMENT 注釋][約束];

3.刪除字段

ALTER TABLE 表名 DROP 字段名

4.修改表名

ALTER TABLE 表名 RENAME TO 新表名;

5.刪除表

DROP TABLE [if exists]表名;  如果存在
TRUNCATE TABLE 表名;    //刪除指定表，并且重新創建

DML介紹：

? ? ? ? DML用來對數據庫中表的記錄進行增刪改操作，是數據庫的操作語言

添加數據：INSERT
修改數據：UPDATE
刪除數據：DELETE

DML-添加數據

1.給指定字段添加數據

1.給指定字段添加數據
INSERT INTO 表名(字段名1，字段名2,....) VALUES(值1，值2,...);2.給全部字段添加數據
INSERT INTO 表名 VALUES(值1,值2);3.批量添加數據
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1，值2),(值1，值2);
INSERT INTO 表名 VALUES(值1，值2,...),(值1，值2,...);

注意：

插入數據時，指定的字段順序需要與值的順序是一一對應的
字符串和日期型數據應該包含在引號中
插入的數據大小，應該在字段的規定范圍內

DML-修改數據

UPDATE 表名 SET 字段名1=值1,字段名2=值2,....[WHERE 條件];注意：修改語句的條件可以有，也可以沒有，如果沒有條件，則會修改整張表的所有數據

例子：

修改id為1的name值為'你好'
update newtable set name='你好' where id=1;

DML-刪除數據

DELETE FROM 表名 [WHERE 條件];

例子：

刪除newxingbie為女的所有數據

刪除所有數據

DELETE from newtable;

DQL-介紹

DQL數據庫查詢語言，用來查詢數據庫中表的記錄

查詢語句：SELET

DQL-語法：

SELECT字段列表
FROM表名列表
WHERE條件列表
GROUP BY分組字段列表
HAVING分組后條件列表
ORDER BY排序字段列表
LIMIT分頁參數

查詢分類

基本查詢條件查詢(WHERE)聚合函數(count，max，avg，sum)分組查詢(GROUP BY)排序查詢(ORDER BY)分頁查詢(LIMIT)

DQL-基本查詢

1.查詢多個字段

SELECT 字段1，字段2，字段3... FROM 表名;
SELECT * FROM 表名;

2.設置別名

SELECT 字段1 [AS 別名],[AS 別名2],....FROM 表名;

例如起字段名為工作地址

具有一次性，單一性，第二次查詢不用as則為原來的樣子

如下，沒有as也是同樣效果，所以as可以省略

3.去除重復記錄? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

SELECT DISTINCT 字段列表 FROM 表名;

例如

DML-條件查詢

1.語法：

SELECT 字段列表 FROM 表名 WHERE 條件列表;

2.條件

比較運算符                功能
>                        大于
>=                       大于等于
<                        小于           
<=                       小于等于
=                        等于
<>或!=                   不等于
BETWEEN...AND...         在某個范圍之內(含量小，最大值)
IN(...)                  在in之后的列表中的值，多選一
LIKE 占位符               模糊匹配(...匹配單個字符，%匹配任意個字符)
IS NULL                  是NULL########################################################################
邏輯運算符                    功能
AND或&&                      并且(多個條件同時成立)
OR或||                       或者(多個條件任意一個成立)
NOT或！                      非，不是

is null的使用

查詢有身份證號的員工信息
select * from yg where idcard id is not null;查詢沒有身份證號的員工信息
select * from yg where idcard id is null

例子

年齡不等于88的員工

年齡在15歲(包含)到20歲(包含)之間的員工信息

還有一個是between...and在某個范圍之內的來匹配

性別為女，年齡小于25

or語句使用-查詢年齡的等于18或20或40的員工信息

in是滿足括號中一個條件即可

篩選名字為兩個字的員工like使用

select * from yg where name like '__';

查身份證號最后一位是5的員工like語句

select * from yg where idcard like '%5';
%5代表最后一個字符是5
或者
select * from yg where idcard like '_________________5';
和上一個一樣

DQL-聚合函數

1.常見聚合函數

函數        功能
count       統計數量
max         最大值
min         最小值
avg         平均值
sum         求和

語法：

SELECT 聚合函數(字段列表) FROM 表名;

注意：null值不參與聚合運算

1.統計該企業員工數量

2.統計該企業員工的平均年齡

3.統計企業員工最大年齡和最小年齡

4.統計西安地區員工的年齡之和

DQL-分組查詢

1.語法

SELECT 字段列表 FROM 表名 [WHERE 條件] GROUP BY 分組字段名 [HAVING 分組后過濾條件]

2.where和having區別

執行時機不同：where是分組之前進行過濾，不滿足where條件，而having是分組之后對結果進行過濾
判斷條件不同：where不能對聚合函數進行判斷，而having可以

3.根據性別分組，統計男員工和女員工的數量

4.根據性別分組，統計男員工和女員工的平均年齡

5.查詢年齡小于45的員工，并根據工作地址分組，獲取員工數量大于2的工作地址

起別名查詢

DQL-排序查詢

1.語法：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式,字段2 排序方式2;

2.排序方式

ASC：升序（默認值）
DESC：降序

3.根據年齡對公司的員工進行升序排序

SELECT * FROM yg ORDER BY age asc;

或

4.根據入職時間，對員工進行降序排序

SELECT * FROM yg ORDER BY ENDRYDATE DESC;

5.根據年齡對公司的員工盡心升序排序，年齡相同，則按照入職時間降序排序

DQL-分頁查詢

1.語法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查詢記錄數;

起始索引從0開始，起始索引=（查詢頁碼-1）*每頁顯示記錄數
分頁查詢是數據庫的方言，不同的數據庫有不同的實現，mysql中是LIMIT
如果查詢的是第一頁數據，起始索引可以省略，直接簡寫為limit 10

2.查詢第一頁數據，顯示10條記錄

起始為：（1-1）*10

或者

2.查詢第2頁員工數據，每頁顯示10條記錄

查看第1行數據

DQL-案例練習

1.查詢年齡為20,21,22,23歲的女性員工信息

2.查詢性別為男，并且年齡在20到40歲以內的姓名為三個字的員工

3.查詢員工表中，年齡小于60的，男性員工和女性員工的人數

4.查詢所有年齡小于等于35歲員工的姓名和年齡，并對查詢結果按年齡升序排序，如果年齡相同按入職時間降序排序

5.查詢性別為男，且年齡在20-40歲以內的前5個員工信息，對查詢的結果俺年齡升序排序，年齡相同按入職時間排序

DQL-執行順序

from     表名列表
where    條件列表
group by 分組字段列表
having   分組后條件列表
select   字段列表
order by 排序字段列表
limit    分頁參數

DCL數據庫控制語言，用來管理數據庫用戶，控制數據庫的訪問權限

DCL管理用戶

1.查詢用戶

use  mysql;
select * from user;

2.創建用戶

create user '用戶名'@'主機名' identifled by '密碼';

3.修改用戶密碼

alter user '用戶名'@'主機名' identified with mysql_native_password by '新密碼';

4.刪除用戶

drop user'用戶名'@'主機名';

DCL-權限控制

mysql中定義了很多種權限，但是常用的就以下幾種

權限                                    說明
ALL,ALL PRIVLEGES                      所有權限
SELECT                                 查詢數據
INSERT                                 插入數據
UPDATE                                 修改數據
DELETE                                 刪除數據
ALTER                                  修改表
DROP                                   刪除數據庫/表/視圖
CREATE                                 創建數據庫/表

?1.查詢權限

SHOW GRANTS FOR '用戶名'@'主機名';

例子

賦給root@%所有的數據庫和表的權限之后如下

2.授予權限

GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';

授予me用戶對于sjk這個數據庫的所有權限

授予root@%所有權限對于所有數據庫和表

3.撤銷權限

REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';

撤銷root@%的所有權限