数据库操作

DDL数据定义语言

数据定义语言DDL用来创建数据库中的各种对象：表、视图、索引、同义词、聚簇等

如：CREATE TABLE (表) / VIEW (视图) / INDEX (索引词 ) / SYN (同义) / CLUSTER (簇)

DDL操作是隐性提交的！不能rollback

所有的语句都使用分号结尾

连接数据库（这个不需带空格，空密码的情况下，-p后面不能带空格）

mysql -uroot -p

修改密码

update mysql.user set authentication_string=password('密码') where user='root' Host='localhost';

刷新权限

flush privileges;

查看所有数据库

show databases;

切换数据库

use 数据库名;

查看所有的表

show tables;

查看表的信息

describe 表名；

创建数据库

create database 数据库名；

退出连接

exit;

创建数据库

mysql关键字不区分大小写

如果该数据库不存在的话，则创建数据库

create database if not exists 数据库名;

创建数据库时指定默认字符集

create database if not exists 数据库名 default character set = utf8;

如果该数据库存在，则删除数据库

drop database if exists 数据库名;

查看创建数据库的语句

show create database 数据库名;

如果你的表名或者字段名是一个特殊字符，就需要带 ``

创建数据库表

模板

create table [IF NOT EXISTS] `表名`(
	`字段名` 列类型(长度) 索引 注释, 
	`字段名` 列类型(长度) 索引 注释,
	`字段名` 列类型(长度) 索引 注释,
	primary key(`字段名`)
)表引擎 字符集设置 注释

例子：

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',		--必须是单引号
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

查看创建表的语句

show create table 表名;

查看表的结构

desc 表名;

修改删除数据库表

修改表名

alter table 旧表名 rename as 新表名;

增加表的字段

alter table 表名 ADD 字段名 列属性();

修改约束

alter table 表名 modify 字段名 列属性();

字段重命名

alter table 表名 change 旧字段名 新字段名 列属性();

删除表的字段

alter table 表名 drop 字段名;

删除表

drop table [if exists] 表名;

所有的创建和删除尽量加上判断，以免报错

sql关键字大小写不敏感，建议大家写小写

所有符号全部用英文

​

DML 数据操作语言

数据操纵语言DML主要有三种形式：

1. 插入：INSERT

2. 更新：UPDATE

3. 删除：DELETE

insert

insert into `表名`(`字段1`,`字段2`,...,`字段3`) values (值1,值2,...,值3);

插入两条数据

insert into `表名`(`字段1`) values (值1), (值2);

主键自增可以省略

字段名是可以省略的，但是后面的值必须按照数据库表字段的顺序全都对应上，一个都不能少

可以同时插入多条数据，VALUES后面的每一组值值，需要使用英文逗号隔开

update

update 表名 set 字段1=值1,字段2=值2,...，字段3=字段3 where 条件;

如果不带条件，修改的则是整个表的字段

delete

delete from 表名 where 条件;

truncate 命令作用：完全清空一张表的数据，表的结构不会变

truncate 表名;

区别：

• 相同点：

  • 都能删除数据，都不会删除表结构

• 不同点：

  • trancate 重新设置自增列，计数器归零
  • trancate 不会影响事务

where条件子句

搜索的条件由一个或多个表达式组成，结果为布尔值

详情搜索 sql运算符 查看

DQL 数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

SELECT 字段名 FROM 表或视图名 WHERE 查询条件;

查询所有记录

select * from 表名;

查询指定字段信息

select 字段 from 表名;

别名 as 可以给字段起别名，也可以给表起别名，as在某些特定的情况下可以不写

select 字段 as 字段别名 from 表名 as 表别名;

函数 concat()

select concat(名字:name) as 新名字 from 表名;

输出结果就是列名为新名字，数据是名字:加上name的数据

去重 distinct

select distinct * from result;

数据库的列（表达式）

查询mysql版本

select version();

用来计算，例：3*100-1

select 3*100-1;

查询自增的步长

select @@auto_increment_increment;

模拟学生成绩+1分查询

select stuScore+1 from score;

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量....

select 表达式 from 表;

联表查询join

超过一张表就使用连接查询

分析需求,分析查询的字段来自哪些表，（连接查询）

确定使用哪种连接查询 7种

join on：连接查询，on代表条件判断

where：等值查询

1. 内连接

select * from A inner join B where A.key=B.key;

2. 左连接

select * from A left join B on A.key=B.key where B.key is null;

3. 右连接

select * from A right join B on A.key=B.key where A.key is null;

4. 左外连接

select * from A left join B on A.key=B.key;

5. 右外连接

select * from A right join B on A.key=B.key

6. 全外连接

UNION ：有去重的功能。

select * from A left join B where A.key=B.key 
union 
select * from A right join B where A.key=B.key;

7. 两表独有的数据集

select * from A left join B on A.key=B.key where B.key is null 
union 
select * from A right join B on A.key=B.key where A.key is null;

排序和分页

排序：

• asc：升序
• desc：降序

order by 字段名 顺序

分页：缓解数据库压力，不用分页的话就用瀑布流，例如百度图片

语法：

limit 起始值,页面大小

表达式

(n-1)*pageSize , pageSize

• pageSize：页面大小

• (n-1)*pageSize：起始页

子查询和嵌套查询

select * from xxx where id=(
	select id from xxx where id in (
		select id from xxx where ...
	)
)

分组查询

select * from xxx where ... group by xxx having ...

相关博客：

• https://www.cnblogs.com/mark5/p/11115750.html

union

相关文章

• https://www.runoob.com/mysql/mysql-union-operation.html

mysql自带函数

这里只是部分函数：

• UUID()：自动生成一串UUID，不需要参数

• MD5(param)：数据库级别的MD5加密

事务

mysql是默认开启事务自动提交的

set autocommit = 0	//关闭

set autocommit = 1	//开启（默认）

上面两个语句不要没事闲的运行！！！

成功时提交 commit

失败时回滚 rollback

事务原则，ACID:

原子性：要么都成功，要么都失败

一致性：事务前后，数据完整性一致

隔离性：多个用户并发访问数据库时， 事务之间不会相互干扰

持久性：事务一旦提交则不可逆，被持久化到数据库中

索引

相关博客：

• https://blog.csdn.net/wufuhuai/article/details/79631466

分类

主键索引：唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引：避免重复的列出现，唯一索引可以重复，多个列都可以表示为唯一索引

常规索引：默认的，index。key关键字来设置

全文索引：快速定位索引

使用

1. 在创建表的时候给字段增加索引

2. 创建完毕后增加索引

显示所有的索引信息

show index from 表名;

增加一个全文索引

alter table 数据库名.表名 add fulltext index `索引名`(`列名`)

创建常规索引

-- 索引名 为 id_表名_字段名
create index 索引名 on 表名(`字段名`)

分析sql语句执行的状况

-- 非全文索引
EXPLAIN 语句;

-- 全文索引
explain select * from 表名 match(字段) against('值');

原则

• 索引不是越多越好
• 当数据量大的时候再去建索引（大于500万条）
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

数据结构及算法原理

相关文章：

• http://blog.codinglabs.org/articles/theory-of-mysql-index.html
  ​

约束

阿里mysql规约硬性规定不得使用mysql约束

如果需要这个功能，需要用逻辑去实现

MySQL编程

DELIMITER $$		--写函数之前必须写，标志

CREATE FUNCTION 函数名()
RETURNS INT
BEGIN
	--函数体
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;

	WHILE i<num DO
		--语句
		INSERT INTO 表名(`字段名1`,`字段名2`...)VALUES ('值1','值2'...);
		SET i = i+1;
	END WHILE;
	RETURN i;

END;

调用函数

select 函数名();

DCL 数据控制语言

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1. GRANT：授权。

2. ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚命令使数据库状态回到上次最后提交的状态。其格式为：

rollback;

3. COMMIT [WORK]：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。

   在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

   • 显式提交：用COMMIT命令直接完成的提交为显式提交

     COMMIT；

   • 隐式提交：

     用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

   • 自动提交

     若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

     SET AUTOCOMMIT ON

用户管理，权限管理

可视化操作软件可以直接用户权限

创建用户，用户名不用加引号

CREATE USER 用户名 IDENTIFIED BY '密码'

修改密码

• 当前用户

  SET PASSWORD = PASSWORD('新密码')

• 指定用户

  SET PASSWORD FOR 用户名 = PASSWORD('新密码')

重命名

RENAME USER 用户名 TO 新用户名;

用户授权全部的权限

ALL PRIVILEGES 库.表

ALL PRIVILEGES除了给别人授权，其他都能干

GRANT ALL PRIVILEGES ON *.* TO 用户名

查询权限

• 指定用户

  SHOW GRANTS FOR 用户名;
  
  SHOW GRANTS FOR root@localhost;

• root权限

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

撤销权限 REVOKE

REVOKE ALL PRIVILEGES ON *.* FROM 用户

删除用户

DROP USER 用户名

备份

导出

• 直接拷贝物理文件

• 在可视化工具中手动导出

使用命令行导出(cmd等) mysqldump 命令行使用

mysqldump -h主机名 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名

注意：

• 文件名不要忘记加.sql后缀

• 不加表名则视为导出数据库

导入

登录的情况下，选中数据库

use 数据库名;

选择源文件：

source 物理磁盘位置/文件名

未登录的情况下：

mysql -u用户名 -p密码 库 < 源文件

数据库设计三大范式

第一范式

• 数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性，而不是集合。

保留数据的原子性（不可再分）

反例：省市区三个信息不能放在一个字段内

第二范式

满足第一范式的基础上：

• 表中的所有列，都必需依赖于主键，而不能有任何一列与主键没有关系（一个表只描述一件事情）。

消除表的无关数据，一张表只描述一个事情。主键存在的意义就是唯一地标识表中的某一条记录。如果某一列和该行记录没关系，也就没必要存在。

反例：天气和用户没什么关系，所以用户表里不需要有天气字段

第三范式

满足第二范式的基础上：

• 任何非主属性不依赖于其它非主属性（在第二范式基础上消除传递依赖）（也表明不允许数据存在冗余的现象）

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

与主键直接相关：例如两张表的关联需要保存的是两张表的主键

阿里mysql规约：关联查询的表不得超过三张

规范性和性能的问题：

• 考虑商业化的需求和目标，（成本和用户体验），数据库的性能更加重要
• 在考虑规范和性能的问题的时候，需要适当考虑一下 规范性
• 故意给某些表增加一些冗余的字段。（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）