CRUD Flashcards
Syntax for creating a database
Create database DATABASENAME;
Is the mysql command case sensitive?
No
TRUE OR FALSE:
The database name must follow the rules in naming variable
TRUE
Is the semicolon a requirement in database?
Yes
Display all the database in my sql
Show databases;
Deleting the database
DROP database DATABASENAME;
Creating a table
create table tablename(
field1 datatype1
field2 datatype2
field3 datatype3);
Adding field to the table
alter TABLE TABLENAME add FIELD DATATYPE;
Changing the data type or fieldname of the table
alter TABLE TABLENAME change OLDFIELD NEWFIELD DATATYPE;
Setting a primary key of the table
alter table TABLENAME add primary key(FIELDNAME);
Deleting a field
alter table TABLENAME drop FIELDNAME;
SHOWING ALL TABLES IN THE DATABASE
SHOW TABLES;
Renaming table
alter table ORIG_NAME rename NEW_NAME;
Adding Record to Table
insert into TABLENAME values(VALUE1, VALUE2, VALUE3, ….);
Inserting record to table example
insert into STUDENT values(‘101’,’Cris’,’Female’,15,5500)
Used to view the record of the tables
select * from TABLENAME;
column modifier is used to display leading zeros of a number based on the display width.
ZeroFill
column modifier automatically increases the value of a column by adding 1 to the current maximum value.
Autoincrement
How to use ZeroFill?
create table student( student_no varchar(20), firstname varchar(20),age Tinyint(4) Zerofill);
Example on how to use AUTOINCREMENT?
create table student(student_no INT PRIMARY KEY AUTO_INCREMENT, firstname varchar(20));
Editing Record
UPDATE tablename SET fieldname = value where fieldname = criteria
What is being done?
UPDATE EMPLOYEE SET FirstName = ‘Crista’ where Emp_no = ‘101’
The FirstName of Emp_no 101 will be set to Crista
What will happen?
UPDATE tbemployee SET FirstName = ‘Crista’
This command will change the first name of all record to Crista
What will happen?
UPDATE tbemployee SET age = age + 2
All recorded will be updated by adding 2 to the age
What will happen?
UPDATE EMPLOYEE SET salary = salary + 500 where gender = ‘female’
All female employee will be updated by adding 500 to the salary.
What will happen?
UPDATE EMPLOYEE SET age = 19, salary = 6100 where emp_no = ‘102’;
The record of employee who have a employee number of 102 will be updated. The salary will be set to 6100 and age to 19
Deleting Record
Delete from TABLENAME where CONDITION;
What will happen?
Delete from tbstudent where sn = ‘102 ’;
This command will delete an student who have a student number of 102
What will happen?
Delete from tbstudent where firstname =‘Karen’ and lastname =‘Lagasca’;
This command will delete an student who have a first name of Karen and last name of Lagasca
What will happen?
Delete from tbstudent where firstname like ‘m%’
This command will delete an student who have first name that start with letter m
- A statement used to select data from a database.
- The result is stored in a result table called the result-set
SELECT
How to select ALL FIELDS of a table?
SELECT * FROM table_name;
Use select command with selected field
SELECT column_name, column_name FROM table_name;
- used to sort the result-set by one or more columns.
- sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY KEYWORD
SQL ORDER BY KEYWORD SYNTAX
SELECT column_name,column_name
FROM table_name ORDER BY column_name,column_name ASC/DESC
What is being done?
SELECT * from EMPLOYEE order by LASTNAME ASC
The records will be ordered alphabetically by last name in ascending order
SELECT * from EMPLOYEE order by DEPT
This will display all the record that sorted by department in ascending order
- clause used to filter records
- used to extract only those records that fulfill a specified criterion.
THE SQL WHERE CLAUSE BY KEYWORD
How to display all the records that have a position of manager in a table named employee?
SELECT * FROM employee
WHERE position = ‘Manager’
How to display all the record that have a department of ACC in employee table?
SELECT * FROM employee
WHERE dept = ‘ACC’
- can be used in SQL command especially in number
- <, >,<=, >=, =, <>
Relational Operator
Display All the record from employee table that have a salary greater than 10000
select * from employee where salary > 10000;
- It is used to select values within a range.
- Selects values within a range. The values can be numbers, text, or dates.
BETWEEN KEYWORD
SYNTAX FOR BETWEEN KEYWORD
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
What will happen?
select * from EMPLOYEE where AGE BETWEEN 20 AND 25;
This will display all the record that the age is between 20 and 25. Remember that 20 & 25 are included
Display all the record from EMPLOYEE table that the FIRSTNAME start from C to G only and not the H
select * from EMPLOYEE where FIRSTNAME BETWEEN ‘C’ AND ‘H’;
It allows you to specify multiple values in a WHERE clause.
IN KEYWORD
IN KEYWORD SYNTAX
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,…);
WHAT WILL HAPPEN?
select * from EMPLOYEE where age in (18, 25)
This will display all the record that the age is 18 or 25
