Mysql常用语句整理


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

满足条件删除

delete from <tablename> where <condition>

:条件必须是一个 boolean 类型的值或表达式,如

where sid=1;

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

运算符说明运算符说明
=等于!= 或 <>不等于
>大于>=大于等于
<小于<=小于等于
is null为空,注:不能使用=null 判断是否为空is not null非空,注:不能使用!=null 判断是否非空
andor
notin(…)在…中;加 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;
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 JOINRIGHT 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');


发表评论

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