Posted in mysql

Mysql常用语句整理

   2019年5月3日  18,394  Leave a comment
Mysql常用语句整理

一、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>;

清空表（ truncate 删除表的所有数据，不支持条件过滤，也不支持回滚，不记录日志，效率比 delete 高。 ）

truncate table <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正则表达式exists是否存在；加 not 相反。一般可以和 in/not in 相互替换

四、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;
Mysql常用语句整理-打不死的小强
2）外连接（OUTER JOIN）

<1> 左连接（LEFT JOIN）

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

SELECT * FROM a LEFT JOIN b ON a.id=b.aid;
Mysql常用语句整理-打不死的小强

<2> 右连接（RIGHT JOIN）

同left join相反。

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

<3> 全连接（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;
Mysql常用语句整理-打不死的小强
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');
赏

发表评论

邮箱地址不会被公开。 必填项已用*标注