mysql 入门 ubutu centos windows

一些查询实例：

ubuntu 下mysql导入出.sql文件

同一个表相同id不同列比较
//having在组内比较，将会挑选组内物理位置排在前面的score 与 1比较

select *  form student group by id having score>1;


查询有 3 门以上课程是 90 分以上的学生的学号及(90 分以上的)课程数
SELECT Sno, COUNT(*)
FROM SC
WHERE Grade>=90
GROUP BY Sno
HAVING COUNT(*)>=3 // 

student表的别名s1 和 s2，查找student中cname='a1'比cname='b1'大的id，显示两个表。
select * from student  s1 join student s2 where s1.id=s2.id and s1.cname='a1';
改进：显示一行
select s1.id ,s1.name from student  s1 join student s2 where s1.id=s2.id and s1.cname='a1';

查找符合条件的每组成员超过2 的组，注意count（*）,计算的是组内的记录数
select id,count(*)from student group by id having count(*)>2; 

按学生科目总成绩排名

 select id,SUM(score)from student group by id order by SUM(score)desc;

获取倒数排列前10名的LogID

select top 10  LogID from API_LOG  ORDER BY LogID DESC

左连接两个表合并查询一个值，返回记录

select 
m.id,m.username,

d.userType,d.vehPlate m.last_login_time

from usr m  left join user_detail d 
on m.id=d.id where m.username='123456'

比较强大的实例链接

http://blog.csdn.net/muxiaoshan/article/details/7617533

经典：有三个字段可以接查询条件，以速度由快到慢依次为 on where having

on用于连接表，where用于约束查询条件，having在查询结果中筛选需要的信息

作为left jion 不管右边表是不是有对应的记录，一定显示左边的记录。

要求你将 连接表的条件 m.id=d.id放在on的后面，

如果放在where后面left和right将会失效

idName 中含有 “罗” 字的所有记录

select * from table where table.idName LIKE '%罗%'

复制表：

select * into 目标表名 from 源表名

将表中的数据插入另一个表
insert into 目标表名(fld1, fld2) select fld1, 5 from 源表名

mysql与sqlserver的区别

取4-6条记录

sqlserver：select top 3 * from users where userID not in(select top 3 userID from users)
mysql：select * from users limit 3,3

重复记录

查：

select name from tablename group by name having count(1)>1 //如果相同name的值相同的有两个大于1 ，则 name 打印出来 ,由于group by的显示所以只能打印name

应用

//having 来筛选groupby的每个单元，查找选2门或者3课的学生名字（将study和student表联合起来查）

select studentName 
from student t left join study y on t.studentId = y.studentId 
group by studentName 
HAVING count(1)>1 and count(1)<4 

//用 in 消除group by 必须包含select中所有选项的限制（当然语法上不能，呵呵）

select * from student where studentId  
in 
(select studentId  from  study group by studentId having count(1)=3 ) 

select * from  tablename  where name in (select name from tablename group by name having count(1)>1) //打印出包括name 的完整记录，不过数据大的时候很慢

select  * from tablename a where (     (select count(*)tablename where Title = a.Title)    > 1  )   ORDER BY Title DESC // 取表名中，title 相同的记录

select time,max(total) as total,name from timeand group by time //用time分组，time相同的时候取 total最大的记录

select station_name,count(distinct station_name) from tb_spider_station   group by station_name;//显示所有记录，重复的只显示一条。但是不呢个select*

select row_number()over(order by StationName) as id,*  from tb_station //sqlserver 在select中增加自增id，用于区分完全相同的记录

row_number()over(order by OccTime)as id 做为一个字段，成为自增的ID

查出所有重复的记录

SELECT * FROM tb_spider_station_copy a
WHERE ((SELECT COUNT(*)
FROM tb_spider_station_copy
WHERE station_name = a.station_name and station_stake = a.station_stake) > 1)
ORDER BY a.standard_road_id DESC

过滤

Select * From t Tablename Where ID In (Select Max(ID) From Tablename Group By Title)  // 相同title 取id最大的

删：

改：

新增加自增列 id

alter table tb_spider_station drop column id
alter table tb_spider_station add id int identity(1,1)  //自增从 1  开始 每次自增 1

ubuntu14

安装mysql

sudo apt-get install mysql-server mysql-client 

启动关闭mysql服务：sudo service mysql start/stop/restart


登陆
mysql -uroot -p123456（没有木马不用加-p和它后面的）


登陆后变为：
mysql>    //命令写完后，就用“；”结尾，然后回车。直接回车会出现下面的标示
mysql->  //一条命令没有写完，可以继续写

导入和到出sql

ubuntu 下mysql导入出.sql文件
1.导出整个数据库
　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名
　　mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
　　2.导出一个表
　　mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
　　3.导出一个数据库结构
　　mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql
　　-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
　　4.导入数据库
　　常用source 命令
　　进入mysql数据库控制台，
　　如mysql -u root -p
　　mysql>use 数据库
　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
　　mysql>source /home/pt/test.sql
或直接导入命令为： mysql -h localhost -u root -p temp
#Ubuntu

导入和到出sql（centos出现中文无法显示）

mysql 导入windows下的sql文件，数据库中文乱码。

1
设置mysql中文支持

找到mysql配置文件
find / -name *.cnf
cp  /显示的目录/my-large.cnf  /etc/my.cnf
vim /etc/my.cnf

在[clent] 和 [server]下都加一句
default-character-set=utf8
保存文件，重启mysql ，手动插入中文数据，查看中文数据显示正常

或者

查看mysql当前的编码格式
show variables like '%set%'
show variables like '%col%'


2
导入文件后依然中文乱码
原因:sql文件的编码形式是gbk ，而不管数据库是utf8的还是GBK的，导入文件必须是utf8编码的。
貌似因为系统环境是 utf8中文编码的。
解决方案： vim 打开sql文件，将文件编码变成utf8的
：set fileencoding=utf8
保存，查看当前编码
：set fileencoding   
注意： “：set fileencoding”会显示当前未保存的文件编码格式。最好查看后再保存
然后登录mysql
source test.sql
中文正常


注：mysql用utf8显示数据会边框什么的，会比较正常

mysql用户增加和密码

mysql链接远程主机

mysql -h主机地址 -u用户名 －p用户密码

mysql修改用户密码

方法一：

mysqladmin -u用户名 -p旧密码 password 新密码

mysqladmin的密码没有设置的话为空

方法二：先关掉mysql 。net stop mysql

>use mysql
>update user set Password=password('新密码') where User='root';
>flush privileges;

增加删除用户：用户名test1 ，密码 abc

grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;     //对所有数据库有权限,同时可以在网络访问，危险啊

grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;    //只对数据库mydb可用，且只能在localhosr本机可用

grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;    //密码设置为空

sql语句分为三类：
DDL 定义：create drop alter//针对抽象的字段，表，数据库
DML 操作：insert delete update select//针对具体的数据
DCL 控制：grant revoke//创建用户，限制用户权限。



DDL 
show databases;//显示所有数据库
create/drop database test1 //数据库删除，其下的表也会完全删除


use  test1 //选择数据库test1
//创建表：姓名，雇用日期，工资，


create table emp(enname varchar(10), hiredata date,sal decimal(10,2),deptno int(2));
show tables //显示所有表
desc emp //查看表定义：
show create table emp；//详细查看表定义：




drop database/table test //删除数据库/表




alter table emp modify ename varchar(20);//将表emp的ename属性改为 varchar（20）
alter table emp change ename ename2  varchar(10);//将表emp的ename属性改为 varchar（10），同时将字段（属性）改名


alter table emp add age int（2）after [某属性];//将表emp的增加属性age，没有after默认位最后
alter table emp drop age  ;//将表emp的属性age删除


alert table emp rename emp2//将表名改为emp2 






DML 


曾
insert into emp (ename,hiredate,sal,deptno) values('rt','2014-12-31','2000',1);
insert into emp values('123 567_91-3456789123','2014-22-58','9999.12345',1);//非法的日期将会默认为全 0；
insert into emp （ename,sal）values('deptno',1);//只插入ename 和sal 的值，其余位NULL
删
delete from emp where ename='rt'
delete  emp  //清空表中的数据

查：可以接where
select * from emp;
select ename sal from emp;
select distinct ename from emp;//去重显示
select *  from emp order by hiredate desc，sal asc;//按hiredate降序显示，hiredate相同的按工资升序排列。第一级的排序默认升序
select * from emp order  by sal limit 1,3;//按sal排列，从第二条记录起，显示前三个，limit在其他数据库不一定能用
聚合
where和having的区别是：前者是聚合前过滤，后者是对结果过滤，所以尽可能用where
select deptno ,count(1) from emp group by deptno having count(1)>1；//显示 deptno内总人数大于一的 deptno
select sum(sal),max(sal),min(sal) from emp;


表连接：内（找相同）、外（找不同）
select ename, deptname from emp ,dept where emp.deptno = dept.deptno;//找出每个人的deptname，默认只显示而这完全匹配的数据
select ename, deptname from emp left join dept on emp.deptno = dept.deptno;//找出每个人的deptname，左连接，强制显示emp中元素，即使没有找到匹配


子查询：
select * from emp where deptno in (select deptno from dept);
select emp.* from emp ,dept where emp.deptno=dept.deptno;//子查询转化为，表链接。
select * from emp ,dept where emp.deptno=dept.deptno;//子查询转化为，表链接，显示两个表所有内容



记录联合：union（去重） ，union all
将记录了相同数据的不同表，数据汇总在一起显示
select deptno from emp  union all select deptno from dept;//共打印12条记录，emp9条，dept3条





改
update emp set hiredate='2012-12-12' where ename='rt';//2012-12-12是字符串所以要加'',数字不需要加''
update emp a ,dept b set a.sql=a.sql*b.deptno , b.deptname=a.ename where a.deptno=b.deptname;//同时更新表emp 和 表 dept

连接：

left join  :显示左表所有数据和右表匹配的数据

right join ：、、、

inner join ：只返回左右边匹配的数据 和 where的功能相同但是where是隐式的连接而inner jion 是显示连接。结果相同，where接连接条件，inner join接连接条件

full join ：匹配和不匹配的数据都会返回

一般要使得数据库查询语句性能好点遵循一下原则:

1在做表与表的连接查询时，大表在前，小表在

2 不使用表别名，通过字段前缀区分不同表中的字段

3查询条件中的限制条件要写在表连接条件前

4 尽量使用索引的字段做为查询条件

inner join 连接三个表:

SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

或者不加（）也行,多个小连接列名相同的时候，mysql会一次给列名编号

select * from table0 t0

inner join table1 t1 on t1.id = t0.id

inner join table2 t2 on  t2.name = t0.name

inner join table3 t3 on t3.other = t0.other

where t1.id=123 nad t2.name = '狗' and t3.other = 'xxx'

eclipse 与 mysql的连接，用java读写数据库

package cn;
import java.sql.*;
//import java.io.*;

public class my
{ 
		public static void main(String args[]) 
	{ 
			try 
			{
				Class.forName("com.mysql.jdbc.Driver"); //加载MYSQL JDBC驱动程序 
				System.out.println("Success loading Mysql Driver!"); 
			} catch (Exception e)
			{ 
				System.out.print("Error loading Mysql Driver!"); 
				e.printStackTrace();
			} 
			
		try
		{ 
			Connection connect = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root",""); 
			System.out.println("Success connect Mysql server!");
		//返回
			 Statement stmt = connect.createStatement();
			ResultSet rs = stmt.executeQuery("select * from emp");//emp为表的名称  
			
	
			while (rs.next()) 
			{ 
				System.out.println(rs.getString("ename"));//ename是表t_auth的一个字段
			}
		//插入
			
			String sql="insert into emp (ename,hiredate,sal,deptno)  VALUES   (?,?,?,?) ";//共四列，四个字段
			PreparedStatement Statement=connect.prepareStatement(sql); 
		 
			//插入数据：1ename,	2hiredate, 3sal,   4 deptno
			Statement.setString(1,"c");//匹配char
			Statement.setString(2,"2014-04-13");//匹配date类型
			Statement.setDouble(3,12345.678);//匹配 decimal
			Statement.setInt(4,7);

			
			
			System.out.println("222");

			Statement.executeUpdate(); //
			System.out.println("333");
			} catch (Exception e)
			{ 
				System.out.print("get data error!"); e.printStackTrace(); 
			} 
		} 
}

Windows：

安装最新版的mysql5.7 完整版：

名字:mysql-installer-community-5.7.3.0-m13.2063434697.msi

大小：237 MB 

安装过程：服务名设置为 mysql，我的用户名和密码都设置为 root

完全安装后：直接可以在 cmd中  用net start\stop mysql 来启动关闭

配置外网链接

1 防火墙：打开3306端口（win7 防火墙--高级--入站规则--新建-----------）

2在mysql配置中： networking -- bind-address  这个值默认是*，我设置为0.0.0.0    //重启mysql

3 //为root设置权限可以外网使用,第一个root是用户，第二个root是密码

  mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 

//使授权生效

   mysql>flush privileges; 

4 第三步结束后，立竿见影，马上能 从任何主机 以root访问数据库的所有数据库

客户端远程使用mysql，可以用navicat for mysql 很不错的工具，支持各种数据库，适合需要连接各种不同的数据库的人

其他：

授权：用户从ip 192.168.1.3   用户名为myuser  密码 mypasswd 连接mysql的数据库 dk

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 
FLUSH   PRIVILEGES; 
允许连接所有数据库就将 dk改为 * ，允许任何ip就将 ip改为  % 

网上的其他配置方法中，很多已经过时，如下已经在mysql5.7完整版 中 配置好了，使用了以后会提示没有任何功能或者记录受到影响。

以下操作都是不需要的，但是可以用来理解mysql的机制

比如：mysql 5.7 默认root 的 host 是%，即可以从任何IP登陆

1. %  允许从任何ip登录  
2. X.X.X.X  允许从指定的ip访问  

1. mysql>use mysql;  
2. mysql>update user set host = '%' where user ='root';  
3. mysql>select host, user from user;  //可以看到host设置情况
4. mysql>flush privileges;  

mysql：

如果要清空表中的所有记录，可以使用下面的两种方法：

自增字段恢复为 1（下面两句，效果一样，但是返回不同）

delete from tablename（返回删除的记录数目 ）

truncate table tablename（返回 0 ）

自增字段不恢复1 

delete from tablename where1 或者 where true（返回删除的记录数目 ）

相比没有where的delete情况，它扫描了每条记录，速度要慢很多

删除表users 名为nike的记录的前 6 条：
delete FROM users WHERE name = 'nike' ORDER BY id DESC LIMIT 6;