1、 操作数据库
- 创建数据库
create database if not exists hello;
- 删除数据库
drop database if exists hello
- 使用数据库
use hello
如果表明或者字段名是一个特殊字符,就需要带 - 查看数据库
show databases --查看所有的数据库
- 查看数据库定义语句
show create database school
- 查看表格定义语句
show create table teacher
- 查看表结构
DESC teacher
2. 常用表的列类型
-
数值型——可以不加引号
TINYINT: 微整型,占一个字节, 范围 -128~127 SMALLINT: 小整型,占2个字节,范围 -32768~32767 INT: 整型,占4个字节 BIGINT: 大整型,占8个字节 FLOAT: 单精度浮点型,占4个字节,最大3.4e38,可能产生误差 DOUBLE: 双精度浮点型,占8个字节,可能产生误差 DECIMAL(M,D): 定点小数,小数点不会变化,几乎也不会产生误差,M代表总的有效数,D代表小数点后的有效位数(工资、价格.....) BOOL/BOOLEAN: 通常用于存储两个值得数据, TRUE/FALSE。真正存储时 true转为1,FALSE转为0。因为Mysql中没有真正的布尔型,最终会自动转为微整型TINYINT。
-
日期时间型——必须加引号
DATE: 日期型 2020-4-12 TIME: 时间型 16:48:25 DATETIME: 日期时间型 2020-4-2 16:48:25 TIMESTAMP:时间戳,1970.1.1到现在的毫秒数 YEAR: 年份
-
字符串型——必须加引号
VARCHAR(M): 变长字符串,不会产生空间浪费,操作速度相对慢,M的最大值为65535
CHAR(M): 定长字符,可能会产生空间浪费,操作速度相对快,往往存储一些固定长度的数据(如手机号码、身份证号等),M的最大值为255
TINYTEXT:微型文本,大小为2-1
TEXT(M): 大型变长字符串,M的最大值是2G.
3. 数据库的字段属性
1.auto_increment
auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。
MySQL要求将auto_increment属性用于作为主键的列。此外,每个表只允许有一个auto_increment列。例如:id smallint not null auto_increment primary key
2.binary
binary属性只用于char和varchar值。当为列指定了该属性时,将以区分大小写的方式排序。与之相反,忽略binary属性时,将使用不区分大小写的方式排序。例如:hostname char(25) binary not null
3.default
default属性确保在没有任何值可用的情况下,赋予某个常量值,这个值必须是常量,因为MySQL不允许插入函数或表达式值。此外,此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性,没有指定默认值时默认值将为NULL,否则默认值将依赖于字段的数据类型。例如:subscribed enum('0', '1') not null default '0'
4.index
如果所有其他因素都相同,要加速数据库查询,使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组,每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组,与搜索整个未索引的表相比,这将在性能方面得到极大的提升。
create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);
我们也可以利用MySQL的create index命令在创建表之后增加索引:create index lastname on employees (lastname(7));
这一次只索引了名字的前7个字符,因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好,所以应当在实践中尽量使用小的索引。
5.not null
如果将一个列定义为not null,将不允许向该列插入null值。建议在重要情况下始终使用not null属性,因为它提供了一个基本验证,确保已经向查询传递了所有必要的值。
6.null
为列指定null属性时,该列可以保持为空,而不论行中其它列是否已经被填充。记住,null精确的说法是“无”,而不是空字符串或0。
7.primary key
primary key属性用于确保指定行的唯一性。指定为主键的列中,值不能重复,也不能为空。为指定为主键的列赋予auto_increment属性是很常见的,因为此列不必与行数据有任何关系,而只是作为一个唯一标识符。主键又分为以下两种:
(1)单字段主键
如果输入到数据库中的每行都已经有不可修改的唯一标识符,一般会使用单字段主键。注意,此主键一旦设置就不能再修改。
(2)多字段主键
如果记录中任何一个字段都不可能保证唯一性,就可以使用多字段主键。这时,多个字段联合起来确保唯一性。如果出现这种情况,指定一个auto_increment整数作为主键是更好的办法。
8.unique
被赋予unique属性的列将确保所有值都有不同的值,只是null值可以重复。一般会指定一个列为unique,以确保该列的所有值都不同。例如:email varchar(45) unique
9.zerofill
zerofill属性可用于任何数值类型,用0填充所有剩余字段空间。例如,无符号int的默认宽度是10;因此,当“零填充”的int值为4时,将表示它为0000000004。例如:orderid int unsigned zerofill not null
10.unsigned
无符号的整数,声明了该列不能声明为负数
每一个表都必须存在以下五个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
4. 创建数据库表
通用语法:
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释];
实例:
CREATE TABLE
IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT 'HELLO' COMMENT 'HELLO',
`password` VARCHAR ( 20 ) NOT NULL,
`sex` VARCHAR ( 2 ) NOT NULL ,
`birthday` DATETIME DEFAULT NULL,
`address` VARCHAR ( 50 ) DEFAULT NULL,
`email` VARCHAR ( 30 ) DEFAULT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = UTF8;
5、数据表的类型
5.1 数据库引擎
- INNODB MySQL默认引擎
- MYISAM 较早时间使用的引擎
常规使用操作:
- MYISAM 节约空间、速度较快、支持全文索引
- INNODB 安全性高、事务处理速度快、多表多用户操作
MySQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm -表结构的定义文件
- *.MYD -数据文件(data)
- *.MYI -数据索引(index)
设置数据库表的字符集编码
charset=utf8
不设置的情况下,会是MySQL默认的字符集编码,MySQL的默认编码是Latin1,不支持中文
6、修改删除表
修改
-- 所有的创建和删除操作尽量加上判断,避免报错
-- 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
-- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
-- 修改表的字段(重命名、修改约束)
-- change用来字段重命名,不能修改字段类型和约束
-- ALTER TABLE 表名 MODIFY 字段名 列属性
-- modify不能用来字段重命名,只能修改字段类型和约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher CHANGE age age1 int(1) -- 重命名
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher DROP age1
删除
-- 删除表,如果表存在再删除
DROP TABLE IF EXISTS teacher