MySQL常用SQL语句大全

###Mysql
#如何使用mySQL去创建数据库及数据库的使用
1，创建数据库
        create database 库名；
2，删除数据库
        drop database 库名；
#在我们创建好数据库之后就是对数据库的表的一些增删改查的操作
1，创建表
        create table 表名（
            字段一 类型(长度)
            字段二 类型（长度）
            ....
        创建表时创建主键一：在字段后面 如：uid int primary key (推荐，因为简单)
        创建表时创建主键二：在表创建的最后面 如：
                                            create table 表名（
                                                uid int(3),
                                                username varchar(20),
                                                userpass varchar(20),
                                                primary key(uid)
                                            
                                        
    #注意：在写id的时候不要直接写，要加前缀如：uid user_id                                         ）
    #删除主键
            alter table 表名 drop primary key
    #主键自动增长 一般主键是自动增长的，不需要指定   
            主键增长的语句 auto_increment(只使用于mysql) 
    #非空约定 not null
    #唯一约定 unique                            
    ）
2，刷新表
        select *from 表名；
##增
1，给表里面添加数据
        insert into 表名 （字段一，字段二，字段三） values （'值一','值二','值三'）；
        insert into 表名 values （'值一','值二','值三','值四'）；
2,添加新的字段
        alter table 表名 add 字段名称 数据类型（长度）约束；
##删
1，删除某一个字段
        alter table 表名 drop 字段名称
2，删除数据
        (1):删除指定条件的数据
            delete from 表名 where 条件
        (2):删除表内所有的数据
            delete from 表名
            truncate 表名 摧毁表再重新创建表，达到删除表里所有的数据
##改
1，修改字段的数据，长度或约定
        alter table 表名 modify 字段名称 数据类型（长度）约束

2，修改字段名称
        alter table 表名 change 旧字段 新字段 数据类型（长度）约定；
3，修改表的名称
        rename table 旧表名 to 新表名
4, 更改修改数据
        update 表名 set 字段=字段值 ，where 条件
        where 一般的格式 where 字段 = 值

##查

1,查看表中所有的字段
    select *from 表名；
2，查询指定的字段
    select 字段一 ， 字段二 from 表名；
3，查询时指定别名
    select 字段一 AS '别名一' ，字段二 as '别名二' from 表名；
    AS可以省略
    select 字段一 '别名一' ，字段二  '别名二' from 表名；
4，查询时添加常量
    #零时使用的列可以通过查询动态添加进去
    #列如： 在查询数据时要临时添加一个列“班级”，值为“java二班”
    select name  as '名字' ，id as '序号'，'java二班' as '班级' from 表名；
5，查询时合并
    #列入在查询学生数据总分是就要把所有的课程分数都有添加在一起
    select name as '名字'， （chinese + english + math） as '总分' from student;
    #注意在合并字段时必须是整数型的字段，若是非整数型的字段是没有效果的
    select name as '名字' (chinese + name) as '总分' from 表名；

6，查询时去掉重复记录（distinct）
    #在我们查询地址的时候就会出现一些重复的地区，这时就要去重
    格式一：select distinct 字段名 from 表名；
    格式二：select distinct （字段名） from 表名；

7，    条件查询
    #逻辑条件 and(且) or（或）
    #当出现两个或两个以上的条件时，就一定会出现逻辑关系，要么是与，要么是或
    需求：查询id为3，且名字为张三的学生
    select * from 表名 where id=3 and name ='张三'; （交集 :结果数小于或等于任意一个条件的结果数）
    需求：查询id为1 ，或名字为王五的学生
    select *from 表名 where id=1 or name= '王五'； （交集 ： 结果数大于或等于任意一个条件的结果）

8，比较条件 > < >= <= <>  (between and)在...之间
    #查询分数大于等于80
    select * from 表名 where score >= 80;
    #查询分数小于等于90    
    select * from 表名 where score <= 90;
    #查询分数大于或等于80分，且小于或等于85分的学生
    select * from 表名 where score >= 85 and score <= 95;
    #查询分数在80分和90分之间
    select * from 表名 where score between 80 and 90 ;
    #查询年龄不等于30 的学生
    select * from 表名 where age <> 30;

9, 模糊条件 like
    #模糊替代符号
        %：代替任意字符
        _:代替一个字符
    #需求：查询姓李的学生
    select * from 表名 where name like '李%';
    #需求：查询姓名中包含四的学生
    select * from 表名 where name like '%四%';
    #需求：查询姓李名字中一个字的学生
    select *from 表名 where name like '李_';

10，判空条件
    # is null ,is not null ,='',<>''
    null:表示没有数据 is null ,is not null
    空字符：有数据 =‘’
    #需求：查询没有性别的学生（或男或女）
    select *from 表名 where sex is null or sex ='';
    #需求：查询有性别的学生
    select *from 表名 where sex is not null and sex <>'';

11, 聚合函数查询:用于统计结果
    #max() min() avg() count()
    #max():取最大值
    select max(字段名) from 表名；
    #min():取最小值
    select min(字段名) from 表名；
    #avg():取平均值
    select avg(字段名) from 表名；
    #count()统计表的记录数量
    select count(*) from 表名；
    select count(id) from 表名；
    ##使用count统计表的时候，不要使用存在null的字段

12，查询后排序（order by）
    #desc :降序 数值从大到小排序 字母 z-a
    #asc :升序 数值从小到大排序 字母 a-a
     #默认情况下是按插入的顺序排序的
    select *from 表名；    
    --id按升序排序
    select * from 表名 order by id asc;
    --成绩按降序
    select *from 表名 order by  socer desc;
    -- 多个排序条件的情况：先按照前面的条件排序，当出现重复记录，再按照后面的条件排序
    -- 需求： 按照age升序，按照servlet成绩升序排序
    SELECT * FROM student ORDER BY age ASC,servlet ASC;

    -- select * from student order by username asc;

13,    分组查询(group by)
    #需求： 查询每个地区有多少人
    # 预期结果：
    --       广州天河  3
    --       广州越秀  1
    select address ,count(*) from 表名；
    -- 1)对地区进行分组   2)在分组的基础可以进行统计，统计的是每组的数据
    select address ，count(*) from 表名 group by address;    
    -- 需求： 统计男女的人数
    -- 注意： where条件必须放在group by分组之前 
    select address ,count(*) from 表名 where sex is not null and sex <>'' group by sex;
    3.12 分组后筛选(having)
    -- 需求： 查询哪些地区的人数大于2个的地区
    -- 1）查询哪些地区多少人  2）筛选人数大于2的地区
    -- 注意： having使用在group by分组之后的，对分组后的条件进行筛选
    select addess ,count(*) from 表名 group by addess having count(*) > 2;


##外键约束
    --外键的作用：约束两种表的数据
    --问题：约束那两种表的数据了？
    --答案：比如员工表！！！

    CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptName VARCHAR(20)
    )


    INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');
    INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');
    INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');
    INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');

    问题：当我在员工表里插入数据的时候就会因为一些重复的的数据（例如：deptname）使我们的表看起来重复（冗余），那么就会浪费数据库里的空间，
    --如何解决部门名称数据冗余的问题了？
    --这个时候就应该独立创建一个部门表，然后把部门名称房子部门表里。
    这个时候我们就可以根据两个表的id，把两个表链接起来。 因为这时存在了主副表的关系，我们这时就要在插入的数据的时候注意一些细节。
    --1）如果我们在主表里有了id的规格，那么我们在副表里就要遵从主表的id规格 例如：主表的id有 1 和 2 ，1：是研发部门 2：推广部门 。在我们添加员工表的数据的时候就只能添加1 或 2，不然的就会超出规则导致错误。
    ##那么是怎么来解决在出入员工部门的id的时候插入了不存在的id？问了解决这种非法的数据插入，我们就要只用外键约束了！！！
     
    -- 部门表
    CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
    ) 

    -- 添加外键约束（foreign key）
    -- 需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。
    -- 员工表（副表： 被别的表约束。外键设置在副表）
    CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        deptId INT,
        CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) 
        --          外键名称                     外键字段  参考    
        【constraint 外键名称 foreign key 外键字段（副表里与主表关联的字段） references 主表（主副表的关联字段）】       
        
    )

    ##-- 外键约束在什么情况下，会其作用？
    -- 插入数据：当往副表插入了主表不存在的数据时，外键起作用
    -- 修改数据：当往副表修改为主表不存在的数据时，外键起作用
    -- 删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用
    
    ##-- 当有了外键之后，应该如何管理数据呢？
    -- 插入数据： 先插入主表的数据，再插入副表数据
    -- 修改数据： 先修改主表数据，再修改副表数据
    -- 删除数据：先删除副表数据，再删除主表数据


##级联操作
    ##在我们设置了外键之后，我们在对主表和副表的时候就会对我们的操作设置了限制，这时我们设置级联操作，我们在对操作主副表的时候就不会有限制了
    -- 员工表（副表： 被别的表约束。外键设置在副表）
    DROP TABLE employee;
    CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        deptId INT,
    -- 添加级联修改： ON UPDATE CASCADE
    -- 添加级联删除： ON DELETE CASCADE 
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
    --          外键名称                     外键字段  参考          
    )

    -- 部门表（主表：约束别人的表）
    CREATE TABLE dept(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
)

    SELECT * FROM employee;
    SELECT * FROM dept;
    INSERT INTO employee(NAME,deptId) VALUES('陈六',2);
    INSERT INTO employee(NAME,deptId) VALUES('王五',2);

    -- 只修改部门
    UPDATE dept SET id=1 WHERE id=2;
    -- 只删除部门
    DELETE FROM dept WHERE id=1;


##交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）
    -- 需求： 员工及其部门 
    TRUNCATE TABLE dept;
    DELETE FROM dept;
    TRUNCATE TABLE employee;


    INSERT INTO dept(NAME) VALUES('软件开发部');
    INSERT INTO dept(NAME) VALUES('软件维护部');
    INSERT INTO dept(NAME) VALUES('秘书部');

    INSERT INTO employee(NAME,deptId) VALUES('张三',4);
    INSERT INTO employee(NAME,deptId) VALUES('李四',5);
    INSERT INTO employee(NAME,deptId) VALUES('王五',4);

    INSERT INTO employee(NAME,deptId) VALUES('陈六',5);

    -- 多表查询的步骤： 1）确定查询哪些表  2）确定查询哪些字段  3）确定连接条件(规则： 表数量-1)
    -- 内连接效果：只有满足连接条件的数据才会显示出来
    -- 1.2 内连接查询（使用最多）
    SELECT e.name,d.name
        FROM employee e,dept d
        WHERE e.deptId=d.id;

    -- 另一种语法(外部连接)
    SELECT e.name,d.name
        FROM employee e
        INNER JOIN dept d
        ON e.deptId=d.id;

## 左外连接查询（其次）
    -- 需求： 查询部门及其部门的员工（部门全部显示）
        -- 左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，
         -- 就显示满足条件的的数据，但是如果不满足连接条件，则显示null
    -- 预期结果：
        -- 软件开发部  张三
        -- 软件开发部  王五
        -- 软件维护部  李四
        -- 软件维护部  陈六
        -- 秘书部      null
    SELECT d.name,e.name
        FROM dept d
        LEFT OUTER JOIN employee e
        ON d.id=e.deptId;    

## 右外连接查询
        -- 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，
         -- 就显示满足条件的数据，但是如果不满足连接条件，则显示null
    SELECT d.name,e.name
        FROM employee e
        RIGHT OUTER JOIN dept d
        ON d.id=e.deptId;      
      
         
## 自连接查询
    -- 需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)
    -- 预期结果：
           员工    上司
        -- 张三   null
        -- 李四   张三
        -- 王五   李四
        -- 陈六   王五
    SELECT e.name AS '员工',b.name AS '上司'
        FROM employee e
        LEFT OUTER JOIN employee b
        ON e.bossId=b.id;