数据库常用代码
表操作
创建表:create table user(id int(10) ,name varchar(20))
复制表:create table new_table like old_table;
插入数据:insert table new_table select * from old_table;
注意第一种方式全部复制,第二种方法复制表结构和数据,不复制主键、索引
第二种方式复制表:create table new_table select * from old_table;
第二种方式插入数据:create table new_table select * from old_table where 0;
- 创建临时表:create temporary table tmpl(id int not null);可通过参数 tmp_table_size 设定临时表的大小
- 创建内存表:creat table tmp2(id int not null) enging=memory; 可通过参数 max_heap_table_size 设定内存表的大小,超出报错
数据操作
插入数据:insert into 表名(字段名,字段名) values(值,值);insert into 表名 values(值,值);
- 查询数据:select * from 表名;
- Select id from 表名;
按条件查询:
- Select * from 表名 where id = 10;
- Select * from 表名 id=10 and name=’小明’;
- 修改记录:update 表名 set 字段=值 ,字段=值 where 条件
- Update user set name=’小明’,sex=’男’ id=4;
- 删除记录:delete from 表名 where 条件
- Delete from user where id=4;
查询结果进行排序
按某一字段升序:SELECT * FROM user ORDEY BY id;
按某一字段降序:SELECT * FROM user ORDEY BY id DESC;
按多个字段降序:ORDEY BY 字段1,字段2 DESC;
查询结果进行分组:
按字段:SELECT * from user group by 字段;
设置分组条件(HAVING):
select sex,count(sex) from user where age>15 group by sex having count(sex)>2;
限制查询数量:
检索前六行记录:select * from user limit 6;
从第2条数据开始,检索出五条数据:select * from user limit 2,5;
设置为主键:creat table 表名 ([]),PRIMARY KEY (列名);
添加字段:alter table user add tel varchar(25) not null;
字段重命名:alter table <表名> change <字段名> <字段新名称> <字段的类型>;
字段设置默认值:alter table 表名 alter 字段名 set default 默认值;
删除默认值:alter table 表名 alter 字段名 drop default;
定义字段自增:AUTO_INCREMENT;
查询
基本语法格式:
select 查询内容
from 表名
where 表达式
group by 字段名 (分类)
having 表达式 (配合group by 使用,对分组后的数据进行过滤)
order by 字段名 (排序)
limit 记录数
数据过滤:select * from user where age=20 and name = 'kun';
select * from user where age=20 or name ='kun';
select * from user where id in (3,5,7);
select * from user where id not in (3,5,7)
select * from user whee name is null;
select * from user where age between 10 and 20;
以张开头:select * from user name like '张%';
以明结尾:select * from user name like '%明';
包含明:select * from user name like '%明%';
字段控制查询过滤:
去除重复列:select distinct age from user;
设置列的别名:select name as 姓名 from user; 或者 select name 姓名 from user;
子查询:
查询年龄大于20的用户的姓名、性别、年龄
select * from (select name,sex,age from user where age >20) as user20;
查询和david一样性别的用户:
select * from user where sex = (select sex from user where name = 'david'
查询性别为女的id和name:
select id,name from user where id in(select id from user where sex='女'