SQL語言基礎(chǔ)

SQL全稱： Structured Query Language队秩，是結(jié)構(gòu)化查詢語言褪猛，用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)的計(jì)算機(jī)語言材原。
SQL的特點(diǎn)：
    具有綜合統(tǒng)一性赢底，不同數(shù)據(jù)庫的支持的SQL稍有不同
    非過程化語言（不需要關(guān)心實(shí)現(xiàn)原理专筷，按照需求寫代碼即可）
    語言簡(jiǎn)捷，用戶容易接受
    以一種語法結(jié)構(gòu)提供兩種使用方式
SQL語言的組成：
    數(shù)據(jù)定義語言-DDL
    數(shù)據(jù)操縱語言-DML
    數(shù)據(jù)控制語言-DCL
    數(shù)據(jù)查詢語言-DQL
SQL的特點(diǎn)：
    SQL 對(duì)關(guān)鍵字的大小寫不敏感
    SQL語句可以以單行或者多行書寫浴栽，以分行結(jié)束
    SQL的注釋：
    -- 這是單行注釋  --后需要加空格
    #這也是單行注釋  --不需要加空格
    /*
    這是多行注釋
    這是多行注釋
    */
    
數(shù)據(jù)庫（DB）是一個(gè)以某種組織方式存儲(chǔ)在磁盤上的數(shù)據(jù)的集合肚吏。
數(shù)據(jù)庫應(yīng)用系統(tǒng)是指基于數(shù)據(jù)庫的應(yīng)用軟件
數(shù)據(jù)庫管理系統(tǒng)分類：
    關(guān)系型數(shù)據(jù)庫（RDBMS）（表之間存在對(duì)應(yīng)關(guān)系）
             1、Oracle數(shù)據(jù)庫 （老大欲险，最掙錢的數(shù)據(jù)庫）
             2镐依、MySQL數(shù)據(jù)庫 （最流行中型數(shù)據(jù)庫））
             3、SQL server數(shù)據(jù)庫 （Windows上最好的數(shù)據(jù)庫）
             4天试、PostgreSQL（功能最強(qiáng)大的開源數(shù)據(jù)庫）
             5槐壳、SQLite（最流行的嵌入式數(shù)據(jù)庫）
    非關(guān)系型數(shù)據(jù)庫(NoSQL)
                1、Redis（最好的緩存數(shù)據(jù)庫）
                2喜每、MongoDB（最好的文檔型數(shù)據(jù)庫）
                3务唐、Elasticsearch（最好的搜索服務(wù)）
                4、Cassandra（最好的列式數(shù)據(jù)庫）
                5带兜、HBase（優(yōu)秀的分布式枫笛、列式數(shù)據(jù)庫）
SQL和數(shù)據(jù)庫管理系統(tǒng)的關(guān)系：
    SQL是一種用于操作數(shù)據(jù)庫的語言，SQL適用于所有關(guān)系型數(shù)據(jù)庫刚照。
    MySQL刑巧、Oracle、SQLServer是一個(gè)數(shù)據(jù)庫軟件无畔，這些數(shù)據(jù)庫軟件支持標(biāo)準(zhǔn)SQL啊楚，也就是通過SQL可以使用這些軟件，
    不過每一個(gè)數(shù)據(jù)庫系統(tǒng)會(huì)在標(biāo)準(zhǔn)SQL的基礎(chǔ)上擴(kuò)展自己的SQL語法浑彰。
    大部分的NoSQL數(shù)據(jù)庫有自己的操作語言恭理，對(duì)SQL支持的并不好。
MySQL的介紹：
    MySQL數(shù)據(jù)庫管理系統(tǒng)由瑞典的DataKonsultAB公司研發(fā)郭变，該公司被Sun公司收購颜价，現(xiàn)在Sun公司又被Oracle公司收購涯保，因此MySQL目前屬于 Oracle 旗下產(chǎn)品。
    MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言周伦。
    MySQL 軟件采用了雙授權(quán)政策夕春，分為社區(qū)版和商業(yè)版，由于其體積小横辆、速度快撇他、總體擁有成本低，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫狈蚤。
MySQL的特點(diǎn)：
    ①M(fèi)ySQL數(shù)據(jù)庫是用C和C++語言編寫的困肩，以保證源碼的可移植性
    ②支持多個(gè)操作系統(tǒng)例如：Windows、Linux脆侮、Mac OS等等
    ③支持多線程锌畸，可以充分的利用CPU資源
    ④為多種編程語言提供API，包括C語言靖避，Java潭枣，PHP。Python語言等
    ⑤MySQL優(yōu)化了SQL算法幻捏，有效的提高了查詢速度
    ⑥MySQL開放源代碼且無版權(quán)制約盆犁，自主性強(qiáng)、使用成本低篡九。
    ⑧MySQL歷史悠久谐岁、社區(qū)及用戶非常活躍榛臼，遇到問題伊佃，可以很快獲取到幫助。
MySQL的版本
    MySQL的命名機(jī)制使用由3個(gè)數(shù)字和一個(gè)后綴組成的版本號(hào)沛善。例如航揉，像mysql-8.0.26的版本號(hào)這樣解釋：、
    第1個(gè)數(shù)字(8)是主版本號(hào)金刁，描述了文件格式帅涂。所有版本的發(fā)行都有相同的文件格式。
    第2個(gè)數(shù)字(0)是發(fā)行級(jí)別尤蛮。主版本號(hào)和發(fā)行級(jí)別組合到一起便構(gòu)成了發(fā)行序列號(hào)漠秋。
    第3個(gè)數(shù)字(26)是在此發(fā)行系列的版本號(hào)，隨每個(gè)新分發(fā)版遞增抵屿。
    目前，My SQL的最新版本為MySQL 8.0捅位。
----------------------------------------------------------------------------------------------------------------------------------------------
1.啟動(dòng)mysql服務(wù)
net start mysql

2.修改root用戶密碼
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

3.創(chuàng)建用戶
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

4.登錄mysql
mysql -u root -p  root -- 密碼

5.查看數(shù)據(jù)庫版本8.0.19
select version(); 

SQL語言的組成:
    數(shù)據(jù)定義語言-DDL
    數(shù)據(jù)操縱語言-DML
    數(shù)據(jù)控制語言-DCL
    數(shù)據(jù)查詢語言-DQL

數(shù)據(jù)定義語言-DDL：對(duì)數(shù)據(jù)庫的常用操作轧葛、對(duì)表結(jié)構(gòu)的常用操作搂抒、修改表結(jié)構(gòu)  
     
6.查看所有數(shù)據(jù)庫
show databases;

7.創(chuàng)建數(shù)據(jù)庫
create database [if not exists] mydb1 [charset=utf8];
CREATE DATABASE hellodatabase;

8.切換數(shù)據(jù)庫(選擇要操作的)
use  mydb1;

9.刪除數(shù)據(jù)庫
drop database if exists mydb1;

10.修改數(shù)據(jù)庫編碼
alter database mydb1 character set utf8;

----------------------------------------------------------------------------------------------------------------------------------------------
數(shù)據(jù)類型
數(shù)據(jù)類型是指在創(chuàng)建表的時(shí)候?yàn)楸碇凶侄沃付〝?shù)據(jù)類型，只有數(shù)據(jù)符合類型要求才能存儲(chǔ)起來尿扯，使用數(shù)據(jù)類型
的原則是:夠用就行求晶，盡量使用取值范圍小的，而不用大的衷笋，這樣可以更多的節(jié)省存儲(chǔ)空間芳杏。
1.數(shù)值類型
類型              大小  范圍（有符號(hào)）     范圍（無符號(hào)）  用途
TINYINT         1 byte (-128，127    )       (0辟宗，255)         小整數(shù)值
INT或INTEGER 4 bytes                                                             整數(shù)值
FLOAT               4 bytes                                                             單精度 浮點(diǎn)數(shù)值
DOUBLE          8 bytes                                                             雙精度 浮點(diǎn)數(shù)值
DECIMAL         DECIMAL (有效位數(shù)爵赵，小數(shù)位數(shù))                     小數(shù)值
2.字符串類型
類型      大小                      用途
CHAR        0-255 bytes         定長(zhǎng)字符串
VARCHAR 0-65535 bytes   變長(zhǎng)字符串
3.日期類型
類型              格式                              用途
DATE            YYYY-MM-DD                  日期值
TIME                HH:MM:SS                        時(shí)間值或持續(xù)時(shí)間
YEAR                YYYY                                年份值
DATETIME    YYYY-MM-DD HH:MM:SS 混合日期和時(shí)間值
TIMESTAMP   YYYYMMDD HHMMSS         混合日期和時(shí)間值，時(shí)間戳
----------------------------------------------------------------------------------------------------------------------------------------------

11.創(chuàng)建表
-- 創(chuàng)建表是構(gòu)建一張空表泊脐，指定這個(gè)表的名字空幻，這個(gè)表有幾列，每一列叫什么名字容客，以及每一列存儲(chǔ)的數(shù)據(jù)類型秕铛。
create table [if not exists]表名(  
    字段名1 類型[(寬度)] [約束條件] [comment '字段說明'],  
    字段名2 類型[(寬度)] [約束條件] [comment '字段說明'],  
    字段名3 類型[(寬度)] [約束條件] [comment '字段說明']  
)[表的一些設(shè)置];  

create  table if not exists student(
    sid int,
    name varchar(20),
    gender varchar(2),
    age int,
    birth date, 
    address varchar(20),
    score double
);

12.查看當(dāng)前數(shù)據(jù)庫的所有表名稱
show tables;

13.查看指定某個(gè)表的創(chuàng)建語句
show create table student;
--------------------------------------------------------------
CREATE TABLE hellodatabase.`student` (
  `sid` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` varchar(2) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  `score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
---------------------------------------------------------------

14.查看表結(jié)構(gòu)
desc student;

15.刪除表
drop table student;

16.修改表結(jié)構(gòu)
16.1為student表添加一個(gè)新的字段為：系別 dept 類型為 varchar(20)
ALTER TABLE student ADD `dept` VARCHAR(20); 

16.2為student表的dept字段更換為department varchar(30)
ALTER TABLE student change `dept` department VARCHAR(30); 

16.3刪除student表中department這列
ALTER TABLE student DROP department;

16.4將表student改名成 stu
rename table student to stu;
----------------------------------------------------------------------------------------------------------------------------------------------

DML是指數(shù)據(jù)操作語言，英文全稱是Data Manipulation Language缩挑，用來對(duì)數(shù)據(jù)庫中表的數(shù)據(jù)記錄進(jìn)行更新但两。
關(guān)鍵字：插入insert 刪除delete更新update
-------------------------------------------
17.數(shù)據(jù)插入
方式1：指定插入列
insert into student(sid,name,gender,age,birth,address,score)
             values(1001,'張三','男',18,'1996-12-23','北京',83.5);
方式2：效率高！
insert into student values(1002,'李四','男',18,'1996-12-23','上海',83.5),
                                        (1003,'王五','男',18,'1996-12-23','深圳',83.5),
                                        (1004,'趙六','男',18,'1996-12-23','杭州',83.5);
-------------------------------------------
18.數(shù)據(jù)修改
18.1將所有學(xué)生的地址修改為重慶 
update student set address = '重慶';

18.2將id為1004的學(xué)生的地址修改為北京 
update student set address = '北京' where sid = 1004 ;

18.3將id為1005的學(xué)生的地址修改為北京供置，成績(jī)修成績(jī)修改為100 
update student set address = '廣州',score=100 where sid = 1005;

18.4將id為1001的學(xué)生的成績(jī)修成績(jī)加10 
update student set score = score+10 where sid = 1001;
-------------------------------------------
19.數(shù)據(jù)刪除
delete只刪除內(nèi)容谨湘，而truncate類似于drop table，可以理解為是將整個(gè)表刪除士袄，然后再創(chuàng)建該表啸如；

19.1刪除sid為1004的學(xué)生數(shù)據(jù)
delete from student where sid  = 1004;

19.2刪除表所有數(shù)據(jù)
delete from student; 

19.3清空表數(shù)據(jù)
truncate table student;
或
truncate student;
---------------------------------------------------------------------
練習(xí)
20. 查看當(dāng)前正在使用的數(shù)據(jù)庫
select database(); 

創(chuàng)建員工表employee，字段如下：
id（員工編號(hào)）窒朋，name（員工名字）粮坞，gender（員工性別），salary（員工薪資）
CREATE TABLE employee
(
    id INT,
    NAME VARCHAR(20),
    sex VARCHAR(10),
    salary FLOAT    
);
插入數(shù)據(jù)
INSERT INTO employee VALUES(1,"zhangsan","male",2000);
INSERT INTO employee VALUES(2,"lisi","male",1000),(3,"xiaohong","female",4000);
修改數(shù)據(jù)
將所有員工薪水修改為5000元赤拒。
將姓名為’zhangsan’的員工薪水修改為3000元秫筏。
將姓名為’lisi’的員工薪水修改為4000元,sex改為female。
將xiaohong的薪水在原有基礎(chǔ)上增加1000元挎挖。

----------------------------------------------------------------------------------------------------------------------------------------------
MySQL約束 constraint
約束作用：
    表在設(shè)計(jì)的時(shí)候加入約束的目的就是為了保證表中的記錄完整性和有效性这敬，
    比如用戶表有些列的值（手機(jī)號(hào)）不能為空，有些列的值身份證號(hào)）不能重復(fù)蕉朵。
分類：
    主鍵約束(primary key) PK
    自增長(zhǎng)約束(auto_increment)
    非空約束(not null)
    唯一性約束(unique)
    默認(rèn)約束(default)
    零填充約束(zerofill)
    外鍵約束(foreign key) FK
---------------------------------------------------------------------   
MySQL約束-主鍵約束
    MySQL主鍵約束是一個(gè)列或者多個(gè)列的組合崔涂，其值能唯一地標(biāo)識(shí)表中的每一行
    主鍵約束相當(dāng)于（唯一約束 + 非空約束）的組合，主鍵約束列不允許重復(fù)始衅，也不允許出現(xiàn)空值冷蚂。
    每個(gè)表最多只允許一個(gè)主鍵
    當(dāng)創(chuàng)建主鍵的約束時(shí)缭保，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。

21.創(chuàng)建單列主鍵有兩種方式蝙茶，一種是在定義字段的同時(shí)指定主鍵艺骂，一種是定義完字段之后指定主鍵
create table emp1(
    eid int primary key, -- 主鍵
    name VARCHAR(20),
    deptId int,
    salary double
);
方式2
create table emp2(
    eid INT,
    name VARCHAR(20),
    deptId INT,
    salary double,
    constraint  pk1 primary key(eid) -- constraint  pk1可以省略
 );
 
22.查看表的索引
show index from emp2;

添加多列主鍵(聯(lián)合主鍵），主鍵列都不能為空
    所謂的聯(lián)合主鍵隆夯，就是這個(gè)主鍵是由一張表中多個(gè)字段組成的钳恕。
    注意：
         1. 當(dāng)主鍵是由多個(gè)字段組成時(shí)，不能直接在字段名后面聲明主鍵約束蹄衷。
         2. 一張表只能有一個(gè)主鍵忧额，聯(lián)合主鍵也是一個(gè)主鍵
     
23.創(chuàng)建聯(lián)合主鍵
create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  constraint pk1 primary key(name,deptId) -- constraint  pk1可以省略
);

24.添加單列主鍵
create table emp4(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double
);
alter table emp4 add primary key(eid); 

25.添加復(fù)合主鍵
create table emp5(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double
);
alter table emp5 add primary key(eid,name);-- 復(fù)合主鍵

26.刪除單列主鍵 
alter table emp1 drop primary key;
 
27.刪除聯(lián)合主鍵 
alter table emp5 drop primary key;
---------------------------------------------------------------------
自增長(zhǎng)約束
    在MySQL中，當(dāng)主鍵定義為自增長(zhǎng)后宦芦，這個(gè)主鍵的值就不再需要用戶輸入數(shù)據(jù)了宙址，而由數(shù)據(jù)庫系統(tǒng)根據(jù)定義自動(dòng)賦值。
    每增加一條記錄调卑，主鍵會(huì)自動(dòng)以相同的步長(zhǎng)進(jìn)行增長(zhǎng)抡砂。
    默認(rèn)情況下，auto_increment的初始值是 1恬涧，每新增一條記錄注益，字段值自動(dòng)加 1。
    一個(gè)表中只能有一個(gè)字段使用 auto_increment約束溯捆，且該字段必須有唯一索引
    auto_increment約束的字段必須具備 NOT NULL 屬性
    auto_increment約束的字段只能是整數(shù)類型（TINYINT丑搔、SMALLINT、INT提揍、BIGINT 等啤月。
    auto_increment約束字段的最大值受該字段的數(shù)據(jù)類型約束，如果達(dá)到上限劳跃，auto_increment就會(huì)失效谎仲。

create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);

28.指定自增字段初始值
方式1，創(chuàng)建表時(shí)指定
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=100;

方式2刨仑，創(chuàng)建表之后指定
create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
alter table t_user2 auto_increment=100;

注意 delete數(shù)據(jù)之后自動(dòng)增長(zhǎng)從斷點(diǎn)開始郑诺；truncate數(shù)據(jù)之后自動(dòng)增長(zhǎng)從默認(rèn)起始值開始

---------------------------------------------------------------------
MySQL 非空約束（not null）指字段的值不能為空。
對(duì)于使用了非空約束的字段杉武，如果用戶在添加數(shù)據(jù)時(shí)沒有指定值辙诞，數(shù)據(jù)庫系統(tǒng)就會(huì)報(bào)錯(cuò)。
29.添加非空約束-方式1轻抱，創(chuàng)建表時(shí)指定
create table t_user6 ( 
  id int , 
  name varchar(20) not null, 
  address varchar(20) not null 
);
添加非空約束-方式2
create table t_user7 ( 
  id int , 
  name varchar(20) , -- 指定非空約束 
  address varchar(20) -- 指定非空約束 
); 
alter table t_user7 modify name varchar(20) not null; 
alter table t_user7 modify address varchar(20) not null;

-- 注意：change可以修改列名稱飞涂，modify則不能
-- 16.2為student表的dept字段更換為department varchar(30)
-- ALTER TABLE student change `dept` department VARCHAR(30); 

30.刪除非空約束
alter table t_user7 modify name varchar(20) ; 
alter table t_user7 modify address varchar(20) ;

---------------------------------------------------------------------
唯一約束（Unique Key）是指所有記錄中字段的值不能重復(fù)出現(xiàn)。
例如，為 id 字段加上唯一性約束后较店，每條記錄的 id 值都是唯一的志鹃，不能出現(xiàn)重復(fù)的情況。

31.添加唯一約束-方式1泽西，創(chuàng)建表時(shí)指定
create table t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique -- 指定唯一約束 
);
添加唯一約束-方式2
create table t_user9 ( 
  id int , 
  name varchar(20) , 
  phone_number varchar(20) -- 指定唯一約束 
); 
alter table t_user9 add constraint unique_ph unique(phone_number);

32.刪除唯一約束
alter table t_user9 drop index unique_ph;

---------------------------------------------------------------------
MySQL 默認(rèn)值約束用來指定某列的默認(rèn)值。
33.添加默認(rèn)約束-方式1
create table t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default '北京' -- 指定默認(rèn)約束 
);
添加默認(rèn)約束-方式2
create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default  '北京';

34.刪除默認(rèn)約束
alter table t_user11 modify column address varchar(20) default null;
或
alter table t_user11 modify  address varchar(20) default null;

show create table t_user11;

---------------------------------------------------------------------
零填充約束(zerofill)
    插入數(shù)據(jù)時(shí)缰趋，當(dāng)該字段的值的長(zhǎng)度小于定義的長(zhǎng)度時(shí)捧杉，會(huì)在該值的前面補(bǔ)上相應(yīng)的0
    zerofill默認(rèn)為int(10)
    當(dāng)使用zerofill 時(shí)，默認(rèn)會(huì)自動(dòng)加unsigned（無符號(hào)）屬性秘血，使用unsigned屬性后味抖，數(shù)值范圍是原值的2倍，例如灰粮，有符號(hào)為-128~+127仔涩，無符號(hào)為0~256。
35.添加零填充約束
create table t_user12 ( 
  id int zerofill , -- 零填充約束
  name varchar(20)   
);
insert t_user12 VALUES(123,"張三");  
select * from t_user12;
-- 0000000123   張三

36.刪除零填充約束
alter table t_user12 modify id int;