MySQL和数据库总结

第01章-WEB技术-MySQL基础语法

启动MySQL

服务模式

# 命令安装服务
mysqld -install
# 移除服务
mysqld -remove
# 启动该服务
net start MySQL
# 停止服务
net stop MySQL

修改密码

# 连接数据库
mysql -u root -p
# 修改数据
ALTER USER 'root'@'localhost' IDENTIFIED  BY '你的新密码';

# 或者：SQL工具进行远程连接的密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的新密码';

# 刷新
flush privileges;

数据库操作

创建一个名字叫 mydb 的数据库

CREATE DATABASE mydb;
 当再次执行时会报错，数据库 mydb 已经存在
 Can't create database 'mydb';database exists

显示数据库

SHOW DATABASES [LIKE '数据库名'];

删除数据库

DROP DATABASE mydb;

使用(进入）数据库

USE mydb;

表操作

显示表结构

DESC table_name;

修改表

#创建表
CREATE TABLE mytb (
    id INT,
    name CHAR(5)
);
#删除字段
ALTER TABLE mytb DROP id; 
#增加字段
ALTER TABLE mytb ADD ids INT;  
#添加一个字段在最前面
ALTER TABLE mytb ADD id INT FIRST; 
#增加一个字段在 name 后
ALTER TABLE mytb ADD sex INT AFTER name; 
#修改 name 字段的长度为 10
ALTER TABLE mytb MODIFY name CHAR(10);
#修改 ids 字段的属性为 long 不为空默认值为 100
ALTER TABLE mytb MODIFY ids BIGINT NOT NULL DEFAULT 100;

修改表名为mytable

ALTER TABLE mytb RENAME TO mytable;

删除表mytable

DROP TABLE mytable

删除表有drop，truncate，delete三种操作，它们的区别如下：

drop:删除表数据和表结构

DROP TABLE user;

truncate:保留表结构，删除数据，释放空间

TRUNCATE TABLE user;

delete:保留表结构，删除数据，释放空间

DELETE FROM user;

三种的执行速度，一般来说：drop>truncate>delete

释放空间可以体现在：

1.通过 delete 删除的行数据是不释放空间的，如果表 id 是递增式的话，那么表数据的 id 就可能不是连续的；而通过 truncate 删除数据是释放空间的，如果表 id 是递增式的话，新增数据的 id 又是从头开始，而不是在已删数据的最大 id 值上递增。

2.使用 delete 删除数据时，mysql 并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql 会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间

复制表

复制表结构

CREATE TABLE `copy_user` LIKE `user`;

复制结构和数据，将查询出来的结果创建成新的表

CREATE TABLE IF NOT EXISTS `tablename` [AS] SELECT * FROM …;

只复制表结构：

CREATE TABLE `tablename` SELECT * FROM `tablename2` WHERE 1=0;

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

CREATE TEMPORARY TABLE IF NOT EXISTS `tablename`
(
    列1 数据类型(数据大小) 约束,
    列2 数据类型(数据大小) 约束,
    列3 数据类型(数据大小) 约束,
    ....
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

临时表还可以使用“#”开头来表示

常用的数据类型

数据类型

常用数据类型	类型	描 述
bit	整型	bit 数据类型是整型，其值只能是0、1或空值。
bigint	整型	介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。这种数据类型在数据库里占用8个字节
int	整型	int 数据类型可以存储从- 231(-2147483648)到231 (2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节
smallint	整型	smallint 数据类型可以存储从- 215(-32768)到215(32767)之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库里占用2 字节空间
tinyint	整型	tinyint 数据类型能存储从0到255 之间的整数。它在你只打算存储有限数目的数值时很有用。 这种数据类型在数据库中占用1 个字节
numeric	精确数值	numeric数据类型与decimal 型相同
decimal	精确数值	decimal 数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。 范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数
money	货币型	money 数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据，精确到货币单位的万分之一
smallmoney	货币型	smallmoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648 到214748.3647 之间的数据，精确到货币单位的万分之一
float	近似数值	float 数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308 之间的任意数
real	近似数值	real 数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.40E+38到3.40E+38之间的浮点数
datetime	日期时间	datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月3 1日间所有的日期和时间数据， 精确到三百分之一秒或3.33毫秒
Smalldatetime	日期时间	smalldatetime 数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟
cursor	游标	cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用
timestamp	时间戳	timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”， 但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的
Uniqueidentifier	特殊数据型	Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列
char	字符型	char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符
varchar	字符型	varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。 它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度
text	字符型	text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符
nchar	字符型	nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍
nvarchar	字符型	nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍
ntext	字符型	ntext 数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储230 -1或将近10亿个字符，且使用的字节空间增加了一倍
binary	二进制	binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时，你应该使用这种数据类型
varbinary	二进制	varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时，你应该使用这种数据类型
image	二进制	image 数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节

约束注意

!注意
1.在DECIMAL(P,D) 的语法中：
P 是表示有效数字数的精度。
P 范围为 1〜65。
D 是表示小数点后的位数。 
D 的范围是 0~30。MySQL 要求 D 小于或等于(<=)P。
 如：DECIMAL(6,2)中最多可以存储 6 位数字，小数位数为 2 位；因此，列的范围是从-9999.99 到 9999.99。
    
2.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，
    	比如 CHAR(30) 就可以存储 30 个字符。
 char 是一种定长字符串。它一旦声明后无论存储的值是否达到该长度，都占有相应的字节长度。
 varchar 是一种变长字符串，它的长度由存储的内容决定，为存满的时以实际存储内容为主。
 
3.BLOB 是一个二进制大对象，可以容纳可变数量的二进制数据。
   有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
    
4.TEXT 同样也有 4 种类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
   它们的区别也是可存储的最大长度不同。具体使用哪种类型可根据实际情况选择。

约束

约束用于规定表中的数据规则

在 SQL 中，有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须唯一，不能有相同的值。
• UNIQUE KEY - 必须唯一键，不能有相同的值。如：UNION KEY (id,name)
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或多个列的结合）有唯一标识。如：PRIMARY KEY (id,name)
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件**（MySql无效）**。如：CHECK (id>0)
• DEFAULT - 规定没有给列赋值时的默认值。如：DEFAULT b'1'
• UNSIGNED - 无符号整数

AUTO_INCREMENT - 自增

COMMENT - 表示注释

表的增删改查

插入数据

INSERT INTO 语句可以有两种编写形式。

第1种

INSERT INTO `tablename` 
VALUE
(value1,value2,value3,...);

第2种

INSERT INTO `tablename` 
VALUES 
(value1,value2,value3,...),
(value1,value2,value3,...);

第3种

INSERT INTO `tablename` (column1,column2,column3,...) 
VALUES 
(value1,value2,value3,...),
(value1,value2,value3,...);

如：

INSERT  INTO `users`(`id`,`nickname`,`name`,`pswd`,`sex`,`birthday`,`register`,`status`) 
VALUES 
(1,'小三','张三','123456',0,'0000-00-00 00:00:00','0000-00-00 00:00:00',0),
(2,'小四','李四','123456',0,'0000-00-00 00:00:00','0000-00-00 00:00:00',0),
(3,'小五','王五','123456',1,'0000-00-00 00:00:00','0000-00-00 00:00:00',0);

从查询结果插入数据

INSERT INTO IF EXISTS `tablename` [AS] SELECT * FROM …;

查询表中的数据

SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

结果集。也是一张数据库表，只不过这张表是临时的，用完就销毁

SELECT column_name,column_name
FROM `tablename`
WHERE condition;

# 或者

SELECT * 
FROM `tablename` 
WHERE condition;

在表中，一个列可能会包含多个重复值，如果要排除重复的值，可以使用DISTINCT 关键词用于返回唯一不同的值

SELECT DISTINCT column_name,column_name
FROM table_name;
WHERE condition;

查询数据插入新表

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

语法：

SELECT * 
INTO new_table_name [IN externaldatabase] 
FROM old_tablename;

# 或者只把希望的列插入新表：

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

查询条件

条件运算符

运算符	描述
=	等于	where id=45
<> 或 !=	不等于。在一些版本中，被写成 !=	where id<>45
>	大于	where id>45
<	小于	where id>45
>=	大于等于	where id>=45
<=	小于等于	where id=<45
BETWEEN	在某个范围内	where id BETWEEN 1 AND 10
LIKE	RLIKE	搜索某种模式
IN或者NOT IN	指定针对某个列的多个可能值	where id in (1,3,7)
AND	第一个条件和第二个条件都成立	where id>=45 AND id<60
OR	第一个条件和第二个条件中只要有一个成立	where id>=45 AND (id<60 OR name='李四')

模糊查询通配符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或[!charlist]	不在字

示例：

name LIKE ‘_a%'    表示第二个字符为 a 的人
name like '张%'     姓张的所有人
name like '_伟'     所有姓名为两个字且第二个字为 “伟”
name like '%商%'    姓名中包含 “商”字
name like '%友'     姓名以“友”结尾

删除表中数据

DELETE 语句用于删除表中的记录。

DELETE FROM table_name WHERE some_column=some_value;

如：

DELETE FROM `users` WHERE id='3';

删除所有数据

可以在不删除表的情况下，删除表中所有的行。这意味着表结构等保持不变：

DELETE FROM table_name;

# 或

DELETE * FROM table_name;

修改表中数据

UPDATE 语句用于更新表中的记录。

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

如：

UPDATE `users` SET nickname='wangwu',NAME='ww' WHERE id='2';

视图

视图其实就是一张虚拟表，一张从别的表中导出的一张不完整或经过运算的虚拟表。

视图有什么用，当我们经常查询一个表中的某几个字段的时候，我们可以把这几个字段单独导出一个视图，以后查询的时候直接可以 select *，在数据库中只存放了视图的定义，没有存放视图的数据，视图是依赖原来表中的数据的，原表中的数据一旦发生变化，视图查询出的结果也会跟着变化。下面进行创建一个视图。

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

视图是基于 SQL 语句的结果集的可视化的表

• 视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。
• 视图不能被修改，表修改或者删除后应该删除视图再重建。
• 视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。
• 视图可以被嵌套，一个视图中可以嵌套另一个视图。
• 视图不能索引，不能有相关联的触发器和默认值

语法

CREATE VIEW view_name AS
SELECT column_name1, column_name2
FROM table_name
WHERE condition;

案例

创建用户表：

create table user(
    id int(10) unsigned primary key auto_increment,
    name varchar(60) unique not null,
    pswd varchar(255)not null,
    class_id int(10)
);

插入用户数据：

insert into user (name,pswd class_id) values
 ('zhangsan','123456','1'),
 ('lishi','123456','1'),
 ('wangwu','123456','2');

创建班级表：

create table class (
    id int(10) unsigned primary key auto_increment,
    name varchar(255) not null unique
);

插入班级数据：

insert into class (name) values ('class1'),('class2'),('class3')