MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
在本教程中,会让大家快速掌握 MySQL 的基本知识,并轻松使用 MySQL 数据库。
目录
用户基本操作
连接数据库
mysql -h 主机地址 -u 用户 -P 端口 -p账号密码
连接本地主机数据库
mysql -h localhost -u root -P 3306 -p密码
注:
<1>. 密码前必须没有空格,否则会让你重新输入密码
<2>. 如果刚安装好Mysql,root用户是没有密码的,故直接回车进入mysql中。
Mysql提示符为: mysql>
退出mysql命令
exit
创建用户账户
CREATE USER username@hostname IDENTIFIED BY password;
查看用户账户权限, 使用 SHOW GRANTS语句
SHOW GRANTS FOR username@hostname;
如果要向用户授权,则使用GRANT语句
GRANT privilege,[privilege],... ON privilege_level TO user [IDENTIFIEN BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];
privilege_level是特权应用级别。
权限 | 代码 |
全局级别 | *.* |
数据库级别 | database.* |
表级别 | database.table |
列级别 | 如果使用列权限级别,则必须在每个权限之后使用逗号分隔列的列表。 |
然后跟随着授权用户,如果用户以及存在,则会修改特权,如果不存在,GRNT语句则会创建一个新用户。IDENTIFIEN BY 为可选条件,允许用户设置密码。
REQUIRE 可指定用户是否必须通过安全连接(如SSL
,X059
等)连接到数据库服务器。
可选的WITH GRANT OPTION
子句允许此用户授予其他用户或从其他用户删除您拥有的权限。此外,可以使用WITH
子句来分配MySQL数据库服务器的资源,例如,设置用户每小时可以使用多少个连接或语句。这在MySQL共享托管等共享环境中非常有用。
请注意,要使用GRANT
语句,您必须具有GRANT OPTION权限和您授予其它用户的权限。 如果启用了read_only系统变量,则需要具有SUPER权限才能执行GRANT
语句授权
权限 | 含义 | 全局 | 数据库 | 表 | 列 | 过程 | 代理 |
ALL[PRIVILEGES] | 授予除了 GRANT OPTION 之外的指定访问级别的所有权限 | ||||||
ALTER | 允许用户使用ALTER TABLE 语句 | × | × | × | |||
ALTER ROUTINE | 允许用户更改或删除存储程序 | × | × | × | |||
CREATE | 允许用户创建数据库和表 | × | × | × | |||
CREATE ROUTINE | × | × | |||||
CREATE TEMPORARY TABLE | 允许用户使用CREATE TEMPORARY TABLE 创建临时表 | × | × | ||||
CREATE TABLESPACE | 允许用户创建,更改或删除表空间和日志文件组 | × | |||||
CREATE USER | 允许用户使用 CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES 语句 | × | |||||
CREATE VIEW | 允许用户创建或者修改视图 | × | × | × | |||
DELETE | 允许用户使用DELETE | × | × | × | |||
DROP | 允许用户删除数据库,库和视图 | × | × | × | |||
EVENT | 能够使用世间计划的事件 | × | × | ||||
EXECUTE | 允许用户执行存储过程/存储函数 | × | × | ||||
FILE | 允许用户读取数据库目录中的任何文件 | × | |||||
GRANT OPTION | 允许用户有权授予或撤销其他账户的权限 | × | × | × | × | × | |
INDEX | 允许用户创建或者删除索引 | × | × | × | |||
INSERT | 允许用户使用INSERT语句 | × | × | × | × | ||
LOOK TABLES | 允许用户在具有SELECT 权限的表上使用 LOCK TABLES | × | × | ||||
PROCESS | 允许用户使用SHOW PROCESSLIST 语句查看所有进程 | × | |||||
PROXY | 启用用户代理 | ||||||
PEFERENCES | 允许用户创建外键 | × | × | × | × | ||
RELOAD | 允许用户使用FLUSH操作 | × | |||||
REPLICATION CLIENT | 允许用户查询主服务器或从服务器的位置 | × | |||||
REPLICATION SLAVE | 允许用户使用复制从站从主机读取二进制日志事件 | × | |||||
SELECT | 允许用户使用SELECT语句 | × | × | × | × | ||
SHOW DATABASES | 允许用户现世所有数据库 | × | |||||
SHOW VIEW | 允许用户使用 SHOW CREATE VIEW 语句 | × | × | × | |||
SHUTDOWN | 允许用户使用 mysqladmin shutdown命令 | × | |||||
SUPER | 允许用户使用其他管理操作,如 | × | |||||
TRIGGER | 允许用户使用TRIGGER操作 | × | × | × | |||
UPDATE | 允许用户使用UPDATE语句 | × | × | × | × | ||
USAGE | 相当于无权限 |
修改用户密码
mysqladmin -u 用户名 -p 旧密码 password 新密码
给root加密码
mysqladmin -u root -password 密码
注:因为开始root没有密码,所以 -p 旧密码一项省略
表的基本操作
建表
CREATE TABLE IF NOT EXISTS 表名(特征1 数据类型 约束条件,特征2 数据类型 约束条件,.....特征n 数据类型 约束条件);
CREATE TABLE IF NOT EXISTS 表名 as select * from 数据来源表;
插入
INSERT INTO 表名(特征1,特征2,....,特征n) VALUES (特征1的数值,特征2的数值,.....,特征n的数值);
INSERT INTO 表名(特征1,特征2,....,特征n) SELECT 特征值1,特征值2,....,特征值n from 数据来源表;
更新
update 表名 set 特征名 = 新的内容,特征2名 = 新内容,.....,特征n = 新内容 where 条件;
添加新列
ALTER TABLE 表名 ADD (新特征1 数据类型 约束,新特征2 数据类型 约束,...,新特征n 数据类型 约束);
删除数据
DELETE FROM 表名 where 条件;
删除表
DROP TABLE 表名;
基础功能实现
查询
SELECT 查询列名 FROM 表名 WHERE 条件 GROUP BY 分组条件列 ORDER BY DESC;
聚合
sum求和/MAX最大值/MIN最小值/AVG平均值/count计数
SELECT *,SUM(特征1),MAX(特征1),MIN(特征1),AVG(特征1),COUNT(特征1) from 表名
WHERE 条件
GROUP BY 分组
ORDER BY DESC;
分组
row_number() over(partition by)
select *,ROW_NUMBER() OVER(PARTITION BY 列 ORDER BY 列2 desc)as t from 表;
链接查询
内连接查询
SELECT * FROM 表1 INNER JOIN 表2 ON 表一.特征 =表二.特征 WHERE 条件;
外连接查询
SELECT * FROM 表一 LEFT JOIN 表二 ON 表一.特征=表二.特征 WHERE 条件;
SELECT * FROM 表一 RIGHT JOIN 表二 ON 表一.特征=表二.特征 WHERE 条件;
条件语句
case语句
select
case when 条件1 then 输出1 end,
case when 条件2 then 输出2 end,
....
case when 条件n then 输出n end
from 表;
if语句
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
select
IF(expr1,expr2,expr3)
from 表;
字符串操作
截取
substring_index
SELECT SUBSTRING_INDEX(字符串,分割符,选取第几个分隔符前的字符串) FROM 表;
substring
select SUBSTRING(string,起始位置,count) from 表;
regexp_substr
select regexp_substr(目标列,'正则表达式') from 表;
left 左侧截取指定字符串
LEFT ( input_string , number_of_characters )
right 右侧截取指定字符串
right ( input_string , number_of_characters )
合并
concat
SELECT CONCAT('string1','string2') FROM 表;
group_concat
对组合并成一行
SELECT 列1 GOUP_CONCAT(列2)FROM 表 group by 列1;
替换
REPLACE
SELECT REPLACE(列,'分隔符','替换字符') FROM 表;
字符串检索
REGEX
SELECT * FROM 表 WHERE 列名 REGEX('正则表达式')
LIKE
LIKE 关键字主要用于搜索匹配字段中的指定内容 有%和_两种模式 %为模糊匹配。_为精准匹配
SELECT * FROM 表 WHERE 列名 LIKE '%哈喽';
匹配以'哈喽'结尾的字符串
SELECT * FROM 表 WHERE 列 LIKE '哈喽%';
匹配以'哈喽'开头的字符串
SELECT * FROM 表 WHERE 列 LIKE '_哈喽';
匹配的'哈喽'字符串前只能有一个字符
SELECT * FROM 表 WHERE 列 LIKE '哈喽_';
匹配的'哈喽'字符串后只能有一个字符
长度检索
LENGTH和CHAR_LENGTH
LENGTH返回字符串中数字和字母一个字符长度,utf-8的汉字则是三字符长度
CHAR_LENGTH不管汉字还是数字或者是字母都算是一个字符
所以返回值length为14 char_length为9
SELECT LENGTH('大家好.我是xxx') FROM 表;
SELECT CHAR_LENGHT('大家好.我是xxx') FROM 表;