MySQL基本命令

登录

mysql -hhostname -Pport -uusername –p

比如：mysql –hlocalhost -P3306 –uroot –p     //主机名 端口号  用户名   密码   同一台服务器上前两个省略

显示所有数据库：show databases;
选定数据库：use dbname;
显示当前数据库中所有表：show tables;
显式指定数据库中的所有表：show tables from dbname;
放弃正在输入的命令：\c
显示命令清单：\h
退出mysql程序：\q

查看MySQL服务器状态信息：\s

SQL

SQL即Structured Query Language；

DDL（ Data Definition Language数据定义语言）
——用来建立数据库、数据库对象和定义其列
——CREATE 、DROP 、ALTER 等
DCL（ Data Control Language数据控制语言）
——用来控制存取许可、存取权限等；

——GRANT、REVOKE 等；

DML（ Data Manipulation Language数据操作语言）
——查询、插入、删除和修改数据库中的数据；

——SELECT、INSERT、 UPDATE 、DELETE等；

创建数据库

create database dbname [数据库选项];  //创建数据库
show create database dbname;    //查看创建数据库的SQL语句

alter database dbname character set gbk   //更改字符集

注意：数据库的名称可以是中文的，有些特殊的名称需要加上· ·，如·123·，

create user username identified by '123456'；//创建数据库 密码为123456

drop user username ;//删除用户

select user();查看当前用户

grant select,insert,update,delete on *.* to username @'%' identified by '123456';//创建具有权限的用户

grant all privileges on *.* to username@'%'；//赋予权限 ,但没有Grant_priv权限

revoke all privileges on *.* from username@'%' ；//回收权限

修改密码

 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

mysqladmin -u root -poldpass password newpass
mysqladmin -uabc -p123456 password 123(要有权限才可以修改)

CRUD

表的CRUD（增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete) ）

use study ;   //选择某个数据库

create table t_emp(empno int, ename varchar(20), esex char(2));//在数据库study中新建表t_emp;

或者create table study.t_emp(empno int, ename varchar(20), esex char(2));//在数据库study中新建表t_emp;

alter table t_emp modify ename varchar(30);    //对表进行修改
alter table t_emp drop esex;
alter table t_emp add esex char(2);

insert into t_emp(empno, ename, esex) values(1000, 'tom', 'm');  //插入数据
insert into t_emp values(1000, 'maggie', 'f');
insert into t_emp(empno, ename) values(1002, 'john');
insert into t_emp(empno, ename, esex) values(1003, null, 'm');
insert into t_emp values(1004, '张三', '男');

show variables like 'character_set%';  //显示字符集    

set names gbk;    //修改字符集

update t_emp set empno=1001 where ename='maggie';  //跟新值
delete from t_emp where esex is null;    //删除某一项
delete from t_emp;   //删除表中的所有记录
desc table;  //显示表结构

select * from table;  //查找表

drop table tablename ; //删除表

完整性约束

主键 (constraint)

create table t_emp(empno int not null primary key, ename varchar(20), esex char(2));
create table t_emp(empno int, ename varchar(20), esex char(2), primary key (empno));
create table t_emp(empno int, ename varchar(20), esex char(2), constraint PK_EMPNO primary key(empno));
create table t_emp(empno int, ename varchar(20), esex char(2));
alter table t_emp add constraint PK_EMPNO primary key(empno);
insert into t_emp values(1000, 'john', 'm');
insert into t_emp values(1000, 'lily', 'f');

insert into t_emp values(null, 'lily', 'f');

外键

create table t_emp(empno int, deptno int, ename varchar(20), esex char(2));
alter table t_emp add constraint PK_EMPNO primary key(empno);
create table t_dept(deptno int, dname varchar(20));

alter table t_dept add constraint PK_DEPTNO primary key(deptno);

alter table t_emp add constraint FK_DEPTNO foreign key(deptno) references t_dept(deptno);//t_emp中的depto或者为null,或者为t_dept中deptno的值   外键约束
set names gbk;
insert into t_dept values(2001, '人事部');
insert into t_dept values(2002, '技术部');
insert into t_emp values(1001, 2001, 'john', 'm');

insert into t_emp values(1003, 2003, 'john', 'm');   //出现错误

自定义完整性约束

create table t_test1(id int auto_increment primary key, name varchar(30), age int default 20);//auto_increment必须是primary key

insert into t_test1 values(null, 'aaa');
insert into t_test1 values(null, 'aaa', null);
insert into t_test1 (name) values( 'bbb');   //其余默认


create table t_test2(id int, name varchar(30), age int);
alter table t_test2 add constraint CC_AGE check (age >=18 and age<=60);  //这条语句不生效  用户自定义完整性约束
alter table t_test2 add constraint CC_AGE check (length(name)>2);

数据类型

数值类型

日期和时间类型

字符串类型

数值

unsigned
zerofill   指定最少的宽度 不足用零

bool是tinyint(1)的别名

IEEE 754浮点数表示
S:符号位
E:指数位
M:尾数位，也叫有效数字位
N =(-1)^S * M * 2^E
单精度4个字节，32位，取值范围-3.402823466E+38~3.402823466E   精度6位
双精度8个字节，64位，取值范围-1.7976931348623157E+308~1.7976931348623157E+308  精度17位

DOUBLE[(M,D) ]  [UNSIGNED] [ZEROFILL] 
M总位数，D是小数点后面的位数

real是double的别名  

DECIMAL[(M,D])] [UNSIGNED] [ZEROFILL]
M缺省是10，D缺省是0
decimal取值范围与double是一样的，但是有更高的精度。numeric是decimal的别名

create table t_number(a tinyint, b tinyint unsigned);
insert into t_number values(100, 200);
insert into t_number values(-129, 200);
insert into t_number values(128, 200);
insert into t_number values(127, 200);
insert into t_number values(127, -1);


create table t_number2(a int(4) zerofill);
insert into t_number2 values(123);
insert into t_number2 values(123456);


create table t_number3(a bool);


create table t_number4(a float, b double);
insert into t_number4 values(12345678.12345, 12345678.12345);
insert into t_number4 values(12345678.12345, 123456789123456789.12345);
insert into t_number4 values(12345678.12345, 12345678912345.12345);

create table t_number5(a double(5,2));
-999.99 ~999.99


create table t_number6(a decimal(30,6));
insert into t_number6 values(123456789123456789.12345);
create table t_number7(a decimal(5,2));
-999.99 ~999.99

日期和时间类型

create table t_datetime(a datetime);
insert into t_datetime values('2014-01-15 10:10:10');
insert into t_datetime values('9999-12-31 23:59:59');
insert into t_datetime values('10000-01-01 00:00:00');
insert into t_datetime values('999-01-01 00:00:00');  //能够存但不保证正常
insert into t_datetime values('99-01-01 00:00:00');  //70-99 补上19
insert into t_datetime values('69-01-01 00:00:00'); //00到69补上20

create table t_timestamp(a timestamp);
insert into t_timestamp values('2014-01-15 10:10:10');
insert into t_timestamp values('2038-01-19 03:14:07');
insert into t_timestamp values('2038-01-19 03:14:08');//能够插入 与时区有关
insert into t_timestamp values('2038-01-19 11:14:07');
insert into t_timestamp values('2038-01-19 11:14:08');//不能够插入 

create table t_date(a date);
insert into t_date values('2012-01-01');

create table t_time(a time);
insert into t_time values('23:12:12');
insert into t_time values('823:12:12');
insert into t_time values('3 23:12:12');

create table t_year(a year);
insert into t_year values (2000);
insert into t_year values ('2155');
insert into t_year values (2156);

字符串类型

char(M)
varchar(M)
这里的M表示字符数

• CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。
• VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

create table t_varchar(a varchar(65535)); // 不能创建成功 utf-8中一个字符占3字节
create table t_varchar(a varchar(21845)); // 不能创建成功 需要空间来保存长度
create table t_varchar(a varchar(21844));

binary/varbinary

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值

• VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，存储需要5个字节。
• 对于CHAR、VARCHAR和TEXT类型，前面的表中的值L和M应解释为字符数目，并且列定义中的这些类型的长度表示字符数目。例如，要想保存一个TINYTEXT值需要L字符+ 1个字节

create table t_enum(sex enum('male', 'female'));
insert into t_enum values('male');
insert into t_enum values('female');
insert into t_enum values('other');
insert into t_enum values(1);
select sex+0 from t_enum;

create table t_set(favourite set('dog', 'cat', 'bird'));  //按位存储

insert into t_set values('dog,cat');  //3
insert into t_set values('dog,bird');  //5
select favourite+0 from t_set;

查询-基本格式  

三个表emp员工表  dept 部门表

数据查询-列

查询指定列 SELECT empno,ename,job FROM emp;

查询所有列 SELECT * FROM emp;

列--别名 SELECT empno as '工号',ename '姓名' FROM emp;  //取别名

使用常量列 SELECT empno, 'C++教程网' FROM emp;

连接符  SELECT empno,concat(ename,'#') FROM emp;

oracle可以用||作为连接符 SELECT empno, ename||'#' FROM emp;

数据查询-where

SELECT empno, ename, job FROM emp WHERE ename = 'SMITH' ；//也可以用大于小于不等于

数据查询-条件(and,or)

SELECT * FROM emp WHERE deptno=30 and sal>1500;

SELECT * FROM emp WHERE job='MANAGER' or job='SALESMAN'

数据查询-between and

SELECT * FROM emp where sal BETWEEN 800 and 1500;//存在not between 用法

SELECT * FROM emp where sal >= 800 and sal <= 1500;

数据查询-空值

SELECT empno, ename, sal, comm FROM emp WHERE comm is null

SELECT empno, ename, sal, comm FROM emp WHERE comm is not null

数据查询-in

SELECT * FROM emp where ename in ('SMITH', 'KING');

模糊查询-like

查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容
通配符: ‘%’  （0个多个字符）

通配符: ‘_’   （单个字符）

SELECT * FROM emp where ename like 'S%';

SELECT * FROM emp where ename like 'S_ITH';

数据查询-排序

SELECT * FROM emp ORDER BY ename ; //升序

SELECT * FROM emp ORDER BY ename desc; //降序

SELECT empno, ename, job FROM emp ORDER BY 2 desc；//按第二个字段降序

SELECT * FROM emp ORDER BY job asc, sal desc;//按多个列名排序 job升序 sal 降序

limit  可用于分页查询

select * from emp ORDER BY sal limit 5;    //只显示前五条记录

select * from emp ORDER BY sal limit 5,5;//前面表示偏移，后面表示条数

distinct  去除重复记录

select distinct job,deptno from emp;

查询有员工的部门信息  
select * from dept where deptno in (SELECT DISTINCT deptno from emp);  

union

UNION (无重复并集)：当执行UNION 时，自动去掉结果集中的重复行，并以第一列的结果进行升序排序。

union即联合查询
select empno,ename,job from emp where job='SALESMAN'
union 
select empno,ename,job from emp where job='MANAGER';   
select empno,ename,job from emp where job='SALESMAN' or job='MANAGER'  //没有排序

UNION ALL (有重复并集)：不去掉重复行，并且不对结果集进行排序。

select job, sal from emp where empno=7902
union all
select job, sal from emp where empno=7788;   


select job, sal from emp where empno=7902
union
select job, sal from emp where empno=7788;

多表查询

• 交叉连接：交叉连接是不带WHERE 子句的多表查询，它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

select * from emp,dept

• 内连接：                                                                                                                                            内连接(等值连接)，在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

select * from emp,dept where emp.deptno=dept.deptno;

       内连接（不等连接）： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运 算符包括>、>=、<=、<、!>、!<和<>

select * from emp INNER JOIN dept on emp.deptno > dept.deptno;

select * from emp,dept where emp.deptno > dept.deptno;

        内连接(自身连接)：思考：查询员工及其领导名称，格式如下员工 领导

select A.ename 员工, B.ename 领导  from emp A, emp B where A.mgr = B.empno

• 外连接:  左外连接返回包括左表中的所有记录和右表中联结字段相等的记录；即左外连接就是在等值连接的基础上加上主表中的未匹配数据

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; //内连接
select * from emp left outer join dept on emp.deptno=dept.deptno

         右外连接返回包括右表中的所有记录和左表中联结字段相等的记录；即右外连接是在等值连接的基础上加上被连接表的不匹配数据

select * from emp right outer join dept on emp.deptno=dept.deptno

       外连接全连接,全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上

select * from emp left  join dept on emp.deptno=dept.deptno
union

select * from emp right  join dept on emp.deptno=dept.deptn

• 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列

SELECT *
FROM emp NATURAL JOIN dept;  //将左表和右表的未匹配数据都加上

SELECT *
FROM emp NATURAL LEFT JOIN dept;   //包含连接表中未匹配项

SELECT *
FROM emp NATURAL RIGHT JOIN dept; //包含 被连接表中未匹配项

子查询

子查询即一个查询语句嵌到另一个查询语句的子句中； 可以出现在另一个查询的列中，where子句中，from子句中等。

select A.ename 员工, B.ename 领导  from emp A, emp B where A.mgr = B.empno;  //上面的内连接
等价于
select ename 员工, (select ename from emp where empno = e.mgr) 领导 from emp e;    //子查询

有些时候连接查询，也可以用子查询来实现

思考：列出所有“CLERK”（办事员）的姓名及其部门名称。

select ename, dname from emp,dept where job='CLERK' and emp.deptno = dept.deptno;

select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK';

select ename, dname from emp  LEFT JOIN dept on emp.deptno = dept.deptno where job='CLERK';
select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK' and deptno in (select deptno from dept);

思考：列出薪金比'SMITH'高的员工

select * from emp where sal > (select sal from emp where ename='SMITH');

select * from emp e where hiredate < (select hiredate from emp where empno=e.mgr);

select ename, dname
from
(select ename, (select dname from dept where deptno=e.deptno) dname
from emp e where job='CLERK') a
where dname is not null;

any/all

<any，小于子查询中的某个值。等价于<max
>any，大于子查询中的某个值。等价于>min
>all，大于子查询中的所有值。等价于>max

<all，小于子查询中的所有值。等价于<min

查询薪金小于销售员某个员工的员工信息

select * from emp WHERE
sal < any (select sal from emp where job='SALESMAN');

select * from emp WHERE
sal < (select max(sal) from emp where job='SALESMAN');


select * from emp WHERE

sal > all (select sal from emp where job='SALESMAN');

exists

exists 存在性条件判断
若内层查询非空，则外层的where子句返回真值，否则返回假。not exists相反

列出与“SCOTT”从事相同工作的所有员工

select * from emp e where EXISTS
(
select * from emp where ename='SCOTT' and e.job = job
);

select * from emp where job =(select job from emp where ename='SCOTT');

select * from emp e where EXISTS
(
select * from emp where ename='SCOTT' and e.job = job
) and ename<> 'SCOTT';  //排除掉SCOTT本身

聚合函数

聚合函数一般用于统计，常用如下:
count(field)  //记录数
avg(field)     //平均值
min(field)     //最小值
max(field)    //最大值

sum(field)    //总和

select count(comm) as 记录数 from emp;  //commn不为空，统计行
select count(*) as 记录数 from emp;  //统计行数，扫描所有字段

select count(0) as 记录数 from emp;  //扫描行首，统计行

select avg(sal) as 平均薪金,
max(sal) as 最高薪金,
min(sal) as 最低薪金,
sum(sal) as 薪金总和

from emp;

group by/having

分组查询通常用于统计，一般和聚合函数配合使用

分组查询格式

select 分组字段或聚合函数
from 表 
group by 分组字段 having 条件 

order by 字段

select deptno, count(*)  num from emp group by deptno;  //列出部门员工数 
select deptno, count(*) cn from emp group by deptno HAVING cn > 3 ORDER BY cn desc;

列出各部门信息以及部门人数   //

select * ,(select count(*) from emp group by deptno having deptno=dept.deptno) total 

from dept 

select *, ifnull((select count(*) from emp group by deptno HAVING deptno = dept.deptno), 0) total 
from dept;  //将空值置为0

查询出薪金成本最高的部门的部门号和部门名称

select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname

HAVING sum(sal) >= all (select sum(sal) from emp group by deptno)

select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
HAVING sum(sal) >= (
select max(t.total)
from
(select sum(sal) total from emp group by deptno) t

)

MySQL函数

控制流程函数

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

SELECT CASE 1 WHEN 1 THEN 'one' 
WHEN 2 THEN 'two' ELSE 'more' END;

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

思考：查询员工的薪金等级     员工 薪金 等级

select ename 员工, sal 薪金 , case grade when 1 then '一级' 
when 2 then '二级' 
when 3 then '三级' 
when 4 then '四级' 
when 5 then '五级' 
end
等级
from emp, salgrade

where sal between losal and hisal 

ifnull(exper1,exper2)  exper1不为空则返回exper1de 值，否则返回exper2的值。

if(exper1,exper2,exper3)  如果exper1为真，返回exper2 ,否则返回exper3 ;

字符串函数

SELECT ASCII('2a');   返回最左边字符的ASCII
SELECT ASCII('a2');
SELECT BIN(12);    返回二进制字符串

SELECT BIT_LENGTH('text');  返回二进制字符串的长度

SELECT CHAR(77,121,83,81,'76');  //整数转化为字符串
SELECT CHAR(77,121,83,81,76);

数值函数

SELECT 3+5;
SELECT 3/5;

SELECT ABS(-32);

日期和时间函数

now

       select now();

date_add/adddate

        SELECT DATE_ADD('1998-02-02', INTERVAL 31 DAY);   日期加上天数后的日期
        SELECT DATE_ADD('1998-02-02', INTERVAL 28 DAY);
        SELECT adddate('1998-02-02', INTERVAL 28 DAY);
        SELECT adddate('1998-02-02', 28);

datediff  计算时间差

       select DATEDIFF(now(),'2014-02-01');
        select DATEDIFF('2014-02-01','2014-03-01');

date_format  时间进行格式控制

        select DATE_FORMAT(now(), '%H:%i:%s');    时分秒
         select DATE_FORMAT(now(), '%Y%M%D');  年月日   英文形式
         select DATE_FORMAT(now(), '%Y%m%d');  年月日  数字形式