mysql查询表里数据,mysql学习笔记001

MySQL数据库操作的基本命令,mysql数据库命令

一、创建数据库:

 create data data _name;

 php中创建数据库的两种方法:(mysql_create_db(),mysql_query())

 $conn = mysql_connect(“localhost”,”username”,”password”) or
 die ( “could not connect to localhost”);
 mysql_create_db(“data _name”) or
 die (“could not create data ”);
 $string = “create data data _name”;
 mysql_query( $string) or
 die (mysql_error()); 

二、选定数据库

在创建表之前,必须要选定要创建的表所在的数据库

选定数据库:

 通过命令行客户端:

use data _name

 通过

php: mysql_select_db()

 $conn = mysql_connect(“localhost”,”username”,”password”) or
 die ( “could not connect to localhost”);
 mysql_select_db(“test”,$conn) or
 die (“could not select data ”);

三、创建表

create table table_name

如:

 create table table_name
 (
 column_1 column_type column attributes,
 column_2 column_type column attributes,
 column_3 column_type column attributes,
 primary key (column_name),
 index index_name(column_name)
 )

在命令行客户端需要键入整个命令

在php中使用,mysql_query()函数

如:

 $conn = mysql_connect(“localhost”,”username”,”password”) or
 die ( “could not connect to localhost”);
 mysql_select_db(“test”,$conn) or
 die (“could not select data ”);
 $query = “create table my_table (col_1 int not null primary key,
  col_2 text
  )”;
 mysql_query($query) or
 die (mysql_error());

四、创建索引

 index index_name(indexed_column) 

五、表的类型

 ISAM MyISAM BDB Heap

 声明表类型的语法:

 create table table_name type=table_type
 (col_name column attribute);

默认使用MyISAM

六、修改表

 alter table table_name

更改表名

 alter table table_name rename new_table_name

或者(高版本中)

 rename table_name to new_table_name

添加和删除列

添加列:

alter table table_name add column column_name colomn attributes

例如:

 alter table my_table add column my_column text not null

first 指定插入的列位于表的第一列

after 把新列放在已经存在的列的后面

    例如:

alter table my_table add column my_next_col text not null first
alter table my_table add column my_next_col text not null after my_other _column

删除列:

alter table table_name drop column column name

添加和删除索引:

 alter table table_name add index index_name (column_name1,column_name2,……)
 alter table table_name add unique index_name (column_name)
 alter table table_name add primary key(my_column)
 alter table table_name drop index index_name

如:

alter table_name test10 drop primary key

更改列定义:

  用change或是modify命令可以更改列的名称或是属性。要更改列的名称,还必须重新定义列的属性。例如:  

 alter table table_name change original_column_name new_column_name int not null

  注意:必须要重新定义列的属性!!!

 alter table table_name modify col_1 clo_1 varchar(200) 

七、向表中输入信息(insert)

 insert into table_name (column_1,column_2,column_3,…..)
 values (value1,value2,value3,……)

 如果要存入字符串,则需要使用单引号“'”将字符串括起来,但是需要注意字符的转意

 如:

insert into table_name (text_col,int_col) value ('hello world',1)

 需要转义的字符有:单引号' 双引号”  反斜杠  百分号%  下划线_

 可以连续使用两个单引号转义单引号

八、updata语句

 updata table_name set col__1=vaule_1,col_1=vaule_1 where col=vaule

  where部分可以有任何比较运算符

 如:

  table folks
  id  fname  iname  salary
  1  Don  Ho  25000
  2  Don  Corleone 800000
  3  Don  Juan  32000
  4  Don  Johnson  44500
  updata folks set fname='Vito' where id=2
  updata folks set fname='Vito' where fname='Don'
  updata folks set salary=50000 where salary<50000

九、删除表、数据库

 drop table table_name
 drop data data _name

在php中可以通过mysql_query()函数使用drop table命令

 在php中删除数据库需要使用mysql_drop_db()函数

十、列出数据库中所有可用表(show tables)

 注意:使用该命前必须先选定数据库

 在php中,可以使用mysql_list_tables()得到表中的清单 

十一、查看列的属性和类型

 show columns from table_name
 show fields from table_name

使用mysql_field_name()、mysql_field_type()、mysql_field_len()可以得到类似信息!

十二、基本的select语句

 要求指出进行选择的表,以及要求的列名称。若要选定所有的列,可用*代表所有的字段名

 select column_1,column_2,column_3 from table_name

 或者

 select * from table_name

用mysql_query()可向Mysql发送查询

十三、where子句

 限制从查询(select)返回的记录行

 select * from table_name where user_id = 2

如果要对存储字符串(char、varchar等类型)的列进行比较,就需要在where子句中用单引号把要比较的字符串括起来

 如:

select * from users where city = ‘San Francisco'

 通过向where子句添加and或是or,可以一次比较几个运算符

 select * from users where userid=1 or city='San Francisco'
 select 8 from users where state='CA' and city='San Francisco'

注意:空值不能和表中的任何运算符比较,对于空值,需要使用is null或是is not null谓词

 select * from users where zip!='1111′ or zip='1111′ or zip is null

如果要找到包含任何值(除空值以外)的所有记录,可以

 select * from table_name where zip is not null

十四、使用distinct

 当使用distinct时,Mysql引擎将删除有一样结果的行。

 select distinct city,state from users where state='CA'

十五、使用between

 使用between可以选择在某个范围内的值,between可用于数字,日期,文本字符串。

 如:

 select * from users where lastchanged between 20000614000000 and 20000614235959
 select * from users where lname between ‘a' and ‘m'

十六、使用in/not in

 若某列可能返回好几个可能的值,就可以使用in谓词

 select * from users where state='RI' or state='NH' or state='VT' or state='MA' or state='ME'

    可改写为:

select * from users where state in (‘RI','NH','VY','MA','ME') 

 如果要达到相同的结果,但结果集相反,可使用not in 谓词

 select * from user where state not in (‘RI','NH','VT','MA','ME')

十七、使用like

 如果需要使用通配符,则要使用like

 select * from users where fname like ‘Dan%' %匹配零个字符
 select * from users where fname like ‘J___' 匹配以J开头的任意三字母词

Mysql中like不区分字母大小写

十八、order by

 order by语句可以指定查询中返回的行的顺序,可对任意列类型排序,通过在末尾放置asc或是desc以设置按升序或是降序排列,如果不设置,默认使用asc 

 select * from users order by lname,fname

可以按照需要根据任意多的列排序,也可以混合使用asc和desc

 select * from users order by lname asc, fname desc

十九、limit

 limit限制从查询中返回的行数,可以指定开始的行数和希望返回的行数

  得到表中的前5行:

 select * from users limit 0,5
  select * from users order by lname,fname limit 0,5

  得到表的第二个5行:

  select * from users limit 5,5

二十、group by 与聚合函数

 使用group by后Mysql就能创建一个临时表,记录下符合准则的行与列的所有信息

 count()   计算每个集合中的行数

 select state,count(*) from users group by state

  *号指示应该计算集合中的所有行

 select count(*) from users

  计算表中所有的行数

 可以在任何函数或列名后使用单词as,然后指定一个作为别名的名称。如果需要的列名超过一个单词,就要使用单引号把文本字符串括起来

 sum() 返回给定列的数目
 min() 得到每个集合中的最小值
 max() 得到每个集合中的最大值
 avg() 返回集合的品均值
 having

 限制通过group by显示的行,where子句显示在group by中使用的行,having子句只限制显示的行。

二十一、连接表

 在select句的from部分必须列出所有要连接的表,在where部分必须显示连接所用的字段。

select * from companies,contacts where companies.company_ID=contacts.company_ID

 当对一个字段名的引用不明确时,需要使用table_name.column_name语法指定字段来自于哪个表

二十二、多表连接

 在select后面添加额外的列,在from子句中添加额外的表,在where子句中添加额外的join参数–>

一、创建数据库: create data data _name; php中创建数据库的两种方法:(mysql_create_db(),mysql_query(...

mysql查询表里的重复数据方法,mysql查询表里数据

INSERT INTO hk_test(username, passwd) VALUES
('qmf1', 'qmf1'),('qmf2', 'qmf11')

delete from hk_test where username='qmf1' and passwd='qmf1'

图片 1

MySQL里查询表里的重复数据记录:

先查看重复的原始数据:

图片 2

场景一:列出username字段有重读的数据

select username,count(*) as count from hk_test group by username having count>1;

SELECT username,count(username) as count FROM hk_test GROUP BY username HAVING count(username) >1 ORDER BY count DESC;

图片 3

这种方法只是统计了该字段重复对应的具体的个数

场景二:列出username字段重复记录的具体指:

select * from hk_test where username in (select username from hk_test group by username having count(username) > 1)

SELECT username,passwd FROM hk_test WHERE username in ( SELECT username FROM hk_test GROUP BY username HAVING count(username)>1)

但是这条语句在mysql中效率太差,感觉mysql并没有为子查询生成临时表。在数据量大的时候,耗时很长时间

图片 4

解决方法:

于是使用先建立临时表 

create table `tmptable` as ( 
SELECT `name` 
FROM `table` 
GROUP BY `name` HAVING count(`name`) >1 
); 

然后使用多表连接查询

SELECT a.`id`, a.`name` 
FROM `table` a, `tmptable` t 
WHERE a.`name` = t.`name`; 

结果这次结果很快就出来了。

用 distinct去重复

SELECT distinct a.`id`, a.`name` 
FROM `table` a, `tmptable` t 
WHERE a.`name` = t.`name`;

场景三:查看两个字段都重复的记录:比如username和passwd两个字段都有重复的记录:

select * from hk_test a
where (a.username,a.passwd) in (select username,passwd from hk_test group by username,passwd having count(*) > 1)

图片 5

场景四:查询表中多个字段同时重复的记录:

select username,passwd,count(*) from hk_test group by username,passwd having count(*) > 1

图片 6

MySQL查询表内重复记录

查询及删除重复记录的方法
(一)
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * 
from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId)>1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有一个记录
delete from people
where peopleId in (select peopleId 
from people group by peopleId having count(peopleId)>1)
and min(id) not 
in (select id from people group by peopleId having count(peopleId)>1)

3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in 
(select peopleId,seq from vitae group by peopleId,seq having count(*)>1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where 
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq 
having count(*) > 1)
and rowid not in (select min(rowid) from vitae group 
by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where 
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq 
having count(*) > 1)
and rowid not in (select min(rowid) from vitae group 
by peopleId,seq having count(*)>1)

(二)
比方说
在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同,现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 
having count(*) >; 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

图片 7

SELECT * from tab1 where CompanyName in( SELECT companyname from tab1 GROUP BY CompanyName HAVING COUNT(*)>1);
-- 129.433ms 

SELECT * from tab1 INNER join ( SELECT companyname from tab1 GROUP BY CompanyName HAVING COUNT(*)>1) as tab2 USING(CompanyName);
-- 0.482ms

方法二

  有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。

  1、对于第一种重复,比较容易解决,使用

select distinct * from tableName

  就可以得到无重复记录的结果集。

  如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

  发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下

  假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)

(四)查询重复

select * from tablename where id in (

select id from tablename group by id having count(id) > 1)

常用的语句

1、查找表中多余的重复记录,重复记录是根据单个字段(mail_id)来判断

 代码如下 复制代码 
 SELECT * FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1);


2、删除表中多余的重复记录,重复记录是根据单个字段(mail_id)来判断,只留有rowid最小的记录

 代码如下 复制代码 
DELETE FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id HAVING COUNT(mail_id )>1);


3、查找表中多余的重复记录(多个字段)

 代码如下 复制代码 
SELECT * FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1);


4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

 代码如下 复制代码 
 DELETE FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COU(www.jb51.net)NT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1);


5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

 代码如下 复制代码 
SELECT * FROM table WHERE (a.mail_id,a.phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1);


存储过程

declare @max integer,@id integer

declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

delete from 表名 where 主字段 = @id

fetch cur_rows into @id,@max

end

close cur_rows

set rowcount 0



(一)单个字段

1、查找表中多余的重复记录,根据(question_title)字段来判断

 代码如下 复制代码 
select * from questions where question_title in (select question_title from people group by question_title having count(question_title) > 1)


2、删除表中多余的重复记录,根据(question_title)字段来判断,只留有一个记录

 代码如下 复制代码 
delete from questions
where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1)
and min(id) not in (select question_id from questions group by question_title having count(question_title)>1) 

(二)多个字段

删除表中多余的重复记录(多个字段),只留有rowid最小的记录

 代码如下 复制代码 
DELETE FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM que(www.jb51.net)stions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1)


用上述语句无法删除,创建了临时表才删的,求各位达人解释一下。

 代码如下 复制代码 
CREATE TABLE tmp AS SELECT question_id FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1);

DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp);

DROP TABLE tmp;

查找mysql数据表中重复记录
mysql数据库中的数据越来越多,当然排除不了重复的数据,在维护数据的时候突然想到要把多余的数据给删减掉,剩下有价值的数据。

以下sql语句可以实现查找出一个表中的所有重复的记录.
select user_name,count(*) as count from user_table group by user_name having count>1;

参数说明:

user_name为要查找的重复字段.

count用来判断大于一的才是重复的.

user_table为要查找的表名.

group by用来分组

having用来过滤.

把参数换成自己数据表的相应字段参数,可以先在Phpmyadmin里面或者Navicat里面去运行,看看有哪些数据重复了,然后在数据库里面删除掉,也可以直接将SQL语句放到后台读取新闻的页面里面读取出来,完善成查询重复数据的列表,有重复的可以直接删除。

效果如下:

图片 8

缺点:这种方法的缺点就是当你的数据库里面的数据量很大的时候,效率很低,我用的是Navicat测试的,数据量不大,效率很高,当然,网站还有其它查询数据重复的SQL语句,举一反三,大家好好研究研究,找到一个适合自己网站的查询语句。

INSERT INTO hk_test(username, passwd) VALUES('qmf1', 'qmf1'),('qmf2', 'qmf11') delete from hk_test where username='...

Mysql 学习笔记001,mysql学习笔记001

Mysql是不区分大小写

要求服务器告诉他的版本号

SELECT VERSION();

mysql> SELECT VERSION();
-----------
| VERSION() |
-----------
| 5.7.18 |
-----------
1 row in set (0.09 sec)

mysql>

 当前时间

SELECT CURRENT_TIME();

mysql> SELECT CURRENT_TIME();
----------------
| CURRENT_TIME() |
----------------
| 13:57:15 |
----------------
1 row in set (0.00 sec)

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:mysql查询表里数据,mysql学习笔记001

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。