123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178 |
- docker search mysql
- docker pull mysql
- docker images
- docker run -d --name=msqdb -e MYSQL_ROOT_PASSWORD='hjkl;' -v /storage/msqdb/mysql-datadir:/var/lib/mysql mysql
- docker ps -a
- docker start msqdb
- docker ps
- docker exec -it msqdb bash
- cat /etc/os-release
- service --status-all
- apt-get update
- apt-get install openssh-server openssh-client
- service ssh status
- service ssh start
- useradd -m raman
- set password for user raman:
- passwd raman
- [hjkl;']
- apt-get install sudo vim
- vi /etc/passwd
- raman:x:1000:1000::/home/raman:/bin/sh
- raman:x:1000:1000::/home/raman:/bin/bash
- sysvinit - /etc/init.d/mysql
- systemd - /lib/systemd/sytem/mysql.service
- Relational Database Management System - RDBMS
- For help with using MySQL, please visit the MySQL Forums, where you can discuss your
- issues with other MySQL users.
- mysql -u root -p
- mysql -u user -p
- mysql -u user -p dbname
- select version(), current_date;
- # Creating a Database and Tables
- CREATE DATABASE bookstore;
- SHOW DATABASES;
- USE bookstore
- CREATE TABLE books (book_id INT,
- title VARCHAR(50),
- author VARCHAR(50) );
- DESCRIBE books;
- # Analyzing and Manipulating Data
- SELECT COUNT(*)
- FROM books
- JOIN authors USING(author_id)
- WHERE author_list = 'Tolstoy';
- # sqlite - use SQL without a database server
- # Relational Database Concepts
- A database server can contain many databases
- Databases are collections of tables
- Tables are two-dimensional with rows (observations) and columns (variables)
- Limited Mathematical and summary operations available
- Very good at combining information from several tables
- SHOW DATABASES;
- SHOW TABLES IN database;
- SHOW COLUMNS IN table;
- DESCRIBE table; # shows the columns and their types
- # Variable Types
- SQL supports a very large number of different formats for internal storage
- of information.
- Numeric - INTEGER, SMALLINT, BIGINT
- NUMERIC(w,d), DECIMAL(w,d) - numbers with width w and d decimal places
- show databases;
- use <databasename>;
- show tables in <databasename>;
- show columns in <tablename>; is equvalent to "describe <tablename>;"
- show rows in <tablename>; # results in error
- create table childrens(id char(6),
- race smallint,
- age decimal(6,3),
- height decimal(7,3),
- weight decimal(7,3),
- sex smallint);
- insert into childrens values(100011, 2, 10.346, 148.5, 38.95, 1);
- We can automate the process using the LOAD DATA command:
- load data infile 'childrens.tab'
- into table childrens
- fields terminated by '\t';
- This will read an entire tab-separated file into the database in one command
- select * from childrens; # to see all of the data in table childrens
- # Created Database: library and Table: booklist
- Reference: MYSQL_in_a_Nutshell.pdf
- create database library;
- use library;
- create table booklist(
- subject varchar(50),
- title varchar(75),
- author varchar(50));
- describe booklist; # similar to show columns in booklist;
- alter table booklist
- change column subject book_id int auto_increment primary key,
- change column title subject varchar(20),
- change column author title varchar(75),
- add column author varchar(50),
- add column edition varchar(20);
- show databases;
- show tables;
- insert into booklist
- (subject, title, author, edition)
- values('Linux', 'Introduction to Linux', 'Machtelt Garrels', '1.27 Edition');
- select * from booklist;
- select book_id, title, author, edition
- from booklist
- where subject = 'Linux';
- select count(*)
- from booklist
- where subject = 'Linux';
- update booklist
- set edition = '1 Edition'
- where book_id = '2';
- delete from booklist
- where author = ' Michael Stutz';
- # database schema
- show columns from clients like 'c%i%';
- alter table clients
- change column city client_city varchar(100) character set 'latin2',
- modify column client_state char(4) after client_city;
- # add column in a table
- alter table <table_name> add column <column_name> <data_type>;
- # set same value to all rows in a column
- update <tabel_name> set <column_name> = 'value' where 1 = 1;
- # rename a column
- alter table <table_name> change <old_column> <new_column> <data_type>;
- # drop a column from a table
- alter table <table_name> drop column <column_name>;
- # list first 10 records from a table
- select * from <tablename> limit 10; # select rows 1 to 10
- select * from <tablename> limit 0, 10; # select rows 01 to 10
- select * from <tablename> limit 10, 10; # select rows 11 to 20
- select * from <tablename> limit 10 offset 10; # select rows 11 to 20
- # execute sql script in MySQL
- mysql> source /home/raman/Scripts/datadef.sql
|