MySQL
Install MySQL with mySQL Workbench
Open Command Prompt
>cd C:\Program Files\MySQL\MySQL Server 8.0\bin
>mysql -u root -p <or> mysql -u root -p<password>
mysql> quit
>mysqld [inside the C:\Program Files\MySQL\MySQL Server 8.0\bin]
>mysqladmin -u root -p shutdown
---------------------------
mySQL workbench
create a new connection
/In qurey type[tab]/
create database <name of the database> [press>control+enter for result]
show databases
use <database name>
create table users (username text)
[creating a table with a <name> as users and inside the braket username and "text" is the type of the username]
show tables
desc users
insert into users (username) values("Babu")
[interting a <Babu> "string inside a "users" database within "username" table]
select * from users
drop table users
[To delete the users in the "users" table ]
show tables
create table users (id int, username text)
desc users
insert into users (id, username) value (1, "Babu")
insert into users (id, username) value (2, "Rahul")
show tables
select * from users
insert into users (id, username) value (null, null)
[Search in google.com for the mySQL data type to assign the data type for the table types]
create table users (id int not null, username text not null)
show engines
show table status
[Two type engine are used mostly are MyISAM (oldest) and InnoDB]
create table test(id int) engine=MyISAM
drop table test
set default_storage_engine=MYISAM
show tables
show tables status
show engines
create table test(id int)
show table status
show engines
set default_storage_engine=INNODB
select @@GLOBAL.SQL_MODE
select @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE
set sql_mode = "STRICT_ALL_TABLES"
select @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE
insert into users (id) value(3)
[it will result as error, ]
set sql_mode = "NO_ENGINE_SUBSTITION"
delete from users
[ will not work, safe mode has to be removed to delete]
select @@SESSION.SQL_SAFE_UPDATES
set sql_safe_updates = 0
delete from users [Now it will delete]
create table users (id int primary key, name text, email text)
[creating a primary key, which will not to be duplicated]
drop table users [before doing clean up the data]
create table users (id int primary key auto_increment, user text)
[Auto Increment of primary key]
insert into users (user) value ("Rahul")
insert into users (user) value ("Hariesh")
insert into users (id,user) value (0,"Satheesh")
select * from users
select * from users where id=3
select * from users where user = 'Satheesh'
select id, user from users where user = 'Satheesh'
delete from users where id=2
set sql_safe_updates = 0
delete from users where user = 'Hariesh'
Import data to MySQL
data export >>> Export to Self-contained file >>>> C:\Users\PS\Documents\dumps\Dump20230922.sql >>> start export
data Import >>> Export to Self-contained file >>>> C:\Users\PS\Documents\dumps\Dump20230922.sql >>> start import
Command line
[inside the C:\Program Files\MySQL\MySQL Server 8.0\bin]
>mysqldump --help
>mysqldump -u root -p -h localhost <database name>
[exporting - copy to a specific location]
>mysqldump -u root -p -h localhost tutorial1 > k:/mysql/world.sql
[importing]
>mysql -u root -p -h localhost
mysql> show databases;
mysql> create database temp;
mysql> quit
>mysql -u root -p -h localhost temp < k:/mysql/world.sql
[importing the sql database file]
select count(*) from users
[counting how many row in the tables]
[check online for MySQL operators for such more...]
select * from users where age=25
[where checking in the table who are the age of ...]
select * from users where age>25
select * from users where age<25
select * from users where name != "Bob" [not equal to]
select * from users where name is null [null]
select * from users where name is not null [not null]
select * from users where name like "%on" [letter with ....]
select * from users where age > 20 and age < 30;
select * from users where age >= 20 and age <= 30;
select * from users where (age >= 20 and age <= 30) and not name = "Vicky";
select * from users where (name not like '%e%') or (name is null);
select * from users where (age > 30 and age < 40) and (name like '%o%' or name like '%e%');
select * from users where name like '%o%' xor id < 5;
update users set name = "Sath" where id=2
[update <table> set <data> = <repalace> where <id>]
update users set name = "Sath" where name = "Pete" and id=8
[will not change to update]
set sql_safe_updates=0
update users set name = "Sath" where name = "Pete"
select * from users order by name
select * from users where id < 20 order by name
select * from users where id < 20 order by name desc
select * from users where id < 20 order by id desc
select * from users where id < 20 order by name asc
select * from users where id < 20 order by name asc, age desc
select * from users where id < 20 order by age asc, name desc
select * from users where id < 20 order by age, name
select * from users order by age limit 5
select * from users order by id limit 5
select * from users order by id limit 1,5
[in limit starting next from 1 and display next 5 rows]
[search in google for mySQL VARCHAR]
create table test(zip_code char(7), name varchar(60), width float)
insert into test(zip_code, name) values ('1234567', 'sath', 24.25)
create table test (value_ decimal(4,2))
[decimal is a option to count total digits and after dot number]
[bit 0 and 1]
create table test (bitfield bit(4))
insert into test(bitfield) values (b'1100')
set sql_safe_updates=0
update products set available = ture where product = "Gold"
select * from test [Result will be 12]
select bin(bitfield) from test [Result will be 1100]
create table products (product varchar(100), available bool default false)
desc products
insert into products (product, available) values ("Electric cycle", false)
insert into products (product, available) values ("Moter cycle", true)
[in the boolean 1 is true and 0 is false]
select * from products
select * from products where available = true
set sql_safe_update = 0
update products set available = true where product = 'Electric cycle';
select * from products
insert into products (product) values ('Two wheeler')
select * from products
create table image (name varchar(50), data blob)
insert into image (name, data) values ("srirahas.jpg", "somecode")
select * from image
select "Hello"
select 12
select 12 from users
select 12, name from users
select now() [returns Date and time]
select year(now())
select year(now()), time(now()), month(now())
create table moments(id int primary key auto_increment, theYear year, theTime time, theDate date)
desc moments
insert into moments(theYear, theDate, theTime) values ('2023', '2022-09-24', '05:30:12');
select * from moments
insert into moments(theYear, theDate, theTime) values (year(now()), date(now()), time(now()));
create table products(name varchar(60), sold_at timestamp default now(), received datetime);
insert into products(name, sold_at, received) values ('Law is attraction', '2022-09-24 11:17:15', '2022-09-24 08:17:15');
select * from products
select year(sold_at), time(sold_at), date(received) from products
insert into products(name, received) values ('Law is attraction', now());
select * from products