Recall the concept

Июль 23, 2022

Содержание

2. Learning Objective: Create, evaluate and improve search queries that use multiple criteria and relational operators to
3. Success criteria know what is Queries know the purpose of the Queries can create Queries using
4. MySQL – RDBMS SQL stands for the Structured Query Language. It defines how to insert, retrieve,
5. Создание базы данных CREATE DATABASE my_first_db; DROP DATABASE: Удалить базу данных DROP TABLE: Удалить таблицу EXPLAIN:
6. ALTER TABLE: Изменить таблицу Удаляем столбец ALTER TABLE users DROP email; Изменение столбца ALTER TABLE users
7. Select SELECT is used to retrieve rows selected from one or more tables. The SELECT statement
8. SELECT, WHERE We need to use another statement, the WHERE clause, allowing us to give the
9. Operators in The WHERE Clause So you can see we used AND statement, we also can
10. Example Say the police knew that a crime had been committed by a heavily scarred woman
11. Example However, the police want to quickly sort through and see who is the most heavily
12. Select with Comparison Operators For numbers (INT, DECIMAL, FLOAT)
13. For strings, you could also use '=', ' ', '>', ' =', '
14. String Pattern Matching - LIKE and NOT LIKE we can perform pattern matching using operator LIKE
15. Arithmetic Operators - +, -, *, /, DIV, % Logical Operators - AND, OR, NOT, XOR
16. Further Reading….. IN, NOT IN BETWEEN, NOT BETWEEN IS NULL, IS NOT NULL ORDER BY Clause
17. create table Employee(empno int(5) primary key, ename varchar(30), job varchar(25), hiredate date, sal double(10,2), commission double(6,2),
19. Скачать презентацию

Слайд 2

Learning Objective: Create, evaluate and improve search queries that use multiple criteria

and relational operators to find specific information

Queries

Слайд 3

Success criteria
know what is Queries
know the purpose of the Queries
can create

Queries using the structure
can create Queries using commands SQL: SELECT, WHERE

Слайд 4

MySQL – RDBMS
SQL stands for the Structured Query Language.
It defines how

to insert, retrieve, modify and delete data.

Слайд 5

Создание базы данных
CREATE DATABASE my_first_db;
DROP DATABASE: Удалить базу данных
DROP TABLE: Удалить

таблицу
EXPLAIN: Показать структуру таблицы
USE: Выбор базы данных
Создать таблицу
CREATE TABLE users (
    username VARCHAR(20),
    create_date DATE
);
Первичный ключ
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20),
    create_date DATE
);

Слайд 6

ALTER TABLE: Изменить таблицу
Удаляем столбец
ALTER TABLE users DROP email;
Изменение столбца
ALTER TABLE

users
CHANCE username
User_name VARCHAR<30>;
INSERT: Добавляем данные в таблицу
INSERT INTO users VALUES (”Alex”,’2002-07-25’);

Слайд 7

Select
SELECT is used to retrieve rows selected from one or more tables.
The

SELECT statement allows you to ask the database a question (Query it), and specify what data it returns.

Слайд 8

SELECT, WHERE
We need to use another statement, the WHERE clause, allowing

us to give the query some criteria (or options):

Слайд 9

Operators in The WHERE Clause
So you can see we used AND

statement, we also can use OR, NOT and others like:

Слайд 10

Example
Say the police knew that a crime had been committed by

a heavily scarred woman (4+ scars), they want a list of all the scarred women:
This would return:

Слайд 11

Example
However, the police want to quickly sort through and see who

is the most heavily scarred. We are going to use an ORDER command:
ORDER BY numScars sorts your returned data into DESCending (big to small) or ASCending (small to big) order

Слайд 12

Select with Comparison Operators
For numbers (INT, DECIMAL, FLOAT)

Слайд 13

For strings, you could also use '=', '<>', '>', '<', '>=', '<=' to compare two strings (e.g., productCode =

'PEC').

Слайд 14

String Pattern Matching - LIKE and NOT LIKE
we can perform pattern matching using operator

LIKE (or NOT LIKE) with wildcard characters. The wildcard '_' matches any single character; '%' matches any number of characters (including zero). For example,

Слайд 15

Arithmetic Operators - +, -, *, /, DIV, %
Logical Operators -

AND, OR, NOT, XOR

Слайд 16

create table Employee(empno int(5) primary key, ename varchar(30), job varchar(25), hiredate

date, sal double(10,2), commission double(6,2), deptt int(2));
INSERT INTO employee VALUES (1001,”Alex”,”Teacher”,’2017-07-25’, 5678.90, 100.0, 10);
Select * from Employee where commission>0
Select jobs from employee;
SELECT * FROM EMPLOYEE WHERE ENAME LIKE “_ _ _ _ _”;
SELECT * FROM EMPLOYEE WHERE ENAME LIKE “_ _ _ _p%”;
SELECT * FROM employee WHERE deptt= 'computer ' ORDER BY ename;
Select ename, hiredate from employee where job not like “history”;

Recall the concept

Содержание

Learning Objective: Create, evaluate and improve search queries that use multiple criteria

Success criteriaknow what is Queriesknow the purpose of the Queriescan create

MySQL – RDBMSSQL stands for the Structured Query Language.It defines how

Создание базы данныхCREATE DATABASE my_first_db;DROP DATABASE: Удалить базу данныхDROP TABLE: Удалить

ALTER TABLE: Изменить таблицуУдаляем столбецALTER TABLE users DROP email;Изменение столбцаALTER TABLE

SelectSELECT is used to retrieve rows selected from one or more tables.The

SELECT, WHEREWe need to use another statement, the WHERE clause, allowing

Operators in The WHERE ClauseSo you can see we used AND

ExampleSay the police knew that a crime had been committed by

ExampleHowever, the police want to quickly sort through and see who

Select with Comparison OperatorsFor numbers (INT, DECIMAL, FLOAT)