MySQL常用语句及权限问题详解

[TOC]

为清楚明了,本文中所有数据库名、表名、字段全部用中文了,实际操作时也能用,但最好还是尽量避免使用中文。当然,用英文的话,字段名就不用加 `` 符号了。

一、解决1227权限问题

# 创建用户(操作主机不限)
create user '用户名'@'%' IDENTIFIED by `密码`;

# 删除用户
drop user '用户名'@'%'

# 赋予 SYSTEM_USER 权限
GRANT SYSTEM_USER ON *.* TO 'root'@'%';

# 赋予所有权限
GRANT ALL privileges on *.* to 'root'@'%' with GRANT option;

# 撤销test用户所有权限
REVOKE ALL privileges ON *.* FROM 'test'@'%';

# 刷新权限
flush privileges;

二、数据库操作

1、登录数据库

mysql -u 用户名(默认root) -p
之后输入密码。

2、查看当前数据库

show databases;

3、创建数据库

create database 数据库名; 

# 数据库未存在,则输出
OK  
时间: 0.003s

# 如果数据库已经存在,则会提示已存在无法创建
1007 - Can’t create database ‘abcd;’; database exists  
时间: 0s

4、删除数据库

drop database 数据库名; 
OK  
时间: 0.008s

三、表操作

1、创建表

新建一个表,首先需要转到目标数据库:use 数据库名;

示例建表规则:
自增id非空字段作为联合主键,主键必须非空且默认以B树方式作为索引。
阿西吧作为另外一个表的外键。

# 创建前先删除确认没有重复的
DROP TABLE IF EXISTS `表名`;

CREATE TABLE `表名`  (
  `自增id` int NOT NULL AUTO_INCREMENT COMMENT '非空自增,准备做主键',
  `非空字段` varchar(15) NOT NULL COMMENT '非空,准备跟上面那哥们儿做联合主键',
  `阿西吧` int(10) COMMENT '这个用作外键',
  `唯一约束` varchar(15) UNIQUE DEFAULT NULL COMMENT '这个字段不能重复,但可为空',
  `有默认值的字段` varchar(15) DEFAULT '哈哈' COMMENT '默认为哈哈',
    
  PRIMARY KEY (`自增id`, `非空字段`),
  CONSTRAINT `阿西吧` FOREIGN KEY (`阿西吧`) REFERENCES `另一个表` (`对应同数据类型字段`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

这里外键有以下几种模式:

  • CASCADE:在父表上update/delete记录时,同步update/delete掉子表的匹配记录
  • SET NULL:在父表上update/delete记录时,将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
  • NO ACTION:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
  • RESTRICT:同no action, 都是立即检查外键约束
  • SET NULL:父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

2、查看表结构与建表SQL

# 查看表结构
describe `表名`;

# 查看建表SQL语句
show create table `表名`;

3、修改表

# 修改表名
alter table `表名` rename `新表名`;

# 添加新字段
## 添加的字段也可以用建表时的全部约束条件
## 默认在结尾添加
alter table `表名` add `新字段名` 数据类型;  
## 在表头添加
alter table `表名` add `新字段名` 数据类型 first;  
## 在表的指定列后添加
alter table `表名` add `新字段名` 数据类型 after `字段名`;  

# 修改表字段名
alter table `表名` change `旧字段名` `新字段名` 字段数据类型;

# 修改表字段数据类型
alter table `表名` modify `字段名` 新数据类型; 

# 修改字段排列顺序
alter table `表名` modify `字段名` 新数据类型 first; 
alter table `表名` modify `字段名` 新数据类型 after `字段名`; 

# 删除字段
alter table `表名` drop `字段名`;

4、删除表

drop table if exists `表名`;

四、查询(查)

业务逻辑最重要的四大块就是增删查改,对应数据库,最核心的也是这四大操作,所以这个模块是重中之重,前面建表那些navicat都可以图形化界面硬刚,但查询语句自己必须得会,将来要写在代码里的。

1、简单查询

# 查询所有字段
select * from `表名`;
# 查询指定字段
select `指定字段` from `表名`;
# 查询多个字段
select `字段1`,`字段2` from `表名`;

# 查询结果显示控制
## 显示前四行
select * from `表名` LIMIT 4;
## 显示第5-10行
select * from `表名` LIMIT 5,10;

2、单条件查询

# 查询符合条件的指定记录
select * from `表名` where 条件;

所有条件里的汉字和日期都要加英文的引号,条件情况举例:
(1)等于和大小匹配

## 数值大小关系型查询
select * from `表名` where `自增id`=5;(或者>,<,>=,<=)

## 普通字符匹配查询
select * from `表名` where `非空字段`='哈哈哈';

(2)IN 和 NOT IN

### 自增id等于1或5或7
select * from `表名` where `自增id` IN (1,5,7);

### 自增id不等于1或5或7
select * from `表名` where `自增id` NOT IN (1,5,7);


(3)BETWEEN

### 自增id在2-8之间
select * from `表名` where `自增id` BETWEEN 2 AND 8;


(4)LIKE

### 查询`非空字段`以w开头的
select * from `表名` where `非空字段` LIKE 'w%';

### 查询`非空字段`以e结尾的
select * from `表名` where `非空字段` LIKE '%e';

### 查询`非空字段`含有e的
select * from `表名` where `非空字段` LIKE '%e%';

3、多条件查询

# 条件1和条件2都得满足
select * from `表名` where 条件1 AND 条件2;

# 条件1和条件2满足其一即可
select * from `表名` where 条件1 OR 条件2;

4、高级查询

# 查询不重复数据
select distinct `字段名` from `表名`;

# 对查询结果进行排序
## 升序排序(默认)
select * from `表名` order by `字段名` asc;
## 降序排序
select * from `表名` order by `字段名` desc;
## 多列排序,先按照字段1排序再按照字段2排序
select * from `表名` order by `字段1`,`字段2`;

# 聚合查询
## 统计符合条件的记录数
select count(*) from `表名` where 条件;
## 统计某列中符合条件的非空记录数
select count(`字段名`) from `表名` where 条件;
## 求某列符合条件的平均值
select avg(`字段名`) from `表名` where 条件;
## 求某列符合条件的最大值
select max(`字段名`) from `表名` where 条件;
## 求某列符合条件的最小值
select min(`字段名`) from `表名` where 条件;
## 求某列符合条件的和
select sum(`字段名`) from `表名` where 条件;


# 分组查询GROUP BY
MySQL8.0需要增加以下配置才能用分组查询:
vim /etc/mysql/mysql.conf.d/mysqld.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

## 例:相同的`阿西吧`为一组,计算每组阿西吧的数量
select `阿西吧`,count(*) from `表名` GROUP BY `阿西吧`;

5、正则查询(高级条件查询)

select * from `表名` where `字段` REGEXP `正则表达式`;

五、插入(增)

插入时对于限定非空字段必须要赋值,字段和值必须一一对应。

# 插入单条数据
INSERT INTO `表名` (`字段1`,`字段2`,`字段3`) VALUES('值1','值2','值3')
# 插入多条数据
INSERT INTO `表名` (`字段1`,`字段2`,`字段3`) VALUES ('值1','值2','值3'),('值4','值5','值6')

六、更新(改)

UPDATE `表名` SET `字段1`='值1',`字段2`='值2' WHERE 条件;

当符合条件的记录有多个时则会批量修改。

七、删除(删)

# 删除所有符合条件的数据
DELETE FROM `表名` WHERE 条件;

# 删除表中所有的记录
DELETE FROM `表名`;

八、索引

MySQL索引可以帮助数据库快速找到需要的数据,从而提高查询的效率。
(其实就是建表时候应该干的活没干,建完表后才去补的。让你吹得这个神=。=)

1、查看索引

# 查看索引:这里显示了表中的索引信息,包括索引名称、数据类型、键值、排序顺序等信息
SHOW INDEX FROM `表名`

2、创建索引

# 创建唯一索引:等效于建表时的UNIQUE(如果表中已有数据这列不能有重复值)
ALTER TABLE `表名` ADD UNIQUE `索引名`(`字段名`);
# 删除唯一索引
DROP INDEX `索引名` ON `表名`;

# 创建主键索引:等效于建表时的PRIMARY KEY
# 以下是一个从自增主键到创建复合主键的例子:
## 原主键取消自增
ALTER TABLE `表名` MODIFY `原主键字段名` int NOT NULL;
## 删除原主键索引
ALTER TABLE `表名` DROP PRIMARY KEY;
## 创建复合主键索引
ALTER TABLE `表名` ADD PRIMARY KEY(`字段1`,`字段2`);

九、视图

1、取别名

可以通过AS来把比较长的表名和字段名缩短,便于创建视图时写连接语句。

# 表别名
select * from `表名` as u where u.`字段名`='值';
# 字段别名
select `字段名` as z from `表名` as u where u.z=888;

2、视图基础操作

视图就相当于建了一张新表,这个新表的数据来自于不同的表,通过一定条件把他们集合在一起,可以像对待普通表一样对视图进行增删查改,改动后的结果也会自动同步到各个组成表中。

# 创建单表视图
CREATE VIEW `视图名` AS 查询语句;

# 创建多表视图
CREATE VIEW `视图名`(`字段1`,`字段2`,`字段3`) AS SELECT `表1`.`字段1`,`表1`.`字段2`,`表2`.`字段3` FROM `表1`,`表2` WHERE `表1`.`字段1`=`表2`.`字段1`;

# 修改视图
写法1:CREATE OR REPLACE VIEW `视图名` AS 查询语句;
写法2:ALTER VIEW `视图名` AS 查询语句;

举个栗子,现在有两个班级,一个和尚班,一个尼姑班。法外狂徒张三恶向胆边生,就想把两个班id和年龄都相同的人直接凑一对儿,把事儿给办了。于是他就建了这样一个视图

CREATE VIEW `法外狂徒`(`男生姓名`,`女生姓名`,`年龄`) AS SELECT `一班`.`姓名`,`二班`.`姓名`,`一班`.`年龄` FROM `一班`,`二班` WHERE `一班`.id=`二班`.id AND `一班`.`年龄`=`二班`.`年龄`;

SELECT * FROM `法外狂徒`;

那么就能很轻易得得到受害人名单,恭喜5对儿新人。

如果想要更简洁的名单,去掉年龄,就可以对视图进行修改。

CREATE OR REPLACE VIEW `法外狂徒`(`男生姓名`,`女生姓名`) AS SELECT `一班`.`姓名`,`二班`.`姓名` FROM `一班`,`二班` WHERE `一班`.id=`二班`.id AND `一班`.`年龄`=`二班`.`年龄`;
# 或者以下写法
ALTER VIEW `法外狂徒`(`男生姓名`,`女生姓名`) AS SELECT `一班`.`姓名`,`二班`.`姓名` FROM `一班`,`二班` WHERE `一班`.id=`二班`.id AND `一班`.`年龄`=`二班`.`年龄`;

SELECT * FROM `法外狂徒`;


这里如果不修改视图列数的话,视图名后面的字段可以省略不写。

还有其他的增删查改基础操作跟普通的数据表是完全一样的,照着来就行了。

3、高级视图操作之表连接

通过表连接可以把具有共同列的不同表整合到一个视图之中,创建语句上没有任何区别,所以这里就只写一下不同连接方式对应的查询语句。
在使用表连接时,需要确保表之间的数据类型和字段名称相同,否则可能会出现数据类型或字段名称不匹配的错误。

# 内连接INNER JOIN:两个表的交集部分,如上面的张三例子
select * from `左表` inner join `右表` on `左表`.`字段1` = `右表`.`字段2`;

# 左连接LEFT JOIN:显示左表的全部数据,右表只列出ON后条件与左表满足的部分
select * from `左表` left join `右表` on `左表`.`字段1` = `右表`.`字段2`;

# 右连接RIGHT JOIN:显示右表的全部数据,左表只列出ON后条件与右表满足的部分
select * from `左表` right join `右表` on `左表`.`字段1` = `右表`.`字段2`;

# 全连接FULL JOIN:显示两侧表中所有满足检索条件的行,无对应字段则置为NULL
# mysql中没有FULL JOIN,可以使用`union`实现全连接;
select * from `左表` left join `右表` on `左表`.`字段1` = `右表`.`字段2` 
union 
select * from `左表` right join `右表` on `左表`.`字段1` = `右表`.`字段2`;

4、删除视图

DROP VIEW IF EXISTS `视图名`;
本文为林子蔚的博客(https://linjoey.cn)原创文章,如需转载,请注明出处。
无标签