mysql常用语句整理

最新推荐文章于 2022-07-14 17:31:26 发布

打不死的小强lee

最新推荐文章于 2022-07-14 17:31:26 发布

阅读量162

点赞数

分类专栏： mysql 文章标签： msql

本文链接：https://blog.csdn.net/wenqiangluyao/article/details/92797260

版权

mysql 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

一、DDL(数据定义语言)

DDL定义数据库库、表、表结构、表字段等。

1、库

（1）查看数据库

show databases;

（2）切换数据库

use <dbname>;

（3）创建数据库

create database <dbname>;

或

create database if not exists <dbname> CHARACTER SET utf8mb4;

（4）删除数据库

drop database <dbname>;

或

drop database if exists <dbname>;

（5）修改数据库字符集

alter database <dbname> CHARACTER SET utf8mb4;

2、表

（1）创建表

create table <tablename> (<column1Name> <column1Type>, <column2Name column2Type>, …);

建表时，指定默认值（DEFAULT），注释（COMMENT），非空约束（NOT NULL），主键（PRIMARY KEY），唯一约束（UNIQUE KEY），索引（INDEX），数据库引擎（ENGINE），表字符集（CHARSET）等，如：

CREATE TABLE `device` (
  `id` char(32) NOT NULL COMMENT '设备id',
  `title` varchar(30) NOT NULL COMMENT '设备名称',
  `dev_type` varchar(50) NOT NULL COMMENT '型号',
  `mac` varchar(40) DEFAULT NULL COMMENT 'mac物理地址',
  `status` tinyint(2) DEFAULT NULL COMMENT '设备状态',
  `user_id` char(32) DEFAULT NULL COMMENT '创建者',
  `created` timestamp NULL DEFAULT NULL,
  `updated` timestamp NULL DEFAULT NULL,
  `deviceimg` varchar(255) DEFAULT NULL COMMENT '图片',
  `template_id` char(32) DEFAULT NULL COMMENT '模板id',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  `parent_id` CHAR(32) COMMENT '所属父设备Id',
  PRIMARY KEY `pk_id` (`id`),
  UNIQUE KEY `uk_mac` (`mac`),
  KEY `idx_template_id` (`template_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备表';

注：有关索引具体内容可以查看我的博客：Mysql索引简介，设计，创建与查看

（2）查看表

show tables;

（3）查看表结构

desc <tablename>;

（4）删除表

drop table <tablename>;

（5）修改表

1）添加列

alter table <tablename> add(<columnName> <columnType>);

2）修改列数据类型

alter table <tablename> modify <columeName> <newColumnType> default <defaultValue>;

3）修改列名及列类型

alter table <tablename> change <oldColumnName> <newColumnName> <newColumnType>;

4）删除列

alter table <tablename> drop <columnName>;

5）修改表名

alter table <oldTablename> rename to <newTablename>;

二、DCL(数据控制语言)

DCL定义数据访问权限和安全级别，如对用户的创建及授权等。

（1）使用root管理员登陆mysql

mysql -uroot -proot;

不安全，推荐：mysql -uroot -p 回城，再输密码

（2）创建新用户：

CREATE USER ’username’@’123.126.24.254’ IDENTIFIED BY ‘pwd’;

说明：@后可指定：

‘%’ - 所有情况都能访问
‘localhost’ - 本机才能访问
‘123.126.24.254’ - 指定ip才能访问

（3）修改密码

命令:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpwd');

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpwd");

例子:

SET PASSWORD FOR 'admin'@'%' = PASSWORD("admin123");

（4）给用户添加权限

GRANT ALL PRIVILEGES ON dbname.* TO ‘username’@’%’;

说明：

all 可以替换为 insert,select,delete,update,create,drop等细粒度权限；
dbname.*:数据库.表，这里指数据库dbname的所有表，可以指定具体表，如hiov.device；
'username’@’%’：用户名@访问地址，这里指username在所有IP地址都可访问。
GRANT ALL PRIVILEGES ON dbname.* TO ‘username@’%’ WITH GRANT OPTION;
WITH GRANT OPTION：权限传递，使用这个子句时将允许用户username @’%’将其权限分配给他人。

（5）撤销授权：

方法一：

revoke create,alter,drop on dbname.* from ‘username’@’%’;

方法二：
将username用户可访问地址修改为localhost

update mysql.user set host='localhost' where user='username';

（6）删除用户：

方法一：

DELETE FROM mysql.user WHERE user=’username’;

方法二：

DROP user username@ip;

（7）使生效：

FLUSH PRIVILEGES;

（8）查看某个用户授权

show grants for username@localhost;

username@localhost：不加@localhost，默认为root@%

（9）查询用户授权

SELECT user,host FROM mysql.user;

user:用户名；host:所属地址；SELECT *可以查出所有权限信息）

三、DML(数据操作语言)

DML操作数据库记录，即增、删、改、查。

（1）插入数据

insert into <tablename>(<column1Name>,<column2Name>,…) values (<value1>, <value2>,…)

（2）更新数据

update <tablename> set <column1Name>=<newValue1>, <column2Name>=<newValue2>,…;

满足条件更新

update <tablename> set <column1Name>=<newValue1>, <column2Name>=<newValue2>,… where <condition>;

（3）删除数据

delete from <tablename>;

满足条件删除

delete from <tablename> where <condition>

注：条件必须是一个boolean类型的值或表达式，如

where sid=1;

可用在where子句中的比较运算符有：

运算符	说明	运算符	说明
=	等于	!= 或 <>	不等于
>	大于	>=	大于等于
<	小于	<=	小于等于
is null	为空，注：不能使用=null判断是否为空	is not null	非空，注：不能使用!=null判断是否非空
and	且	or	或
not	非	in(…)	在…中；加not，相反
between … and …	在…和…之间；加not，相反	like	模糊匹配，加not相反
REGEXP	正则表达式

四、DQL(数据查询语言)

DQL查询数据记录

1、基本查询

（1）查询所有列(不推荐)

SELECT * FROM emp;

（2）查询指定列

SELECT empmo, ename, mgr FROM emp;

（3）WHERE子句条件查询

SELECT * FROM emp WHERE job='CLERK';

说明：上节所述运算符都可在WHERE子句中使用,举例如下：

1）使用like操作符

%表示一个或多个字符，
_表示一个字符，
[charlist]表示字符列中的任何单一字符，
[^charlist]或者[!charlist]不在字符列中的任何单一字符。

SELECT * FROM emp WHERE ename like '[^SAD]__T%';

2）在where条件中使用in

SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');

3）查询字段内容为空/非空的语句

SELECT * FROM emp WHERE mgr IS NULL | IS NOT NULL;

4）使用逻辑操作符号

SELECT * FROM emp WHERE (sal>500 or job='MANAGE') and ename like 'J%';

（4）字段控制查询

1）去重（DISTINCT）

SELECT DISTINCT sal FROM emp;

2）相加或拼接（+）

用在数值型上为相加，用在字符串型上为拼接

SELECT sal+comm FROM emp;

3）别名

SELECT *,ifnull(sal+comm) as totalFROM emp as e;

说明：用在字段和表名上，as可省略。

（5）排序（ORDER BY）

SELECT * FROM emp ORDER BY deptno, sal DESC;

说明：asc升序；desc降序

（6）聚合函数

聚合函数是用来做纵向运算的函数。有count（个数）、sum（求和）、avg（平均数）、max（最大值）、min（最小值）等，如：

SELECT COUNT(*) FROM emp;

（7）分组（GROUP BY）

SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 group by deptno HAVING SUM(sal)<9000;

说明：WHERE是对分组前记录的过滤，如果某行记录不符合条件，则改行记录不会参与分组；而HAVING是对分组后记录的过滤。

（8）限制结果范围（LIMIT、OFFSET）

1） LIMIT可单独使用，如：

从第一条（起始行0开始）开始，查询5条数据：

SELECT * FROM emp LIMIT 0,5;

2） LIMIT和OFFSET一起使用（Mysql5以后版本）

从第三条（起始行0开始）开始，查询5条数据：

SELECT * FROM emp LIMIT 5 OFFSET 2;

等同于：

SELECT * FROM emp LIMIT 2,5;

注意：不是LIMIT 5,2

2、多表查询

（1）合并结果集（纵向合并）

用于把两个SELECT语句的查询结果合并到一起。

UNION ：去掉重复记录
UNION ALL ：不去除重复记录

SELECT * FROM t1 UNION SELECT * FROM t2;

要求：t1、t2 列数，列类型必须相同。

（2）连接查询（横向扩展）

SELECT * FROM emp e,dept d where e.deptno=d.deptno;

这其实是内连接查询，等同于下面内连接的示例：

1）内连接（INNER JOIN）

内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

在这里插入图片描述

2）外连接（OUTER JOIN）

左连接（LEFT JOIN）
left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）。左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

SELECT * FROM a LEFT JOIN b ON a.id=b.aid;

在这里插入图片描述

右连接（RIGHT JOIN）
同left join相反。

SELECT * FROM a RIGHT JOIN b ON a.id=b.aid;

全连接（FULL JOIN）
全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。
Mysql不支持全连接，但是可以通过left join + union + right join模拟实现：

SELECT * FROM a LEFT JOIN b ON a.id = b.aid
UNION
SELECT * FROM a RIGHT JOIN b ON a.id= b.aid;

在这里插入图片描述

3）交叉连接（笛卡尔积）

交叉连接，得到的结果是两个表的乘积，即笛卡尔积；

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

SELECT * FROM A CROSS JOIN B;

4）自然连接（NATURAL JOIN）

使用 NATURAL JOIN 时，MySQL 将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURAL JOIN 不用指定匹配条件。
NATURAL JOIN 默认是同名字段完全匹配的 INNER JOIN，也可以使用 LEFT JOIN 或 RIGHT JOIN。

SELECT * FROM A NATURAL JOIN B;
SELECT * FROM A NATURAL LEFT JOIN B;
SELECT * FROM A NATURAL RIGHT JOIN B;

（3）子查询（嵌套查询）

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='甘宁');

SELECT * from emp WHERE sal > ALL(SELECT sal FROM emp WHERE ename='甘宁');
SELECT * from emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);

说明：

ALL：满足所有
ANY：满足任意一个

SELECT * FROM emp WHERE (job,sal) IN 
	(SELECT job,sal FROM emp WHERE ename='JAMES');

原文：http://www.lwqgj.cn/1203.html

打不死的小强lee

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql常用语句整理

一、DDL(数据定义语言)DDL定义数据库库、表、表结构、表字段等。1、库（1）查看数据库show databases;（2）切换数据库use <dbname>;（3）创建数据库create database <dbname>;或create database if not exists <dbname> CHARACTER SET u...
复制链接

扫一扫