SQL语句学习笔记

SQL语句的分类：       
    DDL： 数据定义语言
        create / drop / alter   
    DML：数据操作语句
        insert / delete /update / truncate  
    DQL： 数据查询语言：
        select / show
CRUD
一、  数据库操作：
增：
创建数据库: create database db1;
删：
    删除数据库：drop database db1;
改：
    修改数据库字符集：alter database db1 default character set gbk;
查：
    查询所有数据库：show databases;
    查看数据库默认字符集：show create database db1;
    选择数据库：use db1;
二、  表操作：
增：
    创建表：create table student(id int,name varchar(20),age int);
    数据约束
1.  默认值：
Create table student(
        Id int,
        Name varchar(20),
        Address varchar(20) default ‘北京’
);
2.  非空:
Create table student(
        Id int,
        Name varchar(20),
        Gender varchar(2) not null
);
3.  唯一：
Create table student(
        Id int unique,
        Name varchar(20)
);
4.  主键:
Create table student(
        Id int primary key, -- 非空+唯一
        Name varchar(20)
);
5.  自增长：
Create table student(
        Id int(4) zerofill primary key auto_increment, -- 自增长，从0开始s
        Name varchar(20)
);
可以影响自增长约束的删除：truncate table student;
6.  外键:
主表：部门表
从表：员工表
Create table dept(
        Id int primary key,
        deptName varchar(20)
);
Create table employee(
        Id int primary key,
        empName varchar(20),
        deptId int,
        constraint employee_dept_fk foreign key(deptId) references dept(id)
);
7.  级联修改：
Create table dept(
        Id int primary key,
        deptName varchar(20)
);
Create table employee(
        Id int primary key,
        empName varchar(20),
        deptid int
        constraint emplee_dept_fk foreign key(deptid) references dept(id) on update cascade on delete cascade
);
删：
    删除表：drop table student;
    可以影响自增长约束的删除：truncate table student;
改：
    表操作：
        修改表名称：alter table student rename to students;
    字段操作：
        添加字段：alter table student add gender varchar(2);
        删除字段：alter table student drop gender;
        修改字段类型：alter table student modify name varchar(10);
        修改字段名称：alter table student change name stuName(20);
查：
    查看所有表：show tables;
    查看指定表结构：desc student;
三、数据操作：
增：
    增加数据：insert into student values(1,’张三’,’男’,20);
              Insert into student(id,name) values(2,’李四’);
删：
    删除数据：delete from student;
              delete from student where id=1;
              truncate table student; -- 不能带条件删除
改：
    修改数据：update student set gender=’女’;
              update student set gender=’男’ where id=1;
              update student set gender=’男’,age=18 where id=2;
查：
    查询所有列:
        select * from student; 
    查询指定列:
        select id,name,gender from student;
    查询时添加常量列:
        select id,name,gender,age,’默认值’ as ‘添加的常量列名’ from student; 
    查询时合并列:
select id,name,(math+chinese) as ‘总成绩’ from student; 
    查询时取出重复记录:
        select distinct gender from student;
    条件查询:
        select * from student where id=2 and name=’张三’; 
        select * from student where math>90 or chinese<80;
        select * from student where math<>70;
        select * from student where math between 75 and 90; -- 包前包后;
        select * from student where address is null;
        select * from student where address is not null or and address<>’’;
        select * from student where name like ‘张_’;--模糊查询  张X
        select * from student where name like’李%’;-- 李XXXX…
    聚合查询：
        select sum(math) as ‘math总成绩’ from student;
        select avg(math) as ‘math平均分’ from student;
        select max(math) as ‘math最高分’ from student;
        select min(math) as ‘math最低分’ from student;
        select count(*) from student; -- 统计共有多少学生字段；
        select count(id) from student; -- 推荐
    分页查询：
        select * from student limit 0,n; -- 第一页 每页n条
        select * from student limit n,n; -- 第二页 
        select * from student limit 2n,n; -- 第三页
    查询排序：
        Select * from student order by id asc; 
        Select * from student order by id;      --正序
        Select * from student order by id desc;  -- 反序
        Select * from student order by math asc,chinese desc; -- 优先级排序;
    分组查询：
        Select gender,count(id) from student group by gender;
    分组后筛选：
        Select gender,count(*) from student group by gender having count(*)>2;
    关联查询（多表查询）
    1.  内连接查询：
        Select empName,deptName
            From employee,dept
            Where employee.deptId=dept.id;

        Select empName,deptName
            From employee
            Inner join dept
            On employee.deptId=dept.id;

        Select empName,deptName
            From employee e
            Inner join dept d
            On e.deptId=d.id;
    2.  左外连接查询（左表的数据一定会完成显示）
        Select d.deptName,e.empName
            From dept d –deptName一定会显示
            Left outer join employee e 
            On d.id=e.deptId;
    3.  右外链接查询（右表的数据一定会完成显示）
        Select d.depaName,e.empName
            From dept d  
            Right outer join employee e -- empName一定会显示
            On d.Id=e.deptId;
    4.  自连接查询
        Select e.empName,b.empName
            From employee e
        Left outer join employee b
        On e.bossId=b.Id;
三、  存储过程：
存储过程—带有逻辑的sql语言 (执行效率快，但是移植性差)
1.  带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $
CALL pro_findById(4);
DROP PROCEDURE pro_findById; -- 删除存储过程。注意：后面不带括号

2.  带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN 
    SET str='helljava'; -- 给参数赋值
END $
CALL pro_testOut(@NAME);
SELECT @NAME;
3.  带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 输入输出参数
BEGIN
   -- 查看变量
   SELECT n;
   SET n =500;
END $
SET @n=10;
CALL pro_testInOut(@n);
SELECT @n;
4.  带有条件判断的存储过程
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str='星期一';
    ELSEIF num=2 THEN
        SET str='星期二';
    ELSEIF num=3 THEN
        SET str='星期三';
    ELSE
        SET str='输入错误';
    END IF;      --这儿的分号不能忘记
END $
CALL pro_testIf(4,@str);
SELECT @str;

5.  带有循环功能的存储过程
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
    -- 定义一个局部变量
    DECLARE i INT DEFAULT 1;
    DECLARE vsum INT DEFAULT 0;
    WHILE i<=num DO
          SET vsum = vsum+i;
          SET i=i+1;
    END WHILE;      --这儿的分号不能忘记
    SET result=vsum;
END $
CALL pro_testWhile(100,@result);
SELECT @result;
6.  使用查询的结果赋值给变量（INTO）
DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
    SELECT empName INTO vname FROM employee WHERE id=eid;
END $
CALL pro_findById2(1,@NAME);
SELECT @NAME;