SQL
一、SQL
Structured Query Language：結(jié)構(gòu)化查詢語言，縮寫為SQL
其實(shí)就是定義了操作所有關(guān)系型數(shù)據(jù)庫的規(guī)則，標(biāo)準(zhǔn)組織指定的規(guī)則
每一種數(shù)據(jù)庫操作的方式不一樣的地方稱之為'方言'
二、SQL通用語法
SQL語句可以單行或者多行書寫，以分號結(jié)尾
可以使用空格和縮進(jìn)來增強(qiáng)語句的可讀性
MySQL數(shù)據(jù)庫的SQL語句不區(qū)分大小寫，但是關(guān)鍵字建議大寫
注釋方式
-- 單行注釋('--'之后必須加空格)#單行注釋/*多行注釋*/三、SQL的分類
DDL（Data Definition Language）：數(shù)據(jù)定義語言
操作數(shù)據(jù)庫和表
DML（Data Manipulation Language）：數(shù)據(jù)庫操作語言
增刪改表中的數(shù)據(jù)
DQL（Data Query Language）：數(shù)據(jù)查詢語言
查詢表
DCL（Data Control Language）：數(shù)據(jù)庫控制語言
授權(quán)操作權(quán)限
DDL：操作數(shù)據(jù)庫、表
1、操作數(shù)據(jù)庫：CRUD
C（Create）：創(chuàng)建
-- 創(chuàng)建數(shù)據(jù)庫mysql> CREATE DATABASE city;-- 首先判斷數(shù)據(jù)庫是存在，不存在才創(chuàng)建mysql> CREATE DATABASE IF NOT EXISTS TEST;-- 指定數(shù)據(jù)庫的字符集mysql> CREATE DATABASE TEXT CHARACTER SET GBK;-- 創(chuàng)建名字為school的數(shù)據(jù)庫，先判斷是否存在，并且指定使用字符集GBKmysql> CREATE DATABASE IF NOT EXISTS SCHHOL CHARACTER SET GBK;-- 查詢數(shù)據(jù)庫的名稱SHOW DATABASES;mysql> SHOW DATABASES; -------------------- | Database | -------------------- | city || information_schema || mysql || performance_schema || sys | -------------------- -- 查看某個(gè)數(shù)據(jù)庫的字符集:查詢某個(gè)數(shù)據(jù)庫的創(chuàng)建語句mysql> show create database mysql; ---------- --------------------------------------------------------------------------------------------------------------------------------- | Database | Create Database | ---------- --------------------------------------------------------------------------------------------------------------------------------- | mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | ---------- --------------------------------------------------------------------------------------------------------------------------------- -- 修改數(shù)據(jù)庫的字符集mysql> ALTER DATABASE SCHOOL CHARACTER SET UTF8;-- 刪除數(shù)據(jù)庫mysql> DROP DATABASE SCHOOL;-- 先判斷數(shù)據(jù)庫是否存在，如果存在才刪除mysql> DROP DATABASE IF EXISTS SCHOOL;-- 使用數(shù)據(jù)庫mysql> USE CITY;-- 查詢當(dāng)前正在使用的數(shù)據(jù)庫名稱mysql> SELECT DATABASE(); ------------ | DATABASE() | ------------ | city | ------------ 1 row in set (0.00 sec)CREATE TABLE 表名( 列名1，數(shù)據(jù)類型1， 列名2，數(shù)據(jù)類型2， 列名3，數(shù)據(jù)類型3 ...... 列名n，數(shù)據(jù)類型n);#最后一行不需要加逗號INT 整數(shù)DOUBLE 小數(shù)(指定一共多少位,小數(shù)點(diǎn)后多少位):SCORE DOUBLE(5,2)DATE 日期類型:只包含年月日，yyyy-MM-ddDATETIME 時(shí)間類型:年月日時(shí)分秒，yyyy-MM-dd HH:mm:ssTIMESTAMP 時(shí)間戳類型:年月日時(shí)分秒，yyyy-MM-dd HH:mm:ss-- 如果是TIMESAMP類型，不給其賦值或者指定為NULL,將會默認(rèn)使用系統(tǒng)的當(dāng)前時(shí)間來自動賦值VARCHAR 字符串類型(指定長度):NAME VARCHAR(20)-- 學(xué)生表-- 編號 姓名 年齡 分?jǐn)?shù) 出生日期 添加時(shí)間mysql> CREATE TABLE STU_MESSAGE( -> ID INT, -> NAME VARCHAR(32), -> AGE INT, -> SCORE DOUBLE(4,1), -> BIRTHDAY DATE, -> INSERT_TIME TIMESTAMP -> );Query OK, 0 rows affected, 1 warning (0.60 sec)-- 檢查表結(jié)構(gòu)mysql> DESC STU_MESSAGE; ------------- ------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------------- ------------- ------ ----- --------- ------- | ID | int(11) | YES | | NULL | || NAME | varchar(32) | YES | | NULL | || AGE | int(11) | YES | | NULL | || SCORE | double(4,1) | YES | | NULL | || BIRTHDAY | date | YES | | NULL | || INSERT_TIME | timestamp | YES | | NULL | | ------------- ------------- ------ ----- --------- ------- 6 rows in set (0.00 sec)-- 復(fù)制表mysql> CREATE TABLE STU LIKE STU_MESSAGE;-- 查詢某個(gè)數(shù)據(jù)庫當(dāng)中的表名稱mysql> SHOW TABLES; ---------------- | Tables_in_city | ---------------- | student | ---------------- -- 查詢表的創(chuàng)建語句mysql> SHOW CREATE TABLE STUDENT; --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table | --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | STUDENT | CREATE TABLE `student` ( `ID` int(11) DEFAULT NULL, `NAME` varchar(32) DEFAULT NULL, `AGE` int(11) DEFAULT NULL, `SCORE` double(4,1) DEFAULT NULL, `BIRTHDAY` date DEFAULT NULL, `INSERT_TIME` timestamp NULL DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk | --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- 查詢表結(jié)構(gòu)mysql> DESC USER; -------------------------- ----------------------------------- ------ ----- ----------------------- ------- | Field | Type | Null | Key | Default | Extra | -------------------------- ----------------------------------- ------ ----- ----------------------- ------- | Host | char(255) | NO | PRI | | || User | char(32) | NO | PRI | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum('N','Y') | NO | | N | || Update_priv | enum('N','Y') | NO | | N | || Delete_priv | enum('N','Y') | NO | | N | || Create_priv | enum('N','Y') | NO | | N | || Drop_priv | enum('N','Y') | NO | | N | || Reload_priv | enum('N','Y') | NO | | N | || Shutdown_priv | enum('N','Y') | NO | | N | || Process_priv | enum('N','Y') | NO | | N | || File_priv | enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv | enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Show_db_priv | enum('N','Y') | NO | | N | || Super_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv | enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Repl_slave_priv | enum('N','Y') | NO | | N | || Repl_client_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv | enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Create_user_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | || Create_tablespace_priv | enum('N','Y') | NO | | N | || ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | || ssl_cipher | blob | NO | | NULL | || x509_issuer | blob | NO | | NULL | || x509_subject | blob | NO | | NULL | || max_questions | int(11) unsigned | NO | | 0 | || max_updates | int(11) unsigned | NO | | 0 | || max_connections | int(11) unsigned | NO | | 0 | || max_user_connections | int(11) unsigned | NO | | 0 | || plugin | char(64) | NO | | caching_sha2_password | || authentication_string | text | YES | | NULL | || password_expired | enum('N','Y') | NO | | N | || password_last_changed | timestamp | YES | | NULL | || password_lifetime | smallint(5) unsigned | YES | | NULL | || account_locked | enum('N','Y') | NO | | N | || Create_role_priv | enum('N','Y') | NO | | N | || Drop_role_priv | enum('N','Y') | NO | | N | || Password_reuse_history | smallint(5) unsigned | YES | | NULL | || Password_reuse_time | smallint(5) unsigned | YES | | NULL | || Password_require_current | enum('N','Y') | YES | | NULL | || User_attributes | json | YES | | NULL | | -------------------------- ----------------------------------- ------ ----- ----------------------- ------- -- 修改表名mysql> ALTER TABLE STU RENAME TO STUDENT;-- 修改表的字符集mysql> ALTER TABLE STUDENT CHARACTER SET UTF8;-- 添加列mysql> ALTER TABLE STUDENT ADD GENDER VARCHAR(10);-- 刪除列mysql> ALTER TABLE STUDENT DROP SEX;-- 修改列的名稱和數(shù)據(jù)類型mysql> ALTER TABLE STUDENT CHANGE GENDER SEX VARCHAR(20);-- 修改列的數(shù)據(jù)類型mysql> ALTER TABLE STUDENT MODIFY SEX VARCHAR(10);-- 先判斷表是否存在，然后刪除mysql> DROP TABLE IF EXISTS STUDENT;
INSERT INTO TABLE_NAME(NAME1,NAME2,...,NAMEn)VALUES (VALUE1,VALUE2,...,VALUEn);1、列名和值要一一對應(yīng)2、如果表名后面不定義列名，則默認(rèn)給所有列添加值3、除了數(shù)字之外的其他類型需要雙引號或者是單引號INSERT INTO STUDENT(ID, NAME, AGE) VALUES (1, '張無忌', 18);INSERT INTO student VALUES(2, '趙敏', 17, 99.9, '20000510', CURRENT_TIMESTAMP());DELETE FROM TABLE_NAME WHERE CONDITION;1、如果不加條件，就是刪除表中所有數(shù)據(jù)2、如果要?jiǎng)h除全部的數(shù)據(jù)，最好采用TRUNCATE，因?yàn)槿绻遣捎肈ELETE，有多少的數(shù)據(jù)就會執(zhí)行多少次，效率特別低-- 刪除符合條件的數(shù)據(jù)DELETE FROM STUDENT WHERE ID = 1;-- 刪除全部數(shù)據(jù)（執(zhí)行兩個(gè)操作，刪除表，然后再創(chuàng)建一個(gè)一模一樣的空表）TRUNCATE TABLE STUDENT;UPDATE TABLE_NAME SET NAME1 = VALUE1, NAME2 = VALUE2,..., NAMEn = VALUEn WHERE CONDITION;1、如果不加任何條件，則將表中的全部數(shù)據(jù)進(jìn)行修改UPDATE STUDENT SET AGE = 117 WHERE NAME = '趙敏';SELECT * FROM TABLE_NAME;