SQL Like 1#

1
select * from geo where Region like 'N%';

1
select * from geo where Region like '%h';

1
select * from geo where Region like 'N%h';

SQL Like 2#

1
select * from geo where Region like '_o%';

1
select * from geo where Region like '__l__%';

SQL in 1#

• Allows stating multiple values in the WHERE clause.
• Equivalent to using multiple ORs
• Statement Structure:

1
SELECT column_name FROM table_name
2
WHERE column_name IN (value1, value2, ...);

TIP

(value1, value2, ...) is an array

• Another way

1
SELECT column_name FROM table_name
2
WHERE column_name IN (SELECT STATEMENT);

TIP

This is a nested select.

SQL in 2#

1
select * from geo where Region in ('North', 'Wales');

1
select * from geo where Cinema in (select Cinema from customers);

TIP

select distinct cinema from brookes.customers2 where cinema in (select cinema from brookes.geo);
select distinct cinema from brookes.geo where cinema in (select cinema from brookes.customers2);
两条语句的查询结果一定相同，因为它们本质上都是查询两个表中cinema字段的交集（去重后），而集合的交集运算满足交换律（A∩B = B∩A）。
注：在我的数据库里， brookes.geo 表中的cinema字段名实际为city
select distinct city from brookes.geo where city in (select cinema from brookes.customers2);

SQL Between#

• Returns the values that fall between the specific range.
• The begin and end values are included.
• Statement Structure:

1
SELECT column_name FROM table_name WHERE
2
column_name [BETWEEN / NOT BETWEEN] value1 AND value2;

1
select * from customers where showing_date between '2014-07-02' AND '2014-07-04';

Alter statement#

• The ALTER statement is used to change the table structure.
• It can be used to add a column, modify a column, rename a column, or drop a column.

1
ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];

1
ALTER TABLE geo ADD notes varchar(50) AFTER country;

1
ALTER TABLE table_name
2
MODIFY column_name column_definition [ FIRST | AFTER column_name ],
3
MODIFY column_name column_definition [ FIRST | AFTER column_name ],
4
... ;

1
ALTER table geo MODIFY Cinema varchar(15), MODIFY Region varchar(15), MODIFY Country varchar(15);

1
ALTER TABLE table_name RENAME column_name to new_ column_name;

1
ALTER table GEO rename column notes to More_details;

1
ALTER TABLE table_name DROP column_name, DROP column_name, ... ;

1
ALTER table geo drop column notes;

Insert statement#

• The INSERT statement is used to insert new records in the table.
• Statement Structure:

1
INSERTINTOtable_name(column1,column2,column3, ...) VALUES(value1,value2,value3, ...);

• Another way

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

NOTE

注意：以上两种操作效果相同，均为从左往右顺序填充数据
第一种可以跳过部分字段，字段与值一一对应
字段数量与值的数量不同时会报错

1
INSERT INTO Geo VALUES (value1, value2, value3, ...);

1
INSERT into geo (Cinema, Region) values ('Yibin', 'Sichuan’);

Null values#

• Null represents a field with an empty value.
• A field with a NULL value is one that has been left blank during record creation
• A NULL value is different from a zero value.
• Example:

1
Select * from geo where country is [null / not null].

Update statement#

• Disable update protections:

1
set SQL_SAFE_UPDATES = 0;

• Updating specified records in the table.

1
UPDATE table_name SET column1 = value1, column2 = value2, ...
2
WHERE condition;

• Example:

1
update geo set country = 'China' where country is Null;

Delete statement 1#

• Deleting specified columns from a table.

1
DELETE FROM table_name WHERE condition;

• Example:

1
DELETE from geo where country = 'China';

Delete statement 2#

• Delete all records :

1
DELETE FROM table_name;

• Example:

1
DELETE from geo;