Mysql基本sql指令

Mysql:

//查看mysql版本等状态

status;

SQL-DDL（数据库定义语言）

数据库：

show databases;  //展示数据库
create database database1;  //创建数据库
use database1;  //进入数据库
drop database database1;  //删除数据库

表：

show tables;  //展示表

/*创建表*/
create table table1(
id int not null auto_increment primary key,
name varchar(16) not null,
age int not null
) ENGINE=InnoDB;

describe table1;  //表属性
select * from table1;  //表内容

SQL-DML（数据库操作语言）

表的curd操作：

/*创建表*/
create table books (
book_id int,
title varchar(50),
author varchar(50)
);


/*修改表*/
alert table books
modify book_id book_id int unsigned auto_increment primary key
modify author auhor_id int
drop title
add title varchar(50);


/*插入数据*/
create table test (num int , name char(10), sex char(5));
insert into test(num,name,sex) values(10,'tom','m');  //方式1
insert into test values(10,'tom','m'); //方式2
insert into test values(10,'tom','m'),(11,'lucy','f'); //插入多行数据
insert into test values(null,'tom',null); 
insert into test(name,sex) values('tom','m');

insert test values('tom','m'); //若num为auto_increment则插入时不用理会
select last_insert_id(); //可以由此获得刚刚插入的numid

replace into test values(10,'tom','m') //若有重复则以新的替代的插入

/*更新数据*/

update test set num=11 where name='tom';

/*删除数据*/

delete from test where name='tom';
delete from test;  //删除所有行

/*约束：主键，外键，唯一等*/

create table test (id int auto_increment primary key, name varchar(50), sex char(5));
create table test (id int auto_increment, name varchar(50), sex char(5), primary key(id));
create table test (id int auto_increment, name varchar(50), sex char(5));
alert table test add primary key(id); //这里默认主键约束名为PRIMARYKEY
alert table test constraint PK_ID primary key(id); //这里修改了主键约束名为PK_ID
alert table test drop constraint PK_ID;

create table books (isbn char(15) primary key, author_id int, title char(20), foreign key(author_id) references authors(author_id));
alert table books add constraint FK_ID foreign key(author_id) references authors(author_id);
 
alert table persons add constraint UN_ID unique(firstname,lastname); //则名和姓的组合必须唯一

/*select查询*/

select * from books where title='harry potter';

/*select结果合并姓名为一列*/
select book_id,concat(firstname, '', lastname) as name from books, authors where books.author_id = authors.author_id;

SELECT empno, ename, job FROM emp WHERE ename = 'SMITH'
SELECT empno, ename, job FROM emp WHERE ename <> 'SMITH'  /* 也可以使用!= */
SELECT empno, ename, sal FROM emp WHERE sal>= 1500
SELECT * FROM emp WHERE deptno=30 and sal>1500;  /*  and */
SELECT * FROM emp WHERE job='MANAGER' or job='SALESMAN'  /* or */
SELECT * FROM emp where sal BETWEEN 800 and 1500;
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 /* not */

SELECT * FROM emp where sal not BETWEEN 800 and 1500;  /* between */
SELECT * FROM emp where ename in ('SMITH', 'KING');  /* in */
SELECT * FROM emp where ename like 'S%';  /* 模糊查询 通配符: ‘%’（0个多个字符）； 通配符: ‘_’ （单个字符） */
SELECT * FROM emp where ename like 'S_ITH';


select job,deptno from emp;
select all job,deptno from emp;  /* 默认是all */
select distinct job,deptno from emp;  /* 去除重复记录 */
select * from dept where deptno in (SELECT DISTINCT deptno from emp); /* 查询有员工的部门信息 */

/* UNION (无重复并集)：当执行UNION 时，自动去掉结果集中的重复行，并以第一列的结果进行升序排序。*/
select empno,ename,job from emp where job='SALESMAN'
union  /* 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;

/*连接操作*/

内连接：包括等值连接和非等值连接，结果中只有匹配的数据

外连接：包括左连接和右连接，全连接，结果中包含不匹配的数据。

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

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

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

自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，可以选择其内外连接的方式，它删除连接表中的重复列。

select * from emp,dept /*交叉连接 */

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;   /*内连接（等值连接） */
select * from emp,dept where emp.deptno=dept.deptno;  /*这种方式不用ON*/

select * from emp INNER JOIN dept ON emp.deptno > dept.deptno;   /* 内连接（不等连接）*/
select * from emp,dept where emp.deptno > dept.deptno;

select A.ename as manager, B.ename as emploee from emp A, emp B where A.mgr = B.empno;   /*内连接（自身连接） */

select * from emp left join dept on emp.deptno=dept.deptno   /*外连接（左连接） */

select * from emp right outer join dept on emp.deptno=dept.deptno /* 外连接（右连接） */


/* 自然连接默认为等值，且会合并deptno一项，而外连接不会 */
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;

/*子查询*/

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

<any，小于子查询中的某个值。等价于<max

>any，大于子查询中的某个值。等价于>min

>all，大于子查询中的所有值。等价于>max

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

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

/*一下所有的别名都省略了as*/
/* 查询员工及其领导名称 */
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' and deptno in (select deptno from dept);

/* 子查询，同上 */
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;

/* 子查询，多出deptno=90的行 */
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';


/* 列出薪金比'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 * 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');

/* 列出与“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');

/* 聚合函数，group by，order by，having */

a.聚合函数一般用于统计，常用如下:

count(field)  //记录数    avg(field)     //平均值
min(field)     //最小值    max(field)    //最大值

sum(field)    //总和

b.分组：group by/having：分组查询通常用于统计，一般和聚合函数配合使用

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

/* 列出各部门各有多少人 */
select deptno, count(*) 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) >= all (select sum(sal) from emp group by deptno);

通常的执行情况是FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
当order by的字段为select中不存在的时，则先order by再select，显然先select再order效率更高。

参考： 《 Mysql数据库学习（三）：表的crud操作、完整性约束、select各种查询》