Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to operate mysql for you. 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.

Administration and Management

Create table create

1. Create table [if not exists] database.tablename (custom content); for example

Mysql > CREATE TABLE NAME1 (

Mysql > ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, # set the primary key and increase it automatically

Mysql > name VARCHAR (30) NOT NULL

Mysql > age TINYINT UNSIGNED NOT NULL

Mysql > Gender ENUM ('Mauremeng F') NOT NULL DEFAULT' Manners, # specify range and default value

Mysql > INDEX index_score (score), # create index

Mysql > UNIQUE (name), # create unique key

Mysql >)

2 、 create table tab_name select col_name,.... From tab_name2 where--; copy specific content from tab_name2 to generate a new table

3. Create table tab_name like tab_name2; creates an empty table according to the table format of tab_name2

Query data

Query data in the table

SELECT

Select col_name from table_name;: filter column

WHERE condition: filter lines

= LIKE BETWEEN...AND..

! = not equal to

IN WHERE ID IN in a specific set (1)

IS NULL, IS NOT NULL

REGEXP=RLIKE regular expression

+-* /% WHERE id + 1 > 4

AND &, OR | |, NOT!

LIKE wildcard

% arbitrary

_ any single

LIMIT restricts the rows displayed, after the select statement, as shown in

Select * from tab_name limit num; displays the previous num line

Select * from tab_name limit num1,num2; displays num2 lines backwards based on num1

Data sorting

ORDER BY clo_name

Select * from test order by col_name [desc | asc]; sort by data in col_name

DESC descending order ASC ascending order default, but do not write

Data grouping

GROUP BY cloumn grouping display, HAVING combined GROUP use, HVAING filter condition

SELECT column,COUNT (*) FROM table GROUP BY column HVAING > = 10

Modify the displayed name AS

SELECT column AS name-

Modify table structure

ALTER TABLE table_name

1. Add a new column

ADD col_name col_type AFTER col_name1; add after the existing col_name1

FIRST; is added to the first

two。 Modify field

CHANGE old_col_name new_col_name nre-col-definition

MODIFY col_name col_defination; modifies the table definition

3. Add Index

ADD {INDEX | KEY} [index_name] (col_name)

Delete a field

DROP col_name

Delete key

DROP {INDEX | KEY} index_name

DROP primary key

Rename

RENAME TABLE table_name TO new_table_name

Delete tabl

DROP TABLE [IF EXISTS] table_name

Insert data

INSERT INTO table_name (col1,col2) VALUES ('val1','val2')

SET col_name='----'

Call function

Mysql > insert into pets (name,sex,birth) values ('dog','F',now ())

Insert multiple rows at a time

INSERT INTO table_name (NAME) VALUES ('-'), ('-'), ('- -')

REPLACE overrides if the inserted primary key content already exists

Modify data UPDATE

UPDATA table_name SET col_name='---' [WHERE condition] LIMIT 2

Delete data

DELETE FROM table_name [SHERE condition]

Clear a table id and start all over again

TRUNCATE TABLE table_name

This is the end of the article on "how to operate mysql". I hope the above content can be of some help 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.