请选择 进入手机版 | 继续访问电脑版

详细讲解MySQL(一):字符集、数据类型、列属性、SELECT、UNION、子查询、

[复制链接]
科达工艺 发表于 2021-1-1 18:33:38 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
目录



(一). MySQL

1. 根本利用

  1. /* Windows服务 */ -- 启动MySQL net start mysql -- 创建Windows服务 sc create mysql binPath= mysqld_bin_path(注意:等号与值之间有空格)/* 毗连与断开服务器 */ mysql -h 所在 -P 端口 -u 用户名 -p 暗码 SHOW PROCESSLIST -- 显示哪些线程正在运行 SHOW VARIABLES -- 显示系统变量信息
复制代码
2. 数据库利用

  1. /* 数据库利用 */ ------------------ -- 检察当前数据库 SELECT DATABASE(); -- 显示当前时间、用户名、数据库版本 SELECT now(), user(), version(); -- 创建库 CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项 数据库选项: CHARACTER SET charset_name COLLATE collation_name -- 检察已有库 SHOW DATABASES[ LIKE 'PATTERN'] -- 检察当前库信息 SHOW CREATE DATABASE 数据库名 -- 修改库的选项信息 ALTER DATABASE 库名 选项信息 -- 删除库 DROP DATABASE[ IF EXISTS] 数据库名 同时删除该数据库相关的目录及其目录内容
复制代码
3. 表的利用

  1. -- 创建表 CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的布局界说 )[ 表选项] 每个字段必须有数据范例 最后一个字段后不能有逗号 TEMPORARY 暂时表,会话竣事时表自动消失 对于字段的界说: 字段名 数据范例 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] -- 表选项   -- 字符集 CHARSET = charset_name 如果表没有设定,则使用数据库字符集 -- 存储引擎 ENGINE = engine_name 表在管理数据时采取的差异的数据布局,布局差异会导致处置处罚方式、提供的特性利用等差异 常见的引擎:InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive 差异的引擎在保存表的布局和数据时采取差异的方式 MyISAM表文件含义:.frm表界说,.MYD表数据,.MYI表索引 InnoDB表文件含义:.frm表界说,表空间数据和日志文件 SHOW ENGINES -- 显示存储引擎的状态信息 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息-- 自增起始数 AUTO_INCREMENT = '行数' -- 数据文件目录 DATA DIRECTORY = '目录' -- 索引文件目录 INDEX DIRECTORY = '目录' -- 表注释 COMMENT = 'string' -- 分区选项 PARTITION BY ... (详细见手册) -- 检察所有表 SHOW TABLES[ LIKE 'pattern'] SHOW TABLES FROM 库名 -- 检察表布局 SHOW CREATE TABLE 表名 (信息更详细) DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] -- 修改表 -- 修改表自己的选项 ALTER TABLE 表名 表的选项 eg: ALTER TABLE 表名 ENGINE=MYISAM; -- 对表举行重命名 RENAME TABLE 原表名 TO 新表名 RENAME TABLE 原表名 TO 库名.表名 (可将表移动到另一个数据库) -- RENAME可以互换两个表名 -- 修改表的字段机构(13.1.2. ALTER TABLE语法) ALTER TABLE 表名 利用名 -- 利用名 ADD[ COLUMN] 字段界说 -- 增加字段 AFTER 字段名 -- 体现增加在该字段名背面 FIRST -- 体现增加在第一个 ADD PRIMARY KEY(字段名) -- 创建主键 ADD UNIQUE [索引名] (字段名)-- 创建唯一索引 ADD INDEX [索引名] (字段名) -- 创建普通索引 DROP[ COLUMN] 字段名 -- 删除字段 MODIFY[ COLUMN] 字段名 字段属性 -- 支持对字段属性举行修改,不能修改字段名 (所有原有属性也需写上)CHANGE[ COLUMN] 原字段名 新字段名 字段属性 -- 支持对字段名修改 DROP PRIMARY KEY -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性) DROP INDEX 索引名 -- 删除索引 DROP FOREIGN KEY 外键 -- 删除外键 -- 删除表 DROP TABLE[ IF EXISTS] 表名 ...  -- 清空表数据  TRUNCATE [TABLE] 表名  -- 复制表布局  CREATE TABLE 表名 LIKE 要复制的表名  -- 复制表布局和数据  CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名  -- 检查表是否有错误  CHECK TABLE tbl_name [, tbl_name] ... [option] ...  -- 优化表  OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...  -- 修复表  REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]  -- 分析表ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
复制代码
4. 数据利用

  1. /* 数据利用 */ ------------------ -- 增INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...] -- 如果要插入的值列表包含所有字段并且顺序一致,则可以省略字段列表。 -- 可同时插入多条数据记录! REPLACE 与 INSERT 完全一样,可互换。 INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...] -- 查SELECT 字段列表 FROM 表名[ 其他子句] -- 可来自多个表的多个字段 -- 其他子句可以不使用-- 字段列表可以用*取代,体现所有字段 -- 删DELETE FROM 表名[ 删除条件子句] 没有条件子句,则会删除全部 -- 改UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
复制代码
5. 字符集编码

  1. /* 字符集编码 */ ------------------  -- MySQL、数据库、表、字段均可设置编码  -- 数据编码与客户端编码不需一致  SHOW VARIABLES LIKE 'character_set_%' -- 检察所有字符集编码项  character_set_client     客户端向服务器发送数据时使用的编码  character_set_results    服务器端将效果返回给客户端所使用的编码  character_set_connection 毗连层编码  SET 变量名 = 变量值  SET character_set_client = gbk;  SET character_set_results = gbk;  SET character_set_connection = gbk;  SET NAMES GBK;  -- 相当于完成以上三个设置  -- 校对集  校对集用以排序  SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 检察所有字 符集 SHOW COLLATION [LIKE 'pattern'] 检察所有校对集 CHARSET 字符集编码 设置字符集编码 COLLATE 校对集编码 设置校对集编码
复制代码
6. 数据范例(列范例)

  1. /* 数据范例(列范例) */ ------------------  1. 数值范例 -- a. 整型 ---------- 范例         字节         范围(有符号位) tinyint      1字节        -128 ~ 127 无符号位:0 ~ 255 smallint     2字节        -32768 ~ 32767 mediumint    3字节        -8388608 ~ 8388607 int          4字节bigint       8字节 int(M) M体现总位数 - 默认存在符号位,unsigned 属性修改 - 显示宽度,如果某个数不敷界说字段时设置的位数,则前面以0补填,zerofill 属性修改 例:int(5) 插入一个数'123',补填后为'00123' - 在满意要求的情况下,越小越好。 - 1体现bool值真,0体现bool值假。MySQL没有布尔范例,通过整型0和1体现。常用tinyint(1)表 示布尔型。 -- b. 浮点型 ---------- 范例              字节       范围 float(单精度)     4字节      double(双精度)    8字节 浮点型既支持符号位 unsigned 属性,也支持显示宽度 zerofill 属性。 差异于整型,前后均会补填0. 界说浮点型时,需指定总位数和小数位数。 float(M, D) double(M, D) M体现总位数,D体现小数位数。 M和D的巨细会决定浮点数的范围。差异于整型的固定范围。 M既体现总位数(不包罗小数点和正负号),也体现显示宽度(所有显示符号均包罗)。 支持科学计数法体现。 浮点数体现近似值。 -- c. 定点数 ----------  decimal       -- 可变长度  decimal(M, D) M也体现总位数,D体现小数位数。  保存一个精确的数值,不会发生数据的改变,差异于浮点数的四舍五入。  将浮点数转换为字符串来保存,每9位数字保存为4个字节。  2. 字符串范例 -- a. char, varchar ---------- char    定长字符串,速度快,但浪费空间 varchar 变长字符串,速度慢,但节流空间 M体现能存储的最大长度,此长度是字符数,非字节数。 差异的编码,所占用的空间差异。 char,最多255个字符,与编码无关。 varchar,最多65535字符,与编码有关。 一条有效记录最大不能凌驾65535个字节。 utf8 最大为21844个字符,gbk 最大为32766个字符,latin1 最大为65532个字符 varchar 是变长的,需要使用存储空间保存 varchar 的长度,如果数据小于255个字节,则采取一个 字节来保存长度,反之需要两个字节来保存。 varchar 的最大有效长度由最大行巨细和使用的字符集确定。 最大有效长度是65532字节,因为在varchar存字符串时,第一个字节是空的,不存在任何数据,然后 还需两个字节来存放字符串的长度,所以有效长度是65535-1-2=65532字节。 例:若一个表界说为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少? 答:(65535-1-2-4-30*3)/3 -- b. blob, text ---------- blob 二进制字符串(字节字符串) tinyblob, blob, mediumblob, longblob text 非二进制字符串(字符字符串) tinytext, text, mediumtext, longtext text 在界说时,不需要界说长度,也不会盘算总长度。 text 范例在界说时,不可给default值 -- c. binary, varbinary ---------- 雷同于char和varchar,用于保存二进制字符串,也就是保存字节字符串而非字符字符串。 char, varchar, text 对应 binary, varbinary, blob. 7. 日期时间范例 一般用整型保存时间戳,因为PHP可以很方便的将时间戳举行格式化。 datetime        8字节       日期实时间     1000-01-01 00:00:00 到 9999-12-31 23:59:59 date            3字节       日期           1000-01-01 到 9999-12-31 timestamp       4字节       时间戳         19700101000000 到 2038-01-19 03:14:07time            3字节       时间           -838:59:59 到 838:59:59 year            1字节       年份           1901 - 2155 datetime     YYYY-MM-DD hh:mm:ss timestamp    YY-MM-DD hh:mm:ss              YYYYMMDDhhmmss             YYMMDDhhmmss              YYYYMMDDhhmmss              YYMMDDhhmmss date         YYYY-MM-DD              YY-MM-DD              YYYYMMDD              YYMMDD              YYYYMMDD              YYMMDD time         hh:mm:ss              hhmmss              hhmmss year         YYYY              YYYYYY              YY 8. 摆列和集合 -- 摆列(enum) ---------- enum(val1, val2, val3...) 在已知的值中举行单选。最大数量为65535. 摆列值在保存时,以2个字节的整型(smallint)保存。每个摆列值,按保存的位置顺序,从1开始逐一 递增。体现为字符串范例,存储却是整型。 NULL值的索引是NULL。 空字符串错误值的索引值是0。 -- 集合(set) ---------- set(val1, val2, val3...) create table tab ( gender set('男', '女', '无') ); insert into tab values ('男, 女'); 最多可以有64个差异的成员。以bigint存储,共8个字节。采取位运算的形式。 当创建表时,SET成员值的尾部空格将自动被删除。
复制代码
7. 列属性(列约束)

/* 列属性(列约束) */ ------------------

  • PRIMARY 主键


  • 能唯一标识记录的字段,可以作为主键。
  • 一个表只能有一个主键。
  • 主键具有唯一性。
  • 声明字段时,用 primary key 标识。
    也可以在字段列表之后声明
    例:create table tab ( id int, stu varchar(10), primary key (id));
  • 主键字段的值不能为null。
  • 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
    例:create table tab ( id int, stu varchar(10), age int, primary key
    (stu, age));

  • UNIQUE 唯一索引(唯一约束)
    使得某字段的值也不能重复。
  • NULL 约束
    null不是数据范例,是列的一个属性。
    体现当前列是否可以为null,体现什么都没有。
    null, 允许为空。默认。
    not null, 不允许为空。
    insert into tab values (null, ‘val’);
    – 此时体现将第一个字段的值设为null, 取决于该字段是否允许为null
  • DEFAULT 默认值属性
    当前字段的默认值。
    insert into tab values (default, ‘val’); – 此时体现强制使用默认值。
    create table tab ( add_time timestamp default current_timestamp );
    – 体现将当前时间的时间戳设为默认值。
    current_date, current_time
  • AUTO_INCREMENT 自动增长约束
    自动增长必须为索引(主键或unique)
    只能存在一个字段为自动增长。
    默认为1开始自动增长。可以通过表属性 auto_increment = x举行设置,或 alter table tbl
    auto_increment = x;
  • COMMENT 注释
    例:create table tab ( id int ) comment ‘注释内容’;
  • FOREIGN KEY 外键约束
用于限制主表与从表数据完整性。
alter table t1 add constraint t1_t2_fk foreign key (t1_id)
references t2(id);
– 将表t1的t1_id外键关联到表t2的id字段。
– 每个外键都有一个名字,可以通过 constraint 指定
存在外键的表,称之为从表(子表),外键指向的表,称之为主表(父表)。
作用:保持数据一致性,完整性,主要目标是控制存储在外键表(从表)中的数据。
MySQL中,可以对InnoDB引擎使用外键约束:
语法:
foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作]
[主表记录 更新时的动作]
此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下,可以设置
为 null.前提是该外键列,没有not null。
可以不指定主表记录更改或更新时的动作,那么此时主表的利用被拒绝。
如果指定了 on update 或 on delete:在删除或更新时,有如下几个利用可以选择:

  • cascade,级联利用。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被 删除,从表相关记录也被删除。
  • . set null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录 被删除,从表相关记录外键被设置成null。但注意,要求该外键列,没有not null属性约束。
  • restrict,拒绝父表删除和更新。
    注意,外键只被InnoDB存储引擎所支持。
8. 建表规范

  1. /* 建表规范 */ ------------------ -- Normal Format, NF - 每个表保存一个实体信息 - 每个具有一个ID字段作为主键 - ID主键 + 原子表 -- 1NF, 第一范式 字段不能再分,就满意第一范式。 -- 2NF, 第二范式  满意第一范式的前提下,不能出现部分依赖。 消除复合主键就可以制止部分依赖。增加单列关键字。  -- 3NF, 第三范式  满意第二范式的前提下,不能出现通报依赖。 某个字段依赖于主键,而有其他字段依赖于该字段。这就是通报依赖。  将一个实体信息的数据放在一个表内实现。
复制代码
9. SELECT

  1. /* SELECT */ ------------------ SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT a. select_expr -- 可以用 * 体现所有字段。 select * from tb; -- 可以使用表达式(盘算公式、函数调用、字段也是个表达式) select stu, 29+25, now() from tb; -- 可以为每个列使用别名。适用于简化列标识,制止多个列标识符重复。 - 使用 as 关键字,也可省略 as. select stu+10 as add10 from tb; b. FROM 子句 用于标识查询泉源。 -- 可以为表起别名。使用as关键字。 SELECT * FROM tb1 AS tt, tb2 AS bb; -- from子句后,可以同时出现多个表。  -- 多个表会横向叠加到一起,而数据会形成一个笛卡尔积。  SELECT * FROM tb1, tb2; -- 向优化符提示如何选择索引  USE INDEX、IGNORE INDEX、FORCE INDEX  SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;  SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3; c. WHERE 子句  -- 从from得到的数据源中举行筛选。  -- 整型1体现真,0体现假。   -- 表达式由运算符和运算数组成。   -- 运算数:变量(字段)、值、函数返回值    -- 运算符:    =, , , !=, , !, &&, ||,     in (not) null, (not) like, (not) in, (not) between and, is (not),     and, or, not, xor     is/is not 加上ture/false/unknown,查验某个值的真假      与功能相同,可用于null比力 d. GROUP BY 子句, 分组子句 GROUP BY 字段/别名 [排序方式] 分组后会举行排序。升序:ASC,降序:DESC 以下[合计函数]需共同 GROUP BY 使用: count 返回差异的非NULL值数目 count(*)、count(字段) sum 求和 max 求最大值 min 求最小值 avg 求匀称值 group_concat 返回带有来自一个组的毗连的非NULL值的字符串效果。组内字符串毗连。 e. HAVING 子句,条件子句 与 where 功能、用法相同,执行时机差异。 where 在开始时执行检测数据,对原数据举行过滤。 having 对筛选出的效果再次举行过滤。 having 字段必须是查询出来的,where 字段必须是数据表存在的。 where 不可以使用字段的别名,having 可以。因为执行WHERE代码时,大概尚未确定列值。 where 不可以使用合计函数。一般需用合计函数才会用 having SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。 f. ORDER BY 子句,排序子句 order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]升序:ASC,降序:DESC 支持多个字段的排序。 g. LIMIT 子句,限制效果数量子句 仅对处置处罚好的效果举行数量限制。将处置处罚好的效果的看作是一个集合,按照记录出现的顺序,索引从0开 始。 limit 起始位置, 获取条数 省略第一个参数,体现从索引0开始。limit 获取条数 h. DISTINCT, ALL 选项 distinct 去除重复记录 默认为 all, 全部记录
复制代码
10. UNION

  1. /* UNION */ ------------------ 将多个select查询的效果组合成一个效果集合。  SELECT ... UNION [ALL|DISTINCT] SELECT ...   默认 DISTINCT 方式,即所有返回的行都是唯一的 发起,对每个SELECT查询加上小括号包裹。   ORDER BY 排序时,需加上 LIMIT 举行联合。   需要各select查询的字段数量一样。   每个select查询的字段列表(数量、范例)应一致,因为效果中的字段名以第一条select语句为准。
复制代码
11. 子查询

  1. /* 子查询 */ ------------------ - 子查询需用括号包裹。 -- from型 from后要求是一个表,必须给子查询效果取个别名。 - 简化每个查询内的条件。 - from型需将效果生成一个暂时表格,可用以原表的锁定的释放。 - 子查询返回一个表,表型子查询。 select * from (select * from tb where id>0) as subfrom where id>1; -- where型 - 子查询返回一个值,标量子查询。 - 不需要给子查询取别名。 - where子查询内的表,不能直接用以更新。 - select * from tb where money = (select max(money) from tb); -- 列子查询 如果子查询效果返回的是一列。 使用 in 或 not in 完成查询 exists 和 not exists 条件 如果子查询返回数据,则返回1或0。常用于判断条件。 select column1 from t1 where exists (select * from t2); -- 行子查询 查询条件是一个行。 select * from t1 where (id, gender) in (select id, gender from t2); 行构造符:(col1, col2, ...) 或 ROW(col1, col2, ...) 行构造符通常用于与对能返回两个或两个以上列的子查询举行比力。 -- 特殊运算符 != all() 相当于 not in = some() 相当于 in。any 是 some 的别名 != some() 不等同于 not in,不即是此中某一个。 all, some 可以共同其他运算符一起使用。
复制代码
12. 毗连查询(join)

  1. /* 毗连查询(join) */ ------------------ 将多个表的字段举行毗连,可以指定毗连条件。 -- 内毗连(inner join) - 默认就是内毗连,可省略inner。 - 只有数据存在时才气发送毗连。即毗连效果不能出现空行。 on 体现毗连条件。其条件表达式与where雷同。也可以省略条件(体现条件永远为真) 也可用where体现毗连条件。 另有 using, 但需字段名相同。 using(字段名) -- 交叉毗连 cross join 即,没有条件的内毗连。 select * from tb1 cross join tb2;  -- 外毗连(outer join)  - 如果数据不存在,也会出现在毗连效果中。 -- 左外毗连 left join 如果数据不存在,左表记录会出现,而右表为null填充 -- 右外毗连 right join 如果数据不存在,右表记录会出现,而左表为null填充 -- 自然毗连(natural join) 自动判断毗连条件完成毗连。 相当于省略了using,会自动查找相同字段名。 natural join natural left join natural right join select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;
复制代码
13. TRUNCATE

  1. /* TRUNCATE */ ------------------ TRUNCATE [TABLE] tbl_name 清空数据 删除重建表 区别: 1,truncate 是删除表再创建,delete 是逐条删除 2,truncate 重置auto_increment的值。而delete不会 3,truncate 不知道删除了几条,而delete知道。 4,当被用于带分区的表时,truncate 会保留分区
复制代码
来源:https://blog.csdn.net/Java_Yhua/article/details/112044424
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发布主题

专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )