Mysql
Published: 13:19, Thursday 14 July 2011
Notes
What's this? See my article about Notes.
View as plain text file.
install:
apt-get install mysql-client mysql-server
asks for root password directly (cool!)
standard password: ""
start/stop:
(sudo /etc/rc.d/init.d/mysql start)
sudo service mysql start
mysqladmin -u root -p shutdown
init database: /usr/bin/mysql_install_db
db location: /var/lib/msql/
config file: /etc/mysql/my.cnf
root password:
assign root password: mysqladmin -u root password 'new-password'
reset under windows:
stop mysql service
mysql -u root --skip-grant-tables
go to user table and modify password (or insert) using PASSWORD()
login/connect to mysql client
mysql -h host -u root -p
mysql -u root -p
add user
USE mysql;
new user: CREATE USER user@localhost IDENTIFIED BY pwd;
INSERT INTO user (Host, User, Password, Select_priv) VALUES ('', 'Dudel', password('supersecret'), 'Y');
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON db.* TO Dudel;
FLUSH PRIVILEGES;
dump:
mysqldump > file.sql
transfer/copy database
mysqldump --opt db | mysql --host=host-name -C database
i have my own bash script for it now
rename database
not possible
use the bash script for copying, then remove
import/restore: mysql database < dump.sql
backup names: [remote_]dbname_year_month_day.sql
only table creations: --no-data
foreign keys: SET foreign_key_checks = 0; first
truncate database: my own script mysql_truncate_database
phpmyadmin:
config:
sudo vi /etc/dbconfig-common/phpmyadmin.conf
sudo dpkg-reconfigure phpmyadmin
will be in localhost/phpmyadmin
operations:
select database: USE db;
list dbs: SHOW DATABASES;
list tables: SHOW TABLES;
list columns: show columns;
create db
CREATE DATABASE db;
create database if not exists db;
mysqladmin -h localhost -u root -p create dbname
in /var/lib/mysql/dbname
drop database:
mysqladmin -u root -p drop dbname
create table
CREATE TABLE tb (field TYPE(SIZE)) ENGINE engname;
create table if not exists ...
column types:
auto_increment, not null
primary key: primary key (id)
foreign key
foreign key (col) references Table(col) on delete/update opt
opt: restrict | cascade | set null | no action
needs table engine InnoDB
index: index (col)
unique
unique (col)
a type of index
alter table tb add unique (col)
constraints:
check is not supported by mysql! even though they accept it in the syntax!!!
at w3schools they don't mention it! http://www.w3schools.com/Sql/sql_check.asp
load data (tab-delimited): LOAD DATA LOCAL INFILE "file.txt" INTO TABLE tb
insert row
INSERT INTO tb (cols) VALUES ('val1', 'val2');
insert or update/on duplicate: INSERT ... ON DUPLICATE KEY UPDATE ...,
update: update tb set col=expr ..
delete: delete from tb where
select: SELECT col1, col2 or * FROM tb WHERE conds;
select unique: SELECT DISTINCT col FROM tb;
join
select * from tb left join tb2 on expr;
left join: take all from left and map if possible with right
join: take all which can be mapped with right
right join: take all from right and map if possible with left
sort: SELECT cols FROM tb ORDER BY col2[ DESC];
pattern matching:
normal
WHERE col like 'pattern';
any: %
one: _
regexp
WHERE name REGEXP 'pattern';
aggregate/group operations:
count rows: SELECT COUNT(*) FROM tb;
count groups: SELECT col, COUNT(*) FROM tb GROUP BY col;
max value: SELECT MAX(col) AS label FROM tb;
compare with all: select * from tb where col >= all (subselect);
average: avg
percentage
SELECT foo.c, COUNT(*) * t.factor AS pct FROM foo JOIN (SELECT 100/COUNT(*) AS factor FROM foo) AS t GROUP BY foo.c;
databases and tables:
select from more tables: SELECT tb.col, col FROM t1, t2 WHERE t1.c = t2.c
selected db: SELECT DATABASE();
rename table: rename table old to new
drop table: DROP TABLE tb
drop all tables
using a bash script
alter:
add column
ALTER TABLE tb ADD COLUMN [col] AFTER c;
ALTER TABLE tb ADD col type;
rename column/alter column/change column:
(alter table tb change col col type;)
alter table tb modify col type;
add primary key:
alter table tb add primary key;
add foreign key:
alter table tb add foreign key (col) references table (col)
drop foreign key:
... drop foreign key constraint_id
find constraint_id with 'show create table'
remove column/delete column/drop column: ALTER TABLE tb DROP COLUMN col;
batch mode/execute script/execute file: mysql -u user -p db < file.sql
execute query/execute statement: -e
backup: mysqldump --opt -u uname -p > backup.sql
backup: mysqldump --opt -u uname -p > backup.sql
minus
select_statement minus select_statement doesn't work
select distinct cols from t1 left join td using (cols) where b.sth is null
check null: where col is null
views:
create view name as (select ...)
drop view
can update! (only propagatable, e.g. avg doesn't)
can't delete from join view
engine:
alter table name engine = innodb;
in my.cnf default-storage-engine = innodb
see engine: select engine from information_schema.tables where table_name = 'tb'
watch
my own script mysqlwatch
mysqlwatch db query
transactions:
start transaction
commit
abort: rollback
character sets/charsets/latin/utf8:
set: in my.ini: default-character-set=utf8
create database db default character set utf8;
change:
manually:
for all dbs, tables, cols
alter database db charset=utf8;
alter table tb charset=utf8;
alter table tb alter column col charset=utf8;
alternative:
dump all, recreate and import
iconv
string:
concat: concat("asd","asd")
Write a Comment
* These fields are mandatory.