mysql入门操作
目录
一，MySQL简述
1，什么是MySQL
2，什么是SQL
3，SQL的分类
二，数据库的数据存储类型
1，数值类型
2，字符串类型
3，时间和日期类型
三，数据库的基本操作
1，DDL（对数据库，表的创建，删除修改等）
（1）数据库的创建，删除
（2）数据库表的创建，删除，修改，增添数据
2，数据库约束
主键约束（primary key）：
默认值约束（default）：
非空约束（not null）：
唯一约束（unique）：
外键约束(FOREIGN KEY (外键列名)REFERENCES 主表(参照列))：
检查约束：
3，复制表结构和数据：
四，SQL之DML
插入语句（insert into）
删除语句（delete from）
更新语句（update，replace）
查询语句(select)：
简单的sql查询
条件查询（where）
分组统计与筛选（group by having）：
查询结果排序（order by）：
分页（limit）：
多表关联查询：
五，SQL中的函数
聚合函数：
数值型函数：
字符串类型：
日期和时间函数：
流程控制函数：
一，MySQL简述
1，什么是MySQL
MySQL是一种开源的关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）进行数据管理，支持多用户、多线程及跨平台操作，具有高可靠性、高性能和易扩展性，广泛应用于Web应用、企业系统等领域，提供事务支持、ACID特性及多种存储引擎，是LAMP/LNMP等开发架构的核心组件之一。
2，什么是SQL
SQL（Structured Query Language，结构化查询语言）是一种专门用于管理关系型数据库的标准化编程语言，支持数据的定义、查询、操作和权限控制，通过简洁的语法（如SELECT、INSERT、UPDATE、DELETE等命令）实现数据库的创建、增删改查、事务管理及安全控制，是几乎所有关系型数据库（如MySQL、Oracle、SQL Server等）的核心交互接口。
3，SQL的分类
DDL：(Data Definition Language)：数据定义语言，定义对数据库对象(库、表、列、索引)的操作。 CREATE、DROP、ALTER、RENAME、 TRUNCATE等
DML：(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作。 INSERT、DELETE、UPDATE、SELECT等
DQL（Data Query Language）：数据查询语言SELECT将数据的查询单独说明
DCL：(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。 GRANT、REVOKE等
二，数据库的数据存储类型
主要分三大类：数值类型，字符串类型，时间和日期类型
1，数值类型
| 数据类型 | 描述 |
|---|---|
| TINYINT(size) | -128 到 127 常规。 0 到 255 无符号*。在括号中规定最 大位数。 |
| SMALLINT(size) | -32768 到 32767 常规。 0 到 65535 无符号*。在括号中 规定最大位数。 |
| MEDIUMINT(size) | -8388608 到 8388607 普通。 0 to 16777215 无符号*。在 括号中规定最大位数。 |
| INT(size) | -2147483648 到 2147483647 常规。 0 到 4294967295 无 符号*。在括号中规定最大位数。 |
| BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。 0 到18446744073709551615 无符号*。在括号中规定最大位 数。 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。 |
2，字符串类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的最大长度。最多 255 个字 符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值： ENUM('X','Y','Z') |
| SET | 与 ENUM 类似， SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
3，时间和日期类型
| 数据类型 | 描述 |
|---|---|
| DATE() | 日期。格式： YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | 日期和时间的组合。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是'1000-01-01 00:00:00' 到 '9999-12- 31 23:59:59' |
| TIMESTAMP() | 时间戳。 TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
| TIME() | 时间。格式： HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59' |
| YEAR() | 2 位或 4 位格式的年。 注释： 4 位格式所允许的值： 1901 到 2155。 2 位格式所允许 的值： 70 到69，表示从 1970 到 2069 |
三，数据库的基本操作
1，DDL（对数据库，表的创建，删除修改等）
（1）数据库的创建，删除
创建数据库 ：create database 数据库名称;
mysql> create database t_test1;创建数据库时设置字符编码：create database db_name default charset="utf8mb4";
mysql> create database school DEFAULT CHARACTER SET utf8mb4;查看创建的数据库信息：show create database 数据库名称
mysql> show create database t_test1;展示所有数据库：show databases；
使用或切换数据库：use 数据库名称;
mysql> use t_test1;
删除数据库：mysql> drop 数据库名称;
mysql> drop database t_test1;（2）数据库表的创建，删除，修改，增添数据
创建表：
create table [if not exists] t_name (
# 定义表结构
字段名称1 类型 [约束条件],
字段2 类型 [约束条件],
……
字段n 类型 [约束条件]
);mysql> create table t_user(
-> id int primary key,
-> name varchar(50),
-> age int ,
-> gender char(5),
-> address varchar(255),
-> tel char(11)
-> );
展示当前数据库的所有表格：show tables;
mysql> show tables;
+-------------------+
| Tables_in_t_test1 |
+-------------------+
| t_user |
+-------------------+
1 row in set (0.00 sec)展示表结构：desc 数据库表
mysql> desc t_user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)插入数据：insert Into 数据库表（字段1，字段2），value（值1，值2）;
mysql> insert into t_user(id,name,age,gender,address,tel) value(1,"张三","18","男","伍仙桥","10011001");插入多条数据：
mysql> insert into t_user(id,name,age,gender,address,tel) value(2,"李四","19","男","伍仙桥","10011002"),
-> (3,"王五","20","女","伍仙桥","10011003");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0简单查询数据库表的所有内容：select * from 数据库表
mysql> select * from t_user;
+----+--------+------+--------+-----------+----------+
| id | name | age | gender | address | tel |
+----+--------+------+--------+-----------+----------+
| 1 | 张三 | 18 | 男 | 伍仙桥 | 10011001 |
| 2 | 李四 | 19 | 男 | 伍仙桥 | 10011002 |
| 3 | 王五 | 20 | 女 | 伍仙桥 | 10011003 |
+----+--------+------+--------+-----------+----------+
3 rows in set (0.00 sec)修改表结构：alter table 数据表 动作（modify，add...）
修改列类型
ALTER TABLE 表名 MODIFY 列名 列类型; -- 注意存在值的情况，类型不一定能成功
增加列
ALTER TABLE 表名 ADD 列名 列类型;
删除列
ALTER TABLE 表名 DROP 列名;
列改名
ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
ALTER TABLE 表名 RENAME 新表名;
RENAME TABLE 表名 TO 新表名;
修改列类型：
mysql> alter table t_user modify id varchar(255);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t_user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | varchar(255) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)添加列：
mysql> alter table t_user add email varchar(255);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | varchar(255) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)删除列：
mysql> alter table t_user drop email;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | varchar(255) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)更改列名：
mysql> alter table t_user change id tid int;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t_user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| tid | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | char(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)更改表名：
mysql> alter table t_user rename t_user1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_t_test1 |
+-------------------+
| t_user1 |
+-------------------+
1 row in set (0.00 sec)2，数据库约束
创建数据库表的时候，字段名称 该字段类型 [约束 [,……] ]。
约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除。大部分数据库支持下面五类完整性约束:
存在如下一些数据库表的约束：
-
默认值约束
-
主键约束
-
非空约束
-
外键约束
-
唯一约束
-
检查约束 【MySQL8之前，不生效。MySQL8之后是支持检查约束】
主键约束（primary key）：
主键从功能上看相当于非空且唯一，一个表中只允许一个主键，主键是表中唯一确定一行数据的字段。一般建议主键采用“int类型”，一般建议由数据库自身维护这个字段的值。当建立主键约束时，MySQL为主键创建对应的索引——主键索引，主键约束名总为PRIMARY。
设置id为主键：id int primary key 后面的auto_increment代表自增
mysql> create table t_user2(
-> id int primary key auto_increment,
-> age int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user2;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| age | int | YES | | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)此时插入数据id值不能为空且重复，因为id是主键，是这给条数据的唯一标识。
mysql> insert into t_user2(age) value(18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user2;
+----+------+
| id | age |
+----+------+
| 1 | 18 |
+----+------+
1 row in set (0.00 sec)
再次插入id为1时报错：
mysql> insert into t_user2(id,age) value(1,18);
ERROR 1062 (23000): Duplicate entry '1' for key 't_user2.PRIMARY'当设置自动增长时，插入id为NULL不会报错，会自增：
mysql> insert into t_user2(id,age) value(null,18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user2;
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 18 |
+----+------+
2 rows in set (0.00 sec)默认值约束（default）：
给定的字段，设置默认值。
mysql> create table t_user2(
-> id int primary key auto_increment,
-> uname varchar(50),
-> age int default 18
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| uname | varchar(50) | YES | | NULL | |
| age | int | YES | | 18 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)插入数据时若没给指定的值，会在该字段添加默认值
mysql> insert into t_user2(uname) value("张三");
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user2;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 1 | 张三 | 18 |
+----+--------+------+非空约束（not null）：
不允许字段的值为空。
mysql> create table t_user2(
-> id int primary key auto_increment,
-> uname varchar(50) not null,
-> age int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| uname | varchar(50) | NO | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)设置uname为不允许null之后，插入数据必须要给uname值
mysql> insert into t_user2(uname) value("张三");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user2(age) value(18);
ERROR 1364 (HY000): Field 'uname' doesn't have a default value
mysql> select * from t_user2
-> ;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 1 | 张三 | NULL |
+----+--------+------+
1 row in set (0.00 sec)唯一约束（unique）：
唯一约束的作用，是保证该字段的值是唯一的，值不允许重复。
唯一性约束条件确保所在的字段或者字段组合不出现重复值
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引——唯一索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。
mysql> create table t_user2(
-> id int primary key auto_increment,
-> uname varchar(50),
-> age int not null,
-> email varchar(50) unique
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| uname | varchar(50) | YES | | NULL | |
| age | int | NO | | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)插入的数据在该字段中必须不同
mysql> insert into t_user2(uname,age,email) value("李四",20,"123@123.com");
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_user2(uname,age,email) value("王五",20,"123@123.com");
ERROR 1062 (23000): Duplicate entry '123@123.com' for key 't_user2.email'外键约束(FOREIGN KEY (外键列名)REFERENCES 主表(参照列))：
外键：指的是两张或者多张表之间关联关系的字段。
外键约束：是表的约束，是约束表在插入外键数据时能够正确的插入，即给a表某个字段设置外键关联b表某个字段，a表该字段的数据插入或者修改时必须是b表其对应字段的数据。
首先得先有b表：
mysql> create table t_class(
-> id int primary key auto_increment,
-> cname varchar(50)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t_class(cname) value("MYSQL");
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_class;
+----+-------+
| id | cname |
+----+-------+
| 1 | MYSQL |
+----+-------+
1 row in set (0.00 sec)再创建a表设置外键约束：
mysql> create table t_stu(
-> id int primary key auto_increment,
-> sname varchar(50),
-> c_id int,
-> foreign key (c_id) references t_class(id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc t_stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sname | varchar(50) | YES | | NULL | |
| c_id | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)只能插入t_class表中id字段含有的值
mysql> insert into t_stu(sname,c_id) value("张三",1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_stu(sname,c_id) value("李四",2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t_test1`.`t_stu`, CONSTRAINT `t_stu_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `t_class` (`id`))
mysql> select * from t_stu;
+----+--------+------+
| id | sname | c_id |
+----+--------+------+
| 1 | 张三 | 1 |
+----+--------+------+
1 row in set (0.00 sec)检查约束：
⚠️ 注意：检查约束在MySQL8之前是不生效的，当然也不报错。MySQL8之后，就支持了检查约束。
mysql> create table t_user2(
-> id int primary key auto_increment,
-> name varchar(50),
-> age int check(age>=18),
-> gender enum("男","女")
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user2;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)会检测插入的数据是否满足要求：
mysql> insert into t_user2(
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> insert into t_user2(name,age,gender) value("张三",18,"男");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user2(name,age,gender) value("李四",12,"男");
ERROR 3819 (HY000): Check constraint 't_user2_chk_1' is violated.
mysql> insert into t_user2(name,age,gender) value("王五",20,"动物");、
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> select * from t_user2;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 1 | 张三 | 18 | 男 |
+----+--------+------+--------+
1 row in set (0.00 sec)
3，复制表结构和数据：
复制一个表结构的实现方法有两种
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表
如果直接复制表结构，则会默认将约束也复制过来
如果复制表结构的同时，复制数据，则不会复制约束
复制表结构
mysql> select * from t_user2;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 1 | 张三 | 18 | 男 |
+----+--------+------+--------+
1 row in set (0.00 sec)
mysql> desc t_user2;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table t_user3 like t_user2;
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_user3;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from t_user3;
Empty set (0.00 sec)
mysql> insert into t_user3 select * from t_user2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t_user3;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 1 | 张三 | 18 | 男 |
+----+--------+------+--------+
1 row in set (0.00 sec)
mysql>没有复制表结构
mysql> create table t_user4 select * from t_user2;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t_user4;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| id | int | NO | | 0 | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from t_user4;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 1 | 张三 | 18 | 男 |
+----+--------+------+--------+
1 row in set (0.00 sec)四，SQL之DML
数据库使用时，大多数情况下，开发者只会操作数据，也是就增删改查（CRUD）。是核心，是任何一个IT人士必备技能。增删改查四条语句，最重要的是查询（DQL）。
有关数据表的DML操作INSERT INTO、DELETE、TRUNCATE、UPDATE、SELECT、条件查询、查询排序、聚合函数、分组查询
插入语句（insert into）
插入一条数据：insert into 数据表（字段一，字段二）value（值一，值二）
注意插入值时要注意对应的类型：字符类型插入值时要加引号，数值数据插入时，可以加引号也可以不加引号。
mysql> create table t_user1(
-> id int primary key auto_increment,
-> uname varchar(50),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t_user1(uname,age) value("张三",18);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 1 | 张三 | 18 |
+----+--------+------+
1 row in set (0.00 sec)插入多条数据
mysql> insert into t_user1(uname,age) values("李四",19),("王五","20");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 1 | 张三 | 18 |
| 2 | 李四 | 19 |
| 3 | 王五 | 20 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql>删除语句（delete from）
删除指定数据行delete from 数据表 where 条件：
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 1 | 张三 | 18 |
| 2 | 李四 | 19 |
| 3 | 王五 | 20 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> delete from t_user1 where id=1;
Query OK, 1 row affected (0.04 sec)
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 2 | 李四 | 19 |
| 3 | 王五 | 20 |
+----+--------+------+
2 rows in set (0.00 sec)删除所有数据（delete from 数据表）：
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 2 | 李四 | 19 |
| 3 | 王五 | 20 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> delete from t_user1;
Query OK, 2 rows affected (0.03 sec)
mysql> select * from t_user1;
Empty set (0.00 sec)更新语句（update，replace）
updata更新指定语句 update 数据表名 set 字段名=值1 where 条件：
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 张三 | 18 |
| 5 | 李四 | 19 |
| 6 | 王五 | 20 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> update t_user1 set uname="杨六" where age=18;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 19 |
| 6 | 王五 | 20 |
+----+--------+------+
3 rows in set (0.00 sec)update更新字段的所有数据 pdate 数据表名 set 字段名=值1：
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 19 |
| 6 | 王五 | 20 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> update t_user1 set age=18;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
+----+--------+------+
3 rows in set (0.00 sec)replace更新数据（用法更插入数据类似：replace into 数据表(字段1，字段2) value(值1，值2)）：
使用replace时是根据表中主键查找数据，如果存在数据则删除数据，再插入新数据，如果不存在主键，则新增加数据。
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> replace into t_user1(uname,age) value("王五",20);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 王五 | 20 |
+----+--------+------+
4 rows in set (0.00 sec)更新数据：先删除再插入
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 王五 | 20 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> replace into t_user1(id,uname,age) value(7,"小二",20);
Query OK, 2 rows affected (0.03 sec)
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 小二 | 20 |
+----+--------+------+
4 rows in set (0.00 sec)查询语句(select)：
简单的sql查询
#查询所有数据
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 小二 | 20 |
+----+--------+------+
4 rows in set (0.00 sec)
#查询指定字段
mysql> select uname,age from t_user1;
+--------+------+
| uname | age |
+--------+------+
| 杨六 | 18 |
| 李四 | 18 |
| 王五 | 18 |
| 小二 | 20 |
+--------+------+
4 rows in set (0.00 sec)条件查询（where）
条件判断
=判断两次的值是否相等
is判断空null
is not null来判断不为空
<=>可以判断null或者普通值
!=不等于
<>也是不等于逻辑运算符
and
or
not
#等值查询
mysql> select uname,age from t_user1 where age=20;
+--------+------+
| uname | age |
+--------+------+
| 小二 | 20 |
+--------+------+
1 row in set (0.00 sec)
#等值判断查询
mysql> select * from t_user1 where age<=20;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 小二 | 20 |
+----+--------+------+
4 rows in set (0.03 sec)
#NULL不能用=判断
mysql> select * from t_user1;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 4 | 杨六 | 18 |
| 5 | 李四 | 18 |
| 6 | 王五 | 18 |
| 7 | 小二 | 20 |
| 8 | 黄七 | 23 |
| 9 | 张三 | 44 |
| 10 | 唐九 | 37 |
| 11 | 帅哥 | NULL |
+----+--------+------+
8 rows in set (0.00 sec)
mysql> select * from t_user1 where age=NULL;
Empty set (0.03 sec)
mysql> select * from t_user1 where age is NULL;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 11 | 帅哥 | NULL |
+----+--------+------+
1 row in set (0.00 sec)
#逻辑算运算符
mysql> select * from t_user1 where age>=20 and age<=30;
+----+--------+------+
| id | uname | age |
+----+--------+------+
| 7 | 小二 | 20 |
| 8 | 黄七 | 23 |
+----+--------+------+
2 rows in set (0.00 sec)
#别名
mysql> select uname as "名字",age as "年龄" from t_user1 where age between 20 and 30;
+--------+--------+
| 名字 | 年龄 |
+--------+--------+
| 小二 | 20 |
| 黄七 | 23 |
+--------+--------+
2 rows in set (0.03 sec)分组统计与筛选（group by having）：
#分组
mysql> select gender as "性别" from t_user1 group by gender;
+--------+
| 性别 |
+--------+
| 女 |
| 男 |
+--------+
2 rows in set (0.00 sec)
#统计
mysql> select gender as "性别",count(*) as "人数" from t_user1 group by gender;
+--------+--------+
| 性别 | 人数 |
+--------+--------+
| 女 | 3 |
| 男 | 5 |
+--------+--------+
2 rows in set (0.00 sec)
#分组后的条件筛选
mysql> select gender as "性别",count(*) as "人数" from t_user1 group by gender having count(gender)>4;
+--------+--------+
| 性别 | 人数 |
+--------+--------+
| 男 | 5 |
+--------+--------+
1 row in set (0.00 sec)查询结果排序（order by）：
# 默认升序
mysql> select * from t_user1 order by age;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 11 | 帅哥 | NULL | 男 |
| 4 | 杨六 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
| 6 | 王五 | 18 | 女 |
| 7 | 小二 | 20 | 男 |
| 8 | 黄七 | 23 | 男 |
| 10 | 唐九 | 37 | 男 |
| 9 | 张三 | 44 | 男 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
# 设置降序
mysql> select * from t_user1 order by age desc;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 9 | 张三 | 44 | 男 |
| 10 | 唐九 | 37 | 男 |
| 8 | 黄七 | 23 | 男 |
| 7 | 小二 | 20 | 男 |
| 4 | 杨六 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
| 6 | 王五 | 18 | 女 |
| 11 | 帅哥 | NULL | 男 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
#设置升序
mysql> select * from t_user1 order by age asc;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 11 | 帅哥 | NULL | 男 |
| 4 | 杨六 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
| 6 | 王五 | 18 | 女 |
| 7 | 小二 | 20 | 男 |
| 8 | 黄七 | 23 | 男 |
| 10 | 唐九 | 37 | 男 |
| 9 | 张三 | 44 | 男 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
#存在多字段排序时，根据顺序依次排序
mysql> select * from t_user1 order by age asc,id desc;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 11 | 帅哥 | NULL | 男 |
| 6 | 王五 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
| 4 | 杨六 | 18 | 女 |
| 7 | 小二 | 20 | 男 |
| 8 | 黄七 | 23 | 男 |
| 10 | 唐九 | 37 | 男 |
| 9 | 张三 | 44 | 男 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
分页（limit）：
limit num # 查询多少条
limit num1, num2; # num1: 偏移量, num2 : 每页的数量
#显示前几条数据：
mysql> select * from t_user1;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 4 | 杨六 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
| 6 | 王五 | 18 | 女 |
| 7 | 小二 | 20 | 男 |
| 8 | 黄七 | 23 | 男 |
| 9 | 张三 | 44 | 男 |
| 10 | 唐九 | 37 | 男 |
| 11 | 帅哥 | NULL | 男 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
mysql> select * from t_user1 limit 2;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 4 | 杨六 | 18 | 女 |
| 5 | 李四 | 18 | 女 |
+----+--------+------+--------+
2 rows in set (0.00 sec)
#偏移前两条数据，显示后面4条数据：
mysql> select * from t_user1 limit 2,4;
+----+--------+------+--------+
| id | uname | age | gender |
+----+--------+------+--------+
| 6 | 王五 | 18 | 女 |
| 7 | 小二 | 20 | 男 |
| 8 | 黄七 | 23 | 男 |
| 9 | 张三 | 44 | 男 |
+----+--------+------+--------+
4 rows in set (0.00 sec)多表关联查询：
#先看两张表的结构
mysql> select * from t_stu;
+----+--------+------+
| id | sname | c_id |
+----+--------+------+
| 1 | 张三 | 1 |
| 3 | 李四 | 2 |
| 4 | 王五 | 3 |
| 5 | 老六 | 4 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> select * from t_class;
+----+-----------------------+
| id | cname |
+----+-----------------------+
| 1 | MYSQL |
| 2 | Python |
| 3 | JAVA |
| 4 | 计算机网络基础 |
+----+-----------------------+
4 rows in set (0.00 sec)
#直接查询两张表（引发笛卡尔积现象）
mysql> select * from t_stu,t_class;
+----+--------+------+----+-----------------------+
| id | sname | c_id | id | cname |
+----+--------+------+----+-----------------------+
| 5 | 老六 | 4 | 1 | MYSQL |
| 4 | 王五 | 3 | 1 | MYSQL |
| 3 | 李四 | 2 | 1 | MYSQL |
| 1 | 张三 | 1 | 1 | MYSQL |
| 5 | 老六 | 4 | 2 | Python |
| 4 | 王五 | 3 | 2 | Python |
| 3 | 李四 | 2 | 2 | Python |
| 1 | 张三 | 1 | 2 | Python |
| 5 | 老六 | 4 | 3 | JAVA |
| 4 | 王五 | 3 | 3 | JAVA |
| 3 | 李四 | 2 | 3 | JAVA |
| 1 | 张三 | 1 | 3 | JAVA |
| 5 | 老六 | 4 | 4 | 计算机网络基础 |
| 4 | 王五 | 3 | 4 | 计算机网络基础 |
| 3 | 李四 | 2 | 4 | 计算机网络基础 |
| 1 | 张三 | 1 | 4 | 计算机网络基础 |
+----+--------+------+----+-----------------------+
16 rows in set (0.00 sec)
#给上条件可以解决
mysql> select * from t_stu,t_class where t_stu.c_id = t_class.id;
+----+--------+------+----+-----------------------+
| id | sname | c_id | id | cname |
+----+--------+------+----+-----------------------+
| 1 | 张三 | 1 | 1 | MYSQL |
| 3 | 李四 | 2 | 2 | Python |
| 4 | 王五 | 3 | 3 | JAVA |
| 5 | 老六 | 4 | 4 | 计算机网络基础 |
+----+--------+------+----+-----------------------+
4 rows in set (0.00 sec)
#使用join（没有条件时引发笛卡尔积）
mysql> select * from t_stu join t_class;
+----+--------+------+----+-----------------------+
| id | sname | c_id | id | cname |
+----+--------+------+----+-----------------------+
| 5 | 老六 | 4 | 1 | MYSQL |
| 4 | 王五 | 3 | 1 | MYSQL |
| 3 | 李四 | 2 | 1 | MYSQL |
| 1 | 张三 | 1 | 1 | MYSQL |
| 5 | 老六 | 4 | 2 | Python |
| 4 | 王五 | 3 | 2 | Python |
| 3 | 李四 | 2 | 2 | Python |
| 1 | 张三 | 1 | 2 | Python |
| 5 | 老六 | 4 | 3 | JAVA |
| 4 | 王五 | 3 | 3 | JAVA |
| 3 | 李四 | 2 | 3 | JAVA |
| 1 | 张三 | 1 | 3 | JAVA |
| 5 | 老六 | 4 | 4 | 计算机网络基础 |
| 4 | 王五 | 3 | 4 | 计算机网络基础 |
| 3 | 李四 | 2 | 4 | 计算机网络基础 |
| 1 | 张三 | 1 | 4 | 计算机网络基础 |
+----+--------+------+----+-----------------------+
16 rows in set (0.00 sec)
#带上条件
mysql> select * from t_stu join t_class where t_stu.c_id = t_class.id;
+----+--------+------+----+-----------------------+
| id | sname | c_id | id | cname |
+----+--------+------+----+-----------------------+
| 1 | 张三 | 1 | 1 | MYSQL |
| 3 | 李四 | 2 | 2 | Python |
| 4 | 王五 | 3 | 3 | JAVA |
| 5 | 老六 | 4 | 4 | 计算机网络基础 |
+----+--------+------+----+-----------------------+
4 rows in set (0.00 sec)
五，SQL中的函数
聚合函数：
| 函数名称 | 作用 |
|---|---|
| MAX | 查询指定列的最大值 |
| MIN | 查询指定列的最小值 |
| COUNT | 统计查询结果的行数 |
| SUM | 求和，返回指定列的总和 |
| AVG | 求平均值，返回指定列数据的平均值 |
数值型函数：
| 函数名称 | 作用 |
|---|---|
| ABS | 求绝对值 |
| SQRT | 求平方根 |
| POW 和 POWER | 两个函数的功能相同，返回参数的幂次方 |
| MOD | 求余数 |
| CEIL 和 CEILING | 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
| FLOOR | 向下取整，返回值转化为一个BIGINT |
| RAND | 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列 |
| ROUND | 对所传参数进行四舍五入 |
| SIGN | 返回参数的符号 |
字符串类型：
| 函数名称 | 作用 |
|---|---|
| LENGTH | 计算字符串长度函数，返回字符串的字节长度 |
| CHAR_LENGTH | 计算字符串长度函数，返回字符串的字节长度，注意两者的区别 |
| CONCAT | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| INSERT(str,pos,len,newstr) | 替换字符串函数 |
| LOWER | 将字符串中的字母转换为小写 |
| UPPER | 将字符串中的字母转换为大写 |
| LEFT(str,len) | 从左侧字截取符串，返回字符串左边的若干个字符 |
| RIGHT | 从右侧字截取符串，返回字符串右边的若干个字符 |
| TRIM | 删除字符串左右两侧的空格 |
| REPLACE(s，s1，s2) | 字符串替换函数，返回替换后的新字符串 |
| SUBSTRING(s，n，len) | 截取字符串，返回从指定位置开始的指定长度的字符换 |
| REVERSE | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
| STRCMP(expr1,expr2) | 比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反 |
| LOCATE(substr,str [,pos]) | 返回第一次出现子串的位置 |
| INSTR(str,substr) | 返回第一次出现子串的位置 |
日期和时间函数：
| 函数名称 | 作用 |
|---|---|
| CURDATE() CURRENT_DATE() CURRENT_DATE | 两个函数作用相同，返回当前系统的日期值 |
| CURTIME() CURRENT_TIME() CURRENT_TIME | 两个函数作用相同，返回当前系统的时间值 |
| NOW | 返回当前系统的日期和时间值 |
| SYSDATE | 返回当前系统的日期和时间值 |
| DATE | 获取指定日期时间的日期部分 |
| TIME | 获取指定日期时间的时间部分 |
| MONTH | 获取指定日期中的月份 |
| MONTHNAME | 获取指定曰期对应的月份的英文名称 |
| DAYNAME | 获取指定曰期对应的星期几的英文名称 |
| YEAR | 获取年份，返回值范围是 1970〜2069 |
| DAYOFWEEK | 获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1 |
| WEEK | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1 〜53 |
| DAYOFYEAR | 获取指定曰期是一年中的第几天，返回值范围是1~366 |
| DAYOFMONTH 和 DAY | 两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31 |
| DATEDIFF(expr1,expr2) | 返回两个日期之间的相差天数，如 SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); |
| SEC_TO_TIME | 将秒数转换为时间，与TIME_TO_SEC 互为反函数 |
| TIME_TO_SEC | 将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒 |
流程控制函数：
| 函数名称 | 作用 |
|---|---|
| IF(expr,v1,v2) | 判断，流程控制，当expr = true时返回 v1，当expr = false、null 、 0时返回v2 |
| IFNULL(v1,v2) | 判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2 |
| CASE | 搜索语句 |