MySQL数据库——基础&约束
数据库就是存储数据,管理数据的仓库
数据库分类:关系型数据库(MySQL)和非关系型数据库(redis)
1.—数据库结构
数据库–>表(行和列)–>数据
2.—SQL语句
定义
结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ “S-Q-L”),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
SQL 是1986年10 月由美国国家标准局(ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。
分类
DML(Data Manipulation Language)数据操纵语言
如:insert,delete,update,select(插入、删除、修改、检索)简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
DDL(Data Definition Language)数据库定义语言
如:create table之类
DCL(Data Control Language)数据库控制语言
如:grant、deny、revoke等,只有管理员才有相应的权限
DQL(Data Query Language)数据库查询语言
如: select 语法
注意:SQL不区分大小写
3.—数据库常用操作(CRUD)
创建库–Create
mysql> create database cgb2022 default charactor set utf8;
Query OK, 1 row affected (0.01 sec)
删除库–Delete
mysql> drop database cgb2022;
Query OK, 0 rows affected (0.01 sec)
修改库–Update
查看库–Read
查看正在使用的数据库
select database();
mysql> select database();
+------------+
| database() |
+------------+
| cgb2021 |
+------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cgb2022 |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.03 sec)
4.—表的常用操作
表设计
创建表–create table
create table 表名(字段名称 字段类型(字段长度),字段2,字段3);
格式:
create table 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
...
);
类型:
varchar(n) 字符串
int 整形
double 浮点
date 时间
timestamp 时间戳
注意:doble类型不可以指定长度,否则会报错。
tb_door
tb_order_detail
#: 使用指定的数据库
mysql> use cgb2021;
Database changed
mysql> create table tb_door(id int(11), door_name varchar(100), tel varchar(20));
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> create table tb_order_detail(id int(11), order_id int(11), num tinyint(4), item varchar(30), price double);
Query OK, 0 rows affected, 3 warnings (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_cgb2021 |
+-------------------+
| tb_door |
| tb_order_detail |
+-------------------+
2 rows in set (0.00 sec)
修改表
添加列(添加字段)
alter table tb_door add column money NUMERIC(7,2)
######################################
mysql> alter table tb_door add column address varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tb_door add column money numeric(7,2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改表名
rename table 表名 to 新表名;
例如:# 为分类表category 改名成 category2
RENAME TABLE category TO category2;
修改表的字符集
alter table 表名 character set 字符集(了解);
例如:# 为分类表 category 的编码表进行修改,修改成 gbk
ALTER TABLE category CHARACTER SET gbk;
修改表删除列
alter table 表名 drop 列名;
例如: # 删除分类表中description这列
ALTER TABLE category DROP description;
修改表修改列的类型长度及约束.
alter table 表名 modify 列名 类型(长度) 约束;
例如:# 为分类表的描述字段进行修改,类型varchar(50) 添加约束 not null
ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;
修改表删除列
alter table 表名 drop 列名;
例如:# 删除分类表中description这列
ALTER TABLE category DROP description;
修改表修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
例如:# 为分类表的分类名称字段进行更换 更换为 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);
删除表–drop table 表名
mysql> drop table tb_door;
查看表–show tables;
mysql> show tables;
+-------------------+
| Tables_in_cgb2021 |
+-------------------+
| tb_door |
| tb_order_detail |
+-------------------+
2 rows in set (0.00 sec)
查看表结构–desc 表名;
mysql> desc tb_door;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| door_name | varchar(100) | YES | | NULL | |
| tel | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| money | decimal(7,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> desc tb_order_detail;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| order_id | int | YES | | NULL | |
| num | tinyint | YES | | NULL | |
| item | varchar(30) | YES | | NULL | |
| price | double | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5.—数据的常用操作
插入记录-- insert into 表名 value(值);
语法: select into 表名 value(值1,值2,值13,值4,值5,值6);
注意:
表结构有几个字段,插入几个值
值的顺序要和字段的顺序保持一致
向tb_door表中插入2条记录
mysql> insert into tb_door value(001,"张三","13623552743","山西长治",9999);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_door value(010,"李四","13836259613","山西太原",66666.369);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into tb_door value(null,"李白","12968971480",null,99);
Query OK, 1 row affected (0.02 sec)
查询记录–SELECT * FROM 表名
查询tb_door表中的所有记录
SELECT * FROM tb_door;
mysql> select * from tb_door;
+------+-----------+-------------+----------+----------+
| id | door_name | tel | address | money |
+------+-----------+-------------+----------+----------+
| 1 | 张三 | 13623552743 | 山西长治 | 9999.00 |
| 10 | 李四 | 13836259613 | 山西太原 | 66666.37 |
| NULL | 李白 | 12968971480 | NULL | 99.00 |
+------+-----------+-------------+----------+----------+
3 rows in set (0.00 sec)
修改记录–update 表名 set 值 where 条件值
修改tb_door表中id为1的记录
update tb_door set tel=“192168100” where id=1;
mysql> update tb_door set tel="192168100" where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_door;
+------+-----------+-------------+----------+----------+
| id | door_name | tel | address | money |
+------+-----------+-------------+----------+----------+
| 1 | 张三 | 192168100 | 山西长治 | 9999.00 |
| 10 | 李四 | 13836259613 | 山西太原 | 66666.37 |
| NULL | 李白 | 12968971480 | NULL | 99.00 |
+------+-----------+-------------+----------+----------+
3 rows in set (0.00 sec)
删除记录–
删除tb_door表中door_name为李白的数据
delete from tb_door where door_name=“李白”;
mysql> delete from tb_door where door_name="李白";
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_door;
+------+-----------+-------------+----------+----------+
| id | door_name | tel | address | money |
+------+-----------+-------------+----------+----------+
| 1 | 张三 | 192168100 | 山西长治 | 9999.00 |
| 10 | 李四 | 13836259613 | 山西太原 | 66666.37 |
+------+-----------+-------------+----------+----------+
2 rows in set (0.01 sec)
排序
将tb_door表记录按照id排序
Select * from tb_door order by id desc;
mysql> select * from tb_door order by id desc;
+------+-----------+-------------+----------+----------+
| id | door_name | tel | address | money |
+------+-----------+-------------+----------+----------+
| 10 | 李四 | 13836259613 | 山西太原 | 66666.37 |
| 5 | 李白 | 2968971480 | 唐朝 | 528.23 |
| 1 | 张三 | 192168100 | 山西长治 | 9999.00 |
+------+-----------+-------------+----------+----------+
3 rows in set (0.00 sec)
记录总数–Select count(*) from 表名
查询tb_door表中的总记录数
Select count(*) from tb_door;
mysql> Select count(*) from tb_door;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.01 sec)
6.—数据类型
类型名称 | 说明 |
int(integer) | 整数类型 |
char | 长度固定,不足使用空格填充 |
varchar | 变长字符串,查询稍慢,但节省空间。 |
double | 小数类型,doble类型不可以指定长度,否则会报错。 |
numeric(5,2) decimal(5,2) | 指定整数位与小数位长度的小数类型 |
date | 日期类型,格式为yyyy-MM-dd,包含年月日,不包含时分秒 |
datetime | 日期类型,格式为 YYYY-MM-DD HH:MM:SS,包含年月日时分秒 |
timestamp | 日期类型,时间戳 ,从1970年1月1日到指定日期的毫秒数 |
命名规则
- 字段名必须以字母开头,尽量不要使用拼音
- 长度不能超过30个字符(不同数据库,不同版本会有不同)
- 不能使用SQL的保留字,如where,order,group
- 只能使用如下字符az、AZ、0~9、$ 等
- Oracle习惯全大写:USER_NAME,mysql习惯全小写:user_name
- 多个单词用下划线隔开,而非java语言的驼峰规则
字符
- char长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位。查询速度极快但浪费空间
- varchar变长字符串,最多容纳4000个字符,varchar(11)存储abc,只占3位。查询稍慢,但节省空间。Oracle为varchar2
- 大文本: 大量文字(不推荐使用,尽量使用varchar替代)
- 以utf8编码计算的话,一个汉字在u8下占3个字节
注:不同数据库版本长度限制可能会有不同
数字
- tinyint,int 整数类型
- float,double 小数类型
- numeric(5,2) decimal(5,2) ——也可以表示小数,表示总共5位,其中可以有两位小数
- decimal 和 numeric 表示精确的整数数字
日期
- date 包含年月日
- time 时分秒
- datetime 包含年月日和时分秒
- timestamp 时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数
图片
- blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
7.—字段约束
主键约束–primary key
主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。
创建一个表–www,把id设置为主键约束
mysql> create table www(id int primary key,name varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert www value(1,"张三");
Query OK, 1 row affected (0.00 sec)
mysql> insert www value(1,"李四");
ERROR 1062 (23000): Duplicate entry '1' for key 'www.PRIMARY' ###唯一
mysql> insert www value(2,"李四");
Query OK, 1 row affected (0.00 sec)
mysql> insert www value(null,"李四");
ERROR 1048 (23000): Column 'id' cannot be null ### 不能为空
主键自增策略–AUTO_INCREMENT
当主键为数值类型时,为了方便维护,可以设置主键自增策略(auto_increment),设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中。而且每次用完AUTO_INCREMENT值,都会自增1. AUTO_INCREMENT=1
创建一个表–www,把id设置为主键约束,并且添加主键自增策略 AUTO_INCREMENT
mysql> create table www(id int primary key auto_increment,name varchar(100));
Query OK, 0 rows affected (0.03 sec)
mysql> insert www value(1,"张三");
Query OK, 1 row affected (0.01 sec)
mysql> insert www value(2,"李四");
Query OK, 1 row affected (0.02 sec)
mysql> insert www value(null,"王五"); ##使用主键自增策略
Query OK, 1 row affected (0.01 sec)
mysql> insert www value(null,"老六"); ##使用主键自增策略
Query OK, 1 row affected (0.00 sec)
mysql> select * from www;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 老六 | ##使用主键自增策略
+----+------+
删除主键约束
如需撤销 PRIMARY KEY 约束,请使用下面的 SQL:
ALTER TABLE persons DROP PRIMARY KEY
非空约束–not null
如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
创建一个表–ps,把id设置为非空约束
mysql> create table ps(id varchar(20) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into ps value("abcd");
Query OK, 1 row affected (0.01 sec)
mysql> insert into ps value(null);
ERROR 1048 (23000): Column 'id' cannot be null
唯一约束–unique
如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
创建一个表–www,把id设置为唯一约束
mysql> create table www(name varchar(30) unique);
Query OK, 0 rows affected (0.02 sec)
mysql> insert www values("list");
Query OK, 1 row affected (0.00 sec)
mysql> insert www values("list");
ERROR 1062 (23000): Duplicate entry 'list' for key 'www.name' ###不能重复
mysql> insert www values(null);
Query OK, 1 row affected (0.00 sec)
mysql> insert www values(null);
Query OK, 1 row affected (0.00 sec)
mysql> insert www values(null);
Query OK, 1 row affected (0.00 sec) #### 可以为空
mysql> select * from www;
+------+
| name |
+------+
| NULL |
| NULL |
| NULL |
| list |
+------+
4 rows in set (0.00 sec)
删除唯一约束
如需撤销 UNIQUE 约束,请使用下面的 SQL:
ALTER TABLE persons DROP INDEX 名称
面试题
问:针对auto_increment ,删除表中所有记录使用 delete from 表名 或使用 truncate table 表名,二者有什么区 别?
删除方式:
- delete 一条一条删除,不清空auto_increment记录数。
truncate 直接将表删除,重新建表,auto_increment将置为零,从新开始。
8.—DOS操作数据乱码解决
我们在dos命令行操作中文时,会报错
insert into category(cid,cname) values(‘c010’,’中文’);
ERROR 1366 (HY000): Incorrect string value: '\xB7\xFE\xD7\xB0' for column 'cname' at
row 1
错误原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk
查看MySQL内部设置的编码
show variables like 'character%'; 查看所有mysql的编码
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)
##需要修改client、connection、results的编码一致(GBK编码)
解决方案1:在cmd命令窗口中输入命令,此操作当前窗口有效,为临时方案。
set names gbk;
解决方案2:安装目录下修改my.ini文件,重启服务所有地方生效。
9.—MySQL数据库密码重置(扩展)
- 停止mysql服务器运行输入services.msc 停止mysql服务
- 在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口
- 新打开cmd,输入mysql -uroot 不需要密码
use mysql;
update user set password=password('abc') WHERE user='root'
- 关闭两个cmd窗口