作为一名web前端工程师，也要尝试学习后端知识，从数据库基础开始。下面是一些常用的mysql数据库命令，会持续更新，而且后期会推出koa+mysql的简单实践，在这篇文章之后~_~
想学习前端技术的可以来我的前端直播授课QQ群：445611230.可以领取免费的学习资料。
二、创建库、表、列
CREATE DATABASE 【database name】;——创建
SHOW DATABASES; —— 查看本地mysql数据库
USE【database name】; —— 进入到某个数据库
CREATE TABLE【table name】; —— 创建当前数据库的表
打开cmd 用上述命令创建 juejin
数据库
然后分别创建两个数据库表（mso_type | mso_blog），作为下面所有命令操作的实例：
mso_type[博客类型]
名类型长度不是null键注释
idvarchar40not nullprimary key博客类型唯一Id
typetinyint默认not nullunsigned博客类型
type_titlevarchar8not nullunique博客类型名称
create_timedatetime默认default current_timestamp默认创建时间
modify_timedatetime默认default current_timestamp默认修改时间
mso_blog[博客]
名类型长度不是null键注释
idvarchar40not nullprimary key博客唯一Id
blog_typetinyint默认not nullforeign key(type) references mso_type(type) on delete cascade on update cascade博客类型
blog_titlevarchar32not nullunique博客名称
blog_descriptionvarchar100not null默认博客描述
blog_logovarchar100not null默认博客logo
blog_linkvarchar100not null默认博客markdown 地址
blog_authorvarchar32not null默认博客作者
create_timedatetime默认default current_timestamp默认创建时间
modify_timedatetime默认default current_timestamp默认修改时间
mso_type[博客类型] 的表代码如下：
CREATETABLEmso_type( idVARCHAR(40)PRIMARYKEY, typeTINYINTUNSIGNEDNOTNULL, type_titleVARCHAR(8)NOTNULLUNIQUE, create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP, modify_timeDATETIMEDEFAULTCURRENT_TIMESTAMP);复制代码
mso_blog[博客] 的表代码如下：
CREATETABLEmso_blog (idVARCHAR(40) PRIMARYKEY, blog_type TINYINTUNSIGNEDNOTNULL, blog_titleVARCHAR(32)NOTNULLUNIQUE, blog_descriptionVARCHAR(100)NOTNULL, blog_logoVARCHAR(100)NOTNULL, blog_linkVARCHAR(100)NOTNULL, blog_authorVARCHAR(32)NOTNULL, create_time datetimedefaultcurrent_timestamp, modify_time datetimedefaultcurrent_timestamp);复制代码
三、查看表、列
SHOW TABLES FROM 【database name】; —— 查看当前数据库所有的表列表展示
SHOW TABLES FROM 【database name】\G ; —— 查看当前数据库所有的表网格展示
SHOW CREATE TABLE 【table name】; —— 查看当前表列表展示
SHOW CREATE TABLE 【table name】\G; —— 查看当前表列表展示
SHOW INDEXEX FROM 【table name】; —— 查看当前表的索引
SHOW COLUMNS FROM 【table name】; —— 查看当前表的列
四、修改数据库
ALTER TABLE【table name】ADD 【column name column definition】【AFTER|FIRST】【column name】;—— 在当前数据表中添加列或者在某个列前面或者后面添加
比如在 mso_type 表中添加一列comment并且添加到 type_title 后面
ALTERTABLEmso_typeADDcommentVARCHAR(100)AFTERtype_title;复制代码
如果添加多列
ALTERTABLEmso_typeADDcomment1VARCHAR(100)AFTERtype_title,ADDcomment2VARCHAR(100)AFTERcomment1;复制代码
ALTER TABLE【table name】DROP 【column name】;—— 在当前数据表中删除单列
ALTER TABLE【table name】DROP 【column name】,DROP 【column name】;—— 在当前数据表中删除多列
ALTERTABLEmso_typeDROPcomment,DROPcomment1,DROPcomment2;复制代码
ALTER TABLE【table name】ADD CONSTRAINT【constraint name】PRIMARY KEY (【column name】);—— 添加列主键约束
ALTER TABLE【table name】ADD UNIQUE (【column name】);—— 添加列的唯一约束
ALTER TABLE【table name】ADD FOREIGN KEY (【column name】) REFERENCES 【table name】(【column name】);—— 添加列的外键约束
ALTER TABLE 【table name】 ALTER 【column name】SET DEFAULT 【column value】;—— 添加列的默认约束
ALTER TABLE 【table name】 ALTER 【column name】DROP DEFAULT;—— 删除列的默认约束
ALTER TABLE 【table name】 DROP PRIMARY KEY; —— 删除列的主键约束
SHOW INDEXES FROM 【table name】; —— 查看当前表的索引
ALTER TABLE 【table name】DROP INDEX【column name】; —— 删除单前表的列索引
ALTER TABLE 【table name】DROP FOREIGN KEY【column name】; —— 删除单前表的列外键约束
ALTER TABLE 【table name】MODIFY 【column name column definition】【AFTER|FIRST】; —— 修改列定义
ALTER TABLE 【table name】CHANGE 【column name column realname column definition】【AFTER|FIRST】; —— 修改列名称
ALTER TABLE 【table name】RENAME 【table rename】; —— 修改表的名称1
RENAME TABLE 【table name】TO 【table rename】; —— 修改表的名称2
五、数据表插入、更新、删除
INSERT {INTO} 【table name】VALUES(【column values | default | exp】);—— 数据库表插入数据
INSERTmso_type (id,type, type_title)VALUES(uuid(),1,"1");INSERTmso_blogVALUES(uuid(),1,'123','xxxx','xxxx','xxxx',default,default);INSERTmso_blogVALUES(uuid(),1,'123','xxxx','xxxx','xxxx',default,default),(uuid(),1,md5('123'),'xxxx','xxxx','xxxx',default,default);特殊:INSERTmso_blog (blog_type)SELECTtypeFROMmso_typeWHEREtype=1;复制代码
UPDATE 【table name】SET【column name】= 【exp｜value】,【column name】= 【exp｜value】 WHERE = 【exp｜value】;—— 数据库单表更新
DELETE FROM 【table name】WHERE = 【exp｜value】;—— 数据库单表记录删除
六、数据表查询
1、无条件查询
SELECT【column name1,column name2 | *】FROM 【table name】;—— 数据库表记录查询1
SELECT 【column name1 as rename1,column name2 rename name2 | *】FROM 【table name】;—— 数据库表记录查询3
SELECT【colum.name1,column.name2】FROM 【table name】; —— 数据库表记录查询3
2、分组查询
SELECT【column name|*】FROM 【table name】GROUP BY 【column name】;—— 数据库表记录分组查询
3、条件分组查询
SELECT【column name|*】FROM 【table name】GROUP BY 【column name】HAVING 【column name】| exp;—— 数据库表记录条件分组查询
SELECTtypeFROMmso_typeGROUPBYtypeHAVINGtype_title ="1"SELECTtypeFROMmso_typeGROUPBYtypeHAVINGCOUNT(type) >1复制代码
4、排序、限制查询
SELECT【column name|*】FROM 【table name】ORDER BY 【column name】【DESC|ASC】,【column name】【DESC|ASC】—— 数据库表记录排序查询 SELECT【column name|*】FROM 【table name】ORDER BY 【column name】【DESC|ASC】,【column name】【DESC|ASC】LIMIT 【limit, offset】—— 数据库表记录排序查询
SELECTtypeFROMmso_typeORDERBYtypeDESCLIMIT2,2;复制代码
5、子查询
定义：子查询是指在另一个查询语句中的SELECT子句
SELECT * FROM 【table name1】 WHERE 【column name】 = (SELECT 【column name】 FROM 【table name2】);
行级子查询
SELECT * FROM 【table name1】 WHERE (【column name1, column name2】) = (SELECT 【column name3】,【column name4】 FROM 【table name2】WHERE【column name】= 【column value】);
SELECT * FROM 【table name1】 WHERE ROW(【column name1, column name2】) = (SELECT 【column name3】,【column name4】 FROM 【table name2】WHERE【column name】= 【column value】);
创建一个数据库实例:
CREATETABLEIFNOTEXISTStdb_goods( goods_idSMALLINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT, goods_nameVARCHAR(150)NOTNULL, goods_cateVARCHAR(40)NOTNULL, brand_nameVARCHAR(40)NOTNULL, goods_priceDECIMAL(15,3)UNSIGNEDNOTNULLDEFAULT0, is_showBOOLEANNOTNULLDEFAULT1, is_saleoffBOOLEANNOTNULLDEFAULT0);复制代码
写入记录
INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);复制代码
求下面的结果
-- 求所有电脑产品的平均价格,并且保留两位小数，AVG,MAX,MIN、COUNT、SUM为聚合函数
SELECTROUND(AVG(goods_price),2)ASavg_priceFROMtdb_goods;复制代码
-- 查询所有价格大于平均价格的商品，并且按价格降序排序
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price >5845.10ORDERBYgoods_priceDESC;复制代码
-- 使用子查询来实现
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price > (SELECTROUND(AVG(goods_price),2)ASavg_priceFROMtdb_goods)ORDERBYgoods_priceDESC;复制代码
-- 查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price =ANY(SELECTgoods_priceFROMtdb_goodsWHEREgoods_cate ='超级本')ORDERBYgoods_priceDESC;复制代码
