MYSQL基础
1.DDL语句
数据库/表的创建、删除
--创建数据库
create database 数据库名 charset=utf8
--删除数据库
drop database test1
--创建表
CREATE TABLE tablename(
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,)
--删除表
DROP TABLE tablename
- column_name 是列的名字
- column_type 是列的数据类型
- constraints是这列的约束条件
修改表操作
--修改表类型
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRSTAFTER col_name]
--修改表emp的ename字段，将archar(10)改为varchar(20)
ALTER TABLE emp MODIFY ename varchar(20);
--增加表字段
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRSTAFTER col_name]
--在表emp中新增加字段age，类型为int(3)
ALTER TABLE emp ADD age int(3)
--字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition[FIRSTAFTER col_name]
--将age 改名为age1，同时修改字段类型为int(4)
ALTER TABLE emp CHANGE age age1 int(4)
--修改字段排列顺序
使用[FIRSTAFTER col_name]来实现
--将新增的字段birth date 加在ename之后
ALTER TABLE emp ADD birth DATE after ename
--更改表名
ALTER TABLE tablename RENAME [TO] new_tablename
DML语句
DML操作是指对数据库中记录的操作，主要包括表记录的插入、更新、删除和查询
--插入记录
INSERT INTO tablename (field1,field2...) VALUES(value1,value2...)(value1,value2...)
--也可以不指定字段名称，但是values后的顺序应该和字段的排列顺序一致
--更新记录
UPDATE tablename SET field1=value1,field2=value2... [WHERE CONDITION]
--删除记录
DELETE FROM tablename [WHERE CONDITION]
DELETE t1,t2... FROM t1,t2...[WHERE CONDITION]
--delete a,b from emp a, dept b where a.deptno=b.deptno and a.deptno=3;
查询语句
查询不重复的记录 ，可以使用distinct关键字来实现
排序和限制，可以使用ORDER BY关键字来实现
DESC表示按照字段进行降序排列，ASC则表示升序排列，默认升序
聚合
集合操作的语法：
SELECT [field1,field2...] fun_name
FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2,...fiedln]
[WITH ROLLUP]
[HAVING where_contition]
- func_name表示要做的聚合操作，也就是聚合函数，常用的有sum、count、max、min
- GROUP BY 关键字表示要进行分类聚合的字段
- WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总
- HAVING关键字表示对分类后的结果再进行条件的过滤
注意：having 和where的区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤
表连接
表连接分为内连接和外连接，主要区别是，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。
--内查询：
select ename,deptname from emp,dept where emp.deptname=dept.deptname;
--左连接
select ename,deptname from emp left join dept on emp.deptname=dept.deptname;
子查询：
当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。
用于子查询的关键字包括 in、not in、 =、!=、exists、not exists等。
记录联合
将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起，可以使用union 和union all 关键字来实现。
SELECT * FROM t1
UNIONUNION ALL
SELECT * FROM t2
UNION 和 UNION ALL的区别是UNION 会去重
Mysql支持的数据类型
数值类型
1.整数类型
--整数类型
TINYINT SMALLINT MEDIUMINT INT INTEGER BIGINT
--浮点数
FLOAT DOUBLE
--定点数类型
DEC(M,D) DECIMAL(M,D)
在整数类型中，MySQL支持在类型名称后面的小括号内制定显示宽度，例如int(5)表示当数值宽度在小于5为的时候在数字前面填满宽度，如果不显示制定宽度则默认为int(11). 一般配合zerofill使用。
mysql> alter table t1 modify id2 int zerofill;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------------+
| id1 | id2 |
+------+------------+
| 1 | 0000000001 |
| 1 | 1111111111 |
+------+------------+
2 rows in set (0.01 sec)
所有的整数类型都有一个可选属性UNSIGEND(无符号),如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值下限取0，上限取原值的2倍。如果一个列制定为zerofill，则Mysql自动为该列添加UNSIGEND属性。
mysql> create table t2(id tinyint, id2 tinyint UNSIGNED);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
| id2 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(127,127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(129,129);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t2 values(126,129);
Query OK, 1 row affected (0.01 sec)
整数类型还有一个属性：AUTO—INCREMENT。这个属性只能用与整数类型，一般从1开始，每行+1.
定义示例：
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(ID));
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL, PRIMARY UNIQUE(ID));
2.小数类型
MySQL分为两种：浮点数（float、double）和定点数（decimal）
浮点数和定点数都可以用类型名称后加“（M，D）”的方式来进行表示
- M表示一共有多少位数字（整数位加小树位）
- D表示位于小数点后面位数
浮点数后面跟"(M,D)"的用法是非标准用法。float和double在不制定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，而decimal在不制定精度时，默认的整数位为10，默认的小数位为0
3.日期时间类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001(s) | 2038年的某个时刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
如果需要经常插入或者更新日期为当前系统时间，通常使用TIMESTAMP来表示，TIMESTAMP值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽定固定为19个字符。如果想要获得数字值，应该在TIMESTAMP列加"+0"。
mysql> create table t3 (id1 timestamp);
mysql> desc t3;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into t3 values(null);
mysql> select * from t3;
+---------------------+
| id1 |
+---------------------+
| 2021-01-23 21:50:08 |
+---------------------+
--插入
mysql> insert into t3 values(20210123220410);
注：
- 一个表中只能给第一个TIMESTAMP字段设置默认值为系统日期。
- TIMESTAMP和时区相关。当插入日期时，会先转换为本地时间后存放；从而数据库里面取出时，也同样需要将日期转换为本地时区后显示。
4.字符串类型
| 字符串类型 | 字 节 | 描述及存储需求 |
|---|---|---|
| CHAR(M) | M | M为0~255之间的整数 |
| VARCHAR(M) | M为0~255之间的整数，值的长度+1个字节 | |
| TINYBLOB | 允许长度0~255之间的整数，值的长度+1个字节 | |
| BLOB | 允许长度0~65535之间的整数，值的长度+2个字节 | |
| MEDIUMBLOB | 允许长度0~167772150之间的整数，值的长度+3个字节 | |
| LONGBLOB | 允许长度0~4294967295之间的整数，值的长度+4个字节 | |
| TINYTEXT | 允许长度0~255之间的整数，值的长度+1个字节 | |
| TEXT | 允许长度0~65535之间的整数，值的长度+1个字节 | |
| MEDIUTEXT | 允许长度0~167772150之间的整数，值的长度+1个字节 | |
| LONGTEXT | 允许长度0~4294967295之间的整数，值的长度+1个字节 | |
| VARBINARY(M) | 允许长度0~M个字节的变长字节字符串，值的长度+1个字节 | |
| BINARY(M) | M | 允许长度0~M个字节的变长字节字符串 |
CHAR和VARCHAR都是字符串类型，它们的具体区别为：
长度大小区别：
CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间；VARCHAR(M)定义的列的长度为可变长，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节）。
存数据时的区别：
-
CHAR值存储时，如果字符数没有达到定义的位数，会在后面用空格补全，再存入数据库中。比如定义
CHAR(10)，那么不论存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充； -
VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。
取数据时的区别：
- 数据库取CHAR值时，尾部的空格会被删除；
- 数据库取VARCHAR值时，尾部的空格仍然保留。
总结：
CHAR定长，一般用于固定长度的表单提交数据存储 ；例如：身份证号，手机号，电话，密码等；而VARCHAR不定长。
从空间上考虑，VARCHAR更好，从效率上考虑，CHAR更好。
Mysql中的运算符
算术运算符
| 运算符 | 作用 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| /，DIV | 除法 |
| %，MOD | 取余 |
比较运算法
| 运算法 | 作用 |
|---|---|
| = | 等于 |
| != | 不等于 |
| <=> | NULL安全的等于(NULL-safe) |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 存在于指定范围 |
| IN | 存在于指定集合 |
| IS NULL | 为NULL |
| IS NOT NULL | 不为NULL |
| LIKE | 通配符匹配 |
| REGEXP或RLIKE | 正则表达匹配 |
mysql> select 1<>1,1<>1,null<>null;
+------+------+------------+
| 1<>1 | 1<>1 | null<>null |
+------+------+------------+
| 0 | 0 | NULL |
+------+------+------------+
1 row in set (0.00 sec)
mysql> select 1<=>1,1<=>1,null<=>null;
+-------+-------+-------------+
| 1<=>1 | 1<=>1 | null<=>null |
+-------+-------+-------------+
| 1 | 1 | 1 |
+-------+-------+-------------+
1 row in set (0.00 sec)
- “IN”运算符的使用格式为 "a IN (value1,value2)"， 当a的值存在于列表中时，则整个比较表达式返回的值为1，否则返回0.
mysql> select 1 in (1,2,3), 0 in (1,2);
+--------------+------------+
| 1 in (1,2,3) | 0 in (1,2) |
+--------------+------------+
| 1 | 0 |
+--------------+------------+
逻辑运算法
| 运算符 | 作用 |
|---|---|
| NOT 或 ! | 逻辑非 |
| AND 或 && | 逻辑与 |
| OR 或 || | 逻辑或 |
| XOR | 逻辑异或 |
"XOR"表示逻辑异或，当任意一个操作数为NULL时，返回值为NULL。对于非NULL的操作数，如果两个的逻辑真假值相异，则返回结果1，否则返回2.
mysql> select 1 xor 1, 0 xor 0, 1 xor 0, null xor 1;
+---------+---------+---------+------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | null xor 1 |
+---------+---------+---------+------------+
| 0 | 0 | 1 | NULL |
+---------+---------+---------+------------+