如何高效掌握MySQL数据库基础笔记？

MySQL数据库 sql语句分类 分类 全称 说明 DDL 数据库定义语言，用来定义数据库（数据库，表，字段） DML 数据操作语言，用来对数据表中的数据进行增删改 DQL 数据查询语言，用来查询数据库中的表的记录 DCL 数据控制语言，用来创建数据库、控制数据库的访问权限 DDL语句 DDL操作数据库 查询： 查询所有数据库: SHOW DATABASES; 查询当前数据库： select database(); 创建： create database [if not exists]数据库名 [default charset 字符集] [collate 排序规则];(字符集用utf8mb4)长度为4个字节 删除： drop database [if exists] 数据库名; 使用： use 数据库名; 查询当前数据库所有表： show tables; 查询表的结构： desc 表名; 查询指定表的建表语句： show creata table 表名; 创建数据表： create table 表名( 字段1 类型[comment 字段注释], 字段2 类型, 字段3 类型, ...... 字段n 类型 )[comment 表注释]; DDL-数据类型 MySQL中数据类型很多，主要分为3类：数值型、字符串型、日期时间类型 数值类型 MySQL 支持所有标准 SQL 数值数据类型。 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。 作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。 类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 Bytes (-128，127) (0，255) 小整数值 SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值 日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。 TIMESTAMP类型有专有的自动更新特性，将在后面描述。 类型 大小 ( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' YYYY-MM-DD hh:mm:ss 混合日期和时间值 TIMESTAMP 4 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳 字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。 类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据 注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。 CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 char(10)最多只能放十个，不满十个用空格补全，性能较好，定长字符（空间换时间） varchar(10)最多只能放10个，存储一个字符只占一个字符，性能较差，边长字符在使用过程中需要计算长度，（时间换空间） BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。 BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。 易错 列名应该在类型之前，不需要使用单引号： 列名（例如'编号'，'员工工号'）应该在类型（例如 INT，VARCHAR）之前，而且不需要使用单引号。 CHAR 类型的长度不需要指定为 1： 当使用 CHAR 类型时，不需要指定长度为 1。如果你希望性别只存储一个字符，可以保留 CHAR 类型而不用指定长度。 UNSIGNED 关键字放在 INT 后面： 如果你希望年龄是无符号整数，应该将 UNSIGNED 放在 INT 后面。 去掉最后一个逗号： 列定义的最后一个后面不需要逗号。 这个修改后的语句应该能够正确创建一个名为 renshi 的员工表。 MySQL中的主键 主键的定义 主键：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。 use scholldb; create table student( 学号 char(10) not null primary key, //设置主键为学号，且是唯一主键 姓名 char(10) not null ); 联合主键 关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。 use scholldb; create table student( 学号 char(10) not null ， 课程号 char(10) not null ， 成绩 float(5,2) ， primary key(学号,课程号) //联合外键为学号课程号 ); 查询表中主键的命令：desc (表名); MySQL中外键 保持数据的一致性、完整性。在一个库中使用多表中的主键定义一个外键，保持数据的完整性。 引用全部在一个表时数据太多太杂，所以拆为多个表，在表中设置唯一关字，然后作为表的主键，作为其他表的外键，当多表联合查询时作为关键字段 为了一张表记录的数据不要太过冗余。这和软件project的模块化思想差点儿相同类似，仅仅只是在数据库中是对表关系进行解耦，尽量让表 记录的数据单一化。假如一张学生表中把成绩和学生信息放在一张表中就太冗余了，成绩全然能够以学生的id作为区分标识。 列：在一个数据库中定义A表的外键来自B表中的C字段，此时A表作为B表的父表 创建表时创建外键 [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…] // 1、 create table student( 学号 char(11) not null foreign key, //在定义主键时添加外键。（在添加外键约束之前，必须确保引用表中的主键列已经定义） 姓名 char(10) not null ); 2、 FOREIGN KEY(deptId) REFERENCES tb_dept1(id) create table student( 学号 char(11) not null, 姓名 char(10) not nll, foreign key (学号) references course(学号) //创建数据表student，并在表student上创建外键约束，让它的键 学号 作为外键引表到 course表中 的学号字段 ); 对已有表创建外键 单个主键 ALTER TABLE student ADD FOREIGN KEY (班级编号) REFERENCES class(班级编号); //将表student中的班级编号字段作为外键引用class中的班级编号作为主键 多个主键 alter table score add foreign key (学号) references student(学号), add foreign key (课程号) references student(课程号); check完整性约束 Check完整性约束是一种用于限制表中数据值的约束。它定义了一个条件，该条件必须在插入或更新数据时得到满足。如果数据不符合约束条件，操作将被拒绝并抛出错误。 通过使用Check完整性约束，可以确保表中的数据满足特定的条件，例如范围、数据类型、格式等。这样可以防止非法或无效的数据进入表中，提高数据的准确性和一致性。 以下是创建Check完整性约束的一般语法： ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 CHECK (条件); 其中，"表名称" 是要添加约束的表名，"约束名称" 是约束的名称（可以自定义），"条件" 是定义约束的条件表达式。 数据插入 INSERT...VLAUES语句 insert into 表名 vlaues (值); insert into 表名(字段1,字段2,) values(值1,值2); REPLACE INSERT语句 此语句的作用是当我们在插入一条数据时，如果此条已经存在，那么先删除原来存在的数据再添加插入的数据，如果不存在那么直接插入新的数据。注意：却分是否存在是通过主键来确定的 replace into 表(字段) values(值); INSERT IGNORE INTO 语句 此语句的作用是如果插入的数据已经存在那么就忽略插入的数据（也就是不改变原来的数据），如果不存在则插入新的数据。注意：却分是否存在是通过主键来确定的 insert ignore into 表(字段) values (值); set插入 知道部分值插入数据，插入已知值，其他值为空（主键不能为空） insert into 表 set 字段1=‘值1’ , 字段2=‘值2’ ... ; 传递参数 MySQL中的limit 约束行数，输出约束行 其次第一行的量为0，而不是1 SELECT 字段名 FROM 表名 LIMIT offset , 列数; mysql 中的concat函数 作用：将多个字符串连接成一个字符串 concat (str1 , str2, ...) ; SELECT CONCAT(地区, '省或市') FROM student; #不添加新列查看 select concat（地区，'省或市'）as 新列名 from student ; #添加新列查看 UPDATE student SET 地区 = CONCAT(地区, '省或市'); # 直接更改数据 MySQL中数据更新/修改 只改想改的数据： update 表名 set 字段名=值 where 条件子句; update student set 出生日期='1998-02-10' where id = 1; #在表student中把id=1的记录字段为出生日期的数据改变为1998-01-10 修改全部数据： update 表名 set 字段名1=值1,字段名2=值2...,字段名n=值n; #对于NULL不能用=符号，要用is null 修改表结构： alter table 表名 修改的动作语法 列：alter table 表名 modify 字段名 数据类型 ; 列：aller table student modify 姓名 varchar(20) not null ; #修改student 中的姓名列的数据类型为varchar(20) 并设置为不允许包含空值 修改数据类型： alter table 表名 modify 字段 新数据类型; alter table c1 modify name varchar(20) not null; 修改字段名： alter table 表名 change 旧字段名 新字段名 新数据类型; alter table c3 change name name1 varchar(30) not null 修改表之增加主键： alter table 表名 add constraint 约束名字 约束类型[字段]; alter table c5 add constraint PK_c5_id primary key(id); 说明：PK_c5_id是约束名(指定主键约束为PK_c5_id，对大部分数据库有效但对于MySql无效，此主键约束名仍为primary) 在建表时给主键个性化名字较好 修改表名： rename table 旧表名 to 新表名; rename table c5 to cc55; 建表后添加唯一性约束： alter table 表名 add unique(字段名) alter table c9 add unique(id); 建表后添加默认值约束： alter table 表名 alter 列名 set default’默认值’; alter table c11 alter name set default “欧”; 建表后添加非空约束： alter 表名 modify 字段名字段类型not null; alter table c12 modify id int not null; 建表以后添加外键： alter table 表名称 add foreign key (列名称) references关联表名称(列名称); alter table stuInfo add foreign key (scode) references score(studentID); MySQL中的数据删除 Mysql删除表中的数据有三种方法，分别是delete ，drop，truncate delete删除数据 delete from 表名 where 条件 一行一行的删除，可以滚动，删除 之后不释放空间，保留表的数据结构 列：delete from student where 出生日期<1997-01-01 drop删除数据 drop table 表名 直接删除全部删除，不可找回，删除完之后释放空间 truncat删除数据 truncate table 表名 删除表的所有数据，删除完之后释放空间，保留数据结构不可以回滚，不可以找回，不能与where连用 mysql中的数据查询 单表查询 选择表中的若干列 指定查询 select 列1,列2...form 表名; 经过计算的查询 MySQL中的视图 什么是视图：一个虚拟表，主要是select语句执行返回的结果集。视图结果动态生成主要是sql语句执行结果与联合，基于查询结果。 视图的创建 语法：create view 视图名 [列表名] as select 语句 MySQL数据库用户管理 添加和删除用户 创建用户 create user 用户名 identified by '密码'; 添加一个新用户user密码为root IDENTIFIED BY 用于设定密码，MySQL 会先将密码进行加密，在将其保存到 user 表 列：create user 'user'@'localhost' identified by 'root'; 查看当前已有用户 MySQL 中所有的用户及权限信息都存储在默认数据库 mysql 的 user 表中。 进入 mysql 数据库，通过 desc user; 可以查看 user 表的结构。 use mysql; desc user; //查看user表的结构 host: 允许访问的主机地址，localhost 为本机，% 为任何主机。 user: 用户名 authentication_string: 加密后的密码值，哈希函数加密不可逆，在传入密码值时通过相同的加密方式加密对比加密值判断 使用 select * from user; 查看 user 表中当前有哪些用户。 select host,user,authentication_string from user; 账号重命名 rename user 原来用户名 to 新的用户名; 仅 MySQL 5及之后的版本支持 RENAME USER。 MySQL 5以前的版本，要重命名一个用户，可使用 UPDATE 直接更新 user 表（谨慎操作）。 修改密码 set password for 用户= password('password'); 可以使用 SET PASSWORD 语句重置账号密码。 使用 SET PASSWORD 重置账号密码。新密码必须通过 Password() 函数进行加密。 当不指定用户名时， SET PASSWORD 会重置当前登录用户的密码 SET PASSWORD = Password('password'); 删除用户 drop user 用来删除一个或多个MySQL用户，并取消相关权限 Drop user 用户1,用户2,... ; 删除用户user drop user user@localhost; 权限管理 用户认证 形式： 用户名+主机 远程主机也可链接 授权 grant [权限1,权限2,权限3] on *.* to user@'host' identified by 'password'; 查看当前权限 show grants; 查看指定用户的权限 show grants for admin@localhost;