Learn MySQL database operations (i)

All codes are used for self-study test and annotation, knowledge details or knowledge points will not be exhaustive, there will be no explanation, only for their own study review.

# #数据库操作 # #创建数据库 MyTest, and set the database character set to GBKCREATE DATABASEmyTest CHARSET gbk;# #进入数据库 myTest UsemyTest; # #创建数据库表 t_dept, and set the table's character set to GBK, and the character set of the Char and test fields automatically changes to GBKCREATE TABLEt_dept (DeptnoINT( One) auto_increment, # #设置自增dnameVARCHAR( -) not NULL, # #设置locVARCHAR( $),CONSTRAINTPk_deptnoPRIMARY KEY(Deptno), # #设置主键UNIQUE KEYUk_dname (dname), # #设置唯一索引FULLTEXTKEYIndex_loc (Loc ( -)) # #设置全文索引)DEFAULTCHARSET GBK; # #设置字符集 # #创建数据库表 T_diary, default character set (if you do not change the database character set to GBK before, you will get an error when storing Chinese)CREATE TABLEt_diary (DiarynoINT( One) Auto_increment,tablenameVARCHAR( -), DiarytimeDATETIME,CONSTRAINTPk_diarynoPRIMARY KEY(Diaryno), # #设置主键INDEXIndex_tablename (TableName ( -)ASC# #设置普通索引); # #查看表 t_dept field, view the definition of table T_diaryDESCt_dept; SHOWCREATE TABLEt_diary;# #将t_diary表与其字段的字符集改为GBKALTER TABLEt_diary change CHARSET gbk;ALTER TABLET_diary Change TableName tablenameVARCHAR( -) CHARSET gbk;# #创建视图 view_dept, take the table t_dept (deptno,dname) field and sort in descending order of DeptnoCREATE VIEWView_dept as SELECTDeptno, Dname fromT_deptORDER  byDeptnoDESC; # #查看视图的定义SHOWCREATE VIEWview_dept;# #创建触发器 tri_diarytimeCREATE TRIGGERTri_diarytime beforeINSERT  onT_dept forEach ROWINSERT  intoT_diary (tablename, Diarytime)VALUES('t_dept', now ()); # #向表 t_dept Insert test DataINSERT  intoT_dept (Dname,loc)VALUES('Zhang San','Zhang San can climb trees .');INSERT  intoT_dept (Dname,loc)VALUES('John Doe','Li Shihui Fishing');INSERT  intoT_dept (Dname,loc)VALUES('Harry','Harry will drive .');INSERT  intoT_dept (Dname,loc)VALUES('Zhao Liu','Zhao Liu will be a very flattery');INSERT  intoT_dept (Dname,loc)VALUES('Zhou Qi','riding a donkey in seven weeks');INSERT  intoT_dept (Dname,loc)VALUES('Wu Eight','Wu Eight is the same city'); # #查看 Insert results for t_dept tableSELECT *  fromt_dept;# #查看触发器向 The results inserted in the T_diary tableSELECT *  fromt_diary;# results in the #查看视图 view_deptSELECT *  fromview_dept;

Learn MySQL database operations (i)