2021-06-07

、用户管理
1、新建用户

mysql> create user name identified by ‘root’;
1
2、更改密码

mysql> set password for name=password(‘ok’);
1
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;
1
2、创建数据库

mysql> create database db_name; //创建名称为db_name的数据库
1
3、删除数据库

drop database db_name; //删除名称为db_name的数据库
1
4、使用数据库

use db_name; //对数据库操作前必须使用
1
三、对表操作
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;
1
3、查看表的结构

describe student; # 可以简写为desc student;
1
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
五、对数据操作
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的数据
1
3、修改数据
update 表名 set 修改内容 where 条件；

update student set age = 20 where id = 2; #修改id为2的学生年龄为20
1
4、查找数据（方法较多）
select 内容 from 表名 （可附属条件）；

select name,age ,id from student; #查询数据表student中的所有名字与年龄
1
六、查询条件关键词用法
1、where用法，where后加条件
注：条件符可用 ‘> ‘, ’ =’ , ’ <’ , ’ >=’, <= , != ;

select * from student where age>40; #查询age大于40的学生信息
1
2、and用法，一般在where后，连接多条件“ 且 ”的关系！两个条件都得满足

select * from student where age>40 and age<60; #小于60且大于40的
1
3、or用法，一般在where后，连接多条件“ 或 ”的关系！A or B满足A或者B都显示出来

select * from c where age<40 or age>60; #大于60和小于40的都显示
1
4、between…and…用法，在两者之间

select * from c where age between 40 and 60; 年龄在40到60之间的
1
5、in用法，在某几个值内的

select * from c where age in (30,48,68,99); #in 查询指定集合内的数据
1
6、order by排序用法，asc升序（从小到大） des降序（从大到小）

select * from c order by age desc; #将年龄降序排列显示
1
注：多字段排序 使用，隔开 如下SQL

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

SELECT * FROM students st ORDER BY st.sAge DESC, st.sGrade DESC;
1
（2） 先后顺序
MySql中，order by 多个字段时，按字段先后顺序排优先级。
如：

SELECT * FROM students st ORDER BY st.sAge ASC, st.sStuId DESC, st.sGrade DESC;
1
结果：

总结

（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;

在这里插入代码片
1
七、多表关联查询
https://blog.csdn.net/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

mybatis写法：

insert into USER( SERIAL_NUMBER) select table_tmp.* from( SELECT #{item.SerialNumber,jdbcType=CHAR} FROM DUAL ) 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;
————————————————
版权声明：本文为CSDN博主「微笑smiled」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_43896747/article/details/86484200