Shulou(Shulou.com)06/02 Report--

This article will explain in detail what are the commonly used sentence commands in the MYSQL database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Common statement commands in MYSQL database

1. Start the MySQL service

Net start mysql

two。 Log in to the MySQL database

Mysql-u root-p # or mysql-h localhost-u root-p

3. Database view deletion

# View all databases SHOW DATABASES; # create a database CREATE DATABASE KTX # Delete a database DROP DATABASE KTP # use this database USE k

3. Database table viewing and operation

# View the SHOW TABLES of all the tables in the database; # check the structure of the table (the following five sentences have the same effect) DESC nscape # because it is simple, it is recommended to use DESCRIBE nposition show COLUMNS FROM nmitry explain nten # to create a table CREATE TABLE n (id INT, name VARCHAR (10); CREATE TABLE m (id INT, name VARCHAR (10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n (id), UNIQUE (name)) CREATE TABLE m (id INT, name VARCHAR (10)); # directly import or copy the query results to the newly created table CREATE TABLE n SELECT * FROM mbot # the data structure of the newly created table is similar to that of an existing table # create a temporary table # temporary tables will exist during your connection to MySQL When disconnected, MySQL automatically deletes the table and frees up the space used. It can also be deleted manually. CREATE TEMPORARY TABLE l (id INT, name VARCHAR (10)); # directly import or copy the query results to the newly created temporary table CREATE TEMPORARY TABLE tt SELECT * FROM nbot # delete an existing table DROP TABLE IF EXISTS mash # change the name of the existent table ALTER TABLE n RENAME mbot rename TABLE n TO mbot # view the creation statement SHOW CREATE TABLE n of the table

4. Database table structure view

# add field ALTER TABLE n ADD age VARCHAR (2); # Delete field ALTER TABLE n DROP age;# change field properties and properties ALTER TABLE n CHANGE age an INT;# only change field properties ALTER TABLE n MODIFY age VARCHAR (7)

5. Database table data

# add data INSERT INTO n VALUES (1, 'tom',' 23'), (2, 'john',' 22'); INSERT INTO n SELECT * FROM n; # copy the data again and reinsert # delete data DELETE FROM n WHERE id = 2th # change data UPDATE n SET name = 'tom' WHERE id = 2th # data lookup SELECT * FROM n WHERE name LIKE'% h%' # data sorting (reverse order) SELECT * FROM n ORDER BY name, id DESC

6. Database table key

# add primary key ALTER TABLE n ADD PRIMARY KEY (id); ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id); # there is only one primary key, so it seems useless to define the key name # delete primary key ALTER TABLE n DROP PRIMARY KEY; # add foreign key ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n (id); # automatically generate key name m_ibfk_1ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n (id) # delete the foreign key ALTER TABLE m DROP FOREIGN KEY `fk_ id` using the defined key name fk_id#; # modify the foreign key ALTER TABLE m DROP FOREIGN KEY `key id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n (id); # add the unique key ALTER TABLE n ADD UNIQUE (name); ALTER TABLE n ADD UNIQUE u_name (name); ALTER TABLE n ADD UNIQUE INDEX u_name (name); ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name) from the new # after deletion CREATE UNIQUE INDEX u_name ON n (name); # add index ALTER TABLE n ADD INDEX (age); ALTER TABLE n ADD INDEX i_age (age); CREATE INDEX i_age ON n (age); # delete index or unique key DROP INDEX u_name ON n and drop INDEX i_age ON n

7. Database view

# create view CREATE VIEW v AS SELECT id, name FROM nutter VIEW v (id, name) AS SELECT id, name FROM nscape # View view (similar to table operation) SELECT * FROM vposition DESC SHOW CREATE VIEW # View create view statement SHOW CREATE VIEW vten # change view CREATE OR REPLACE VIEW v AS SELECT name, age FROM nten alter create view # delete view DROP VIEW IF EXISTS v

8. Database connection

# Inner join SELECT * FROM m INNER JOIN n ON m.id = n.idten # left outer join SELECT * FROM m LEFT JOIN n ON m.id = n.idtern # right outer join SELECT * FROM m RIGHT JOIN n ON m.id = n.idtern # cross join SELECT * FROM m CROSS JOIN nten # standard writing SELECT * FROM m, nten # join usage SELECT id,name FROM mUNIONSELECT id,name FROM n similar to full join full join

9. Database function

# aggregate function SELECT count (id) AS total FROM n; # Total SELECT sum (age) AS all_age FROM n; # summation SELECT avg (age) AS all_age FROM n; # average SELECT max (age) AS all_age FROM n; # maximum SELECT min (age) AS all_age FROM n; # minimum # Mathematical function SELECT abs (- 5) # absolute values SELECT bin (15), oct (15), hex (15); # binary, octal, hexadecimal SELECT pi (); # pi 3.141593SELECT ceil (5.5); # the minimum integer value 6SELECT floor (5.5) that is greater than x; # the maximum integer value 5SELECT greatest less than x. # returns the smallest value in the set, 1SELECT mod (5); # remainder 2SELECT rand (); # returns a random value from 0 to 1, different SELECT rand (5) each time; # provides a parameter (seed) that causes the RAND () random number generator to generate a specified value. SELECT round (1415.1415); # rounded 1415SELECT round (1415.1415, 3); # rounded three places 1415.142SELECT round (1415.1415,-1); # rounded integer places 1420SELECT truncate (1415.1415, 3); # truncated to 3 decimal places 1415.141SELECT truncate (1415.1415,-1); # truncated to-1 decimal place 1410SELECT sign (- 5) The negative value of the # symbol-1SELECT sign (5); the positive value of the # symbol 1SELECT sqrt (9); # square root 3SELECT sqrt (9); # square root square root string function SELECT concat ('aural,' paired, 'paired,' le'); # concatenated string-appleSELECT concat_ws (',', 'averse,' paired, 'le') # concatenate the string with', 'split the string-chinese', pjournal pjournal leselect insert (' chinese', 3, 2, 'IN'); # replace the 2 characters of the string' chinese' from position 3 with 'IN'-chINeseSELECT left (' chinese', 4); # return the 4 characters to the left of the string 'chinese'-chinSELECT right (' chinese', 3) # returns the three characters to the right of the string 'chinese'-eseSELECT substring (' chinese', 3); # returns the substring-ineseSELECT substring after the third character of the string 'chinese' (' chinese',-3); # returns the substring-eseSELECT substring after the third but last character of the string 'chinese' (' chinese', 3,2) # returns two characters after the third character of the string 'chinese'-inSELECT trim (' chinese'); # cuts the empty characters on both sides of the string 'chinese' -' chinese'SELECT ltrim ('chinese'); # cuts the empty characters on both sides of the string' chinese'-'chinese'SELECT rtrim (' chinese'); # cuts the empty characters on both sides of the string 'chinese' -' chinese'SELECT repeat ('boy', 3) # repeat characters' boy' three times-'boyboyboy'SELECT reverse (' chinese'); # reverse sort-'esenihc'SELECT length (' chinese'); # return string length-7SELECT upper ('chINese'), lower (' chINese'); # uppercase and lowercase CHINESE chineseSELECT ucase ('chINese'), lcase (' chINese'); # uppercase and lowercase CHINESE chineseSELECT position ('i' IN 'chinese') # return the first position of'i' in 'chinese'-3SELECT position (' e' IN 'chinese'); # return the first position of' i' in 'chinese'-5SELECT strcmp (' abc', 'abd'); # compare strings, the first parameter is less than the second return negative number-1SELECT strcmp (' abc', 'abb') # compare strings. The first parameter is greater than the second return positive number-SELECT current_date, current_time, now (); # 2018-01-13 12:33:43 2018-01-13 12:33:43SELECT hour (current_time), minute (current_time), second (current_time); # 1231 34SELECT year (current_date), month (current_date), week (current_date) # 2018 1 1SELECT quarter (current_date); # 1SELECT monthname (current_date), dayname (current_date); # January SaturdaySELECT dayofweek (current_date), dayofmonth (current_date), dayofyear (current_date) # 7 131 control stream function SELECT if (3 > 2, 'tweets,' f'), if (3 file.sqlmysqldump-u root-p db_name table_name > file.sql# database restore mysql-u root-p < file.sql about what are the common sentence commands in MYSQL database? this article ends here. I hope the above can be helpful to you so that you can learn more knowledge. If you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.