MySQL语句详情整理大全【全面总结】

前言：

一、用户管理

1、新建用户

mysql> create user name identified by 'root';

2、更改密码

mysql> set password for name=password('ok');

3、设置用户权限

MySQL> show grants for name; //查看name用户的权限

mysql> grant select on db_name.* to name; //给用户name的db_name数据库所有权限

mysql> revoke select on db_name.*to name; //与grant操作相反，去掉权限

二、对数据库操作

1、查看所有的数据库

mysql> show databases;

2、创建数据库

mysql> create database db_name;  //创建名称为db_name的数据库

3、删除数据库

> drop database db_name;  //删除名称为db_name的数据库

4、使用数据库

>use db_name; //对数据库操作前必须使用

三、对表操作

1、创建表
列的格式：列名+数据类型+属性；

create table student(
id int auto_increment primary key, //设置主键
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100) 
)default charset=utf8;  //编码

2、显示数据库中的所有表

show tables;

3、查看表的结构

describe student;   # 可以简写为desc student;

4、对表名重命名

rename table student to stubents;
或者使用：
alter table stubent rename students;

5、设置主键与外键：

• 主键：

方法1：primary key:
create table student(
	sno char(4) primary key,   #设置主键（单字段主键）
	sname char(10),
	sage int,
	ssex char(10)
);

方法2：primary key (列名) 
create table course(
	cno char(4),
	cname char(8),
	ccredit int,  
	primary key (cno)   #设置主键的第二种方法
);

• 外键：
  foreign key(列名) references 父类表(列名)

create table Score (
	Sno varchar(20) not null,
	Cno varchar(20) not null,
	Degree Decimal(4,1),
	foreign key(Sno) references student(Sno),  #外键
	foreign key(Cno) references Course(Cno),
	primary key(Son,Cno)   #设置sno和cno的属性组为主键（即多字段主键，或联合主键）
);

• 自主增删主外键

增删主键：

1、alter table 表名 modify 主键列名 新列类型（不含auto_increment);   #删除自增，才能删除主键

2、alter table 表名 drop primary key;    #删除主键

3、alter table 表名 add primary key(列名);   #添加主键

增删外键：

1、alter table 子表名 add [constraint fk_sno] foreign key(子表的外键名称) references 父表名（父表的主键名称）;

--添加外键约束。fk_sno为外键ID名，若不添加，系统会自动配一个。

2、alter table 子表名 drop foreign key fk_sno;

--删除外键约束。fk_sno为外键ID名, 若不知，可查询建表明细（show create table 表名）。

6、复制表

create table user2 select * from user; #复制表user命名为user2

五、对数据操作

1、插入数据
insert into 表名 values(属性);

insert into student values(null,'aa','男','1988-10-2','......');  #给定所有属性数据

insert into student(name, sex, date) values('bb','女','1889-03-6');  #给定部分属性数据

insert into student values (null,'王老师'), (null, '李老师'); #同时插入多条记录

2、删除数据

delete from student where id=5; #删除id为5的数据

3、修改数据
update 表名 set 修改内容 where 条件；

update student set age = 20 where id = 2; #修改id为2的学生年龄为20

4、查找数据（方法较多）
select 内容 from 表名 （可附属条件）；

select name,age ,id from student;  #查询数据表student中的所有名字与年龄

六、查询条件关键词用法

1、where用法，where后加条件
注：条件符可用 ‘> ‘, ’ =’ , ’ <’ , ’ >=’, <= , != ;

select * from student where age>40; #查询age大于40的学生信息

2、and用法，一般在where后，连接多条件“ 且 ”的关系！两个条件都得满足

select * from student where age>40 and age<60; #小于60且大于40的

3、or用法，一般在where后，连接多条件“ 或 ”的关系！A or B满足A或者B都显示出来

select * from c where age<40 or age>60; #大于60和小于40的都显示

4、between…and…用法，在两者之间

select * from c where age between 40 and 60; 年龄在40到60之间的

5、in用法，在某几个值内的

select * from c where age in (30,48,68,99); #in 查询指定集合内的数据

6、order by排序用法，asc升序（从小到大） des降序（从大到小）

select * from c order by age desc; #将年龄降序排列显示

注：多字段排序 使用，隔开 如下SQL

SELECT * FROM students st ORDER BY st.sAge, st.sGrade DESC;

（1）排序情况
预期结果：sAge和sGrade两个字段都按降序排列，但事实并非如此
实际结果：sAge按升序排序，sGrade按降序排列
MySql中，order by 默认是按升序排列的 所以需改造SQL：

 SELECT * FROM students st ORDER BY st.sAge DESC, st.sGrade DESC;

（2） 先后顺序
MySql中，order by 多个字段时，按字段先后顺序排优先级。
如：

SELECT * FROM students st ORDER BY st.sAge ASC, st.sStuId DESC, st.sGrade DESC;

结果：

总结

（1）在MySql中，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。

（2）尤其非常特别重要：默认按升序（ASC）排列。

（3）order by 后可加2个（或多个）字段，字段之间用英文逗号隔开。

（4）若A用升序，B用降序，SQL该这样写：order by A ASC, B DESC; 默认同理，也可以这样写：order by A, B DESC;

（5）若A、B都用降序，必须用两个DESC，order by A DESC, B DESC;

（6）多个字段时，优先级按先后顺序而定。

7、其他：
not用法，取反；as 另命名；group by 分组查询；

select name,max(age) from c group by sex;  #按性别分组进行，查年龄最大值

select * from c where not age between 40 and 60; #不再40到60之间的

select name as '名字',max(age)  as '年龄' from c group by sex;  #按性别分组进行，查年龄最大值

like 字符串匹配；is null 要求指定值等于null;

在这里插入代码片

七、多表关联查询

https://blog.youkuaiyun.com/plg17/article/details/78758593

INNER JOIN（内联）：两个表a,b 相连接，取出符合连接条件的字段

LEFT JOIN（左联）：先返回左表的所有行，再加上符合连接条件的匹配行

RIGHT JOIN（右联）：先返回右表的所有行，再加上符合连接条件的匹配行

1、内连接——最常用
定义：仅将两个表中满足连接条件的行组合起来作为结果集。

在内连接中，只有在两个表中匹配的行才能在结果集中出现 ：上图中间部分两个都有的数据（第二部分）

关键词：INNER JOIN

格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式

说明：
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名

（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名

（3）INNER是默认方式，可以省略

2、左连接
定义：在内连接的基础上，还包含左表中所有符合条件的数据行，并在其中的右表列填写NULL，（上图一和二部分）

关键字：LEFT JOIN

注意:

当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况

就不同了。

当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。

3、右连接
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL，（上图二和三部分）

关键字：RIGHT JOIN

八、批量处理

1、批量插入

insert into USER
  (SERIAL_NUMBER)
  select table_tmp.*
    from ((SELECT ?
             FROM DUAL
           union all
           SELECT ?
             FROM DUAL
           union all
           SELECT ?
             FROM DUAL
             .
             .
             .
             )) table_tmp

mybatis写法：

  <insert id="insertUserBatch">
    insert into USER(
    SERIAL_NUMBER)
    select table_tmp.* from(
    <foreach close=")" collection="userList" index="index" item="item" open="(" separator="union all">
      SELECT
      #{item.SerialNumber,jdbcType=CHAR}
      FROM DUAL
    </foreach> ) table_tmp

九、CET的使用（with…as…用法）

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

 with cte as(
    select 1 from dual 
  
     union all

     select 2 from dual
 )
 select * from cte;

未更完！待续。。。。