MYSQL——库表操作
- 1.1 SQL语句基础
- 1.1.1. SQL简介
- 1.1.2. SQL语句分类
- 1.1.3. SQL语句的书写规范
- 1.2 数据库的操作
- 1.2.1 数据库的登录及退出
- 1.2.2 查看数据库
- 1.2.3 创建数据库
- 1.2.4 切换数据库
- 1.2.5 查看当前用户
- 1.2.6 删除数据库
- 1.3 MySQL字符集
- 1.3.1. 字符集
- 1.3.2. 字符序
- 1.3.3 查看MySQL字符集
- 1.3.4 查看MySQL字符序
- 1.3.5 查看当前字符集的设置
- 1.4 表的基本操作
- 1.4.1 创建表
- 1.4.2 查看表
- 1.4.3 删除表
- 1.4.4 修改表
- 1.4.5 复制表的结构
- 1.5 表的约束
- 1.5.1 非空约束
- 1.5.2 默认值约束
- 1.5.3 列描述--comment
- 1.5.4 主键约束
- 1.5.5 主键自增长
- 1.5.6 唯一性约束
- 1.5.7 外键约束
- 1.5.8 检查约束
- 1.5.9 删除表的约束
- 作业
1.1 SQL语句基础
1.1.1. SQL简介
SQL:结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数 据维护的标准语言。使用SQL语句，程序员和数据库管理员可以完成如下的任务
改变数据库的结构
更改系统的安全设置
增加用户对数据库或表的许可权限
在数据库中检索需要的信息
对数据库的信息进行更新
1.1.2. SQL语句分类
MySQL致力于支持全套ANSI/ISO SQL标准。在MySQL数据库中，SQL语句主要可以划分为以下几类
DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作， 如:CREATE、DROP、ALTER、RENAME、 TRUNCATE等
DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作，如:INSERT、 DELETE、UPDATE、SELECT等
DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级 别，如:
GRANT、REVOKE等
Transaction Control:事务控制 COMMIT、ROLLBACK、SAVEPOINT等
1.1.3. SQL语句的书写规范
在数据库系统中，SQL语句不区分大小写(建议用大写) ，但字符串常量区分大小写 SQL语句可单行或多行书写，以“;”结尾。
关键词不能跨多行或简写。
用空格和缩进来提高语句的可读性。 子句通常位于独立行，便于编辑，提高可读性。
注释:
/* */ 和// 为多行注释
– 和 # 为单行注释
1.2 数据库的操作
1.2.1 数据库的登录及退出
登录格式
mysql -u 用户名 -h 服务器的主机地址 p 密码 -A
-p 后面是用户登录密码，注意:-p 和密码之间没有空格。如果出现空格，系统将不会把后面的字符 串当成密码来对待，没有密码时不写
-A参数:不预读数据库信息，加上该参数则不预读数据库中的表等信息，不会有表明及列名的补全， 但读取速度会加快，不加该参数可能使用数据库时出现下列提示(不影响使用)
退出命令：
exit
quit
\q
1.2.2 查看数据库
格式
mysql> show databases [like 条件];
注意有databases；（有s）
mysql自带的数据库：
- Information_schema: 主要存储了系统中的一些数据库对象信息，如:用户表信息、列信息、权限 信息、字符集信息、分区信息等。(数据字典表)
- performance_schema:主要存储数据库服务器的性能参数
- mysql: 存储了系统的用户权限信息及帮助信息
- sys: 5.7新增，之前版本需要手工导入。这个库是通过视图的形式把information_schema 和
performance_schema结合起来，查询出更加令人容易理解的数据
使用条件查看：
mysql> show databases like 'sys';
带有通配符的条件
%:匹配任意零个或多个字符 _ :匹配任意单个字符
mysql> show databases like 's%';
显示时间
mysql> select now();
显示数据库版本
mysql> select version();
1.2.3 创建数据库
- 格式
create database 数据库名
- 查看创建数据库的语句
show create database 数据库名
1.2.4 切换数据库
- 查看当前使用的数据库，格式：
select database();
- 选择数据库，格式：
use 数据库名
1.2.5 查看当前用户
select user();
1.2.6 删除数据库
- 格式
drop database 数据库名
1.3 MySQL字符集
- MySQL字符集包括字符集(CHARACTER)和排序规则(校对规则)(COLLATION)两个概念
1.3.1. 字符集
- Character Set(字符集):是字符的编码规则，规定了字符在数据库中的存储格式，比如占多少空间，支持哪些字符等
- 不同的字符集有不同的编码规则，在运维和使用MySQL数据库中，选取合适的字符集非常重要，如果选择 不恰当，轻则影响数据库性能，严重的可能导致数据存储乱码
- mysql 5.7的默认字符集是latin1，而8.0中是utf8mb4;
- mysql8的库表创建以及程序中尽可能使用utf8mb4字符集(可支持emoji)
- mysql中的utf8字符集是utf8mb3字符集的别名，避免使用
- MySQL数据库在开发运维中，字符集选用规则如下:
如果系统开发面向国外业务，需要处理不同国家、不同语言，则应该选择utf-8或者utf8mb4
如果只需要支持中文，没有国外业务，则为了性能考虑，可以采用GBK
gbk支持中文简体字符，但不是国际通用字符集
big5支持中文繁体字符
utf8几乎支持世界所有国家的字符
utf8mb4完全兼容UTF-8，用四个字节存储更多的字符
1.3.2. 字符序
- 字符序就是字符排序的规则集合
- 如:使用A>B>a>b的规则来进行排序或者另一种规则a>b>A>B顺序排序
- 字符序主要对字符的排序有影响
1.3.3 查看MySQL字符集
- 查看所有支持的字符集
mysql> show character set;
- 查看指定的字符集
mysql> show character set like 'gbk';
mysql> show character set like 'utf%';
1.3.4 查看MySQL字符序
- 查看支持的字符序
mysql> show collation;
- 指定条件查询
mysql> show collation where charset = "utf8mb4";
1.3.5 查看当前字符集的设置
- 通过变量查看
mysql> show variables like 'character_set%';
1.4 表的基本操作
1.4.1 创建表
- 格式
create table 表名(表选项)
- 表定义选项格式为：
列名1 列类型1 约束， 列名2 列类型2 约束，...
- 示例1：创建数据库并建新表
mysql> select database(); # 查看当前数据库
mysql> create database mydb2_stuinfo; # 创建数据库
Query OK, 1 row affected (0.02 sec)
mysql> use mydb2_stuinfo; # 使用数据库
Database changed
mysql> create table student1(id int, name varchar(30), sex char(2), age int
unsigned, score float, addr varchar(50));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| student1 |
+-------------------------+
1.4.2 查看表
- show语句：显示当前数据库中已有的数据表
show tables [from 数据库名][like wild];
- describe语句：查看数据表中的各列信息
{describe|desc}表名[列名];
#or
show columns from 表名称;
mysql> use mydb2_stuinfo;
mysql> desc student1;
#or
mysql> show columns from student1;
- 使用\G可以查看更全面的表定义信息
mysql> show create table student1\G
1.4.3 删除表
- 格式：
drop table [if exists] 表名;
1.4.4 修改表
- 更改表名（重命名）:
# 法1:
alter table 表名 rename 新表名;
#法2:
rename table 表名 to 新表名;
- 添加新列
alter table 表名 add 新列名 列类型 [after|first] 列名;
- 删除列
alter table 表名 DROP 列名;
- 修改列名
alter table 表名 change 旧列名 新列名 列类型;
- 修改列类型
alter table 表名 modify 列名 列类型;
- 修改列位置
alter table 表名 modify 列名 列类型 after 某列;
1.4.5 复制表的结构
- 方法一:在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下:
create table 新表名 like 源表;
- 方法二:在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中，下面的语法格式将源表的结构和记录都拷贝到新表中。
create table 新表名 select * from 源表
- 方法三:如果已经存在一张结构一致的表，复制数据:
mysql> select * from student2;
mysql> insert into student2 select * from student1;
1.5 表的约束
1.5.1 非空约束
mysql> create table myclass1(class_name varchar(20) not null, class_room varchar(20) not null, class_id int);
- 以上为创建表时的设置，也可以创建之后修改:
1 #语法:
2 alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;
# 也可以通过modify修改
mysql> alter table student1 modify name varchar(30) not null;
- 删除not null
mysql> alter table student1 modify id int null;
或者
mysql> slter table student1 modify name varchar(30);
1.5.2 默认值约束
- 例子
mysql> create table teacher1(id int not null, name varchar(30) not null, sex char(2) default '女');
- 创建表之后增加默认值：
mysql> alter table student1 modify sex char(2) default '女';
1.5.3 列描述–comment
- 列描述没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义，相当于
C/C++的注释
mysql> create table myclass2( class_name varchar(20) not null comment '教 室',class_room varchar(20) default '计科2');
1.5.4 主键约束
- 创建时增加主键
mysql> create table t1 ( id int unsigned primary key comment '学号不能为空', name varchar(20) not null ,sex char(2) default '男');
- 修改表，追加主键
mysql> alter table teacher1 add primary key(id);
- 删除主键：
alter table 表名 drop primary key;
- 复合主键
mysql> create table t2 (id int, hostname varchar(10), ip varchar(20), port int unsigned, primary key(ip, port));
1.5.5 主键自增长
- 作用：给主键添加自增长的数值
- auto_increment:当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1 操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键
- 示例：
#先增加主键
mysql> alter table student1 modify id int primary key;
#设置自增长
mysql> alter table student1 modify id int auto_invrement;
- 指定自增长的起始值
#指定自增长的起始值为100
mysql> create table t3 (id int priamry key auto_increment, systemname varchar(10) not null)auto_increment=100;
1.5.6 唯一性约束
- 创建表时实现
mysql> create table t4(id int primary key, name varchar(20) unique comment '名字不能重复，可以为空');
1.5.7 外键约束
- 作用：限定某个表的某个字段的引用完整性
- 格式
foreign key (从表的字段名称)references 主表名字(主表的字段名称) #建立外键关联
- 示例：
#定义主表
mysql> create table myclass3(id int primary key, name varchar(20) not null comment'班级名');
#定义从表
mysql> create table myclass3_stu(id int primary key, name varchar(30) not null comment '学生名', class_id int, foreign key(class_id) references myclass3(id));
1.5.8 检查约束
- 示例1:创建表时设置检查约束
mysql> create table t5(id int primary key, name varchar(20), salary float, check(salary>0 and salary<1000));
- 示例2：修改表时添加约束
#格式：
alter table 表名 add constraint <检查约束名> check(检查约束)
mysql> alter table t5 add constraint check_id check(id>0);
1.5.9 删除表的约束
删除not null约束
alter table 表名 modify 列名 类型;
删除unique约束
alter table 表名 drop index 唯一约束名;
删除primary key约束
alter table 表名 drop primary key;
删除foreign key约束
alter table 表名 drop foreign key 外键名;
作业
完成以下建表操作
