Skip to main content

My SQL Basics



Database Create:


create database patientdb;



View:


show databases;


SHOW DATABASES lists the databases on the MySQL server host


+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | patientdb | | performance_schema | | sakula | | sql_hr | | sql_inventory | | sql_invoicing | | sql_store | | studentdb | | sys | | world | +--------------------+ 12 rows in set (0.0017 sec)


show create database patientdb;




Select database:

The USE statement tells MySQL to use the named database as the default (current) database for subsequent statements.


use patientdb;


Drop:


drops all tables in the database and deletes the database


drop database patientdb;



Create Table:


                create table student_table(


                stud_id int NOT NULL AUTO_INCREMENT,


                name varchar(30) NOT NULL,


                age int NOT NULL,


                PRIMARY KEY(stud_id)


                );


view table:


Display all the tables present in the database.


show tables;



Insert record:

The INSERT INTO statement is used to insert new records in a table.


It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

insert into student_table(stud_id,name,age)
values (1101,'tom',23);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);


insert into student_table

values

(1104,'sam',22),

(1106,'jack',21),

(1107,'john',21);




View records:


Display all the records.


select * from student_table;




Replace



123,xyz street,


update student_table

set address = REPLACE(address,'xyz','abc')

where stud_id = 1101;


Delete record --> limit clause:

delete from student_table where id=1102;



delete from student_table;

Delete all the records of the table


delete from student_table order by stud_id limit 2;

Delete first two records



Select column:

select name from student_table;


select name,age from student_table;


Having clause --> 


HAVING Clause is used with GROUP BY clause.

select * from student_table group by name having age > 30;
+-----+---------+------------+-----+
| id  | name    | occupation | age |
+-----+---------+------------+-----+
| 101 | Peter   | Engineer   |  32 |
| 104 | Stephen | Scientist  |  45 |
+-----+---------+------------+-----+


Add new column:


alter table student_table add column city varchar(30) NOT NULL;



alter table student_table

add column stud_class varchar(30) default null after age,

add column stud_marks int default null after age;



alter table student_table

change column name stud_name int;


Drop a column:


alter table student_table

drop column stud_marks,

drop column stud_class;


Index in sql:

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.


CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);


consider table --> 1 million records


student named michael?


select * from table where name = 'michael'


indexing  --> clustered index and non-clustered index



create index stud_index ON student_table(stud_name);


GROUP BY:


The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.



select name,count(age) as n_age from student_table GROUP By name;



Order by:

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.




select stud_id,name from student_table order by age asc;


select stud_id,name,age from student_table where stud_id>1103 order by age desc;



Insert on duplicate key update:


insert into student_table(stud_id,name,age) values (1108,'tom',23) on duplicate key update name = 'ramesh';


Comments

Post a Comment

Popular posts from this blog

Is-A and Has-A relationships in python

  In object-oriented programming, the concept of IS-A is a totally based on Inheritance, which can be of two types Class Inheritance or Interface Inheritance. It is just like saying "A is a B type of thing". For example, Apple is a Fruit, Car is a Vehicle etc. Inheritance is uni-directional. For example, House is a Building. But Building is not a House. #Is-A relationship --> By Inheritance class  A:    def   __init__ ( self ):      self .b= 10    def   mym1 ( self ):      print ( 'Parent method' ) class  B(A):    def   mym2 ( self ):      print ( 'Child method' ) d = B() d.mym1() #output: Parent method d.mym2() #output: Child method HAS-A Relationship:  Composition(HAS-A) simply mean the use of instance variables that are references to other objects. For example Maruti has Engine, or House has Bathroom. Let’s understand...
Post a Comment
Read more

Magic Methods in Python

  What Are Dunder Methods ? In Python, special methods are a set of predefined methods you can use to enrich your classes.  They are easy to recognize because they start and end with double underscores, for example  __init__  or  __str__ . Dunder methods let you emulate the behavior of built-in types.  For example, to get the length of a string you can call  len('string') . But an empty class definition doesn’t support this behavior out of the box: These “dunders” or “special methods” in Python are also sometimes called “magic methods.” class NoLenSupport : pass >>> obj = NoLenSupport () >>> len ( obj ) TypeError : "object of type 'NoLenSupport' has no len()" To fix this, you can add a  __len__  dunder method to your class: class LenSupport : def __len__ ( self ): return 42 >>> obj = LenSupport () >>> len ( obj ) 42 Object Initialization:  __init__ "__init __ ...
Post a Comment
Read more

Inheritance and Types in Python

  Inheritance   Creating a new class from existing class is known as inheritance . The class from which features are inherited is known as base class and the class into which features are derived into is called derived class . Syntax: class  derived- class (base  class ):       < class -suite>      Inheritance promotes reusability of code by reusing already existing classes.  Inheritance is used to implement  is-a  relationship between classes.   Following hierarchy is an example representing inheritance between classes:   Single inheritance   When a derived class inherits only from syntax, the base class is called single inheritance. If it has one base class and one derived class it is called single inheritance.   Diagram     Syntax class  A:  #parent class         #some code       class  b(A):...
Post a Comment
Read more