MySQL Common command operations

MySQL Common commands summary1: Use the show statement to find out what database is currently present on the server: (case-insensitive) MySQL>SHOW DATABASES; (use databases when displaying the database)2:2, create a database Mysqldata (to display a data when using database) MySQL> CREATE DATABASEMysqldata; (Delete with DropDatabasedbname)3: Select the database you created MySQL>  UseMysqldata; (press ENTER to appear database changed the operation is successful!) , of course not; also prompt success)4: See what tables exist in the database now MySQL>SHOW TABLES;5: Create a database table MySQL> CREATE TABLEMYTABLE (nameVARCHAR( -), SexCHAR(1));6: Shows the structure of the table: MySQL>DESCRIBE (DESC) MYTABLE;7: Add a record to the table MySQL> Insert  intoMYTABLEValues("HyQ", "M");8: Load data into a database table in text mode (for example, D:/mysql.txt) MySQL> LOADDATA LOCAL INFILE "D:/Mysql.txt " into TABLEMYTABLE;9: Import the. sql File command (for example, D:/mysql.sql) MySQL> Use Database; MySQL>SOURCE D:/Mysql.sql;Ten: Delete Table MySQL>Drop TABLEMYTABLE; One: Empty table MySQL>Delete  fromMYTABLE; A: Update data in table MySQL>UpdateMYTABLESetSex="Man"whereName=' Jack ';------------------------------------------------------------Field Operation----------------------To Add a field:Alter TableTableNameAdd columnAgeint; Delete a field:Alter TableTableNameDROP COLUMNnew2; Insert a word defaults think:Alter TableTableNameAdd columnAgeint default 0; Modify the type of a fieldAlter TableTableName MODIFY New1VARCHAR(Ten); To modify the name of a field, be sure to re-specify the field's typeAlter Table UserChange New1 new4int; Add a primary key:Alter TableTableNameAdd Primary Key(col) to delete a primary key:Alter TableTableNameDrop Primary Key(COL)----------------Self-growth---------------CREATE TABLEClass (IDINT  not NULL PRIMARY KEYAuto_increment,usernameVARCHAR( the) not NULL) Auto_increment=  -100 can be used when the table is available "Auto_increment=n "option to specify a self-increment initial value. Available AlterTableTABLE_NAME Auto_increment=n command to reset the starting value of the increment. Inserting data Insert into text Values(NULL,'Jack'); it's useless to use null auto_increment=  -; Indicates default starting from 1Create TableClass (Cidint(5) not NULLAuto_increment,//note Self-growth is an int type namevarchar( -),  Primary Key(Cid));Create TableXuesheng (student_idint(5) not NULLauto_increment, namevarchar( -), ageint,  Primary Key(student_id),Foreign Key(student_id)Referencesclass (Cid));1: Insert is performed first intoXueshengValues(NULL,'Jack',' +'); The student's ID depends on the class's CID and cannot create a student on its own without a class. 2: Insert is performed first intoClassValues(NULL,'c001'); Insert Student Action again intoXueshengValues(NULL,'Jack',' +'), you can succeed. ----------------function-------------------------SelectNow (),current_date()Select *  fromTableNamewhereField1 like’%Value%’--find data in a specific format, note that the middle is <%%>Sum:Select sum(field1) asSumvalue fromtablename Average:Select avg(field1) asAvgvalue fromtablename Max:Select Max(field1) asMaxValue fromTableName min:Select min(field1) asMinValue fromtablename Sort by:Select *  fromTableNameOrder  byField1,field2[desc]Select the first 10 records:Select *  fromTableName LimitTen; (Be careful not to use top) to split pages:Select *  fromTableName Limit2,4(i.e. remove 3rd to 6th, 4 Records)Select ASCII('A');//corresponds to 65 a string that returns a binary value n representing the Select Bin (8);//8 of Bits------------------------------------Import the. sql File command (for example, D:/daoru.sql) So you can write directly in the text and execute it in the import cmd: Create TableDaoru (Namevarchar( -), Passwordvarchar( -));Insert  intoDaoruValues('JACK','123'); Execute MySQL>SOURCE D:/Mysql.sql;----------------------------

MySQL Common command operations