MySQL（一）：SQL语句

1.DDL数据定义语言

1.1、 创建，查看以及使用/切换库

创建数据库db1：
create table db1;
查看当前数据库：
select database();
进入库db1：
use db1;
创建数据库并指定默认字符集UTF-8：
create database db1 default character set utf8;
查看库是什么字符集：
show create database db1;
查看当前MySQL使用的字符集：
show variables like 'character%';

1.2 常用数据类型

<1>整数型

TINYINT：1 字节，有符号范围(-128，127)，无符号范围(0，255)，小整数值。
SMALLINT：2 字节，有符号范围(-32768，32767) ，无符号范围(0，65535)，大整数值。
MEDIUMINT：3 字节，有符号范围(-8388608，8388607)，无符号范围(0，16777215)，大整数值。
INT：4 字节，有符号范围(-2147483648，2147483647)，无符号范围(0，4294967295)，大整数值。
BIGINT：8 字节，(0，2的64次方减1)，极大整数值。

<2>浮点型

 FLOAT(m,d）：4 字节，单精度浮点型，备注：m代表总个数，d代表小数位个数。
 DOUBLE(m,d）：8 字节 ，双精度浮点型，备注：m代表总个数，d代表小数位个数。

<3>定点型

 DECIMAL(m,d）：依赖于M和D的值，备注：m代表总个数，d代表小数位个数。

<4>字符串类型

 CHAR：0-255字节，定长字符串。
 VARCHAR：0-65535字节，变长字符串。
 TINYTEXT：0-255字节，短文本字符串。
 TEXT：0-65535字节，长文本数据。
 MEDIUMTEXT：0-16777215字节    中等长度文本数据。
 LONGTEXT：0-4294967295字节  极大文本数据。

char的优缺点：存取速度比varchar更快，但是比varchar更占用空间

<5>时间型

 date：3，yyyy-MM-dd，存储日期值
 time：3，HH:mm:ss，存储时分秒
 year：1，yyyy，存储年
 datetime：8 ，yyyy-MM-dd HH:mm:ss，存储日期+时间
 timestamp：4，yyyy-MM-dd HH:mm:ss，存储日期+时间，可作时间戳

1.3 创建表

语法：

CREATE TABLE 表名 (
                  字段名1 字段类型1 约束条件1 说明1,
                  字段名2 字段类型2 约束条件2 说明2,
                  字段名3 字段类型3 约束条件3 说明3
                  );

约束条件：

comment：说明解释
not null：不为空
default：默认值
unsigned：无符号（即正数）
auto_increment：自增
zerofill：自动填充
unique key：唯一值

示例：

create table student(
     id int auto_increment not null comment '学号',
     name varchar(20) comment '姓名',
     age tinyint,
     class varchar(20),
     unique key (id)
     );

1.4 查看表

查看数据库中的所有表：show tables；
查看表结构：desc 表名;
查看创建表的sql语句：show create table 表名;

1.5 表结构维护与删除

修改表名：
rename table student to user;
添加列：
alter table user add address varchar(50) comment '地址';
修改列：
alter table user change servnumber telephone varchar(20);
删除列：
alter table user drop telephone;
删除表：
drop table user;

2、DML数据操纵语言

2.1 插入表数据

insert into 表名（字段名） values（字段对应值）;
insert into user (name,age,class,address) values ('张一',18,'三年二班','河南省洛阳市');

2.2 修改及删除

修改

update 表名 set 字段名1=值1 where 字段名=值;
update user set age = 19 where name = '张一';

删除：

delete from 表名 where 字段名=值;
delete from user where id = 1;

3、DQL数据查询语言

数据准备

/*创建部门表*/
CREATE TABLE dept(
    deptnu      INT  PRIMARY KEY comment '部门编号',
    dname       VARCHAR(50) comment '部门名称',
    addr        VARCHAR(50) comment '部门地址'
);
​
某个公司的员工表
CREATE TABLE employee(
    empno       INT  PRIMARY KEY comment '雇员编号',
    ename       VARCHAR(50) comment '雇员姓名',
    job         VARCHAR(50) comment '雇员职位',
    mgr         INT comment '雇员上级编号',
    hiredate    DATE comment '雇佣日期',
    sal         DECIMAL(7,2) comment '薪资',
    deptnu      INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
​
/*创建工资等级表*/
CREATE TABLE salgrade(
    grade       INT  PRIMARY KEY comment '等级',
    lowsal      INT comment '最低薪资',
    higsal      INT comment '最高薪资'
);
​
/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');
​
/*插入emp表数据*/
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000,  10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500,  30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
​
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

3.1 where条件查询

精确条件查询：
select * from employee where ename = '林俊杰';
模糊条件查询：
select * from employee where ename like '%林%';
范围查询：
select * from employee where sal between 8000 and 10000;
离散查询：
select * from employee where ename in ('林俊杰','周杰伦','猪八戒');
清除重复值：
select distinct(job) from employee;
统计查询：
select count(*) from employee;
select avg(sal) from employee;

3.2 group by分组查询

常用于统计场合，一般和聚合函数连用。

select deptnu,count(*) from employee group by deptnu;

3.3 having条件查询

一般跟在group by之后，对查询的结果进行筛选操作

select deptnu,count(*) from employee group by deptnu having deptnu = 10;

3.4 order by排序查询

对查询的结果进行排序操作

select * from employee order by sal;

3.5 limit限制查询

对查询结果起到限制条数的作用。limit n，m； n:代表起始条数值，不写默认为0；m代表：取出的条数。

select * from employee limit 2,3

3.6 左连接与右连接查询

左连接说明： left join 是left outer join的简写，左(外)连接，左表(a_table)的记录将会全部表示出来， 而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右连接说明：right join是right outer join的简写，与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

select * from employee e left join dept d on e.deptnu = d.deptnu where e.empno = 1007;

3.7 内连接查询与联合查询
获取两个表中字段匹配关系的记录,INNER JOIN 表名 ON 条件;

select e.*,d.dname from employee e inner join dept d on e.deptnu = d.deptnu and e.ename = '林俊杰';
select e.*,d.dname from employee e , dept d where e.deptnu = d.deptnu and e.ename = '林俊杰';

联合查询：就是把多个查询语句的查询结果结合在一起
union查询的注意事项：
(1)两个select语句的查询结果的“字段数”必须一致；
(2)通常，也应该让两个查询语句的字段类型具有一致性；
(3)也可以联合更多的查询结果；
(4)用到order by排序时，需要加上limit（加上最大条数就行），需要对子句用括号括起来

(select * from employee where ename = '林俊杰') 
union (select * from employee where ename = '猪八戒');

4、练习

查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
select count(*) total_num ,deptnu from employee group by deptnu;
select d.deptnu,d.dname,d.addr,total_num 
    from dept d,
        (select count(*) total_num ,deptnu from employee group by deptnu) e 
            where d.deptnu = e.deptnu; 

列出薪资比安琪拉高的所有员工
select * from employee  
    where sal > (select sal from employee where ename = '安琪拉');

列出所有员工的姓名及其直接上级的姓名。
select e.ename,leader.ename 
    from employee e left join employee leader 
      on e.mgr = leader.empno ;

列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
select e.empno,e.ename,dept.dname from 
      employee e left join employee leader 
        on e.mgr = leader.empno 
                left join dept on e.deptnu = dept.deptnu 
                    where e.hiredate<leader.hiredate;

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;

列出所有文员的姓名及其部门名称，所在部门的总人数。
select b.ename,a.dname,b.job,c.zongshu 
      from dept a ,employee b ,
          (select deptnu,count(*) as zongshu from employee group by deptnu) c 
              where a.deptnu=b.deptnu and b.job='文员' and b.deptnu=c.deptnu;