Mysql入门讲解，语法举例

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

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

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 创建数据库表

创建MySQL表需要：表名 + 表字段名 + 定义每个字段

语法

 CREATE TABLE table_name (column_name column_type);

 CREATE TABLE IF NOT EXISTS 'SAKURA'('uid' INT UNSIGNED AUTO_INCREMENT,

‘uname’ VARCHAR(100) NOT NULL,

PRIMARY KEY (‘uid’))ENGINE = InnoDB DEFAULT CHARSET = utf8;

解析：

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1.

PRIMARY KEY 关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔.

 ENGINE 设置存储引擎，CHARSET 设置编码。

MySQL删除数据表  

语法

删除表用drop

DROP TABLE table_name;

删除表内数据，用delete。

delete from student where T_name = "sakura";

 清除表内数据，保存表结构,用truncate。 

truncate table student;       

MySQL 删除表的几种情况:

1、drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

实例，删除学生表：

drop table student;

2、truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;

实例，删除学生表：

truncate table student;

3、delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

实例，删除学生表：

delete from student;

MySQL 插入数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过PHP脚本来插入数据。

语法

INSERT INTO table_name(field1,field2,....fieldN) values (value1,value2,....value3);

MySQL 查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。

语法

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

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

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

MySQL UPDATE

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。.

语法

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

MySQL LIKE子句

我们知道在MySQL中使用SQL SELECT命令来读取数据，同时我们可以在SELECT语句中使用WHERE子句来获取指定的记录。

WHERE子句中可以使用等号=来设定获取数据的条件，如“runoob_author ='RUNOOB.COM'”。

但是有时候我们需要获取runoob_author字段含有“COM”字符的所有记录，这时我们就需要在WHERE子句中使用SQL LIKE子句。

SQL LIKE子句中使用百分号％字符来表示任意字符，类似于UNIX或正则表达式中的星号*。

如果没有使用百分号％，LIKE子句与等号=的效果是一样的。

语法

以下是SQL SELECT语句使用LIKE子句从数据表中读取数据的通用语法：

SELECT FIELD1 FROM SAKURA WHERE FIELD1 LIKE condition1 filed2 = 'somevalue';

• 你可以在WHERE子句中指定任何条件。
• 你可以在WHERE子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号=。
• LIKE通常与％一同使用，类似于一个元字符的搜索。
• 你可以使用AND或者或指定一个或多个条件。
• 你可以在DELETE或UPDATE命令中使用WHERE ... LIKE子句来指定条件。
• '％a'//以及结尾的数据
  'a％'//以开头的数据
  '％a％'//含有a的数据
  '_a_'//三位且中间字母是a的
  '_a'//两位且结尾字母是a的
  'a_'//两位且开头字母是的

查询以java字段开头的信息。

SELECT * FROM SAKURA WHERE name LIKE'JAVA%';

查询包含java字段的信息。

SELECT * FROM SAKURA WHERE name LIKE'%JAVA%';

查询以java字段结尾的信息。

SELECT * FROM SAKURA WHERE name LIKE‘%JAVA’；

 

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

MySQL 排序我们知道从 MySQL 表中使用 SQL SELECT 语句来读取：

MySQL 拼音排序

如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * 
FROM runoob_tbl
ORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * 
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);

MySQL GROUP BY 语句

1、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：

SELECT name FROM employee_tbl GROUP BY name;

 

Mysql 连接的使用

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

列子：

1.学生表student（s_id,name,sex,score）    

2.班级表class (c_id,c_name)

3.学生-班级student_class(s_id,c_id)

#查询每个班级的学生人数.

SELECT c_name,count(*) FROM student_class ,class WHERE student_class.cid=class.c_id GROUP BY c_name;

#查询每个班的男女人数，以及总人数

SELECT c_name,sum(case when sex='女' then 1ELSE 0 END)as '女',sum(case when sex='男' then 1 ELSE 0 END)as'男',count(*)as total FROM student,student_class,class WHERE student.s_id=student_class.s_id and student_class.c_id=class.c_id GROUP BY c_name;

 

#查询每个班级的平均分 按照平均分高低排序

SELECT c_name,avg(score)as avg_s FROM student,student_class,class WHERE student.s_id=student_class.s_id and student_class.c_id = class.c_id GROUP BY c_name ORDER BY avg_s DESC;

 

#查询一班中 分数大于90分的学生信息

SELECT * FROM student WHERE score > 90 and s_id in(SELECT s_id FROM student_class WHERE c_id in(SELECT c_id from class WHERE c_name = '一班'));

 

#查询每个班级的女生人数和平均分

select c.c_name.count(s.s_id)as'女生人数',avg(s.score)as'平均分' from class c inner join student_class sc on sc.c_id = c.c_id

inner join student s on s.s_id = sc.s_id where s.sex='女' group by c.c_name ORDER BY avg(s.score) desc;

 

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexname ON tablename(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexname(columnName);

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

删除索引的语法

DROP INDEX [indexName] ON tablename;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

 

MySQL分页查询优化

 一般分页查询

 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

select * from table limit 5; --返回前5行

select * from table limit 0,5; --同上，返回前5行

select * from table limit 5,10; --返回6-15行

MS SqlServer TOP子句

语法：

SELECT TOP number|percent column_name(s) FROM table_name;

1. 选取表中前2条记录

select top 2 * from table;

2. 选取表中50%的记录

select top 50 persent from table;