show databases;

use ldb;
show tables;

-- 以下都是sakila的练习,单行的时候可以不用;多行就必须要
use sakila;

-- actor 字段actor_id, first_name, last_name. last_update
select * from actor ;
select count(*) from actor;
-- 查询多个字段用,分隔需要的字段
select first_name, last_name from actor;

-- 查询系统时间，要查的数据数不存在任何表里面的时候可以省略from
select now();
select 1 + 8;
-- 没有from的时候可以加一个虚拟表dual，结果和上面一样，只是看起来更工整
select now() from dual

-- where 加查询条件，查询条件可以由and、or、not逻辑运算符或多个表达式的组合
-- where也可以用在update、delete语句中，用来指定要更新或删除的行
select * from actor where last_name='allen'
select * from actor where last_name='davis' and first_name="susan"
select * from actor where last_name='davis' and actor_id < 100
select * from actor where last_name='allen' or last_name="davis"
-- in("a", "b", "c") 相当于 name='a' or name='b' or name='c'
select * from actor where last_name in ("allen", "davis")
select * from language where name not in ("french", "german")
-- between min and max 或者not between min and max 相当于 a >= min and a <= max
select * from film where replacement_cost between 1 and 10
-- like 模糊匹配字符数据 以a开头任意长度a%、以a结尾任意长度%a、%a%包含a、%a%b%同时包含a和b且a在b前面、a_以a开头长度位2的字符串，_a长度2以a结尾
select * from actor where first_name like "p%E"
select * from actor where first_name like "_ay"
select * from actor where first_name not like "%ay"
-- is null,null不是0也不是1.就是一个null； is null 或者 is not null
select * from staff where password is null
select * from staff where password is not null
-- exists，一般用在where子句中，判断一个子查询是否返回数据行
-- 查询language中的语言在film中有哪种语言有影片
select * from language where exists(select * from film where film.language_id = language.language_id);