SQL

MySQL数据库远程访问

1
2
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;

创建数据库

1
CREATE DATABASE database_name CHARACTER SET utf8;

导出数据库表

1
2
3
4
5
mysqldump -h127.0.0.1 -uusername -ppassword
database_name table_name > database_name.table_name.sql;

mysqldump -h127.0.0.1 -uusername -ppassword
database_name table_name --where="id>100" > database_name.table_name.sql;

导出数据库表结构

1
mysqldump  -hhost -P3306 -uusername -ppassword  database_name --no-data > db_name_schema.sql

导入数据库

1
2
3
mysql -h127.0.0.1 -uusername -ppassword database_name < database_name.table_name.sql;
mysql -uusername -ppassword;
> source sql_file_path;

数据从一张表导出到另一个张表

1
2
3
4
5
6
INSERT INTO database_name.table_name
(field1,
field2)
SELECT field1,
field2
FROM database_name.table_name;

根据一张表更新另一张表

1
2
3
4
UPDATE kdreader.library_book
INNER JOIN store_book
set kdreader.library_book.author_id=store_book.author_id
WHERE kdreader.library_book.name=store_book.titl;

修改表结构:增加Column

1
ALTER TABLE table_name_example ADD COLUMN field_name field_type DEFAULT default_value;

修改表结构:更新Column

1
ALTER TABLE table_name_example ALTER COLUMN field_name field_type DEFAULT default_value;

修改表结构:删除Column

1
ALTER TABLE table_name_example DROP COLUMN field_name;

修改表结构:删除unique约束

1
ALTER TABLE table_name_example DROP INDEX unique_key_name;

表重命名

1
ALTER TABLE old_name RENAME TO new_name;

增加Unique约束

1
ALTER TABLE Persons ADD CONSTRAINT uni_PersonID UNIQUE (Id_P,LastName);

分组查询

1
2
3
4
5
6
SELECT book_labels.label_id,
Count(book_labels.book_id) cnt
FROM store_book_labels AS book_labels
GROUP BY book_labels.label_id
ORDER BY cnt DESC
LIMIT 20;

Reference