数据表操作（三）

时间：2021-02-07 18:46:40

一、数据表的操作

1、创建表

create table 表名(
    列名  类型  是否可以为空，
    列名  类型  是否可以为空
　  )ENGINE=InnoDB DEFAULT CHARSET=utf8
#查看数据表创建语句
show create table t5 \G

　　

2、清空表

delete from 表名
truncate table 表名

3、删除表

drop table 表名

 4、engine

　　ISAM - 优点：速度快，磁盘空间占用少；某个库或表的磁盘占用情况既可以通过操作系统查相应的文件（夹）的大小得知，也可以通过SQL语句SHOW TABLE STATUS查得

　　　　　 缺点：没有数据完整性机制，即不支持事务和外键

　　　　    如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。 　　

 

　　InnoDB - 优点：支持事务和外键，数据完整性机制比较完备；可以用SHOW TABLE STATUS查得某个库或表的磁盘占用

　　　　　　 缺点：速度超慢，磁盘空间占用多；所有库都存于一个（通常情况）或数个文件中，无法通过操作系统了解某个库或表的占用空间

 

　　BDB - 优点：支持事务，不支持外键，由于在事务支持的基础上，外键可以在数据库的客户端（可能是最终客户的服务器端，例如php）间接实现，所以数据完整性仍然是有保障的；

　　　　　缺点：速度慢，磁盘占用多；不能通过SHOW TABLE STATUS查询某个库或表的空间占用；

　　　　　用操作系统可了解库相应的文件夹，或表相应的文件的大小，但由于BDB表总是还要产生log文件，而实际的磁盘占用应该把log文件也包含在内，所以用操作系统查得某库或表的大小总是小于实际占用空间。

 5、自增列　

#一个表中只能有一个自增列且必须为主键
#插入值时忽略自增列
create talbe t3(
    id int auto_increment primary key,
    name varchar(20)
)
#修改自增列的当前值
alter table t10 auto_increment=20;

　

6、会话级别 - 表示当前连接

show session variables like 'auto_inc%'  查看会话级别的全局变量
set session auto_increment_increment=2;   设置会话步长
set session auto_increment_offset=2;   设置会话起始值

7、全局级别 - 表示所有连接

show global variables like 'auto_inc%';
set global auto_increment_increment=200
set global auto_increment_offset=10
当全局修改时，所有登录的用户均根据全局设置

 

 

二、数据表约束

1、可空约束

not null    - 不可空
null        - 可空

2、默认值约束

create table tb1(
nid int not null defalut 2,
num int not null
)

3、主键约束

    #一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )

4、外键约束

 　　#一种特殊的索引，只能是指定内容
            creat table color(
                nid int not null primary key,
                name char(16) not null
            )

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )
　　　　　　　# fk_cc为约束名（不能重复） - foreign key(外键名) references 表名(列名)　
　　　　　　　# 当主键为两列时，对应的外键也必须为两列，如constraint fk_t1_t2 foreign key (id1,id2 references t1(nid,pid)

　　

 5、唯一约束

#不能重复、加速查找，与主键的区别为可以为空
unique 约束名(列名)
unique 约束名(列名1，列名2)    列名1+列名2 联合唯一

　　

 

 

 

三、数据表操作

1、增加数据

insert into 表 (列名1,列名2...) values (值1,值2,值3...)
insert into 表 (列名1,列名2...) values (值1,值2,值3...),(值1,值2,值3...)
insert into 表 (列名1,列名2...) select (列名1,列名2...) from 表
示例:
insert into tb(name,age) values('alex',12),('root',18)
insert into tb12(name,age) select name,age from tb12

　　

2、删除数据

delete from 表
delete from 表 where id＝1 and name＝'alex'

3、修改数据

update 表 set name ＝ 'alex' where id>1
update tbl2 set name='alex' where id>12 name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'

　　

　　

四、数据的基本类型　　

1、数值型

　　1）tinyint - 小整数，数据类型用于保存一些范围的整数数值范围

　　　　unsigned - 0 ～ 255

　　　　signed - -128 ～ 127

　　　　PS: MySQL中无布尔值，使用tinyint(1)构造

　　2)  int[(m)] - 整数，数据类型用于保存一些范围的整数数值范围

　　　　unsigned - 0 ～ 4294967295

　　　　signed - -2147483648 ～ 2147483647

　　　　PS：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002

　　3）bigint[(m)] - 大整数，数据类型用于保存一些范围的整数数值范围

　　　　unsigned - 0 ～ 18446744073709551615

　　　　signed - -9223372036854775808～ 9223372036854775807

　　4）decimal[(m[,d])] - 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30

　　　　PS: 对于精确数值计算时需要用此类型，decaimal能够存储精确值的原因在于其内部按照字符串存储

　　5）FLOAT[(M,D)] - 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数

　　　　unsigned - 1.175494351E-38 to 3.402823466E+38

　　　　signed - -3.402823466E+38 to -1.175494351E-38

　　　　PS：数值越大，越不精准

　　6）DOUBLE[(M,D)]  - 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数

　　　　unsigned - 2.2250738585072014E-308 to 1.7976931348623157E+308

　　　　signed - -1.7976931348623157E+308 to -2.2250738585072014E-308

2、字符串型

　　1）char (m) - 用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度

　　　　PS: 即使数据小于m长度，也会占用m长度

　　2）varchar(m) - 用于表示变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中

　　　　PS：varchar占用空间少，但char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

　　　　　　 因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡，创建数据表时将定长的数据列往前放，先检索定长列执行速度会得到优化

　　3）text - text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符

　　4）mediumtext - A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters

　　5）longtext - A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters

　　　 PS：当文件长度超出longtext时，则应保存在服务器硬盘上，数据库中保存指向文件的路径　

　　6）关于字符长度

　　　  　　UTF－8：一个汉字＝3个字节

　　　　　 GBK：一个汉字＝2个字节

　　　　　varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别，MySQL 检查长度，可用 SQL 语言来查看：

　　　　select LENGTH(fieldname) from tablename

3、日期型

　　1）DATE - YYYY-MM-DD（1000-01-01/9999-12-31）

 　   2）TIME - HH:MM:SS（'-838:59:59'/'838:59:59'）

　    3)  DATETIME - YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y） 　　

 　   4）TIMESTAMP - YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

 　　5）YEAR - YYYY（1901/2155）

 4、枚举类型

　　　　enum - An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)　

    　CREATE TABLE shirts (
      　　　　              name VARCHAR(40),
             　　　　       size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                　　　　　　);
     INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

　　

 5、集合类型

　　　　set - A SET column can have a maximum of 64 distinct members

      CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
      INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

6、二进制类型

　　　　bit[(M)] - 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

　　　　TINYBLOB - 不超过 255 个字符的二进制字符串，0-255字节

　　　　BLOB - 二进制形式的长文本数据，0-65 535字节

　　　　MEDIUMBLOB - 二进制形式的中等长度文本数据，0-16 777 215字节

　　　　LONGBLOB - 二进制形式的极大文本数据，0-4 294 967 295字节

 

 

 

 

 

 

 

　

 

标签：

相关文章