دستوراتی که در MySQL استفاده میکنم

پشتیبان گرفتن از دیتابیس ها

$ mysqldump -uroot -proot --all-databases > backup.sql
$ mysqldump -uroot -proot my_database > backup.sql
# compress backup
$ mysqldump -uroot -proot my_database | gzip > backup.gz

$ mysqldump -uroot -proot --all-databases > backup.sql

$ mysqldump -uroot -proot my_database > backup.sql

# compress backup

$ mysqldump -uroot -proot my_database | gzip > backup.gz

چک کردن سلامت دیتابیس ها

$ mysqlcheck --verbose -h host -u root -p --auto-repair --optimize --all-databases

1	$ mysqlcheck --verbose -h host -u root -p --auto-repair --optimize --all-databases

دستورات مهم

# Load a csv file to table
LOAD DATA INFILE '/home/input.csv' INTO TABLE mytable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

# Rename table
RENAME TABLE mytable TO _mytable

# Set auto increment number to 1
ALTER TABLE mytable AUTO_INCREMENT=1

# Add index to column
ALTER TABLE mytable ADD INDEX inedex_name(field);

# Add unique index to column
ALTER TABLE mytable ADD UNIQUE INDEX index_name(field);

# Remove index
ALTER TABLE mytable DROP INDEX index_name;

# Insert data from another table
INSERT INTO mytable(id, name) SELECT id,name FROM y;

# Create database with character set
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Change database character set
ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# See what's going on with table
SHOW TABLE STATUS WHERE name LIKE 'x';

# Load a csv file to table

LOAD DATA INFILE '/home/input.csv' INTO TABLE mytable

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

# Rename table

RENAME TABLE mytable TO _mytable

# Set auto increment number to 1

ALTER TABLE mytable AUTO_INCREMENT=1

# Add index to column

ALTER TABLE mytable ADD INDEX inedex_name(field);

# Add unique index to column

ALTER TABLE mytable ADD UNIQUE INDEX index_name(field);

# Remove index

ALTER TABLE mytable DROP INDEX index_name;

# Insert data from another table

INSERT INTO mytable(id, name) SELECT id,name FROM y;

# Create database with character set

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Change database character set

ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# See what's going on with table

SHOW TABLE STATUS WHERE name LIKE 'x';

دیدگاهتان را بنویسید لغو پاسخ