mysql¶
change character
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
load data from file
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
# 如
LOAD DATA INFILE "haici-two-all.csv"
INTO TABLE enname.name
COLUMNS TERMINATED BY "\t"
(meaning, name, zh_name, sex, pronunciation, source);
display history of queries
cat ~/.mysql_history
Show Full Column Details of a Table
SHOW FULL COLUMNS FROM enname.name;
或
DESCRIBE table_name;
Counting rows
SELECT COUNT(*) FROM enname.name;
empty a table
TRUNCATE table_name;
Get length of data of column
SELECT char_length(meaning) FROM enname.name where name="zoe";
Ubuntu中配置Mysql编码
mysql的配置文件/etc/mysql/my.cnf中对应位置加入：
[client]
default-character-set = utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
Adding ID (auto increment, primary key) after table exist?
ALTER TABLE name add column Id INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(Id);
delete duplicate rows in mysql
DELETE n1 FROM name n1, name n2 WHERE n1.id > n2.id AND n1.name = n2.name;
count with condition
select count(*) from old_name where meaning = '';
select count(*) from old_name where meaning is not null;
join and dump
select * from name as n RIGHT JOIN old_name ON (old_name.name=n.name) into outfile "combined1.csv" fields terminated by '\t' lines terminated by '\r\n';
copy an existing table
CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable; # if necessary
LOAD DATA INFILE does not work
Just change the owner of a file.
1) Check permissions of the file with this command: ls -lhrt <filename>
2) Then change ownership: chown mysql.mysql <filename>
3) Now try LOAD DATA INFILE command. It will work.
Displaying Query Results Vertically
select * from name limit 10,1\G;
drop table with foreign Key
SET foreign_key_checks = 0;
drop table ...
SET foreign_key_checks = 1;