半小时MySQL基础入门值得收藏

半小时MySQL基础入门，值得收藏

概述

这篇文章主要针对刚入门的开发，一般半个小时是可以学完的，当然要理解的话估计不止30分钟，对于初学者来说只需满足自己需求可以增删改查等简易的维护即可。

下面介绍下MySQL的根底方面内容。

一、开始使用

MySQL为关系型数据库(RelationalDatabaseManagementSystem)，一个关系型数据库由一个或数个表格组成,表格一般包括下列：

表头(header):每一列的名称;

列(col):具有相同数据类型的数据的汇合;

行(row):每一行用来描述某个人/物的具体信息;

值(value):行的具体信息,每个值必须与该列的数据类型相同;

键(key):表中用来辨认某个特定的人\物的办法,键的值在当前列中具有唯一性。

二、登录MySQL

mysql-h-u用户名-p

mysql-D所选择的数据库名-h主机名-u用户名-p

三、创立数据库

对于表的操作需要先进入库use库名;

--创立一个名为samp_db的数据库，数据库字符编码指定为utf8

createdatabasesamp_dbcharactersetutf8;

dropdatabasesamp_db;--删除库名为samp_db的库

showdatabases;--显示数据库列表。

usesamp_db;--选择创立的数据库samp_db

showtables;--显示samp_db下面所有的表名字

describe表名;--显示数据表的结构

deletefrom表名;--清空表中记录

四、创立数据库表

使用createtable语句可完成对表的创立,createtable的常见形式:语法：createtable表名称(列声明);

--如果数据库中存在user_accounts表，就把它从数据库中drop掉

DROPTABLEIFEXISTS`user_accounts`;

CREATETABLE`user_accounts`(

`id`int(100)unsignedNOTNULLAUTO_INCREMENTprimarykey,

`password`varchar(32)NOTNULLDEFAULT''COMMENT'用户密码',

`reset_password`tinyint(32)NOTNULLDEFAULT0COMMENT'用户类型：0－不需要重置密码；1-需要重置密码',

`mobile`varchar(20)NOTNULLDEFAULT''COMMENT'手机',

`create_at`timestamp(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6),

`update_at`timestamp(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6)ONUPDATECURRENT_TIMESTAMP(6),

--创立唯一索引，不允许重复

UNIQUEINDEXidx_user_mobile(`mobile`)

)

ENGINE=InnoDBDEFAULTCHARSET=utf8

COMMENT='用户表信息';

数据类型的属性解释

NULL：数据列可包含NULL值；

NOTNULL：数据列不允许包含NULL值；

DEFAULT：默认值；

PRIMARYKEY：主键；

AUTO_INCREMENT：自动递增，适用于整数类型；

UNSIGNED：是指数值类型只能为正数；

CHARACTERSETname：指定一个字符集；

COMMENT：对表或者字段表明；

五、增删改查

1、SELECT

SELECT语句用于从表当选取数据。

语法：SELECT列名称FROM表名称

语法：SELECT*FROM表名称

--表station取个别名叫s，表station中不包含字段id=13或者14的，并且id不等于4的查询出来，只显示id

SELECTs.idfromstationsWHEREidin(13,14)andidnotin(4);

--从表Persons选取LastName列的数据

SELECTLastNameFROMPersons

--从表users选取id=3的数据，并只拉一条数据(据说能优化性能)

SELECT*FROMuserswhereid=3limit1

--结果集中会自动去重复数据

SELECTDISTINCTCompanyFROMOrders

--表Persons字段Id_P等于Orders字段Id_P的值，

--结果集显示Persons表的LastName、FirstName字段，Orders表的OrderNo字段

SELECTp.LastName,p.FirstName,o.OrderNoFROMPersonsp,OrdersoWHEREp.Id_P=o.Id_P

--gbk和utf8中英文混合排序最简单的方法

--ci是caseinsensitive,即大小写不敏感

SELECTtag,COUNT(tag)fromnewsGROUPBYtagorderbyconvert(tagusinggbk)collategbk_chinese_ci;

SELECTtag,COUNT(tag)fromnewsGROUPBYtagorderbyconvert(tagusingutf8)collateutf8_unicode_ci;

2、UPDATE

Update语句用于修改表中的数据。

语法：UPDATE表名称SET列名称=新值WHERE列名称=某值

--update语句设置字段值为另一个结果取出来的字段

updateusersetname=(selectnamefromuser1whereuser1.id=1)

whereid=(='小苏');

--更新表orders中id=1的那一行数据更新它的title字段

UPDATE`orders`settitle='这里是标题'WHEREid=1;

3、INSERT

INSERTINTO语句用于向表格中插入新的行。

语法：INSERTINTO表名称VALUES(值1,值2,....)

语法：INSERTINTO表名称(列1,列2,...)VALUES(值1,值2,....)

--向表Persons插入一条字段LastName=JSLite字段Address=shanghai

INSERTINTOPersons(LastName,Address)VALUES('JSLite','shanghai');

--向表meeting插入字段a=1和字段b=2

INSERTINTOmeetingSETa=1,b=2;

--SQL实现将一个表的数据插入到另外一个表的代码

--如果只希望导入指定字段，可以用这种办法：

--INSERTINTO目标表(字段1,字段2,...)SELECT字段1,字段2,...FROM来源表;

INSERTINTOorders(user_account_id,title)SELECTm.user_id,m.titleFROMmeetingmwherem.id=1;

--向表charger插入一条数据，已存在就对表charger更新`type`,`update_at`字段；

INSERTINTO`charger`(`id`,`type`,`create_at`,`update_at`)VALUES(3,2,'2022-05-1811:06:17','2022-05-1811:06:17')ONDUPLICATEKEYUPDATE`id`=VALUES(`id`),`type`=VALUES(`type`),`update_at`=VALUES(`update_at`);

4、DELETE

DELETE语句用于删除表中的行。

语法：DELETEFROM表名称WHERE列名称=值

--在不删除table_name表的情况下删除所有的行，清空表。

DELETEFROMtable_name

--或者

DELETE*FROMtable_name

--删除Person表字段LastName='JSLite'

DELETEFROMPersonWHERELastName='JSLite'

--删除表meetingid为2和3的两条数据

DELETEfrommeetingwhereidin(2,3);

5、WHERE

WHERE子句用于规定选择的规范。

语法：SELECT列名称FROM表名称WHERE列运算符值

--从表Persons当选出Year字段大于1965的数据

SELECT*FROMPersonsWHEREYear>1965

六、操作符

1、AND和OR

AND-如果第一个条件和第二个条件都成立；

OR-如果第一个条件和第二个条件中只要有一个成立；

1.1、AND

--删除meeting表字段

--id=2并且user_id=5的数据和

--id=3并且user_id=6的数据

DELETEfrommeetingwhereidin(2,3)anduser_idin(5,6);

--使用AND来显示所有姓为"Carter"并且名为"Thomas"的人：

SELECT*FROMPersonsWHEREFirstName='Thomas'ANDLastName='Carter';

1.2、OR

--使用OR来显示所有姓为"Carter"或者名为"Thomas"的人：

SELECT*FROMPersonsWHEREfirstname='Thomas'ORlastname='Carter'

2、ORDERBY

语句默认按照升序对记录进行排序。

ORDERBY-语句用于根据指定的列对结果集进行排序。

DESC-按照降序对记录进行排序。

ASC-按照顺序对记录进行排序。

--Company在表Orders中为字母，那么会以字母顺序显示公司名称

SELECTCompany,OrderNumberFROMOrdersORDERBYCompany

--后面跟上DESC那么为降序显示

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC

--Company以降序显示公司名称，并OrderNumber以顺序显示

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC,OrderNumberASC

3、IN

IN-操作符允许我们在WHERE子句中规定多个值。

IN-操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。语法：SELECT"字段名"FROM"表格名"WHERE"字段名"IN('值一','值二',...);

--从表Persons选取字段LastName等于Adams、Carter

SELECT*FROMPersonsWHERELastNameIN('Adams','Carter')

4、NOT

NOT-操作符总是与其他操作符一起使用，用在要过滤的前面。

SELECTvend_id,prod_nameFROMProductsWHERENOTvend_id='DLL01'ORDERBYprod_name;

5、UNION

UNION-操作符用于合并两个或多个SELECT语句的结果集。

--列出所有在中国表〔Employees_China〕和美国〔Employees_USA〕的不同的雇员名

SELECTE_NameFROMEmployees_ChinaUNIONSELECTE_NameFROMEmployees_USA

--列出meeting表中的pic_url，

--station表中的number_station别名设置成pic_url防止字段不一样报错

--按更新时间排序

SELECTid,pic_urlFROMmeetingUNIONALLSELECTid,number_stationASpic_urlFROMstationORDERBYupdate_at;

--通过UNION语法同时查询了products表和comments表的总记录数，并且按照count排序

SELECT'product'AStype,count(*)ascountFROM`products`unionselect'comment'astype,count(*)ascountFROM`comments`orderbycount;

6、AS

as-可理解为：用作、当成，作为；别名

一般是重命名列名或者表名。

语法：selectcolumn_1as列1,column_2as列2fromtableas表

SELECT*FROMEmployeeASemp

--这句意思是查找所有Employee表里面的数据，并把Employee表格命名为emp。

--当你命名一个表之后，你可以在下面用emp代替Employee.

--示例SELECT*FROMemp.

SELECTMAX(OrderPrice)ASLargestOrderPriceFROMOrders

--列出表Orders字段OrderPrice列最大值，

--结果集列不显示OrderPrice显示LargestOrderPrice

--显示表users_profile中的name列

SELECTfrom(SELECT*fromusers_profilea)ASt;

--表user_accounts命名别名ua，表users_profile命名别名up

--满足条件表user_accounts字段id等于表users_profile字段user_id

--结果集只显示mobile、name两列

SELECTua.mobile,FROMuser_accountsasuaINNERJOINusers_profileasupONua.id=up.user_id;

7、JOIN

用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

JOIN:如果表中有至少一个匹配，那么返回行

INNERJOIN:在表中存在至少一个匹配时，INNERJOIN关键字返回行。

LEFTJOIN:即使右表中没有匹配，也从左表返回所有的行

RIGHTJOIN:即使左表中没有匹配，也从右表返回所有的行

FULLJOIN:只要其中一个表中存在匹配，就返回行(MySQL是不支持的，通过LEFTJOIN+UNION+RIGHTJOIN的方式来实现)

SELECTPersons.LastName,Persons.FirstName,Orders.OrderNo

FROMPersons

INNERJOINOrders

ONPersons.Id_P=Orders.Id_P

ORDERBYPersons.LastName;

七、SQL函数

1、COUNT

COUNT让我们能够数出在表格中有多少笔资料被选出来。

语法：SELECTCOUNT("字段名")FROM"表格名";

--表Store_Information有几笔store_name栏不是空白的资料。

--"ISNOTNULL"是"这个栏位不是空白"的意思。

SELECTCOUNT(Store_Name)FROMStore_InformationWHEREStore_NameISNOTNULL;

--获取Persons表的总数

SELECTCOUNT(1)AStotalsFROMPersons;

--获取表station字段user_id相同的总数

selectuser_id,count(*)astotalsfromstationgroupbyuser_id;

2、MAX

MAX函数返回一列中的最大值。NULL值不包括在计算中。

语法：SELECTMAX("字段名")FROM"表格名"

--列出表Orders字段OrderPrice列最大值，

--结果集列不显示OrderPrice显示LargestOrderPrice

SELECTMAX(OrderPrice)ASLargestOrderPriceFROMOrders

3、round

ROUND函数用于把数值字段舍入为指定的小数位数。

语法：SELECTROUND(column_name,decimals)FROMtable_name

--列知名称和价格舍入为最接近的整数

SELECTProductName,ROUND(UnitPrice,0)asUnitPriceFROMProducts

4、format

FORMAT函数用于对字段的显示进行格式化。

语法：SELECTFORMAT(column_name,format)FROMtable_name

--显示每天日期所对应的名称和价格〔日期的显示格式是"YYYY-MM-DD"〕

SELECTProductName,UnitPrice,FORMAT(Now(),'YYYY-MM-DD')asPerDateFROMProducts

5、ucase

UCASE函数把字段的值转换为大写。

语法：SELECTUCASE(column_name)FROMtable_name

--选取"LastName"和"FirstName"列的内容，然后把"LastName"列转换为大写。

SELECTUCASE(LastName)asLastName,FirstNameFROMPersons

6、lcase

LCASE函数把字段的值转换为小写。

语法：SELECTLCASE(column_name)FROMtable_name

--选取"LastName"和"FirstName"列的内容，然后把"LastName"列转换为小写

SELECTLCASE(LastName)asLastName,FirstNameFROMPersons

八、触发器

语法：createtrigger{before|after}#之前或者之后出发insert|update|delete#指明了激活触发程序的语句的类型on#操作哪张表foreachrow#触发器的执行间隔，foreachrow通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

delimiter$

CREATETRIGGERset_userdateBEFOREINSERT

on`message`

forEACHROW

BEGIN

set@statu=new.status;--声明复制变量statu

if@statu=0then--判断statu是否等于0

UPDATE`user_accounts`SETstatus=1WHEREopenid=NEW.openid;

endif;

END

$

DELIMITER;--恢复结束符号

OLD和NEW不辨别大小写

NEW用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。

OLD用OLD.col_name来引用更新前的某一行的列

九、添加索引

1、普通索引(INDEX)

语法：ALTERTABLE表名字ADDINDEX索引名字(字段名字)

--–直接创立索引

CREATEINDEXindex_userONuser(title)

--–修改表结构的方式添加索引

ALTERTABLEtable_nameADDINDEXindex_nameON(column(length))

--给user表中的name字段添加普通索引(INDEX)

ALTERTABLE`user`ADDINDEXindex_name(name)

--–创立表的时候同时创立索引

CREATETABLE`user`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,

`content`textCHARACTERSETutf8COLLATEutf8_general_ciNULL,

`time`int(10)NULLDEFAULTNULL,

PRIMARYKEY(`id`),

INDEXindex_name(title(length))

)

--–删除索引

DROPINDEXindex_nameONtable

2、主键索引(PRIMARYkey)

语法：ALTERTABLE表名字ADDPRIMARYKEY(字段名字)

--给user表中的id字段添加主键索引(PRIMARYkey)

ALTERTABLE`user`ADDPRIMARYkey(id);

3、唯一索引(UNIQUE)

语法：ALTERTABLE表名字ADDUNIQUE(字段名字)

--给user表中的creattime字段添加唯一索引(UNIQUE)

ALTERTABLE`user`ADDUNIQUE(creattime);

4、全文索引(FULLTEXT)

语法：ALTERTABLE表名字ADDFULLTEXT(字段名字)

--给user表中的description字段添加全文索引(FULLTEXT)

ALTERTABLE`user`ADDFULLTEXT(description);

5、添加多列索引

语法：ALTERTABLEtable_nameADDINDEXindex_name(column1,column2,column3)

--给user表中的name、city、age字段添加名字为name_city_age的普通索引(INDEX)

ALTERTABLEuserADDINDEXname_city_age(name(10),city,age);

6、建立索引的机会

在WHERE和JOIN中出现的列需要建立索引，但也不完全如此：

MySQL只对，>=，BETWEEN，IN使用索引

某些时候的LIKE也会使用索引。

在LIKE以通配符%和_开头作查询时，MySQL不会使用索引。

--此时就需要对city和age建立索引，

--由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

SELECTt.NameFROMmytabletLEFTJOINmytablemONt.Name=m.usernameWHEREm.age=20ANDm.city='上海';

SELECT*FROMmytableWHEREusernamelike'admin%';--而下句就不会使用：

SELECT*FROMmytableWHERENamelike'%admin';--因此，在使用LIKE时应注意以上的区别。

十、创立后表的修改

1、添加列

语法：altertable表名add列名列数据类型[after插入位置];

例如:

--在表students的最后追加列address:

altertablestudentsaddaddresschar(60);

--在名为age的列后插入列birthday:

altertablestudentsaddbirthdaydateafterage;

--在名为number_people的列后插入列weeks:

altertablestudentsaddcolumn`weeks`varchar(5)notnulldefault""after`number_people`;

2、修改列

语法：altertable表名change列名称列新名称新数据类型;

--将表tel列改名为telphone:

altertablestudentschangeteltelphonechar(13)default"-";

--将name列的数据类型改为char(16):

altertablestudentschangenamenamechar(16)notnull;

--修改COMMENT前面必须得有类型属性

altertablestudentschangenamenamechar(16)COMMENT'这里是名字';

--修改列属性的时候倡议使用modify,不需要重建表

--change用于修改列名字，这个需要重建表

altertablemeetingmodify`weeks`varchar(20)NOTNULLDEFAULT''COMMENT'开放日期周一到周日：0~6，间隔用英文逗号隔开';

--`user`表的`id`列，修改成字符串类型长度50，不能为空，`FIRST`放在第一列的位置

altertable`user`modifyCOLUMN`id`varchar(50)NOTNULLFIRST;

3、删除列

语法：altertable表名drop列名称;

--删除表students中的birthday列:

altertablestudentsdropbirthday;

4、重命名表

语法：altertable表名rename新表名;

--重命名students表为workmates:

altertablestudentsrenameworkmates;

5、清空表数据

办法一：deletefrom表名;办法二：truncatetable"表名";

DELETE:1.DML语言;2.可以回退;3.可以有条件的删除;

TRUNCATE:1.DDL语言;2.无法回退;3.默认所有的表内容都删除;4.删除速度比delete快。

--清空表为workmates里面的数据，不删除表。

deletef