MySQL

2010-05-02 , Papiewski Łukasz , Serwery / Administracja / Sieci

SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]
 
 
mysqldump  --user=root -p --default-character-set=latin1 -c 
--insert-ignore --skip-set-charset  nazwa_bazy > dump.sql
 
Kopia wybranej bazy danych na dysk w formacie komendy mysql.
 
iconv -f ISO-8859-1 -t UTF-8 dump.sql > dump_utf8.sql
 
Przykład zmiany kodowania pliku, tu bazy zapisanej w iso do utf-8.
mysql --user=root -p --execute= 
"CREATE DATABASE nowa_nazwa_bazy CHARACTER SET utf8 COLLATE utf8_polish_ci;"
 
--Stworzenie nowej bazy danych z kodowanie utf-8
mysql --user=root --max_allowed_packet=16M -p 
--default-character-set=utf8 nowa_nazwa_bazy < dump_utf8.sql
CREATE DATABASE [databese name]; 
--Now we have a lot of databases so we type:
USE [DATABASE name]; 
ALTER TABLE `Category` CHANGE `descr` `description` TEXT CHARACTER 
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
CREATE TABLE users(first_name CHAR(20),second_name CHAR (30));
INSERT INTO users (first_name,second_name) VALUES ('Łukasz', 'Papiewski');
ALTER TABLE users ADD COLUMN DATE DATETIME;
SHOW COLUMNS FROM users; OR decribe users;
ALTER TABLE users CHANGE second_name secondname CHAR(30);
SELECT * FROM users;
UPDATE users SET DATE='2008-04-26 22:04:15' WHERE secondname='Papiewski';
INSERT INTO users (DATE) VALUES  (CURRENT_TIMESTAMP());
SELECT COUNT(*) FROM users;
ALTER TABLE users ADD COLUMN nr INT FIRST;
ALTER TABLE users engine = innodb;
ALTER TABLE users ADD PRIMARY KEY (nr);
UPDATE users SET nr= '0' WHERE secondname='Papiewski';
UPDATE users SET nr= '1' WHERE DATE=' 2008-04-26 22:39:28';   
UPDATE users SET firstname='Edyta', secondname='Papiewska' WHERE nr='1';
ALTER TABLE users ADD PRIMARY KEY (nr);
INSERT INTO users (firstname, secondname, DATE) VALUES 
('Aleksandra','Papiewska', CURRENT_TIMESTAMP()) ON duplicate KEY UPDATE nr=2;
SELECT MIN(DATE) FROM users AS label;
ALTER TABLE users ALTER COLUMN nr SET DEFAULT 3;
ALTER TABLE users ALTER COLUMN nr DROP DEFAULT;
CREATE INSERT INTO users (firstname,secondname,DATE) VALUES ('Damian','Zapomniałem',CURRENT_TIMESTAMP());
ALTER TABLE users DROP kdy;
ALTER TABLE users ADD COLUMN nr INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE things MODIFY id BIGINT NULL AUTO_INCREMENT FIRST;
 -  Incorrect
TABLE definition; there can be ONLY one auto COLUMN AND it must be defined
AS a KEY.
SELECT u.secondname , t.host FROM users u,things t WHERE  u.nr=t.id;
SELECT u.secondname , t.host FROM users u JOIN things t ON u.nr=t.id;
DELETE FROM users WHERE nr=5;
SET AUTOCOMMIT=0;
ALTER TABLE things AUTO_INCREMENT = 5;
LOAD DATA LOCAL INFILE "/home/papi/store.txt" INTO TABLE films;
UPDATE films SET Nr=(Nr-1)  WHERE Nr > 60 ;
UPDATE films SET english_name=UCASE(english_name) ;
SELECT @variable = 1;
ALTER TABLE films ADD UNIQUE(english_name);
ALTER TABLE tbl_name RENAME TO new_tbl_name
SELECT name FROM emp WHERE dept_no =(SELECT dept_no FROM emp WHERE name = 'JONES')
ALTER TABLE `films`  DEFAULT CHARACTER SET iso-8859-1 COLLATE latin1_polish_ci ;
 SHOW CHARACTER SET;
SHOW collation LIKE 'latin2%';
 SHOW VARIABLES LIKE 'character_set%';
 SHOW VARIABLES LIKE 'collation%'
 SET global init_connect='SET NAMES latin2';
SET NAMES 'latin2';
 GRANT ALL PRIVILEGES ON  firma TO papi@localhost 
Procedura usuwajaca
   DELETE FROM films WHERE Nr=74;
   UPDATE films SET Nr=(Nr-1)  WHERE Nr > 74; 
  SELECT MAX(Nr) FROM films;
   ALTER TABLE films AUTO_INCREMENT=220;
ALTER DATABASE 'shop' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
   SET >@@character_set_results='latin2'; 
   SET @@character_set_client='latin2'; 
   ALTER TABLE 'tbl_product' ADD UNIQUE ('pd_name')
    RENAME TABLE admin TO survey_admin;
 
    RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
 
    DELETE FROM db WHERE Db='store';
    CREATE DATABASE store;
    CREATE USER 'store'@'localhost' IDENTIFIED BY 'd6it3';
    GRANT SELECT,INSERT,DELETE,CREATE,DROP ON store.* TO 'store'@'localhost';
 
    cat /usr/share/mysql/mysql_fix_privilege_tables.sql | 
mysql --no-defaults --force --user=papi --host=127.0.0.1 --database=mysql  -p
 
 
 INSERT INTO poomla.Content(`title`,`shortTitle`,`text`,`created`,`modified`) 
SELECT `title`,`alias`,`introtext`,`created`,modified FROM jos_content;
 
UPDATE poomla.Content p, papiewski.jos_content j  SET p.text=j.fulltext WHERE j.fulltext > "" AND p.id = j.id;
 
 INSERT INTO poomla.Content(`title`,`shortTitle`,`text`,`created`,`modified`) 
SELECT `title`,`alias`,concat(`introtext`,`fulltext`),`created`,modified FROM jos_content;
 
 UPDATE poomla.Content AS p, papiewski.jos_content AS j  
LEFT JOIN papiewski.jos_categories AS cat ON j.catid=cat.id 
LEFT JOIN poomla.Category AS pcat ON pcat.name=cat.title  
SET p.categoryId=pcat.id WHERE  p.shortTitle=j.alias;

Cytaty

- Simplicity is the ultimate sophistication. - Leonardo da Vinci,
- Popularny człowiek wzbudza zawiść potężnych - Thufir Hawat o Leto Atrydzie (na Kaladanie),
- Szczęście następuje po smutku, a smutek po szczęściu; człowiek jest naprawdę wolny, gdy przestaje rozróżniać między smutkiem a szczęściem, między dobrem a złem - Aforyzmy buddyjskie.