MySQL库和表的操作

库是存放表的容器，表是存放数据的容器。

Show databases;
Show tables;

库的操作
创建 create database tbl_name;
删除 drop database tbl_name,t2;
使用 use tbl_name;
修改
备份
字符编码

表的操作
增 create table tbl_name;
删 drop table tbl_name;
Truncate table tbl_name; –清空表数据
Delete from tbl_name;
改 alter update
update 表名 set sex=”f” where name=’hyq’;
查 select
多表查询
Select t1.name 姓名,t2.name 老师from t1,t2 where t1.id=t2.id;
连接查询 left right
Select t1.id,t1.name,if(t2.name is null,’t2 name is null’,t2.name)
From t1 left join t2 on t1.id=t2.id;
select * from table1 where 工资>2500 and 工资<3000
select 姓名 from table1 where 性别=’0’ and 工资=’4000’
select * from table1 where not 工资= 3200
select * from table1 order by 工资desc //将工资按照降序排列
select * from table1 order by 工资 asc //将工资按照升序排列
select * from table1 where name like ‘%张’ /’%张%’ /’张%’ //查询1，首位字‘张’3，尾位字‘张’2，模糊查询
select count(*) from table //查询表中所有记录数
alter table table3 add tilte varchar(10) //向表3 中添加一列“title(职位)”
alter table table3 drop column sex //删除table3中‘性别’这一列
select * into table2 from table3 //将表3中的所有数据转换成表2 （相当于复制）
update table3 set money=money*1.2 //为表3所有人工资都增长20%
select max(sal) as ‘最高工资’,min(sal) as ‘最低工资’ from emp; 显示员工的最高工资和最低工资
select avg(sal) as ‘平均工资’,sum(sal) as ‘总计工资’ from emp; 显示所有员工的平均工资和总计工资。

查询符号
Select * from tbl_name where score!=82;
Select * from tbl_name where score<>82;
Select * from tbl_name where not score=82;

范围查询 between and
Select * from tbl_name where score not between 60 and 100;

插入 insert into tbl_name values();
Insert tbl_name(name,age) values(‘jack’,18),(‘andy’,22); 插入多条记录
Insert into tbl_name select * from tbl_name;把查询结果插入某表中
Replace into tbl_name values();替换记录数据
重命名
Rename table t1 to t2;
Alter table t1 rename t2;
显示表结构
describe tbl_name;