MySql command operations

MySql command operations

Common MYSQL query commands:

Mysql> select version (); view MySQL version mysql> select current_date (); view MySQL's current date mysql> select version (), current_date (); view the MySQL version number and the current date mysql> show databases; display the current database mysql> USE mysql select to USE the database (USE and QUIT commands do not need to end with a semicolon) database changedmysql> select database (); displays the currently selected Database mysql> show tables; displays the tables in the current database mysql> select * from db; displays the tables (db) mysql> describe mytable; displays the table structure.

Or show columns from table name;

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

Perform the following operations:

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); (: specify a pseudonym as Result)

View the number of connections currently occupied by MySQL users

Command:show processlist;

If it is a root account, you can see the current connection of all users. For other common accounts, you can only view the connections you are using.

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

mysql> show processlist;

I. Database:

Mysql> create database abccs; CREATE a database mysql> USE abccs select use database mysql> drop database DATABASE database name; delete DATABASE

II. Table:

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 containing the employee name, gender, birth date, and city of birth.

Because the column values of name and birthadd change, 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 font length later, you can use the alter table statement.

Gender can be expressed by only one character: "m" or "f". Therefore, 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) indicates that the table created just now has no records.

3. Add new record:

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

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

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

Create an example file named "“mysql.txt", arrange each record in each row according to the table structure, and separate the values with the "tab.

abccs f 1977-07-07 china mary f 1978-12-12 usatom m 1970-09-02 usa

Use this command to load the Upload File named mytable.txt to the table:

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

Data Import commandload data local infile "File name"into table Table Name;

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

5. Update record:

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

6. Delete record:

Mysql> delete from mytable where id = 10; // delete all records with id = 10; mysql> delete from mytable where id = 10 limit 1; // delete one record with id = 10; mysql> DELETE from mytable // delete all records of a table; mysql> delete from t1 where c> 10; mysql> drop table tablename1, tablename2 ,...; // Delete an entire table or multiple tables. Be careful when using this operation.

7. Rename a table:

mysql> alter table t1 rename t2;

8. Modify the table structure of mysql:

View the mysql table structure:

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

Modify Field attributes

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

Modify Default Value

mysql> alter table tablename alter id default 0

Add primary key to the field

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

Delete 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 Field

mysql> alter table t2 drop column c;

Appendix:

To change column a from INTEGER to tinyint not null (same name ),

Change Column B from CHAR (10) To CHAR (20), rename it, and change from B to c:

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

Add a new TIMESTAMP column named d:

ALTER TABLE t2 ADD d TIMESTAMP;

Add an index on column d and set column a as 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 have indexed c because the AUTO_INCREMENT column must be indexed,

In addition, we declare that c is not null because the indexed Column cannot be NULL.

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

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

3. Data backup and recovery:

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

Export table

mysqldump --opt school > school.sql

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

Select the file name. open the file to see if you have any new discoveries.

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

Note: Back up the teacher and student tables in the school database to school. teacher. student. SQL file, school. teacher. student. SQL is a text file with any file name. Open it and you will find new information.

Import table

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

(Or change school. SQL to school. teacher. SQL/school. teacher. student. SQL)

Export Database

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

Note: Back up database dbl and db2 to the db1.db2. SQL file. db1.db2. SQL is a text file with a file name.

Choose, open it, and you will see new discoveries.

(For example:

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

Import the database dbname on the host named "user" and "password pass" to file. dump .)

Import Database

mysql < db1.db2.sql

Copy Databasemysqldump --all-databases > all-databases.sql

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

Import Database

mysqlmysql>drop database a;mysql>