MySQL快速入门及常用命令

数据库

笔记内容

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

1. 数据查询语言DQL

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

2 .数据操纵语言DML

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

插入：INSERT
更新：UPDATE
删除：DELETE

3. 数据定义语言DDL

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

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

4. 数据控制语言DCL

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

mysql常用命令：

数据库相关语句

进入mysql中的命令
mysql -u root -p，密码最好设为123456
展现当前所有数据库：
show databases；
创建数据库：
create database mydb1；(数据库名字，最好设为中文)sql语句不区分大小写，但是自己写的时候可以用大小写区分，关键词都使用大写
create database if not exists mydb2;(if not exists 表示在没有的情况下创建，有则不会发生改变，也不会报错)
创建数据库，规定编码表：
create database mybd2 character set UTF8; (在mysql中UTF-8应该写为 UTF8)
创建数据库，校队规则，Collation是在一个字符集中进行如何排序的规则
create datebase mybd3 character set UTF8 collate UTF8_general_ci 不区分大小写
create datebase mybd4 character set UTF8 collate UTF8_bin;区分大小写
安顺序和逆序查看表中数据：
select *from test1 order by name asc; 升序
select *from test1 order by nasme desc;降序
查看数据库，显示数据库创建语句：
show databases; 查看所有的数据库
show create database db_name; 显示数据库创建语句
删除数据库
drop database mybd1;
修改数据库的编码表
alter database mydb1 character set utf8;
备份，恢复数据库

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

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

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

表的相关语句

在操作表之前一定要先选择数据库
use 数据库名；
显示表：
show tables; 显示全部的表
desc 表名; 简单描述表结构
describe 表名；简单描述表结构
sho create table :查看生成表的 DDL 语句
创建表：
create table 表名( 字段名字符类型，字段名字符类型，)；
和java 相反，数据类型写在名字后
create table student(id varchar(20, age int);
注意表的名字如果和关键字一样，要用``隔开（在1的旁边，不是单引号）
create order(id int); 在之后的表操作中也一直要加上```
修改表
- 增加新列
  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 表名；

表内数据的操作

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

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

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

使用 WHERE 子句过滤记录.
- SELECT * | {column_names}
- FROM table_name
- WHERE <filter_condition>; //filter_condition 是一个逻辑表达式，即表达式的结果是布尔类型。、
去重：DISTINCT 过滤相同的记录
SELECT DISTINCT {column_names}
FROM table_name;
注意：DISTINCT 操作的基本单位是行（记录），只有当两行数据一模一样的情况下，才会删除另一行。
如：查询 t_students 表中的不同班级数据。
select distinct id from student where calss；
限制结果：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;
```

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