一、数值类型
1、常量
（1）字符串常量
ASCII字符串常量占一个字节
例如:'Hello Word'
Unicode字符串常量占两个字节
例如:N'Hello Word'
mysql> select 'Hello Word'; +------------+ | Hello Word | +------------+ | Hello Word | +------------+ 1 row in set (0.00 sec) mysql> select N'Hello Word'; +------------+ | Hello Word | +------------+ | Hello Word | +------------+ 1 row in set (0.01 sec)
（2）数值常量
整数常量：不带小数的十进制数
浮点数常量：使用小数点数的数值常量
例如：5.23、10.5E5(科学计数法)
mysql> select 3.14159; +---------+ | 3.14159 | +---------+ | 3.14159 | +---------+ 1 row in set (0.00 sec) mysql> select 100e-3; +--------+ | 100e-3 | +--------+ | 0.1 | +--------+
（3）十六进制常量
MySQL支持十六进制；一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符；其最前面有一个大写字母“X”或者小写字母“x”;0-9,A(a)-F(f);
例如：X'41'表示大写字母A；x'4D7953514C'表示字符串MySQL；
mysql> select X'41'; +-------+ | X'41' | +-------+ | A | +-------+ 1 row in set (0.00 sec)
（4）日期时间常量
用单引号将表示日期时间的字符串括起来构成；
日期型常量包括年、月、日，数据类型为DATE；例如：'2021-09-30';
时间型常量包括小时、分钟、秒、微秒，数据类型为TIME；例如：'13:47:00:00';
mysql> select '2021-09-30 13:56:00'; +---------------------+ | 2021-09-30 13:56:00 | +---------------------+ | 2021-09-30 13:56:00 | +---------------------+ 1 row in set (0.01 sec)
（5）位字段值
可以使用b'value'符号写位字段值；例如b'0' ,b'1';不同的数据库，展示的内容不一样；
mysql> select b'1'; +------+ | b'1' | +------+ | | +------+ 1 row in set (0.00 sec)
（6）布尔值
TRUE 1
FALSE 0
mysql> select TRUE; +------+ | TRUE | +------+ | 1 | +------+ 1 row in set (0.00 sec)
（7）NULL值
通常用来表示空值，无数据；但是并不等同于字符串类型的空字符串
mysql> select NULL; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.04 sec)
2、变量
用户可以在表达式中使用自己定义的变量，称为用户变量；
使用用户变量前必须先定义或者初始化；如果使用没有初始化的变量，它的值位NULL；
用户变量前加@用来区分列名；
系统变量前加两个@@；
mysql> set @a=1; Query OK, 0 rows affected (0.01 sec) mysql> select @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.00 sec)
3、运算符
（1）算数运算符
+(加)、-(减)、*(乘)、/(除)、%(取模)
mysql> select 7%3; +------+ | 7%3 | +------+ | 1 | +------+ 1 row in set (0.00 sec)
（2）位运算符
&(位与)、|(位或)、^(位异或)、~(位取反)、>>(位右移)、<<(位左移);
例如：二进制101转十进制：1*2**2+0*2**1+1*2**0=4+0+1=5
二进制101向左移1位：1010 转换为十进制为：10；原来的数乘以2；
二进制101向右移动1位：10；转换位十进制为：2；原来的数除以2取整；
mysql> select 5&1; +-----+ | 5&1 | +-----+ | 1 | +-----+ 解释： 5的二进制为101 1的二进制为001 1与0 结果为假0 0与0 结果为假0 1与1 结果为真1 所以结果为1；
mysql> select 5|1; +-----+ | 5|1 | +-----+ | 5 | +-----+ 解释： 5的二进制为101； 1的二进制位001； 1或0 结果为真1 0或0 结果假0 1或1 结果为真1 所以结果为5；
mysql> select 5 >> 1; +--------+ | 5 >> 1 | +--------+ | 2 | +--------+ 1 row in set (0.00 sec) mysql> select 5 << 1; +--------+ | 5 << 1 | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
（3）比较运算符
其结果为逻辑值真、假、NULL
=(等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)、<>/!=(不等于)、<=>(相等或等于空)
mysql> select 5 > 4; +-------+ | 5 > 4 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec)
（4）逻辑运算符
NOT或！ 逻辑非
AND或&& 逻辑与
OR或|| 逻辑或
XOR 逻辑异或
mysql> select 5 >4 OR 5>8; +-------------+ | 5 >4 OR 5>8 | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select 5 >4 AND 5>8; +--------------+ | 5 >4 AND 5>8 | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
4、表达式
表达式就是常量、变量、列名、复杂计算、运算符和函数的组合；
5、内置函数
ABS() 绝对值
SORT() 开根号
COUNT() 聚合
ASCII()
CHAR()
NOW() 获取当前时间
YEAR()
ENCODE()/ENCRYPT() 加密函数
IF())/IFNULL() 流程控制函数
FORMAT() 格式化函数
CAST() 类型转换函数
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-09-30 16:18:49 |
+---------------------+
1 row in set (0.00 sec)
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> select ASCII('A');
+------------+
| ASCII('A') |
+------------+
| 65 |
+------------+
1 row in set (0.00 sec)
mysql> select char(65);
+----------+
| char(65) |
+----------+
| A |
+----------+
1 row in set (0.00 sec)
mysql> select year(now()); #month(now()),day(now())
+-------------+
| year(now()) |
+-------------+
| 2021 |
+-------------+
1 row in set (0.00 sec)
mysql> select cast(X'41' as unsigned); #此时不再是字符a；而是字符65；
+-------------------------+
| cast(X'41' as unsigned) |
+-------------------------+
| 65 |
+-------------------------+
1 row in set (0.00 sec)
二、数据库定义
1、创建数据库
语法格式：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET charset_name
|[DEFAULT] COLLATE collation_name
说明：语句中"[]"内为可选项；
db_name:数据库名
IF NOT EXISTS:在建数据可前进行判断，只有该数据库目前不存在是才执行CREATE DATABASES操作；
DEFAULT:指定默认值；
CHARACTER SET:指定数据库字符集(Charset)
charset_name为字符集名称
COLLATE:指定字符集的校对规则
2、选择数据库
USE db_name;
3、修改数据库
语法格式：
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification [,alter_specification]
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
4、删除数据库
语法格式：
DROP DATABASE [IF EXISTS] db_name
db_name是要删除的数据库名；可以使用IF EXISTS子句以避免删除不存在的数据库时出现的MySQL错误信息；
5、查看数据库
SHOW [DATABASES | SCHEMAS]
三、表定义
数值类型：
整型 INT
浮点型 FLOAT、DOUBLE、DECIMAL
日期和时间类型：
日期类型 DATE(YYYY-MM-DD)、YEAR
日期时间类型 DATETIME、TIME
时间戳类型 TIMESTAMP
字符串类型：
固定长度类型 CHAR 最大长度255
可变长度类型 VARCHAR 最大长度65535
文本类型 TEXT
1、创建表
语法格式：
CREATE TABLE [IF NOT EXISTS] tbl_name(字段名1 数据类型 [列级完整性约束条件] [默认值] [,字段名2 数据类型 [列级完整性约束条件] [默认值]] [表级完整性约束])[ENGINE=引擎类型];
mysql> create table student (id int not null auto_increment, name varchar(10),age int); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key #自增列只能有1列，而且必须是key； mysql> create table student (id int not null auto_increment primary key, name varchar(10),age int); Query OK, 0 rows affected (0.01 sec) #id数据类型为int型，不允许为空，且为自增长，主键；
2、添加字段
ALTER TABLE tbl_name ADD [COLUMN] 新字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名]
3、修改字段
ALTER TABLE tbl_name CHANGE [COLUMN] 原字段 新字段名 数据类型
[约束条件]
ALTER TABLE tbl_name ALTER [COLUMN] 字段名 {SET | DROP} DEFAULT;
ALTER TABLE tbl_name MODIFY [COLUMN] 字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名]
4、删除字段
ALTER TABLE tbl_name DROP [COLUMN] 字段名；
5、重命名表
ALTER TABLE 原表名 RENAME [TO] 新表名
RENAME TABLE 原表名1 TO 新表名1 [,原表名2 TO 新表名2]......;
6、删除表
DROP TABLE [IF EXISTS] 表1[,表2]......
7、查看表
SHOW TABLES [{FROM | IN} db_name];
8、查看表结构
SHOW COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name];
DESC tbl_name;
四、索引定义
可以理解成目录，在查找时有目录查找速度会加快；
用途划分：
普通索引（INDEX） 类似于排序
唯一性索引（UNIQUE） 在某一个特定的列中不允许有重复值；
主键（PRIMARY KEY）
全文索引（FULLTEXT） 引擎MyIAM支持
聚簇索引 引擎InnoDB支持
列表索引
单列索引
组合索引
1、索引的创建
（1）方法
CREATE TABLE tbl_name[col_name data_type] [CONSTRAINT index_name]
[UNIQUE][INDEX|KEY][index_name](index_col_name[lengh])[ASC(升序)|DESC(降序)]
（2）方法(常用)
CREATE [UNIQUE] INDEX index_name ON tbl_name(col_name[(length)][ASC|DESC],...)
（3）方法
ALTER TABLE tbl_name ADD[UNIQUE|DULLTEXT][INDEX|KEY][index_name](col_name[length][ASC|DESC],...)
2、索引的查看
语法格式：
SHOW {INDEX|INDEXS|KEYS}{FROM|IN} tbl_name [{FROM|IN} db_name]
3、索引的删除
语法格式：
(1)DROP INDEX index_name ON tbl_name;
(2)ALTER TABLE tbl_name DROP INDEX index_name;
五、数据更新
1、插入数据
INSERT语法格式：
INSERT INTO tbl_name(column_list) VALUES (value_list1)[,(value_list2)],...
从数据查询中插入数据
INSERT INTO tbl_name1 (column_list1) SELECT (column_list2) FROM tbl_name2 WHERE (condition)
注意：column_list1与column_list2必须个数相同且数据类型一一对应；
使用INSERT...SET语句插入部分列值数据
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
使用INSERT...SELECT语句插入子查询数据
INSERT [INTO] tbl_name [(col_name,..)] SELECT ...
2、删除数据
语法格式：
DELETE FROM tbl_name [WHERE <condition>];
删除所有记录可以使用：
TRUNCATE [TABLE] tbl_name;
注意：TRUNCATE是删除整个表，重新建一个表，在删除全部数据时候效率高于DELETE,DELETE逐条删除；TRUNCATE无法恢复，AUTO_INCREMENT计数器重置；
3、修改数据
语法格式：
UPDATE tbl_name SET col_name1=expr1 [,col_name2=expr2 ...] [WHERE where_definition]
六、视图定义
视图是从一个或多个表(或视图)导出的表；例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同的；
视图是一张虚表；
视图一经定义以后，就可以像表一样被查询、修改、删除和更新；
1、创建视图
语法格式：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
mysql> select * from student1; +----+-------+------+-------+ | id | name | age | class | +----+-------+------+-------+ | 1 | jerry | 30 | NULL | | 2 | tom | 22 | NULL | | 3 | marry | 20 | NULL | | 4 | jack | 18 | NULL | | 5 | lilei | 30 | 1-1 | +----+-------+------+-------+ mysql> create view v_avg(avg) as select avg(age) from student1; mysql> create view v_age as select age from student1 where age>=20;
2、删除视图
语法格式：
DROP VIEW [IF EXISTS]
view_name [,view_name] ...
3、修改视图定义
语法格式：
ALTER VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
4、查看视图定义
语法格式：
SHOW CREATE VIEW view_name
七、数据查询
1、select语句
SELECT [ALL|DISTINCT(唯一,去除重复)|DISTINCTROW] select_expr,... [FROM table_reference [,table_reference]...] [WHERE where_definition] /*WHERE子句/
[GROUP BY {col_name|expr|position} [ASC|DESC],...[WITH ROLLUP]] /*GROUP BY子句*/
[HAVING where_definition] /*HAVING子句*/
[ORDER BY {col_name|expr|position} [ASC|DESC],...] /*ORDER BY子句*/
[LIMIT {[offset,] row_count|row_count OFFSET offset}] /*LIMIT子句*/
子句 说明 是否必须使用 SELECT 要返回的列或表达式 是 FROM 从中检索数据的表 仅在从表选择数据是使用 WHERE 行级过滤 否 GROUP BY 分组说明 仅在按组计算聚合时使用 HAVING 组级过滤 否 ORDER BY 输出排序顺序 否 LIMIT 要检索的顺序 否
2、列的选择与指定
（1）选择指定的列
SELECT list1,list2,... FROM table_name;
（2）选择所有列
SELECT * FROM table_name;
3、定义并使用列的别名
SELECT list1 AS alias_name1, list2 AS alias_name2,..., FROM table_name;
4、计算列值
函数名 说明 COUNT 求组中项数，返回int类型整数 MAX 求最大值 MIN 求最小值 SUM 求和 AVG 求平均 STD或STDDEV 返回给定表达式中所有值的标准值 VARIANCE 返回给定表达式中所有值的方差 GROUP_CONCAT 返回由属于一组的列值链接组合而成的结果 BIT_AND 逻辑或 BIR_OR 逻辑与 BIT_XOR 逻辑异或
mysql> select * from student; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | zhang | 18 | | 2 | wang | 20 | | 6 | li | 15 | | 7 | zhao | 13 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select count(age) from student where age >= 15; +------------+ | count(age) | +------------+ | 3 | +------------+ 1 row in set (0.00 sec) mysql> select sum(age) from student where age >= 15; +----------+ | sum(age) | +----------+ | 53 | +----------+ 1 row in set (0.01 sec) mysql> select avg(age) from student; +----------+ | avg(age) | +----------+ | 16.5000 | +----------+ 1 row in set (0.00 sec)
mysql> select * from student order by age asc; +----+-------+------+ | id | name | age | +----+-------+------+ | 7 | zhao | 13 | | 6 | li | 15 | | 1 | zhang | 18 | | 2 | wang | 20 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select * from student order by age desc; +----+-------+------+ | id | name | age | +----+-------+------+ | 2 | wang | 20 | | 1 | zhang | 18 | | 6 | li | 15 | | 7 | zhao | 13 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select * from student order by age desc limit 2; +----+-------+------+ | id | name | age | +----+-------+------+ | 2 | wang | 20 | | 1 | zhang | 18 | +----+-------+------+ 2 rows in set (0.00 sec)
mysql> select * from student; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | zhang | 18 | | 2 | wang | 20 | | 6 | li | 15 | | 7 | zhao | 13 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select sum(id*age) as 'id乘以年龄的总和' from student; #(1*18+2*20+6*15+7*13) +-------------------------+ | id乘以年龄的总和 | +-------------------------+ | 239 | +-------------------------+ 1 row in set (0.00 sec)
5、FROM子句多表连接查询
（1）内连接
FROM table1 [INNER] JOIN table2 ON 连接条件
等值连接
FROM table1, [table2] ... WHERE table1.字段 <比较运算符> table2.字段
（2）外连接
左连接
FROM table1 [LEFT] JOIN table2 ON 连接条件
右连接
FROM table1 [RIGHT] JOIN table2 ON 连接条件
mysql> select * from student; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | zhang | 18 | | 2 | wang | 20 | | 6 | li | 15 | | 7 | zhao | 13 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select * from student1; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | jerry | 30 | | 2 | tom | 22 | | 3 | marry | 20 | | 4 | jack | 18 | +----+-------+------+ 4 rows in set (0.00 sec) mysql> select * from student join student1 on student.age=student1.age; +----+-------+------+----+-------+------+ | id | name | age | id | name | age | +----+-------+------+----+-------+------+ | 2 | wang | 20 | 3 | marry | 20 | | 1 | zhang | 18 | 4 | jack | 18 | +----+-------+------+----+-------+------+ 2 rows in set (0.00 sec)
6、WHERE子句与查询条件
查询条件 操作符
比较 =, <>, !=, <, <=, >, >=, !, NOT+比较运算符
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL