MySQL应用

用户管理

1.以某用户登入某个资料库：

mysql -u username -ppassword db_name

-p与密码之间没有空格

如果没有密码，-p可省略

db_name为可选项

2.创建用户：

mysql -u root -ppassword mysql

create user username@hostname identified by 'password';

set password = password("psw");

set password for username@hostname = password("");

3.删除用户

mysql -u root -ppassword mysql

drop user username@hostname;

如果从mysql.user表中直接删除用户，会导致重新创建同一用户时报错。

4.授权

grant privilegesCode on dbName.tableName to username@host identified by'password' with grant option;

flush privileges;

privilegesCode表示授予的权限类型，常用的有以下几种类型：

• all privileges：所有权限。
• select：读取权限。
• delete：删除权限。
• update：更新权限。
• create：创建权限。
• drop：删除数据库、数据表权限。

dbName.tableName表示授予权限的具体库或表，常用的有以下几种选项：

• *.*：全局权限，授予该数据库服务器所有数据库的权限。
• dbName.*：数据库权限，授予dbName数据库所有表的权限。
• dbName.dbTable：表格权限，授予数据库dbName中dbTable表的权限。

username@host表示授予的用户以及允许该用户登录的IP地址。其中Host有以下几种类型：

• localhost：只允许该用户在本地登录，不能远程登录。
• %：允许任何一台机器，本地和远程。
• 172.168.32.12：具体的IP表示只允许该用户从特定IP登录。

with grant option表示用户可以将自己拥有的权限授权给别人。

flush privileges表示刷新权限变更。

5.撤销授权

revoke privilegesCode on dbName.tableName from username@hostname identified by 'password';

flush privileges;

6.查看权限

show grants for username@hostname;

7.授权系统

MySQL的授权系统通常是通过MySQL数据库中的四个表来实现的，这些表有user、db、tables_priv和columns_priv。

• user--该表决定是否允许用户连接到服务器。如果允许连接，权限字段则为该用户的全局权限。
• db--用于决定哪些用户可以从哪些主机访问哪些数据库。包含在db表中的权限适用于这个表标识的数据库。
• tables_priv--该表与db表相似，不同之处是它用于表而不是数据库。这个表还包含一个其他字段类型，包括timestamp和grantor两个字段，用于存储时间戳和授权方。
• columns_priv--该表作用几乎与db和tables_priv表一样，不同之处是它提供的是针对某些表的特定列的权限。这个表也多出了一个字段类型，即其他字段，包括了一个timestamp列，用于存放时间戳。

碰到的问题：

问题描述：新建用户，查看只有grant usage权限，但可以增删改查。

问题原因：以前没有彻底删除用户，user和db两个表中数据的不一致，用户在db中还能map到权限。

解决办法：清除db表中相关行的数据。

8. 查看用户

在Mysql中其实有一个内置且名为mysql的数据库，这个数据库中存储的是Mysql的一些数据

select User, Host, Password from mysql.user;

 

 

 

命令管理

1.执行文件中的mysql语句

方法1：进入mysql之前，命令行里执行

mysql [db_name] < /文件路径/文件名

如果文件路径中有空格，记得加上，譬如/google driver/

方法2：已经进入mysql，执行

source /文件路径/文件名

或者

. /文件路径/文件名

注意：文件路径无引号

2. 备份和还原数据库

进入mysql之前，命令行中执行。

备份： mysqldump -uroot -p db_name > filename.sql

还原，前提db_name已经被创建： mysql -uroot -p db_name < filename.sql

 

 

数据管理

1.数据库操作

create database db_name;

drop database db_name;

show databases;

use db_name;

2.表格操作

show tables;

show columns from tb_name;

create table tb_name (column_name column_type, column_name column_type,);

例子：create table Took (sID integer, oID integer, grade smallint, primary key (sID, oID), foreign key (sID) references Student(sID), foreign key (oID) references Offering(oID)) DEFAULT CHARSET=utf8;  创建表格，声明key和外部key，声明编码格式。

drop table tb_name;

insert into tb_name (field1, field2, ...) values (value1, value2, ..);

insert into tb_name (subquery);

例子：create table invite (name char, campus varchar(25) default 'StG', email text, age int);

insert into invite(name, email) (select firstname, email from student where cgpa >3.4);

delete from tb_name where clause;

delete from tb_name;全部删除

例子：delete from invite where name='shen';

update tb_name set attributes_assignments where clause;

update tb_name set attributes_assignments;更新所有行

例子：updata invite set campus='ut';

select column_name,column_name from tb_name where clause [limit n] [offset m]

例子：select * from student;选择student里面的全部数据

例子：select * from student s1, student s2;重命名student，并取self-joins。

例子：select name as title from course where clause; 重命名name为title。

例子：select * from offering where dept='csc' and cnum>=300;选取3rd和4th-year的课程

例子：select * from student order by sID [asc | desc];默认以学号升序排列

例子：select grade-10 from took; 数学操作符

例子：select concat(dept, cnum) as course from course; 合并两列并重命名

例子：select dept,cnum,'satisfied' as breadthrequirement from course where breadth；新增breadthrequirement，赋值satisfied

例子：select * from course where name like '%comp%'; pattern匹配，%任意字符，_任意单个字符；

 

3.聚合函数(aggregation function)和分组(group by) 

聚合函数：sum, avg, count, max, min 

count(*): 所有行数

count(属性)：某列非空行数

count(distinct 属性)：某列非空不重复行数

分组：group by 属性 having 需要满足的条件

如果使用分组group by，select、having后的属性要么是聚合函数，要么是group by后的属性，包含其他属性没有意义。

换句话说：分组后，select、having的属性要么是分组依据的属性(列名)，要么就是其他某一列通过聚合函数聚合后得到的属性

例子：select count(*) from student where campus='stg'; 返回campus='stg'的所有行数，select属性为聚合函数

例子：select count(sid) from student where campus='stg'; 返回campus='stg'的所有sid非空行数，select属性为聚合函数

例子：select campus, count(*) from student group by campus; 按campus分组，并返回campus和每组campus对应的行数

例子：select sID, count(*) from student group by campus; 没有意义。

例子：select campus, count(*) from student group by campus having count(*)>1; 按campus分组，并返回满足count(*)>1条件的campus和每组campus对应的行数

例子：

4. SQL查询顺序

语言请求顺序：select, from, where, group by, having, order by

实际执行顺序：from, where, group by, having, select, order by

选择表格，筛选行，分组，分组后筛选，选取列，排序

5. set还是bag操作  

select from where：默认结果为bag，不删除重复项；

select distinct from where：结果为set，删除重复项；

union：删除重复项；

union all：保留重复项；

6. 视图view

视图简单、安全、数据独立

创建视图：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select from where
[WITH [CASCADED | LOCAL] CHECK OPTION]

1）OR REPLACE：表示替换已有视图

2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表

3）select_statement：表示select语句

4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

　　cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件

　　local表示更新视图的时候，要满足该视图定义的一个条件即可

TIPS：推荐使用WHIT [CASCADED | LOCAL] CHECK OPTION选项，可以保证数据的安全性

例子：CREATE OR REPLACE VIEW topresults (名字,姓氏,课程代码) AS SELECT firstname, surname, cnum FROM Student, Took, Offering WHERE Student.sid = Took.sid AND Took.oid = Offering.oid AND grade >= 80 AND dept = 'CSC'; 创建或更新视图

查看视图 

DESC view_name：查看view列属性

SHOW CREATE VIEW view_name：查看create view指令

SELECT * FROM view_name：查看view中内容

修改视图

CREATE OR REPLACE：更新视图

ALTER：修改视图，保持视图与基表的一致性

DML操作：UPDATE view_name set name='王五' where 学号='1';

删除视图：

DROP VIEW [IF EXISTS] view_name;

7. NULL

A [LEFT | RIGHT | RIGHT] JOIN B 引入NULL值

如果聚合列A每一项的值均为NULL，那么该列聚合函数的结果为NULL，但是count(A)为0

否则，NULL值对聚合函数sum, avg 和 count 无影响，也不会成为max和min中的结果

8.子查询(Subquery)

在FROM中的子查询：必须用()和重命名

SELECT sid, concat(dept,cnum) as course, grade FROM Took, (SELECT *FROM Offering WHERE instructor=‘Horton’) Hoffering WHERE Took.oid = Hoffering.oid;

在WHERE中的子查询：必须用()，并用any，all，in，exists解决查询结果为多项的问题： 

SELECT sid, surname FROM Student WHERE cgpa > ALL (SELECT cgpa FROM Student WHERE campus = ‘StG’);

SELECT surname, cgpa FROM Student WHERE EXISTS ( SELECT * FROM Took WHERE Student.sid = Took.sid and grade > 85);结果同下例

SELECT surname, cgpa FROM Student WHERE (sid, cgpa) IN ( SELECT sid, cgpa FROM Took WHERE Student.sid = Took.sid and grade > 85);结果同上例

 

 

大小写敏感问题：

mysql数据库、表、triggers实际上都对应了datadir目录（或子目录）下的文件，因此，这些对象的名字是否大小写敏感主要是依赖于操作系统和文件系统的，Wins下大小写不敏感，Unix下大小写敏感（Mac OS X除外）；可以通过修改参数改变大小写敏感。

 

编码问题

四个层级的编码，服务器、数据库、表、字段

每个层级的默认编码只能管理下一层级新建而没有定义编码的情况

修改当前层级的编码并不能改变下层已存在对象的编码

查看数据库编码

show variables like '%char%';

查看表的编码

show create table <表名>;

例子： show create table hero;

查看字段编码

show full columns from <表名>;

例子： show full columns from hero;

修改数据库编码

alter database <数据库名> character set utf8;

例子：alter database how2java character set utf8;

修改表编码，修改表的编码不能改变字段的编码

alter table <表名> character set utf8;

例子：alter table item character set utf8;

修改字段编码

alter table <表名> change <字段名> <字段名> <字段类型> character set utf8 [COLLATE utf8_unicode_ci];

例子：alter table item change name name varchar(30) character set utf8 [COLLATE utf8_unicode_ci] 

alter table <表名> modify <字段名> <字段类型> character set utf8 [COLLATE utf8_unicode_ci];

例子：alter table item modify name varchar(30) character set utf8 [COLLATE utf8_unicode_ci]

修改所有字段的编码

alter table <表名> convert to character set utf8;

注释： 

[]：括弧里为可选项

引用资料：

参考mysql说明文档：

https://dev.mysql.com/doc/refman/8.0/en/adding-users.html

用户权限说明：

https://www.cnblogs.com/caibird2005/p/4512905.html

深入解析MySQL视图VIEW

https://www.cnblogs.com/geaozhang/p/6792369.html#chuangjianshitu

编码参考：

https://blog.csdn.net/springsunss/article/details/70337915

https://cloud.tencent.com/developer/article/1424845