MySQL快速入门及常用命令

数据库

笔记内容

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：

1. 插入：INSERT
2. 更新：UPDATE
3. 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。

mysql常用命令：

数据库相关语句

1. 进入mysql中的命令
   mysql -u root -p，密码最好设为123456

2. 展现当前所有数据库：
   show databases；

3. 创建数据库：
   create database mydb1；(数据库名字，最好设为中文)sql语句不区分大小写，但是自己写的时候可以用大小写区分，关键词都使用大写
   create database if not exists mydb2;(if not exists 表示在没有的情况下创建，有则不会发生改变，也不会报错)

4. 创建数据库，规定编码表：
   create database mybd2 character set UTF8; (在mysql中UTF-8应该写为 UTF8)

5. 创建数据库，校队规则，Collation是在一个字符集中进行如何排序的规则
   create datebase mybd3 character set UTF8 collate UTF8_general_ci 不区分大小写
   create datebase mybd4 character set UTF8 collate UTF8_bin;区分大小写

6. 安 顺序 和 逆序查看表中数据：
   select *from test1 order by name asc; 升序
   select *from test1 order by nasme desc;降序

7. 查看数据库，显示数据库创建语句：
   show databases; 查看所有的数据库
   show create database db_name; 显示数据库创建语句

8. 删除数据库
   drop database mybd1;

9. 修改数据库的编码表
   alter database mydb1 character set utf8;

10. 备份，恢复数据库

    备份：在cmd中 mysqldump -u 用户名 -p 数据库名>文件名.sql

    恢复：方式一：在mysql中 source 文件名.sql

    ​ 方式二：在cmd 中 mysql -u 用户名 p 数据库名 <文件名.sql

表的相关语句

1. 在操作表之前一定要先选择数据库
   use 数据库名；

2. 显示表：
   show tables; 显示全部的表
   desc 表名; 简单描述表结构
   describe 表名；简单描述表结构
   sho create table :查看生成表的 DDL 语句

3. 创建表：
   create table 表名( 字段名 字符类型，字段名 字符类型，)；
   和java 相反，数据类型写在名字后
   create table student(id varchar(20, age int);
   注意表的名字如果和关键字一样，要用``隔开（在1的旁边，不是单引号）
   create order(id int); 在之后的表操作中也一直要加上```

4. 修改表

   • 增加新列
     alter table 表名 add 字段名 字符类型(限定大小),add 字段名 字符类型(限定大小)
     alter table student add id int,add name varchar(100);
   • 修改类的字段类型
     alter table 表名 modify 字段名 字符类型（限定大小）;
     alter table student id varchar(100);
   • 删除某列
     alter table 表名 drop 字段；
     alter table student drop id;
   • 修改表名
     altet table 表名 to 新表名
     alter table stuff to user;
   • 修改表的字符集
     alter table 表名 character set 字符集；
     alter table student character set utf8;
   • 修改表某列的名称
     修改表的方式有两种
     Change既可以修改列的名称，也可以修改列的字段类型
     Modify只可以修改列的字段类型。
     举例：
     alter table student change name username varchar(50);
     alter table student modify name varchar（50）;
   • 修改表的默认值
     举例：修改了studnet中的name的默认值为0
     alter table studnet modify name varchar(50) default 0;
   • 删除表
     drop table 表名；

​

表内数据的操作

1. 新增数据（Insert语句）
   字符串和日期型数据应包含在单引号中。
   表名后没有带列名的需要把所有数据都填充，而且数据类型要和表结构一直，或者可以指定相应列名，单独赋值
   a. 表名后没有带列名的，把数据全部填充
   insert into 表名 values(数据);//有s啊！！s！！
   举例 ：在student表中新增一个学生数据，学生有 id 姓名，年纪，三个属性， 数据类型分别为 int varchar(100) int
   insert into student values(123,'赵元'，18);
   b. 指定了相应的类名，单独赋值,未填充的数据来自default
   insert into 表名(列明1，列名2） values(数据1，数据2);
   insert into student (id,name) values(123,'赵元');
   插入空值 insert into table values(null),没啥意思

   注意:插入单行的时候使用VALUES，在插入多行的时候使用VALUE 这样比较快一点

2. 修改数据（Update语句）

   • UPDATE语法可以用新值更新原有表行中的各列。
   • SET子句指示要修改哪些列和要给予哪些值。
   • WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
     a. 未限定范围，指定列的指都被改变
     update 表名 set 列名=数据；
     update student set age=18;
     b. 用where限定范围
     update 表名 set 列名=数据 where 限定范围。（where后跟的是一个逻辑表达式，即表达式的结果是布尔类型。）

3. 删除数据（Delete语句）
   delete from table_name     
   [WHERE where_definition]

• 如果不使用where子句，将删除表中所有数据。
• Delete语句不能删除某一列的值（可使用update）删除的单位是行(行)
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
• 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
  a. 删除某一行的值 delete from student where id =1; 将student中id为1 的这一行数据都删掉了
  b. 删除一个表内所有数据 delete from student ;

==删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同 ==
delete 和drop的区别:
delete from 表名：删除一个表内的元素
drop table 表明：删除一个表

数据查询语言--简单查询（DQL）

DQL：Data Query Language
作用：查询表中的数据。
关键字：
SELECT

1. 测试计算
   虽然 SELECT 语句通常用于从表中检索数据，但我们也可以用它计算表达式和函数的值。如：
   没啥意义
   SELECT 32; -- 计算表达式32的值
   SELECT NOW(); -- 查看当前的时间
   SELECT TRIM(' ab cd ‘); -- 修剪字符串' ab cd '左右两边的空白
   SELECT CONCAT('ab','cd’); -- 拼接字符串'ab'和'cd'
2. 查询单列
   SELECT column_name FROM table_name;
   举例：查询表 students 中学生的姓名。select name from student;
3. 查询多列
   SELECT col_name1, col_name2, … , col_namen
   FROM table_name;
   举例：我们查询 t_students 中学生的姓名和各科成绩。select chinese，math from student;
4. 查询所有列
   select *from table_name;
   举例：查询 t_students 表中的所有数据。select *from student;

• 注意：除非确实需要表中的所有数据，否则最好不要使用 * 通配符。因为，查询不必要的数据会降低查询和应用程序的效率。
• 提示：当我们不知道列的名称时，可以使用 * 通配符获取它们的数据。

5. 使用 WHERE 子句过滤记录.

   • SELECT * | {column_names}
   • FROM table_name
   • WHERE <filter_condition>; //filter_condition 是一个逻辑表达式，即表达式的结果是布尔类型。、

6. 去重 ：DISTINCT 过滤相同的记录
   SELECT DISTINCT {column_names}
   FROM table_name;
   注意：DISTINCT 操作的基本单位是行（记录），只有当两行数据一模一样的情况下，才会删除另一行。
   如：查询 t_students 表中的不同班级数据。
   select distinct id from student where calss；

7. 限制结果：SELECT 语句返回所有匹配的记录，指向显示一定数量的行数的时候需要用到

   • 方式1： LIMIT offset, nums; -- LIMIT 3, 4; //偏移量是3，显示4行数据

• 方式2： LIMIT nums OFFSET offset; //显示num行数据，偏移量是 offset
  注意：offset是指偏移量，当我们从第1行查起，偏移量自然为0。此时，可以写成 LIMIT nums;

  **扩展：**我们可以使用 LIMIT 关键字实现分页查询。 LIMIT (page_num – 1) * page_size, page_size;
  

8. 对查询结果进行排序：我们可以使用 ORDER BY 子句对查询结果进行排序。
   多列排序的时候，在最前面的优先级最强（首先按照第一列的要求进行排序，第一列排序完之后再细微调整第二列，也就是优先级由高到低的过程。）
   如： 根据语文成绩 顺序排列，数学成绩 逆序，英语成绩 顺序
   select *from student order by chinese asc, math desc,english asc;

9. 分组查询
   [GROUP BY {column_names}] [HAVING <filter_condition>]
   使用 HAVING 过滤分组
   举例：查询各班人数
   select count(id) as number from student
   举例： 查询人数大于2的班级

注意：group concat()

当使用group by a列语句之后，在单独检索除了a列以	外的其他字段都没有什么意义，这个时候相同分类的数据	已经合并了， 所以，如果想再取出全部的数据，则必须	使用group concat()函数

注意：在 MySQL 中，0 表示 false，非 0 表示 true，故：SELECT * FROM t_students WHERE 1; 也是合法的。

SELECT语句的执行顺序

(5) SELECT column_name, ... 
(1) FROM table_name, ...
(2) [WHERE ...] 
(3) [GROUP BY ...] 
(4) [HAVING ...] 
(6) [ORDER BY ...]; 
注意：从上到下是写sql语句时正确的语法结构，（）括号里面的数字代表该条sql语句在服务器内部解析的先后顺序。

数据类型：

注意：这里以MySQL为例，不同的DBMS的都支持数值类型，字符串类型以及日期类型，但他们的实现可能不一样。

整数

数据类型	占用字节	说明
TINYINT	1	很小的整数
SMALLINT	2	小的整数
MEDIUMINT	3	中等大小的整数
INT	4	普通大小的整数
BIGINT	8	大整数

浮点数类型和定点数类型

类型名称	占用字	说明
FLOAT(M,D)	4	单精度浮点数
DOUBLE(M,D)	8	双精度浮点数
DECIMAL(M,D)	M+2	定点数

其中 M 称为精度，表示总共的位数; 
D 称为标度，表示小数的位数。
DECIMAL 类型不同于 FLOAT & DOUBLE，DECIMAL 实际是以字符串存放	的，它的存储空间并不固定，而是由精度 M 决定的

时间的数据类型

类型名称	日期格式	占用字节
YEAR	YYYY (2018)	1
TIME	HH:MM:SS (10:20:00)	3
DATE	YYYY-MM-DD (2018-7-23)	3
DATETIME	YYYY-MM-DD HH:MM:SS	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4

ATETIME 和 TIMESTAMP 虽然显示的格式是一样的，但是它们有很大的区别：
DATETIME 的系统默认值是 NULL, 而 TIMESTAMP 的系统默认值是 当前时间 NOW();
DATETIME 存储的时间与时区无关，而 TIMESTAMP 与时区有关。

字符串类型

类型名称	占用字节	说明
CHAR(M)	M, 1 <= M <= 255	固定长度字符串
VARCHAR(M)	L+1, L <=M, 1 <=M <=255	变长字符串
TINYTEXT	L+1, L < 2^8	非常小的文本字符串
TEXT	L+2, L < 2^16	小的文本字符串
MEDIUMTEXT	L+3, L < 2^24	中等大小的文本字符串
LONGTEXT	L+4, L < 2^32	大的文本字符串
ENUM	1 或者 2个字节，取决于枚举的数目，最大 65535个	枚举类型
SET	1,2,3,4或8个字节	集合类型

ENUM 类型总有一个默认值，当ENUM 列声明为NULL，则默认值为NULL。如果 ENUM 列被声明为 NOT NULL，则其默认值为列表的第一个元素。

二进制类型

不怎么用
字符串类型存储的是非二进制字符串（字符字符串），
二进制类型存储的是二进制字符串（字节字符串）

常见运算符介绍：

基本和java 一样，与java 不同的有一下几个：

		IS NULL	是否为NULL
		<=>	安全的等于
		IN	是否在列表内
		LIKE	通配符匹配

IiKE 通配符规则：

not like
not like
‘%’ 匹配任何数目的字符，甚至包括零字符
‘_’ 只能匹配一个字符
例如： 查询student中姓名第一个字为‘赵’的学生
select *from tsudnet where name like ‘赵%’

别名

注意：在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。
提示：AS 关键字不仅能给列起别名，还可以给表起别名。在多表查询中，我们会这样使用它。
我们可以给 Chinese + math + english 起一个更简单直接一点的名字。
SELECT name, chinese + math + english AS total FROM t_students;

聚合函数

• COUNT()
  COUNT(*) 计算表中的总行数;

    Select count(*)|count(列名) from tablename
    [WHERE where_definition]
    COUNT(column_name) 计算指定列下的总行数，计算时将忽略值为NULL的行。
  

• SUM():返回指定列的所有值之和
  elect sum(列名)｛,sum(列名)…｝ from tablename
  [WHERE where_definition]
  计算时将忽略值为NULL的行;

• AVG():返回指定列的平均值
  计算时将忽略值为NULL的行;
  Select sum(列名)｛,sum(列名)…｝ from tablename
  [WHERE where_definition]

• MAX():返回指定列的最大值
  计算时将忽略值为NULL的行;
  Select max(列名)　from tablename
  [WHERE where_definition]

• MIN():返回指定列的最小值
  计算时将忽略值为NULL的行;
  Select max(列名)　from tablename
  [WHERE where_definition]

分组

使用 GROUP BY 关键字对数据进行分组。
[GROUP BY {column_names}] [HAVING<filter_condition>]
使用 HAVING 过滤分组
注意：使用分组后，数据会根据所指定的字段分组，但是不能把每个组内的数据完全显示
这时需要使用group_concat()函数，使全部的数据显示出来。

例如：

聚合函数和group by分组一起使用的时候，聚合函数会对每个一个组进行单独计算
举例：查询每个班的学生人数
select count(id) ,class as count from student group by class;
查询每个班人数大于2 的班级和人数：
select count(id) as number,class as count from student group by class having number>2;

Having 和where？？？

• where语句在分组之前，执行完where语句再去对数据进行分组

• having语句在分组之后，执行完分组之后，再对分组数据进行一个过滤筛选。

  例如：仅选出各科成绩都在90之上的学生，之后，对学生进行分组

数据完整性

数据完整性是为了保证插入到数据库中的数据是正确的，它防止了用户可能的输入错误。

数据完整性主要分为以下三类：

• 实体完整性 （唯一性）
  规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现。
• 域完整性：
  指数据库表的列（即字段）必须符合某种特定的数据类型或约束。比如NOT NULL。
• 参照完整性：
  保证一个表的外键和另一个表的主键对应。

定义表的约束

定义主键约束

• 定义主键约束
  primary key:不允许为空，不允许重复
  (可以区分两条记录的唯一性)

新建主键：
	方式一：新建表的时候创建
    		create table student (id int PRIMARY KEY);
			primary key就是设置主键的关键字
	方式二： 修改表创建主键
			alter table studnet MODIFY id int PRIMARY KEY;
			alter table studnet ADD id int PRIMARY KEY;
			MODIFY  字段 类型 PRIMARY KEY，设置为主键
			ADD 字段 类型 PRIMARY KEY 新增字段为主键

• 删除主键：
  alter table tablename drop primary key ;

• 定义主键自动增长
  auto_increment

     方式一：新建表的时候创建自增长
     create table student(id int PRIMARY KEY AUTO_INCREMENT);
     
     方式二: 修改表这设置主键自增长
     alter table student MODIFY id int PRIMARY KEY AUTO_INCREMENT;	 	 
  

• 定义唯一约束
  unique

• 定义非空约束
  not null

• 定义外键约束
  constraint constraint_FK_name foreign key(ordersid) references orders(id),

注意：实体完整性的自动维护

实体完整性中的自动维护，当删除自动维护添加的主键的时候，之后再添加元素，id也从所删的主键之后添加，不会连续，自己添加主键后，自动维护添加的主键是从最大的id开始自增。

定义唯一性约束 Unique

用在 例如用户注册的时候选择手机号或者邮箱
唯一性约束，可以插入多个null数据，但是其他的数据只能是唯一的

• 定义唯一性约束
  alter table student ADD email varchar(20) UNIQUE;
  alter table student MODIFY id VARCHAR(20) UNIQUE;
• 删除唯一性约束：
  alter table student DROP INDEX email；

Not NULL

两种方式：

• 第一种在新建表的时候，添加not null约束。
  create table student(id int not null);
• 第二种在新建完表之后，手动添加。
  alter table student id int not null;

外键约束

比如有两张表，一张学生选课表，另外一张课程表，学生选择的课程必须是课程表中出现的课程，这个时候可以用外键来约束。如果不是课程表内的课程，则无法插入。

新建外键：

• 方式一：在创建表的时候添加外键
  create table student(id int primary key auto_increment
  name varchar(20),
  cid int,
  CONSTARAINT student_course_FK foreign key (cid) REFERENCES course(id));
  create table course(
  id int PRIMARY KEY AUTO_INCREMENT,
  cname varchar(20));
• 方式二:修改表添加外键
  alter table student add CONSTRAINT student_course_FK foreign key(cid) references course(id);
  删除外键：
  alter table student drop foreign key student_course_FK;

外键的优劣：
关于外键的优劣，有两种观点，一种认为外键可以起到很好的约束作用，值得使用
另外一种观点，认为外键虽然可以起到约束，但是同时它给数据库系统带来的整体开销远远大于获得的益处。

多表设计：

一对多：在多的一方添加外键约束，有效地减少数据冗余，也就是减少数据重复
多对多：生成第三个表，存储她们的关系
一对一：可以将另外一张表的字段直接写入表中，很不常用

多表查询

• 连续查询
• 子查询
• 联合查询
• 报表查询

连续查询：

• 交叉连接（cross join）:不带on子句，返回连接表中所有数据行的笛卡儿积。
• 内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。
• 外连接：分为左外连接（left out join）、右外连接（right outer join）。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。
  Select *from TABLE1 join_type TABLE2 [on (join_condition)]
  [where (query_condition)]

交叉连接

返回所有结果的笛卡尔积。交叉连接基本没有使用的场景，但是交叉连接的结果作为其他连接查询的基础。
两种方式：（交叉连接查询table1 和table 2）

• 方式一：select * from table1,table2;
• 方式二：select * from table1 CROSS JOIN table2;

内连接

返回连接表中符合连接条件及查询条件的数据行。

• 显式内连接：使用inner join关键字，在on子句中设定连接条件
  SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;
  相当于：不需要再使用as 设置别名，别名可以直接跟在后面
  SELECT * FROM customer as c INNER JOIN orders as o
  ON c.id=o.customer_id;
• 隐式内连接：不包含inner join关键字和on关键字，在where子句中设定连接条件
  SELECT * FROM customer c,orders o WHERE c.id=o.customer_id;

外连接查询：

左连接查询和右连接查询没有太大区别

左连接查询：

• 使用left outer join关键字，在on子句中设定连接条件
  SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
  不仅包含符合c.id=o.customer_id连接条件的数据行，还包含左表中的其他数据行
• 带查询条件的左外连接查询，在where子句中设定查询条件
  SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;

右连接查询：

• 使用right outer join关键字，在on子句中设定连接条件
  SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;
  不仅包含符合c.id=o.customer_id连接条件的数据行，还包含orders右表中的其他数据行
• 带查询条件的右外连接查询，在where子句中设定查询条件
  SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;

子查询

• 子查询也叫嵌套查询，是指在where子句或from子句中又嵌入select查询语句（一般写在where字句）
• 练习：
  查询“郭靖”的所有订单信息
  SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name LIKE ‘%郭靖%');
  select * from customer c inner join orders o on c.id = o.customer_id and c.name like '%郭靖%';

联合查询 UNION

• 联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返并没有重复数据行的查询结果。联合查询使用union关键字
  SELECT * FROM orders WHERE price>100 UNION SELECT * FROM orders WHERE customer_id=1;

报表查询

• 报表查询对数据行进行分组统计，其语法格式为：
  select … from … [where…] [ group by … [having… ]] [ order by … ]
  -其中group by 子句指定按照哪些字段分组，having子句设定分组查询条件。