网站site的收录数量要多远索引量,暴雪时分电视剧免费观看,怎样建设外贸网站,免费发帖推广平台有哪些数据库管理及数据类型 一#xff1a;数据类型
1.数值类型
整数类型
整数类型#xff1a;TINYINT SMALLINT MEDIUMINT INT BIGINT
作用#xff1a;用于存储用户的年龄、游戏的Level、经验值等 浮点数类型
浮点数类型#xff1a;FLOAT DOUBLE
作用#xf… 数据库管理及数据类型
一数据类型
1.数值类型
整数类型
整数类型TINYINT SMALLINT MEDIUMINT INT BIGINT
作用用于存储用户的年龄、游戏的Level、经验值等 浮点数类型
浮点数类型FLOAT DOUBLE
作用用于存储用户的身高、体重、薪水等 float(5,3) 5宽度 3精度
注意宽度不算小数点宽度-精度点前
案例
MySQL [(none)] create database diandian;
Query OK, 1 row affected (0.00 sec)MySQL [(none)] use diandian
Database changed
MySQL [diandian] create table t1(id float(6,2));
Query OK, 0 rows affected (0.24 sec)MySQL [diandian] insert into t1 values(2.22);定点数类型
定点数类型DEC
定点数在MySQL内部以字符串形式存储比浮点数更精确适合用来表示货币等精度高的数据
位类型
位类型BIT
BIT(M)可以用来存放多位二进制数M范围从1~64如果不写默认为1位
2.字符串类型
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型
枚举类型枚举列可以把一些不重复的字符串存储成一个预定义的集合
mysql create table enum_table( e ENUM(fish,apple,dog));
Query OK, 0 rows affected (0.35 sec)
mysql insert into enum_table(e) values(fish);
Query OK, 1 row affected (0.11 sec)mysql select * from enum_table;
------
| e |
------
| fish |
------
1 row in set (0.00 sec)mysql insert into enum_table(e) values(nihao);
ERROR 1265 (01000): Data truncated for column e at row 1时间和日期类型
时间和日期类型DATE TIME DATETIME TIMESTAMP YEAR
作用用于存储用户的注册时间文章的发布时间文章的更新时间员工的入职时间等 mysql create table t8 (id1 timestamp NOT NULL default CURRENT_TIMESTAMP,id2 datetime default NULL
);timestamp 类型的列还有个特性默认情况下在 insert, update 数据时timestamp 列会自动以当前时间CURRENT_TIMESTAMP填充/更新。“自动”的意思就是你不去管它MySQL 会替你去处理。mysql insert into t8(id1) values(20180109000000);
mysql select * from t8;
---------------------------
| id1 | d2 |
---------------------------
| 2018-01-09 00:00:00 | NULL |
---------------------------
1 row in set (0.00 sec)扩展
select now();查看当前时间二表操作
1.案例
表:school.student1
字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录语法
create table 表名(自定义)(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
在同一张表中字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的创建库表
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 show tables;
------------------
| Tables_in_school |
------------------
| student1 |
------------------
1 row in set (0.00 sec)插入语法
insert into 表名(字段1,字段2...) values(字段值列表...);插入数据
mysql insert into student1(id,name,sex,age) values(1,xingdia,m,26);查看表结构
mysql desc student1;
-------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum(m,f) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
-------------------------------------------------
4 rows in set (0.00 sec)查询数据
mysql select id,name,sex,age from student1; //查询表中所有字段的值
Empty set (0.00 sec)mysql select * from student1; /查询表中所有字段的值
Empty set (0.00 sec)mysql select name,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)扩展插入
mysql insert into student1 values (1,xingdian,m,33),(2,alice,m,20),(3,jack,m,40); //顺序插入
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql insert into student1(name,age) values (zhuzhu,10),(gougou,20); //只向指定的字段插入值
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 02.案例
表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 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);注意
DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名; 查看表详细结构
SHOW CREATE TABLE 表名;三表完整性约束
1.作用
用于保证数据的完整性和一致性
2.约束条件
PRIMARY KEY (PK) 标识该字段为该表的主键可以唯一的标识记录不可以为空 UNIQUE NOT NULL
FOREIGN KEY (FK) 标识该字段为该表的外键实现表与表父表主键/子表1外键/子表2外键之间的关联
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的可以为空一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长整数类型而且为主键
DEFAULT 为该字段设置默认值
注意
是否允许为空默认NULL可设置NOT NULL字段不允许为空必须赋值
字段是否有默认值缺省的默认值是NULL如果插入记录时不给字段赋值此字段使用默认值
MySQL [(none)] sex enum(male,female) not null default male
MySQL [(none)] age int unsigned NOT NULL default 20 必须为正值无符号 不允许为空 默认是20 是否是key 主键 primary key 外键 forengn key
3.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 [(none)] insert into school.student4(id,name) values(2,robin);
Query OK, 1 row affected (0.08 sec)MySQL [(none)] select * from school.student4;
---------------------------------
| id | name | sex | age | hobby |
---------------------------------
| 2 | robin | m | 18 | dance,book |
---------------------------------
1 row in set (0.00 sec)注意报错的原因
MySQL [(none)] insert into school.student4 values(3,NULL,m,40,book);
ERROR 1048 (23000): Column name cannot be null4.唯一约束
作用
MySQL索引的建立对于MySQL的高效运行是很重要的索引可以大大提高MySQL的检索速度
表company.department1
创建表
MySQL [(none)] create database company;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)] CREATE TABLE company.department1 (dept_id INT,dept_name VARCHAR(30) UNIQUE,comment VARCHAR(50));
Query OK, 0 rows affected (0.34 sec)查看表结构
MySQL [(none)] desc company.department1;
---------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
---------------------------------------------------
3 rows in set (0.00 sec)数据插入注意查看插入数据时的提示
MySQL [(none)] insert into company.department1 values (1,xingdian,hr);
Query OK, 1 row affected (0.04 sec)MySQL [(none)] insert into company.department1 values (1,xingdian,hr);
ERROR 1062 (23000): Duplicate entry xingdian for key dept_name5.主键约束
注意primary key 字段的值是不允许重复且不允许不NULLUNIQUE NOT NULL
表school.student6
创建表
MySQL [(none)] create table school.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.47 sec)插入数据
MySQL [(none)] insert into school.student6 values (1,alice,female,22);
Query OK, 1 row affected (0.18 sec)MySQL [(none)] insert into school.student6(name,sex,age) values- (jack,male,19),- (tom,male,23);
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0MySQL [(none)] select * from school.student6;
------------------------
| id | name | sex | age |
------------------------
| 1 | alice | female | 22 |
| 2 | jack | male | 19 |
| 3 | tom | male | 23 |
------------------------
3 rows in set (0.00 sec)
四修改表
语法格式
修改表名
ALTER TABLE 表名 RENAME 新表名;
增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 删除字段
ALTER TABLE 表名 DROP 字段名;
修改字段
ALTER TABLE 表名 MODIFY 字段名 新数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
1.修改数据库引擎
mysql alter table service engineinnodb; //enginemyisam|memory|....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;4.修改字段类型
MySQL [school] desc student10;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.01 sec)MySQL [school] alter table student10 modify age tinyint not null ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| id | int(11) | YES | | NULL | |
| age | tinyint(4) | NO | | NULL | |
----------------------------------------------
2 rows in set (0.00 sec) MySQL [school] alter table student10 modify id int not null primary key ; 修改字段类型、约束、主键
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| age | tinyint(4) | NO | | NULL | |
----------------------------------------------5.增加约束
MySQL [school] alter table student10 modify id int not null primary key ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| age | tinyint(4) | NO | | NULL | |
----------------------------------------------
2 rows in set (0.00 sec)MySQL [school] alter table student10 modify id int not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined //错误该字段已经是primary keyMySQL [school] alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
-------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | tinyint(4) | NO | | NULL | |
-------------------------------------------------------6.增加主键
MySQL [school] desc student1;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
-------------------------------------------
3 rows in set (0.00 sec)MySQL [school] alter table student1 add primary key(id);MySQL [school] desc student1;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
-------------------------------------------
3 rows in set (0.00 sec)7.修改主键和自增
MySQL [school] alter table student1 modify id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student1;
----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
----------------------------------------------------8.删除主键
MySQL [school] desc student10;
-------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | tinyint(4) | NO | | NULL | |
-------------------------------------------------------
2 rows in set (0.00 sec)MySQL [school] alter table student10 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key删除自增
ySQL [school] alter table student10 modify id int not null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| age | tinyint(4) | NO | | NULL | |
----------------------------------------------MySQL [school] alter table student10 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0MySQL [school] desc student10;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| id | int(11) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
----------------------------------------------
2 rows in set (0.00 sec)9.复制表
复制表结构记录 key不会复制: 主键、外键和索引复制表结构/记录表结构不会将Key复制
mysql create table new_service select * from service;只复制表结构
mysql create table new1_service select * from service where 12; //条件为假查不到任何记录可以复制主键,只复制表结构
mysql create table t4 like employees;10.删除表
mysql DROP TABLE 表名;11.修改数据表中字段的值
语法
Update 表名 set 列名值where 条件
mysql update student set name123 where id1;删除某一行
delete from 表名 where id1
mysql delete from type where id1;五库操作
1.简介
系统自带库的含义及作用
MySQL [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------information_schema虚拟库主要存储了系统中的一些数据库对象的信息例如用户表信息、列信息、权限信息、字符信息等
performance_schema主要存储数据库服务器的性能参数
mysql授权库主要存储系统用户的权限信息
sys主要存储数据库服务器的性能参数
注意information_schema
SCHEMATA 存放的是系统中的库
MySQL [information_schema] select * from information_schema.SCHEMATA;
------------------------------------------------------------------------------------------------
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
------------------------------------------------------------------------------------------------
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | school | latin1 | latin1_swedish_ci | NULL |
| def | sys | utf8 | utf8_general_ci | NULL |
------------------------------------------------------------------------------------------------目录_名称
实际库_名称
默认_字符_设置_名称
默认_分类_名称 TABLES 存储表名
MySQL [information_schema] select * from information_schema.TABLES\G
*************************** 283. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: xingdianTABLE_NAME: t1TABLE_TYPE: BASE TABLEENGINE: InnoDBVERSION: 10ROW_FORMAT: DynamicTABLE_ROWS: 1AVG_ROW_LENGTH: 16384DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0INDEX_LENGTH: 0DATA_FREE: 0AUTO_INCREMENT: NULLCREATE_TIME: 2022-09-22 08:18:38UPDATE_TIME: 2022-09-22 08:18:54CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ciCHECKSUM: NULLCREATE_OPTIONS: TABLE_COMMENT:
283 rows in set (0.02 sec) COLUMNS 存储字段
*************************** 3083. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: xingdianTABLE_NAME: t1COLUMN_NAME: idORDINAL_POSITION: 1COLUMN_DEFAULT: NULLIS_NULLABLE: YESDATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULLCHARACTER_OCTET_LENGTH: NULLNUMERIC_PRECISION: 10NUMERIC_SCALE: 0DATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLCOLUMN_TYPE: int(11)COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,referencesCOLUMN_COMMENT: GENERATION_EXPRESSION:
3083 rows in set (0.03 sec)2.创建库
方案一交互式操作
mysql create database xingdian;数据库命名规则
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
方案二非交互式
mysql -u root -pQianFeng123 -e create database diandian3.查看数据库
mysql show databases;
mysql show create database xingdian;
mysql select database(); 查看当前所在的库4.切换数据库
mysql use xingdian;5.删除数据库
mysql DROP DATABASE 数据库名;NUMERIC_SCALE: 0DATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLCOLUMN_TYPE: int(11)COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,referencesCOLUMN_COMMENT: GENERATION_EXPRESSION: 3083 rows in set (0.03 sec) #### 2.创建库方案一交互式操作shell
mysql create database xingdian;数据库命名规则
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
方案二非交互式
mysql -u root -pQianFeng123 -e create database diandian3.查看数据库
mysql show databases;
mysql show create database xingdian;
mysql select database(); 查看当前所在的库4.切换数据库
mysql use xingdian;5.删除数据库
mysql DROP DATABASE 数据库名;
