查询语句
1.显示建表语句 show create table
show create table bps_process
2.更新字段 update
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
3.插入数据(以查询结果)
insert into table_bak select * from table where file_type = 'XXXX';
insert into actor_name select first_name, last_name from actor;
4.模糊查询 like
select * from table_name where opp_number like '00%' (以00开头)
5.模糊查询 in
select * from TABLE_NAME where stat_key in ('AAA','BBB','CCC','DDD')
6.连表查询 join on
select a.* from a join b on b.thread_id = a.thread_id where b.module_id = '11' ;
外连接 左面显示全
select * from employees a left join dept_emp b on a.emp_no=b.emp_no
三表连接
select a.last_name ,a.first_name ,c.dept_name
from employees a left join dept_emp b on a.emp_no = b.emp_no
left join departments c on c.dept_no = b.dept_no
7.分组 group by 计数 having
select emp_no, count(emp_no) as t from salaries group by emp_no having t > 15
8.唯一 不重复 distinct 必须在select的第一个
SELECT DISTINCT Company FROM Orders
9. 不存在 not in
select* from employees a where a.emp_no not in (select b.emp_no from dept_manager b)
操作语句
1. 执行计划explain 用于交互式分析和排除故障
explain select * from employees
2. 数据拼接 || 下列为中间用空格隔开
select last_name||" "||first_name as Name from employees;
3.插入数据如果重复 忽略 or ignore mysql 为ignore
insert or ignore into actor values (3,'ED','CHASE','2006-02-15 12:34:33');
4. 修改 alter, 增加 add, 删除 drop
alter table actor
add create_date datetime NOT NULL default('0000-00-00 00:00:00') datetime类型
5. 更新表update set
update titles_test set from_date = '2001-01-01',to_date = NULL where to_date = '9999-01-01'
6. replace函数,其中包含三个参数,第一个字段的名称,第二字段的需要被修改值,第三个为该字段修改后的值。
update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5
10. limit 1,1 偏移量从0开始 第二行开始显示出一条记录
select * from salaries where to_date = '9999-01-01' order by salary desc limit 1,1