阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL基本操作

654次阅读
没有评论

共计 8180 个字符，预计需要花费 21 分钟才能阅读完成。

MySQL 之基本操作

数据库操作

  • 创建数据库 
    		create database db1;
    		create database db1 default charset gbk;
    		create database if not exists db1 default character  set utf8;
  • 显示创建信息 
    show create database db1;
  • 修改数据库默认的字符格式 
    alter database db1 default character set gbk;
  • 删除数据库 
    drop database db1;

区别：delete、truncate、drop

  • delete：删除数据
    1、数据操作语言（DML）在事务控制里，DML 语句是需要 commit，不提交的话可以 rollback；删除大量记录速度慢，只删除数据不回收高水位线
    2、可以带条件删除
  • truncate：删除数据
    1、数据定义语言（DDL）清大量数据速度快，高水位线（high water mark）下降
    2、不能带条件 truncate
  • drop：对象表、库、用户等；数据定义语言

数据查询语句：select

通配符：

  		• %        匹配 0 个或任意多个字符
    		_        匹配一个字符=       精确匹配like     模糊匹配
    		regexp (. 任意单个字符 * 前导字符出现 0 次或连续多次  .* 任意长度字符.....)        使用正则表达式来匹配

排序：

  		• order by       排序，默认升序
    		asc            升序排列结果
    		desc           降序排列结果
    		group by       聚合
    		distinct       去除重复的行

常用函数

查看 mysql 支持字符加密函数：

select password('123');        
select md5('123');
select sha1('123');            
select encrypt('123');        基本上不用了

使用 select 来调度 mysql 中的常见函数：

select version();     当前数据库版本
select current_user(); 当前用户
select current_time(); 当前时间
select current_date(); 当前日期
select now();       当前日期时间

MySQL 表操作

表 是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。表的操作
包括创建表、查看表、修改表和删除表。

 语法：

create table 表名(字段名 1  类型[(宽度) 约束条件],
        字段名 2  类型[(宽度) 约束条件],
        字段名 3  类型[(宽度) 约束条件]
)[存储引擎 字符集];
    说明：auto_increment 表示自增长 
        primary key 表示主键
        not null 表示不为空
==在同一张表中，字段名是不能相同 ==宽度和约束条件可选 == 字段名和类型是必须的

创建表

表 school.student1

表 school.student1
字段            字段            字段
id              name        sex            age
1               tom         male            23        记录
2               jack        male            21        记录
3               alice       female          19        记录
 
 
mysql> CREATE  DATABASE school;                            //创建数据库 school
mysql> use school;
mysql> create table student1(-> id int,
    -> name varchar(50),                 
    -> sex enum('m','f'),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)
MySQL 基本操作

mysql> show tables;                                                //查看表（当前所在库）+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)

查看表

MySQL 基本操作

mysql> select * from student1;                                //查询表中所有字段的值
Empty set (0.00 sec)
 
mysql> select name,age from student1;                //查询表中指定字段的值
Empty set (0.00 sec)

查询表中所有字段的值

向表中插入内容
语法：

insert into 表名(字段 1, 字段 2...) values(字段值列表...);

表 school.student2

                  字段名              数据类型
编号              id                   int
姓名              name                varchar(50)
出生年份         born_year            year
生日              birthday              date
上课时间          class_time             time
注册时间          reg_time             datetime
 
mysql> create table student2(-> id int,
    -> name varchar(50),         
    -> born_year year,
    -> birthday date,
    -> class_time time,
    -> reg_time datetime 
    -> );
mysql> desc student2;
mysql> insert into student2 values(1,'tom',now(),now(),now(),now());
mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);

表 school.student3

id     id              int
姓名    name            varchar(50)
性别    sex             enum('male','female')
爱好    hobby           set('music','book','game','disc')
 
mysql> create table student3(-> id int,
    -> name varchar(50),
    -> sex enum('male','female'),
    -> hobby set('music','book','game','disc')
    -> );
 
mysql> desc student3;
mysql> show create table student3\G    
mysql> insert into student3 values (1,'tom','male','book,game');
mysql> insert into student3 values (2,'jack','male','film');
mysql> select * from student3;

查看表结构

DESCRIBE 查看表结构
DESCRIBE 表名;
DESC 表名;

SHOW CREATE TABLE 查看表详细结构
SHOW CREATE TABLE 表名;

表完整性约束

作用：用于保证数据的完整性和一致性

约束条件                        说明
PRIMARY KEY (PK)              标识该字段为该表的主键，可以唯一的标识记录，不可以为空  UNIQUE + NOT NULL
FOREIGN KEY (FK)              标识该字段为该表的外键，实现表与表（父表主键 / 子表 1 外键 / 子表 2 外键）之间的关联
NOT NULL                       标识该字段不能为空
UNIQUE KEY  (UK)              标识该字段的值是唯一的，可以为空，一个表中可以有多个 UNIQUE KEY
AUTO_INCREMENT               标识该字段的值自动增长（整数类型，而且为主键）DEFAULT                        为该字段设置默认值
UNSIGNED                      无符号，正数
ZEROFILL                       使用 0 填充，例如 0000001
 
说明：1. 是否允许为空，默认 NULL，可设置 NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是 NULL，如果插入记录时不给字段赋值，此字段使用默认值
     sex enum('male','female') not null default 'male'
    age int unsigned NOT NULL default 20         必须为正值（无符号）不允许为空  默认是 20
3. 是否是 key
    主键 primary key
    外键 forengn key
    索引 (index,unique...)

DEFAULT、NOT NULL

表 school.student4

mysql> create table school.student4(-> id int not null,
    -> name varchar(50) not null,
    -> sex enum('m','f') default 'm' not null,
    -> age int unsigned default 18 not null, hobby set('music','disc','dance','book') default 'book,dance');

 MySQL 基本操作

mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)

 MySQL 基本操作

设置唯一约束 UNIQUE

表 company.department

mysql> create table company.department(-> dept_id int,
    -> dept_name varchar(30) unique,
    -> comment varchar(50));

 MySQL 基本操作

设置主键约束 PRIMARY KEY

primary key 字段的值是不允许重复，且不允许不 NULL（UNIQUE + NOT NULL）
单列做主键
多列做主键（复合主键）

单列做主键

表 school.student6 方法一

mysql> create table student6(-> id int primary key not null auto_increment,
    -> name varchar(50) not null,
    -> sex enum('male','female') not null default 'male',
    -> age int not null default 18);
Query OK, 0 rows affected (0.20 sec)
 
mysql> insert into student6 values (1,'alice','female',22);
 
mysql> insert into student6(name,sex,age) values
    -> ('jack','male',19),
    -> ('tom','male',23);
 
mysql> select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | jack  | male   |  19 |
|  3 | tom   | male   |  23 |
+----+-------+--------+-----+
3 rows in set (0.00 sec)

 MySQL 基本操作

表 school.student7 方法二

mysql>  create table student7(-> id int auto_increment not null,
    -> name varchar(50) not null,
    -> sex enum('male','female') not null default 'male',
    -> age int not null default 18,
    -> primary key(id));
Query OK, 0 rows affected (0.21 sec)

MySQL 基本操作

复合主键

表 school.service

host_ip              存储主机 IP
service_name         服务名
port                 服务对应的端口
allow(Y,N)           服务是否允许访问
主键：host_ip + port = primary key
 
mysql> create table service(-> host_ip varchar(15) not null,
    -> service_name varchar(10) not null,
    -> port varchar(5) not null,
    -> allow enum('Y','N') default 'N',
    -> primary key(host_ip,port)
    -> );
Query OK, 0 rows affected (0.00 sec)

MySQL 基本操作

mysql>  insert into service values ('192.168.2.168','ftp','21','Y');
Query OK, 1 row affected (0.09 sec)
 
mysql> insert into service values ('192.168.2.168','httpd','80','Y');
Query OK, 1 row affected (0.03 sec)

 MySQL 基本操作

设置字段值增 AUTO_INCREMENT

表 company.department3

CREATE TABLE department3 (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(30),
    comment VARCHAR(50)
    );

 MySQL 基本操作

设置外键约束 FOREIGN KEY

父表 company.employees

mysql> create table employees(-> name varchar(50) not null,
    -> mail varchar(20),
    -> primary key(name)) engine=innodb;

子表 company.payroll

mysql> create table payroll(-> id int not null auto_increment,
    -> name varchar(50) not null,
    -> payroll float(10,2) not null,
    -> primary key(id),
    -> foreign key(name)
    -> references employees(name)
    -> on update cascade
    -> on delete cascade
    -> )engine=innodb;
MySQL 基本操作

mysql> select * from employees;
+------+--------------+
| name | mail         |
+------+--------------+
| tom  | 11111@qq.com |
+------+--------------+
1 row in set (0.00 sec)
 
mysql> select * from payroll;
+----+------+---------+
| id | name | payroll |
+----+------+---------+
|  1 | tom  |   11.00 |
+----+------+---------+
1 row in set (0.00 sec)
 
mysql> update employees set name='tomaaa' where name='tom';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from payroll;
+----+--------+---------+
| id | name   | payroll |
+----+--------+---------+
|  1 | tomaaa |   11.00 |
+----+--------+---------+
1 row in set (0.00 sec)
 
mysql> select * from employees;
+--------+--------------+
| name   | mail         |
+--------+--------------+
| tomaaa | 11111@qq.com |
+--------+--------------+
1 row in set (0.00 sec)
 
 
 
mysql> delete from employees where name='tomaaa';
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from employees;
Empty set (0.00 sec)
 
mysql> select * from payroll;
Empty set (0.00 sec)

View Code

mysql> update employees set name='tomaaa' where name='tom';
mysql> delete from employees where name='alice';

 结论：
当父表中某个员工的记录修改时，子表也会同步修改
当父表中删除某个员工的记录，子表也会同步删除

修改表 ALTER TABLE

语法：

  •  修改表名 
    ALTER TABLE 表名 RENAME 新表名;
  •  增加字段 
    ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名  数据类型 [完整性约束条件…];ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名
  •  删除字段 
    ALTER TABLE 表名 DROP 字段名;
  • 修改字段 
    		ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
    		                          
    		ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
    		                          
    		ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

 实例：

MySQL 基本操作

1. 修改存储引擎
mysql> alter table service 
        -> engine=innodb;                                                          //engine=myisam|memore|....
 
2. 添加字段
mysql> create table student10 (id int);
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int not null default 22;
    
mysql> alter table student10
    -> add stu_num int not null after name;                                //添加 name 字段之后
 
mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;                //添加到最前面
 
3. 删除字段
mysql> alter table student10
        -> drop sex;
 
mysql> alter table service
        -> drop mac;
 
4. 修改字段类型 modify
mysql> alter table student10
        -> modify age tinyint;
    
mysql> alter table student10
        -> modify id int not null primary key ;                                                             //修改字段类型、约束、主键
 
5. 增加约束（针对已有的主键增加 auto_increment）mysql> alter table student10 modify id int not null primary key auto_increment;         //错误，该字段已经是 primary key
ERROR 1068 (42000): Multiple primary key defined
 
mysql> alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
6. 增加复合主键
mysql> alter table service2
        -> add primary key(host_ip,port);        
 
7. 增加主键
mysql> alter table student1
        -> add primary key(id);
 
8. 增加主键和自动增长
mysql> alter table student1
        -> modify id int not null primary key auto_increment;
 
9. 删除主键[primary key auto_increment]
a. 删除自增约束
mysql> alter table student10 modify id int not null; 
 
b. 删除主键
mysql> alter table student10                                 
        -> drop primary key;

修改表示例

复制表

  • 复制表结构+记录 （key 不会复制: 主键、外键和索引） 
    mysql> create table new_service select * from service;
  •  只复制表结构 
    mysql> create table new1_service select * from service where 1=2;  // 条件为假，查不到任何记录
  •  复制表结构，包括 Key 
    mysql> create table t4 like employees;

     

 

正文完
星哥玩云-微信公众号
post-qrcode
MySQL 数据库
发表至： 数据库技术
2022-01-28
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-28发表，共计8180字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
Redis info的参数详解
MySQL之视图详解
解决MySQL时间转换异常
Redis缓存数据库详解
Oracle物化视图详解
Greenplum 4.3 For CentOS 6.5 安装指南
Oracle 11g RAC开启归档日志和闪回
MongoDB用户权限控制
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
4088661
评论数
4
文章搜索
热门文章
申请腾讯混元的API Key并且使用LobeChat调用混元AI

申请腾讯混元的API Key并且使用LobeChat调用混元AI

申请腾讯混元的 API Key 并且使用 LobeChat 调用混元 AI 之前星哥写了一篇文章《手把手教拥有...
基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

基于 Docker 快速搭建一个开源的 IT 人员在线工具箱 -it-tools 在日常的开发工作中，我们经常...
让每个人都可以轻松使用Git-腾讯自研Git客户端

让每个人都可以轻松使用Git-腾讯自研Git客户端

让每个人都可以轻松使用 Git- 腾讯自研 Git 客户端 Git，作为当今最流行的版本控制系统，其强大的功能...
使用Docker部署开源的WPS-Office

使用Docker部署开源的WPS-Office

使用 Docker 部署开源的 WPS-Office 一、前言 越来越多的企业和个人开始将应用程序部署在 Do...
干货：MySQL增量备份脚本

干货：MySQL增量备份脚本

1、线上环境有时候考虑到需要做增量备份数据库，所以写了个简单的脚本 2、添加自动任务每小时或者更短时间执行就好...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
写好shell脚本的8个建议

写好shell脚本的8个建议

导读 这八个建议，来源于键者几年来编写 shell 脚本的一些经验和教训。事实上开始写的时候还不止这几条，后来...
使用Linux容器迅速搭建渗透测试环境（上篇）

使用Linux容器迅速搭建渗透测试环境（上篇）

作为一名渗透测试人员，我经常需要搭建一些小型实验环境（有时候不是那么小），当然，这样做的原因是多方面的，例如在...
列举一下你可能没注意的Linux命令

列举一下你可能没注意的Linux命令

这篇文章仅讨论 Linux 环境下的东西，很显然对 MacOS 也适用。其实很多东西虽然有 Windows 也...
在Linux中使用grep正则表达式入门

在Linux中使用grep正则表达式入门

正则表达式是一种符号表示法，用于识别文本模式。Linux 处理正则表达式的主要程序是 grep。grep 搜索...
Handbrake：开源的视频格式转换软件

Handbrake：开源的视频格式转换软件

最近，当我的儿子让我数字化他的高中篮球比赛的一些旧 DVD 时，我马上就想到了 Handbrake[1]。它是...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中