MySql common query command operation list

MYSQL commonly used query commands:

mysql> select version(); View the version number of MySQLmysql> select current_date(); View the current date of MySQLmysql> select version(),current_date(); View the version number and current date of MySQL at the same timemysql> show databases; Display the currently existing databasesmysql> USE mysql Select to use the database (USE and QUIT commands do not need to end with a semicolon)
Database changed
mysql> select database(); Displays the currently selected databasemysql> show tables; Displays the tables in the current databasemysql> select * from db; Displays the contents of the table (db)mysql> describe mytable; Displays the structure of the table

Or show columns from table name;

mysql> select
-> user()
-> \c
mysql>

Practice the following:

mysql> select
-> USER()
-> ,
->now()
->;
mysql> Select (20+5)*4;
mysql> Select (20+5)*4,sin(pi()/3);
mysql> Select (20+5)*4 AS Result, sin(pi()/3); (AS: specifies the pseudonym as Result)

View the number of connections currently occupied by MySQL users

Command: show processlist;

If you are the root account, you can see all users currently connected. If it is any other common account, you can only see the connections you occupy.

show processlist; only the first 100 items are listed. If you want to list all items, please use show full processlist;

mysql> show processlist;

one. database:

mysql> CREATE DATABASE abccs; Create a databasemysql> USE abccs Select to use the databasemysql> drop database database name; Delete the database

two. surface:

1. Create a table mytable:

mysql> CREATE TABLE mytable
-> (
-> name VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> birthaddr VARCHAR(20)
-> );

Create an employee birthday table, which contains employee name, gender, date of birth, and city of birth.

Since the column values ​​of name and birthadd are variable, VARCHAR is selected, and its length is not necessarily 20.

You can choose any length from 1 to 255. If you need to change its word length later, you can use the ALTER TABLE statement.

Gender can be represented by just one character: "m" or "f", so CHAR(1) is used;

The birth column uses the DATE data type.

2. Query the newly added records:

mysql> select * from mytable;

Empty set (0.00 sec) This means that the table just created has no records yet.

3. Add a new record:

mysql> insert into mytable 
-> values
->(
->'abccs',
->'f',
->'1977-07-07',
->'china'
->);

4. Load data into a database table using text mode:

It is too troublesome to add new records one by one.

Create a text file "mysql.txt", arrange each line and each record according to the table structure, and separate the values ​​with tabs.

abccs f 1977-07-07 china 
Mary F 1978-12-12 USA
tom m 1970-09-02 usa

Use this command to load the text file "mytable.txt" into the table:

mysql> Load data local infile "mytable.txt" into table mytable;

load data local infile "file name" into table table name;

Note: You'd better copy the file to the mysql/bin directory, and first use the use command to select the library where the table is located.

5. Update log:

mysql> update mytable set birth = "1973-09-02" where name = "tom";

6. Deleting records:

mysql> delete from mytable where id=10; //Delete all records with id=10;
mysql> delete from mytable where id=10 limit 1; //Limit the deletion of 1 record with id=10;
mysql> delete from mytable //Delete all records in a table;
mysql> DELETE FROM t1 WHERE C>10;
mysql> drop table tablename1,tablename2,…; //Delete an entire table or multiple tables, use with caution.

7. Rename the table:

mysql> alter table t1 rename t2;

8. Modify the mysql table structure:

View the mysql table structure:

mysql> describe mytable; or use show columns from table name;

Modify field properties

mysql> alter table tablename modify id int(10) unsigned auto_increment primary key not null

Modify the default value

mysql> alter table tablename alter id default 0

Add a primary key to the field

mysql> alter table tablename add primary key(id);

Deleting the primary key

alter table tablename drop primary key;
drop primary key on tablename;

Modify table data engine

mysql> alter table tableName ENGINE = MyISAM (InnoDB);

Add a new field name:

mysql> alter table mytable add column single char(1);
mysql> ALTER TABLE table ADD field INT(11) UNSIGNED NOT NULL

Delete a field

mysql> alter table t2 drop column c;

Attachment:

To change column a from INTEGER to TINYINT NOT NULL (same name),

And change column b from CHAR(10) to CHAR(20) and rename it from b to c:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR(20);

Add a new TIMESTAMP column named d:

ALTER TABLE t2 ADD d TIMESTAMP;

Add an index on column d and make column a the primary key:

ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Add a new AUTO_INCREMENT integer column named c:

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);

Note that we indexed c because the AUTO_INCREMENT column must be indexed.

And additionally we declare c to be NOT NULL, because indexed columns cannot be NULL.

Create an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name(10));

three. Data backup and recovery:

Export and import data: (The command is executed in the mysql/bin directory of DOS)

Export Table

mysqldump --opt school > school.sql

Note: Back up all tables in the database school to the school.sql file. school.sql is a text file.

You can choose any file name, open it and you will find something new.

mysqldump --opt school teacher student > school.teacher.student.sql

Note: Back up the teacher table and student table in the school database to the school.teacher.student.sql file. school.teacher.student.sql is a text file with any file name. Open it and you will find something new.

Import Table

mysql
mysql>create database school;
mysql>use school;
mysql>source school.sql;

(Or replace school.sql with school.teacher.sql / school.teacher.student.sql)

Export Database

mysqldump --databases db1 db2 > db1.db2.sql

Note: Back up the databases dbl and db2 to the db1.db2.sql file. db1.db2.sql is a text file.

Take any one, open it and you will find something new.

(For example:

mysqldump -h host -u user -p pass --databases dbname > file.dump

That is, import the database dbname with the name user and password pass on the host into the file file.dump. )

Importing a database

mysql < db1.db2.sql

Copy database mysqldump --all-databases > all-databases.sql

Note: Back up all databases to the all-databases.sql file. all-databases.sql is a text file with any file name.

Importing a database

mysql
mysql>drop database a;
mysql>