mysql（增删改查)

SQL语言

关系型数据库的常见组件

• 数据库： database
• 表： table，行： row 列： column（域）
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 存储过程： procedure
• 存储函数： function
• 触发器：trigger
• 事件调度器： event scheduler,任务计划

SQL语言的规范

在数据库系统中，SQL语句不区分大小写，建议用大写
SQL语句可单行或多行书写，以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性

注释

• SQL：标准

  –注释内容 单行注释，有空格

  /*注释内容*/ 多行注释

• MySQL注释：

  ·# 注释内容和 shell一样

数据库对象和命名

数据库的组件（对象）：
数据库，表，索引，视图，存储过程，函数，触发器，事件调度器等

命名规则：

• 必须以字母开头，可包括数据和三个特殊字符（# _ $）
• 不要使用Mysql的保留字
• 同一个database（Schema）下的对象不能同名

SQL语句分类

• DDL: Data Defination Language 数据定义语言
  CREATE，DROP，ALTER

• DML: Data Manipulation Language 数据操纵语言
  INSERT，DELETE，UPDATE

• DQL：Data Query Language 数据查询语言
  SELECT

• DCL：Data Control Language 数据控制语言
  GRANT，REVOKE，COMMIT，ROLLBACK

获取SQL 命令使用帮助：

官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

MySQL字符集默认是latin文 从Mysql8.0开始默认就是utf8mb4

设置服务器默认的字符集

vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

设置mysql客户端默认的字符集

vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4

创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';

MariaDB [(none)]> create  database db1;
Query OK, 1 row affected (0.00 sec)

[root@Centos7 db1]# cat  db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
[root@Centos7 db1]# pwd
/var/lib/mysql/db1   #默认数据存放位置在 /var/lib/mysql

创建指定字符集数据库

MariaDB [(none)]> create  database  if not exists db2 character set 'utf8';
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

修改数据库

ALTER DATABASE DB_NAME character set utf8;

MariaDB [(none)]> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> alter  database db1 character set  utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

MariaDB [(none)]> drop  database db1;
Query OK, 0 rows affected (0.03 sec)

数据库类型

数据类型参考链接
https://dev.mysql.com/doc/refman/8.0/en/data-types.html

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(±9.22*10的18次方)
上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些
交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是
相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

浮点型(float和double)，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以
实际为准，即6位

定点数
在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如:
decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字
节，小数点后的9个数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占
用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储
财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串(char,varchar,text)
char(n) 固定长度，最多255个字符
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合
char和varchar：
参考：https://dev.mysql.com/doc/refman/8.0/en/char.html

日期时间类型
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记
录最后被修改的时间

修饰符
适用所有类型的修饰符：
NULL 数据列可包含NULL值，默认值
NOT NULL 数据列不允许包含NULL值，*为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号

DDL语句

表：二维关系
设计表：遵循规范
定义：字段，索引
字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上

创建表

create table  NAME

获取帮助

HELP CREATE TABLE

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：
Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型

通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement

create table  test  select   * from test1

通过复制现存的表的表结构创建，但不复制数据 只复制表的结构

-+------+-----+---------+-------+
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }

create  table test  like test1

查看表

show table  [from  NAME]

查看表的结构

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看表的状态

SHOW TABLE STATUS LIKE 'tbl_name’

show table  status like  'test'\G 

查看库中所有的表

SHOW TABLE STATUS FROM db_name

show table status from test\G

删除表

DROP TABLE  'db_name';

修改表

ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

DML语句

插入一行或者多行

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

注意：一定要有限制条件，否则将修改所有行的指定字段

可利用mysql 选项避免此错误：

[root@centos ~]#vim /etc/my.cnf
[mysql]
safe-updates

DELETE语句

删除表中数据，但不会子哦对那个缩减数据文件的大小

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数

注意：一定要有限制条件，否则将清空表中的所有数据
如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小

TRUNCATE TABLE tbl_name;

编辑表

OPTIMIZE TABLE tb_name

DQL语句

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, …
WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, …)
IS NULL
IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用

REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
常见聚合函数：avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

判断是否为NULL
MariaDB [hellodb]> select * from students where classid is null;

MariaDB [hellodb]> select * from students where classid <=> null;

MariaDB [hellodb]> select * from students where classid is not null;