您现在的位置是：技术博客 > 数据库数据库

常用的sql语句

Lucas2020-07-09 17:42【代码】1005人已围观

简介常用的sql语句

#### 一、新增
````
1、 普通操作：insert into it_student values (null,'张三','男','武汉');

2、按字段插入：insert into it_student (name,sex) values ('张三','男');

3、多数据插入：insert into it_student values (null,'张三','男','武汉'),(null,'李四','男','广东');

4、蠕虫复制（部分字段）：

insert into 数据表名称（字段1，字段2，……）select 字段1，字段2 from 数据表名称

5、蠕虫复制（全部字段）：insert into 数据表名称 select * from 数据表名称 ;

6、主键冲突：如果一个表中已经存在某主键，当再次插入数据，主键重复时，就会报错

解决一，replace into it_student values (1,'李四',30,'广州');

解决二，insert into it_student values (1,'李四',30,'广州') on duplicate key update name='李四',age=30,address='广州';

注意：此时1为重复的主键，即原表有主键1，原数据为（1,'李四',24,'武汉'）

7、 指定数据库增加数据表： create table itcast.it_student;

8 、向数据表中添加一个字段（也可以是主键）： alter table 表名 add 字段名 字段描述 ;

9、 给表中某字段添加索引： create [unique|fulltext|spatial] index idxname on 表名(字段1，字段二...)

10 、创建视图： create view 视图名称 as select 语句;

11 、新增用户： create user 'username'@'localhost' identified by 'password';

12、 给用户设置权限： grant select,insert,update on mydb.* to 'username'@HostName identified by “password”;

13 、 给数据表创建联合索引：primary key ('param1','param2')  ,

注意：表的引擎必须是myisam

14 、创建临时表：create temporary table if not exists tab1 like tab2 
````
#### 二、删除
````
1 、删除表格： drop table it_student;

2、 删除某一列： alter table t2 drop column c, drop column d;

3、 删除数据库： drop database itcast;

4、 删除表格中的所有记录： delete from 数据表名称;  truncate 数据表

① 都有清空表数据功能，保留字段

② delete from 自动增长因子不变，继承之前的值

③ truncate 数据表 自动增长因子从新开始计数

5 、删除指定id或其他条件的记录 delete from 数据表名称 where id(1,2,4) 代表删除id为1,2,4 的三条记录

6 、批量删除： delete from 数据表名称 where id in(2,4)

7、 高级删除：delete from 数据表名称 where 条件 order by 字段 [ase|desc] limit 数量;

8 、删除主键： alter table 表名 drop primary key(id);

9 、删除索引（索引不能修改，只能先删后添加）： drop index idxname;

10 、删除视图：drop view 视图名称;

11 、删除某个字段重复的记录，并保留重复记录中id最大：

注：以下示例是，从ph_diff表中找到PreRent字段值重复的记录，并删除重复的保留id最大，最小的那些重复记录
````

**去重，保留id最小的**
````
delete from ph_diff where id not in 
(
    select a.id from 
    (
        select max(id) id from ph_diff a where exists
        (
            select id from ph_diff b group by PreRent having count(id) > 1
        )
        group by PreRent
    ) a
);
````
**
去重**
````
delete from ph_diff where id not in 
(
		select a.id from 
		(
    		select min(id) id from ph_diff a where exists
    		(
        			select id from ph_diff b group by PreRent having count(id) > 1
    		)
    		group by PreRent
		) a
);
````

**多字段联合分组去重，保留最小id**
````
delete from iiir_archive_tenant_browse_history where id not in =
(
    select a.id from
    (
        select max(id) id from iiir_archive_tenant_browse_history a where exists
        (
            select id from iiir_archive_tenant_browse_history b group by concat(type_id,key_id,tenant_id) having count(id) > 1
        )
        group by concat(type_id,key_id,tenant_id)
    ) a
);
````
#### 三、修改

````
1 、修改字段名称：alter table 数据表名称 change 旧字段名称 新字段名称 数据类型;

如： alter table it_student change name username varchar(20);

2 、修改字段数据类型：alter table 数据表名称 modify 字段名称 新字段类型；

如： alter table it_student modify id tinyint unsigned;

注意：如果数据表中存在临界数据，必须先清空临界数据后才能修改数据类型

3、 修改数据表名称：alter table 原表名 rename to 新表名

如： alter table it_user rename to user;

4 、修改某个表上的某个字段的值： update 数据表名 set 字段名1=更新后的值1，字段名2=更新后的值2 where 更新条件;

如： update it_student set name='李氏',address='上海' where id=1;

5 、高级更新：基本语法如下 update 数据表 set 字段=值 ……order by 字段 [asc|desc] limit 记录数;

如： update it_student set name='张山' order by name asc limit1;

6 、修改数据表引擎 如： alter table table_name engine=innodb;

7、 连表修改

如：update room,ph_room set room.RoomArea = ph_room.jzmj where room.RoomID = ph_room.id;
````

#### 四、查询

1 、内连接查询（inner join）：select * from 数据表1 [inner] join 数据表2 [inner] join 数据表3 on 关联条件;

2、 左外连接查询（left join）： select */字段列表 from 数据表1（左表）left join 数据表2（右表）on 关联条件;

注意： 保留左表（左表中的字段，加上右表中的字段）;

3 、右外连接查询（right join）： select */字段列表 from 数据表1（左表）right join 数据表2（右表） on 关联条件;

4 、自连接查询 ：如： select * from it_category t1  join it_product t2 on t1.pid = t2.cid;

5 、简单查询数据表字段、字段类型、数据表：

如： ① desc/describe 数据表名称 或② show columns from 数据表名

③ show tables; ④ show tables like 'b%';  %代表n个字符 _ 代表一个字符

注意： 如果数据表/库包含_或%用\来转义操作

6 、查询指定条件的记录 select * from 数据表名称 where 条件

如： select * from it_student where name='张三';

7、 高级查询 select * from it_student \G;

8 、去重查询 select distinct * from 数据表;

查询出来的字段若存在重复值，则只显示一条，count(distinct 字段) ，统计某个字段的时候，只统计不重复的数据

9 、高级查询（虚拟表） 如：select unix_timestamp(); 查询当前系统时间戳

10 、五子句查询：select * from 表 [where,group by,having,orderby,limit] 顺序不能变

如：select * from it_student where id&gt;2 group by sex having age&gt;25 order by score desc limit 0,2;

11、 聚合函数 count() 获取总记录数，通常以下函数都会配合group by 来使用

语法： avg()平均值 min()  max()  sum()  group_concat()用于连接数据

如：count(*)   avg(score)   group_concat(name) =&gt;name1,name2……

如：select sex,sum(age) as agevalue from it_student group by sex (每一个性别的总记录数)

如：select sex,avg(score) as avgvalue from it_student group by sex; (所有分数的平均值)

如：select group_concat(name) from it_student; (把表中的所有名字连接起来)

12、 回溯统计：with rollup

13 、查看视图：select * from information_screma.views \G;

14、 查询mysql版本：select version();

15、select case when A&gt;B then A else B end,case when B&gt;C then B else C end From test;

**统计近30天每天用户注册数量**
````
SELECT
	COUNT(user_id) user_ids_number,FROM_UNIXTIME(create_time, '%Y-%m-%d') AS time
FROM
	user
WHERE
	DATE_SUB(
		CURDATE(),
		INTERVAL 30 DAY
	) <= FROM_UNIXTIME(create_time, '%Y-%m-%d')
GROUP BY
	FROM_UNIXTIME(create_time, '%Y-%m-%d');
````

**查询活动列表（当前时间位于活动时间内的活动排在前面，不在活动时间内的排在后面）**
````
SELECT id,start_time,end_time,IF((unix_timestamp(NOW()) > start_time AND unix_timestamp(NOW()) < end_time), 1, 0) as a FROM `lu_activity`;
````

很赞哦！ ( 0 )

上一篇：存储过程函数

下一篇：mysql配置优化