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

two。 Modify the table (key points)

A) modify the table name

ALTER TABLE table name RENAME TO new table name

B) modify the data type of the field

ALTER TABLE table name MODIFY field name data type attribute index

C) modify the field name

ALTER TABLE table name CHANGE old field name new field name data type attribute index

D) add fields

ALTER TABLE table name ADD field name data type attribute index [FIRST | AFTER field name]

If first and after are not written later, the default is to add to the end of the field.

E) Delete fields

ALTER TABLE table name DROP field name

ERROR 1091 (42000): Can't DROP 'phone'; check that column/key exists

Deleting a field that does not exist will report the above error

F) modify the arrangement of fields

ALTER TABLE table name MODIFY field 1 data type property index AFTER field 2

G) modify the table engine

ALTER TABLE table name ENGINE = engine name

When there is a large amount of data, it is not recommended to modify the table engine as above, but to first create a table like the myuser table, and then modify the engine of the empty table to the engine we want to modify, and then import the data into the new table.

H) change the self-increment to the current maximum

=

Give an example to illustrate the addition, deletion, modification and search

ENGINE=MyISAM DEFAULT CHARSET=utf8

INSERT INTO myuser VALUES (NULL,'user1',md5 ('123456'), 18j0pi 1510023998' do not abandon, do not give up, dare to take on, dare to shine the sword')

INSERT INTO myuser VALUES (NULL,'user2',md5 ('123456'), 180Pol 1510023998' make learning a habit')

INSERT INTO myuser VALUES (NULL,'user3',md5 ('123456'), 180.Pol 1510023998' make me a habit')

-- modify the table name

ALTER TABLE myuser RENAME TO user1

ALTER TABLE user1 RENAME TO myuser

-- modify field data types

ALTER TABLE myuser MODIFY age INT NOT NULL DEFAULT 0

ALTER TABLE myuser MODIFY age TINYINT UNSIGNED NOT NULL DEFAULT 0

-- modify the field name

ALTER TABLE myuser CHANGE age myage TINYINT UNSIGNED NOT NULL DEFAULT 0

ALTER TABLE myuser CHANGE myage age TINYINT UNSIGNED NOT NULL DEFAULT 0

-- add fields

ALTER TABLE myuser ADD city VARCHAR (255) NOT NULL DEFAULT 'Global Village'

ALTER TABLE myuser ADD lovegood SET ('Qiu Liang', 'avi','mp4','vob','girl') NOT NULL FIRST

ALTER TABLE myuser ADD tel CHAR (11) NOT NULL AFTER age

-- deleting a field

ALTER TABLE myuser DROP lovegood

ALTER TABLE myuser DROP city

ALTER TABLE myuser DROP tel

ALTER TABLE myuser DROP phone

-- the position of the field

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER age

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER sex

-- modify the table engine

ALTER TABLE myuser ENGINE = InnoDB

ALTER TABLE myuser ENGINE = MyISAM

When there is a large amount of data, it is not recommended to modify the table engine as above, but to first create a table like the myuser table, and then change the engine of the empty table to the engine we want to modify, and then import the data into the new table.

-- change the self-increment to the current maximum

ALTER TABLE myuser AUTO_INCREMENT = 0

CREATE TABLE IF NOT EXISTS info (

Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

Name VARCHAR (255) NOT NULL

Age TINYINT UNSIGNED NOT NULL DEFAULT 0

Sex TINYINT NOT NULL DEFAULT 0

City VARCHAR (255) NOT NULL DEFAULT 'Beijing'

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- add data to all values of all fields

INSERT INTO info VALUES (NULL,' Ma Hongxiang', 45pc0pl 'Nanjing')

INSERT INTO info VALUES (NULL,' Wenzhuo', 38pc0pr 'Nanning')

INSERT INTO info VALUES (NULL,' Jiejie', 0jie 26jie 'Chengdu')

-- list all fields

INSERT INTO info (id,name,age,sex,city) VALUES (NULL,' Lin Lin', 16pc0pm 'Chengdu')

INSERT INTO info (id,name,age,sex,city) VALUES (NULL,' Debao', 78pm 1 recorder 'Hangzhou')

-- specify field insertion

INSERT INTO info (name,age,sex) VALUES ('Li Xiang', 150150)

INSERT INTO info (name,age,sex) VALUES ('Xue Lin', 17pr 3)

INSERT INTO info (name,age,sex) VALUES ('Sisi', 14. 0)

INSERT INTO info (name,age,sex) VALUES ('Li Shuai Xiang', 14. 0)

-insert multiple pieces of data at the same time

INSERT INTO info (id,name,age,sex,city) VALUES (NULL,' Yanyan, 16pyrr0) Zhengzhou), (NULL,' Wenshuai, 24pyrr1), (NULL,' Liangliang, 98pyrr2) Jinan), (NULL,' Saoli, 84pi 1meme 'Ningbo'), (NULL,' Little Prince Ma Li, 56pi 1meme'Xi'an'), (NULL,' Zhang Sanpi, 110mem2 'Xuzhou'), (NULL,' Tianyi Brother, 7pi 2mi 'Shenyang')

INSERT INTO info (id,name,age,sex,city) VALUES (NULL,' Jiehui', 2jin1 dint 'Zhengzhou'), (NULL,' Fangfang', 18pc0je'Xi'an'), (null,' Wang Xiaoer, 18pyrrine 1 'Hotel'), (null,' Wang Zhongwang', 255pyrrine 4 'Shuanghui'), (null,' Xiao Wang', 255pd4)), (null,' next door Lao Wang', 43pl 1))

INSERT INTO info (id,name,age,sex,city) VALUES (NULL,' Ball', 18jre 1 dint 'Tokyo hot'), (null,' pouch', 18 pouch 1 dagger 'Osaka'), (null,' Bai GE', 18pr 1 'Hokkaido'), (null,' Heige', 18jue 1jue 'Europe and America')

-- modify data

UPDATE info SET age=66,sex=0 WHERE id=3

-- Delete data

DELETE FROM info WHERE id=26

-- list all fields

SELECT id,name,age,sex,city FROM info

-- use * query

SELECT * FROM info

-- query specified fields

SELECT id,name FROM info

-compare: =

=! =

-- specified range: BETWEEN AND NOT BETWEEN AND

-- specify the collection: IN NOT IN

-- matching character LIKE NOT LIKE

-- whether it is a null IS NULL IS NOT NULL

-- query AND OR with multiple conditions

-compare: =

=! =

SELECT id,name,age,sex,city FROM info WHERE id = 10

SELECT id,name,age,sex,city FROM info WHERE id 10

SELECT id,name,age,sex,city FROM info WHERE id = 10

SELECT id,name,age,sex,city FROM info WHERE id! = 10

SELECT id,name,age,sex,city FROM info WHERE id 10

-- specified range: BETWEEN AND NOT BETWEEN AND id is greater than or equal to 15 and less than or equal to 20

SELECT id,name,age,sex,city FROM info WHERE age BETWEEN 15 AND 20

SELECT id,name,age,sex,city FROM info WHERE age NOT BETWEEN 15 AND 20

-- specify the collection: IN NOT IN

SELECT id,name,sex,age,city FROM info WHERE id IN (22, 7, 14, 15, 15, and 2)

SELECT id,name,sex,age,city FROM info WHERE id NOT IN (22, 7, 14, 15, 15, and 2)

-- matching character LIKE NOT LIKE

-- the fuzzy query character _ represents one character% represents more than 0 1 characters

SELECT id,name,sex,age,city FROM info WHERE name LIKE'Li _'

SELECT id,name,sex,age,city FROM info WHERE name LIKE'Li _'

-- starts with the specified character

SELECT id,name,sex,age,city FROM info WHERE name LIKE 'King%'

-ends with a specified character

SELECT id,name,sex,age,city FROM info WHERE name LIKE'% King'

-- all those with middle characters come out!

SELECT id,name,sex,age,city FROM info WHERE name LIKE'_ King%'

SELECT id,name,sex,age,city FROM info WHERE name NOT LIKE'% King%'

-- whether it is a null IS NULL IS NOT NULL

-- is NULL

SELECT id,name,sex,age,city FROM info WHERE name IS NULL

-- IS NOT NULL

SELECT id,name,sex,age,city FROM info WHERE name IS NOT NULL

-- query AND OR with multiple conditions

-- find out all those with the word Wang and are older than 17.

-- AND

SELECT id,name,sex,age,city FROM info WHERE name LIKE'% King% 'AND age > 17

-- query all the data with the word Li or gender 0.

-- OR

SELECT id,name,sex,age,city FROM info WHERE name LIKE'% Lee%'OR sex=0

-- value query gender

SELECT sex FROM info

-- remove the duplicates in the result DISTINCT

SELECT DISTINCT sex FROM info

Please find out the location of the characters in our class

SELECT DISTINCT city FROM info

-- ORDER BY sorting

-- ASC grew up

SELECT id,name,sex,age,city FROM info ORDER BY age ASC

-- DESC from big to small

SELECT id,name,sex,age,city FROM info ORDER BY age DESC

-- those whose ID is less than 25 are sorted by id from largest to smallest.

If there is a where condition, orderby needs to be written after the where condition.

SELECT id,name,sex,age,city FROM info WHERE id < 25 ORDER BY age DESC

SELECT id,name,sex,age,city FROM info WHERE id < 25 ORDER BY age DESC,id DESC

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.