First, the database operation
--CREATE DATABASE, database name cannot use keywordCreate DatabaseTrainsinfo;--Create a database of Chinese namesSetnames GBK;Create DatabaseChina;Drop DatabaseChina;--View all Databasesshow databases;--View the specified part of the database--%: Indicates that multiple characters are matched--_: Means matching multiple charactersShow databases like 'm%';--to view the creation statement for a databaseShowCreate DatabaseTest;showCreate Databasestudent;--Update Database--database name can not be modified--Database Modification Limited to library options: Character set and proofing set (collation set dependent character set)--Delete the database (do not delete the database arbitrarily)Drop DatabaseTrainsinfo;
Second, table operation
--New Data SheetCreate Table if not existsStudent (namevarchar(Ten), Gendervarchar(Ten), Number varchar(Ten), ageint) charset UTF8;--CREATE table database name. Table name represents the creation of the current data table to the specified databaseCreate Table if not existsstudent.student (Namevarchar(Ten), Gendervarchar(Ten), Number varchar(Ten), ageint) charset UTF8;--View Data Sheet--View all database tablesshow tables;--View some tablesShow tables like 's%';--View Table creation statementsShowCreate Tablestudent;--View Table Structuredescstudent;describe student;show Columns fromstudent;# namevarchar(Ten) YES # Gendervarchar(Ten) YES # Number varchar(Ten) YES # Ageint( One) YES--Modify the data table, table changes are divided into two parts: modify the table itself and modify the field--The table itself can be modified: Table name and table options--Modify Table name:RenameTableStudent toStudentinfo;--Modify Table Options: Character set, proofing collection storage engineAlter TableStudentinfo CharSet=UTF8;--Modify field: New, modify duplicate name, delete--new FieldAlter TableStudentinfoAdd columnIdintFirst ;--Modify a field: Modify a property or data type--Modify the Gender field in a table to sexAlter Tablestudentinfochange Gender Sexvarchar(Ten);--Delete the age field from the student tableAlter TableStudentinfoDropAge ;--Delete a data tableDrop TableStudent.student;
Third, data operation
--Data Manipulation--New Data--There are two types of scenarios:--Scenario One: Insert data into a full table field without specifying a list of fields, requiring that the values of the data appear in the same order as the fields that are designed in the tableInsert intoStudentinfoValues(1,'Xujian','nan',' -'),(2,'Xiewei','nan',' -');--Scenario Two: Inserting data, specifying the field listInsert intoStudentinfo ( Number, Gender,name,id)Values(' -','nan','Luyang',3);--View DataSelect * fromStudentinfo;--Update DataUpdateStudentinfoSetGender='NV' whereId=3;--Delete DataDelete fromStudentinfowhereId=3;
Four, Character set operation
--View all character setsShowcharacter Set;--View the server default character set for external processingShow variables like 'character_set%'# character_set_client UTF8 Server default client to data character Set # character_set_connection utf8# character_set_database UT f8# Character_set_filesystembinary# character_set_results UTF8 Server default character set for out-of-data--Modify the server to think that the character set for client data is GBKSetCharacter_set_client=Gbk
MySQL Learning notes (i)