SQL基本知识

1. 数据库概述

数据库的两种类型：关系型数据库（Relational Database Management System, RDBMS）和非关系型数据库。

关系型数据库的主要产品有：oracle、mysql

非关系型数据库的主要产品有：Redis(key-value 存储系统)、MongoDB(基于分布式文件存储的数据库)

RDBMS和数据库的关系

SQL（Structured Query Language） 是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作oracle, sql server, mysql等所有的关系型数据库

SQL语句常用的有：

• DQL：数据查询语言，用于对数据进行查询，如select
• DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop

2. 常用数据类型

数据完整性

• 一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表里；
• 在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

数据字段类型

• 使用数据类型的云泽是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间

• 常用数据类型如下：

  • 整数：int, bit
  • 小数：decimal
  • 字符串：varchar, char
  • 日期时间：date, time, datetime
  • 枚举类型：enum

• 注意

  • decimal表示浮点数，如decimal(5,2)表示共存5位小数，小数占2位；
  • char表示固定长度的字符串，如char(3),如果填充‘bb’时会补一个空格为’bb ’
  • varchar表示可变长度的字符串，如varchar(3)，填充’bb’时就会存储’bb’
  • 字符串text表示存储大文本，当字符串大于4000时，推荐使用

约束

• 主键【primary key】：物理上存储的顺序
• 非空【not null】 ：此字段不允许填写空值
• 唯一【unique】 ：此字段的值不允许重复
• 默认【default] ：当不填写此值时会使用默认值，如果填写则以填写为准

创建学生表时，设置数据类型和约束语句

create table students(
    id int unsigned primary key auto_increment not null,  -- unsigned表示无符号
    name varchar(20) default '',
    age tinyint unsigned defalut 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密')，
    cls_id int unsigned default 0
)

创建班级表

create table classes(
    id int unsigned primary key auto_increment not null,
    name varchar(10)
)

3. 数据库基本操作

1. 命令行连接数据

• 打开终端，运行命令

mysql -uroot -p
回车后输入密码，当前设置的密码为123456

连接成功后如下图

2. 退出登录

quit 和 exit

• 登录成功后，输入如下命令查看效果

查看版本：select version();
显示当前时间：select now();

• 修改输入提示符

prompt python>
prompt \U-\D>

• \D 完整日期

• \U 当前使用用户

3. 常见操作

数据库用户名：root

数据库密码：123456

数据库的操作

-- 链接数据库
mysql -uroot -p123456
-- 退出数据库
exit
-- 查看创建数据库
show databases; -- 必须有s
-- 查看当前正在使用的数据库
select database(); -- 如果出现NULL表示目前没有使用数据库
-- 使用数据库
use test;
-- sql语句最后需要有分号;结尾
-- 显示数据库版本
select version()
-- 显示时间
select now()
-- 创建数据库 create
create database demo;
-- 创建数据库时需指定字符集
create database demo charset=utf8; -- 必须是utf8字符集
-- 查看数据库的创建语句
show create database demo;
-- 删除数据库
drop database demo;

数据表的操作

-- 查看当前数据库中所有表
show tables;

-- 创建表：students表（id、name、age、high、gender、cls_id）
    -- unsigned: 表示没有符号，没有负数
    -- auto_increment: 表示自动增长
    -- enum: 表示枚举，默认从1开始，枚举值（1，2,3）和原始值（‘男’，‘女’，‘中性’）在使用上是相同的
    -- create table 数据表名称 (字段 类型 约束[, 字段 类型 约束]);
    -- 多个约束 不分前后顺序
    -- 最后一个字段不要添加逗号
create table students(
    id int unsigned primary key auto_increment not null, 
    name varchar(20) not null,
    age tinyint unsigned defalut 0,
    height decimal(5,2) default 0.0,
    gender enum('男','女','中性','保密') default '保密'，
    cls_id int unsigned not null
);

-- 创建表：classes表(id、name)
create table classes(
    id int unsigned primary key auto_increment not null,
    name varchar(10)
);

-- 查看表的创建语句
show create table students;
-- 查看表结构
desc students;

-- 修改表结构：alter add/modify/change
    -- 修改表-添加字段
    -- alter table 表名 add 列名 类型/约束；
    -- 增加生日信息
    alter table students add birthday datetime default "2020-11-11 11:12:13";

    -- 修改表-修改字段：不改字段名，只修改类型及约束
    -- alter table 表名 modify 列名 类型及约束；
    alter table students modify birthday date defalut "2020-11-11";

    -- 修改表-修改字段：修改字段名、类型及约束；
    -- alter table 表名 change 原列名 新列名 类型及约束；
    alter table students change birthday birth date defalut "2020-11-11";

-- 修改表-删除字段
alter table students drop birth;

-- 删除表【慎用】
drop table students;

数据的增删改查

-- 向classes中添加数据
insert into classes values(1,'python1'),(2,'python2'),(3,'python3')

-- 增加 insert
	-- insert [into] 表名 values (值1,值2,...)
	-- 全列插入 值和表的字段的顺序一一对应
	-- 占位符：只有主键字段才有占位符的概念，占位符有0，default,NULL
	-- 全列插入在实际开发中用的不多  如果表结构一旦发生变化，全列插入就会标错
	insert into students values (0, '小乔', 18, 180.00, '女', 2);
	insert into students values (0, '小乔', 18, 180.00, '女'); -- 会报错
	insert into students values (default, '大乔', 19, 180.00, '女', 2);
	
	-- 指定列插入
	-- 值和列一一对应
	-- insert into 表名 (列1,...) values (值1,...)
	insert into students (name, high, gender, cls_id) values ('张飞', 190.00, '保密', 1)；
	insert into students (name, high, gender, cls_id) values ('鲁班', 150.00, 1, 1)；
	insert into students (name, high, gender, cls_id) values ('静香', 160.00, 2, 1)；
	-- 多行插入 批量插入
	-- insert into 表名 (列1,...) values (值1,...), (值2,...),...
	insert into students values (0, '孙尚香', 18, 180.00, '女', 2), (0, '甄姬', 20, 170.00, '女', 3);
	insert into students (name, high, gender, cls_id) values ('张飞', 190.00, '保密', 1), ('关羽', 190.00, '男', 1)；

-- 修改 update
-- where 表示修改的范围
-- update 表名 set 列1=值1，列2=值2...[where 条件]
-- 没有where 条件限制就是全表更新
update students set age = 25 where id =4; -- sql中 通过一个等号表示相等

-- 删除 delete
	-- 物理删除
	-- delete from tbname [where 条件判断]
	delete from students where id = 5;
	-- 查询有哪些学生没有被删除
	
-- 查询基本使用 select
	-- 查询所有列
	-- select * from 表名;
	select * from students;
	-- 指定字段查询
	select name,age FROM students;
    -- 指定条件查询
    select * from students where age between 17 and 20;
    -- 查询指定列
    select name from students where id in (2,5);
    -- 字段的顺序
    select name, gender, age from students;
    -- 可以使用as为列或表指定别名
    select name as n from students where age = 18; -- sql 中表示相等 使用= 而不是==
    select name, concat(age, ', ', gender, ', ', cls_id) as info from Websites;
    -- 返回唯一不同的值 distinct
    select distinct name FROM students;