I’ve been working on ecshop recently and realized I’ve forgotten a ton of MySQL, so let me review it.
name is the database, name1 is table 1, and so on
create database name character set ‘utf8’; create a database and its encoding (one-step creation)
crate database name; create a database
show databases; view database names (list databases)
use name; select a database (enter the database)
drop database name; drop a database
update table set field1=value1,field2=value2 where id=number; update the database; if a field value is a literal value it must be quoted, number is a stand-in for the numeric id
ceate table name1(id name email phone); create a data table
auto_increment auto numbering
primary key the primary key
unique key constraint key
desc name1; view the table structure
show columns from name1; view the table structure
default max; default value
select * from name1; view the data table’s data
show create database name; view the database encoding
alter database character set ‘utf8’; set the database encoding
alter table table modify field new_data_type; modify a field’s data type
alter table table change field new_field new_data_type; modify the field and the data type
alter table table add new_field data_type; add a field first optional parameter set the newly added field as the first field of the table after add the newly added field after the specified existing field alter table table
drop field; delete a field
alter table table modify field1 data_type
first/after field2; modify the field position (first/after)
alter table table add field data_type
first/after; add a field at the beginning/end of the table
alter table table
ENGINE |CHARSET=MyISAM | utf8; modify the engine or encoding
alter table table
drop foreign key foreign_constraint_key; delete the table’s foreign constraint key
alter table child_table
drop foregin key foreign_key; remove the child-parent table association
alter table table rename new_table_name; modify the table name
select name from table; query categories ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
select distinct category from table; the queried field must not repeat select username from name1 where username=’admin’; query the admin belonging to the field username in the data table * select id,usename,email from name1 where id in (1,20) order by username; query the records in the data table whose field username has an id from 1 to 20 adding not before in does the opposite. In real applications, for example, if you want to query the list of administrators who log in as secondary admins and don’t show the super admin while displaying the rest, then you use in. Example: select id,username,email from name1 where username not in (“admin”) order by id desc; **
select id,username from name1 where id between 1 and 20; query the id data between 1 and 20 adding not before between does the opposite select id, username from name1 where username like ‘b%’; query all data starting with b
insert name1 (id,user,pwd) values (‘1’,’123’,’admin’); write data into the table select field,field1,field2 from name1 where field2 is null; query the values of field, field1 and field2 for records in the table where field2 is empty adding not before null does the opposite select field,field1 from table where field1 like ‘_ _y’; query records in the table that end with the letter y and have only 4 letters before that y select field,field1,field2,field3 from name1 where field=’value’ and field1>=’5’; query the record data in the table where the field’s value is present and greater than 5 select field,field1,field2,field3 from name1 where field=’6’ or field=’5’; query record data whose field value is 5 or 6, a multi-field query (or multi-condition query) =select field,field1,field2,field3 from name1 where field in(5,6);
select distinct id from table; query the table’s id values, returning ids without duplicates
select distinct field_id from table; query results without duplicates
select field from table order by field; sort the query results in ascending order; for multi-column sorting, add a comma (,) after the field to separate the fields
select field1,field2 from table order by field1 desc; sort the query results in descending order by field1
select field1,field2 from table order by field1 desc,field2; multiple values sorted differently: field1 in descending order, field2 in ascending order
————————————————————————————————————————————————————
mysqldump -u user -h host -p dbname [tbname,[tbname…]]>filename.sql tbname represents a table name in the database; separate multiple tables with a space
Example: mysqldump -u user -h host -p bookDB book>D:/test/book_01.sql create a backup named book_01.sql
mysqldump -u user -h host -p —databases [tbname,[tbname…]]>filename.sql back up multiple databases
Example: mysqldump -u user -h host -p —phone bookDB book>D:/test/book_01_phone.sql create a backup named book_01_phone.sql, which contains 2 databases; using —all—databases backs up all databases
mysql -u user -p [dbname]<filename.sql mysql restore
