MySQL基础语法

一、MySQL概述

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。
MySQL数据库管理系统，简称MySQL，是世界上最流行的开源数据库管理系统，其社区版(MySQL Community Edition)是最流行的免费下载的开源数据库管理系统。

二、数据库的数据类型

1.整数、浮点数和定点数类型

MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。其中最常用的是INT类型的。

浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。

DECIMAL类型的取值范围与DOUBLE类型相同。DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长度，D表示的是小数点后的长度。如DECIMAL(5,2)就是的数据7.132插入数据库显示的结果是7.13。

2.字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
注:当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1。

文本类型用于表示大文本数据。它的类型分为TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT四种。
BLOB用于存储二进制数据。

3.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。如下图所示：

TIME类型的显示形式一般为HH:MM:SS，可以使用CURRENT_TIME或NOW()输人当前系统时间。
YEAR类型用于表示年份，当使用YEAR类型时，一定要区分‘0’和0，因为‘0’表示year值是2000年，而数字格式的0表示的YEAR是0000年。
DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。也可以使用NOW来输人当前系统的日期和时间。
TIMESTAMP使用CURRENT_TIMESTAMP输人系统当前日期和时间，输人NULL时系统会输人系统当前日期和时间，无任何输人时系统会输入系统当前日期和时间。

三、数据库、数据表的基本操作

1.数据库的基本操作

数据库的创建

create database 数据库名称;

例如:create database data;
查看该数据库基本信息命令

show create database 数据库名称;

删除数据库命令

drop database 数据库名称;

查询MySQL所有数据库命令

show databases;

切换数据库命令

use 数据库名称;

查看当前使用的数据库

select database();

2.数据表的基本操作

注:在创建数据表之前，需要先选中某个数据库(即你需要创建数据表的数据库)，倘若没有选中，则会报No database selected错误。

2.1创建数据表

 create table 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

2.2查看数据表

查看当前数据库中所有表

 show tables;

查看某数据表的基本信息

show create table 数据表名称;

查看某数据表的字段信息

desc 数据表名称;

2.3修改数据表

修改表名

alter table 初始名 rename to 修改名;

修改字段名

alter table 数据表名 change 初始字段名 修改字段名 数据类型;

修改字段数据类型

alter table 数据表名 modify 字段名 数据类型;

增加字段

alter table 数据表名 add 字段名 数据类型;

删除字段

alter table 数据表名 drop 字段名;

2.4删除数据表

drop table 数据表名;

四、 数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。
下面将逐个介绍数据表的约束：

1.主键约束

primary key(主键约束)用于标识唯一对应的记录，被标识为主键的数据在表中是唯一的且其值不能为空。
设置主键约束第一种方式

create table stu(
id int primary key,
name varchar(20)
);
desc stu;

运行结果:

设置主键约束的第二种方式

create table stu(
id int,
name varchar(20),
primary key(id)
);
desc stu;

运行结果:

2.非空约束

NOT NULL(非空约束)指的是字段值不能为空，不同于主键约束，非空约束可以标识多个字段。
设置非空约束

create table stu(
id int not null,
name varchar(20) not null
);
desc stu;

运行结果:

3.默认值约束

DEFAULT(默认值约束)用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值。
设置默认值约束

create table stu(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

运行结果:

4.唯一性约束

UNIQUE(唯一性约束)用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。
设置唯一性约束

create table stu(
id int not null,
name varchar(20) unique
);
desc stu;

运行结果:

5.外键约束

FOREIGN KEY(外键约束)常用于多张表之间的约束，经常与主键约束一起用。
对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

5.1添加外键约束

外键约束基本语法

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

设置外键约束（在创建表时）
–创建student表作为主表–

create table student(
id int primary key,
gender varchar(20),
name varchar(10),
birthday date
);

–创建class表作为从表并添加外键约束–

create table class(
classid int primary key,
studentid int,
CONSTRAINT fk_class_student FOREIGN KEY(classid) REFERENCES student(id)
);

创建主键约束（创建表后添加）
–创建student表作为主表–

create table student(
id int primary key,
gender varchar(20),
name varchar(10),
birthday date
);

–创建class表作为从表–

create table class(
classid int primary key,
studentid int
);

–添加外键约束–

alter table class add foreign key(classid) references student(id);

5.2删除外键约束

删除外界约束的语法如下：

alter table 从表名 drop foreign key 外键名;

5.3外键约束注意事项

• 主表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主表发生变化时应注意主表与从表的数据一致性问题。
• 主键不能包含空值，但允许在外键中出现空值。

五、数据表的插入、更新、删除

1.数据表的插入

字段插入数据

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

同时插入多条记录

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

2.数据表的更新

基本语法

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

3.数据表的删除

基本语法

DELETE FROM 表名 [WHERE 条件表达式];

六、MySQL数据表查询

1.简单查询

查询所有字段

select * from 表名;

查询指定字段

select 字段1、字段2… from 表名;

算数运算符

 select 字段1,字段2+10… from student;

注:进行算数运算的字段的数据类型必须是int型

2.条件查询

2.1 使用关系运算符查询

在where中可以使用关系运算符进行查询，常用的关系运算符如下图:

查询年龄小于17岁的学生

select * from student where age<=17;

运行结果:

2.2 使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

例如上述例子中 需要将年龄为 17、18岁的学生列出来，则MySQL命令为:

select * from student where age in (17,18);

而查询除17、18岁以外的学生，则为：

select * from student where age not in (17,18);

2.3 使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。

例如上述例子查询15到18岁的学生，命令为:

select * from student where age between 15 and 18;

2.4 使用空值查询

使用 IS NULL关键字判断字段的值是否为空值。

例如

select * from student where name is not null;

2.5 使用AND OR关键字查询

可使用AND关键字可以连接两个或者多个查询条件。

例如，查询既大于十五岁又是男生的学生：

select * from student where age>15 and gender='male';

可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来。

例如，查询大于十五岁或者是男生的学生：

select * from student where age>15 or gender='male';

2.6 使用LIKE关键字查询

可使用LIKE关键字可以判断两个字符串是否相匹配。

普通字符串
例如，查询name为lili的学生：

select * from student where name like 'lili';

含有%的通配符
%用于匹配任意长度的字符串，查询以a开头的字符串为’a%‘，查询以a结束的字符串为’%a’，查询包含a的字符串为’%a%'。

例如，查询以li开头的字符串：

select * from student where name like 'li%';

含有_的通配符
下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。'a__‘查询以a开头的长度为3的字符串，’__a’查询以a为结束的长度为3的字符串，'a__b’查询以a开头b结束的长度为4的字符串。

例如，查询以l开头i结尾长度为4的字符串：

select * from student where name like 'l__i';

2.7 使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条。

例如，查询年龄小于18岁的学生，但只需要三个：

select * from student where age<18 limit 3;

2.8 使用GROUP BY进行分组查询

我们先创建一个数据库和员工表来学习GROUP BY：

-- 创建数据库
CREATE DATABASE mydb;
USE mydb;

-- 创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int
);

-- 向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001); 
INSERT INTO employee values(2,'lucy',9000,1002); 
INSERT INTO employee values(3,'joke',5000,1003); 
INSERT INTO employee values(4,'wang',3000,1004); 
INSERT INTO employee values(5,'chen',3000,1001); 
INSERT INTO employee values(6,'yukt',7000,1002); 
INSERT INTO employee values(7,'rett',6000,1003); 
INSERT INTO employee values(8,'mujk',4000,1004); 
INSERT INTO employee values(9,'poik',3000,1001);

GROUP BY和聚合函数一起使用
统计各部门员工个数

select count(*), departmentnumber from employee group by departmentnumber;

运行结果:

GROUP BY和聚合函数以及having一起使用
统计工资总和大于8000的部门

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

运行结果:

2.9 使用ORDER BY对查询结果排序

基本语法格式

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

注：参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

查询学生并按年纪大小升序排列

select * from student order by age asc;

运行结果：

七、多表连接查询

1.交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可。

2.内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。
准备如下代码:

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建部门表
CREATE TABLE department(
  did int (4) NOT NULL PRIMARY KEY, 
  dname varchar(20)
);

-- 创建员工表
CREATE TABLE employee (
  eid int (4) NOT NULL PRIMARY KEY, 
  ename varchar (20), 
  eage int (2), 
  departmentid int (4) NOT NULL
);

-- 向部门表插入数据
INSERT INTO department VALUES(1001,'财务部');
INSERT INTO department VALUES(1002,'技术部');
INSERT INTO department VALUES(1003,'行政部');
INSERT INTO department VALUES(1004,'生活部');
-- 向员工表插入数据
INSERT INTO employee VALUES(1,'张三',19,1003);
INSERT INTO employee VALUES(2,'李四',18,1002);
INSERT INTO employee VALUES(3,'王五',20,1001);
INSERT INTO employee VALUES(4,'赵六',20,1004);

查询员工姓名以及所属部门命令：

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

运行结果：

3.外连接查询

需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下：
1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。
准备如下代码：

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);
-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1002);
INSERT INTO student VALUES(4,'赵六',23,1003);
INSERT INTO student VALUES(5,'Jack',22,1009);

左（外）连接查询
左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

运行结果：

右（外）连接查询
右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

运行结果: