MySQL概述

MySQL啟動

net start mysql80
net stopmysql80

MySQL客戶端連接

需配置環(huán)境變量
增加路徑 C:\Program Files\MySQL\MySQL Server 8.0\bin

mysql [-h 127.0.0.1] [-p 3306] -u root -p
mysql -u root -p

圖形界面操作

創(chuàng)建數(shù)據(jù)庫
右鍵‘@localhost’-->new-->scheam

創(chuàng)建表結(jié)構(gòu)
右鍵表-->new-->table

修改表
右鍵表-->new-->modify table

用sql語句操作
右鍵表/數(shù)據(jù)庫-->new-->query console

SQL

DDL語法

數(shù)據(jù)庫操作

# 查詢所有數(shù)據(jù)庫
show databases;

# 創(chuàng)建數(shù)據(jù)庫
create database itcsat;

# 創(chuàng)建數(shù)據(jù)庫并指定類型
create database itheima default charset utf8mb4;

# 添加if not exists避免報錯（如果已有相同名稱數(shù)據(jù)庫會報錯）
create database if not exists test;

# 刪除數(shù)據(jù)庫
drop database if exists test

# 添加if exists避免報錯（如果沒有該名稱數(shù)據(jù)庫會報錯）
drop database if exists test

# 使用itcast數(shù)據(jù)庫
use itcast;

# 查看當(dāng)前的數(shù)據(jù)庫
select database();

表操作

# 創(chuàng)建新表tb_user
create table tb_user(
    id int comment '編號',
    name varchar(50) comment '姓名',
    age int comment '年齡',
    gender varchar(1) comment '性別'
) comment '用戶表';

# 創(chuàng)建新表emp
create table emp(
    id int comment '編號',
    workno varchar(10) comment '工號',
    name varchar(10) comment '姓名',
    gender char(1) comment '性別',
    age tinyint unsigned comment '年齡',
    idcard char(18) comment '身份證號',
    entrydate date comment '入職時間'
) comment '員工表';

# 查看表信息（簡略）
desc tb_user;

# 查看表信息（詳細(xì)）
show create table tb_user;

# 刪除表
drop table [ if exists ] 表名;

# 刪除指定表并重新創(chuàng)建該表（原來的數(shù)據(jù)都沒了芬为，表結(jié)構(gòu)還在）
truncate table 表名;

表字段操作

# 添加字段nickname
alter table emp add nickname varchar(20) comment '昵稱';

# 修改字段的數(shù)據(jù)類型
alter table 表名 modify 字段名 新數(shù)據(jù)類型 (長度);

# 修改字段名和其數(shù)據(jù)類型（舊：nickname，新username）
alter table emp change nickname username varchar(30) comment '昵稱';

# 刪除字段（刪除emp表的username字段）
alter table emp username;

# 修改表名
alter table emp rename to employee;

DML語法

對數(shù)據(jù)庫中表中的數(shù)據(jù)操作
添加數(shù)據(jù)

# 給指定字段添加數(shù)據(jù)
insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);

# 查看表
select * from employee;

# 給全部字段添加數(shù)據(jù)
insert into 表名 (值1, 值2, ...)

# 批量添加數(shù)據(jù)歧寺，用逗號連接多個值
insert into employee values(3,'3','韋一笑','男',38,'123456789012345670','2005-01-01'),(4,'4','趙敏','女',18,'123456789012345670','2005-01-01');

修改數(shù)據(jù)

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 條件 ] ;

# 修改id為1的數(shù)據(jù)呐芥，將name修改為itheima
update employee set name = 'itheima' where id = 1;

# 修改id為1的數(shù)據(jù), 將name修改為小昭, gender修改為 女（逗號連接）
update employee set name = '小昭' , gender = '女' where id = 1;

# 將所有的員工入職日期修改為 2008-01-01
update employee set entrydate = '2008-01-01';

刪除數(shù)據(jù)

delete from 表名 [ where 條件 ] ;

# 刪除gender為女的員工
delete from employee  where gender = '女';

# 刪除所有員工
delete from employee;

DQL語法

查詢數(shù)據(jù)庫中表的記錄

# 查詢多個字段
select 字段1, 字段2, 字段3 ... from 表名 ;

# 查詢所有字段（）不推薦
select * from 表名 ;

# 查詢字段時可以給字段設(shè)置別名（省去as也行）
select 字段1 [ AS 別名1 ] , 字段2 [ AS 別名2 ] ... from 表名;
select 字段1 [ 別名1 ] , 字段2 [ 別名2 ] ... from 表名;

# 去除重復(fù)元素
select distinct 字段列表 from 表名;

# 例子
select name,workno,age from emp;
select workaddress as '工作地址' from emp;
select workaddress '工作地址' from emp;
select distinct  workaddress '工作地址' from emp;

條件查詢

比較運算符

• between ... and ... 在某個范圍之內(nèi)(含最小伍伤、最大值)
• in(...) 在in之后的列表中的值蓝纲，多選一
• like 占位符 模糊匹配(_匹配單個字符, %匹配任意個字符)

邏輯運算符

• AND 或 && 且
• OR 或 || 或
• NOT 或 ! 非

# 查詢年齡小于等于 20 的員工信息
select * from emp where age <= 20;

# 查詢沒有身份證號的員工信息(null的)
select * from emp where idcard is null;

# 查詢有身份證號的員工信息(非null的)
select * from emp where idcard is not null;

# 查詢年齡不等于 88 的員工信息
select * from emp where age != 88;
select * from emp where age <> 88;

# 查詢年齡在15歲(包含) 到 20歲(包含)之間的員工信息
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;

# 查詢性別為 女 且年齡小于 25歲的員工信息
select * from emp where gender = '女' and age < 25;

# 查詢年齡等于18 或 20 或 40 的員工信息
select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

# 查詢姓名為兩個字的員工信息(兩個下劃線)
select * from emp where name like '__';

# 查詢身份證號最后一位是X的員工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

聚合函數(shù)

• count 統(tǒng)計數(shù)量
• max
• min
• avg 平均值
• sum

# 統(tǒng)計該企業(yè)員工數(shù)量
select count(*) from emp;
select count(idcard) from emp; -- 統(tǒng)計的是idcard字段不為null的記錄數(shù)

# 統(tǒng)計該企業(yè)員工的最大年齡
select max(age) from emp;

# 統(tǒng)計西安地區(qū)員工的年齡之和
select sum(age) from emp where workaddress = '西安';

分組查詢

where與having區(qū)別

• 執(zhí)行時機(jī)不同：where是分組之前進(jìn)行過濾，不滿足where條件喂击，不參與分組；而having是分組之后對結(jié)果進(jìn)行過濾淤翔。
• 判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷翰绊，而having可以。

執(zhí)行順序: where > 聚合函數(shù) > having 旁壮。

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

# 根據(jù)性別分組 , 統(tǒng)計男性員工 和 女性員工的數(shù)量
select count(*) from emp group by gender; -- 只要count結(jié)果辞做，沒有對應(yīng)男女

# 加上性別字段，能對應(yīng)上count結(jié)果 （count(*) from emp group by gender類似于一個字段）
select gender, count(*) from emp group by gender;

# 根據(jù)性別分組 , 統(tǒng)計男性員工 和 女性員工的平均年齡
select  gender, avg(age) from emp group by gender;

# 查詢年齡小于45的員工 , 并根據(jù)工作地址分組 , 獲取員工數(shù)量大于等于3的工作地址
select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) > 3;
-- 取別名
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count > 3;

# 統(tǒng)計各個工作地址上班的男性及女性員工的數(shù)量
select workaddress, gender, count(*) people_count from emp group by workaddress, gender;

排序查詢

排序方式

• ASC : 升序(默認(rèn)值)
• DESC: 降序

select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2 ;

# 根據(jù)年齡對公司的員工進(jìn)行升序排序
select * from emp order by  age asc;

# 根據(jù)入職時間, 對員工進(jìn)行降序排序
select * from emp order by  entrydate desc;

# 根據(jù)年齡對公司的員工進(jìn)行升序排序 , 年齡相同 , 再按照入職時間進(jìn)行降序排序
select * from emp order by age asc, entrydate desc;

分頁查詢

• 起始索引從0開始寡具，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數(shù)。
• 分頁查詢是數(shù)據(jù)庫的方言稚补，不同的數(shù)據(jù)庫有不同的實現(xiàn)童叠，MySQL中是LIMIT。
• 如果查詢的是第一頁數(shù)據(jù)，起始索引可以省略厦坛，直接簡寫為 limit 10五垮。

select 字段列表 from 表名 limit 起始索引, 查詢記錄數(shù) ;

# 查詢第1頁員工數(shù)據(jù), 每頁展示10條記錄
select * from emp limit 0,10;
select * from emp limit 10;

# 查詢第2頁員工數(shù)據(jù), 每頁展示10條記錄 --------> (頁碼-1)*頁展示記錄數(shù)
select * from emp limit 10,10;

執(zhí)行順序

編寫順序:
select-->from-->where-->group by-->having-->order by-->limit
執(zhí)行順序：
from-->where-->group by-->having-->select-->order by-->limit

DCL語法

管理數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的訪問權(quán)限

用戶管理操作

查詢用戶

use mysql;
select * from mysql.user;
-- Host代表當(dāng)前用戶訪問的主機(jī),如果為localhost,僅代表只能夠在當(dāng)前本機(jī)訪問,是不可以遠(yuǎn)程訪問的杜秸。User代表的是訪問該數(shù)據(jù)庫的用戶名放仗。在MySQL中需要通過Host和User來唯一標(biāo)識一個用戶.

創(chuàng)建用戶

CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';
-- 創(chuàng)建用戶itcast, 只能夠在當(dāng)前主機(jī)localhost訪問, 密碼123456;
create user 'itcast'@'localhost' identified by '123456';
-- 創(chuàng)建用戶heima, 可以在任意主機(jī)訪問該數(shù)據(jù)庫, 密碼123456;
create  user 'heima'@'%' identified by '123456';

修改用戶密碼

ALTER USER '用戶名'@'主機(jī)名' IDENTIFIED WITH mysql_native_password BY '新密碼' ;
-- 修改用戶heima的訪問密碼為1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

刪除用戶

DROP USER '用戶名'@'主機(jī)名' ;
-- 刪除itcast@localhost用戶
drop user 'itcast'@'localhost' ;

權(quán)限控制

常用權(quán)限

• ALL, ALL PRIVILEGES(所有權(quán)限)
• SELECT(查詢數(shù)據(jù))
• INSERT(插入數(shù)據(jù))
• UPDATE(修改數(shù)據(jù))
• DELETE(刪除數(shù)據(jù))
• ALTER(修改表)
• DROP(刪除數(shù)據(jù)庫/表/視圖)
• CREATE(創(chuàng)建數(shù)據(jù)庫/表)

多個權(quán)限之間,使用逗號分隔;授權(quán)時，數(shù)據(jù)庫名和表名可以使用 * 進(jìn)行通配撬碟，代表所有诞挨。

查詢權(quán)限

SHOW GRANTS FOR '用戶名'@'主機(jī)名' ;

# 查詢 'heima'@'%' 用戶的權(quán)限
show grants for 'heima'@'%';

授予權(quán)限

GRANT 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'主機(jī)名';

# 授予 'heima'@'%' 用戶itcast數(shù)據(jù)庫所有表的所有操作權(quán)限
grant all on itcast.* to 'heima'@'%';

撤銷權(quán)限

REVOKE 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 FROM '用戶名'@'主機(jī)名';

# 撤銷 'heima'@'%' 用戶的itcast數(shù)據(jù)庫的所有權(quán)限
revoke all on itcast.* from 'heima'@'%';