MySQL Common commands

1. Create/Delete Database Create/DROP DATABASE DATABASENAME;2, creating a data Table CREATE TABLE table_name (COL_NAME1, Col_type [ notNULL],#Null is not allowed, the default can be null if not written, [] represents writable and writableCol_name2, Col_type [ notNULL], Col_name3, Col_type [ notNULL]) Example: Create TABLE t2 (R1 int notnull,r2 varchar (5) notnull);3, switch data table directory use TableName;4, constraint constraint: You want to insert data to do a check, such as gender only male and female, other values can not join the constraint is the data of the Prosecutor, when the non-conforming data, will not insert the database PRIMARY KEY constraints: Ensure entity integrity, primary key cannot be duplicated, cannot be empty such as: PRIMARY Keycreate TABLE USERS (user_qq VARCHAR (20) not NULL PRIMARY key,user_name VARCHAR (50) not null,user_sex CHAR (2not NULL,) foreign KEY constraint: The data on one table is derived from another table to guarantee referential integrity such as: represents the creation of the score table, the USER_QQ in the table originates from the USER_QQ column in the user table, and if not, the insert fails the CREATE TABLE SCORES ( User_qq VARCHAR (20) NOT null REFERENCES USERS (USER_QQ), GNO int not NULL, SCORE int. NOT NULL) CHECK constraints: Guaranteed domain integrity, where a value on a column conforms to a format or a range such as: to Game number greater than 0CREATEE TABLE games (GNO INT not NULL CHECK (GNO>0), Gname VARCHAR (50) not NULL, GTYPE VARCHAR (20Not NULL) Default constraint: Guaranteed domain integrity, such as player gender default to male create TABLE USERS (user_sex VARCHAR (2) Not NULL DEFAULT'male') Self-increment column: Guaranteed entity full create TABLE games (GNO INT not NULL auto_increment,)5, view the basic structure of the table describe/DESC table_namefield: Field Name type: field type null: Nullable key: Whether or not to index default: Extra: Additional information, such as self-add columns to view the detailed structure of the table, you can find all the statements created show Create_table table_name plus \g can format results6Modify table name change Table old_name RENAME to new_name modify field name ALTER TABLE Table_namechange Old_name new_name new_type such as: Alter TABLE gameschange GNO game_id VARCHAR (20Modify the field data type ALTER TABLE Table_namemodify col_name new_type such as: ALTER TABLE gamesmodify GNO VARCHAR (20add field ALTER TABLE Table_nameadd new_col_name new_type delete field ALTER TABLE Table_namedrop col_name supplemental PRIMARY KEY constraint ALTER TABLE Table_ Nameadd CONSTRAINT col_nameprimary Key (col_name) such as: Note constraint name, PRIMARY KEY constraint PK, foreign key is Fkalter TABLE usersadd constrint pk_users_ Userqqprimary Key (USERQQ) Add foreign KEY constraint alter TABLE f_tableadd CONSTRAINT con_nameforeign KEY (f_col) REFERENCES m_table (M_col) Example: Alter TABLE Scoresadd CONSTRAINT fk_scores_gamesforeign KEY (GNO) REFERENCES Games (GNO) Add check constraint ALTER table Table_nameadd CONSTRAINT Con_namecheck (EXP) such as: ALTER TABLE gamesadd CONSTRAINT ck_games_gnocheck (GN0>0) Add default ALTER TABLE Table_namealter col_name set defaults value such as: ALTER TABLE Usersalter user_sex set default'male'Add a self-adding column alter TABLE table_namemodify column col_name ... Auto_increment such as: Alterr TABLE' Games'MODIFY COLUMN'GNO'INT not NULL auto_incrementprimary KEY7, insert data Insert value for all columns, column value same number, column value same order INSERT [into] table_name values (V1,V2,V3 ...) such as: INSERT into Usersvalues ('2011-01-22','Sunday','male','1985-09-08','13800110022') INSERT into Usersvalues ('2011-01-22','Sunday', DEFAULT,'1985-09-08','13800110022'Insert values for a specific column, specify the order, and the column values correspond to insert [into] table_name (COL1, COL2 ...) VALUES (V1,v2 ...) Example: INSERT into USERS (USER_QQ, user_name, User_mobile) VALUES ('1545','DDAFG','1585435535654'insert multiple records at one time insert [into] table_name[(col1,col2 ... Coln)]valurs (V11,v12 ... V1N), (V21,v22 ... V2N), (V31,v32 ... V3N) ... such as: INSERT into USERS (USER_QQ, user_name, User_birthday, User_mobile) VALUES ('1545','DDAFG','1938-04-25','1585435535654')('1545','DDAFG','1983-04-25','1585435535654'Modify data to modify all data update Table_nameset{col_name=EXPRESSION} [,... N] such as: UPDATE usersset user_sex='male'UPDATE Scoresset Score= score + 1008, modify specific data update Table_nameset{col_name=EXPRESSION} [,... N]where condition_expression such as: UPDATE usersset user_sex='female'WHERE user_qq='12315'9Delete data deletes data using delete command deleting [from] table_name[where condition_expression] such as: delete from USERS WHERE user_sex='female'use TRUNCATE TABLE to delete all data in the table TRUNCATE TABLE table_nametruncate table SCORES10, query Select Col1,col2,.. Colnfrom Table1,table2 ... Tablen[where CONDITION]#Query Criteria[GROUP by Group_by_list]#To group the results of a query[Having CONDITIONS]#are statistical results as query criteria[ORDER by order_list[asc| DESC]]#sort the results of the statisticsquerying all rows and columns of a table select*Partial column of the From Table query table Select USER_QQ, user_name from the users alias using select USER-qq as'player QQ', user_name as'player Name'From usersselect USER-qq'player QQ', user_name'player Name'From USERS#Remove asDISTINCT Query Results eliminate duplicate rows Select DISTINCT user_qq from Scoreslimit Specify the display range of data in the result set select* FROM USERS LIMIT 2,3#Query section-show a total of several dataQuery general condition query such as: query QQ number 12301 player information Select* from USERS WHERE user_qq ='12301'such as: Query score is also greater than 2500 points of data Select* FROM SCORES WHERE score > 2500If you query the game number 1 and the score is greater than 4000 of the score information select* FROM SCORES WHERE gno= 1 and score> 4000Fuzzy query between andsuch as: Query scores from 2500 to 3000, including the boundary select* FROM SCORES WHERE score >= 2500 and score <= 3000SELECT* FROM SCORES WHERE score between 2500 and 3000a select that is not between 2500 and 3700* FROM score WHERE score not between 2500 and 3700wildcard characters:'_'a character%any length [] within the specified range [^] Not in parentheses, such as querying all the kings of the player, pay attention to use Likeselect* from the USERS WHERE user_name like'Wang%'querying for null values, mainly with is NULL, was not nullselect*From the USERS WHERE user_birthday is null sorts the query result sort on a single column, that is, sort by column, sorted by , sorted by, sort by: query all the score information in the score table numbered 1, and sort by fractional ascending select * FROM SCORES WHERE gno=1 ORDER by score asc/desc Sort multiple columns: Sort by, sort by, prioritize, pay attention to the conditions, such as: Query the score table all information, and according to the game number of ascending and fractional descending order select*From SCORES ORDER by GNO ASC, score desc When an order by is followed by two conditions, the preceding precedence is the highest commonly used aggregate function sum () avg () min () max () count () Etc: Query how many players in a player's table information select COUNT (name) from Namesselect count (*from NAME unless all the columns on the line are empty etc: Query QQ number is 12301 of the total number of players in the game Select SUM (score) FORM score WHERE user_qq='12301'etc: Query QQ number is 12302 of the average score of the player select AVG (score) from SCORES WHERE user_qq='12302'etc: Query QQ number is 12302 of the player's total, average, and highest score select SUM (score) as'Total', AVG (Score) as'Average score', MAX (Score) as'Highest score'From SCORES WHERE user_qq ='12302'grouped in result set, group byetc: query each player's total score, average score, highest fraction select USERS, SUM (score) as'Total number', AVG (Score) as"', MAX (Score) as"'from SCORES Group by users filter group results when using group BY, the HAVING clause is used to further set the statistic condition for grouping statistics, and the HAVING clause is the filter condition based on the statistical results of the aggregate function. Having written after group by query average score greater than 4000 players QQ number total, average score select User_qq, SUM (score) as'Total number', AVG (Score) as'Average score'From SCORES GROUP by USER_QQ have AVG (score) > 4000statistical results can also be sorted by querying the average score and total scores of all users and sorting by the average score in reverse order select USER_QQ, AVG (score) as'Average score', SUM (Score) as'Total number'from SCORES GROUP by user_qq Order by AVG (score) Descselect from the WHERE Gboup by aggregate function having an ORDER by multi-table join query to query scores from three tables respectively Info, show player nickname, game name and score select User_name as'Nickname', Gname as'Game Name', score as'score'From the users, games, scoreswhere users. USER_QQ= Score. User_qq and games. GNO =SCORES. Multiple table names appear directly behind the Gnofrom, which is an inner join, an implicit inner join display within the join format: Faster select col_list from TABLE1 [INNER] Join TABLE2 on TABLE1. COL=TABLE2. Colselect user_name as"', Gname as"', score as"'From SCORES INNER JOIN in games. gno=SCORES. Gnoinner JOIN USERS on SCORES. USER_QQ=USERS. User_qqselect user_name'Nickname', SUM (Score) as"', AVG (Score) as"'From USERS U INNER JOIN SCORES SON s.users_qq=u.users_qq GROUP by U.USERS_QQ, user_name outside connection: Do link two table status is different, there is a table is the base table, the underlying table data must appear, there is no null and left outer joins when the left table is the base table, right outer joins when the right table is the base table, The first table that appears is the left select col_list form TABLE1/right [OUTER] JOIN TABLE2 on TABLE1. Col=TABLE2. Col, for example, asked to query all players about the number 5th game score information, the base table is the player table, some players do not play the game will appear select User_name as"', GNO as"', SCORES as"'From the USERS U left JOIN SCORES S on u.user_qq=s.users_qq and S.gno = 5Subqueries in, subqueries are not generally written as select* fromSELECT* from 1111 where NUM in (SELECT NUM from 2222 where NAME ="'The exists keyword, if later established, executes the outer statement of the Select*From SCORES WHERE EXISTS (SELECT* from USERS WHERE users_name ='Sunwukong'Union Query Union [ALL] links multiple results vertically as one, with the ALL keyword showing all data (even if duplicate data is displayed) SELECT user_name from Usersunion [All]select GAME from Gamesselect+The string indicates the column name query QQ number is 12301 of the players all scores and calculates the total score, the average score, shows to the same result set select User_qq, GNO, score from SCORES WHERE user_qq='12301'UNION Allselect'Total' ' ', SUM (score) from SCORES WHERE user_qq ='12301'UNION Allselect'Average score',' ', AVG (score) from SCORES WHERE user_qq ='12301'

MySQL Common commands