Below when you see # it means from the unix shell. Di bawah ini ketika Anda melihat # berarti dari shell unix. When you see mysql> it means from a MySQL prompt after logging into MySQL. Bila Anda melihat mysql> berarti dari prompt MySQL setelah login ke MySQL.
To login (from unix shell) use -h only if needed. Untuk login (dari unix shell) use-h hanya jika diperlukan.
# [mysql dir]/bin/mysql -h hostname -u root -p # [Mysql dir] / bin / mysql-h hostname-u root-p
Create a database on the sql server. Buat database di server sql.
mysql> create database [databasename]; Mysql> membuat database [databasename];
List all databases on the sql server. Daftar semua database di sql server.
mysql> show databases; Mysql> show database;
Switch to a database. Beralih ke database.
mysql> use [db name]; Mysql> use [db name];
To see all the tables in the db. Untuk melihat semua tabel dalam database.
mysql> show tables; Mysql> show tabel;
To see database's field formats. Untuk melihat format kolom database.
mysql> describe [table name]; Mysql> menjelaskan [nama tabel];
To delete a db. Untuk menghapus sebuah db.
mysql> drop database [database name]; drop mysql> database [nama database];
To delete a table. Untuk menghapus tabel.
mysql> drop table [table name]; mysql drop> table [nama tabel];
Show all data in a table. Tampilkan semua data dalam tabel.
mysql> SELECT * FROM [nama tabel];
Returns the columns and column information pertaining to the designated table. Mengembalikan kolom dan kolom informasi yang berkaitan dengan tabel yang ditunjuk.
mysql> show columns from [table name]; Mysql> menunjukkan kolom dari [nama tabel];
Show certain selected rows with the value "whatever". Tampilkan dipilih baris tertentu dengan nilai "apapun".
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever"; mysql> SELECT * FROM [nama tabel] WHERE [field name] = "apapun";
Show all records containing the name "Bob" AND the phone number '3444444'. Tampilkan semua catatan yang berisi nama "Bob" DAN nomor telepon '3444444 '.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444'; mysql> SELECT * FROM [nama tabel] WHERE name = "Bob" DAN PHONE_NUMBER = '3444444 ';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field. Tampilkan semua catatan tidak mengandung nama "Bob" DAN urutan nomor telepon '3444444 'oleh medan PHONE_NUMBER.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number; mysql> SELECT * FROM [nama tabel] WHERE name = "Bob" DAN PHONE_NUMBER = order '3444444 'oleh PHONE_NUMBER!;
Show all records starting with the letters 'bob' AND the phone number '3444444'. Menampilkan semua record yang dimulai dengan 'bob' huruf DAN nomor telepon '3444444 '.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444'; mysql> SELECT * FROM [nama tabel] WHERE nama seperti "% Bob" DAN PHONE_NUMBER = '3444444 ';
Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5. Menampilkan semua record yang dimulai dengan 'bob' huruf DAN nomor telepon batas '3444444 'catatan 1 sampai 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5; mysql> SELECT * FROM [nama tabel] WHERE nama seperti "Bob%" DAN PHONE_NUMBER = batas '3444444 '1,5;
Use a regular expression to find records. Gunakan ekspresi reguler untuk mencari rekaman. Use "REGEXP BINARY" to force case-sensitivity. Gunakan "BINARY REGEXP" untuk memaksa kasus-sensitivitas. This finds any record beginning with a. Ini menemukan ada catatan dimulai dengan a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a"; mysql> SELECT * FROM [nama tabel] WHERE rec RLIKE "^ a";
Show unique records. Tampilkan catatan unik.
mysql> SELECT DISTINCT [column name] FROM [table name]; Mysql> SELECT DISTINCT [nama kolom] FROM [nama tabel];
Show selected records sorted in an ascending (asc) or descending (desc). Menampilkan record diurutkan dari ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC; Mysql> SELECT [col1], [col2] FROM [nama tabel] ORDER BY [col2] DESC;
Return number of rows. Kembali jumlah baris.
mysql> SELECT COUNT(*) FROM [table name]; mysql> SELECT COUNT (*) FROM [nama tabel];
Sum column. Sum kolom.
mysql> SELECT SUM(*) FROM [table name]; mysql> SELECT SUM (*) FROM [nama tabel];
Join tables on common columns. Gabung tabel pada kolom umum.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id; Mysql> pilih lookup.illustrationid, lookup.personid, person.birthday dari lookup kiri bergabung dengan orang di lookup.personid pernyataan = = person.personid untuk bergabung ulang tahun dalam tabel orang dengan id ilustrasi primer;
Creating a new user. Membuat pengguna baru. Login as root. Login sebagai root. Switch to the MySQL db. Beralih ke db MySQL. Make the user. Buat pengguna. Update privs. Update privs.
# mysql -u root -p # Mysql-u root-p
mysql> use mysql; Mysql> menggunakan mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); mysql> INSERT INTO user (Host, User, Password) VALUES ('%',' username ', PASSWORD (' password '));
mysql> flush privileges; flush privileges mysql>;
Change a users password from unix shell. Mengubah password user dari shell unix.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password' # [Mysql dir] / bin / mysqladmin-u username-h hostname.blah.org-p password 'new-password'
Change a users password from MySQL prompt. Mengubah password user dari prompt MySQL. Login as root. Login sebagai root. Set the password. Set password. Update privs. Update privs.
# mysql -u root -p # Mysql-u root-p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); @ 'Hostname' 'user' mysql> SET PASSWORD UNTUK = PASSWORD ('passwordhere');
mysql> flush privileges; flush privileges mysql>;
Recover a MySQL root password. Recover password root MySQL. Stop the MySQL server process. Hentikan proses server MySQL. Start again with no grant tables. Mulai lagi dengan tidak ada tabel hibah. Login to MySQL as root. Login ke MySQL sebagai root. Set new password. Pengaturan sandi baru. Exit MySQL and restart MySQL server. Keluar dan restart MySQL server MySQL.
# /etc/init.d/mysql stop # / Etc / init.d berhenti / mysql
# mysqld_safe --skip-grant-tables & # Mysqld_safe - skip-grant-table &
# mysql -u root # Mysql-u root
mysql> use mysql; Mysql> menggunakan mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root'; mysql update user> set password = PASSWORD ("newrootpassword") di mana User = 'root';
mysql> flush privileges; flush privileges mysql>;
mysql> quit Mysql> berhenti
# /etc/init.d/mysql stop # / Etc / init.d berhenti / mysql
# /etc/init.d/mysql start # / Etc / init.d start / mysql
Set a root password if there is on root password. Mengatur password root jika ada pada password root.
# mysqladmin -u root password newpassword # Mysqladmin-u root password Newpassword
Update a root password. Update password root.
# mysqladmin -u root -p oldpassword newpassword # Mysqladmin-u root-p Newpassword oldpassword
Allow the user "bob" to connect to the server from localhost using the password "passwd". Biarkan pengguna "bob" untuk terhubung ke server dari localhost dengan menggunakan password "passwd". Login as root. Login sebagai root. Switch to the MySQL db. Beralih ke db MySQL. Give privs. Berikan privs. Update privs. Update privs.
# mysql -u root -p # Mysql-u root-p
mysql> use mysql; Mysql> menggunakan mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd'; penggunaan hibah mysql> pada *.* untuk bob @ localhost diidentifikasi oleh 'passwd';
mysql> flush privileges; flush privileges mysql>;
Give user privilages for a db. Berikan hak istimewa pengguna untuk db. Login as root. Login sebagai root. Switch to the MySQL db. Beralih ke db MySQL. Grant privs. Grant privs. Update privs. Update privs.
# mysql -u root -p # Mysql-u root-p
mysql> use mysql; Mysql> menggunakan mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N'); mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES ('%',' databasename ',' username ',' Y ',' ',' Y Y ', 'Y', 'Y', 'N');
mysql> flush privileges; flush privileges mysql>;
or atau
mysql> grant all privileges on databasename.* to username@localhost; Mysql> memberikan semua hak istimewa pada databasename .* ke localhost username @;
mysql> flush privileges; flush privileges mysql>;
To update info already in a table. Untuk memperbarui info sudah dalam sebuah tabel.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; mysql> UPDATE [nama tabel] SET Select_priv = 'Y', Insert_priv 'Y' =, Update_priv 'Y' = mana [field name] = 'user';
Delete a row(s) from a table. Menghapus baris (s) dari tabel.
mysql> DELETE from [table name] where [field name] = 'whatever'; mysql> DELETE dari [nama tabel] mana [field name] = 'apa';
Update database permissions/privilages. Update database perizinan / hak istimewa.
mysql> flush privileges; flush privileges mysql>;
Delete a column. Hapus kolom.
mysql> alter table [table name] drop column [column name]; Mysql> alter table [nama tabel] drop kolom [nama kolom];
Add a new column to db. Tambah kolom baru ke db.
mysql> alter table [table name] add column [new column name] varchar (20); Mysql> alter table [nama tabel] menambah kolom [nama kolom baru] varchar (20);
Change column name. Ubah nama kolom.
mysql> alter table [table name] change [old column name] [new column name] varchar (50); Mysql> alter table [nama tabel] perubahan [kolom nama lama] [nama kolom baru] varchar (50);
Make a unique column so you get no dupes. Membuat kolom yang unik sehingga Anda tidak mendapatkan dupes.
mysql> alter table [table name] add unique ([column name]); Mysql> alter table [nama tabel] menambahkan unik ([nama kolom]);
Make a column bigger. Membuat kolom yang lebih besar.
mysql> alter table [table name] modify [column name] VARCHAR(3); Mysql> alter table [nama tabel] memodifikasi [kolom name] VARCHAR (3);
Delete unique from table. Hapus unik dari tabel.
mysql> alter table [table name] drop index [colmn name]; Mysql> alter table [table name] drop index [colmn nama];
Load a CSV file into a table. Load file CSV ke tabel.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); '/ Tmp / filename.csv' mysql> LOAD DATA INFILE menggantikan KE DAFTAR [nama tabel] BIDANG DIHENTIKAN OLEH ',' LINES '\ n' DIHENTIKAN OLEH (field1, Field2, field3);
Dump all databases for backup. Dump semua database untuk backup. Backup file is sql commands to recreate all db's. file Backup adalah perintah sql untuk menciptakan semua db's.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql # [Mysql dir] / bin / mysqldump-u root-ppassword - opt> / tmp / alldatabases.sql
Dump one database for backup. Dump satu database untuk backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql # [Mysql dir] / bin / mysqldump-u username-ppassword - database> databasename / tmp / databasename.sql
Dump a table from a database. Dump tabel dari database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql # [Mysql dir] / bin / mysqldump-c-u username-ppassword databasename tablename> / tmp / databasename.tablename.sql
Restore database (or database table) from backup. Restore database (tabel atau database) dari backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql # [Mysql dir] / bin / mysql-u username-ppassword <databasename / tmp / databasename.sql
Create Table Example 1. Membuat Contoh Tabel 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255)); mysql> CREATE TABLE [nama tabel] (firstname VARCHAR (20), VARCHAR middleinitial (3), lastname VARCHAR (35), suffix VARCHAR (3), officeid VARCHAR (10), userid VARCHAR (15), username VARCHAR (8) , email VARCHAR (35), telepon VARCHAR (25), kelompok VARCHAR (15), stempel tanggal DATE, waktu timestamp, pgpemail VARCHAR (255));
Create Table Example 2. Membuat Tabel Contoh 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato'); Mysql> membuat tabel [nama tabel] (int personid (50) tidak null auto_increment primary key, firstname varchar (35), middlename varchar (50), lastnamevarchar (50) 'Bato' default);
Share it to your friends..!
 |
0 komentar:
Post a Comment