JavaWeb：MySQL——基础语法

这篇我写的断断续续的，有的插在中间的是后面补充的，如果有暂时看不懂的地方先往后跳。

mysql官网文档

https://dev.mysql.com/doc/

使用mysql（win+R->cmd）

cmd，一定要是管理员身份运行

然后 net start MySQL80

关闭就是net stop MySQL80

已经下载安装配置好的直接输入下面这个语句——链接mysql数据库，然后输入密码就行。
mysql -u root -p
可以参考这篇博客

以后要修改密码：
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘your-password’;

基础概念准备：

定义

1. 数据库：存储数据的仓库，英文名DataBase，简称DB。
2. 数据库管理系统：是管理数据库的大型软件，我们后面要用的MySQL就是数据库管理系统，但是通常简称数据库管理系统为数据库。
3. SQL：结构化查询语言，是操作数据库的一门编程语言。

SQL的通用语法

1. 以分号为指令的结尾。
2. 不区分大小写。
3. 注释：多行用/* … */，单行用#。

SQL分类

操作数据库以及数据库中的表：

• DDL（Data Definition Language）数据定义语言，用来操作数据库，表，列等。

操作表中的数据：

• DML（Manipulation) 数据操作语言，对数据库中的表的数据进行增删改。
• DQL （Query）数据查询语言。

权限控制：

• DCL（Control）数据控制语言，定义数据库的访问权限，安全等级以及创建用户。

DDL

（后面涉及到的指令小写也可以，数据库不区分大小写）

操作库

1. 查询：SHOW DATABASES;
2. 创建数据库：CREATE DATABASE 数据库名称;
3. 判断，如果不存在创建数据库：CREATE DATABASE IF NOT EXISTS 数据库名称;
4. 删除数据库：DROP DATABSE 数据库名称;
5. 判断，如果存在删除数据库：DROP DATABASE IF EXISTS 数据库名称;
6. 查看当前使用的数据库：SELECT DATABASE();
7. 使用数据库: USE 数据库名称;（这个也不加DATABASE[S]）
8. 查询数据库在本地的目录

show variables like "%datadir%";

操作表

查询表

• 查询当前数据库下所有表的名称： SHOW TABLES;
• 查询表的结构： DESC 表的名称；（这个也不加TABLE[S]）

创建表

• CREATE TABLE 表的名称 ( ——1. 注意是括号
  字段名1 数据类型1，
  字段名2 数据类型2，
  …
  字段n 数据类型n ——2.结尾没有逗号
  ); ——3.注意分号

举个例子

create table tb_user(
id int,
username varchar(20),
password varchar(32)
);

varchar(20)表示username的数据类型是字符数不超过20的字符串。
创建结果，创建一个name为tb_user的表

id	username	password

关于数据库的数据类型

这里稍微说一下定长和变长：定长就是根据定义的字符串长度分配空间，变长就是根据实际的字符串的长度分配空间，但是两个的共同特点都是一旦实际的字符串超过定义的字符串长度就会报错。

删除表：

• 删除表：DROP TABLE 表的名称;
• 判断，如果存在删除表： DROP TABLE IF EXISTS 表的名称;

修改表：

• 修改表名：ALTER TABLE 表名 RENAME TO 新的表名;
• 添加一列：ALTER TABLE 表名 ADD 列名 数据类型;

连续添加多列

ALTER TABLE Stu ADD (E_MAIL VARCHAR(20),PHOTO VARCHAR(100));

• 修改数据类型：ALTER TABLE 表名 MODIFY 列名 新数据类型;
• 修改列名和数据类型：ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
• 删除列：ALTER TABLE 表名 DROP 列名;

拷贝表

有两种方法：用as语句或者like语句，这一小段截取自这篇博客，感谢分享！

（注意1，2两种as语句的复制差别，一个有数据内容，一个无）

-- 1.使用AS复制相同的表结构，这个有复制内容
 CREATE TABLE table_name AS SELECT * FROM other_table; 
-- 2. 使用AS复制相同的表结构 （无内容）
 CREATE TABLE table_name AS SELECT * FROM other_table WHERE 1=2;(或者LIMIT 0)
-- 3.使用LIKE复制相同的表结构 
CREATE TABLE table_name LIKE other_table;

表结构已有，插入数据 / 复制数据 (如果指定列复制要标注类型相同）

--复制全部数据 注意：复制全部数据 列的类型要相同 
INSERT INTO table_name SELECT * FROM other_name; 
--或者复制指定的列 
INSERT INTO test_index_tbl1 ( 
 test_index_tbl1.`id`,
 test_index_tbl1.`name`, 
 test_index_tbl1.`author`,
 test_index_tbl1.`info` 
) SELECT id, NAME, author, info 
FROM 
test_index_tbl;

as和like的区别：

AS:用来创建相同表结构并复制源表数据(可根据后面的条件来控制要不要复制源表数据)。创建出来的table_name 缺少源表的索引信息，只有表结构相同，没有索引。

LIKE：用来创建完整表结构和全部索引。创建出来的新表包含源表的完整结构和索引信息。

查看创建表的语句

SHOW CREATE TABLE table_name ;

从这边开始的查询语句设计指定表的时候都不加TABLE，直接表名就行

DML

操作数据（增删改）
增加数据

• 给指定列添加数据：INSERT INTO 表名(列名1，列名2…) VALUE(值1，值2…);
• 给全部列添加数据：INSERT INTO 表名 VALUE(值1，值2…);
• 批量添加数据（根据指定的列一行一行进行添加）：INSERT INTO 表名(列名1，列名2…) VALUES(值1，值2…),(值1，值2…)…;

INSERT INTO STU (NAME) VALUES ('李四'),('王五');
SELECT * FROM STU;

name	score
李四	Null
王五	Null

• 批量添加全部列数据：INSERT INTO 表名 VALUES(值1，值2…),(值1，值2…)…;

批量添加全部列数据就得写出所有列数据的值，否则就会这样：

正确方法应该是：

INSERT INTO STU VALUES ('李四',28.5),('王五',33.7);
SELECT * FROM STU;

name	score
李四	28.5
王五	33.7

INSERT INTO STU (NAME,SCORE) VALUES ('李四',28.5),('王五',33.7);
SELECT * FROM STU;

和这段代码效果一样。

注意（值1，值2…）中间有逗号

修改数据

• UPDATE 表名 SET 列名1=值1,列名2=值2, … [WHERE 条件];

UPDATE STU SET NAME='mio';
SELECT * FROM STU;

name列全部数据都变成mio，如果不加 WHERE 条件 默认修改该列全部数据。

UPDATE STU SET NAME='gaga' WHERE SCORE=33.7;
SELECT * FROM STU;

分数为33.7的成员NMAE变为‘gaga’。

删除数据

• DELETE FROM 表名 [WHERE 条件];

DELETE FROM STU WHERE NAME='mio';
SELECT * FROM STU;

所有NAME属性为’mio‘的成员全部被删除，如果不加 WHERE 条件 所有数据被删除。

DQL

操作数据（查询）

补充一下：select … [from 表名]，括起来的地方其实不是一定要存在的，如果不需要从表中引用数据就不需要from。

基础查询

• 查询表中指定列的数据：SELECT 字段列表 FROM 表名;

SELECT name,sex FROM stu;

查询stu表中列名为name，sex的两列数据

• 查询表的所有列数据：SELECT * FROM 表名;
• 去除重复记录：SELECT DISTINCT 字段列表 FROM 表名;

只是展示的数据中不包含重复数据，对原表中数据没有影响。

• 起别名：AS 别名

SELECT name as 姓名,sex,score as 分数, club FROM stu;

姓名	sex	分数	club

为了使展示的数据更清晰，可以用as+别名将展示的表中的列名用别名替换（同样不影响表中数据）。不写as也是可以的，但是别名和原列名中间需要有空格。

条件查询

• 按照条件查询表中指定列的数据：SELECT 字段列表 FROM 表名 [WHERE 条件];

条件：
1.同时满足多个条件可以用&&，但是SQL推荐使用AND，如果是一个数据范围内（INT型，DATE型…）也可以用BETWEEN 数据开始 AND 数据结尾。

2.多个条件满足一个即可也就是或，可以用||，推荐使用OR，也可以是IN(选择条件1，选择条件2，选择条件3…)

3.NULL值的比较不能用=，而是IS NULL或者IS NOT NULL。

4.模糊查询LIKE + ‘模糊条件’

5.IN的补集是NOT IN，BETWEEN的补集是NOT BETWEEN
Qq

1.查询第一个字是马的名字
SELECT * FROM stu WHERE name LIKE '马%'; 
2.查询第二个字是花的名字
SELECT * FROM stu WHERE name LIKE '_花%'; 
3.查询包含德字的名字
SELECT * FROM stu WHERE name LIKE '%德%'; 

_表示单个字符，%表示任意个字符，可以是0个。

排序查询

• 按照条件进行排序：SELECT 字段列表(展示的列）FROM 表名 ORDER BY 排序字段名1 [排序方式1]，排序字段名2 [排序方式2]…;

排序方式：

• ASC：升序排序（默认）
• DESC：降序排序

当有多个排序条件，当前面的条件值一样的时候才会根据后面的条件进行排序。

两个效果一样，按年龄升序排序
SELECT * FROM stu ORDER BY age;
SELECT * FROM stu ORDER BY age ASC; 
按年龄降序排序
SELECT * FROM stu ORDER BY age DESC; 

聚合函数:SELECT 聚合函数名(列名) FROM stu ;

• 统计数量：SELECT COUNT(列名) FROM stu ;
  （建议列名用*）
• 最大值：SELECT MAX(列名) FROM stu ;
• 最小值：SELECT MIN(列名) FROM stu ;
• 求和：SELECT SUM(列名) FROM stu ;
• 平均值：SELECT AVG(列名) FROM stu ;

NULL不参与所有的聚合函数运算。

分组查询

• SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]

筛查的效果就包含在字段列表，比如根据性别分组求数学平均分

1.
SELECT AVG(math) FROM stu GROUP BY sex;
为了方便男，女数学平均分对应查看，建议这样写
SELECT sex,AVG(math) FROM stu GROUP BY sex;
2.查询男，对数学平均分及对应的人数
SELECT sex，AVG(math),COUNT(*) FROM stu GROUP BY sex;
3.分数低于70不参与分组
SELECT sex，AVG(math),COUNT(*) FROM stu WHERE math>70 GROUP BY sex;
4.分组后保留个数大于2的数据,会把3结果下COUNT(*)小于等于2的筛掉
SELECT sex，AVG(math),COUNT(*) FROM stu WHERE math>70 GROUP BY sex HAVING COUNT(*)>2;

WHERE和HAVING的区别：

• WHERE是分组之前进行限定，不满足WHERE条件的不参与分组，HAVING是分组后对结果进行过滤。
• WHERE不能对聚合函数进行判断，HAVING可以，因为执行WHERE的时候还没有执行聚合函数。

执行顺序：WHERE>聚合函数>HAVING

分页查询

• SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数;

起始索引的计算=（当前页数-1）*查询条目数

不同数据库语言的分页不太一样，MySQL的是LIMIT