MySQL必知必记的CRUD命令

MySQL入门基础操作之必知必记的CRUD命令

• 数据库：DataBase（DB）
• 标准的SQL语句：
• 一、查询语句：select
• 二、DML：insert、update、delete
• 三、DDL：create、alter、drop、truncate
• 四、DCL：grant、revoke
• 五、事务控制语句：commit、rollback、savepoint

数据库：DataBase（DB）

登录MySQL：mysql -p 密码 -u 用户名 -h 主机名 --default-character-set=utf8

>mysql -u root -p		root用户登录	-u：指定登录的用户，后面有无空格均可
复制

查看所有DB：SHOW DATABASES;
新建DB：CREATE DATABASE [IF NOT EXISTS] 数据库名;
进入/切换DB：USE 数据库名;
查看DB包含的数据表：SHOW TABLES;
查看表结构：DESC 表名;
复制

结束符：英文分号（;）
大小写：语句不区分大小写->自动转为大写（故一般建议也是用大写）
数据区分大小写
标识符：字母开头+数字+三个特殊字符（#_$）

标准的SQL语句：

• 查询语句：select
• DML：insert、update、delete
• DDL：create、alter、drop、truncate
• DCL：grant、revoke
• 事务控制语句：commit、rollback、savepoint

一、查询语句：select

SELECT 字段... from 表名 WHERE 判断条件 ORDER BY DESC;
SELECT * FROM 表名 WHERE NOT 字段 LIKE '\_%' ESCAPE '\';
复制

去除重复行：SELECT DISTINCT xxx…
a BETWEEN b AND c 包括左右：a>=b && a<=c
LIKE 反斜线（\）作为转义字符，但无法转义自身，需用ESCAPE关键字显式进行转义
IS NULL
IN
NOT、AND、OR
ORDER BY DESC 默认升序ASC，DESC降序

数据库函数

• 单行函数：一次操作一行
  字符函数 数值函数 日期函数 转换函数 通用函数
• 多行函数 ：一次操作多行
  组函数、 聚合函数

多表连接查询-联结(join)：

• 交叉连接-笛卡尔积（cross join）：所有情况的组合 ，不推荐使用
• 内连接 ：多张表通过 相同字段进行匹配，只显示匹配成功的数据

写法一.
	select * from 表名1,表名2
	where 表名1.字段 = 表名2.字段 ;
写法二.
	select * from 表名1 
	inner join 表名2
	on 表名1.字段 = 表名2.字段;
复制

• 不等值连接（一般不用）

select * from 表名1,表名2
	where 表名1.字段 != 表名2.字段;
复制

• 自连接：将一张表通过别名 “视为”不同的表,必须费性能(n的平方)

• 外连接

  左外连接：以左表为基准（左表数据全部显示），去匹配右表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)

a.（oracle独有）
	select * from 表名1,表名2
	where 表名1.字段 = 表名2.字段(+) ;
b.
	select * from 表名1
	left outer join 表名2
	on 表名1.字段 = 表名2.字段;
复制

右外连接：以右表为基准（右表数据全部显示），去匹配左表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)

a.（oracle独有）
	select * from 表名1,表名2
	where 表名1.字段(+) = 表名2.字段 ;
b.
	select * from 表名1
	right outer join 表名2
	on 表名1.字段 = 表名2.字段;
复制

全外连接 = 左外 + 右外连接 - 去重

• 子查询-嵌套查询：

集合运算：
并运算：UNION
交运算：INTERSECT
差运算：MINUS

二、DML：insert、update、delete

插入表信息语句：INSERT INTO 表名(字段...) VALUES(字段对应插入的值...);
同时插入多个值：INSERT INTO 表名(字段...) VALUES(字段对应插入的值...),(字段对应插入的值...),...;
修改表信息语句：UPDATE 表名 SET 字段=修改的值 WHERE 判断条件;
删除表信息语句：DELETE FROM 表名 WHERE 判断条件;
复制

三、DDL：create、alter、drop、truncate

建表：

CREATE TABLE 表名(
	字段 类型 [约束],
	...
);
复制

获取数据建表：

CREATE TABLE 表名
AS 
取建表数据;
复制

修改表结构：

ALTER TABLE 表名
ADD(
	字段 类型 [约束],
	...
);
复制

仅修改一行数据可省略括号

修改列类型：

ALTER TABLE 表名
MODIFY 字段 新类型 [约束] [FIRST | AFTER 指定字段];
复制

删除列：

ALTER TABLE 表名
DROP 字段;
复制

重命名数据表：

ALTER TABLE 表名
RENAME TO 新表名;
复制

修改列名：

ALTER TABLE 表名
CHANGE 字段 新字段 新类型;
复制

删表：

DROP TABLE 表名;
复制

删表-保留表结构：

TRUNCATE 表名;
复制

truncate一次性删除全部，再新建表结构，速度快；delete一行一行删除

五种完整性约束：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

MySQL使用information_schema数据库里的TABLE_CONSTRAINTS表来保存所有的约束信息
行级约束：在建表类型后直接声明 - [约束]
表级约束：在建表末尾处补充说明 - CONSTRAINT 约束名 约束(字段…)
也可建表后在修改添加

增加约束：

ALTER TABLE 表名
ADD 约束(字段...);
复制

修改列类型时增加约束：

ALTER TABLE 表名
MODIFY 字段 新类型 [约束] [FIRST | AFTER 指定字段];
复制

删除约束：

ALTER TABLE 表名
DROP INDEX 约束名;
复制

删除主键约束：

ALTER TABLE 表名
DROP PRIMARY KEY;
复制

添加外键约束：

CONSTRAINT 约束名(表名_主表名_fk) FOREIGN KEY(字段) REFERENCES 主表(字段)	[on delete cascade | on delete set null]
复制

on delete cascade：删主表记录时从表记录级联删除
on delete set null：删主表记录时从表记录的外键设为null

索引：

创建索引：CREATE INDEX 索引名 ON 表名(字段...);
删除索引：DROP INDEX 索引名 ON 表名;
复制

视图：

创建视图：

CREATE OR REPLACE VIEW 视图名
as
创建视图的数据 
[with check option];
复制

with check option：指定不允许修改该视图的数据
Oracle采用-with check only
删除视图：DROP VIEW 视图;

四、DCL：grant、revoke

show grants for xxx;	查看用户权限		
grant connect,resource to xxx;			授予xxx用户链接/资源处理权限	
grant all privileges on DB.* to 用户名@localhost identified by '密码'		为用户授权访问本地DB
grant all privileges on databases.* to 用户名@"%" identified by '密码'		准许用户从其他客户机访问DB	
复制

五、事务控制语句：commit、rollback、savepoint

开启事务：

• BEGIN
• SET AUTOCOMMIT=0
• START TRANSACTION

保留点SAVEPOINT：

• SAVEPOINT savepointName 声明
• ROLLBACK TO savepointName 回滚
• RELEASE TO savepointName 删除