mysql入门基本概念及语法

一、数据库的概念

数据库：按照数据结构来组织、存储和管理数据的仓库。其中关系型数据库最为常用。

关系型数据库：

• 1.数据以表格的形式出现
• 2.每行为各种记录名称
• 3.每列为记录名称所对应的数据域
• 4.许多的行和列组成一张表单
• 5.若干的表单组成database

二、数据库中的一些名词

• 两种单位：

  • 1.库：相当于文件夹——用来管理，本身没法存
  • 2.表：相当于文件——存数据

• 其中表分为

  • 行：一条数据
  • 列（也叫字段）：每条数据的项

• 主键：主键是唯一的。若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键 。

  eg:
  一个学生信息表(学号，姓名，性别，班级)
  其中每个学生的学号是唯一的，学号就是一个主键

• 外键：外键用于关联两个表。

  成绩表中的学号不是成绩表的主键，但它和学生表信息中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生信息表的外键。

• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

  eg：
  成绩表（学号 科目 成绩）
  一个学生有很多门科目，单用学号无法确定一条数据。这时我们使用学号+科目来确定，这时学号和科目被称为复合主键。

• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

三、数据库基本操作语法

• 查看所有数据库：SHOW DATABASES;

• 切换到数据库：USE 数据库名;

• 查看数据库中所有的表：SHOW TABLES;

• 查看数据表属性：SHOW COLUMNS FROM 数据表名;

• 查看数据表的详细索引信息：SHOW INDEX FROM 数据表名;

• 查看当前表的统计信息：SHOW TABLE STATUS FROM XXX LINK XXX;

• 创建数据库：CREATE DATABASE 数据库名;

• 删除数据库：DROP DATABASE 数据库名;
  在未进入系统模式下mysqladmin -u root -p drop 数据库名

• 重命名数据库：官方没有给标准方法，可以使用创建新库，然后导入实现

• 创建数据表：

  CREATE TABLE table_name (column_name column_type);
  
  CREATE TABLE IF NOT EXISTS `runoob_tbl`(
     `runoob_id` INT UNSIGNED AUTO_INCREMENT,
     `runoob_title` VARCHAR(100) NOT NULL,
     `runoob_author` VARCHAR(40) NOT NULL,
     `num` float default 6.8，
     `submission_date` DATE,
     PRIMARY KEY ( `runoob_id` )
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_bin;
  

  ​ 注意：

  • 创建数据库是必须要设置PRIMARY KEY
  • 括号后面的内容，可以省略，在数据库文档中有默认设置
  • 设置结构，必须包括名字和数据类型（not null，auto_increment，default为可选项）

• 删除数据表：drop table 表名;

• 重命名数据表：alter命令

四、常用数据类型

1.数值型

• TINYINT–整数—1字节
• TSMALLINT–整数—2字节
• MEDIUMINT – 整数— 3字节
• INT–整数—4字节
• BIGINT–整数—8字节
• FLOAT–浮点数–4字节
• DOUBLE–浮点数–8字节
• DECIMAL–浮点数（有效数字，小数点位数）–MAX（参数+2）

2.字符串

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

• char和varchar区别：char类型是定长字符串，varchar类型是变长字符。char的长度如果没有填满，会用空白补齐。varchar在字符串的后部有一部分空间存储字符串长度。所以在字符串中如果字符串长度稳定，可以使用char，长度变化较大使用varchar。MySQL官方建议使用varchar。
• text类型的特征：不能有默认值，不知道最大长度时使用。一般都使用varchar类型来代替text。
• nchar、nvarchar类型，存储中文字符集。正常情况下，我们使用varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时, 会出现乱码(显示为??)．
• mysql中不会显示nchar、nvarchar，因为mysql是通过字符集来进行设置的。
• blob存储二进制，可以将图片存储进数据库

3.时间日期

类型	所占字节	格式
DATE	3	YY YY-MM-DD
TIME	3	HH:MM:SS
YEAR	1	YYYY
DATATIME	8	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	毫秒数

4.枚举类型

• ENUM：最多65536个元素

• SET：最多64个元素

五、数据库编码方式

数据库的在开发之初就要考虑到使用场景的需求，根据需求设计合理的字符编码集，便于数据库后期的维护和扩展。要想支持更改国家的文字，就要选择容量更大的编码。

常用编码：

• 中文编码 ：GBK、GB2312，big5（繁体）
• 万国码：utf8（3字节），utf8mb4（4字节）

编码的排序后缀含义：
　　_BIN 二进制排序
　　_CI(CS) 是否区分大小写，CI不区分，CS区分
　　_AI(AS) 是否区分重音，AI不区分，AS区分
　　_KI(KS) 是否区分假名类型,KI不区分，KS区分
　　_WI(WS) 是否区分宽度 WI不区分，WS

注意：
Unicode字符编码，是在通用字符集基础上发展的一种可以容纳世界上所有文字的字符编码方案。utf-8是遵循这个编码方案的字符集。

六、数据表数据操作四大基本语句增 删 改 查

1.增

新增一行

INSERT INTO tablename (item1, item2, item3) VALUES ("value1","value2","value3");

新增一列
格式: alter table 表名 add 新增列名 列元素类型 约束;

alert table student add sex varchar not null; 

2.删
删除某一列中的某个值
格式：DELETE FROM 表名称 WHERE 列名称 = 值;

DELETE FORM student WHERE id = 100;

不使用where语句进行筛选，会删除全部表中数据。

删除整列
格式：alter table 表名 drop 要删除的列名;

alert table studet drop sex;

3.改

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 旧值;

4. 查

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 使用 WHERE 语句来包含任何条件。
• 使用 LIMIT 属性来设定返回的记录数。
• 通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。offset 必须和 limit结合使用，否则不会生效。
• 输出的属性顺序和命令的顺序相同。

七、sql约束

约束用于限制加入表数据的类型，可以在创建表时规定约束，也可以在创建之后。

• not null:强制列不接受null值，not null约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或更新记录。

create table student (
id int not null,
name varchar(30) not null,
birthday date
);

//强制id和name列不接受null值

• unique: unique约束唯一标识数据库中的每条记录 ， unique 和 primary key 约束均为列或列集合提供了唯一性的保证。primary key 拥有自定义的unique约束。

注意:每个表可以有多个unique约束，但是只能有一个primary key约束。

create table student(
id int not null,
name varchar(30) not null,
birthday date,
constant uc_personID unique (id,name)
);
//多个unique值

• primary key: primary key约束唯一标识数据库表中的每条记录，主键必须包含唯一的值，主键列不能包含null值，每个表都应该有一个主键，并且只能有一个主键。

create table student(
id int not null,
name varchar(30) not null,
birthday date,
primary key(id);
);

• foreign key: 一个表中的foreign key指向另一个表中的primary key，foreign key约束用于预防破坏表之间连接的动作。foreign key约束也能防止非法数据插入外键列，因为它必须是指向的那个表中的值之一。

create table student(
id int not null,
name varchar(30) not null,
birthday date,
primary key(id);
);

create table score(
id int not null,
Chinese float,
foreign key (id) references student(id)//score的id指向student的id
);

//向表中插入记录
insert into score(id,Chinese)values(101,95.8);
//此时 如果student的id字段存在101则插入成功，否则插入失败

• check:check约束用于限制列中的值的范围，如果对单个列定义check约束，那么该列只允许特定的值，如果对一个表定义check约束，那么此约束会在特定的列中对值进行限制。

create table student(
id int not null,
name varchar(30) not null,
birthday date,
check(id>0)//id列必须大于0
);

• default: default约束用于向列中插入默认值，如果没有规定其他的值，那么会将默认值添加到所有的新记录

create table student(
id int not null,
name varchar(30) not null,
birthday date,
sex varchar(4) not null defalut "男"
);

八、WHERE筛选

• WHERE 字符比较没有区分大小写，添加BINARY启动大小写。
• 使用表中字段进行比较，来筛选需要的信息。

九、LIKE模糊查找

//将 student 表中获取 name 字段中以 锦 为结尾的的所有记录
SELECT * from student  WHERE name LIKE '%锦';

• where和like结合使用，实现文本的模糊搜索。
• %来表示任何字符
• 补充如下

   '%a'     //以a结尾的数据
   'a%'     //以a开头的数据
   '%a%'    //含有a的数据
   '_a_'    //三位且中间字母是a的
   '_a'     //两位且结尾字母是a的
   'a_'     //两位且开头字母是a的

十、UNION 查找合并

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• UNION可以合并多个查询的信息，将多张表的相关信息筛选合并。
• UNION的参数可选，默认是DISTINCT，表示过滤掉重复的信息。ALL返回所有结果集，包含重复数据。
• 可以对多个属性进行数据合并，筛选时要求属性的个数和类型相同。

十一、DISTINCT去重

SELECT DISTINCT ...

筛选过滤出相同的数据，当有多个属性时，必须完全相同，才会剔除

十二、ORDER BY 排序

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE conditions LINK ...];
ORDER BY field1, [field2...] [ASC [DESC]]

• 可以设定多个字段来排序。每个字段设置排序规则，然后用“,”分开
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 可以添加 WHERE…LIKE 子句来设置条件。

十三、GROUP BY 分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

• 使用分组，将列表中某一属性相同的内容统一起来。

常用函数

• COUNT(expression)：返回查询的记录总数，expression 参数是一个字段或者 * 号

• SUM(expression)：返回指定字段的总和

• AVG(expression)：返回一个表达式的平均值

• MAX/MIN(expression)：返回字段 expression 中的最大(小)值

• CURDATE()/CURRENT_DATE()：返回当前日期

• CURTIME()/CURRENT_TIME()：返回当前时间

• CURRENT_TIMESTAMP()：返回当前日期和时间

• DATE()：从日期或日期时间表达式中提取日期值

数据库导出、导入

导出

mysqldump -u username -p 数据库名 table1name table2name ...> 路径和文件名.sql
//备份某几张表
mysqldump -u username -p --databases -> Backup.sql
//备份整个数据库的数据
mysqldump -u username -p -all-databases > BackupName.sql
//备份全部数据库

导出

mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

• 导入时，会覆盖掉原来的信息。