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

Let's learn about the detailed explanation of mysql additions, deletions, changes and searches. I believe you will benefit a lot after reading it. The text is not in the essence. I hope the detailed explanation of mysql additions, deletions, changes and searches is what you want.

1. Increase

(1) create a database dks

?

1create database dks

(2) create a table named T1 and specify the engine and character set

?

1create table T1 (id int,name varchar (20) not null,ages int) engine=innodb default charset=utf8

(3) insert data. Double quotation marks are required for character types.

?

1insert into T1 (id,name,ages) values (1, "zhangsan", 28)

(4) insert multiple pieces of data

?

1insert into T1 (id,name,ages) values (5, "xiaohong", 58), (5, "xiaoming", 68)

(5) add a column after it

?

1alter table T1 add job varchar (20)

(6) add a column of city after the id column

?

1alter table T1 add city tinyint (2) after id

two。 Delete

(1) Delete database dks

?

1drop database dks

(2) Delete table T1

?

1drop table t1

(3) clear the contents of the table

?

1delete from t1

(4) Delete job column

?

1alter table t1 drop column job

(5) Delete data, filter ages=18 data with where condition, and delete

?

1delete from t1 where ages=18

3. Check

(1) View all databases

?

1show databases

(2) access to dks database

?

1use dks

(3) check how many tables are in the database

?

1show tables

(4) View the data content in T1 table

?

1select * from T1

(5) only view the id column

?

1select id from t1

(6) check the id and name columns

?

1select id,name from t1

(7) query data with ages greater than 20 and name equal to "zhangsan"

?

1select * from T1 where ages > 20 and name= "zhangsan"

(8) query data with ages greater than 20 and name equal to "zhangsan" and id not equal to 1

?

1select * from T1 where ages > 20 and name= "zhangsan" and id! = 1

(9) use the in parameter to specify multiple lines

?

1select * from where id in (2pm 3)

(10) use the not in parameter to exclude multiple lines

?

1select * from T1 where id not in (2jol 3)

(11) Fuzzy query, using wildcard% query;% is equivalent to the * sign in linux, with all

?

12select * from T1 where name like "xiao%"; # query all xiao-related characters

(12) one underscore can be assigned to one character, and multiple underscores can match multiple characters.

?

1select * from T1 where name like "xiao_"

(13) View only the first three rows of data

?

1select * from T1 limit 3

(14) View the two rows of data after the third row

?

1select * from T1 limit 3

(15) sort data from large to small

?

1select * from T1 order by ages desc

(16) sort the data from small to large

?

1select * from T1 order by ages asc

(17) specify libraries, tables and fields, and query T1

?

1select dks.t1.name from t1

(18) View the database character set

?

1show variables like'% char%'

(19) View the mysql database storage engine

?

1show engines

(20) View the mysql default storage engine

?

1show variables like'% storage_engine%'

4. Change

(1) both change and modify can modify the definition of the table. In different cases, the column name needs to be written twice after change, which is inconvenient. Column names can be modified when changge has its advantages, while modify cannot.

(2) modify the column name

?

12alter table T1 change age ages int;# changes age to ages

(2) modify field type and length

?

1alter table T1 modify column ages varchar (10)

(3) judge and modify the data of id=3

?

12update T1 set id=2 where id=3;# changes the data of id=3 to id=2

(4) modify the content of name field

?

1update t1 set name='zhangsan' where id=1

(5) modify the storage engine of T1 table in mysql

?

1alter table t1 engine=innodb

After reading this article on the detailed explanation of mysql additions, deletions, changes and searches, many readers will certainly want to know more about it. If you need more industry information, you can follow our industry information section.

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.