目录
主键primary key 和 唯一键unique key:
一、常用的数据类型:
类型 | 含义 |
---|---|
tinyint(n) | 1个字节,范围(-128~127) |
smallint(n) | 2个字节,范围(-32768~32767) |
mediumint(n) | 3个字节,范围(-8388608~8388607) |
int(n) | 4个字节(32个比特位),整数型,范围(-2147483648~2147483647) |
bigint(n) | 8个字节,整数型,范围(+-9.22*10的18次方) |
float(m,d) | 单精度浮点,8位精度,4字节32位。m数字总个数,d小数位 |
double(m,d) | 双精度浮点,16位精度,8字节64位 。m总个数,d小数位 |
char | 固定长度的字符类型 |
varchar | 可变长度的字符类型 |
text | 文本 |
image | 图片 |
decimal(5,2) | 5个有效长度数字,小数点后面有2位(例如123.56) |
具体说明:
1、int(N)
int(N)中的N不是限制字段取值范围的,int的取值范围是固定的(0至4294967295)或(-2147483648至2147483647)。N这个值是为了zerofill在字段中的值不够时补零的。
int默认是signed(有符号),取值范围(-2147483648至2147483647)。如果加了unsigned( 无符号)参数那么取值范围就为(0至4294967295)。
2、float(m,d)
设一个字段定义为float(6,3),表示6个有效长度数字,小数点后面有3位。如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。如果插入数12.123456,存储的是12.123,如果插入12.12,存储的是12.120。
整数部分最大是3位,如果插入1234.56,会插入失败。
3、char与varchar
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:
值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
---|---|---|---|---|
'' | ' ' | 4个字节 | '' | 1个字节 |
'ab' | 'ab ' | 4个字节 | 'ab ' | 3个字节 |
'abcd' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
'abcdefgh' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
字节大小:
- char无论是否有值,都会占用固定长度的字节大小,保存在磁盘上都是4字节。
- varchar在保存字符时,默认会加一个隐藏的结束符,因此结束符会多算一个字节。
优劣比较:
- varchar比char节省磁盘空间。
- 但varchar类型的数据读写速度比char慢,因为char是连续的磁盘空间,e而varchar在多次增删改查中会产生一些磁盘空间碎片。
二、查看数据库结构
mysql中的命令默认不区分大小写。表名和库名区分大小写。
在linux系统中的客户端,命令后一定要加分号。
1、查看当前服务器中的数据库
bash
复制代码
SHOW DATABASES; #大小写不区分,分号“;"表示结束
2、查看数据库中包含的表
csharp
复制代码
方法一: USE 数据库名; #切换库 SHOW TABLES; #查看库中的表 方法二: show tables from 数据库名; #直接查看某个库中的表 #例如:show tables from mysql;
3、查看表的结构(字段)
ini
复制代码
方法一: USE 数据库名; #切换库 DESCRIBE 表名; #查看指定表的结构 方法二: DESCRIBE [数据库名.]表名; 可缩写成:DESC 数据库名.表名; #例如:desc mysql.user;
三、SQL语句
关系型数据库,都是使用SQL语句来管理数据库中的数据。
SQL,即结构化查询语言(Structured Query Language) 。
SQL语句用于维护管理数据库,包括数据查询、数据更新、访问控制、对象管理等功能。
3.1 SQL中的名词介绍
- 数据库:database
- 表:table,行:row 列:column
- 索引:index
- 视图:view
- 存储过程:procedure
- 存储函数:function
- 触发器:trigger
- 事件调度器:event scheduler,任务计划
- 用户:user
- 权限:privilege
3.2 SQL语言规范
- 在数据库系统中,SQL 语句不区分大小写,建议用大写。
- SQL语句可单行或多行书写,默认以 " ; " 结尾。
- 关键词不能跨多行或简写。
- 用空格和TAB 缩进来提高语句的可读性。
- 子句通常位于独立行,便于编辑,提高可读性。
数据库对象和命名:
数据库的组件(对象):
- 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。
命名规则:
- 必须以字母开头,后续可以包括字母,数字和三个特殊字符(# _ $)
- 不要使用MySQL的保留字,如table、select、show、databases。
- 数据库名、表名、用户名区分大小写
3.3 SQL语言分类
1、DDL:数据定义语言(Data Definition Language),用于创建数据库对象,如库、表、索引等。
例如:CREATE,DROP,ALTER 等。
2、DML:数据操纵语言(Data Manipulation Language),用于对表中的数据进行管理。
例如: SELECT、UPDATE、INSERT、DELETE 等。
3、DQL:数据查询语言( Data Query Languag ),用于从数据表中查找符合条件的数据记录。
例如: SELECT
4、DCL:数据控制语言(Data Control Language),用于设置或者更改数据库用户或角色权限
例如: GRANT,REVOKE
5、TCL:事务控制语言(Transaction Control Language),用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。
例如: COMMIT,ROLLBACK,SAVEPOINT
四、创建及删除数据库和表
4.1 创建新的数据库
ini
复制代码
CREATE DATABASE 数据库名; #例:CREATE DATABASE school;
4.2 创建新的表
语句格式:
ini
复制代码
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]); #主键一般选择能代表唯一性的字段,不允许取空值(NULL),值也不允许重复,主键字段的值是唯一的。一个表只能有一个主键。
示例:
sql
复制代码
USE school; #切换school库 CREATE TABLE class01 (id int NOT NULL,name char(10) NOT NULL,score decimal (5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id)); #新建表class01 #第一个字段名id,数据类型是int整数型,NOT NULL不许为空值。 #第二个字段名name,数据类型是char固定长度的字符,长度是10个字节。 #第三个字段名score,数据类型是decimal (5,2),表示5个有效数字、其中小数点后2位。 #第四个字段名passwd,DEFAULT '' 表示默认值是无值(不是空值null)。 #PRIMARY KEY (id),表示id字段是主键。 DESC class01; #查看class01表的结构
4.3 删除指定的数据表
sql
复制代码
DROP TABLE [数据库名.]表名; #例:DROP TABLE class01;
4.4 删除指定的数据库
ini
复制代码
DROP DATABASE 数据库名; #例:DROP DATABASE school;
五、管理表中的数据记录(表数据的增删改查)
5.1 向数据表中插入新的数据记录(增)
5.2 查询表数据记录(查)
5.3 修改/更新数据表中的数据记录(改)
5.4 在数据表中删除指定的数据记录(删)
删除数据记录时,一定要加条件,否则会将整个表都删除!
sql
复制代码
DELETE FROM 表名 [WHERE 条件表达式]; #删除一定要加条件,不然会删除整个表 例: DELETE FROM class01 WHERE id=2; #删除id字段值为2的数据记录
六、修改表名和表结构
修改表名和表结构时,都要使用ALTER
语句!
6.1 修改表名 rename
6.2 扩展表结构(增加字段) add
6.3 修改字段名,添加唯一健 change
CHANGE可修改字段名、数据类型、约束等所有项。
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 [数据类型] [约束]; 例: ALTER TABLE class01 CHANGE score phone int unique key; #将score字段名修改为phone,数据类型修改为int整数型,并添加唯一键约束。
6.4 删除字段 drop
ALTER TABLE 表名 DROP 字段名; 例: ALTER TABLE class01 DROP passwd; #删除passwd字段
6.5 添加主键约束 add primary key
方法1:创建表时设置主键
create table 表名(字段1 XXX, 字段2 XXX, ....primary key(字段)); create table 表名(字段1 XXX primary key, . . ..); #将主键作为字段1的属性
方法2:在现有表中添加主键
ALTER TABLE 表名 add primary key(字段名); 例: ALTER TABLE class01 add primary key(id); #将id字段添加为主键
主键primary key 和 唯一键unique key:
共同点:字段的值都是唯一的,不允许有重复值。
不同点:
- 自定义的表中只能有一个主键,但是可以有多个唯一键。
- 主键字段中不允许有null值,唯一键允许有null值。
(系统的表中可以有多个主键)
七、MYSQL常见的约束
- 主键约束(primary key) PK
- 自增长约束(auto_increment)
- 非空约束(not null)
- 唯一性约束(unique)
- 默认值约束(default)
- 零填充约束(zerofill)
- 外键约束(foreign key)FK
案例演示:
下面的案例将一次性演示:主键约束、自增长约束、非空约束、唯一性约束、默认约束、零填充约束。
插入数据记录:
1)因为id字段设置了自增长,如果不指定id字段值,则默认从1开始递增;
int(4) zerofill ,设置了零填充约束,如果数值不满4位数,则前面用“0”填充到4位,所以下面案例中的id值为0001。
sql
复制代码
INSERT INTO tt01(name,cardid,hobby) values('小明',1101,'running');
2)再次插入数据记录,不指定id字段值,则id值会自增1,此时为0002。
sql
复制代码
INSERT INTO tt01(name,cardid,hobby) values('小红',1102,'running');
3)自增长,如果添加失败也会自动递增一次,数值会被占用。
#下面两条命令会执行失败,因为cardid设置了唯一键不能重复。但执行失败,id值也会自增,0003、0004这两个数值会被占用。 INSERT INTO tt01(name,cardid,hobby) values('小强',1102,'singing'); INSERT INTO tt01(name,cardid,hobby) values('小方',1102,'running'); #下面这条命令执行成功后,id值是0005,因为0003和0004被执行失败的命令占用了。 INSERT INTO tt01(name,cardid,hobby) values('小丽',1103,'singing');
4)插入数据记录,指定id值为10。
则后面插入的数据就会从10开始自增1,即增长为11。
INSERT INTO tt01 values(10,'小强',1104,'singing'); #指定id值为10 INSERT INTO tt01(name,cardid,hobby) values('小方',1105,'dancing'); #后面插入的数据id值是11
5)cardid字段设置了唯一键约束,如果字段值重复,则会数据会插入失败。
#cardid重复,数据会添加失败 INSERT INTO tt01(name,cardid,hobby) values('小平',1105,'dancing');
6)name字段设置了不允许为NULL,且默认值为“匿名”,则插入数据时如果不指定name字段的值,该字段会自动插入默认值“匿名”。
INSERT INTO tt01(cardid,hobby) values(1106,'dancing');
总结
查看数据库和表:
show databases;
use 库名;
show tables;
desc 表名;
desc 表名\G
show create table 表名;
创建及删除数据库和表:
create database 库名;
create table 表名(字段1 数据类型 [属性], 字段2 .....);
DROP TABLE [数据库名.]表名;
DROP DATABASE 数据库名;
表数据的增删改查:
insert into 表名 values (所有字段的值);
insert into 表名 (指定字段名称) values(字段的值);
delete from 表名 [where ...];
truncate table 表名; #删除表中所有数据。相当于格式化,速度很快
update 表名 set 字段=值[, 字段2=....] [where ...];
select 字段1[,字段2....] from 表名 [where ...];
select * from 表名;
修改表名和表结构:
alter table 表名 RENAME/ADD/CHANGE/DROP 字段名 数据类型 属性;
alter table 表名 ADD 键名(字段); //给指定字段添加键约束
注:零填充约束、非空约束、默认约束、自增长约束不能通过ADD方式添加,可以在建表时设置,或者CHANGE修改字段时添加。
主键和唯一键:
主键:primary key
唯一键:unique key
共同点: 字段的值都是唯一性,不允许有重复的值
不同点:
- 一个表中 只能有1个主键,但是可以有多个唯一键
- 主键字段中不允许有null值,唯一键是允许有null
添加主键字段的三种方法:
创建表时指定主键字段的两种方式:
- create table 表名 (字段1 XXX,字段2 xxx,...,primary key(字段));
- create table 表名 (字段1 XXX primary key, ....);
在现有表中添加主键:
- alter table 表名 ADD primary key(字段);