mysql learning for newbie

1. create table :
mysql> create table dc_owner (kolom_1 integer not null, kolom_2 char(20) not null);
Query OK, 0 rows affected (0.01 sec)

2. show table field/column :
mysql> show columns from dc_owner;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| kolom_1 | int(11) | | | 0 | |
| kolom_2 | char(20) | | | | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3. insert data into table :
mysql> insert dc_owner (kolom_1,kolom_2) \
-> values (1,'weleh booo');
Query OK, 1 row affected (0.01 sec)

4. Show data in table :
mysql> select * from dc_owner;
+---------+----------------+
| kolom_1 | kolom_2 |
+---------+----------------+
| 1 | weleh booo |
+---------+----------------+
1 row in set (0.00 sec)

5. add new column table :
mysql> alter table dc_owner add column kolom_3 integer;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

6. update data table :
mysql> update dc_owner set kolom_3=12345678 where kolom_2='weleh booo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dc_owner;
+---------+----------------+----------+
| kolom_1 | kolom_2 | kolom_3 |
+---------+----------------+----------+
| 1 | weleh booo | 12345678 |
+---------+----------------+----------+
1 row in set (0.00 sec)

7. modify column's type :
mysql> show columns from dc_owner;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| kolom_1 | int(11) | | | 0 | |
| kolom_2 | char(20) | | | | |
| kolom_3 | int(11) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table dc_owner modify kolom_3 int not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show columns from dc_owner;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| kolom_1 | int(11) | | | 0 | |
| kolom_2 | char(20) | | | | |
| kolom_3 | int(11) | | | 0 | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

8. change column's name (include type) :
mysql> select * from dc_owner;
+---------+----------------+----------+
| kolom_1 | kolom_2 | kolom_3 |
+---------+----------------+----------+
| 1 | weleh booo | 12345678 |
+---------+----------------+----------+
1 row in set (0.00 sec)

mysql> alter table dc_owner change kolom_1 Number int not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table dc_owner change kolom_2 Name char(20) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> alter table dc_owner change kolom_3 ID_Number int not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from dc_owner;
+--------+----------------+-----------+
| Number | Name | ID_Number |
+--------+----------------+-----------+
| 1 | weleh booo | 12345678 |
+--------+----------------+-----------+
1 row in set (0.00 sec)

9. Rename table :
mysql> show tables;
+---------------------+
| Tables_in_weleh_db |
+---------------------+
| dc_owner |
| san_info |
| server |
| server_info |
+---------------------+
4 rows in set (0.01 sec)

mysql> alter table dc_owner rename dc_list;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_weleh_db |
+---------------------+
| dc_list |
| san_info |
| server |
| server_info |
+---------------------+
4 rows in set (0.00 sec)


10. Remove column table :
mysql> alter table dc_list drop column Number;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from dc_list;
+----------------+-----------+
| Name | ID_Number |
+----------------+-----------+
| weleh booo | 12345678 |
+----------------+-----------+
1 row in set (0.00 sec)

11. add column table with auto_increment value, add index b
ecause auto_increment needs indexed and not null :
mysql> alter table dc_list add Number int unsigned not null auto_increment, add index (Number);
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from dc_list;
+----------------+-----------+--------+
| Name | ID_Number | Number |
+----------------+-----------+--------+
| weleh booo | 12345678 | 1 |
+----------------+-----------+--------+
1 row in set (0.00 sec)

mysql> insert dc_list (Name,ID_Number) \
-> values ('john doe',10001000);
Query OK, 1 row affected (0.02 sec)

mysql> select * from dc_list;
+----------------+-----------+--------+
| Name | ID_Number | Number |
+----------------+-----------+--------+
| weleh booo | 12345678 | 1 |
| john doe | 10001000 | 2 |
+----------------+-----------+--------+
2 rows in set (0.00 sec)

12. Delete database and table :
mysql> drop database binary_data;
Query OK, 0 rows affected (0.49 sec)

mysql> show databases;
+-----------+
| Database |
+-----------+
| weleh_db |
| data_db |
| mydb |
| mysql |
| test |
+-----------+

mysql> show tables;
+---------------------+
| Tables_in_weleh_db |
+---------------------+
| binary_data |
| dc_list |
| server_info |
| storage_info |
+---------------------+
4 rows in set (0.00 sec)

mysql> drop table binary_data;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_weleh_db |
+---------------------+
| dc_list |
| server_info |
| storage_info |
+---------------------+
3 rows in set (0.00 sec)


13. Show column on mysql.user table :
mysql> show columns from mysql.user
-> ;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) binary | | PRI | | |
| User | varchar(16) binary | | PRI | | |
| Password | varchar(16) binary | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)

mysql> select host,user,password from mysql.user;
+-----------+--------+------------------+
| host | user | password |
+-----------+--------+------------------+
| localhost | root | |
| batman | root | |
| localhost | | |
| batman | | |
| % | weleh | 5837db511a0b95c3 |
| localhost | weleh | 5837db511a0b95c3 |
+-----------+--------+------------------+
6 rows in set (0.00 sec)

Comments

Post a Comment

Popular posts from this blog

OSS RC : learning by doing (new beginning)

CIF Self Management: #/opt/ericsson/nms_cif_sm/bin/smtool [option] [MC] Option [stop|start|online|offline|coldrestart|warmrestart|cancel|config|help|exit] i.e : #./smtool online Activity Manager, #./smtool –list Log : Log [-print] [-type error|event|command|nwstat|security] [-number number] [-host host] [-filter filterfile[:number]] i.e #/opt/ericsson/nms_cif_sm/bin/log –type error –number 10 |more #./log –filter myfilterfile.txt:2 (means: second row of myfilterfile.txt as filter criteria) See errors as they come in, use: #/opt/ericsson/nms_cif_sm/bin/newLogRecordsViewer.sh Openfusion #cd /opt/inprise/openfusion/bin/ Environment file: # ls –la .javaenv Check version: #./version Start manager: #./manager Log is on duty: administrative state is unlocked Log will never become full : wrap Log full: halt (until persistent storage is exhausted) Err&system log: #/opt/inprise/openfusion/domains/OpenFusion/loca...
Read more

Link sudoers in linux (centos7) with Active Directory group

Image
Assignment: create Active Directory group for linux administrator who need a root privilege (sudo su) with no password required. 1. Create linuxadm group at Active Directory server with group type: security. 2. Add Active Directory user account into linuxadm group (e.g bruce wayne). 3. Add Active Directory group into sudoers profile (/etc/sudoers) using visudo on linux server. [root@labnfs ~]# visudo %BATMAN.LOCAL\\linuxadm         ALL=(ALL)       NOPASSWD: ALL 4.  check UID  before/after run "sudo su" on Linux server using Active Directory user account to confirm it. aghiel@aghiel-mbproi9 ~ % ssh bruce@labnfs bruce@labnfs's password:  [bruce@labnfs ~]$  [bruce@labnfs ~]$ id uid=215401106(bruce) gid=215400513(domain users) groups=215400513(domain users),215401120(linuxadm) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [bruce@labnfs ~]$ sudo su [root@labnfs bruce]# id uid=0(root) gid=0(root) groups=0(root) contex...
Read more

RSYNC via SSH on solaris 10

How to use rsync via ssh on solaris 10: 1. install rsync package on both (server & client) and follow the instruction : # pkgadd -d rsync-2.6.9-sol10-sparc-local (sparc) #pkgadd -d rsync-3.0.7-sol10-x86-local (x86) don't forget to install libcap also if it doesn't exist on servers (#pkginfo |grep -i libcap) : #pkgadd -d libpcap-1.1.1-sol10-sparc-local (sparc) #pkgadd -d libpcap-1.1.1-sol10-x86-local (x86) Voila! you have rsync package installed!: -bash-3.00$ which rsync /usr/local/bin/rsync -bash-3.00$ which ssh/usr/bin/ssh 2. to check whether it is worked using ssh or not : #rsync -avn -e ssh --rsync-path=/usr/local/bin/rsync remoteuser@remotehost:/dirtobecopied/ /localdir/destination 3. to try synchronize the data with option Z (compressed using gzip) : #rsync -avz -e ssh --rsync-path=/usr/local/bin/rsync remoteuser@remotehost:/dirtobecopied/ /localdir/destination 4. to limit transfer speed/bandwidth (e.g 1MBps): #rsync -avz --bwlimit=1024 --rsync-path=/usr/local/bin/rs...
Read more