【学习笔记】SQL数据库

sql语句笔记

1、初识MySQL

JavaEE：企业级Java开发 Web
前端（页面：展示，数据！）
后台（连接点：连接数据库JDBC，链接前端（控制，控制视图跳转，和给前端传递数据））
数据库（存数据, Text, Excel, word)

1.1 为什么学习数据库

数据库是所有软件体系中最核心的存在。

1.2 什么是数据库

数据库（DB，DataBase）
概念： 数据仓库，软件，安装在操作系统（window，linux，mac…）之上!SQL,可以存储大量的数据。500万
作用：存储数据，管理数据

1.3 数据库分类

关系型数据库：

• MySQL, Oracle, Sql Server, DB2, SQLite
• 通过表和表之间，行和列之间的关系进行数据的存储， 学院信息表，考勤表

非关系型数据库：

• Redis，MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL,数据库管理系统

1.4 MySQL

是个关系型数据库管理系统。
MySQL是最好的RDBMS(Relational Database Management System,关系数据库管理系统)应用软件之一。
开源的数据库软件。
体积小，速度快，总体拥有成本低，招人成本低，所有人都必须会。

5.7版本稳定，8.0

安装建议：
1、尽量不要使用exe，注册表
2、尽可能使用压缩包安装~

1.5 安装MySql

D:\software\mysql-5.7.19\bin>mysqld -install
Service successfully installed.
D:\software\mysql-5.7.19\bin>mysqld --initialize-insecure --user=mysql

D:\software\mysql-5.7.19\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


D:\software\mysql-5.7.19\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> update mysql.user set authentication string=password('123456') where user='root' and Host = 'localhost';
ERROR 1064 (42000):
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

D:\software\mysql-5.7.19\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


D:\software\mysql-5.7.19\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


D:\software\mysql-5.7.19\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1.6 SQLyog

SQLyog的下载、安装、破解、配置（MySQL可视化工具安装）

1.7 连接数据库

命令行连接

mysql -u root -p --连接数据库


update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码

flush privileges;  ---刷新权限

---------------------------------------------

show datebase -- 查看所有的数据库
use school --切换数据库 use 数据库名
show tables;  --  查看数据库中所有的表
describe student;  -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库
exit; -- 退出连接

-- 单行注释

/*
多行注释
*/

2. 操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中的表的数据

2.1 操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2、删除数据库

DROP DATABASE [IF EXISTS] westos

3、使用数据库

--tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带
 USE `school`

4、查看数据库

SHOW DATABASES --查看所有的数据库

2.2 数据库的列类型

数值

• tinyint 十分小的数据，一个字节
• smallint 较小的数据 ， 两个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数， 四个字节
• big 较大的数据 ， 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节（精度问题）
• decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

• char 字符串，固定大小 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期
java.util.Date

• date YYYY-MM-DD,日期
• time HH：mm：ss 时间格式
• datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
• timestamp 时间戳，1970.1.1 到现在的毫秒数
• year 年份表示

null

• 没有值，未知
• 注意，不要使用null进行运算，结果为null

2.3 数据库的字段属性（重点）

Unsigned：

• 无符号的整数
• 声明了该列不能为负数

zerofill:

• 0填充的
• 不足的位数，使用0类填充， int(3) , 5 —005

自增：

• 通常理解为自增，自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键~index，必须是整数类型
• 可以自定义设计主键自增的起始站和步长

非空 NUll not null

• 假设设置为not null，如果不给他赋值，就会报错
• null，如果不填写值，默认就是null

默认：

• 设置默认的值
• sex，默认值为男，

扩展：
每一个表都必须存在以下五个字段，未来做项目用的，表示一个记录存在的意义

id 主键
version 乐观锁
is delete 伪删除
gmt create 创建时间
gmt update 修改时间

2.4 创建数据库表

--  目标：创建一个schoo1数据库
--   创建学生表（列，字段）使用SQL创建
--   学号 int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址,email


-- 注意点，使用英文（），表的名称和字段尽量使用 '' 括起来
--  AUTO INCREMENT 自增
-- 字符串使用单引号括起来！
-- 所有的语句后面加，（英文的），最后一个不用加
-- PRIMARYKEY主键，一般一个表只有一个唯一的主键！

CREATE TABLE IF NOT EXISTS `student1`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '登录密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '性别' ,
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT  '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
     `字段名`  列类型 [属性] [索引] [注释],
     `字段名`  列类型 [属性] [索引] [注释],
     `字段名`  列类型 [属性] [索引] [注释],
     `字段名`  列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

2.5 MyISAM 和 InnoDB的区别

 SHOW CREATE DATABASE school  -- 查看创建数据库的语句
SHOW CREATE TABLE student  -- 查看student数据表的定义语句
DESC student -- 显示表的结构

INNODB 默认使用
MYISAM 早些年使用

常规使用操作:

• MYISAM 节约空间， 速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存储的位置：
所有的数据库文件都存在data目录下；一个文件夹对应一个数据库
本质还是文件的存储！

MySQL引擎在物理文件上的区别

设置数据库表的字符集编码
CHARSET=utf8;
不设置的话会是sql默认的字符集编码

2.6 修改和删除表

修改

-- 修改表   ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student1 RENAME AS student_first
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 属性
ALTER TABLE student_first ADD age INT(11)
-- 修改表的字段
-- ALTER TABLE  表名  MODIFY 字段名 列属性  -- 修改约束
ALTER TABLE student_first MODIFY age VARCHAR(11)  -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]  -- 字段重命名
ALTER TABLE student_first CHANGE age age1 INT(1)  -- 字段重命名

-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE student_first DROP age

删除

-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS student

所有的创建和删除操作尽量加上判断，以免报错

注意：

• `` 字段名，使用这个包裹
• 注释 – /**/
• sql关键字大小写不敏感，建议大家写小写
• 所有的符号全部用英文！

3. MySQL数据管理

3.1 外键（了解即可）

方式一， 在创建表的时候，增加约束（麻烦）

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方式二， 创建表成功后，添加外键约束

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级',
	PRIMARY	KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student1`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '登录密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '性别' ,
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10)NOT NULL COMMENT '学生的年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT  '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建表的时候， 没有外键关系
ALTER TABLE `student1`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列）REFERENCES 那个表（哪个字段）

---

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2 DML语言

DML语言：数据操作语言
• Insert
• update
• delete

3.3 添加

-- 插入数据（添加）
-- insert into 表名（[字段名1 ， 字段名2， 字段名3]） values ('值1','值2','值3')
-- insert into 表名（字段名1） values ('值1'),('值2'),('值3')
INSERT INTO `grade`(`gradename`) VALUES ('大四')


-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES（'大三'）


-- 一般写插入语句，我们一定要保证数据和字段一一对应。

-- 插入多个字段
INSERT INTO `grade`(`gradename`)  VALUES ('大四'),('大三'),('大二')

语法: insert into 表名（[字段名1 ， 字段名2， 字段名3]） values ('值1','值2','值3')

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要要一一对应，不能少
3. 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可VALUES

3.4 修改

update 修改谁（条件） set原来的值=新值

-- 修改学员名字
UPDATE