MySQL基础与DDL、DML、DQL、约束详细命令

一、MySQL服务器启动与关闭

1.手动打开服务窗口
2.打开win+r输入cmd，运行指令services.msc 打开服务窗口
3.使用管理员打开cmd

• net start mysql:启动mysql服务器
• net stop mysql:关闭mysql服务器

二、SQL分类

1.DDL (Data Definition Language)数据定义语言
用来定义数据库对象。
2.DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。
3.DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。
4.DCL(Data Control Language)数据控制语言
用来定义数据库的访问权限和安全级别，及创建用户。

三、MySQL语法规范

1、不区分大小写，但建议关键字大写，表名、列名小写
2、每条命名最好用分号结尾
3、每套命令根据需要，可以进行缩进或换行
4、注释：
     单行注释：#注释文字（MySQL特有）
     单行注释:-- 注释文字(注意-- 后有个空格)
     多行注释:/**/

四、DDL操作

1.操作数据库

1.1创建数据库

(1)直接创建数据库
CREATE DATABASE 数据库名称；
(2)先判断是否存在，再创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名称
(3)创建数据库并指定字符集
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

1.2查询数据库名称

(1)查询所有数据库名称
SHOW DATABASES;
(2)先查询某个数据库的字符集：创建语句
SHOW CREATE DATABASE 数据库名称;

1.3修改数据库字符集

ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

1.4删除数据库

(1)删除数据库
DROP DATABASE 数据库名称;
(2)先判断是否存在，再删除
DROP DATABASE IF EXISTS 数据库名称;

1.5使用数据库

(1)查询当前正在使用的数据库名称
SELECT DATABASE();
(2)使用数据库
USE 数据库名称;

2.操作表

2.1创建表

CREATE TABLE 表名(
     列名1 数据类型1，
     列名2 数据类型2，
     列名3 数据类型3
     …
     列名n 数据类型n
);
注：最后一列最后面不需要加逗号

数据类型：
1.整数类型：TINYINT、SMALLINT、MEDIUMINT、INT（常用）和 BIGINT
2.浮点数类型：FLOAT、DOUBLE
3.定点：DECIMAL(M,D) M表示的是数据的长 度，D表示的是小数点后的长度
4.字符串类型：定长CHAR(M) 和 变长VARCHAR(M)
5.文本类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
6.日期时间类型：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP
7.二进制类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

2.2查询表

(1)查询数据库中所有表名称
SHOW TABLES;
(2)查询表结构
DESC 表名称;

2.3修改表

(1)修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
(2)修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称;
(3)添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
(4)修改列名称 类型
名称：ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
类型：ALTER TABLE 表名 MODIFY 列名 新数据类型;
(5)删除列
ALTER TABLE 表名 DROP 列名;

2.4删除表

(1)删除表，数据无法找回(速度最快)
DROP TABLE 表名;
(2)删除表中数据，不删除表的结构(不能用where)
TRUNCATE TABLE 表名;
(3)删除表中数据，不删除表的结构(可以用where，不加条件默认删除所有数据)
DELETE FROM 表名

五、DML操作

1.添加数据

INSERT INTO 表名(列名1…列名n) VALUES(值1…值n);
注：

1. 列名与值名要一一对应
2. 如果不写列名，默认给所有列添加值
   INSERT INTO 表名 VALUES(值1…值n)
3. 除了数字类型，其他类型需要用单引号或双引号引起来

2.删除数据

DELETE FROM 表名 WHERE 条件
注：

1. 如果没有条件会删除表中所有数据
2. 如果要删除所有数据：
   delete from 表名；–效率低(原理为有多少条数据就会执行多少次删除操作)
   truncate table 表名；–效率高(原理为删除表，再创建一个一模一样的表)

3.修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2…列名n=值名n (WHERE 条件);
注：

1. 如果不加条件，则表中所有数据都会被修改

六、DQL操作

查询表内所有数据： select * from 表名

1.语法

SELECT
     字段列表
FROM
     表名列表
WHERE
     条件列表
GROUP BY
     分组字段
HAVING
     分组之后的条件
ORDER BY
     排序
LIMIT
     分页限定

2.基础查询

(1)多个字段查询
SELECT 字段1，字段2…字段n FROM 表名;
注：
- 如果查询所有的字段，可以用*代替字段列表
(2)去除重复
DISTINCT
(3)计算列
一般可以用四则运算符进行计算
如果有NULL参与运算，则结果只为NULL，所以可以用IFNULL(可能出NULL的字段,出现NULL的替换值)
(4)别名
原本名称 AS 别名
也可以不写AS
例：原本名称 别名

3.字符串函数查询

SELECT 字符串函数 FROM 表名

函数	说明
LOWER(str)，UPPER(str)	前者将字符串str中的所有字母变成小写，后者将字符串str中的所有字母变成大写
CONCAT(s1,s2，…)	返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
CONCAT_WS(x,s1,s2,…)	返回多个字符串拼接之后的字符串，每个字符串之间有一个x
SUBSTRING(s,n,len)、MID(s,n,len)	两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串
LEFT(s,n)、RIGHT(s,n)	前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符
LTRIM(str)，RTRIM(str)，TRIM(str)	分别去掉字符串str左边，右边，两边的空格
INSERT(s1,x,len,s2)	返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
REPLACE(s,s1,s2)	返回一个字符串，用字符串s2替代字符串s中所有的字符串s1
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)	三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）
FIELD(s,s1,s2,…)	返回第一个与字符串s匹配的字符串的位置

4.条件查询

WHERE 条件

作用	符号
大于	>
小于	<
大于等于	>=
小于等于	<=
等于	=(注意只有一个等号，并不是编程语言中的==)
不等于	<> 或 !=
在…与…之间	between…and…
在集合中	in
模糊查询	like ‘%’
查询某列为null的值	is null

5.排序查询

语法：OEDER BY 排序字段1 排序方法1,排序字段2 排序方法2…
排序方式：
ASC：升序
DESC：降序
注：
如果有多个排序条件，则当第一个条件相同时，才会判断第二个

6.聚合函数

以整列进行纵向计算，计算时不会进行null值计算，所以应注意排除null值

1. count：计数
2. max：获取最大值
3. min：获取最小值
4. sum：求和
5. avg：求平均值

7.分组查询

语法：GROUP BY 分组字段；
注：

1. 分组后查询的字段：分组字段，聚合函数
2. where与having区别：
   1. where在分组前限定，如果不满足条件，则不参与分组，而having在分组后限定，不满足则不会被查询出来
   2. where后不能跟聚合函数，having可以

8.分页查询

语法：LIMIT 开始索引，每页查询条数
开始索引=(当前页数-1) * 每页条数
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,10;

七、约束

用来对表中数据进行限定

1.非空约束：not null

(1)创建时添加
CREATE TABLE text(
     num INT,
     name VARCHAR(20) NOT NULL
);
(2)已有表时添加
ALTER TABLE text MODIFY name VARCHAR(20) NOT NULL;
(3)删除非空约束
ALTER TABLE text MODIFY name VARCHAR(20);

2.唯一约束：unique

唯一约束允许存在null值，但只能有一个
(1)创建时添加
CREATE TABLE text(
    num INT,
     name VARCHAR(20) UNIQUE
);
(2)已有表时添加
ALTER TABLE text MODIFY name VARCHAR(20) UNIQUE;
(3)删除唯一约束
ALTER TABLE text DROP INDEX name;

3.主键约束：primary key

主键约束意思为该字段的值非空且唯一，一张表只能有一个字段为主键(可以理解为表中数据的唯一标识)
(1)创建时添加
CREATE TABLE text(
     num INT PRIMARY KEY,
     name VARCHAR(20)
);
(2)已有表时添加
ALTER TABLE text MODIFY num INT PRIMARY KEY;
(3)删除主键
ALTER TABLE text DROP PRIMARY KEY;

4.外键约束：foreign key

用于让表与表之间产生关联
(1)创建时添加
CREATE TABLE text(
     num INT,
     name VARCHAR(20),
    work INT - -外键列
    constrain 外键名 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名)
);
(2)已有表时添加
ALTER TABLE text ADD constrain 外键名 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名);
(3)删除外键
ALTER TABLE text DROP FOREIGN KEY;

5.级联操作

级联操作的意思就是在操作一个对象的同时，对相关的对象也执行某些操作。
当有了外键约束的时候，无法直接修改被约束的键，必须先修改或删除副表中的所有关联数据，才能修改或删除主表。而使用级联操作，可以直接实现修改。
语法：
ALTER TABLE 表名 ADD constrain 外键名 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名) ON UPDATE CASCADE(或 ON DELETE CASCADE);
级联更新：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE

6.自动增长

如果某一列是数值类型，可以使用AUTO_INCREMENT可以实现自动增长
(1)对有主键约束的实现自动增长
CREATE TABLE text(
     num INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20)
);
(2)删除自动增长
ALTER TABLE text MODIFY id INT ;
(3)添加自动增长
ALTER TABLE text MODIFY id INT AUTO_INCREMENT;

7.添加默认值

DEFAULT (默认值) 定义是指表中添加新行时，给表中某一列指定的默认值
(1)创建时定义默认值
CREATE TABLE <表名> (
     <列名1> 数据类型（长度） DEFAULT 默认值
     <列名1> 数据类型（长度） DEFAULT 默认值
… );
(2)给列添加默认值
ALTER TABLE <表名1>ADD CONSTRAINT <约束名> DEFAULT (默认值) FOR <字段名>