目录
13.3.1 只复制数据表结构到新表(主键类型和自增方式是不会复制过去的)
一、SQL简述
1.SQL的优点
1、简单易学,具有很强的操作性
2、绝大多数重要的数据库管理系统均支持SQL
3、高度非过程化;用SQL操作数据库时大部分的工作由DBMS自动完成
2.SQL的分类
1、DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
4、DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
二、数据库的数据类型
1.数值类型:
根据数值取值范围的不同MySQL 中的整数类型可分为5种,分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT
类型 | 大小 | 有符号(SIGNED) | 无符号(UNSIGNED) | 描述 |
tinyint | 1 byte | (-128,127) | (0,255) | 小整数值 |
smalltnt | 2 bytes | (-32768,32767) | (0,65535) | 大整数值 |
mediumtnt | 3 bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
int或integer | 4 bytes | (-2147483648,2147483647) | (0,2^64-1) | 大整数值 |
BIGINT | 8 bytes | / | / | 极大整数值 |
FLOAT | 4 bytes | / | / | 单精度浮点数值 |
DOUBLE | 8 bytes | / | / | 双精度浮点数值 |
2.字符串类型:
在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是:VARCHAR存储可变长度的字符串
类型 | 大小 | 描述 |
char | 0-255 bytes | 定长字符串 |
varchar | 0-65535 bytes | 变长字符串 |
tinyblob | 0 -255 bytes | 不超过255个字符的二进制数据 |
tinytext | 0-255 bytes | 短文本字符串 |
blob | 0-65 535 bytes | 二进制形式的长文本数据 |
text | / | 长文本数据 |
mediumblob | / | 二进制形式的中等长度文本数据 |
mediumtext | / | 中等长度文本数据 |
longblob | / | 二进制形式的极大文本数据 |
longtext | / | 极大文本数据 |
3.日期类型:
类型 | 大小 | 范围 | 格式 | 描述 |
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值 |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000 -01-01 00:00:00 至 ~ | / | 混合日期和时间值 |
TIMESTAMP | 4 | 1970 -01-01 00:00:00:00 至2038 | / | 时间戳 |
三、 数据库、数据表的基本操作
1.数据库的基本操作
1.1创建数据库
创建数据库就是在数据库系统中划分一块空间存储数据,语法如下:
create database 数据库名字;
创建数据库后查看该数据库基本信息MySQL命令:
show create database 数据库名字;
1.2删除数据库
drop database 数据库名字;
1.3查询所有的数据库
show databases;
1.4查询当前数据库
select database();
1.5使用切换数据库
use 数据库名字;
2.数据表的基本操作
数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意:在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作,否则会抛出“No databas selected”错误。
语法如下:
create table 表名(
字段1 字段类型,
字段2 字段类型,
...
字段n 字段类型
);
2.1创建数据表
示例:创建学生表Mysql命令:
mysql> create table student(
-> id int,
-> name varchar(20),
-> gender varchar(10),
-> birthday date
-> );
2.2查看数据表
2.2.1 查询当前数据库所有表
基本语法:
show tables;
示例:查看当前数据库中所有表Mysql命令:
show tables;
运行效果展示:
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee |
| goods |
| student |
+------------------+
3 rows in set (0.00 sec)
2.2.2 查看表的基本信息
基本语法:
show create table 数据库名字;
示例:查看表的基本信息Mysql命令:
show create table student;
运行效果展示:
mysql> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2.3 查看表的字段信息
基本语法:
desc 数据库名字;
示例:查看表的字段信息Mysql命令:
desc student;
运行效果展示:
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.3修改数据库
有时,希望对表中的某些信息进行修改,例如:修改表名、修改字段名、修改字段 数据类型…等等。在MySQL中使用alter table修改数据表.
2.3.1 修改表名
基本语法:
alter table 表名 rename to 新表名;
示例:修改student表名为stu Mysql命令:
alter table student rename to stu;
运行效果展示:
mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.00 sec)
2.3.2 修改字段数据类型
基本语法:
alter table 表名 modify 字段名 新数据类型(长度);
示例:修改字段name varcahr(20)数据类型为name varchar(10) Mysql命令:
alter table student rename to stu;
运行效果展示:
mysql> alter table stu modify name varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.3.3 修改字段名
基本语法:
alter table 表名change 旧字段名 新字段名 类型(长度);
示例:修改字段名name为sname Mysql命令:
alter table stu change name sname varchar(10);
运行效果展示:
mysql> alter table stu change name sname varchar(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.3.4 增加字段
基本语法:
alter table 表名 add 增加的字段名 类型(长度);
示例:增加字段address varchar(50) Mysql命令:
alter table stu add address varchar(50);
运行效果展示:
mysql> alter table stu add address varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| sname | varchar(10) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2.3.5 删除字段
基本语法:
alter table 表名 drop 字段名;
示例:删除字段 Mysql命令:
alter table stu drop address;
运行效果展示:
mysql> alter table stu drop address;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| sname | varchar(10) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.4删除数据表
基本语法:
drop table 表名;
示例:删除stu数据表 Mysql命令:
drop table stu;
运行效果展示:
mysql> drop table stu;
Query OK, 0 rows affected (0.00 sec)
四、数据表的约束
为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;这些规则常称为表的约束。
为什么要给表中的列添加约束?
- 保证数据的完整性。
- 保证数据的有效性。
- 保证数据的正确性。
4.1 常见约束
约束条件 | 说明 | 作用 |
NOT NULL | 非空约束 | 限制此列的值必须提供,不能为NULL |
UNIQUE | 唯一约束 | 在表中的多条数据,此列值不能重复 |
PRIMARY KEY | 主键约束 | 唯一+非空,能够唯一标识数据表中的一条数据 |
FOREIGN KEY | 外键约束 | 建立不同表之间的关系 |
4.2 非空约束
非空约束即 NOT NULL指的是字段的值不能为空,基本的语法格式如下所示:
字段名 数据类型 not null;
示例:将stu表中的name设置为not null:
mysql> create table stu(
-> id int,
-> name varchar(20) not null
-> );
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.3 唯一约束
唯一性约束即UNIQUE用于保证数据表中字段的唯一性,即表中字段的值不能重复出现,其基本的语法格式如下所示:
字段名 数据类型 unique;
示例:将stu表中的name设置为unique:
mysql> create table stu(
-> id int,
-> name varchar(20) unique
-> );
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.4 主键约束
主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号,并且这个身份证号是唯一的。
主键约束基本语法:
字段名 数据类型 primary key;
设置主键约束(primary key)的第一种方式:
示例:将stu表中的id设置为primary key:
mysql> create table stu(
-> id int primary key,
-> name varchar(20)
-> );
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
设置主键约束(primary key)的第二种方式:
mysql> create table stu(
-> id int,
-> name varchar(20),
-> primary key(id)
-> );
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.4.1 添加主键约束
alter table 表名 modify 字段名 类型 primary key;
示例:创建表时没有给id加主键约束,之后加上:
mysql> create table stu(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> alter table stu modify id int primary key ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.4.2 删除数据表主键约束
alter table 表名 drop primary key;
示例:删除stu表中id的primary key:
mysql> alter table stu drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
运行效果展示:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.4.3 主键自动增长
当有些数据表中没有合适的列作为主键时,我们可以额外定义一个与记录本身无关的列(id)作为主键此列数据无具体含义主要用来标识一条记录,在mysql我们可以将此列定义为int,同时设置为自动增长,当我们向数据表中新增一条数据时,无需提供id,它会自动生成。
基本语法:
定义int类型字段自动增长 : auto_increment
示例:
mysql> create table type(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
运行效果展示:
mysql> desc type;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
注意:自动增长是从1开始的,每增加一天记录,自动增长的列会自定+1,当我们把某条记录删除之后再添加数据,自动增长的数据也不会重复生成(只保证唯一性,不保证连续性)
4.5 外键约束
外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下:
--- 在创建数据表时语法如下:
constraint 外键名 foreign key (从表外健字段)references 主表 (主键字段)
--- 将创建数据表创号后语法如下:
alter table 从表名 add constraint 外键名 foreign key (从表外健字段) references 主表 (主键字段)
示例:创建一个学生表 MySQL命令:
mysql> create table student(
-> id int primary key,
-> name varchar(20)
-> );
示例:创建一个班级表 MySQL命令:
mysql> create table class(
-> classid int primary key,
-> studentid int
-> );
示例:学生表作为主表,班级表作为副表设置外键:
mysql> alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
运行效果展示:
mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
`classid` int(11) NOT NULL,
`studentid` int(11) DEFAULT NULL,
PRIMARY KEY (`classid`),
KEY `fk_class_studentid` (`studentid`),
CONSTRAINT `fk_class_studentid` FOREIGN KEY (`studentid`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.5.1 删除外键
语法如下:
alter table 从表名 drop foreign key 外键名;
示例:删除上述外键:
alter table class drop foreign key fk_class_studentid;
运行效果展示:
mysql> alter table class drop foreign key fk_class_studentid;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table class;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
`classid` int(11) NOT NULL,
`studentid` int(11) DEFAULT NULL,
PRIMARY KEY (`classid`),
KEY `fk_class_studentid` (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意:外键的那个字段不在了证明删除成功了
五、数据表插入数据
在MySQL通过INSERT语句向数据表中插入数据。在此,我们先准备一张学生表,代码如下:
mysql> create table student(
-> id int,
-> name varchar(30),
-> age int,
-> gender varchar(30)
-> );
5.1 为表中所有字段插入数据
每个字段与其值是严格一一对应的。也就是说:每个值、值的顺序、值的类型必须与对应的字段相匹配。但是,各字段也无须与其在表中定义的顺序一致,它们只要与 VALUES中值的顺序一致即可。语法如下:
insert into 表名 (字段名1,字段名2,...) values(值1,值2,...)
示例:向学生表中插入一条学生信息:
insert into student (id,name,age,gender) values (1,'bob',16,'male');
运行效果展示:
mysql> insert into student (id,name,age,gender) values (1,'bob',16,'male');
Query OK, 1 row affected (0.00 sec)
5.1.1 数据查询语句
基本语法:select + 关键字后指定要显示的内容 from 表名
select * from 表名
示例:查询上述添加的数据:
select * from student;
运行效果展示:
mysql> select * from student;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | bob | 16 | male |
+------+------+------+--------+
1 row in set (0.00 sec)
5.2 为表中指定字段插入数据
插入数据的方法基本和为表中所有字段插入数据一样,只是需要插入的字段由你自己指定。
5.3 同时插入多条记录
语法如下:
insert into 表名 [(字段名1,字段名2,...)] values (值 1,值 2,…),(值 1,值 2,…),...;
在该方式中:(字段名1,字段名2,…)是可选的,它用于指定插人的字段名;(值 1,值 2,…),(值 1,值 2,…)表示要插人的记录,该记录可有多条并且每条记录之间用逗号隔开。
示例:向学生表中插入多条学生信息:
insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');
运行效果展示:
mysql> insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
查询数据:
mysql> select * from student;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | bob | 16 | male |
| 2 | lucy | 17 | female |
| 3 | jack | 19 | male |
| 4 | tom | 18 | male |
+------+------+------+--------+
4 rows in set (0.00 sec)
六、更新数据
在MySQL通过UPDATE语句更新数据表中的数据。在此,我们将就用上述中的student学生表:
6.1 UPDATE基本语法
UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
在该语法中:字段名1、字段名2…用于指定要更新的字段名称;值1、值 2…用于表示字段的新数据;WHERE 条件表达式 是可选的,它用于指定更新数据需要满足的条件.
6.2 UPDATE更新部分数据
示例:将name为tom的记录的age设置为20并将其gender设置为female:
update student set age=20,gender='female' where name='tom';
运行效果展示:
mysql> update student set age=20,gender='female' where name='tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | bob | 16 | male |
| 2 | lucy | 17 | female |
| 3 | jack | 19 | male |
| 4 | tom | 20 | female |
+------+------+------+--------+
4 rows in set (0.00 sec)
6.3 UPDATE更新全部数据
示例:将所有记录的age设置为18:
update student set age=18;
运行效果展示:
mysql> update student set age=18;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from student;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | bob | 18 | male |
| 2 | lucy | 18 | female |
| 3 | jack | 18 | male |
| 4 | tom | 18 | female |
+------+------+------+--------+
4 rows in set (0.00 sec)
七、删除数据
7.1 DELETE基本语法
delete from 表名 [where 条件]
7.2 DELETE删除部分数据
示例:先准备一张数据表,代码如下
mysql> select * from stu;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | zcf | 21 |
| 2 | liu | 21 |
| 3 | wang | 21 |
| 5 | zhang | 21 |
+----+-------+------+
4 rows in set (0.00 sec)
删除id等于5的所有记录:
delete from stu where id=5;
运行效果展示:
mysql> delete from stu where id=5;
Query OK, 1 row affected (0.00 sec)
7.3 DELETE删除全部数据
示例:删除stu表中的所有记录:
delete from stu;
运行效果展示:
mysql> delete from stu;
Query OK, 3 rows affected (0.00 sec)
八、Mysql 数据表简单查询
8.1 简单查询概述
简单查询即不含where的select语句。在此,我们讲解简单查询中最常用的两种查询:查询所有字段和查询指定字段。
在此,先准备测试数据,
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 7 | zcf | 21 | male |
| 8 | liu | 22 | male |
| 9 | wang | 22 | male |
+----+------+------+--------+
3 rows in set (0.00 sec)
8.2 查询所有字段
基本语法:
select * from 表名;
示例:查询表stu所有字段:
select * from stu;
运行效果展示:
mysql> select * from stu;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 7 | zcf | 21 | male |
| 8 | liu | 22 | male |
| 9 | wang | 22 | male |
+----+------+------+--------+
3 rows in set (0.01 sec)
8.3 查询指定字段
基本语法:
select 指定字段 from 表名;
示例:查询指定字段(id、name):
select id,name from stu;
运行效果展示:
mysql> select id,name from stu;
+----+------+
| id | name |
+----+------+
| 7 | zcf |
| 8 | liu |
| 9 | wang |
+----+------+
3 rows in set (0.00 sec)
8.4 常数的查询
在SELECT中除了书写列名,还可以书写常数。可以用于标记:
常数的查询日期标记基本语法:
select * ,'年月日' from 表名;
示例:查询日期指定字段(id、name):
select id,name,'2022-8-30' from stu;
运行效果展示:
mysql> select id,name,'2022-8-30' from stu;
+----+------+-----------+
| id | name | 2022-8-30 |
+----+------+-----------+
| 7 | zcf | 2022-8-30 |
| 8 | liu | 2022-8-30 |
| 9 | wang | 2022-8-30 |
+----+------+-----------+
3 rows in set (0.00 sec)
8.5 从查询结果中过滤重复数据
基本语法:
select distinct 指定字段 from 表名;
示例:查询过滤stu中gender的命令:
select distinct gender from stu;
运行效果展示:
mysql> select distinct gender from stu;
+--------+
| gender |
+--------+
| male |
+--------+
1 row in set (0.00 sec)
九、Mysql 数据表条件查询
9.1 条件查询的基本语法
select * from 表名 where 条件列表;
9.2 条件列表
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ....and.... | 在某个范围之内(含最小值、最大值) |
in(...) | 在in 之后的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 是null |
逻辑运算符 | 功能 |
and 或 && | 并且(多个条件同时成立) |
or 或 || | 或者(多个条件任意一个成立) |
not 或 ! | 非 |
9.3 使用比较运算符查询
测试数据:
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | male |
| 1002 | zczz | 22 | male |
| 1003 | sss | 15 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
| 1006 | abbs | 14 | male |
+------+-------+------+--------+
示例:查询年龄等于或大于17的学生的信息
select * from student where age>=17;
运行效果展示:
mysql> select * from student where age >= 17;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1002 | zczz | 22 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
+------+-------+------+--------+
3 rows in set (0.00 sec)
9.4 使用IN关键字查询
IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中,则将字段所在的记录将査询出来。
示例:查询sid为1002和1003的学生信息
select * from student where sid in ('1002','1003');
运行效果展示:
mysql> select * from student where sid in('1002','1003');
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1002 | zczz | 22 | male |
| 1003 | sss | 15 | male |
+------+-------+------+--------+
2 rows in set (0.00 sec)
9.5 使用BETWEEN AND关键字查询
BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内,则将所在的记录将查询出来
示例:查询15到18岁的学生信息
select * from student where age between 15 and 18;
运行效果展示:
mysql> select * from student where age between 15 and 18;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | male |
| 1003 | sss | 15 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
+------+-------+------+--------+
4 rows in set (0.00 sec)
9.6 使用AND关键字查询
在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
示例:查询年纪大于15且性别为male的学生信息
select * from student where age > 15 and gender='male';
运行效果展示:
mysql> select * from student where age > 15 and gender='male';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1002 | zczz | 22 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
+------+-------+------+--------+
3 rows in set (0.00 sec)
9.6 使用OR关键字查询
在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时,只要记录满足其中任意一个条件就会被查询出来
示例:查询年纪大于15或者性别为male的学生信息
select * from student where age > 15 or gender='male';
运行效果展示:
mysql> select * from student where age > 15 or gender='male';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | boy |
| 1002 | zczz | 22 | male |
| 1003 | sss | 15 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
+------+-------+------+--------+
5 rows in set (0.00 sec)
9.7 使用LIKE关键字查询
MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配
9.7.1 普通字符串
示例:查询sname中与lili匹配的学生信息
select * from student where sname like 'lili';
运行效果展示:
mysql> select * from student where sname like 'lili';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | boy |
+------+-------+------+--------+
1 row in set (0.00 sec)
9.7.2 含有%通配的字符串
%用于匹配任意长度的字符串。例如,字符串“a%”匹配以字符a开始任意长度的字符串
示例:查询学生姓名以li开始的记录
select * from student where sname like 'li%';
运行效果展示:
mysql> select * from student where sname like 'li%';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | boy |
+------+-------+------+--------+
1 row in set (0.00 sec)
示例:查询学生姓名以g结尾的记录:
select * from student where sname like '%z';
运行效果展示:
mysql> select * from student where sname like '%z';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1002 | zczz | 22 | male |
+------+-------+------+--------+
1 row in set (0.00 sec)
示例:查询学生姓名包含s的记录
select * from student where sname like '%s%';
运行效果展示:
mysql> select * from student where sname like '%s%';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1003 | sss | 15 | male |
| 1004 | asss | 18 | male |
| 1005 | abss | 17 | male |
| 1006 | abbs | 14 | boy |
+------+-------+------+--------+
4 rows in set (0.00 sec)
9.7.3 含有'_'通配的字符串
下划线通配符只匹配单个字符,如果要匹配多个字符,需要连续使用多个下划线通配符。例如,字符串“ab_”匹配以字符串“ab”开始长度为3的字符串
示例:查询学生姓名以ab开头且长度为4的记录
select * from student where sname like 'ab__';
运行效果展示
mysql> select * from student where sname like 'ab__';
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1005 | abss | 17 | male |
| 1006 | abbs | 14 | boy |
+------+-------+------+--------+
2 rows in set (0.00 sec)
9.8 使用空值查询
在MySQL中,使用 IS NULL关键字判断字段的值是否为空值。请注意:空值NULL不同于0,也不同于空字符串
示例:查询sname不为空值的学生信息:
select * from student where sname is null;
运行效果展示:
mysql> select * from student where sname is null;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1007 | NULL | 20 | boy |
+------+-------+------+--------+
1 row in set (0.00 sec)
十、使用函数查询Mysql数据表
10.1 聚合函数
在开发中,我们常常有类似的需求: 统计某个字段的最大值,最小值, 平均值等等。为此MySQL中提供了聚合函数来实现这些功能。所谓聚合,就是将多行汇总成一行;其实,所有的聚合函数均如此输入多行,输出一行。聚合函数具有自动滤空的功能,若某一个值为NULL,那么会自动将其过滤使其不参与运算。
聚合函数使用规则:只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。
基本语法:
select 聚合函数(字段列) from 表名;
介绍:将一列数据作为一个整体,进行纵向计算。
常见聚合函数:
函数 | 功能 |
count | 统计计算 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
10.1.1 count( ) [统计计算函数]
统计表中数据的行数或者统计指定列其值不为NULL的数据个数
示例:查询student有多少人:
select count(*) from student ;
运行效果展示:
mysql> select count(*) from student ;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
10.1.2 max( )[最大值函数]
计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算
示例:查询student表中年龄最大的学生:
select max(age) from student;
运行效果展示:
mysql> select max(age) from student;
+----------+
| max(age) |
+----------+
| 22 |
+----------+
1 row in set (0.00 sec)
10.1.3 min( ) [最小值函数]
计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算
示例:查询student表中年龄最小的学生:
select min(age) from student;
运行效果展示:
mysql> select min(age) from student;
+----------+
| min(age) |
+----------+
| 14 |
+----------+
1 row in set (0.01 sec)
10.1.4 sum( ) [求和函数]
计算指定列的数值和,如果指定列类型不是数值类型则计算结果为0
示例:查询student中年龄总和:
select sum(age) from student;
运行效果展示:
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
| 122 |
+----------+
1 row in set (0.00 sec)
10.1.5 avg( ) [平均值函数]
计算指定列的平均值,如果指定列类型不是数值类型则计算结果为0
示例:查询student中年龄平均值:
select avg(age) from student;
运行效果展示:
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 17.4286 |
+----------+
1 row in set (0.00 sec)
10.2 其他函数
10.2.1 时间函数
select now();
select day(now());
10.2.2 字符串函数
连接函数:
select concat()
select instr();
统计长度:
select length();
10.2.3 数学函数
绝对值:
select abs(数值);
向下取整:
select floor(3.14);
向上取整:
select ceiling(3.14);
十一、mysql 数据表分组查询
11.1分组查询基本语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [分组后过滤条件]
11.1.1 where 与 having 区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段
11.2 使用group by进行分组查询
group by 子句可像切蛋糕一样将表中的数据进行分组,再进行查询等操作。换言之,可通俗地理解为:通过group by 将原来的表拆分成了几张小表。
示例:根据性别分组student表:
select gender from student group by gender;
运行效果展示:
mysql> select gender from student group by gender;
+--------+
| gender |
+--------+
| 女 |
| 男 |
+--------+
2 rows in set (0.00 sec)
11.3 group by和聚合函数一起使用
示例1:根据性别分组并统计男女数量student表:
select gender,count(*) from student group by gender;
运行效果展示:
mysql> select gender,count(*) from student group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 3 |
| 男 | 4 |
+--------+----------+
2 rows in set (0.00 sec)
示例2:统计部门编号大于1001的各部门员工个数
select sid,count(*) from student where sid > 1001 group by sid;
运行效果展示:
mysql> select sid,count(*) from student where sid > 1001 group by sid;
+------+----------+
| sid | count(*) |
+------+----------+
| 1002 | 1 |
| 1003 | 1 |
| 1004 | 1 |
| 1005 | 1 |
| 1006 | 1 |
| 1007 | 1 |
+------+----------+
6 rows in set (0.00 sec)
11.4 group by和聚合函数以及havin一起使用
示例:查询年龄小于22的,并根据性别分组,获取员工数量大于等于3的员工:
select gender,count(*) from student where age < 22 group by gender having count(*) > 3;
运行效果展示:
mysql> select gender,count(*) from student where age < 22 group by gender having count(*) > 3;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 4 |
+--------+----------+
1 row in set (0.00 sec)
11.5 使用order by 对查询结果进行排序
11.5.1 排序查询基本语法
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
11.5.2 基础的排序方式
asc | 升序(默认值) |
desc | 降序 |
示例1:查询所有学生并按照年纪大小升序排列
select * from student order by age asc;
运行效果展示:
mysql> select * from student order by age asc;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1006 | abbs | 14 | 女 |
| 1003 | sss | 15 | 男 |
| 1001 | lili | 16 | 男 |
| 1005 | abss | 17 | 男 |
| 1004 | asss | 18 | 女 |
| 1007 | NULL | 20 | 男 |
| 1002 | zczz | 22 | 女 |
+------+-------+------+--------+
7 rows in set (0.00 sec)
示例2:查询所有学生并按照年纪大小降序排列
select * from student order by age desc;
运行效果展示:
mysql> select * from student order by age desc;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1002 | zczz | 22 | 女 |
| 1007 | NULL | 20 | 男 |
| 1004 | asss | 18 | 女 |
| 1005 | abss | 17 | 男 |
| 1001 | lili | 16 | 男 |
| 1003 | sss | 15 | 男 |
| 1006 | abbs | 14 | 女 |
+------+-------+------+--------+
7 rows in set (0.00 sec)
11.6 使用LIMIT限制查询结果的数量
当执行查询数据时可能会返回很多条记录,而用户需要的数据可能只是其中的一条或者几条
11.6.1 基本语法
select 字段列表 from 表名 limit 起始索引,查询记录数;
注意:
- 起始索引从0开始,起始索引=(查询页码-1)* 每页显示记录数。
- 如果查询的是第一页数据,起始索引可以省略。
- 索引值不是页码值,索引值就是从第多少条开始记录选择的
示例1:查询学生表中年纪最小的3位同学
select * from student order by age asc limit 3;
效果展示:
mysql> select * from student order by age asc limit 3;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1006 | abbs | 14 | 女 |
| 1003 | sss | 15 | 男 |
| 1001 | lili | 16 | 男 |
+------+-------+------+--------+
3 rows in set (0.00 sec)
示例2:查询第一页学生数据,每页展示4条记录:
select * from student limit 0,4;
效果展示:
mysql> select * from student limit 0,4;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | 男 |
| 1002 | zczz | 22 | 女 |
| 1003 | sss | 15 | 男 |
| 1004 | asss | 18 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)
示例3:查询剩下的学生数据:
select * from student limit 4,3;
结果展示:
mysql> select * from student limit 4,4;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1005 | abss | 17 | 男 |
| 1006 | abbs | 14 | 女 |
| 1007 | NULL | 20 | 男 |
+------+-------+------+--------+
3 rows in set (0.00 sec)
11.7 查询的执行顺序
from > where > group by > select > order by > limit
十二、别名设置
在査询数据时可为表和字段取別名,该别名代替表和字段的原名参与查询操作
12.1 为表取别名
在查询操作时,假若表名很长使用起来就不太方便,此时可为表取一个別名,用该别名来代替表的名称。语法格式如下所示:
select * from 表名 [as] 表的别名;
示例:将student改为stu查询整表
select * from student as stu;
运行效果展示:
mysql> select * from student as stu;
+------+-------+------+--------+
| sid | sname | age | gender |
+------+-------+------+--------+
| 1001 | lili | 16 | 男 |
| 1002 | zczz | 22 | 女 |
| 1003 | sss | 15 | 男 |
| 1004 | asss | 18 | 女 |
| 1005 | abss | 17 | 男 |
| 1006 | abbs | 14 | 女 |
| 1007 | NULL | 20 | 男 |
+------+-------+------+--------+
7 rows in set (0.01 sec)
12.2 为字段取别名
在查询操作时,假若字段名很长使用起来就不太方便,此时可该字段取一个別名,用该别名来代替字段的名称。语法格式如下所示:
select 字段名1 [as] 别名1 , 字段名2 [as] 别名2 , ... from 表名where ... ;
示例:将student中的name取别名为“姓名” 查询整表
select name '姓名' from student ;
运行效果展示:
mysql> select sid,sname '姓名' from student ;
+------+--------+
| sid | 姓名 |
+------+--------+
| 1001 | lili |
| 1002 | zczz |
| 1003 | sss |
| 1004 | asss |
| 1005 | abss |
| 1006 | abbs |
| 1007 | NULL |
+------+--------+
7 rows in set (0.00 sec)
十三、Mysql的迁移与备份
Mysqldump命令方式和mysql命令行方式(create table .. Select )实现数据库迁移或备份。
13.1 复制数据库
13.1.1 复制数据库中所有的内容
mysqldump -u root -p --all-databases > db.sql
13.1.2 复制数据库中的某几个库
mysqldump -u root -p --databases 库名1 库名2 > db.sql
13.1.3 复制数据库中的某张表
mysqldump -u root -p 库名 表名1 表名2 > db_table.sql
13.1.4 导出数据不到出结构
mysqldump -t 库名 -u root -p > 文件名
示例:
[root@192 sql_bak]# mysqldump -t student -uroot -p > student.data
Enter password:
[root@192 sql_bak]# ll
total 4
-rw-r--r-- 1 root root 1503 Sep 21 10:51 student.data
13.1.5 导出结构不导出数据
mysqldump -d 库名 -u root -p > 文件名
示例:
[root@192 sql_bak]# mysqldump -d student -uroot -p > student1.data
Enter password:
[root@192 sql_bak]# ll
total 8
-rw-r--r-- 1 root root 1687 Sep 21 10:54 student1.data
-rw-r--r-- 1 root root 1503 Sep 21 10:51 student.data
13.2 还原数据库
13.2.1 还原数据库基本语法
mysql -u root -p -D 库名 < db.sql(先前备份的文件名)
注意:这个库名事先要存在。
如果不带数据库名则会报下方错误:
[root@192 sql_bak]# mysql -u root -p < student.db.sql
Enter password:
ERROR 1046 (3D000) at line 22: No database selected
如果数据库名不存在则会报下方错误:
[root@192 sql_bak]# mysql -u root -p -D student < student.db.sql
Enter password:
ERROR 1049 (42000): Unknown database 'student'
13.3 复制数据表
13.3.1 只复制数据表结构到新表(主键类型和自增方式是不会复制过去的)
create table 新表 select * from 旧表名
13.3.2 复制旧表的数据结构到新表(两个表结构一样)
insert into 新表 select * from 旧表
13.3.3 复制旧表的数据结构到新表(两个表结构不一样)
insert into 新表 (字段1, 字段2......) select 字段1, 字段2...... from 旧表
13.3.4 把旧表的所有字段类型都复制到新表
create table 新表名 like 库名.旧表名
十四、Mysql事务
14.1 Mysql事务概念
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。 事务用来管理instert,update,delete语句。
14.2 查看Mysql数据库使用的引擎
show engines;
示例:(innoDB是数据库默认引擎)
mysql> show engines;
+--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f oreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor ary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+
9 rows in set (0.00 sec)
14.3 Mysql常用的数据库引擎
14.4 Mysql表级锁和行级锁
表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程连读也不允许。
行级, 仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁( row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL主要的两种锁的特性可大致归纳如下:
表级锁: 开销小,加锁快;不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁);锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
考虑上述特点,表级锁适用于并发性不高,以查询为主,少量更新的应用,比如小型的web应用;而行级锁适用于高并发环境下,对事务完整性要求较高的系统,如在线事务处理系统。
14.5 MyIASM 和 Innodb引擎详解
Innodb引擎:
Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别。 该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。 但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。 由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
MyIASM引擎:
MyIASM是MySQL(5.5之前)默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。 不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。 如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。
14.5.1 两种引擎的选择
大尺寸的数据集趋向于选择InnoDB引擎,因为它支持事务处理和故障恢复。 数据库的大小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。 主键查询在InnoDB引擎下也会相当快,不过需要注意的是如果主键太长也会导致性能问题。
大批的INSERT语句(在每个INSERT语句中写入多行,批量插入)在MyISAM下会快一些,但是UPDATE语句在InnoDB下则会更快一些,尤其是在并发量大的时候。