mysql note webex.txt - Database rdbms(mysql,sql server...

Info icon This preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Database - rdbms (mysql,sql server , oracle, ms access, sybase, foxbro) - ordbms (oracle 10g) how to give instruction to dase base to store or retrieve the data sql - structured query language Ansi sql mysql is application of database type of statement (instruction) in sql - DDL (DATA DEFINITION LANGUAGE) CREATE,ALTER,DROP AND TRUNCATE ,RENAME - DML (DATA MANIPULATION LANGUAGE) INSERT,DELETE,UPDATE, SELECT , MERGE - DCL (DATA CONTROL LANGUAGE) GRANT AND REVOKE - TCL (TRANSACTION CONTROL LANGUAGE) COMMIT,ROLLBACK, SAVEPOINT DDL create, alter,drop and truncate mysql datatype int,float,char, varchar, varchar2, date blob,clob, CREATE TABLE table_name (columnlist); column_list column_name datatype[(size)], column_name datatype[(size))], .... column_name datatype[(size)] [PRIMARY KEY], column_name datatype[(size))], .... column_name datatype[(size)] REFERENCES parent_table_name(column_name), column_name datatype[(size))], .... example create table dept (dno int primary key, dname char(20),dloc varchar(20));
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
create table emp (empno int primary key, ename char(20),sal float,dno int references dept(dno)) create table emp (empno int primary key, ename char(20),sal float default 20000,dno int references dept(dno)) how to create database CREATE DATABASE database_name; how to see what are database are available SHOW DATABASES; how to switch database USE database_name; how to see the tables are avaialble in database SHOW TABLES; how to see the structure of tables DESC table_name; how to see|read|retreive the data in the table select * from table_name; select column_name , column_name from table_name; -------------- INSERT STATEMENT INSERT INTO table_name VALUES (v1,v2,v3...); -if the value datatype is char|varchar|date dataype you need put single quote) - date should be in the format year/month/day in oracle 01-jan-2015 in mysql 2015/01/01 inserting single record insert into dept values (10,'sales'); insert into emp values (111,'kavi',12000,10); insert into emailids value (111,'[email protected]', '2016/10/10'); how to insert mulitiple record insert into dept values (10,'sales'),(20,'purchase'),(30,'academy'); ------------ delete statement DELETE FROM table_name [ WHERE condition]; condtion - relational and logical operator
Image of page 2
> , >= , < , <= , =, <> ,!= AND , OR , NOT LIKE example delete from emp; delete from emp where empno = 100; delete from emp where dno = 10; delete from emp where dno = 10 or dno = 20 delete from emp where dno = 10 or sal > 5000; delete from emp where dno = 10 and sal > 5000; delete from emp where dno = 10 or dno = 20 and sal >= 5000; delete from emp where dno = 10 or dno = 20 or sal >= 5000; 1 10 3000 2 20 5000 3 10 2000 4 30 5000 5 40 3000 6 60 2000 ------------------- update - will change the value in the existing row(s) on a specific column(s) UPDATE table_name SET column_name = value | expression | subquery , [ column_name = value | expression | subquery,...] [WHERE condition]; update emp set sal = sal + 1000 ; update emp set sal = 12000; update emp set sal = (select sal from emp where empno = 222) where empno = 111; update emp set sal = 15000 where empno = 111; update emp set sal = sal +10000 where dno = 10 and sal <5000; update emp set sal = sal +1000 , desig='strainer' where dno = 10 and sal <5000; ------------------------ alter table - to change structure of the table how to add column to a table ALTER TABLE table_name ADD [COLUMN] column_defintion; alter table emp add column dob date; how to drop the column in a table ALTER TABLE table_name DROP [COLUMN] column_name;
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern