一:SQL結(jié)構(gòu)化查詢語言分類
DDL:數(shù)據(jù)定義語言
DCL:數(shù)據(jù)控制語言
DML:數(shù)據(jù)操作語言
DQL:數(shù)據(jù)檢索語言
二:DDL數(shù)據(jù)定義語言
2.1 庫定義
2.1.1 創(chuàng)建庫
mysql> create database cxytest charset utf8mb4;
2.1.2 刪除數(shù)據(jù)庫(生產(chǎn)中禁止使用)
mysql> drop database cxytest;
2.1.3 修改字符集
mysql> alter database test charset utf8mb4;
2.1.4 查看創(chuàng)建的數(shù)據(jù)庫
mysql> show create database cxytest;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| cxytest | CREATE DATABASE `cxytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2 表定義
2.2.1 列屬性
數(shù)據(jù)類型
數(shù)字類型:
tinyint 1個字節(jié) 0-255 3位數(shù)
int 3個字節(jié) 0-4294967295 10位數(shù)
說明:手機號碼是無法存儲到int的,一般是使用char類型來存儲手機號
字符類型:
char(10)
定長10位,存儲空間1次性分配,如果字符沒有寫滿10位,也分配10位,可以分配255個;char(255)
varchar(10)
可變長10位,按需分配空間,寫幾位占幾位,可以分配65535個;char(65535)
enum('m','f') 枚舉類型
比較適合于將來此列的值是固定范圍內(nèi)的特點,可以使用enum,可以很大程度的優(yōu)化我們的索引結(jié)構(gòu)
時間類型:
datetime
時間范圍從 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
timestamp
時間范圍從1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999
約束
primary key 主鍵:非空,唯一
unique key:唯一鍵;不重復(fù)
not null:非空
unsigned:無符號,數(shù)字類型的列,非負數(shù)
其他屬性
default 默認值
auto_increment 自增長
comment 注釋
2.2.2 表屬性
engine 引擎
charset 字符集
comment 注釋
2.3 表創(chuàng)建
2.3.1 創(chuàng)建語法
語法:create table 表名(
列1 屬性(數(shù)據(jù)類型,約束,其他屬性)
列2 屬性
列3 屬性
)
2.3.2 規(guī)范建表的例子
USE test; """切換到要建表的數(shù)據(jù)庫下
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號', """int整型;非空;主鍵;自增長;注釋"""
sname VARCHAR(20) NOT NULL COMMENT '姓名', """varchar字符串;非空;注釋"""
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡', """tinyint整型;unsigned:無符號,數(shù)字類型的列,非負數(shù);非空;有默認值為0;注釋"""
sgender ENUM('男','女','未知') NOT NULL DEFAULT '未知', """枚舉類型; 非空,有默認值'未知' """
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份證', """char字符串定長18位;非空;unique:唯一鍵不重復(fù);注釋"""
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入學(xué)時間' """timestamp時間類型;非空;默認值為當前時間;注釋"""
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '學(xué)生表'; """表引擎innodb;字符集utf8mb4;注釋"""
建表規(guī)范:
- 表名小寫
- 不能是數(shù)字開頭
- 注意字符集和存儲引擎
- 選擇合適的數(shù)據(jù)類型
- 每個列都要有注釋
- 每個列設(shè)置為非空,無法保證非空的,用0來填充
2.3.3 刪表(生產(chǎn)中禁止使用此命令)
mysql > drop table stu;
2.3.4 修改表
在stu表中添加qq列
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq號';
在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信號' AFTER sname;
在id列前加一個新列num
ALTER TABLE stu ADD num INT NOT NULL COMMENT '數(shù)字' FIRST;
刪除剛才添加的列(危險)
ALTER TABLE stu DROP num;
修改sname數(shù)據(jù)類型的屬性
ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL;
修改sgender改為列名sg char(3) not null default '未知'
ALTER TABLE stu CHANGE sgender sg CHAR(3) NOT NULL DEFAULT '未知'
2.3.5 查看表屬性
查看表中有哪些列以及其屬性
mysql> desc stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(128) | NO | | NULL | |
| wechat | varchar(64) | NO | UNI | NULL | |
| sage | tinyint(3) unsigned | NO | | 0 | |
| sg | char(3) | NO | | 未知 | |
| sfz | char(18) | NO | UNI | NULL | |
| intime | timestamp | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | UNI | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
查看數(shù)據(jù)庫里有哪些表
mysql> show tables;
+-------------------+
| Tables_in_cxytest |
+-------------------+
| stu |
+-------------------+
1 row in set (0.00 sec)
查看創(chuàng)建表時的語句
mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`sname` varchar(128) NOT NULL,
`wechat` varchar(64) NOT NULL COMMENT '微信號',
`sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年齡',
`sg` char(3) NOT NULL DEFAULT '未知',
`sfz` char(18) NOT NULL COMMENT '身份證',
`intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入學(xué)時間',
`qq` varchar(20) NOT NULL COMMENT 'qq號',
PRIMARY KEY (`id`),
UNIQUE KEY `sfz` (`sfz`),
UNIQUE KEY `qq` (`qq`),
UNIQUE KEY `wechat` (`wechat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學(xué)生表'
三:DCL數(shù)據(jù)控制語言
grant
revoke
在mysql基礎(chǔ)管理里有介紹
四:DML 對表中的數(shù)據(jù)進行增刪改
4.1 增 insert
最標準的insert語句
INSERT INTO stu(id,sname,sage,sgender,sfz,intime)
VALUES
(1,'張三',20,'男',1111134454,NOW());
省事的寫法
INSERT INTO stu
VALUES (2,'趙四',29,'女','8796554678',NOW());
針對性的錄入
INSERT INTO stu(sname,sfz)
VALUES ("王鐵柱",9808988332)
同時錄入多行數(shù)據(jù)
INSERT INTO stu(sname,sage,sg,sfz,intime)
VALUES
('田二妞',29,'女','6549890',NOW()),
('支付寶',76,'男',4321908,NOW()),
('王大富',33,'未知','23213434',NOW());
4.2 刪(謹慎使用)delete
DELETE FROM stu WHERE id=4;
4.3改update
修改表里的內(nèi)容,一定要跟where字句一起使用
UPDATE stu SET sname='張三豐' WHERE id=1;
五:DQL數(shù)據(jù)檢索語言(select)
5.1 單獨使用,查看系統(tǒng)參數(shù)
查看系統(tǒng)參數(shù)
SELECT @@port;
SELECT @@datadir;
SELECT @@basedir;
SELECT @@server_id;
5.2 select 函數(shù)()
SELECT DATABASE(); ''''查看當前使用的哪個數(shù)據(jù)庫''''
SELECT NOW(); ''''查看當前系統(tǒng)時間''''
SELECT USER(); ''''查看當前登錄的用戶是誰''''
concat字符串的拼接
select concat(user,'@','host') from mysql.user;
image.png
group_concat:將列轉(zhuǎn)行,將幾列數(shù)據(jù)轉(zhuǎn)成一行顯示
SELECT GROUP_CONCAT(USER,'@','host') FROM mysql.user;
image.png
5.3 select-from 子句
語法
select 列1,列2,列3 from 表名;
查詢stu中所有的數(shù)據(jù)(不要對大表進行操作)
SELECT * FROM stu;
查詢stu表中,學(xué)生姓名和入學(xué)時間
SELECT sname,intime FROM stu;
image.png
5.4 where子句
查詢中國所有的城市信息
SELECT * FROM city WHERE COUNTRYCODE='CHN';
查詢北京市的信息
SELECT * FROM city WHERE NAME='peking'
查詢甘肅省所有城市的信息
SELECT * FROM city WHERE DISTRICT='GANSU'
查詢世界上人口小于100w的城市
SELECT * FROM city WHERE population<1000000;
查詢中國人口大于500w的城市
使用AND用法
SELECT * FROM city WHERE Countrycode='CHN' AND population>5000000
查詢中國或者美國的城市信息
使用or用法
SELECT * FROM city WHERE countrycode='CHN' OR COUNTRYCODE='USA';
使用in()用法
SELECT * FROM city WHERE countrycode IN('CHN','USA')
查詢世界上人口數(shù)量大于100w小于200w的城市信息
2種寫法
SELECT * FROM city WHERE population>1000000 AND population<2000000
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000
模糊查詢
SELECT * FROM city WHERE district LIKE 'guang%'
'''''不要使用全模糊查詢,百分號不能放在字符最前面,這樣會影響數(shù)據(jù)庫速度'''
5.5 group by+聚合函數(shù)
- 作用:將統(tǒng)計好的數(shù)據(jù),按照by后面的條件進行分組排列
max() 最大值
min() 最小值
avg() 平均值
sum() 總和
count() 個數(shù)
group_councat() 列轉(zhuǎn)行
統(tǒng)計世界上每個國家的總?cè)丝跀?shù)sum()
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
統(tǒng)計中國各個省的總?cè)丝跀?shù)量sum()
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
統(tǒng)計世界上每個國家的城市個數(shù)count()
SELECT countrycode,COUNT(NAME) FROM city GROUP BY countrycode
統(tǒng)計中國每個省的城市列表group_concat
SELECT district,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
5.6 having 子句
- 作用:運行在group by后面,跟where的作用一樣,只是優(yōu)先級比where低
統(tǒng)計中國每個省的總?cè)丝跀?shù),只打印總?cè)丝跀?shù)小于100w的
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<1000000
5.7 order by +limit
- 作用:排序,默認從小到大,從大到小需要加desc
order by 條件 desc;
limit 顯示數(shù)據(jù)中的某幾列
limit 3:顯示前3行
limit 3,7:跳過前3行之后,從第4行開始顯示到第7行
查看中國所有的城市,并按人口數(shù)進行排序(從大到小)
SELECT NAME,population FROM city WHERE countrycode='CHN' ORDER BY population DESC;
統(tǒng)計中國各個省的總?cè)丝跀?shù)量,按照總?cè)丝趶拇蟮叫∨判?/h4>
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;
統(tǒng)計中國每個省的總?cè)丝?找到總?cè)丝诖笥?00w的,并按總?cè)丝趶拇蟮叫∨判?只顯示前三名
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;
5.8 distinct 去重復(fù)查詢
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city;
5.9 聯(lián)合查詢-union all
查詢中國或者美國城市信息
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE COUNTRYCODE='USA'
6.0 子句的優(yōu)先級順序
select f1,f2......
from 表
where 過濾條件
group by 分組條件
having 過濾條件
order by 排序條件
limit 限制條件
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city;
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE COUNTRYCODE='USA'
select f1,f2......
from 表
where 過濾條件
group by 分組條件
having 過濾條件
order by 排序條件
limit 限制條件
