Chinaunix首页 | 论坛 | 博客
  • 博客访问: 84186
  • 博文数量: 38
  • 博客积分: 273
  • 博客等级: 二等列兵
  • 技术积分: 215
  • 用 户 组: 普通用户
  • 注册时间: 2011-11-14 22:01
文章分类

全部博文(38)

文章存档

2012年(14)

2011年(24)

分类: Mysql/postgreSQL

2011-12-15 10:57:07

一、mysql 一张表左连

mysql> select A.*,B.* from tbl_category A left join tbl_category B on A.id=B.id;
+------+----------+-----------+------+----------+-----------+
| id   | name     | parent_id | id   | name     | parent_id |
+------+----------+-----------+------+----------+-----------+
|    1 | feilei1  |         0 |    1 | feilei1  |         0 |
|    2 | feilei2  |         0 |    2 | feilei2  |         0 |
|    3 | feilei3  |         0 |    3 | feilei3  |         0 |
|    4 | feilei11 |         1 |    4 | feilei11 |         1 |
|    5 | feilei21 |         2 |    5 | feilei21 |         2 |
+------+----------+-----------+------+----------+-----------+
5 rows in set (0.00 sec)

mysql> select A.* from tbl_category A;
+------+----------+-----------+
| id   | name     | parent_id |
+------+----------+-----------+
|    1 | feilei1  |         0 |
|    2 | feilei2  |         0 |
|    3 | feilei3  |         0 |
|    4 | feilei11 |         1 |
|    5 | feilei21 |         2 |
+------+----------+-----------+
5 rows in set (0.00 sec)

mysql> select A.*,B.* from tbl_category A left join tbl_category B on A.id=B.parent_id;
+------+----------+-----------+------+----------+-----------+
| id   | name     | parent_id | id   | name     | parent_id |
+------+----------+-----------+------+----------+-----------+
|    1 | feilei1  |         0 |    4 | feilei11 |         1 |
|    2 | feilei2  |         0 |    5 | feilei21 |         2 |
|    3 | feilei3  |         0 | NULL | NULL     |      NULL |
|    4 | feilei11 |         1 | NULL | NULL     |      NULL |
|    5 | feilei21 |         2 | NULL | NULL     |      NULL |
+------+----------+-----------+------+----------+-----------+
5 rows in set (0.00 sec)

mysql> select A.*,B.* from tbl_category A left join tbl_category B on A.id=B.parent_id where B.id is not null;
+------+---------+-----------+------+----------+-----------+
| id   | name    | parent_id | id   | name     | parent_id |
+------+---------+-----------+------+----------+-----------+
|    1 | feilei1 |         0 |    4 | feilei11 |         1 |
|    2 | feilei2 |         0 |    5 | feilei21 |         2 |
+------+---------+-----------+------+----------+-----------+
2 rows in set (0.00 sec)

==============================>删除重复记录:
1。留下重复记录的其中一行:
DELETE test AS a FROM test AS a,
 (   SELECT *, min(id) FROM test    GROUP BY c1 HAVING COUNT(*) > 1 ORDER BY id ) AS b
WHERE  a.c1 = b.c1 AND a.c2=b.c2 AND a.c3=b.c3 AND a.id > b.id;

不留:
delete A,B from test A,test B where A.c1=B.c1 and A.c2=B.c2 and A.c3=B.c3 and A.id>B.id;
留:
delete A from test A,test B where A.c1=B.c1 and A.c2=B.c2 and A.c3=B.c3 and A.id>B.id;

==============================>统计各类型的邮箱数量:
select count(id) as idcount,substring(email,(LOCATE('@',email)+1)) from users where created < '2010-01-13 00:00:00' group by substring(email,(LOCATE('@',email)+1)) order by idcount desc
二、mysql命令
1、linux mysql 数据库导出
mysqldump -uroot -pwuxuping --default-character-set=utf8 eagle>eagle.sql //导出数据库表及所有数据,但没导出存储过程
mysqldump -uroot -pwuxuping --default-character-set=utf8 eagle -R>database.sql //导出数据表,并导出存储过程

2、linux mysql 数据库导入
mysqladmin -uroot -pwuxuping create neweagle; //一般的创建数据库,需要数据库支持中文的话,别忘了添

加set utf-8语句
mysql -uroot -p neweagle 
其他常用的语句

1:使用SHOW语句找出在服务器上当前存在什么数据库:
  mysql> SHOW DATABASES;
2:创建一个数据库MYSQLDATA
  mysql> CREATE DATABASE MYSQLDATA;
3:选择你所创建的数据库
  mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)
4:查看现在的数据库中存在什么表
  mysql> SHOW TABLES;

显示所有表和表的Comment (表注释):show table status;

select * from 表 where id=274  如果表里字段特别多,就显示的很乱。此时这样做,会显示的很清晰:

select * from 表 where id=274\G


5:创建一个数据库表
  mysql> CREATE TABLE MYTABLE (name VARCHAR(20), *** CHAR(1));
6:显示表的结构:
  mysql> DESCRIBE MYTABLE;

显示表字段的comment(字段名注释): show full fields from 表名;

显示建表SQL语句  (如:create table account...)  show create table account


7:往表中加入记录
  mysql> insert into MYTABLE values (”eagle”,”M”);
8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)
  mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
9:导入.sql文件命令(例如D:/mysql.sql)
  mysql> use database;
  mysql> source d:/mysql.sql;
10:删除表
  mysql> drop TABLE MYTABLE;
11:清空表
  mysql> delete from MYTABLE;
12:更新表中数据
  mysql> update MYTABLE set ***=”f” where name=eagle;
13:删除数据库
  mysql> drop database <数据库名>


mysql> alter table employee change depno depno int(5) not null;
加索引
mysql> alter table 表名 add index 索引名 (字段名1[,字段名2 …]);
例子: mysql> alter table employee add index emp_name (name);

加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
例子: mysql> alter table employee add primary key(id);

加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
例子: mysql> alter table employee add unique emp_name2(cardnumber);

查看某个表的索引
mysql> show index from 表名;
例子: mysql> show index from employee;

删除某个索引
mysql> alter table 表名 drop index 索引名;
例子: mysql>alter table employee drop index emp_name;

修改表:增加字段:mysql> ALTER TABLE table_name ADD field_name field_type;
查看表:mysql> SELECT * FROM table_name;
修改原字段名称及类型:mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
如:alter table users change play_games play_games set('1','2','3','4','5','6','7','8','9');[[play_games是字段名,set('1'..)这是字段类型,跟varchar(25)是这样的。]]
删除字段:ALTER TABLE table_name DROP field_name;
修改字段的默认值,大小等:alter table tbl_guestbook alter column GPName set default '1';

添加字段时写备注:
ALTER TABLE `gamecomment` ADD `picture` blob COMMENT '用户上传的图片';
ALTER TABLE `game_test_code` add `demarcation` int(8) not null default 0 COMMENT '界线';


ALTER  TABLE default_page RENAME TO default_page_back;

算是表备份:把表中的数据导入到另一表中::::
insert into topic_comment (select * from topic_comment2);


三、中文乱码:

查看:mysql > SHOW VARIABLES LIKE 'character%';
设置:mysql > set character_set_client=latin1;

但这只是在本次有效。要想一直有效的话。进入    sudo gedit /etc/my.cnf,在最后加上:

[client]
default-character-set=latin1

在 [mysqld] 下面加上  default-character-set=latin1

重新启动 mysql 就好了。


四、mysql执行创建存储过程的sql,出现错误:

错误如下:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

本次mysql连接解决办法:mysql > set global log_bin_trust_function_creators=TRUE;

终极解决方式:(编缉my.cnf,添加如下)
[mysqld]
log_bin_trust_routine_creators = 1




五、mysql查看数字库一字段,占用的最长的记录。
方法一:select `id`,`blockstring`,CHAR_LENGTH(`blockstring`) as len from 表名 order by len desc limit 0,1;
方法二:select max(CHAR_LENGTH(blockstring)) as max_len from 表名;

六、备份表与还原表:
备:shell:mysqldump -uimager -p123456 -h127.0.0.1 b2c users > users.mysql
还:shell:mysqldump -uimager -p123456 -h127.0.0.1 b2c users < users.mysql

七、更改字段类型:
alter table `detail_info` MODIFY `clientsize` int(11) DEFAULT '0' COMMENT '客户端大小';

八、复制表:
create table 新表 select * from 旧表



--------------------------------------------
一、当数据库压力大时,排查是因为什么压力大,步骤:
1、在243或246上,ssh 局域网202,sudo -s切到root,
2、w命令,查看负载情况。
3、top看是谁占CPU等较多,找出那个PID
4、ps -ef | grep PID,找出对应的端口号。
5、找到端口号了,就可以登录进去数据库,如:/opt/mysql/bin/mysql -ushiwan -p -h192.168.10.202 -P3307
6、show full processlist\G,查看销费比较大的SQL语句。

附:数据库结构202:3307主,3308主,3306从;203:3306,3307,3308均从
阅读(1449) | 评论(0) | 转发(0) |
0

上一篇:mysql 性能优化方案

下一篇:shell

给主人留下些什么吧!~~